피벗 테이블 새로 고침(1)

1. 피벗 테이블 만들기

아래와 같이 일자별 지점별로 데이터를 집계한다고 할 경우

피벗테이블 새로 고침.xlsx

0.01MB

 

삽입 - 피벗 테이블을 누르면 범위가 자동으로 A1부터 C6셀까지 지정되고,

배치할 위치가 새 워크시트로 지정되므로 확인 버튼을 누릅니다.

 

그러면, 피벗 테이블 필드 창이 배치되는데,

일자와 지점명을 누르면 모두 행 영역에 배치되는데 지점명을 끌어서 열 영역으로 옮기고, 매출 필드를 클릭하면 숫자이므로 자동으로 값 영역으로 배치되고 합계가 표시됩니다.

 

만들어진 피벗 테이블의 모양은 아래와 같습니다.

행 레이블에 날짜, 열 레이블에 지점명, 그리고 가운데 값 영역에 일자별, 지점별 매출이 표시되고 있고, 맨 오른쪽에는 날짜별 총합계가 표시되고,

맨 아래 줄에는 총합계로 지점별 합계와 총합계가 표시되고 있습니다.

 

2. 새로 고침

이때 6/1자 A지점의 매출액을 45,000이 아니라 50,000으로 수정해도

 

자동으로 피벗 테이블에서 6/1자 A지점의 매출액이 변하지 않습니다.

 

 

이 때는 피벗 테이블을 만들면 생기는 피벗 테이블 분석 탭에서 '새로 고침' 윗부분의 아이콘을 누르

 

비로소 45,000이 50,000으로 바뀝니다.

 

 

2. 피벗 테이블 자동 갱신 매크로

 

가. 매크로 기록

 개발도구 - 매크로 기록을 누르고, 매크로 기록 창이 나오면 확인 버튼을 누르고,

피벗 테이블 분석 탭의 새로 고침을 누른 다음 개발도구 - 기록 중지를 누릅니다.

 

그리고, 개발도구 - Visual Basic을 눌러 비주얼 베이식 에디터로 들어가서

모듈 1의 Module1을 더블 클릭하면

 

매크로1 서브 프로시저에 

ActiveSheet.PivotTables("피벗 테이블1").PivotCache.Refresh라는 구문이 표시됩니다.

 

ActiveSheet.PivotTables("피벗 테이블1").PivotCache.Refresh에서

ActiveSheet는 현재 시트이고,

PivotTables("피벗 테이블1")은 현재 작성된 피벗 테이블을 가리키는 것입니다.

 

엑셀 아이콘 모양의 '보기 Microsoft Excel(Alt+F11)'을 눌러 엑셀로 돌아가서 피벗 테이블 분석 탭을 클릭하면

피벗 테이블 이름이 '피벗 테이블1'로 표시되고 있습니다.

 

PivotCache.Refresh는 피벗 테이블 분석 탭의 새로 고침 명령에 해당합니다.

 

그런데 여전히 6/1자 B지점의 매출을 60,000으로 수정해도 반영되지 않습니다.

 

이것은 Worksheet_Change 이벤트에서 위 프로시저를 실행해야 하기 때문입니다.

 

 

나. Worksheet_Change 이벤트

Worksheet_Change는 자료를 입력할 Sheet1 시트에서 만들어야 합니다.

 

프로젝트 탐색기에서 Sheet1을 더블 클릭합니다.

그리고, 오른쪽 (일반)으로 표시된 곳에서 Worksheet를 선택하고, (선언)이라고 표시된 곳에서 Change를 선택합니다.

 

그러면 아래쪽 코드 영역에 Private Sub Worksheet_SelectionChange(ByVal Target As Range)와 Private Sub Worksheet_Change(ByVal Target As Range) 프로시저 2개가 자동 생성됩니다. SelectionChange 이벤트는 지워도 되고, 내용이 없으므로 그대로 둬도 됩니다.

 

이제 Module1에 있던 내용을 복사해서 Private Sub Worksheet_Change(ByVal Target As Range)안에 붙여 넣습니다.

 

그런데, 작업은 Sheet1에서 이뤄지고, 피벗 테이블은 Sheet2에 있기 때문에 ActiveSheet를 Sheets("Sheet1")이라고 수정해야 합니다.

 

이제 엑셀의 Sheet1에서 6/1자 B지점의 매출을 45,000으로 수정하면 Sheet2 피벗 테이블의 내용이 50,000에서 45,000으로 자동 변경됩니다.

 

 

 

다. 범위를 표로 변경

범위로 되어 있으면, 새로운 데이터를 입력하더라도

 

자동으로 피벗 테이블의 범위가 자동으로 변경되지 않습니다. 

피벗 테이블 분석 탭에서 데이터 원본 변경 윗부분을 눌러

 

범위를 확인해 보면 당초 범위로 되어 있는 것을 확인할 수 있습니다.

 

따라서, 입력 범위를 범위가 아니라 표로 바꿔야 합니다.

 

삽입 탭에서 표를 누르면 표 만들기 창이 생기는데 범위가 알아서 지정되고, 머리글 포함에도 체크가 되어 있습니다. 따라서, 확인 버튼을 누르

 

그러면 아래와 같이 머리글이 있는 표가 만들어집니다.

 

이제 8행에 입력을 하면 자동으로 표의 영역이 넓어지고, 매출까지 입력한 후 피벗 테이블을 보면

 

여전히 반영이 안 됩니다.

 

 

이것은 피벗 테이블의 범위가 아직도 범위로 되어 있어서 그런 것이므로 피벗 테이블 분석 탭에서 데이터 원본 변경을 누른 후 범위를 '표1'로 변경합니다.

 

그러면 변경사항이 피벗 테이블에 반영됩니다. 추가로 데이터를 입력해도 새로 고침을 누르지 않더라도 바로바로 데이터가 반영돼서 편리합니다.

 

피벗테이블 새로 고침1.xlsm

0.02MB