요점 : 간단한 매크로를 사용하여 소스 데이터가 변경 될 때마다 자동으로 피벗 테이블을 새로 고치는 방법을 알아 봅니다. 또한 파일이 열릴 때 피벗 테이블을 업데이트하는 비 매크로 솔루션을 공유합니다. 비디오 자습서 및 Excel 파일 다운로드가 포함되어 있습니다.
기술 수준 : 중급
Excel 파일 다운로드
자신으로 수행하여 최선을 다한다면 다음을 수행 할 수 있습니다. 따라 할 비디오에서 사용중인 파일을 다운로드하십시오. 다음은 VBA 코드가 포함 된 Excel 파일입니다.
자동으로 피벗 테이블 새로 고침 .xlsm (41.5 KB)
자동으로 피벗 테이블 업데이트
소스 데이터가 변경 될 때 피벗 테이블을 즉시 자동으로 업데이트 할 수 있습니까?
물론입니다. 아래에서 만드는 방법을 보여 드릴 아주 간단한 매크로입니다.
매크로에 익숙하지 않은 경우 VBA와 함께 매크로 & VBA를 시작하는 방법에 대한 3 부작 무료 동영상 시리즈를 확인하는 것이 좋습니다.
또한 피벗 테이블을 처음 사용하는 경우 그것들이 무엇이고 어떻게 사용하는지에 대한 시리즈가 있습니다. 피벗 테이블에 대한 시리즈의 첫 번째 비디오를보십시오. & 대시 보드
피벗 테이블을 자동으로 업데이트하기 위해 간단한 지침 하나로 매크로를 작성합니다. 이 지침은 기본적으로 워크 시트를 변경하면 모든 피벗 테이블과 데이터 연결을 새로 고칩니다. 매크로를 만드는 단계는 다음과 같습니다.
Visual Basic Editor를 엽니 다.
리본의 개발자 탭에서 Visual Basic 단추를 클릭하면됩니다.
Visual Basic 편집기를 여는 키보드 단축키는 Alt + F11입니다.
표시되지 않는 경우 개발자 탭에서 여기의 지침을 사용하여 표시 할 수 있습니다. 이 작업은 한 번만 수행하면 나중에 Excel을 열 때마다 개발자 탭이 항상 표시됩니다.
소스 데이터가 포함 된 시트 모듈을 엽니 다.
Visual Basic 편집기의 프로젝트 탐색기 창에서 변경할 통합 문서를 찾습니다. 해당 통합 문서 아래에 통합 문서 내의 시트가 나열됩니다. 소스 데이터가 포함 된 시트를 선택하십시오. 그런 다음 두 번 클릭합니다.
프로젝트 탐색기 창이 보이지 않으면보기에서 활성화 할 수 있습니다. 메뉴 (단축키 : Ctrl + R).
워크 시트 변경을위한 새 이벤트 추가
시트를 두 번 클릭하면 해당 개체의 코드 모듈이 열립니다. 코드 모듈 내에서 이벤트 매크로를 만들고 싶습니다. 이렇게하려면 왼쪽의 개체 드롭 다운 상자에서 워크 시트를 선택합니다.
그러면 Worksheet_SelectionChange 이벤트가 실제로 원하지 않는 모듈이므로 잠시 후에 삭제하겠습니다. 그 전에 오른쪽의 절차 드롭 다운 메뉴로 이동하여 변경을 선택하겠습니다.
Worksheet_Change라는 상단의 이벤트. 이제 그 아래에있는 불필요한 코드를 강조 표시하고 삭제합니다.
Worksheet_Change 이벤트 매크로는 변경이있을 때마다 실행됩니다. 해당 워크 시트의 셀에. Worksheet_Change 이벤트에 VBA 코드를 추가하여 사용자가 셀을 편집 할 때 작업을 수행 할 수 있습니다.
참고 : 기본적으로 추가되는 SelectionChange 이벤트는 사용자가 시트에서 셀을 선택할 때마다 실행됩니다. 사용자가 셀을 편집 / 변경할 때만 코드가 실행되기를 원하므로 Change 이벤트를 사용합니다. 시트 모듈 및 이벤트에 대해 자세히 알아 보려면 VBA 코드 모듈 & 사용자 이벤트를 기반으로 매크로를 실행하는 방법에 대한 기사를 확인하십시오.
새로 고침 할 VBA 코드 추가 모든 피벗 테이블.
다음으로 Worksheet_Change 줄 바로 아래에 다음 지침을 입력합니다.
ThisWorkbook.RefreshAll
RefreshAll 메서드는 통합 문서의 모든 피벗 테이블, 쿼리 및 데이터 연결을 새로 고칩니다. 이 작업은 데이터 탭에서 새로 고침 단추를 수동으로 클릭하는 것과 동일합니다.
이 코드 줄을 Worksheet_Change 이벤트에 추가하면 코드가 워크 시트에 변경 될 때마다 통합 문서가 새로 고쳐집니다. in.
피벗 테이블 & 동일한 시트의 소스 데이터
Aleksandrs는 YouTube 동영상 댓글에 대해 훌륭한 질문을했습니다. 피벗 테이블과 소스 데이터가 동일한 시트에있는 경우 이벤트를 비활성화하는 코드를 추가해야합니다.
새로 고침으로 인해 이벤트가 재귀 루프에 들어가고 Excel이 중단 될 수 있습니다. 이를 방지하는 코드는 다음과 같습니다.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
매크로가 실행 중인지 확인
매크로가 작동하는지 확인하는 한 가지 방법 그것을 테스트하는 것입니다. 소스 데이터를 변경하고 피벗 테이블에 반영되는지 확인합니다.데이터가 너무 많아서 변경 사항을 찾기가 쉽지 않거나 다른 이유로 매크로가 실행되는지 확인할 수있는 다른 방법이 있습니다.
VB 편집기에서 Worksheet_Change 매크로 바로 왼쪽에있는 회색 열. 그러면 빨간색 원이 나타납니다. 또한 해당 코드 줄을 빨간색으로 강조 표시합니다.
이를 중지 또는 중단 점이라고합니다.
중단 점을 켜거나 끄는 키보드 단축키는 다음과 같습니다. F9
이제 매크로를 트리거하는 동작이 발생할 때마다 Excel은 VB 편집기로 이동하고 매크로를 일시 중지하여 코드를 확인할 수 있습니다. 이 경우 해당 작업은 워크 시트에서 수행되는 변경 사항입니다.
그런 다음 F8 키를 눌러 각 줄을 누르거나 F5 키를 눌러 끝 (또는 다음 중단 점)까지 실행합니다.
워크 시트를 변경했는데 Excel에서 VB 편집기로 이동하지 않으면 문제가있는 것입니다. 매크로가 실행되지 않습니다. 이 경우 파일을 매크로 사용 통합 문서 (.xlsm) 및 / 또는 사용 매크로로 저장하지 않았을 수 있습니다. & 파일을 닫은 다음 다시 열고 매크로를 활성화해야 할 수 있습니다.
매크로에 배치 한 중단 점을 제거하려면, 빨간색 원을 클릭하면 사라집니다 (키보드 단축키 : F9).
모든 중단 점을 지우는 키보드 단축키는 Ctrl + Shift + F9입니다.
매크로
이 매크로를 사용하여 피벗 테이블을 새로 고칠 때의 한 가지 단점은 매크로가 실행될 때마다 실행 취소 기록이 손실된다는 것입니다. 즉, 실행 취소 단추를 클릭하거나 Ctrl + Z를 누르면 Excel에서 마지막으로 수행 한 작업을 기억하지 못하므로 실행 취소 할 수 없습니다. 결과적으로 아무 일도 일어나지 않으며 마지막 변경 사항은 취소되지 않습니다.
실행 취소 기록을 유지할 수있는 대안이 있습니다. 그러나이 대안은 통합 문서가 열릴 때만 피벗 테이블을 새로 고칩니다. 변경 사항이있을 때마다 새로 고쳐지지는 않습니다. 이 옵션을 사용하는 방법은 다음과 같습니다.
피벗 테이블의 모든 셀에서 시작 :
- 리본의 분석 탭으로 이동합니다.
- 옵션 버튼을 선택합니다.
- 열린 새 창에서 데이터 탭으로 이동합니다.
- “파일을 열 때 데이터 새로 고침”확인란을 선택합니다.
확인을 클릭 한 후 동일한 소스 데이터 범위에서 여러 피벗 테이블을 생성 한 경우 다음 경고 메시지가 표시 될 수 있습니다. 확인을 클릭하면됩니다.
다시 비교하면 이 옵션을 사용하면 실행 취소 기록이 유지되지만 통합 문서를 닫았다가 다시 열 때만 피벗 테이블을 새로 고칩니다. 매크로 옵션을 사용하면 실행 취소 기록이 손실되지만 피벗 테이블은 자동으로 업데이트됩니다. 통합 문서가 변경 될 때마다 확인됩니다.
피벗 테이블 새로 고침을위한 변형
우리가 살펴본 매크로는 피벗 테이블을 새로 고칠뿐만 아니라 쿼리도 새로 고칩니다. . 피벗 테이블 만 새로 고치려면 “ThisWorkbook.RefreshAll”명령을 대신 다음 코드로 바꿀 수 있습니다.
Sub Refresh_All_Pivot_Table_Caches()"Refresh all pivot caches in the workbook."Pivot tables are automatically refreshed when cache is refreshed.Dim pc As PivotCache "Refresh all pivot tables For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
각 피벗 테이블은 소스 데이터에 연결된 기본 피벗 캐시입니다. 코드는 통합 문서의 모든 피벗 캐시를 반복하고 각 캐시를 새로 고칩니다. 동일한 소스 범위의 피벗 테이블은 피벗 캐시를 공유 할 수 있으므로이 방법은 모든 피벗 테이블을 반복하는 것보다 빠릅니다. .
마찬가지로 특정 피벗 테이블을 하나만 새로 고치고 싶다고 가정 해 보겠습니다. 이 경우 “ThisWorkbook.RefreshAll”코드를 아래 코드로 바꿀 수 있습니다.
그리고 마지막으로 파워 쿼리를 사용 중이고 피벗 테이블 전에 쿼리가 새로 고쳐 지도록 백그라운드 새로 고침을 비활성화하려는 경우 쿼리에서 백그라운드 새로 고침을 비활성화하여이를 수행하는 방법을 설명하는 문서를 작성했습니다.
대신 이벤트 비활성화
또 다른 옵션은 Worksheet_Change 대신 Worksheet_Deactivate 이벤트를 사용하는 것입니다. Worksheet_Deactivate 이벤트는 사용자가 시트를 떠나 다른 시트를 선택할 때마다 실행됩니다. 이렇게하면 사용자가 모든 항목을 변경할 수 있습니다. 그러면 피벗 테이블이 포함 된 시트를 포함하여 다른 시트로 이동할 때 피벗 테이블이 자동으로 새로 고쳐집니다.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
이 코드 소스 데이터가 포함 된 시트 모듈에 계속 배치됩니다. 피벗 테이블 또는 데이터 연결이 필요한 경우 좋은 옵션입니다. 업데이트하는 데 몇 초 이상 걸리며 “소스 데이터가 변경 될 때마다 기다릴 필요는 없습니다.
이 기능을 사용하지 않으려는 유일한 경우는 피벗 테이블과 소스 데이터는 같은 시트에 있습니다.일반적으로 드문 경우이며 일반적으로 권장하지 않는 경우입니다.
Ted의 제안에 감사드립니다.
시간 절약 & 당황
이 기사가 시간을 절약하고 파일 사용자가 더 쉽게 사용할 수 있기를 바랍니다. 보고서를 보내기 전에 피벗 테이블을 새로 고치는 것을 잊었을 때 당황 스러움을 방지하는 데 도움이되기를 바랍니다. 저, 저는 제가 인정하고 싶은 것보다이 실수를 더 많이했습니다… 🙂
