엑셀 피벗 테이블은 데이터를 분석하는 강력한 도구지만, 여전히 많은 사람에게 어렵고 복잡하게 느껴지곤 합니다. 특히 매번 수동으로 피벗 테이블을 수정해야 하는 업무라면, 시간이 많이 소요되죠.
이번 글에서는 실무에서 생성형AI를 통해 피벗 테이블을 자동화 파이썬 스크립트를 작성하고 실행하는 구체적인 방법을 모두 정리해드립니다.
지금부터 따라오세요!
본 글은 피벗테이블 자동화에 관한 방법을 소개하는 것이지만, 최종목적지는 "대시보드 자동화" 입니다.
피벗 테이블 자동화는 마지막 단계인 대시보드까지 연결되어 있습니다. 피벗테이블이 차트와 연결되고 차트와 숫자Table을 보기 좋게 한 페이지로 정리되면 그것이 대시보드가 되는 것이죠.
본 글에서는 가장 기초가 되는 피벗자동화를 하는 방법을 소개합니다. 무작정 따라오시기만 하세요. 신세계가 열릴 것입니다.
주의) 본 글은 엑셀 피벗테이블을 사용할 줄 아는 사용자에게 적합한 글입니다.
피벗 테이블 자동화는 마지막 단계인 대시보드까지 연결되어 있습니다. 피벗테이블이 차트와 연결되고 차트와 숫자Table을 보기 좋게 한 페이지로 정리되면 그것이 대시보드가 되는 것이죠.
본 글에서는 가장 기초가 되는 피벗자동화를 하는 방법을 소개합니다. 무작정 따라오시기만 하세요. 신세계가 열릴 것입니다.
주의) 본 글은 엑셀 피벗테이블을 사용할 줄 아는 사용자에게 적합한 글입니다.
 |
| 자동화의 전체과정 |
 |
| 엑셀로 대시보드 만들기(예시) |
1. 자동화를 위한 PC환경설정
피벗Table 자동화를 위해서는 PC에 파이썬 및 실행도구(VS CODE)를 설치해야 합니다.
2. 피벗자동화 Process
피벗Table은 Table과 연결되어 있는 원시데이터 (Raw Data)가 있습니다. 우리는 원시데이터를 지우고 새로운 데이터를 붙여넣기를 한 다음 새로고침을 통해 피벗Table을 Update 합니다.
이 과정을 파이썬 스크립트를 통해 자동화 하겠습니다.
아래는 전체과정을 도식화 한 그림입니다.
아래는 전체과정을 도식화 한 그림입니다.
두개의 파이썬스크립트가 있고, 첫번째 파이썬파일을 실행하면 원시데이터를 다운로드합니다. 두번째 파이썬파일을 실행하면 피벗Table이 있는 엑셀파일에서 기존원시데이터를 지우고, 새로 받은 원시데이터를 붙여넣기 후 새로고침 합니다.
우선 py.2 피벗table 자동화하는 방법부터 다뤄보겠습니다.
3. 생성형AI를 통해 원시데이터를 지우는 파이썬 스트립트 만들기
생성형AI에 아래와 같이 작성해서 요청해 봅시다.
아래와 같이 진행되는 파이썬 스크립트를 만들어주세요.
===
1. "C:\OOOO\OOOOOO\OOOO\OOOO" 폴더에 있는 "OOO.xlsx"파일을 연다.
2. 상기 엑셀파일의 Sheet명 "AAA"의 D:U 영역의 DATA를 지운다.
- 폴더경로 복사 : 파일이 있는 폴더에 접근해서 경로 창을 클릭 + 복사
 |
| 경로복사 |
- 피벗Table이 있는 엑셀파일명, 원시데이터 Sheet명, 지워야 할 영역을 A:Z 형식으로 지정
 |
| 명령 프롬프트 |
 |
| 결과 |
설명에서 자세히 실행방법을 설명하고 있습니다. 지정된 폴더경로에서 지정한 엑셀파일을 찾아 열고 지정된 시트의 영역의 DATA를 삭제한다고 설명되어 있음을 확인할 수 있습니다. 위의 내용과 일치하는 결과가 아니더라도 대동소이한 내용의 결과가 출력되어 있을 것입니다.
중요한 내용은 pip 인스톨에 관한 내용입니다. VS CODE의 터미널창에 안내한 그대로 pip install openpyxl 을 치고 enter를 하면 해당 스트립트 실행을 위한 라이브러리가 다운로드되어 실행이 가능한 상태가 됩니다.
참고로 파이썬스크립트는 해당 스크립트를 실행하기 위한 라이브러리가 설치가 되어 있어야 합니다. 기본적인 것은 파이썬을 설치하면 내장이 되어 있습니다만, 그렇지 않은 경우는 pip install 명령을 통해 해당라이브러리를 설치를 해 줘야 합니다. 이 또한 생성형AI가 기본적으로 안내를 해 줍니다. 친절하죠..?
4. 파이썬 스크립트 TEST 실행해 보기
- VS Code에서 새파일을 추가하고 "피벗자동화.py"으로 파일명을 만들어 보자
VS CODE실행
- 생성형AI가 작성해준 스크립트를 Copy해서 VS Code 스크립트 창에 붙여넣기 하고 실행(Ctrl+F5)해 보자
|
| VS CODE실행 |
- 생성형AI가 작성해준 스크립트를 Copy해서 VS Code 스크립트 창에 붙여넣기 하고 실행(Ctrl+F5)해 보자
 |
| 실행결과 |
의도한 바와 같이 D:U에 해당하는 DATA가 지워진 결과입니다.
어떠한가요? 놀랍지 않은가요? 컴퓨터와 대화를 통해서 일을 시킨 첫번째 경험입니다.
생성형AI는 정말 친절하게 모든 설명을 하고 있고, 그대로 따라해 보면 의도한 바와 같이 그대로 잘 실행된 결과를 확인할 수 있습니다. 만약 TEST과정에서 오류가 있다면 터미널의 오류 메시지를 복사해서 그대로 ChatGPT에게 던져 봅시다. 그러면 해결방법을 가이드 해 주거나, 다시 수정을 해 줄 것이고, 이를 몇 번 반복하면 원하는 결과를 얻을 수 있습니다.
위 코딩은 기초적 기능이 담긴 단순한 프로그램이므로 어렵지 않게 성공할 수 있을 것입니다.
어떠한가요? 놀랍지 않은가요? 컴퓨터와 대화를 통해서 일을 시킨 첫번째 경험입니다.
생성형AI는 정말 친절하게 모든 설명을 하고 있고, 그대로 따라해 보면 의도한 바와 같이 그대로 잘 실행된 결과를 확인할 수 있습니다. 만약 TEST과정에서 오류가 있다면 터미널의 오류 메시지를 복사해서 그대로 ChatGPT에게 던져 봅시다. 그러면 해결방법을 가이드 해 주거나, 다시 수정을 해 줄 것이고, 이를 몇 번 반복하면 원하는 결과를 얻을 수 있습니다.
위 코딩은 기초적 기능이 담긴 단순한 프로그램이므로 어렵지 않게 성공할 수 있을 것입니다.
첫 성공이 중요한 이유는, 최초로 성공한 스크립트가 'Seed 스크립트'가 되어 이후 기능을 추가 해 나가는 기반이 되기 때문입니다.
이제부터는 여기서 코드를 추가하여 점차 기능을 확장해 나가는 '빌드업' 과정을 진행할 것입니다.
이제부터는 여기서 코드를 추가하여 점차 기능을 확장해 나가는 '빌드업' 과정을 진행할 것입니다.
5. 생성형AI를 통해 원시데이터 Update 스크립트 만들기
: Raw 컬럼에 새로운 데이터를 붙여넣고 새로고침
상기 “Seed 스크립트”를 생성형AI에 붙여넣기(던지기)하고 엔터를 쳐보자. 그러면 생성형AI 는 스크립트에 대해서 한차례 설명을 할 것이다. 그러면 아래와 같이 코딩 프롬프트를 작성해서 추가적인 대화해보자.
상기 스트립트에서 아래의 내용이 그 다음 반영되도록 작성해주세요===1. "C:\OOOO\OOOOOO\OOOO\OOOO" 폴더에 있는 가장 최신의 파일을 연다.2. "OOO" Sheet의 A2부터 Table전체를 값복사한다.3. "Main file.xls" Sheet명 "OOO" 의 D1을 선택하고 값만 붙여넣기하고 새로고침한다.
* 상기 폴더경로 및 파일명 셀주소등은 사용자 환경에 맞게 수정해 주면 된다.
상기 폴더에 있는 원시데이터(Raw data) 엑셀파일과 피벗TABLE이 있는 Main 엑셀파일이 같이 있습니다. 원시데이터 파일들이 해당 폴더에 다운로드 되어 있고, 가장 최신의 원시데이터 파일을 열어서 Main파일의 원시데이터 영역에 붙여넣기 후 새로고침하는 구조입니다.
필요에 따라 저장 후 종료등의 명령을 추가할 수 있습니다.
아래는 생성형AI가 작성해준 최종 스크립트 파일입니다. 명령 내용이 추가되면서 스크립트가 길어지게 됩니다.
필요에 따라 저장 후 종료등의 명령을 추가할 수 있습니다.
아래는 생성형AI가 작성해준 최종 스크립트 파일입니다. 명령 내용이 추가되면서 스크립트가 길어지게 됩니다.
 |
| 완성스크립트 예시 |
스크립트 작성결과는 각자의 환경에 따라 달라집니다. VS Code에 스크립트는 붙여넣고 test실행과정 및 오류메시지를 생성형AI에 반복적으로 수정요청을 하면서 완성해 나갈 수 있습니다.
아래는 피벗자동화.py 스트립트를 다운로드하는 링크입니다. 주석처리된 부분을 활용하여 본인에게 맞는 환경으로 변경해서 TEST해 보시고 활용하시기 바랍니다.
완성 스크립트(피벗자동화.py) 다운로드하기
 |
| VS CODE화면 |
완성 스크립트(피벗자동화.py) 다운로드하기
6. 마무리
본 글에서는 생성형AI를 활용하여 피벗TABLE 자동화하는 파이썬 스크립트 작성 및 실행방법에 대해서 알아봤습니다. 다음 글에서는 pyautogui 파이썬 라이브러리를 활용하여 원시데이터 다운로드 자동화 스크립트 작성에 대해서 알아보도록 하겠습니다.
Tags:
엑셀자동화
