VBA로 피벗 테이블 건들기

Step 1: 예제 데이터와 피벗 테이블

피벗 테이블에 사용할 간단한 예제 테이블(데이터)을 먼저 만들어 둡니다.

이 데이터를 가지고 아래와 같은 형태의 피벗 테이블을 만들어 보세요. ("이 데이터를 가지고 피벗 테이블을 어떻게 만드는 건데요?" 이런 분도 얼른 X0037, 38, 68, 69, 70 강좌를 읽어보고 다시 오시기 바랍니다)

아이엑셀러

Step 2: 매크로 기록기로 코드 만들기

만드시겠지요? 몇 번 연습해 본 다음, 이번에는 "매크로 기록기"를 사용하여 VBA 코드를 자동으로 만들어 보도록 하겠습니다. "도구-매크로-새 매크로 기록" 메뉴를 선택하신 다음, 위와 같은 피벗 테이블을 작성해 보세요. 매크로 기록기는 일종의 녹음기이므로 불필요한 키보드나 마우스 조작은 삼가해야 한다는 것은 아시지요?

제대로 기록을 하였다면 아마 아래와 같은 코드가 자동으로 생성되어져 있을 것입니다. 항상 생각하는 것이지만 엑셀(VBA)은 대단하다는 생각이 듭니다. 마우스로 버튼만 몇 번 딸깍 거렸을 뿐인데, 어떻게 그것을 자동화 시켜주는 것인지…

너무 바쁜 나머지 피벗 테이블을 만들 시간조차 없는 분을 위해 내친 김에 코드까지 만들어 드리도록 하지요. 참 친절도 하다!!!

Sub Macro1()
'
' Macro1 Macro
' Exceller이(가) 2001-12-05에 기록한 매크로
'

'
    Range("A35").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Preface!R34C1:R88C4").CreatePivotTable TableDestination:="", TableName:= _
        "피벗 테이블1"
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("피벗 테이블1").AddFields RowFields:="Branch", _
        ColumnFields:="Month", PageFields:="BrandName"
    ActiveSheet.PivotTables("피벗 테이블1").PivotFields("Sales_AMT").Orientation = _
        xlDataField
    ActiveWorkbook.ShowPivotTableFieldList = True
End Sub

매크로 기록기를 이용하면 좋긴 한데… 모든 것을 곧이 곧대로 기록하니까 이대로는 뭐가 뭔지 파악하기가 쉽지 않습니다. 따라서 이것을 수정해 주어야 하는데 그러기 위해서는 피벗 테이블을 구성하고 있는 오브젝트나 프로퍼티 등에 대한 기본적인 지식이 좀 필요합니다.

위의 코드 중에서 노란색으로 표기한 다섯 개의 오브젝트(내지는 컬렉션), 즉 PivotCaches, CreatePivotTable, PivotTableWizard, PivotTables, PivotFields에 대해서는 도움말을 반드시 찾아보시기 바랍니다.

Step 3: 좀더 효율적인 코드로

이제 위의 단순 무식한 코드를 좀더 효율적인 코드로 바꾸어 보도록 합니다. VBA에 숙달이 되면 오브젝트의 프로퍼티나 메서드 등이 생각나지 않을 때 잠깐씩 매크로 기록기를 이용하지 위에서와 같은 방법은 사실 잘 사용하지 않습니다(Exceller의 주관적인 판단… ^^).

아래의 버튼을 눌러 보세요.

매크로 기록기를 사용해서 작성한 코드와 동일한 작업을 수행하지요? 코드를 보도록 하지요. 뭔지 모르지만 훨씬 간결하고 잘 이해할 수 있을 것 같은 기분이 드시지요? 이 코드가 위의 코드보다 훨씬 효율적이라고 할 수 있을 것입니다.

Sub CreatePivotTableByExceller()
    Dim pvtPCache As PivotCache
    Dim pvtPTable As PivotTable
    Dim rngStart As Range
    Set rngStart = Sheets("Preface").[A35]

    Set pvtPCache = ActiveWorkbook.PivotCaches.Add _
        (SourceType:=xlDatabase, _
        SourceData:=rngStart.CurrentRegion.Address)
    Worksheets.Add
    Set pvtPTable = pvtPCache.CreatePivotTable _
        (tabledestination:=ActiveSheet.[A3], _
        tablename:="피벗 테이블1")

    With pvtPTable
        .PivotFields("BrandName").Orientation = xlPageField
        .PivotFields("Branch").Orientation = xlRowField
        .PivotFields("Month").Orientation = xlColumnField
        .PivotFields("Sales_AMT").Orientation = xlDataField
    End With
End Sub

코드 자체는 별도의 설명이 필요없을 듯 합니다. 이해가 안되는 부분이 있으면 질문주시기 바랍니다. 다음 시간에는 이 코드를 가지고 좀더 응용을 할 것이니까 잘 살펴보시기 바랍니다. 그리고 한번 보고 휙 집어던지지 말고 잘 보관해 두세요.

오늘은 여기까지…

정리 — 피벗 테이블 VBA 핵심

자주 묻는 질문 (FAQ)

마치며

피벗 테이블은 엑셀에서 ROI가 가장 높은 기능이며, VBA로 다루면 그 활용 범위가 더욱 넓어집니다.