엑셀 VBA에서 피벗 테이블(Pivot Table) 활용 방법

엑셀 VBA를 사용하여 **피벗 테이블(Pivot Table)**을 생성, 수정, 업데이트하는 방법을 정리합니다.

1. 피벗 테이블 개요

피벗 테이블은 데이터를 요약하고 분석할 수 있는 강력한 기능으로, 특정 필드를 기준으로 그룹화하거나, 합계, 평균 등을 계산하는 데 사용됩니다.
VBA에서는 PivotTable, PivotCache, PivotFields 등의 객체를 사용하여 피벗 테이블을 생성하고 조작할 수 있습니다.

2. 피벗 테이블 생성 방법

(1) 기본 피벗 테이블 생성 (새 워크시트에 추가)

Sub CreatePivotTable()
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim ptCache As PivotCache
    Dim pt As PivotTable
    Dim dataRange As Range

    ' 원본 데이터 시트 및 범위 설정
    Set wsData = ThisWorkbook.Sheets("Data") ' 원본 데이터가 있는 시트
    Set dataRange = wsData.Range("A1:D100") ' A1:D100 데이터를 사용

    ' 피벗 테이블을 삽입할 새 워크시트 추가
    Set wsPivot = ThisWorkbook.Sheets.Add
    wsPivot.Name = "PivotTableSheet"

    ' 피벗 캐시 생성 (데이터를 메모리에 로드)
    Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=dataRange)

    ' 피벗 테이블 생성
    Set pt = ptCache.CreatePivotTable(TableDestination:=wsPivot.Range("A3"), TableName:="MyPivotTable")

    ' 필드 추가
    With pt
        .PivotFields("카테고리").Orientation = xlRowField ' 행 필드
        .PivotFields("제품명").Orientation = xlColumnField ' 열 필드
        .PivotFields("매출액").Orientation = xlDataField ' 값 필드
    End With

    MsgBox "피벗 테이블이 생성되었습니다!", vbInformation
End Sub

• 원본 데이터 범위: wsData.Range("A1:D100")에서 가져옴.
• 새로운 시트에 피벗 테이블 생성: TableDestination:=wsPivot.Range("A3")
• 행(Row) 필드: 카테고리
• 열(Column) 필드: 제품명
• 값(Data) 필드: 매출액

(2) 기존 워크시트에서 피벗 테이블 생성

기존에 있는 시트에 피벗 테이블을 추가하려면:

Sub CreatePivotInExistingSheet()
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim ptCache As PivotCache
    Dim pt As PivotTable
    Dim dataRange As Range

    ' 원본 데이터와 피벗 테이블을 넣을 시트 지정
    Set wsData = ThisWorkbook.Sheets("Data")
    Set wsPivot = ThisWorkbook.Sheets("PivotSheet")
    Set dataRange = wsData.Range("A1:D100")

    ' 기존 워크시트에서 피벗 캐시 생성
    Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=dataRange)

    ' 기존 시트의 특정 위치에 피벗 테이블 생성
    Set pt = ptCache.CreatePivotTable(TableDestination:=wsPivot.Range("B4"), TableName:="PivotTable2")

    ' 필드 설정
    With pt
        .PivotFields("카테고리").Orientation = xlRowField
        .PivotFields("매출액").Orientation = xlDataField
    End With

    MsgBox "피벗 테이블이 기존 시트에 생성되었습니다!", vbInformation
End Sub

3. 피벗 테이블 필드 수정 및 데이터 조작

(1) 데이터 필드 요약 방식 변경 (합계 → 평균)

Sub ChangeDataFieldSummary()
    Dim wsPivot As Worksheet
    Dim pt As PivotTable

    Set wsPivot = ThisWorkbook.Sheets("PivotTableSheet")
    Set pt = wsPivot.PivotTables("MyPivotTable")

    ' 데이터 필드 요약 방식을 평균으로 변경
    With pt.PivotFields("매출액")
        .Function = xlAverage ' 평균으로 설정
    End With

    MsgBox "피벗 테이블 요약 방식이 '평균'으로 변경되었습니다.", vbInformation
End Sub

• Function = xlAverage → xlSum(합계), xlCount(개수), xlMax(최대값) 등 변경 가능.

(2) 특정 필드 정렬 및 필터링

Sub FilterPivotField()
    Dim wsPivot As Worksheet
    Dim pt As PivotTable

    Set wsPivot = ThisWorkbook.Sheets("PivotTableSheet")
    Set pt = wsPivot.PivotTables("MyPivotTable")

    ' "카테고리" 필드에서 "전자제품"만 필터링
    With pt.PivotFields("카테고리")
        .ClearAllFilters ' 기존 필터 제거
        .CurrentPage = "전자제품"
    End With

    MsgBox "필터가 적용되었습니다!", vbInformation
End Sub

• .ClearAllFilters → 기존 필터를 제거하고 특정 값만 필터링.

4. 피벗 테이블 업데이트 및 삭제

(1) 피벗 테이블 업데이트 (데이터 변경 후 새로고침)

Sub RefreshPivotTable()
    Dim wsPivot As Worksheet
    Dim pt As PivotTable

    Set wsPivot = ThisWorkbook.Sheets("PivotTableSheet")
    Set pt = wsPivot.PivotTables("MyPivotTable")

    ' 데이터 업데이트
    pt.RefreshTable

    MsgBox "피벗 테이블이 업데이트되었습니다!", vbInformation
End Sub

• 원본 데이터가 변경되면 실행하여 반영.

(2) 피벗 테이블 삭제

Sub DeletePivotTable()
    Dim wsPivot As Worksheet
    Dim pt As PivotTable

    Set wsPivot = ThisWorkbook.Sheets("PivotTableSheet")

    ' 특정 피벗 테이블 삭제
    For Each pt In wsPivot.PivotTables
        pt.TableRange2.Clear ' 피벗 테이블 삭제
    Next pt

    MsgBox "피벗 테이블이 삭제되었습니다!", vbInformation
End Sub

• .Clear를 사용하여 테이블을 삭제 가능.

5. 결론

• 피벗 테이블 생성: PivotCaches.Create 및 CreatePivotTable 사용.
• 필드 추가 및 수정: PivotFields("필드명").Orientation = xlRowField 등 사용.
• 데이터 요약 변경: .Function = xlSum, xlAverage, xlCount 등 사용.
• 필터 및 정렬: .ClearAllFilters, .CurrentPage = "필터값" 적용.
• 업데이트 및 삭제: .RefreshTable을 사용하여 새로고침, .Clear로 삭제.

이제 VBA를 활용하여 피벗 테이블을 자동화할 수 있습니다! 🚀