복수/다수의 피벗 테이블의 데이터 원본을 한번에 번경하기

피벗 테이블로 보고서를를 만들다 보면, 하나의 데이터 소스에서 여러 개의 피벗 보고서을 만드는 경우가 있습니다. 예를 들면, 월간 경영실적 데이터에서 제품군별 판매량 추이를 보거나, 지역별로 구분된 피봇 테이블을 만드는 경우에 이런 일이 생깁니다.

피벗 테이블의 원본 데이터 변경 방법을 검색해보면, 마이크로 소프트의 서포트 사이트나, 인터넷 블로그, Q&A들은 대부분 각 피벗 테이블을 선택 후 '피벗 테이블 데이터 원본 변경' 기능을 사용해서 변경하라고 안내하고 있습니다.

만약 피벗 보고서에 10개의 피벗 테이블이 있다면, 10번의 원본 변경 작업을 해야 하고, 20개면 20번 해야 하는, 단순 노가다가 발생합니다. 한 1년 이렇게 작업해 보니, 더는 그럴 수 없다는 생각이 들어 한번에 원본 데이터를 변경하는 방법을 찾아보았습니다.

1. 피벗 테이블 생성은 데이터 연결을 사용

대개는 데이터가 있는 시트에서 데이터 영역을 선택 후 피벗 테이블 만들기를 진행합니다. 이 방법으로는 복수의 피벗 테이블의 원본테이타를 한번에 바꿀 수 없으므로, 다른 방법으로 생성해야 합니다.

먼저, 원본 데이터 파일 'MasterData' 등으로 준비합니다. 하나의 시트이름을 'RawData'등으로 지정하고, 피벗 테이블 생성에 적합한 형식으로 구성해둡니다.

이제, 새 엑셀 파일을 열고, 리본 메뉴 중 '데이터' 하부의 '연결' 기능을 클릭해서 열어봅니다. 이름/설명/마지막 새로고침이 다 공백으로 되어 있을 것입니다. 우측의 '추가'버튼을 눌러 팝업되는 창('기존 연결')에서 왼쪽 아래에 있는 '더 찾아보기'버튼을 눌러, 위에서 준비한 'MasterData.xlsx'파일을 찾아 선택 후, 추가 팝업되는 창에서 'RawData' 시트를 지정합니다. 이 때, '이름'에 확장자를 제외한 파일명이 기본으로 등록되는데, 이러면 나중에 헷갈리므로, 우측의 '속성' 버튼을 눌러 이름을 편집하는 창을 다시 팝업시킵니다. 맨 위에 '연결 이름' 항목을 'PivotSource' 등으로 변경해둡니다.

 

2. 데이터 연결에서 피봇 테이블 생성하기

리본메뉴 '삽입' 하부의 '피벗 테이블'을 클릭합니다. '피벗 테이블 만들기'창이 팝업되면, '분석할 데이터를 선택하십시오' 부분에서 '외부 데이터 원본 사용'을 선택합니다. 그러면 그 밑의 '연결 선택' 버튼이 활성화 되며, 위에서 만들어 둔 'PivotSource' 연결을 선택하면 됩니다. 원래의 '피벗 테이블 만들기'창으로 돌아오면 '확인'을 눌러서 피벗 테이블을 생성합니다.

이 작업을 반복하면 점점 피벗 테이블이 많아지게 됩니다. 제 경우에는 매월 75개에서 100개 정도를 만드는 듯 합니다. 신나게 만들었는데, 원본 데이터 만드는 부서에서 오류가 있어 정정데이타를 보내오거나, 2월달 데이터로 만들었는데, 3월이 되면, 1,2번 작업을 반복해야 할까요? 아래 4번을 보시면 해결방법이 있습니다.

 

3. 새로운 원본 데이터를 준비하기

데이터는 변경되고 추가되는 것입니다. 1번에 만들었던 파일과 동일한 양식으로 새로운 데이터 파일을 준비합니다. 파일명은 다르게 합니다. 예를 들면 'MasterData2.xlsx'로 합니다.

 

4. 만들어 놓은 피봇 테이블들의 원본 데이터를 한번에 변경하기

리본 메뉴 '데이터'에서 '연결'을 눌러 팝업되는 창에서, 1번에서 만들어진 '연결'을 선택한 후, 우측의 '속성'버튼을 누릅니다. 맨 위에 '연결 이름'을 가지며, 그 하부에 '사용 현황', '정의' 탭을 갖고 있습니다. '정의'탭을 열어보면, 두번째 항목에 '연결 파일'이 정의되어 있고, 그 우측에 '찾아보기' 버튼이 있습니다! 그리고 아래쪽에 '명령 텍스트'에는 'RawData$'라는 시트 이름을 갖고 있습니다.

이제, 연결파일 우측의 '찾아보기'를 클릭하여, 3번에서 만든 새 데이터 파일('MasterData2.xlsx')을 선택합니다. '테이블 선택' 팝업창이 나오면, 'RawData$' 시트를 선택합니다. 그리고 돌아온 '연결 속성' 화면에서 '확인'을 누릅니다. 이제 엑셀은 열심히 데이터를 고치고 있습니다. 잠시후 사용자 입력을 다시 받게 되면, '통합 문서 연결'창 오른쪽 하단의 '닫기'를 누릅니다.

이제, 모든 피벗 테이블, 피벗 차트의 형식은 유지된 상태에서 원본 데이터가 새로운 파일의 값으로 변경되었습니다.

 

참 쉽죠?