어떻게 Microsoft Excel에서 VBA를 사용하여 자동 업데이트 피벗 테이블
이 기사에서는 Microsoft Excel 2010에서 VBA를 사용하여 피벗 테이블을 자동 업데이트하는 방법에 대해 알아 봅니다.
피벗 테이블 차트를 업데이트해야하는 이유는 무엇입니까?
우리 모두 알고 있듯이 피벗 테이블의 소스 데이터를 변경할 때마다 피벗 테이블에 즉시 반영되지 않습니다. 변경 사항을 확인하려면 피벗 테이블을 새로 고쳐야합니다. 피벗 테이블을 새로 고치지 않고 업데이트 된 파일을 보내면 당황 스러울 수 있습니다.
따라서이 기사에서는 VBA를 사용하여 피벗 테이블을 자동으로 새로 고치는 방법을 배웁니다.
통합 문서의 피벗 테이블을 자동으로 새로 고치는 간단한 구문입니다.
'Code in Source Data Sheet Object
Private Sub Worksheet_Deactivate()
sheetname_of_pivot_table.PivotTables("pivot_table_name").PivotCache.Refresh
End Sub
피벗 캐시 란 무엇입니까? 모든 피벗 테이블은 데이터를 피벗 캐시에 저장합니다. 이것이 피벗이 이전 데이터를 표시 할 수있는 이유입니다. 피벗 테이블을 새로 고칠 때 피벗 테이블의 변경 사항을 반영하기 위해 새 소스 데이터로 캐시를 업데이트합니다.
따라서 피벗 테이블의 캐시를 새로 고치는 매크로 만 있으면됩니다. 매크로를 수동으로 실행할 필요가 없도록`link : / events-in-vba-the-worksheet-events-in-excel-vba [worksheet event]`를 사용하여이 작업을 수행합니다.
피벗 테이블 자동 새로 고침을 코딩 할 위치 소스 데이터와 피벗 테이블이 다른 시트에 있으면 VBA 코드가 소스 데이터 시트에 있어야합니다.
여기서는 Worksheet_SelectionChange 이벤트를 사용합니다. 이렇게하면 소스 데이터 시트에서 다른 시트로 전환 할 때마다 코드가 실행됩니다.
이 이벤트를 사용한 이유는 나중에 설명하겠습니다.
여기에서는 sheet2에 소스 데이터가 있고 sheet1에 피벗 테이블이 있습니다.
CTRL + F11 키를 사용하여 VBE를 엽니 다. 프로젝트 탐색기에서 Sheet1, Sheet2 및 통합 문서의 세 가지 개체를 볼 수 있습니다.
Sheet2에는 소스 데이터가 포함되어 있으므로 sheet2 개체를 두 번 클릭합니다.
이제 코드 영역 상단에 두 개의 드롭 다운이 표시됩니다. 첫 번째 드롭 다운에서 워크 시트를 선택합니다. 두 번째 드롭 다운에서 비활성화를 선택합니다. 그러면 빈 하위 이름 Worksheet_Deactivate가 삽입됩니다. 우리의 코드는이 서브에 작성 될 것입니다. 이 서브에 쓰여진 모든 줄은 사용자가이 시트에서 다른 시트로 전환하자마자 실행됩니다.
sheet1에는 두 개의 피벗 테이블이 있습니다. 하나의 피벗 테이블 만 새로 고치고 싶습니다. 이를 위해 피벗 테이블의 이름을 알아야합니다. 피벗 테이블의 이름을 알려면 해당 피벗 테이블의 셀을 선택하고 피벗 테이블 분석 탭으로 이동하십시오. 왼쪽에 피벗 테이블의 이름이 표시됩니다. 여기에서 피벗 테이블의 이름을 변경할 수도 있습니다.
이제 피벗 테이블의 이름을 알았으므로 간단한 선을 작성하여 피벗 테이블을 새로 고칠 수 있습니다.
Private Sub Worksheet_Deactivate()
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
그리고 끝났습니다.
이제 소스 데이터에서 전환 할 때마다이 vba 코드가 실행되어 피벗 테이블 1을 새로 고칩니다. 아래 gif에서 볼 수 있습니다.
통합 문서의 모든 피벗 테이블을 새로 고치는 방법? 위의 예에서는 하나의 특정 피벗 테이블 만 새로 고치고 싶었습니다. 그러나 통합 문서의 모든 피벗 테이블을 새로 고치려면 코드를 약간 변경하면됩니다.
Private Sub Worksheet_Deactivate()
'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub
이 코드에서는`link : / vba-for-loops-with-7-examples [For loop]`를 사용하여 통합 문서의 각 피벗 캐시를 반복합니다. ThisWorkbook 개체는 모든 피벗 캐시를 포함합니다. 여기에 액세스하려면 ThisWorkbook.PivotCaches를 사용합니다.
Worksheet_Deactivate 이벤트를 사용하는 이유는 무엇입니까?
소스 데이터가 변경되는 즉시 피벗 테이블을 새로 고치려면 Worksheet_Change 이벤트를 사용해야합니다. 그러나 나는 그것을 권장하지 않습니다. 시트를 변경할 때마다 통합 문서에서 코드를 실행합니다. 결과를보기 전에 수백 가지 변경 작업을 수행해야 할 수 있습니다. 그러나 Excel은 모든 변경 사항에 대해 피벗 테이블을 새로 고칠 것입니다. 이것은 처리 시간과 자원의 낭비로 이어질 것입니다. 따라서 다른 시트에 피벗 테이블과 데이터가있는 경우 Worksheet Deactivate Event를 사용하는 것이 좋습니다. 작업을 마무리 할 수 있습니다. 변경 사항을 확인하기 위해 피벗 테이블 시트로 전환하면 변경 사항이 수정됩니다.
피벗 테이블과 소스 데이터가 같은 시트에 있고 피벗 테이블이 자동으로 새로 고쳐 지길 원한다면`link : / tips-using-worksheet-change-event-to-run-macro-when- 모든 변경이 이루어짐 [Worksheet_Change Event]`.
Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
원본 데이터가 변경된 경우 통합 문서의 모든 항목을 새로 고치는 방법은 무엇입니까? 통합 문서 (차트, 피벗 테이블, 수식 등)의 모든 항목을 새로 고치려면 ThisWorkbook.RefreshAll 명령을 사용할 수 있습니다.
Private Sub Worksheet_Change(ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
이 코드는 데이터 소스를 변경하지 않습니다. 따라서 소스 데이터 아래에 데이터를 추가하면이 코드는 해당 데이터를 자동으로 포함하지 않습니다. `link : / table-excel-2007-17-amazing-features-of-excel-tables [Excel Tables]`를 사용하여 소스 데이터를 저장할 수 있습니다. 테이블을 사용하지 않으려면 VBA를 사용하여 새 데이터도 포함 할 수 있습니다. 다음 튜토리얼에서 배울 것입니다.
Microsoft Excel에서 VBA를 사용하여 피벗 테이블을 자동 업데이트하는 방법에 대한이 기사가 설명이되기를 바랍니다. 여기에서 값 계산 및 관련 Excel 수식에 대한 더 많은 문서를 찾을 수 있습니다. 블로그가 마음에 들면 Facebook에서 친구들과 공유하세요. 또한 Twitter와 Facebook에서 우리를 팔로우 할 수 있습니다. 우리는 여러분의 의견을 듣고 싶습니다. 우리가 작업을 개선, 보완 또는 혁신하고 여러분을 위해 개선 할 수있는 방법을 알려주십시오. [email protected]로 이메일을 보내주십시오.
관련 기사 :
link : / excel-pivot-tables-how-to-dynamically-update-pivot-table-data-source-range-in-excel [Excel에서 피벗 테이블 데이터 소스 범위를 동적으로 업데이트하는 방법]: 피벗 테이블의 소스 데이터 범위, 피벗 캐시를 사용합니다. VBA에서는 아래와 같이 피벗 테이블 개체를 사용하십시오 …
link : / events-in-vba-run-macro-if-any-change-made-on-sheet-range [지정된 범위의 시트에서 변경된 경우 매크로 실행]: VBA 관행에서 특정 범위 또는 셀이 변경 될 때 매크로를 실행해야합니다. 이 경우 대상 범위가 변경 될 때 매크로를 실행하기 위해 change 이벤트를 사용합니다.
link : / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [Run Macro when any changes is made on Sheet]: 따라서 시트가있을 때마다 매크로를 실행합니다. 업데이트, 우리는 VBA의 워크 시트 이벤트를 사용합니다.
link : / events-in-vba-simplest-vba-code-to-highlight-current-row-and-column-using [Simplest VBA Code to Highlight Current Row and Column Using]:이 작은 VBA 스 니펫을 사용하여 강조 표시 시트의 현재 행과 열.
link : / events-in-vba-the-worksheet-events-in-excel-vba [Excel VBA의 워크 시트 이벤트]: 워크 시트 이벤트는 지정된 이벤트가 발생할 때 매크로를 실행하려는 경우 매우 유용합니다. 그 종잇장.
인기 기사 :
link : / tips-if-condition-in-excel [Excel에서 IF 함수 사용 방법]: Excel의 IF 문은 조건을 확인하고 조건이 TRUE 인 경우 특정 값을 반환하거나 FALSE 인 경우 다른 특정 값을 반환합니다. .
link : / formulas-and-functions-introduction-of-vlookup-function [Excel에서 VLOOKUP 함수 사용 방법]: 다양한 범위의 값을 조회하는 데 사용되는 Excel에서 가장 많이 사용되는 인기 함수 중 하나입니다. 및 시트.
link : / excel-formula-and-function-excel-sumif-function [Excel에서 SUMIF 함수 사용 방법]: 대시 보드의 또 다른 필수 기능입니다. 이를 통해 특정 조건에 대한 값을 합산 할 수 있습니다.
link : / tips-countif-in-microsoft-excel [Excel에서 COUNTIF 함수 사용 방법]:이 놀라운 함수를 사용하여 조건으로 값을 계산합니다. 특정 값을 계산하기 위해 데이터를 필터링 할 필요가 없습니다. Countif 기능은 대시 보드를 준비하는 데 필수적입니다.