[브랜디드] 기획자의 챗GPT 활용법: 엑셀 데이터 마음대로 연동하고 편집하기

옆에서 친절하게 알려주는 나만의 엑셀 선배...!

[콘텐츠 발행 일자: 2024.09.09]

이번 아티클에서는 챗GPT를 엑셀 실무에 활용하는 다양한 방법 중 파워 쿼리(Power Query)에 적용하는 방법을 알아보려 합니다. 

챗GPT는 특히 프로그래밍적인 부분, 즉 수식과 함수처럼 컴퓨터가 이해할 수 있는 코드로 된 부분에 강한데요. 엑셀에는 수식과 함수 외에도 코드 형태의 명령문으로 처리할 수 있는 기능이 많습니다. 그중 대표적인 것이 파워 쿼리입니다. 

본격적인 사례를 살펴보기 전에, 아직 파워 쿼리에 익숙하지 않은 분들을 위해 간략하게 개념을 설명해 볼게요. 파워 쿼리의 대표적인 기능은 데이터를 연결하고 처리하는 것입니다. 

• 엑셀 파일에 외부 데이터를 '연결'하고,
• 연결된 데이터를 '처리'하는 강력한 엑셀 기능

먼저 데이터를 불러오는 게 아니라 '연결'한다고 표현했는데요. 잠시 후 실제 화면을 보여드리겠지만, 데이터를 한 번 불러와 로드하고 끝내는 게 아니라, 데이터 원본 소스와 현재 엑셀 파일을 연결하기 때문에 원본이 업데이트 또는 수정될 때마다 엑셀 파일에 반영됩니다.

연결된 데이터를 '처리'한다는 것은 필요에 맞게 데이터 형식을 변경하거나 테이블을 병합하는 등 데이터를 셰이핑 또는 트랜스폼하는 것인데요. 불러온 데이터를 그대로 사용하면 좋겠지만, 대개는 데이터 전처리 과정이 필요합니다. 현재 엑셀 파일에 맞지 않는 형식일 수도 있고, 잘못된 데이터가 들어가 있거나 데이터를 아예 인지하지 못하는 경우도 있어서 이를 처리하는 과정이 필요해요. 데이터 분석가들은 이런 전처리 과정이 전체 데이터 분석 프로세스의 80%를 차지한다고 할 정도로, 많은 리소스가 투입되어야 하는 단계입니다. 

파워 쿼리는 이러한 처리 능력이 기본 엑셀 기능보다 훨씬 강력합니다. 이 강력한 기능이 코드 형태로 만들어지다 보니, 순차적인 자동화 프로세스로 저장되고요. 한 번 연결한 데이터는 그 데이터를 어떻게 처리하든 모두 저장되니, 데이터를 연결하고 처리해 엑셀로 로드하는 일련의 과정을 하나의 자동화로 만들 수 있는 거죠. VBA을 이용한 매크로가 엑셀 자동화 1번이지만, 파워 쿼리로도 충분히 나만의 데이터 자동화 시스템을 만들 수 있습니다. 

[기초] 파워 쿼리 기초 개념 익히기

정리해 보면, 데이터를 연결하고, 처리하고, 처리한 여러 데이터를 결합해, 엑셀에 로드하는 과정을 파워 쿼리의 4단계라고 할 수 있습니다. 

실제 엑셀 파일을 보면서 이 과정을 익혀볼게요. 먼저 엑셀 메뉴의 [데이터] 탭을 누르면, [데이터 가져오기 및 변환]과 [쿼리 및 연결]이라는 두 개 그룹이 나오는데, 이 부분이 파워 쿼리입니다. 

기존에는 파워 쿼리 메뉴가 따로 있었는데, 이제는 데이터 탭 안에 하나의 그룹으로 존재합니다. 다만, 엑셀 2013 이전 버전에는 파워 쿼리 기능이 없으니, 이점 양해해 주시기 바랍니다. 

말씀드렸듯이, 파워 쿼리의 첫 번째 단계는 데이터 연결입니다. [데이터 가져오기]를 누르면, 다양한 데이터의 원본 소스를 연결할 수 있어요. 

[파일에서]를 보면, 엑셀 통합 문서부터 텍스트, PDF까지 다양한 데이터가 있습니다. 심지어 [폴더에서] 데이터를 불러 올 수도 있는데요. 제가 실무에서 굉장히 유용하게 사용하는 기능으로, 폴더에 있는 파일이 10개든, 1000개든 모두 가져오도록 세팅할 수 있습니다. 

[데이터 베이스에서]는 우리 회사가 SQL을 사용하는지, MySQL을 사용하는지 등에 따라 해당 데이터 베이스에 직접 쿼리를 연결해 데이터를 가져올 수 있고, 같은 마이크로소프트 제품이라 [Azure에서]를 통해 애저에 있는 데이터도 불러올 수 있어요. 최근에 많이 개발되는 [Power Platform]은 물론, 세일즈포스나 다이내믹365 등의 [온라인 서비스에서]도 데이터를 불러 올 수 있습니다. 

[기타 원본에서]에도 여러 가지가 있는데요. 기본적으로 현재 엑셀 파일의 [테이블/범위에서] 데이터를 가져올 수 있고, [웹]에 있는 데이터도 연결할 수 있습니다. 웹상의 정보를 탐색하고 수집하는 웹 크롤링이나 특정 사이트나 페이지에서 필요한 데이터를 추출하는 웹 스크래핑 기능과 유사하게 웹 데이터를 가져올 수 있죠. 

[기초] 파워 쿼리를 이용해 웹 데이터 불러오기

그럼, 파워 쿼리를 이용해 웹 데이터를 어떻게 불러오는지 직접 보여드릴게요. 

왼쪽 이미지는 대한민국의 행정 구역을 설명하는 위키백과 웹 페이지입니다. 스크롤을 쭉 내리면 다양한 데이터를 볼 수 있는데, 파워 쿼리는 테이블로 존재하는 데이터를 엑셀 파일로 가져올 수 있어요. 메뉴 탭의 [데이터]에서 [데이터 가져오기]를 클릭하고, [기타 원본에서]를 눌러 [웹]을 선택해 줄게요.

[웹에서]라는 창이 뜨면, 데이터를 불러올 웹 사이트의 주소를 복사해 붙여 넣고 [확인]을 눌러 줍니다. 그러면 해당 주소의 웹 사이트를 잠시 분석한 후, 아래와 같은 [탐색 창]을 보여줄 거예요. 웹 페이지가 가장 상단에 있고, 그 아래로 테이블 형태의 여러 가지 데이터가 나오죠. 

이 중 가장 필요한 데이터는 "행정 구역별 인구 및 면적" 표입니다. 데이터를 클릭한 후 아래 [데이터 변환]을 눌러 주세요. 그러면 [쿼리 처리 중]이라는 창이 뜨고, 잠시 후 아래와 같이 [파워 쿼리 편집기]가 나타납니다. 

보통 엑셀에서 보시는 것처럼 위에는 탭 메뉴가 있고, 왼쪽에는 쿼리가 있습니다. 가운데에는 불러온 데이터의 미리보기 화면이 있고, 그 위에 해당 쿼리의 수식이 나타나요. 

그리고 오른쪽 [쿼리 설정]에 [적용된 단계]가 있는데요. "원본" 데이터를 불러오고, "탐색"해서, 특정 유형을 "변경"했다는 것이 단계별로, 마치 프로그래밍을 하듯 라인별로 코드가 입력된다는 뜻입니다. 

상단의 [고급 편집기]를 누르면, 

이런 식으로 데이터 전처리 과정이 코드로 표시됩니다. 원본 단계는 첫째 줄, 탐색 단계는 둘째 줄, 마지막 변경된 유형은 세 번째 줄에 위치합니다. 단계별로 진행되는 결과가 화면에 보이는 건데, 사실 엑셀 파일도 마찬가지예요. 엑셀 프로그램도 버튼을 눌러 실행하지만, 뒤에서는 코드로 돌아가잖아요. 

마지막으로, 작업을 완료하고 상단의 [닫기 및 로드]를 누르면 엑셀 파일에 해당 데이터가 업로드됩니다. 이렇게 로드된 데이터는 웹 페이지 데이터와 연결돼 있기 때문에 변동 사항도 자동으로 업데이트됩니다. 

예를 들면, 환율이 고시된 은행의 웹 페이지를 이렇게 엑셀과 연동하면 좋겠죠. 그러면 매일 아침 은행의 웹 페이지를 새로 고쳐, 엑셀의 환율 데이터를 업데이트할 수 있습니다. 데이터를 가져와서, 처리하고, 로드하는 과정이 모두 자동으로 작동하는 거죠. 

파워 쿼리에 대한 기본적인 내용을 간단하게 살펴봤는데요. 이제 파워 쿼리에 챗GPT가 어떤 도움을 줄 수 있을지 알아보겠습니다. 이번에도 실제 예시 데이터를 바탕으로 구체적으로 설명해 드릴게요. 

[심화] 파워 쿼리에 챗GPT 활용하기

파워 쿼리에서 유용한 기능 중 하나인 그룹 기능을 이용해, 지점과 상품을 기준으로 집계 테이블을 만들어 볼 텐데요. 그 과정에서 챗GPT를 활용해 보겠습니다. 

가장 왼쪽 이미지가 가상의 판매 데이터입니다. 이 데이터는 챗GPT가 생성해 준 가상의 샘플 데이터인데요. 이렇게 샘플 데이터를 만들 때도 챗GPT를 이용하면 편리합니다. 

먼저 표의 A열에는 서울, 부산, 인천 등 [지역]명이 있고, 그 옆으로 [상품]과 [판매량], [가격]이 있습니다. 그리고 상품이 판매된 [요일]과 [시간대], [프로모션] 적용 여부까지 나와 있어요. 

가장 위쪽 이미지에는 "[판매금액] 열을 추가해 주세요"라는 설명과 관련 코드가 나와 있는데요. 현재 표에는 단가는 있지만, 판매금액이 없기 때문에 추가가 필요합니다. 그래서 최종적으로, 맨 아래 이미지처럼 '지역별 총 판매금액'과 '전체 대비 비율'이 요약된 데이터를 추출해 보려 해요. 

1) 쿼리 편집기로 데이터 불러오기 

먼저 쿼리 편집기로 데이터를 가져와 볼게요. [데이터] 탭에서 [데이터 가져오기]의 [기타 원본에서]를 누르고 [테이블/범위에서]를 선택하면 됩니다. [데이터 가져오기] 바로 옆에 위치한 표 모양의 아이콘이 '표 또는 범위'를 뜻해요. 

앞서 말씀드린 것처럼, 파워 쿼리는 표로 된 개체를 가져와야 합니다. [삽입] 탭의 [표]를 누르면 범위가 표로 변환되고, 그 증거가 [테이블 디자인]이라는 메뉴가 생기는 것입니다. 

표 이름은 "판매 내역"이라고 바꿔줬습니다. 이렇게 표로 만드는 것이 1단계예요. 

2) 고급 편집기 코드 복사하기 

이제 [데이터] 탭에서 [테이블/범위] 아이콘을 선택해 주면 바로 데이터를 불러올 수 있습니다. 쿼리 편집기에는 기본적으로 "원본"과 "변경된 유형"이 나오는데요. 

'원본'은 최초에 불러온 명령 자체를 뜻하고 '변경된 유형'은 자동으로 열을 탐색해 텍스트, 정수 등 적절한 형식으로 변환한 내용입니다. 이게 기본이에요. 

여기서 [고급 편집기]를 누르면, 현재까지 적용된 원본과 변경된 유형, 두 단계의 코드를 볼 수 있습니다. 이 코드를 복사해 두고, 챗GPT로 넘어가 볼게요.

3) 챗GPT 활용해 테이블 완성하기 

가장 먼저 챗GPT에게 구체적인 역할을 부여해야 합니다. 우리는 파워 쿼리에 관해 도움을 받아야 하니까 엑셀 전문가 역할을 해달라고 요청할게요. 

프롬프트를 적을 때는 하나의 프롬프트에 하나의 명령만 입력해야 정확한 답변을 얻을 수 있습니다. 오류가 발생해도 수정하기 쉽고요. 첫 번째 명령은 "아래와 같은 M 수식이 있습니다. [판매금액]열을 추가해 주세요"입니다. 챗GPT가 제공한 수식을 바탕으로 아래와 같이 답변해 줬어요. 

답변 오른쪽 위에 [copy code]를 클릭한 후, 다시 파워 쿼리 편집기로 돌아옵니다. 복사한 코드를 편집기에 붙여 넣으면 챗GPT가 만든 코드로 변환된 것을 볼 수 있어요. 

여기서 "#판매금액 추가"라는 새 라인은 이전 단계인 '변경된 유형' 단계에 "판매금액"이라는 열을 추가한다는 뜻이고, 판매금액의 각 행은 "판매량과 가격을 곱한 것"이라는 코드입니다. 완료를 누르면, 아래와 같이 기존에는 없었던 "판매금액"열이 등장한 것을 볼 수 있어요. 

다시 챗GPT로 돌아가 볼게요. 이번에는 "지역별로 판매금액을 집계해서 보고 싶습니다. 코드를 수정해 주세요"라고 요청해 볼게요. 현재 [지역]열을 보면, 같은 지역이 여러 번 나오기 때문에 지역끼리 합쳐서 판매금액을 집계해 보려는 거예요. 

이번에는 코드도 추가하지 않고 바로 질문했는데, '그룹 바이 작업'으로 알아서 코드를 만들어줬어요. 이번에도 코드를 복사해, [고급 편집기]를 눌러 기존 내용을 지우고 챗GPT에서 복사한 코드를 붙여 넣습니다. 

"#지역별 집계"라는 새 라인이 추가됐네요. 이렇게 코드를 붙여 넣은 후, 완료를 누르면 아래와 같이 지역별 총판매금액이 집계된 것을 볼 수 있습니다. 테이블을 그룹화한 것인데, 엑셀에서 피벗 테이블을 실행한 것과 동일합니다. 

이번에는 챗GPT에게 "전체 판매금액 대비 지역별로 몇 %인지 추가해 주세요"라고 요청해 볼게요. 만약 서울이 200, 전체 1000이면 서울은 20%가 되겠죠? 이런 식으로 합계 비율을 보려는 건데요. 각 금액의 상대적인 크기를 같이 보기 위한 것입니다. 

마찬가지로 챗GPT가 코드를 만들어줬고, 바로 복사해 가져갈게요. [고급 편집기]에 들어가서 복사한 코드를 붙여 넣으면 끝이죠? 그런데 이번에는 오류가 생겼습니다. 

무슨 오류인지 해석할 수 있다면 좋겠지만, 잘 모를 때도 오류 메시지를 그대로 복사해 챗GPT에게 물어보면 됩니다.

답변을 읽어보니, 챗GPT가 '전체 대비 비율'이라는 열을 추가하고 퍼센티지로 변환하는 코드를 추가했는데, 파워 쿼리에 퍼센티지라는 데이터 타입이 없어 오류가 발생했다는 뜻이네요. 챗GPT가 수정한 코드를 복사해 마찬가지로 [고급 편집기]에 붙여 넣을게요. 수정된 코드를 실행했더니 오류가 수정되고 아래와 같이 결과가 나왔어요.

지역별 판매금액 비율이 소수점 형태로 나왔네요. 챗GPT의 답변을 확인해 보기 위해 "전체 대비 비율"을 선택하고 [변환] 탭에 [데이터 형식]을 눌러봤더니, [백분율]이 존재합니다. 챗GPT에게 다시 물어봐야겠어요. 

이번엔 영어로 답을 하네요. 미안하다며 코드를 바꿔준다고 합니다. 새로운 코드를 복사해 엑셀로 돌아와, 다시 [고급 편집기]에 붙여 넣습니다. 

퍼센티지 타입 앞에 "x100"이 추가된 것을 확인할 수 있는데요. 확인을 눌러 봤더니 역시, 아래와 같이 올바르지 않은 답이 나왔어요. 

전체 대비 비율이 2400%라고 표시되는데요. 계산 방식이 틀린 것 같죠? 챗GPT에게 100을 곱하면 안 될 것 같다고 이야기해 볼게요. 

그랬더니 챗GPT가 새로운 코드를 완성해 줬습니다. 참 신기하죠? 코드의 몇 번째 라인에 관한 건지, 어떤 부분에 대한 이야기인지 설명하지 않고, 마치 사람과 대화하듯이 말해도 알아서 자동으로 코드를 수정해 줬습니다. 챗GPT의 편리한 점이죠. 

수정 코드를 복사해 다시 [고급 편집기]에 붙여 넣었더니, 코드에서 곱하기 100이 사라졌네요. [완료]를 눌러볼까요? 

이제 [전체 대비 비율]이 정확하게 표시됩니다. 마지막으로 메뉴의 [답기 및 로드]를 누르면, 아래와 같이 표가 완성됩니다. 숫자 형식의 표시는 [홈] 탭의 "%"로 표시 형식을 바꿔주면 됩니다. 

실제 예시 데이터를 바탕으로 파워 쿼리에 관해 알아봤습니다. 실무에서 필요한 순간에 꼭 활용해 보시기 바랄게요!