피벗 테이블 생성하기

WorkSheet.PivotTableWizard method

새 피벗 테이블 보고서를 만듭니다. 이 방법은 피벗 테이블 마법사를 표시하지 않습니다. OLE DB 데이터 원본에는 이 방법을 사용할 수 없습니다. Add 메서드 를 사용하여 피벗 테이블 캐시를 추가한 다음 캐시를 기반으로 피벗 테이블 보고서를 만듭니다.

Syntax
expression.PivotTableWizard (SourceType, SourceData, TableDestination, TableName, RowGrand, ColumnGrand, SaveData, HasAutoFormat, AutoPage, Reserved, BackgroundQuery, OptimizeCache, PageFieldOrder, PageFieldWrapCount, ReadData, Connection)

매개변수를 사용할 때마다 하나씩 설명하겠습니다.

Sub CreatePivotTable()

    Dim wsSource As Worksheet
    Dim rng As Range
    Dim wsDestination As Worksheet
    Dim pt As PivotTable

    ' 데이터가 있는 워크시트 설정
    Set wsSource = ThisWorkbook.Sheets("Sheet1")

    ' 데이터 범위 설정
    Set rng = wsSource.Range("A1").CurrentRegion

    ' 새로운 워크시트 생성 및 설정
    Set wsDestination = ThisWorkbook.Sheets.Add

    ' B3에 PivotTable 생성
    Set pt = wsSource.PivotTableWizard(SourceType:=xlDatabase, SourceData:=rng, TableDestination:=wsDestination.Range("B3"))


    ' PivotTable 생성 후 개체 참조 해제
    Set pt = Nothing
    Set rng = Nothing
    Set wsDestination = Nothing
    Set wsSource = Nothing
    
End Sub

위 코드의 실행 결과는 아래와 같습니다.(클래식 피벗 테이블 레이아웃)

SourceType은 보고서 원본으로 XlPivotTableSourceType 값입니다.

xlDatabase는 Microsoft Excel 목록 또는 데이터베이스를 의미합니다.

더 자세한 것은 아래 도움말을 참고하세요.

XlPivotTableSourceType enumeration (Excel)

Office VBA reference topic

learn.microsoft.com

다음은 Access 데이터베이스를 원본으로 피벗 테이블을 만드는 예제입니다.(클래식 피벗 테이블 레이아웃)

xlExternal은 외부데이터를 의미합니다.

Sub PivotTable_External1()

    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim pt As PivotTable
    Dim strConn As String
    Dim myArray As Variant
    Dim sSql As String

    ' 데이터가 있는 워크시트 설정
    Set wsSource = ThisWorkbook.Sheets("Sheet1")

    ' 새로운 워크시트 생성 및 설정
    Set wsDestination = ThisWorkbook.Sheets.Add

    ' 외부 데이터 소스 연결 문자열 설정
    strConn = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
              "DBQ=D:\download\Northwind.accdb;"

    ' SQL 쿼리 설정
    sSql = "SELECT * FROM [Sales by Category]"

    myArray = Array(strConn, sSql)

    ' B3에 PivotTable 생성
    Set pt = wsSource.PivotTableWizard(SourceType:=xlExternal, SourceData:=myArray, TableDestination:=wsDestination.Range("B3"))

    ' PivotTable 생성 후 개체 참조 해제
    Set pt = Nothing
    Set wsDestination = Nothing
    Set wsSource = Nothing

End Sub

다음은 SQL-Server 원본으로 피벗 테이블을 만드는 예제입니다.(클래식 피벗 테이블 레이아웃)

* 호기심에 만들어봤을뿐 보안상 문제가 되므로 절대 따라하지 마세요.

Sub PivotTable_External2()

    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim pt As PivotTable
    Dim strConn As String
    Dim myArray As Variant
    Dim sSql As String

    ' 데이터가 있는 워크시트 설정
    Set wsSource = ThisWorkbook.Sheets("Sheet1")

    ' 새로운 워크시트 생성 및 설정
    Set wsDestination = ThisWorkbook.Sheets.Add

    ' 외부 데이터 소스 연결 문자열 설정
    strConn = "Driver={SQL Server};" & _
              "Server=localhost;" & _
              "Database=Your_Database_Name;" & _
              "Uid=Your_Username;" & _
              "Pwd=Your_Password;"

    ' SQL 쿼리 설정
    sSql = "SELECT * FROM [Sales by Category]"

    myArray = Array(strConn, sSql)

    ' B3에 PivotTable 생성
    Set pt = wsSource.PivotTableWizard(SourceType:=xlExternal, SourceData:=myArray, TableDestination:=wsDestination.Range("B3"))

    ' PivotTable 생성 후 개체 참조 해제
    Set pt = Nothing
    Set wsDestination = Nothing
    Set wsSource = Nothing

End Sub

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

아래 그림을 보면 PivotTableWizard 메서드는 PivotTable 개체를 반환하는걸 알 수 있습니다.

PivotTable개체의 PivotFields 속성을 이용하면 필드를 추가할 수 있습니다.

PivotFields 메서드는 피벗 테이블 보고서에서 하나의 피벗 테이블 필드를 나타내는 개체(PivotField 개체)나 표시된 필드와 숨겨진 필드의 컬렉션을 나타내는 개체(PivotFields 개체)를 반환하며 읽기 전용입니다.

Sub CreatePivotTable()

    Dim wsSource As Worksheet
    Dim rng As Range
    Dim wsDestination As Worksheet
    Dim pt As PivotTable
    Dim pivotTableName As String
    ' 데이터가 있는 워크시트 설정
    Set wsSource = ThisWorkbook.Sheets("Sheet1")

    ' 데이터 범위 설정
    Set rng = wsSource.Range("A1").CurrentRegion

    ' 새로운 워크시트 생성 및 설정
    Set wsDestination = ThisWorkbook.Sheets.Add

    ' PivotTable 이름 설정
    pivotTableName = "MyPivotTable"

    ' B3에 PivotTable 생성
    Set pt = wsSource.PivotTableWizard(SourceType:=xlDatabase, _
                                       SourceData:=rng, _
                                       TableDestination:=wsDestination.Range("B3"), _
                                       TableName:=pivotTableName)

    With pt
        ' Product 필드를 행 필드로 추가
        .PivotFields("Product").Orientation = xlRowField
        ' Category 필드를 열 필드로 추가
        .PivotFields("Category").Orientation = xlColumnField
        ' SalesAmount 필드를 데이터 필드로 추가하고 데이터 합계로 설정
        With .PivotFields("SalesAmount")
            .Orientation = xlDataField
            .Function = xlSum
        End With
        ' Color 필드를 페이지 필드로 추가
        .PivotFields("Color").Orientation = xlPageField
    End With

    ' PivotTable 생성 후 개체 참조 해제
    Set pt = Nothing
    Set rng = Nothing
    Set wsDestination = Nothing
    Set wsSource = Nothing

End Sub

Orientation속성은 PivotField개체의 속성입니다.

지정한 피벗 테이블 보고서에서 필드의 위치를 나타내는 XlPivotFieldOrientation 값을 반환하거나 설정합니다.

XlPivotFieldOrientation enumeration (Excel)

Office VBA reference topic

learn.microsoft.com

위 코드는 Item 메서드가 생략된 코드입니다.원래대로라면 다음과 같습니다.

With pt
    ' Product 필드를 행 필드로 추가
    .PivotFields.Item("Product").Orientation = xlRowField
    ' Category 필드를 열 필드로 추가
    .PivotFields.Item("Category").Orientation = xlColumnField
    ' SalesAmount 필드를 데이터 필드로 추가하고 데이터 합계로 설정
    With .PivotFields.Item("SalesAmount")
        .Orientation = xlDataField
        .Function = xlSum
    End With
    ' Color 필드를 페이지 필드로 추가
    .PivotFields.Item("Color").Orientation = xlPageField
End With

PivotFields 개체의 Item 메서드는 개체 하나를 반환합니다.

PivotFields.Item method (Excel)

Office VBA reference topic

learn.microsoft.com

PivotField 개체의 Function 속성은피벗 테이블 필드(데이터 필드만)를 요약하는 데 사용되는 함수를 반환하거나 설정합니다. 읽기/쓰기가 가능한 XlConsolidationFunction입니다.

XlConsolidationFunction enumeration (Excel)

Specifies the subtotal function (Excel)

learn.microsoft.com

위 코드를 실행하면 아래와 같은 결과가 출력됩니다.

PivotTableWizard method를 이용하여 피벗테이블을 만드는 것은 자주 사용되는건 아닌 것 같아서

하나만 더 설명하고 이 글을 마무리합니다.나머지는 다른 글에서 더 자세히 설명하겠습니다.

위 그림을 보면 총 합계가 표시되어 있습니다.나타나지 않게 하려면 다음과 같이 코드를 수정합니다.

Sub CreatePivotTable()

    Dim wsSource As Worksheet
    Dim rng As Range
    Dim wsDestination As Worksheet
    Dim pt As PivotTable
    Dim pivotTableName As String
    ' 데이터가 있는 워크시트 설정
    Set wsSource = ThisWorkbook.Sheets("Sheet1")

    ' 데이터 범위 설정
    Set rng = wsSource.Range("A1").CurrentRegion

    ' 새로운 워크시트 생성 및 설정
    Set wsDestination = ThisWorkbook.Sheets.Add

    ' PivotTable 이름 설정
    pivotTableName = "MyPivotTable"

    ' B3에 PivotTable 생성
    Set pt = wsSource.PivotTableWizard(SourceType:=xlDatabase, _
                                       SourceData:=rng, _
                                       TableDestination:=wsDestination.Range("B3"), _
                                       TableName:=pivotTableName, RowGrand:=False, ColumnGrand:=False)

    With pt
        ' Product 필드를 행 필드로 추가
        .PivotFields("Product").Orientation = xlRowField
        ' Category 필드를 열 필드로 추가
        .PivotFields("Category").Orientation = xlColumnField
        ' SalesAmount 필드를 데이터 필드로 추가하고 데이터 합계로 설정
        With .PivotFields("SalesAmount")
            .Orientation = xlDataField
            .Function = xlSum
        End With
        ' Color 필드를 페이지 필드로 추가
        .PivotFields("Color").Orientation = xlPageField
    End With

    ' PivotTable 생성 후 개체 참조 해제
    Set pt = Nothing
    Set rng = Nothing
    Set wsDestination = Nothing
    Set wsSource = Nothing

End Sub

수정된 부분은 다음과 같습니다.

Set pt = wsSource.PivotTableWizard(SourceType:=xlDatabase, _
                                       SourceData:=rng, _
                                       TableDestination:=wsDestination.Range("B3"), _
                                       TableName:=pivotTableName, RowGrand:=False, ColumnGrand:=False)

아래 그림은 출력 결과입니다.

피벗 테이블 생성하기 - Add 메서드

피벗 테이블 생성하기

피벗 테이블 분석

슬라이서를 사용하여 데이터 필터링

저작자표시 비영리 변경금지 (새창열림)

'PivotTable' 카테고리의 다른 글

슬라이서를 사용하여 데이터 필터링 (0)	2023.09.26
피벗 테이블 분석 (0)	2023.09.23
피벗 테이블 생성하기 (0)	2023.09.20
피벗 테이블 생성하기 - Add 메서드 (0)	2023.09.19