어떻게 Excel에서 동적으로 업데이트 피벗 테이블 데이터 원본 범위에

Posted on: 2020年11月12日 2020年11月12日
Tags: excel-pivot-tables

현재`link : / table-excel-2007-17-amazing-features-of-excel-tables [Excel Tables]`또는`link : / excel-range-name-dynamic-을 사용하여 피벗 테이블을 동적으로 변경하거나 업데이트 할 수 있습니다. named-ranges-in-excel [Dynamic Named Ranges]. 그러나 이러한 기술은 완벽하지 않습니다. 그래도 피벗 테이블을 수동으로 새로 고쳐야합니다. 수천 개의 행과 열이 포함 된 대용량 데이터가있는 경우 Excel 테이블은 많은 도움이되지 않습니다. 대신 파일이 무거워집니다. 따라서 남은 유일한 방법은 VBA입니다.

이 기사에서는 피벗 테이블이 데이터 소스를 자동으로 변경하도록하는 방법을 알아 봅니다. 즉, 데이터 소스를 변경하는 수동 프로세스를 자동화하여 소스 테이블에 추가 된 새 행과 열을 동적으로 포함하고 피벗 테이블의 변경 사항을 즉시 반영합니다.

소스 데이터 시트에 코드 쓰기 우리는 이것이 완전히 자동적이기를 원하기 때문에 코어 모듈 대신 시트 모듈을 사용하여 코드를 작성할 것입니다. 이렇게하면 //events-in-vba/the-worksheet-events-in-excel-vba.html[worksheet events]`를 사용할 수 있습니다.

소스 데이터와 피벗 테이블이 다른 시트에있는 경우 소스 데이터가 포함 된 시트 개체 (피벗 테이블이 포함되지 않음)에서 피벗 테이블 데이터 원본을 변경하는 VBA 코드를 작성합니다. CTRL + F11을 눌러 VB 편집기를 엽니 다. 이제 프로젝트 탐색기로 이동하여 원본 데이터가 포함 된 시트를 찾습니다. 그것을 두 번 클릭하십시오.

새로운 코딩 영역이 열립니다. 변경 사항이 표시되지 않을 수 있지만 이제 워크 시트 이벤트에 액세스 할 수 있습니다.

왼쪽 드롭 다운 메뉴를 클릭하고 워크 시트를 선택합니다. 왼쪽 드롭 다운 메뉴에서 비활성화를 선택합니다. 코드 영역 이름 workstation_deativate에 작성된 빈 하위가 표시됩니다. 소스 데이터를 동적으로 변경하고 피벗 테이블을 새로 고치는 코드는이 코드 블록에 포함됩니다. 이 코드는 데이터 시트에서 다른 시트로 전환 할 때마다 실행됩니다. 모든 워크 시트 이벤트`link : / events-in-vba-the-worksheet-events-in-excel-vba [here]`에 대해 읽을 수 있습니다.

이제 코드를 구현할 준비가되었습니다.

새 범위를 사용하여 동적으로 피벗 테이블을 업데이트하는 소스 코드 작동 방식을 설명하기 위해 통합 문서가 있습니다. 이 통합 문서에는 두 개의 시트가 있습니다. Sheet1에는 변경할 수있는 원본 데이터가 포함되어 있습니다. Sheet2는 sheet2의 소스 데이터에 의존하는 피벗 테이블을 포함합니다.

이제이 코드를 sheet1의 코딩 영역에 작성했습니다. Worksheet_Deactivate 이벤트를 사용하고 있으므로 소스 데이터 시트에서 전환 할 때마다이 코드가 실행되어 피벗 테이블을 업데이트합니다.

Private Sub Worksheet_Deactivate()

Dim pt As PivotTable

Dim pc As PivotCache

Dim source_data As Range

lstrow = Cells(Rows.Count, 1).End(xlUp).Row

lstcol = Cells(1, Columns.Count).End(xlToLeft).Column

Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))

Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)

Set pt = Sheet2.PivotTables("PivotTable1")

pt.ChangePivotCache pc

End Sub

유사한 통합 문서가있는 경우이 데이터를 직접 복사 할 수 있습니다.이 코드는 아래에서 설명했습니다.

아래 gif에서이 코드의 효과를 볼 수 있습니다.

이 코드는 어떻게 자동으로 소스 데이터를 변경하고 피벗 테이블을 업데이트합니까? 우선 우리는 workstation_deactivate 이벤트를 사용했습니다. 이 이벤트는 코드가 포함 된 시트가 전환되거나 비활성화 된 경우에만 트리거됩니다. 이것이 코드가 자동으로 실행되는 방식입니다.

이제 피벗 테이블의 소스 데이터를 변경하기 위해 피벗 캐시의 데이터를 변경합니다. 피벗 캐시를 사용하여 피벗 테이블이 생성됩니다. 피벗 테이블이 수동으로 새로 고쳐지지 않거나 소스 데이터 범위가 수동으로 변경 될 때까지 피벗 캐시에는 이전 소스 데이터가 포함됩니다.

피벗 테이블 이름 pt, 피벗 캐시 pc 및 source_data 범위에 대한 참조를 만들었습니다. 소스 데이터에는 전체 데이터가 포함됩니다. 전체 테이블을 데이터 범위로 동적으로 가져 오기 위해 마지막 행과 마지막 열을 결정합니다.

lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row

lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column

이 두 숫자를 사용하여 source_data를 정의합니다. 소스 데이터 범위는 항상 A1부터 시작됩니다.

set source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))

이제 동적 소스 데이터가 있습니다. 피벗 테이블에서 사용하면됩니다.

피벗 캐시가 모든 데이터를 저장한다는 것을 알고 있으므로이 데이터를 피벗 캐시에 저장합니다.

Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData : = source_data) 다음으로 업데이트 할 피벗 테이블을 정의합니다. Sheet1의 PivotTable1 (피벗 테이블 이름. 분석 탭에서 피벗 테이블을 선택하면서 피벗 테이블 이름을 확인할 수 있습니다.)을 업데이트하고자하므로 아래와 같이 pt를 설정합니다.

Set pt = Sheet2.PivotTables ( “PivotTable1”)

이제이 피벗 캐시를 사용하여 피벗 테이블을 업데이트합니다. pt 객체의 changePivotCache 메소드를 사용합니다.

pt.ChangePivotCache pc

그리고 우리는 피벗 테이블을 자동화했습니다. 피벗 테이블이 자동으로 업데이트됩니다. 데이터 소스가 동일한 여러 테이블이있는 경우 각 피벗 테이블 개체에서 동일한 캐시를 사용하면됩니다.

예, 이것이 Excel에서 데이터 소스 범위를 동적으로 변경할 수있는 방법입니다. 내가 충분히 설명했으면 좋겠다. 이 기사와 관련하여 질문이 있으시면 아래 댓글 섹션에 알려주십시오.