어떻게 동적으로 업데이트 모든 피벗 테이블 Excel에서 데이터 소스에

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

이전 기사에서 축소 또는 확장 데이터 소스를 사용하여 개별 피벗 테이블을 동적으로 변경하고 업데이트하는 방법을 배웠습니다.

이 문서에서는 통합 문서의 모든 피벗 테이블이 데이터 원본을 자동으로 변경하도록하는 방법에 대해 알아 봅니다. 즉, 한 번에 하나의 피벗 테이블을 변경하는 대신 통합 문서에있는 모든 피벗 테이블의 데이터 원본을 변경하여 원본 테이블에 추가 된 새 행과 열을 동적으로 포함하고 피벗 테이블의 변경 사항을 즉시 반영합니다.

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

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

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

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

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

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

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

Private Sub Worksheet_Deactivate()

Dim source_data As Range

'Determining last row and column number

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

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

'Setting the new range

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

'Code to loop through each sheet and pivot table

For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables



pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=source_data)



Next pt

Next ws

End Sub

유사한 통합 문서가있는 경우이 데이터를 직접 복사 할 수 있습니다.이 코드는 필요에 따라 수정할 수 있도록 아래에 설명되어 있습니다.

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

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

전체 테이블을 데이터 범위로 동적으로 가져 오기 위해 마지막 행과 마지막 열을 결정합니다.

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))

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

통합 문서에 한 번에 포함 할 피벗 테이블 수를 알 수 없기 때문에 각 시트와 각 시트의 피벗 테이블을 반복합니다. 피벗 테이블이 남지 않도록합니다. 이를 위해 중첩 for 루프를 사용합니다.

ThisWorkbook.Worksheets의 각 ws에 대해

ws.PivotTables의 각 pt에 대해

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType : = xlDatabase, _

SourceData : = source_data)

다음 pt

다음 ws

첫 번째 루프는 각 시트를 반복합니다. 두 번째 루프는 시트의 각 피벗 테이블에서 반복됩니다. 피벗 테이블은 변수 pt에 할당됩니다. * pt 개체의 ChangePivotCache 메서드를 사용합니다. ThisWorkbook.PivotCaches.Create 메서드를 사용하여 피벗 캐시를 동적으로 만듭니다. 이 메서드는 SourceType 및 SourceData 두 변수를 사용합니다. 소스 유형으로 xlDatabase를 선언하고 SourceData로 이전에 계산 한 source_data 범위를 전달합니다.

그리고 그게 다입니다. 피벗 테이블이 자동화되어 있습니다. 그러면 통합 문서의 모든 피벗 테이블이 자동으로 업데이트됩니다.

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