어떻게 VBA Excel을 사용하여 자동 새로 고침 피벗 테이블에

Posted on: 2020年11月12日 2020年11月12日
Tags: custom-functions-in-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가 삽입됩니다. 이 서브에 코드가 작성됩니다. 이 하위에 작성된 모든 행은 사용자가이 시트에서 다른 시트로 전환하자마자 실행됩니다.

시트 1에는 두 개의 피벗 테이블이 있습니다. 하나의 피벗 테이블 만 새로 고치고 싶습니다. 이를 위해 피벗 테이블의 이름을 알아야합니다. 피벗 테이블의 이름을 알려면 해당 피벗 테이블의 셀을 선택하고 피벗 테이블 분석 탭으로 이동하십시오. 왼쪽에 피벗 테이블의 이름이 표시됩니다. 여기에서 피벗 테이블의 이름을 변경할 수도 있습니다.

이제 피벗 테이블의 이름을 알았으므로 간단한 선을 작성하여 피벗 테이블을 새로 고칠 수 있습니다.

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를 사용하여 새 데이터도 포함 할 수 있습니다. 다음 튜토리얼에서 배울 것입니다.

예, 이것이 Excel에서 피벗 테이블을 자동으로 새로 고치는 방법입니다. 나는 내가 충분히 설명하고이 기사가 당신에게 잘 도움이 되었기를 바랍니다. 이 주제와 관련하여 질문이 있으면 아래 댓글 섹션에서 저에게 질문 할 수 있습니다.

어떻게 VBA Excel을 사용하여 자동 새로 고침 피벗 테이블에

통합 문서의 모든 피벗 테이블을 새로 고치는 방법? 위의 예에서는 하나의 특정 피벗 테이블 만 새로 고치고 싶었습니다. 그러나 통합 문서의 모든 피벗 테이블을 새로 고치려면 코드를 약간 변경하면됩니다.

Worksheet_Deactivate 이벤트를 사용하는 이유는 무엇입니까?

관련 기사 :

인기 기사 :