Excel에서 모든 피벗 테이블 데이터 원본을 동적으로 업데이트하는 방법

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

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

소스 데이터 시트에 코드 작성

이 작업이 완전히 자동으로 이루어지기를 원하므로 코어 모듈 대신 시트 모듈을 사용하여 코드를 작성합니다. 이렇게하면 워크 시트 이벤트를 사용할 수 있습니다.

소스 데이터와 피벗 테이블이 다른 시트에있는 경우 소스 데이터가 포함 된 시트 개체에서 피벗 테이블 데이터 원본을 변경하는 VBA 코드를 작성합니다 ( 피벗 테이블이 포함되지 않음).

Ctrl + F11을 눌러 VB 편집기를 엽니 다. 이제 프로젝트 탐색기로 이동하여 원본 데이터가 포함 된 시트를 찾습니다. 더블 클릭하세요.

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

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

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

새 범위로 통합 문서의 모든 피벗 테이블을 동적으로 업데이트하는 소스 코드

작동 방식을 설명하기 위해 통합 문서가 있습니다. 이 통합 문서에는 세 개의 시트가 있습니다. Sheet1에는 변경할 수있는 원본 데이터가 포함되어 있습니다. Sheet2 및 Sheet3에는 sheet2의 소스 데이터에 의존하는 피벗 테이블이 포함되어 있습니다.

이제 sheet1의 코딩 영역에이 코드를 작성했습니다. Worksheet_Deactivate 이벤트를 사용하여이 코드가 소스 데이터 시트에서 전환 할 때마다 피벗 테이블을 사용합니다.

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

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

이 코드가 자동으로 변경되는 방식 소스 데이터 및 업데이트 피벗 테이블?

우선 workstation_deactivate 이벤트를 사용했습니다.이 이벤트는 코드가 포함 된 시트가 전환되거나 비활성화 된 경우에만 트리거됩니다. 따라서 코드가 자동으로 실행됩니다.

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

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

lstcol = Cells (1, Columns.Count) .End (xlToL eft) .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

Method를 사용하여 동적으로 피벗 캐시를 만듭니다.이 메서드는 SourceType 및 SourceData 변수 두 개를 사용합니다. 소스 유형으로 xlDatabase 및 SourceData로 앞서 계산 한 source_data 범위를 전달합니다.

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

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

Excel에서 피벗 테이블 데이터 원본 범위를 동적으로 업데이트하는 방법 : 피벗 테이블의 원본 데이터 범위를 동적으로 변경하기 위해 피벗 캐시를 사용합니다. 이 몇 줄은 소스 데이터 범위를 변경하여 모든 피벗 테이블을 동적으로 업데이트 할 수 있습니다.

VBA를 사용하여 피벗 테이블을 자동으로 새로 고치는 방법 : 피벗 테이블을 자동으로 새로 고치려면 VBA 이벤트를 사용할 수 있습니다. 이 간단한 코드 줄을 사용하여 피벗 테이블을 자동으로 업데이트하십시오. 피벗 테이블을 자동으로 새로 고치는 3 가지 방법 중 하나를 사용할 수 있습니다.

지정된 범위의 시트에서 변경된 경우 매크로 실행 : VBA 관행에서 특정 범위 또는 세포 변화. 이 경우 대상 범위가 변경 될 때 매크로를 실행하기 위해 change 이벤트를 사용합니다.

시트에 변경이있을 때 매크로 실행 | 따라서 시트가 업데이트 될 때마다 매크로를 실행하기 위해 VBA의 워크 시트 이벤트를 사용합니다.

|를 사용하여 현재 행과 열을 강조하는 가장 간단한 VBA 코드 이 작은 VBA 스 니펫을 사용하여 시트의 현재 행과 열을 강조 표시하십시오.

Excel VBA의 워크 시트 이벤트 | 워크 시트 이벤트는 시트에 지정된 이벤트가 발생할 때 매크로가 실행되기를 원할 때 매우 유용합니다.

인기 기사 :

생산성을 높이기위한 50 가지 Excel 바로 가기 | 작업 속도를 높이십시오. 이 50 개의 바로 가기를 사용하면 Excel에서 더 빠르게 작업 할 수 있습니다. Excel의 VLOOKUP 함수 | 이것은 다른 범위와 시트에서 값을 조회하는 데 사용되는 Excel의 가장 많이 사용되고 인기있는 기능 중 하나입니다.

Excel 2016의 COUNTIF | 이 놀라운 기능을 사용하여 조건으로 값을 계산합니다. 특정 값을 계산하기 위해 데이터를 필터링 할 필요가 없습니다. Countif 함수는 대시 보드를 준비하는 데 필수적입니다.

Excel에서 SUMIF 함수를 사용하는 방법 | 이것은 또 다른 대시 보드 필수 기능입니다. 이는 값을 요약하는 데 도움이됩니다. 특정 조건에서.