엑셀 피벗테이블 자동화 | 여러 시트 통합 보고서 만들기
엑셀 여러 시트 피벗테이블 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] 엑셀 여러 시트 취합 피벗테이블 만들기예제파일
여러 시트 데이터를 하나로 합치기
회사에서 매월, 또는 매주 반복되는 보고서를 작성하다보면 여러 시트에 흩어진 데이터를 하나로 합치기 위해 끝없이 복사/붙여넣기하는 경우가 많습니다. 자칫 잘못된 범위를 선택하거나 붙여넣기하면, 모든 데이터를 다시 검토해야 하는 아찔한 상황도 종종 발생하는데요.
오늘은 이 모든 과정을 '파워쿼리 함수 하나만 사용'해서, 버튼 클릭 한 번으로 편리하게 자동화하는 방법을 알아보겠습니다.
오빠두Tip : 이번 실습은 엑셀 2019 이후 버전을 기준으로 진행합니다. 엑셀 2016 버전 사용자는 중간에 안내 사항을 참고해서 실습을 진행해주세요!😊- 먼저 파워쿼리로 현재 통합문서의 데이터를 취합하려면 취합할 데이터 범위를 '표'로 변환해야 합니다. 예제파일을 실행하면 '강남역' 부터 '판교점' 시트의 데이터는 표로 작성되어있으며, '홍대입구점' 시트의 데이터는 일반 범위로 작성되어 있습니다. 이 범위를 표로 변환하겠습니다.
홍대입구점 시트의 데이터 범위를 표로 변환합니다. - 홍대입구점의 데이터 범위를 선택한 후, [삽입] - [표] 를 클릭해 표 만들기를 실행합니다. 표 만들기 대화상자가 실행되면 '머리글 포함'을 체크하고 [확인] 버튼을 클릭해 범위를 표로 변환합니다.
범위를 표로 변환합니다. - 변환한 표를 선택하고 [테이블 디자인] 탭에서 왼쪽 영역에 있는 표 이름을 "홍대입구점"으로 변경합니다.
표 이름을 홍대입구점으로 변경합니다. - 이제 파워쿼리 편집기로 현재 통합문서의 모든 시트 데이터를 하나로 취합해보겠습니다. [데이터] 탭 - [데이터 가져오기] - [기타 원본에서] - [빈 쿼리]를 차례대로 클릭해 비어있는 쿼리를 생성합니다.
비어있는 쿼리를 생성합니다. - 파워쿼리 편집기가 실행되면 수식입력줄에 다음과 같이 함수를 작성합니다. 함수를 실행하면 그림과 같이 현재 통합문서의 모든 표 데이터가 출력됩니다.
= Excel.CurrentWorkbook()
현재 통합문서의 표를 출력하는 함수를 입력합니다. 오빠두Tip : 만약 수식입력줄이 보이지 않는다면, [보기] 탭에서 수식입력줄을 체크합니다. - 'Content' 필드의 확장 버튼을 클릭하고 '원래 열 이름을 접두사로 사용' 옵션은 체크 해제 후 [확인] 버튼을 클릭해 데이터를 펼칩니다.
Content 필드를 확장합니다. - 확장된 데이터에는 '지점명'이 이미 포함되어 있어 시트이름과 중복됩니다. 따라서 시트 이름인 'Name' 필드는 우클릭 - [제거]를 클릭해 필드를 삭제합니다.
불필요한 필드는 우클릭 - 제거를 클릭해 삭제합니다. - 데이터 취합이 완료되었으면 [파일] 탭 - [닫기 및 다음으로 로드]를 클릭한 후, [데이터 가져오기] 대화상자가 실행되면 '연결만 만들기'를 선택하고 [확인] 버튼을 클릭합니다.
파일 - 닫기 및 다음으로 로드 - 연결만 만들기로 쿼리를 저장합니다. - 그러면 우측에 '쿼리 및 연결' 패널이 표시되면서 완성된 쿼리가 목록에 표시됩니다.
쿼리 및 연결 패널에 완성된 쿼리가 표시됩니다.
취합한 쿼리로 피벗테이블 만들기
- 이전 단계에서 완성한 쿼리를 우클릭 - [다음으로 로드]를 클릭합니다.
쿼리를 우클릭 - 다음으로 로드를 선택합니다. - 이후 데이터 출력 방식은 '피벗테이블 보고서', 출력할 위치는 '새 워크시트'를 선택한 후 [확인] 버튼을 클릭해 피벗테이블을 생성합니다.
새 워크시트에 피벗테이블 형식으로 쿼리를 출력합니다. 오빠두Tip : 🚨 엑셀 2016 버전에서는 '피벗테이블 보고서' 출력이 제공되지 않습니다.
2016 버전 사용자는 '표' 형식으로 새 워크시트에 데이터를 출력한 후, 표에서 피벗테이블을 생성합니다.
이후 본 게시글의 마지막 섹션인 '취합할 데이터 필터링하기'를 참고해 파일을 취합한 최종 쿼리는 취합 데이터에서 제외되도록 필터를 적용하세요! - 이제 피벗테이블의 각 영역으로 필드를 적절히 배치하면 여러 시트 데이터가 취합된 피벗테이블 보고서가 완성됩니다.
필드를 적절히 배치해 피벗테이블을 완성합니다. - 이렇게 만든 피벗테이블은 통합문서에 새롭게 추가된 시트의 데이터를 실시간으로 반영합니다. 예제파일 중 '신규 데이터' 파일에서 [잠실새내점]과 [수원정자정] 시트를 예제파일로 옮깁니다.
신규데이터 파일의 다른 지점 시트를 새롭게 추가합니다. - 이후 피벗테이블을 우클릭 - [새로고침] 을 클릭해 갱신하면 새 시트의 데이터가 피벗테이블에 실시간으로 반영됩니다.
피벗테이블을 새로고침하면 새 시트의 데이터가 업데이트 됩니다.
쿼리 병합으로 VLOOKUP 자동화 하기
이제 여기서 한 걸음 더 나아가 VLOOKUP 까지 자동화해보습니다. 현재 보고서에는 '제품코드(P1001, P1002, ...)'만 있어 이 코드가 어떤 제품인지 확인하려면 제품 목록을 찾아봐야하는 번거로움이 있습니다. 보통 이러한 경우엔 VLOOKUP 함수를 사용해 원본 데이터에 보조열을 추가하는 방법으로 해결하지만, 새로운 데이터가 추가될 때마다 수식을 다시 적용해야하는 한계에 부딪히곤 합니다. 그럴 때, 파워쿼리의 '쿼리 병합' 기능을 사용하면 VLOOKUP 함수 없이 두 개의 표가 연결된 자동화 보고서를 만들 수 있습니다.
- '신규데이터' 통합문서의 [제품목록] 시트를 예제파일로 옮긴 후, [제품목록] 표를 선택하고 [데이터] 탭 - [테이블/범위에서]를 클릭해 파워쿼리 편집기를 실행합니다.
제품목록 표를 선택하고 [테이블/범위에서]를 클릭해 파워쿼리 편집기를 실행합니다. - 이번에는 데이터를 취합한 쿼리('쿼리1')과 제품목록 쿼리('제품목록')을 '제품코드' 를 기준으로 연결합니다. 먼저 데이터를 출력할 기준 쿼리인 '쿼리1'을 선택하고, [홈] 탭 - [쿼리 병합]을 클릭합니다.
기준 쿼리를 선택 - 홈 탭 - 쿼리 병합을 실행합니다. - 병합 대화상자가 실행되면 병합할 두번째 쿼리로 '제품목록'을 선택한 후, 병합할 기준 값인 [제품 코드] 필드를 '쿼리1'과 '제품목록' 쿼리에서 각각 선택합니다. 그러면 대화상자 아래에 '203개 데이터가 모두 일치합니다'라는 안내가 표시됩니다. 모두 확인했으면 [확인] 버튼을 클릭해 쿼리 병합을 실행합니다.
두번째 쿼리로 제품목록을 선택한 후, 제품 코드를 각각 선택해 연결합니다. - 이제 병합된 [제품목록] 필드를 확장합니다. 확장버튼을 클릭한 후 필드 목록에서 '제품명'과 '제품카테고리'를 선택하고 '원래 열 이름을 접두사로 사용'은 체크 해제 후 [확인] 버튼을 클릭합니다.
병합된 필드에서 출력할 필드를 선택해 확장합니다. - 그러면 아래 그림과 같이 각 제품 코드와 매칭되는 제품명과 제품 카테고리가 출력됩니다.
제품코드와 매칭되는 제품정보가 출력됩니다.
전문가TIP: 취합할 데이터 필터링하기
이제 마지막 단계로 취합 쿼리에서 불필요한 데이터를 제외합니다. 이전 단계에서 추가한 '제품목록' 표는 취합할 데이터인 '지점별 판매 데이터'와 구조가 달라 취합 결과에서는 제외하는 단계를 추가하겠습니다.
- 파워쿼리 편집기에서 '쿼리1'을 선택한 후, 좌측 설정의 적용된 단계 중 가장 첫번째 단계인 [원본] 단계를 선택합니다.
취합 쿼리의 첫번째 단계인 원본을 선택합니다. 오빠두Tip : 만약 파워쿼리 편집기 좌측에 '쿼리 설정'이 안 보인다면, [보기] 탭 - [쿼리 설정]을 클릭합니다. - 이후 [홈] 탭에서 [미리보기 새로고침]을 클릭하면 현재 통합문서에 새롭게 추가한 시트가 반영된 목록으로 업데이트됩니다.
미리보기 새로고침을 클릭하면 쿼리 미리보기가 갱신됩니다. - 이제 [Name] 필드의 필터를 클릭한 후, 목록에서 '제품목록'을 체크 해제합니다. 이후 쿼리의 마지막 단계를 클릭하면, [제품목록] 시트가 제외되고 지점별 판매 데이터만 올바르게 취합된 것을 확인할 수 있습니다.
Name 필드 목록에서 제외할 데이터를 체크 해제합니다. - 마지막으로 [파일] 탭 - [닫기 및 다음으로 로드]를 클릭하고, '연결만 만들기'로 쿼리를 저장하면 여러 시트 데이터 취합과 VLOOKUP 이 적용된 자동화 쿼리가 완성됩니다.
파일 - 닫기 및 다음으로 로드 - 연결만 만들기로 쿼리를 저장합니다. - 이제 피벗테이블의 필드 목록에서 각 영역에 필드를 적절히 배치하면 여러 시트가 취합된 피벗테이블 보고서가 완성됩니다.
여러 시트 데이터가 취합된 피벗테이블 보고서가 완성됩니다.
- 먼저 파워쿼리로 현재 통합문서의 데이터를 취합하려면 취합할 데이터 범위를 '표'로 변환해야 합니다. 예제파일을 실행하면 '강남역' 부터 '판교점' 시트의 데이터는 표로 작성되어있으며, '홍대입구점' 시트의 데이터는 일반 범위로 작성되어 있습니다. 이 범위를 표로 변환하겠습니다.
