[일머리 지식] 엑셀 피벗테이블, 동적 범위 설정, 자동 업데이트

사무직 일을 하시는 분들이 업무에서 가장 많이 활용하는 오피스 프로그램이 엑셀이다. 하루 일과 중에 엑셀프로그램을 한 번도 열지 않고 일하기는 쉽지 않을 정도다. 엑셀은 문서작성부터 수식 계산, 표 및 차트, 데이터 정리 및 분석 등 다양하게 활용할 수 있기 때문이다. 

엑셀에서 가장 많이 활용하는 것이 수식 계산 즉 함수로 개수, 평균, 합계를 구하는 것이고  그리고 표와 차트, 그리고 엑셀에 내장되어 있는 기능인 피벗테이블이다. 

내가 처음 엑셀을 접했을 때 와!! 하고 느꼈든 것이 VLOOKUP 함수와  피벗테이블이었다. Vlook Up함수는 2개 이상의 테이블을 참조하여 데이터를 쉽게 찾도록 해주고 , 피벗테이블은 가로와 세로 형태로 데이터를 요약하여 보여줘서 데이터를 쉽게 해석할 수 있도록 한다.  VLOOKUP 함수와  피벗테이블은 엑셀 사용자들이 가장 많이 사용하는 함수와 기능일 것이다. 그중 피벗테이블에 대해 알아보자

엑셀 피벗테이블 이해.pdf

0.83MB

피벗테이블에 관련한 기초적인 내용은 위의 첨부를 참조하면 되고 오늘은 피벗테이블 동적범위 설정과 데이터 업데이터에 대해 알아보자. (첨부자료: 엑셀 기초교육 자료) 

---

▣ 피벗 테이블 데이터 원본 동적 범위 설정

1. 데이터원본 동적범위 설정 이유
    피벗테이블의 데이터원본이 변경(추가-삭제) 되었을 때 데이터원본의 범위를 매번 변경하는 번거로움을
    없애기 위해 사용함.

2. 방법
   가. 데이터원본을 처음부터 표 개체로 변경하고 피벗테이블의 원본을 표로 설정하는 방법(첨부 참고)
   나. OFFSET 함수를 활용하여 동적범위 설정
        1) 수식 → 이름관리자(이름정의) 클릭 → 이름 입력(1번)
        2) 참조 범위를 Offset 함수를 사용하여 동적범위 설정(2번)
            2-1) 함수 코드: Offset(시작셀(참조), 행(0 입력), 열(0 입력), 높이(행의 수), 넓이(열의 수))
            2-2) 함수 해석: 시작셀 A1셀을 기준으로(행과 열을 모두 0으로 입력) 높이와 넓이 크기 지정

    다. 피벗테이블 삽입 → 표/범위에 이름(데이터원본) 입력

     라. 데이터 추가 ㅍ 피벗테이블 우 클릭 → 새로고침 클릭 → 피벗테이블 업데이트

▣  피벗 테이터 원본 자동 새로고침

VBA 코드로 쉽게 만들 수 있고 VBA 코드는 아래와 같다. 워크시트가 바뀌면 Sheet2의 피벗테이블을 수정해라는 간단한 코드이니 업무에 활용하면 좋을 것 같다. 
※ 동적범위 설정 후 새로고침만 하면 되는데 VBA 코드까지 만들 필요가 있는지는 개인이 판단하시면 된다