엑셀- 참조범위가 자동 업데이트되는 피벗테이블 만들기 (feat. Offset함수)

 

오늘은 아래 링크를 통해서 엑셀의 Offset 함수와 이름 정의 기능을 활용하여 원본 데이터가 변경될 때 자동으로 참조 범위가 업데이트되는 피벗 테이블을 만드는 방법을 소개하고자 합니다. 이 방법을 익히면, 원본 데이터가 변경될 때마다 매번 수동으로 피벗 테이블의 참조 범위를 업데이트할 필요 없이 자동으로 참조 범위가 조정됩니다.

 

아래의 엑셀 전문 블로거는 먼저 Offset 함수의 역할과 사용법을 설명합니다. Offset 함수는 시작 셀에서 일정한 행과 열만큼 떨어진 위치의 범위를 지정해주는 함수로, 원본 데이터가 추가될 때마다 자동으로 참조 범위를 업데이트할 수 있습니다. 예를 들어, =OFFSET($E$1,0,0,COUNTA($E:$E),3)라는 수식은 E1 셀을 기준으로 E 열의 데이터 개수만큼 행을 참조하고, 3개의 열을 포함하는 범위를 지정합니다. 이 수식을 통해 원본 데이터가 추가되면 참조 범위도 자동으로 늘어납니다.

다음으로, 이 Offset 함수를 이름으로 정의하는 과정을 설명합니다. 수식 탭의 이름 관리자(Name Manager)에서 새로운 이름을 정의하고, 참조 범위에 Offset 수식을 입력합니다. 

 

피벗 테이블의 데이터 소스를 자동으로 업데이트되도록 설정하려면, 피벗 테이블을 선택한 후 피벗 테이블 분석(PivotTable Analyze) 탭에서 데이터 소스 변경(Change Data Source)을 클릭합니다. 새 창에서 테이블 또는 범위 선택(Table/Range)에 'Data'를 입력하면 됩니다. 이렇게 하면 원본 데이터가 변경될 때마다 피벗 테이블도 자동으로 업데이트됩니다.

 

마지막으로, 그는 예제 파일을 통해 이를 실습해볼 것을 권장합니다. 예제 파일에는 간단한 데이터 셋이 포함되어 있으며, 이를 바탕으로 피벗 테이블을 만들어 원본 데이터가 추가될 때 참조 범위가 자동으로 업데이트되는 것을 확인할 수 있습니다. 이 방법을 익히면, 원본 데이터의 크기와 복잡성에 관계없이 쉽게 적용할 수 있으며, 반복적인 작업을 줄여 업무 효율성을 크게 높일 수 있습니다. 피벗 테이블뿐만 아니라 피벗 차트도 함께 만들어 원본 데이터에 따라 자동으로 업데이트되도록 설정하면 보고서 작성에 필요한 시간도 상당히 줄이는 효과가 있습니다. 

 

보다 자세한 내용과 예제 파일로 연습해 보고 싶으신 분은 아래 링크 클릭해주세요. 

 

https://keepthefaith.co.kr/피벗테이블

엑셀 (15) 자동 피벗테이블 만들기 - Offset함수 활용 - Kai's Smart Workplace