VBA로 피벗 테이블 건들기 2

Step 1: 예제 데이터

아래 데이터로 피벗 테이블을 만듭니다. 이번에는 월 이름을 Jan, Feb, Mar… 로 입력하였고 1월부터 6월까지의 데이터가 들어 있습니다.

피벗 테이블이 생성되는데… 지난 시간에 만들었던 것과 비교해서 어떤 점이 다를까요?... 그렇습니다. 분기계, 상반기 데이터가 추가가 되었지요? 분명히 위의 소스 테이블에는 월별 데이터만 있고 분기, 반기별 데이터는 없는데 말입니다.

Step 2: CreatePivotTableByExceller2 프로시저

코드를 볼까요?

Sub CreatePivotTableByExceller2()
    Dim pvtPCache As PivotCache
    Dim pvtPTable As PivotTable
    Dim shtSheet As Worksheet
    Dim rngStart As Range, rngButton As Range
    Set rngStart = Sheets("Preface").[A22]
    '''변수를 선언하는 거야 어느 강좌에서나 그랬던 것처럼 별로 다를 것이 없습니다.
    '''다만 PivotCache, PivotTable 등과 같이 낯선 변수 타입, 오브젝트 등이 나오면 항상
    '''도움말을 참고해서 정리를 해 두시기 바랍니다. 오기(?)로 아직 도움말을 찾아보지
    '''않은 분들! 아직 늦지 않았으니까 얼른 도움말을 찾아보세요!!!!
    '''오기도 부릴 때 부려야지요! ^^;

    For Each shtSheet In ThisWorkbook.Sheets
        If shtSheet.Name = "PivotSheet" Then
            MsgBox "같은 시트가 이미 있으므로 지웁니다!", , "동일 시트 발견//By Exceller"
            Application.DisplayAlerts = False
            shtSheet.Delete
            Application.DisplayAlerts = True
        End If
    Next shtSheet

    Worksheets.Add.Name = "PivotSheet"
    '''PivotSheet라는 시트가 이미 있을 경우, 지우고 새로운 PivotSheet 시트를 만듭니다.

    Set pvtPCache = ActiveWorkbook.PivotCaches.Add _
        (SourceType:=xlDatabase, _
        SourceData:=rngStart.CurrentRegion.Address)
    Set pvtPTable = pvtPCache.CreatePivotTable _
        (tabledestination:=Sheets("PivotSheet").[A1], _
        tablename:="MyPivot1")
    '''이 부분은 어제와 거의 같습니다. 엄밀히 말하면 약간 다르긴 한데… 어디가 다를까요?
    '''눈썰미가 있거나 열심히 공부를 하시는 분이라면 금방 아실 터이므로 답은 생략! ^^

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

        .PivotFields("Month").CalculatedItems.Add "1/4분기", "= Jan+Feb+Mar"
        .PivotFields("Month").CalculatedItems.Add "2/4분기", "= Apr+May+Jun"
        .PivotFields("Month").CalculatedItems.Add "상반기", "= Jan+Feb+Mar+Apr+May+Jun"
        '''CalculatedItem 라는 컬렉션 오브젝트를 사용하여 각각의 월을 합산하여 분기계,
        '''또는 반기계를 구하였습니다.

        .PivotFields("Month").PivotItems("1/4분기").Position = 4
        .PivotFields("Month").PivotItems("2/4분기").Position = 8
        .PivotFields("Month").PivotItems("상반기").Position = 9
        '''1/4분기, 2/4분기의 위치를 3월 또는 6월 다음으로 이동시킵니다.
        '''이 때 Pivot Table 개체의 Position 속성을 사용하는군요.

        '''아래의 코드는 피벗 테이블이 있는 시트 상단에 돌아가기 버튼을 만들어 주기
        '''위한 것입니다. 그동안 많이 다루어 왔으므로 크게 어려운 부분은 없을 것이므로
        '''해설은 생략합니다.
    End With
    On Error Resume Next
    Application.CommandBars("PivotTable").Visible = False
    On Error GoTo 0

    Set rngButton = ActiveSheet.[E1]
    With rngButton
        ActiveSheet.Buttons.Add(.Left, .Top, .Width * 2, .Height).Select
    End With
    With Selection
        .Caption = "<<Go Back!!!"
        .OnAction = "GoBack"
        With .Characters(Start:=1).Font
            .Name = "바탕"
            .FontStyle = "보통"
            .Size = 10
            .ColorIndex = 3
        End With
    End With
    rngButton.Select
End Sub

변수를 선언하는 거야 어느 강좌에서나 그랬던 것처럼 별로 다를 것이 없습니다. 다만 PivotCache, PivotTable 등과 같이 낯선 변수 타입, 오브젝트 등이 나오면 항상 도움말을 참고해서 정리를 해 두시기 바랍니다. 오기(?)로 아직 도움말을 찾아보지 않은 분들! 아직 늦지 않았으니까 얼른 도움말을 찾아보세요!!!! 오기도 부릴 때 부려야지요! ^^;

PivotSheet라는 시트가 이미 있을 경우, 지우고 새로운 PivotSheet 시트를 만듭니다. 그 다음 부분은 어제와 거의 같습니다. 엄밀히 말하면 약간 다르긴 한데… 어디가 다를까요? 눈썰미가 있거나 열심히 공부를 하시는 분이라면 금방 아실 터이므로 답은 생략! ^^

CalculatedItem 라는 컬렉션 오브젝트를 사용하여 각각의 월을 합산하여 분기계, 또는 반기계를 구하였습니다. 그리고 1/4분기, 2/4분기의 위치를 3월 또는 6월 다음으로 이동시키는데, 이때 Pivot Table 개체의 Position 속성을 사용하는군요. 아래쪽 코드는 피벗 테이블이 있는 시트 상단에 돌아가기 버튼을 만들어 주기 위한 것입니다. 그동안 많이 다루어 왔으므로 크게 어려운 부분은 없을 것이므로 해설은 생략합니다.

돌아가기 버튼에 연결된 GoBack 프로시저는 다음과 같이 원래 시트로 돌아가는 간단한 프로시저입니다.

Sub GoBack()
    Sheets("Preface").Activate
End Sub

재미있지요? 읽다가 이해가 안되는 부분이나 피벗 테이블과 관련하여 궁금한 것이 있는 분은 질문을 주시기 바랍니다. 아무래도 현재 진행 중인 강좌와 관련있는 질문을 하시면 더 빠른 답변을 받으실 수 있을 것 같은 생각이 드시지요? ^^

아울러 최근에 Exceller의 개인 메일로 질문을 하신 분들은 아마 거의 답변을 받지 못하셨을 것입니다. 답변하기 싫어서 않는 것이 아니고 연말이 되니 이것 저것 신경을 쓸 일이 많아서 제대로 답변드리기가 힘이 들어서 입니다. 널리 이해하시고 질문은 가급적 Exceller 홈페이지의 Q&A 게시판을 이용해 주시기 바랍니다.

다음 시간에 또…

정리 — 피벗 계산 항목 핵심

자주 묻는 질문 (FAQ)

마치며

피벗 테이블의 계산 항목을 VBA로 다루면 소스에 없는 분기계와 반기계도 손쉽게 표시할 수 있습니다.