오늘은 엑셀에서 Offset함수와 Name 이름지정하기를 활용해서, 자동으로 참조 범위가 업데이트되는 피벗테이블을 만들어보겠습니다. 회사 ERP 전산에서 받은 원본 데이터를 한 탭에 두고, 그것을 바탕으로 피벗 테이블을 만들어 놓고 업데이트 하시는 분들은 이 방법을 익혀 놓으면 업무에 필요한 시간을 상당히 단축시킬 수 있습니다. 아래 설명 천천히 따라와 주시고, 포스팅 아래에 있는 예제 파일 다운로드 받아서 한 번 연습해 보시길 바랍니다.
자동 피벗테이블의 의미
여기서 말하는 자동 피벗 테이블은 원본 데이터를 바탕으로 우리가 원하는 형식의 피벗 테이블을 만들어 놓았다면, 참조하는 원본 데이터가 바뀌었을 때 (보통은 행들이 추가), 피벗 테이블의 Refresh 새로고침을 눌렀을 때 자동으로 피벗 테이블의 참조 범위가 업데이트되는 것을 의미합니다. 이것만 해도 상당한 업무 시간을 줄일 수 있는 게… 보통은, 아래 스크릿 샷에 보시는 것 처럼, 피벗 테이블의 Change data source 원본 데이터 변경으로 들어가서, 매번 수동으로 참조 범위를 바꿔줘야 하거든요. 오늘 이 방법을 마스터 하시면, 원본 데이터가 바뀌더라도, 수동으로 피벗 테이블의 참조 범위를 업데이트 할 필요 없게 됩니다.
자동 피벗 테이블 예제 파일 소개
물론 실무에서 마주하는 원본 데이터는 훨씬 크고 복잡하겠지만, 예제 파일에서는 아주 간단한 데이터 셋을 바탕으로 피벗 테이블을 만들어 보았습니다. 사실 원리만 이해하면, 아무리 원본 데이터가 커도 적용하는 방법은 동일하기 때문에, 설명은 간단한 데이터 셋으로 하는 게 더 이해하기 쉬우실 거예요.
예제 파일을 열어 보시면, 아래와 같이, 보기 쉽도록 같은 시트에 E1:G8 를 가지고 A1:B11 에 피벗 테이블을 만들어 보았습니다.
예제 파일에 입력된 Offset함수 설명
예제파일 J1 셀에는 아래와 같은 Offset 함수를 입력 해서, E1:G8 데이터의 데이터가 추가되면 (주로 행 방향으로 추가) 자동으로 참조 범위도 업데이트 되게 하였습니다.
=OFFSET($E$1,0,0,COUNTA($E:$E),3)
위에 입력된 수식을 설명해 드리자면, E1 (절대 참조 – 원본 데이터의 시작점) 에서 시작해서, 밑으로 0칸, 오른쪽으로 0칸 이동한 셀 – 결국 E1 셀 – 에서, E열 전체 중 값이 있는 갯수 만큼 범위의 행을 참조 (counta 현재는 8개의 행이지만 E열 값이 하나 추가되면 Counta 의 값이 9가 되면서 참조되는 행의 갯수도 계속 늘어나게 됩니다), 그리고 마지막으로 E1 셀 기준으로 오른쪽으로 3개의 열 – 원본 데이터가 E, F, G 이렇게 세 개의 열에 있으므로 – 을 참조하라는 명령입니다.
예전 버전의 엑셀에서는 이런 식으로 참조 범위를 결과로 나타내는 함수를 시트에 직접 써놓으면 연산을 하지 못했는데, 요즘 엑셀에서는 아래 노란색으로 하이라이트 된 부분 처럼 참조되는 범위를 시트에 그대로 표시해주니 좋네요.
위에 사용한 Offset 함수의 수식이 좀 이해가 되셨을까요? 이 수식을 그래도 Name 이름으로 지정해서 피벗 테이블 참조 범위를 지정해주는 게 오늘 포스팅의 핵심입니다. 일단 위에 수식을 이해해 보시고, 나중에 익숙해지면 이렇게 따로 시트에 적는 게 아니라, Name 이름 지정할 때 바로 적어 주셔도 무방합니다.
참고로, Offset 함수 자체에 대한 자세한 설명과 예제는 아래 마이크로소프트 지원 사이트 링크를 참고하셔도 좋습니다.
Offset함수 수식을 Name 이름으로 지정
위에 Offset 함수 수식이 이해되셨고, 입력이 잘 된 것 같으면, 수식을 복사해 주세요. 그리고 Formulas 수식 탭에 있는 Name Manager 이름 관리자 를 선택해주세요. 그리고 아래와 같이 refers to 참조 범위를 입력하는 공간에 복사한 수식을 붙여 넣으면, 요 수식이 새로운 이름으로 지정된 것입니다. 저는 새로 지정된 이름에 Data 라는 이름을 부여했습니다.
피벗 테이블 Data Source 에 지정된 이름을 입력
이렇게 offset 함수의 결과값으로 나오는 참조 범위를 이름으로 지정한 후, 이제는 피벗 테이블을 선택합니다. 그러면 엑셀 리본 탭이 피벗 테이블에 맞게 추가가 되는 데요. 그 중 Pivot table analyze 피벗 테이블 분석 – Change Data Source 데이터 소스 변경 을 클릭 해 주세요.
그 다음, 아래와 같이 뜨는 새 창에 Select a table or range 를 선택하시고 Table/ Range 입력하는 공간에 조금 전 지정해준 이름 Data 를 입력해주면, 자동 피벗테이블이 완성 되었습니다!
그럼 정말 참조 범위가 자동으로 업데이트 되는지, 데이터 두 개를 추가해서 테스트 해보겠습니다. 아래와 같이 원본 데이터에 두 개의 데이터를 더 넣었습니다.
AAA, 2011, 5000 / BBB, 2011, 5000
피벗 테이블을 선택하고 Pivottable Analyze 탭의 Refresh 버튼을 클릭 하니, 아래와 같이 자동으로 피벗 테이블이 추가된 두 데이터를 반영하여 결과를 보여주었습니다.
마무리 및 응용 +_ 예제 파일
간단한 데이터 셋으로 연습 해 보았지만, 원리와 방법만 알면 원본 데이터량이 얼마가 되던 쉽게 적용할 수 있을 겁니다. 대부분 회사 전산에서 받는 원본 데이터의 column 열 갯수가 일정하기 때문에, 예제 파일에 보는 것 처럼, 고정 숫자 (3) 로 입력해도 되지만, 열 갯수마저 계속 바뀐다면, Offset 함수의 마지막 Width 인수에도 counta 함수를 써주면 되겠죠.
또한, 피벗 테이블 뿐 아니라 피벗 차트도 함께 이쁘게 만들어 놓고, 원본 데이터에 따라 바로 바로 업데이트되게 만들어 놓으면, 반복 업무를 줄일 수 있을 뿐더러, 보고서 작성에 필요한 시간도 상당히 단축할 수 있습니다.
그럼, 다들 오늘 배운 내용 천천히 이해하시고, 퇴근 시간도 앞 당기고, 칭찬도 받는 슈퍼 직장인이 되시길 바랍니다! 화이팅!
엑셀 사용하면서 다른 쉽고 유용한 엑셀 팁들을 보고 싶으다면 아래 링크 클릭해주세요!