피벗 테이블에 대한 VBA 가이드

이 튜토리얼에서는 VBA를 사용하여 피벗 테이블로 작업하는 방법을 보여드립니다.

피벗 테이블은 데이터에서 주요 인사이트와 요약을 도출하는 데 사용할 수 있는 데이터 요약 도구입니다. 아래 그림과 같이 판매된 제품의 세부 정보가 포함된 A1:D21 셀의 원본 데이터 집합이 있다고 가정해 보겠습니다:

GetPivotData를 사용하여 값 가져오기

값/데이터 필드에 Sales(매출)가 있고 행 필드에 Product(제품), 열 필드에 Region(지역)이 있는 PivotTable1이라는 피벗 테이블이 있다고 가정하겠습니다. 피벗 테이블에서 값을 반환하려면 PivotTable.GetPivotData 메서드를 사용할 수 있습니다.

다음 코드는 피벗 테이블에서 ￦1,130(동부 지점의 총 매출)을 반환합니다:

MsgBox ActiveCell.PivotTable.GetPivotData("판매가격", "지역", "동부지점")

이 경우 판매가격은 “DataField”, “Field1″은 지역, “Item1″은 동부지점입니다.

다음 코드는 피벗 테이블에서 ￦980(북부지점의 제품 ABC에 대한 총 매출)을 반환합니다:

MsgBox ActiveCell.PivotTable.GetPivotData("판매가격", "제품", "ABC", "지역", "북부지점")

이 경우 판매가격는 “DataField”, “Field1″은 제품, “Item1″은 ABC, “Field2″는 지역, “Item2″는 북부지점입니다.

필드를 2개 이상 포함할 수도 있습니다.

GetPivotData의 구문은 다음과 같습니다:

GetPivotData (DataField, Field1, Item1, Field2, Item2…) 

Parameter	Description
Datafield	숫자가 포함된 매출, 수량 등의 데이터 필드입니다.
Field 1	테이블의 열 또는 행 필드 이름입니다.
Item 1	필드 1에 있는 항목의 이름(선택 사항)입니다.
Field 2	테이블의 열 또는 행 필드 이름(선택 사항)입니다.
Item 2	필드 2에 있는 항목의 이름(선택 사항)입니다.

 

시트에 피벗 테이블 만들기

위의 데이터 범위를 기반으로 활성 통합 문서의 Sheet1에 있는 J2 셀에 피벗 테이블을 만들려면 다음 코드를 사용합니다:

Worksheets("Sheet1").Cells(1, 1).Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

 "Sheet1!R1C1:R21C4", Version:=xlPivotTableVersion15).CreatePivotTable _

  TableDestination:="Sheet1!R2C10", TableName:="PivotTable1", DefaultVersion _

  :=xlPivotTableVersion15

  Sheets("Sheet1").Select

결과는 다음과 같습니다:

새 시트에 피벗 테이블 만들기

활성 통합 문서의 새 시트에서 위의 데이터 범위를 기반으로 피벗 테이블을 만들려면 다음 코드를 사용합니다:

Worksheets("Sheet1").Cells(1, 1).Select

Sheets.Add

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

 "Sheet1!R1C1:R21C4", Version:=xlPivotTableVersion15).CreatePivotTable _

  TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _

  :=xlPivotTableVersion15

  Sheets("Sheet2").Select

피벗 테이블에 필드 추가하기

위의 데이터 범위를 기반으로 새로 만든 피벗 테이블에 PivotTable1 이라는 필드를 추가할 수 있습니다. 참고: 피벗 테이블이 포함된 시트는 활성 시트여야 합니다.

행 필드에 제품을 추가하려면 다음 코드를 사용합니다:

ActiveSheet.PivotTables("PivotTable1").PivotFields("제품").Orientation = xlRowField

ActiveSheet.PivotTables("PivotTable1").PivotFields("제품").Position = 1

열 필드에 지역을 추가하려면 다음 코드를 사용합니다:

ActiveSheet.PivotTables("PivotTable1").PivotFields("지역").Orientation = xlColumnField

ActiveSheet.PivotTables("PivotTable1").PivotFields("지역").Position = 1

통화 서식을 사용하여 값 섹션에 판매가격을 추가하려면 다음 코드를 사용합니다:

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

        "PivotTable1").PivotFields("판매가격"), "판매가격 합계", xlSum

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("판매가격 합계")

        .NumberFormat = "$#,##0.00"

End With

실행 결과는 아래와 같습니다:

피벗 테이블의 보고서 레이아웃 변경하기

피벗 테이블의 보고서 레이아웃을 변경할 수 있습니다. 다음 코드는 피벗 테이블의 보고서 레이아웃을 표 형식으로 변경합니다:

ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight18"

피벗 테이블 삭제하기

VBA를 사용하여 피벗 테이블을 삭제할 수 있습니다. 다음 코드는 활성 시트에서 PivotTable1 이라는 피벗 테이블을 삭제합니다:

ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
Selection.ClearContents

통합 문서의 모든 피벗 테이블 서식 지정하기

VBA를 사용하여 통합 문서에 있는 모든 피벗 테이블의 서식을 지정할 수 있습니다. 다음 코드는 통합 문서의 모든 시트를 반복하기 위해 루프 구조를 사용하여 통합 문서의 모든 피벗 테이블에 서식을 지정합니다:

Sub FormattingAllThePivotTablesInAWorkbook()
Dim wks As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim pt As PivotTable

For Each wks In wb.Sheets
For Each pt In wks.PivotTables
pt.TableStyle2 = "PivotStyleLight15"
Next pt
Next wks

End Sub

VBA에서 반복을 사용하는 방법에 대해 자세히 알아보려면 여기를 클릭하세요.

피벗 테이블의 필드 제거하기

VBA를 사용하여 피벗 테이블에서 필드를 제거할 수 있습니다. 다음 코드는 활성 시트의 피벗 테이블에서 피벗 테이블1이라는 이름의 피벗 테이블에서 행 섹션의 Product 필드를 제거합니다:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Orientation = _
xlHidden

필터 만들기

행 섹션에 제품이 있고 값 섹션에 판매가격이 있는 피벗 테이블 PivotTable1이 만들어졌습니다. VBA를 사용하여 피벗 테이블에 대한 필터를 만들 수도 있습니다. 다음 코드는 필터 섹션에서 지역을 기준으로 필터를 만듭니다:

ActiveSheet.PivotTables("PivotTable1").PivotFields("지역").Orientation = xlPageField

ActiveSheet.PivotTables("PivotTable1").PivotFields("지역").Position = 1

단일 보고서 항목(이 경우 동부지역)을 기준으로 피벗 테이블을 필터링하려면 다음 코드를 사용합니다:

ActiveSheet.PivotTables("PivotTable1").PivotFields("지역").ClearAllFilters

ActiveSheet.PivotTables("PivotTable1").PivotFields("지역").CurrentPage = _

"동부지점"

여러 지역(이 경우 동부지점 및 북부지점)을 기준으로 피벗 테이블을 필터링하고 싶다고 가정하면 다음과 같이 코드를 작성하면 됩니다:

ActiveSheet.PivotTables("PivotTable1").PivotFields("지역").Orientation = xlPageField

ActiveSheet.PivotTables("PivotTable1").PivotFields("지역").Position = 1

ActiveSheet.PivotTables("PivotTable1").PivotFields("지역"). _
EnableMultiplePageItems = True

With ActiveSheet.PivotTables("PivotTable1").PivotFields("지역")

.PivotItems("남부지점").Visible = False

.PivotItems("서부지점").Visible = False

End With

피벗 테이블 새로 고침

VBA에서 피벗 테이블을 새로 고침할 수 있습니다. 다음 코드를 사용하여 VBA에서 PivotTable1 이라는 특정 테이블을 새로 고칠 수 있습니다:

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh