엑셀 피벗테이블 자동화 완벽 가이드 | 여러 시트 통합 보고서 만들기

여러 시트 데이터를 하나로 합치기

매월 또는 매주 반복되는 보고서를 작성하다 보면 여러 시트에 흩어진 데이터를 하나로 합치기 위해 끝없이 복사·붙여넣기를 반복하는 경우가 많습니다. 잘못된 범위를 선택하거나 붙여넣기를 잘못 적용하면, 전체 데이터를 처음부터 다시 검토해야 하는 번거로운 상황이 자주 발생합니다.

이번 강의에서는 이 모든 과정을 '파워쿼리 함수 하나만 사용'해서 버튼 클릭 한 번으로 자동화하는 방법을 알아보겠습니다.

1. 먼저 파워쿼리로 현재 통합문서의 데이터를 취합하려면 취합할 데이터 범위를 '표'로 변환해야 합니다. 예제파일을 열면 [강남역]부터 [판교점] 시트의 데이터는 이미 표로 작성되어 있고, [홍대입구점] 시트의 데이터만 일반 범위로 작성되어 있습니다. 이 일반 범위를 표로 변환해보겠습니다.

   

2. [홍대입구점] 시트의 데이터 범위를 선택한 후, [삽입] - [표]를 클릭해 표 만들기를 실행합니다. 표 만들기 대화상자가 열리면 '머리글 포함'에 체크하고 [확인] 버튼을 클릭해 일반 범위를 표로 변환합니다.

   

3. 변환한 표를 선택한 후 [테이블 디자인] 탭의 좌측 영역에서 표 이름을 "홍대입구점"으로 변경합니다.

   

4. 이제 파워쿼리 편집기를 열어 통합문서의 모든 시트 데이터를 하나로 취합해보겠습니다. [데이터] 탭 - [데이터 가져오기] - [기타 원본에서] - [빈 쿼리]를 차례로 클릭해 비어 있는 쿼리를 생성합니다.

   

5. 파워쿼리 편집기가 열리면 수식입력줄에 아래와 같이 함수를 작성합니다. 함수를 실행하면 그림과 같이 현재 통합문서의 모든 표 데이터가 출력됩니다.
   = Excel.CurrentWorkbook()

   

   오빠두Tip : 수식입력줄이 보이지 않는다면, [보기] 탭에서 [수식 입력줄]에 체크합니다.
6. [Content] 필드의 확장 버튼을 클릭한 후, '원래 열 이름을 접두사로 사용' 옵션의 체크를 해제하고 [확인] 버튼을 클릭해 데이터를 펼칩니다.

   

7. 확장된 데이터에는 '지점명' 필드가 이미 포함되어 있어 시트 이름과 중복됩니다. 따라서 시트 이름이 입력된 [Name] 필드는 우클릭 - [제거]를 클릭해 삭제합니다.

   

8. 데이터 취합이 완료되면 [파일] 탭 - [닫기 및 다음으로 로드]를 클릭합니다. [데이터 가져오기] 대화상자가 열리면 '연결만 만들기'를 선택한 후 [확인] 버튼을 클릭합니다.

   

9. 그러면 우측에 [쿼리 및 연결] 패널이 표시되며 방금 만든 쿼리가 목록에 추가됩니다.

   

취합한 쿼리로 피벗테이블 만들기

1. 이전 단계에서 완성한 쿼리를 우클릭 - [다음으로 로드]를 클릭합니다.

   

2. 데이터 출력 방식은 '피벗테이블 보고서', 출력 위치는 '새 워크시트'를 선택한 후 [확인] 버튼을 클릭해 피벗테이블을 생성합니다.

   

   오빠두Tip : 🚨 엑셀 2016 버전에서는 '피벗테이블 보고서' 출력이 제공되지 않습니다.
   2016 버전 사용자는 '표' 형식으로 새 워크시트에 데이터를 출력한 후, 표에서 피벗테이블을 생성합니다.
   이후 본 게시글의 마지막 섹션 '취합할 데이터 필터링하기'를 참고해, 파일을 취합한 최종 쿼리는 취합 데이터에서 제외되도록 필터를 적용해주세요!
3. 이제 피벗테이블의 각 영역에 필드를 적절히 배치하면 여러 시트 데이터가 취합된 피벗테이블 보고서가 완성됩니다.

   

4. 이렇게 만든 피벗테이블은 통합문서에 새롭게 추가되는 시트의 데이터까지 실시간으로 반영합니다. 예제파일 중 [신규 데이터] 통합문서에서 [잠실새내점]과 [수원정자점] 시트를 예제파일로 옮깁니다.

   

5. 이후 피벗테이블을 우클릭 - [새로고침]을 클릭해 갱신하면 새 시트의 데이터가 피벗테이블에 실시간으로 반영됩니다.

   

쿼리 병합으로 VLOOKUP 자동화 하기

이번에는 한 걸음 더 나아가 VLOOKUP 까지 자동화해보겠습니다. 현재 보고서에는 '제품 코드(P1001, P1002, …)'만 표시되어 있어, 코드가 어떤 제품을 의미하는지 확인하려면 별도의 제품 목록을 찾아봐야 하는 번거로움이 있습니다. 일반적으로 이런 상황에서는 VLOOKUP 함수로 원본 데이터에 보조열을 추가해 해결하지만, 새 데이터가 추가될 때마다 수식을 다시 적용해야 하는 한계가 있습니다. 이때 파워쿼리의 '쿼리 병합' 기능을 사용하면 VLOOKUP 함수 없이도 두 표가 연결된 자동화 보고서를 만들 수 있습니다.

1. [신규데이터] 통합문서의 [제품목록] 시트를 예제파일로 옮긴 후, [제품목록] 표를 선택하고 [데이터] 탭 - [테이블/범위에서]를 클릭해 파워쿼리 편집기를 실행합니다.
   
2. 이번에는 데이터를 취합한 쿼리('쿼리1')와 제품목록 쿼리('제품목록')를 '제품 코드'를 기준으로 연결합니다. 먼저 데이터를 출력할 기준 쿼리인 '쿼리1'을 선택한 후, [홈] 탭 - [쿼리 병합]을 클릭합니다.

   

3. 병합 대화상자가 열리면 병합할 두 번째 쿼리로 '제품목록'을 선택한 후, 병합 기준 값인 [제품 코드] 필드를 '쿼리1'과 '제품목록' 쿼리에서 각각 선택합니다. 그러면 대화상자 아래에 '203개 데이터가 모두 일치합니다'라는 안내가 표시됩니다. 모두 확인했으면 [확인] 버튼을 클릭해 쿼리 병합을 실행합니다.

   

4. 이제 병합된 [제품목록] 필드를 확장합니다. 확장 버튼을 클릭한 뒤 필드 목록에서 '제품명'과 '제품 카테고리'를 선택하고, '원래 열 이름을 접두사로 사용'의 체크를 해제한 후 [확인] 버튼을 클릭합니다.

   

5. 그러면 아래 그림과 같이 각 제품 코드와 매칭되는 제품명 및 제품 카테고리가 출력됩니다.

   

전문가TIP: 취합할 데이터 필터링하기

마지막 단계로 취합 쿼리에서 불필요한 데이터를 제외합니다. 이전 단계에서 추가한 '제품목록' 표는 취합 대상인 '지점별 판매 데이터'와 구조가 달라, 취합 결과에서는 제외되도록 필터링 단계를 추가하겠습니다.

1. 파워쿼리 편집기에서 '쿼리1'을 선택한 후, 좌측 [쿼리 설정]의 [적용된 단계] 중 가장 첫 번째인 [원본] 단계를 선택합니다.

   

   오빠두Tip : 파워쿼리 편집기 좌측에 [쿼리 설정] 패널이 보이지 않는다면, [보기] 탭 - [쿼리 설정]을 클릭해 표시합니다.
2. 이후 [홈] 탭에서 [미리보기 새로고침]을 클릭하면 현재 통합문서에 새롭게 추가된 시트가 반영된 목록으로 업데이트됩니다.

   

3. 이제 [Name] 필드의 필터를 클릭한 후, 목록에서 '제품목록'의 체크를 해제합니다. 이후 쿼리의 마지막 단계를 클릭하면 [제품목록] 시트가 제외되고 지점별 판매 데이터만 올바르게 취합된 것을 확인할 수 있습니다.

   

4. 마지막으로 [파일] 탭 - [닫기 및 다음으로 로드]를 클릭하고, '연결만 만들기'로 쿼리를 저장하면 여러 시트 데이터 취합과 VLOOKUP 까지 적용된 자동화 쿼리가 완성됩니다.

   

5. 이제 피벗테이블의 필드 목록에서 각 영역에 필드를 적절히 배치하면 여러 시트가 취합된 자동화 피벗테이블 보고서가 완성됩니다.