ChatGPT X 엑셀 업무자동화(3) - 데이터정규화(Tool 사용법 포함)
지금까지 우리는 ChatGPT에 대해서 알아보고 ChatGPT를 이용해서 엑셀 수식을 작성하는 법을 배웠습니다. 분명 수식에 대한 고민만 해결이 되어도 업무에 큰 도움이 됩니다. 하지만 오늘 다루는 내용을 제대로 소화하지 못한다면 여러분의 엑셀은 단순히 고급계산기의 역할밖에 하지 못한다고 자신있게 말씀드릴 수 있습니다. 이번 업무자동화 프로젝트의 목표는 어디까지나 "엑셀"을 위한 ChatGPT입니다. 엑셀 그 자체를 제대로 이해하고 다루지 못한다면 ChatGPT의 조력도 한계가 있습니다.
목차
엑셀 데이터정규화
이런 저런 자료들을 통해 찾아본 바에 의하면 엑셀 데이터정규화는 꽤 다양한 정의를 가집니다.
하지만 공통적인 의견을 정리하면 다음과 같습니다.
엑셀의 데이터를 분석에 적합한 데이터셋으로 만드는 것
이렇게 분석에 적합하게 정리된 데이터를 Tidy Dataset이라고 부르는데 우리는 앞으로 정규화된 데이터라고 용어를 통일하겠습니다.
이러한 정규화된 데이터는 다음과 같은 특징이 있습니다.
1. 각 변수는 열을 형성합니다.
2. 각 관측치는 행을 형성합니다.
3. 관측 단위의 각 유형은 표를 구성합니다.
예시를 한번 봅시다.
데이터의 변수(연도, 제품, 매출액)는 열로 구분되고 관측치(데이터)는 행으로 한 줄씩 누적이 되는 데이터형태입니다.
이렇게 정리되지 않은 엑셀은 데이터로써의 가치를 지니지 못합니다.
회사에서 이런 형태가 아닌 엑셀들을 많이 보셨을 겁니다. 보고를 위해 1페이지로 작성하려다 보니 별별 해괴한 엑셀들이 다 만들어지죠. 보고서를 위한 정리는 나중에 다룰 피벗테이블로 해도 충분합니다. 적어도 하나의 엑셀파일에 위와 같은 정규화된 엑셀 데이터가 한 시트는 존재하고, 나머지 작업은 그 정규화된 엑셀 데이터를 가지고 가공하는 형태로 업무를 진행해야 합니다. 그렇게 하지 않는다면 우리는 앞으로 다루게 될 진정한 의미의 업무자동화를 더 이상 진행할 수 없게 됩니다.
솔직히 이전 포스팅 포함해서 앞으로 다루게 될 그 어떤 내용보다도 오늘 다루는 이 엑셀 데이터정규화가 가장 중요하다고 생각합니다.
개인적으로는 전 세계에 이렇게 정규화된 엑셀파일들만 존재해야 한다고 강하게 믿습니다!!!!!!
다음으로는 엑셀계의 금쪽이들을 만나봅시다.
열 머리글이 변수가 아닌 데이터셋
변수가 뭐냐! 하시면 위 엑셀의 연도, 제품, 매출액이라고 생각하시면 되겠습니다.
예시 보시죠.
뭔가 잘못됐습니다.
Product들이 왜 옆으로 누워있죠?(이런 엑셀을 보시면 바로 이렇게 이상하다고 생각하셔야 합니다.)
바로 하나의 열을 이루고 있어야 할 제품이라는 변수가 옆으로 누워있으면 이를 "열 머리글이 변수가 아닌 데이터셋"이라고 생각하시면 됩니다.
원래는 엑셀에서 이 문제를 해결하기 위해 하나하나 수작업으로 고치거나 파워쿼리를 사용합니다.
파워쿼리를 배워놓는 것은 아주 좋은 공부라고 생각하지만 지금은 엑셀과 ChatGPT의 협업에 더 중점을 두겠습니다.
이러한 문제를 해결하기 위한 방법을 앞으로 우리는 "열 피벗 해제"라고 부르겠습니다.
한 단계 더 나가보겠습니다. 아래 엑셀 데이터를 봐주시죠.
문제가 조금 더 심각합니다.
위에서 다룬 문제 이외에 구분이라는 열이 이상합니다. 계획, 실적, 달성률 이렇게 3개씩 하나의 세트로 이루어져 있네요.
해결방법은 일단 다음 금쪽이부터 만나보고 아래에서 한꺼번에 다뤄보겠습니다.
열 머리글이 여러 개인 데이터셋
바로 봅시다.
정말.. 이런 엑셀을 만나게 되면 화가 머리끝까지 나곤 합니다. 이런 거 만드는 사람은 컴퓨터 압수해버려야 합니다!!!
물론 그냥 보기좋게 만들어야지 하면서 만드셨겠지만.. 저는 개인적으로 엑셀에서 셀 병합기능 자체가 사라져야 한다고 생각하는 사람입니다. 병합하지 말라고 제발!! 그런거 머릿속에서 지워버리라고!!
여러분, 열 머리글(지금 위 그림의 빨간 네모, 제목줄이라고 생각합시다)은 한 줄로 한 칸에 하나씩 입력하시면 됩니다. 이상한 거 하지 마세요. 이 부분만큼은 창의력을 발휘하지 말아주세요.
열 머리글이 변수가 아닌 데이터셋에서 보았던 지금 이 예제가 위쪽 빨간 네모를 해결한 결과물이라고 보시면 됩니다.(물론 이것도 "열 머리글이 변수가 아닌 데이터셋"이라 작업 중인 겁니다.)
그래서 뭘 어쩌라는 거냐? 궁금하실 텐데요.
제가 직접 많이 실험을 해봤습니다.
업로드해서 이렇게 해달라 저렇게 해달라 요구를 많이 해봤는데 간단한 건 해줍니다. 예를 들어 보겠습니다.
이 데이터가 있는 엑셀을 업로드하고 "열 피벗 해제해 줘"라고 하면 해줍니다.
그런데 조금 복합적인 문제가 있으면 아직은 ChatGPT가 버거워하더라구요.
그래서 만들었습니다, 엑셀 데이터정규화 Tool
https://chatgpt.com/g/g-GVWinXSbN-egsel-deiteojeonggyuhwa-tool
ChatGPT - 엑셀 데이터정규화 Tool
엑셀 데이터 전처리 및 변환 도구
chatgpt.com
만들어서 뿌리는 게 빠를 거 같아 GPTs로 만들었습니다.
예시부터 보시죠.(스압 주의)
이 데이터의 엑셀을 업로드해서
이런 과정을 거쳐서
이런 결과물을 받았습니다.
너무 길어서 중간에 잘랐습니다.
데이터를 잘 정리해주는군요!
제가 학습시킨 내용을 다 공개할 순 없지만 어떤 식으로 진행이 되는지를 보여드릴게요.
엑셀 파일 업로드 후 다음 질문을 순차적으로 진행합니다:
1. 셀 병합 해제를 할까요(Y/N)?
2. 열 피벗 해제를 할까요?(Y/N)
3. 열 피벗 해제 시 열 피벗이 특정 개수로 반복되고 있나요?(Y/N)
- Y를 선택했다면
3-1. 몇 개인가요?
3-2. 가로로 반복되고 있나요, 세로로 반복되고 있나요?
4. 열 머리글이 여러 개인 데이터셋 전처리를 할까요?(Y/N)
5. 작업을 시작할까요?(Y/N)
- Y라면 작업시작
- N이라면 추가적인 작업이 있는지 물어보기.
모든 작업이 끝난 후 데이터프레임 형식으로 결과물을 제공
지금까지 내용을 쭉 따라오신 분들이라면 위 과정이 다 이해가 가실 거예요. 3번만 추가 설명이 필요할 거 같습니다.
예시 보시죠.
위에서 한번 언급했지만 구분이란 이상한 열이 있고 그 안에 원래 열로 존재했어야 할 계획, 실적, 달성률이라는 친구들이 있네요.
이걸 해결해야 하는데 제가 GPTs를 학습시킬 때 '열 피벗이 몇 개로 반복되고 있느냐'라고 질문하고 3개라고 답변하면 '지금 이 그림처럼 3개의 요소가 세트로 반복되고 있다' 이런 식으로 학습을 시켰어요.
그리고 가로로 반복되는지 세로로 반복되는지도 설정해 줬습니다.
열과 행이란 단어를 안 쓴 이유는 전체 범위가 아니라 몇 개만 반복되는 형태라 구분하기 위해서였어요.
이용방법은 간단합니다.
이 빨간 박스 누르시고 엑셀파일을 업로드하시면 순서대로 Y/N을 입력하거나 열 피벗 반복 개수, 가로/세로 선택만 하시면 돼요.
물론 결과물이 원하는 형태가 아니라면 작업 후 수정사항을 요청할 수 있는 단계도 마련해 놓았습니다.
작업을 마치시면 엑셀 다운까지 가능하게 해 놨어요.
팁을 드리자면 딱 봐도 너무 복잡하다 싶으면 간단한 수작업을 먼저 거친 뒤에 업로드하는 걸 추천드려요.
그리고 결과물이 거의 다 됐는데 조금 부족하다 싶으면 일단 다운받아서 수작업으로 하셔도 되고 다운받은 엑셀을 다시 업로드해서 작업 진행하시면 훨씬 빠르고 좋은 결과물을 받으실 수 있을 거예요.
함수를 유지해야 하면 함수유지해 달라고 요청 넣으시는 것도 추천해요.
장담합니다. 여러분의 시간을 아껴드릴 겁니다. 여러분의 퇴근을 앞당길 겁니다.
그동안 많은 포스팅을 했지만 오늘 만든 이 엑셀 데이터정교화 Tool이 이 귀환포탈이란 블로그의 목표에 가장 가까운 결과물인 것 같아서 무척 기분이 좋습니다.
여러분도 고생 많으셨어요!
포스팅이 도움이 되셨다면 구독, 공감, 댓글 부탁드려요!
행복한 하루 되세요!
'■ 칼퇴를 위한 ChatGPT 업무자동화' 카테고리의 다른 글
| ChatGPT X 엑셀 업무자동화(4) - VBA는 더 이상 어렵지 않다. (0) | 2024.07.18 |
|---|---|
| ChatGPT X 엑셀 업무자동화(2) - 복잡하고 긴 수식도 문제없다! (0) | 2024.07.11 |
| ChatGPT X 엑셀 업무자동화(1) - 수식과의 전쟁 (0) | 2024.07.10 |
| ChatGPT X 엑셀 업무자동화 프롤로그 (0) | 2024.07.10 |
| ChatGPT 고급사용법 익히기 (0) | 2024.06.26 |
