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

이 방법은 오피스 2003 버전에서 매크로 기록하면 볼 수 있습니다.

 

피벗 테이블 생성하기

PivotCaches.Add method

새 피벗 테이블 캐시를 PivotCaches 컬렉션에 추가합니다. PivotCache 개체를 반환합니다.

 

Syntax

expression.Add(SourceType, SourceData)

 

expression : 필수 요소로서 PivotCaches 개체를 반환하는 식입니다.

 

SourceType : 필수 요소로서 XlPivotTableSourceType 형식입니다. 피벗 테이블 캐시 데이터의 원본입니다.

XlPivotTableSourceType은 다음 XlPivotTableSourceType 상수 중 하나를 사용할 수 있습니다. 

 

XlPivotTableSourceType Enum (Microsoft.Office.Interop.Excel)

SourceData : 선택 요소로서 Variant 형식입니다. 새 피벗 테이블 캐시의 데이터입니다. SourceType이 xlExternal이 아니면 이 인수는 필수 요소입니다. 기존의 피벗 테이블 보고서의 이름을 나타내는 텍스트 상수, 범위 배열, Range 개체 등을 사용할 수 있습니다. 외부 데이터베이스에서 이 인수는 두 요소를 가지는 배열입니다. 첫째 요소는 데이터 공급자를 지정하는 연결 문자열입니다. 두 번째 요소는 데이터를 얻는 데 사용되는 SQL 쿼리 문자열입니다. 이 인수를 지정하면 SourceType도 지정해야 합니다.

 

주의

피벗 테이블 캐시가 PivotTable 개체로 참조되지 않으면 피벗 테이블 캐시는 통합 문서가 저장되기 전에 자동으로 삭제됩니다.

 

다음 코드는 새 시트의 B3셀에 MyPivotTable이라는 이름으로 피벗 테이블을 생성합니다.

Sub CreatePivotTable()
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim pivotTableName As String
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim dataRange As Range
    
    ' 작업할 워크시트 설정
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    
    ' 데이터 범위 설정
    Set dataRange = wsSource.Range("A1").CurrentRegion
    
    ' 새로운 워크시트 생성 및 설정
    Set wsDestination = ThisWorkbook.Sheets.Add
    
    ' PivotTable 이름 설정
    pivotTableName = "MyPivotTable"
    
    ' PivotCache 생성
    Set pc = ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=dataRange)
    
    ' PivotTable 생성
    Set pt = pc.CreatePivotTable(TableDestination:=wsDestination.Cells(3, 2), TableName:=pivotTableName, DefaultVersion:=xlPivotTableVersion10)
    
    Set pt = Nothing
    Set pc = Nothing
    Set dataRange = Nothing
    Set wsDestination = Nothing
    Set wsSource = Nothing

End Sub

 

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

다음 코드는 행 필드에 "Product", 열 필드에 Category",페이지 필드에 "Color" 값 필드에 "SalesAmount"를 사용합니다.값 필드 설정은 합계입니다.

Sub CreatePivotTable()
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim pivotTableName As String
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim dataRange As Range
    
    ' 작업할 워크시트 설정
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    
    ' 데이터 범위 설정
    Set dataRange = wsSource.Range("A1").CurrentRegion
    
    ' 새로운 워크시트 생성 및 설정
    Set wsDestination = ThisWorkbook.Sheets.Add
    
    ' PivotTable 이름 설정
    pivotTableName = "MyPivotTable"
    
    ' PivotCache 생성
    Set pc = ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=dataRange)
    
    ' PivotTable 생성
    Set pt = pc.CreatePivotTable(TableDestination:=wsDestination.Cells(3, 2), TableName:=pivotTableName, DefaultVersion:=xlPivotTableVersion10)
    
    ' PivotTable 필드 설정
    With pt
        .AddFields RowFields:="Product", ColumnFields:="Category", PageFields:="Color"
        .PivotFields("SalesAmount").Orientation = xlDataField
    End With
    
    Set pt = Nothing
    Set pc = Nothing
    Set dataRange = Nothing
    Set wsDestination = Nothing
    Set wsSource = Nothing

End Sub

이전 글과는 달리 PivotTable 개체의 AddFields 메서드를 사용했습니다.

 

PivotTable.AddFields 메서드

 

행, 열, 페이지 필드를 피벗 테이블 보고서나 피벗 차트 보고서에 추가합니다.

 

Syntax
expression.AddFields (RowFields, ColumnFields, PageFields, AddToTable)

 

RowFields : 행으로 추가되거나 항목 축에 추가될 필드 이름(또는 필드 이름의 배열)을 지정합니다.

ColumnFields : 열에 추가되거나 계열 축에 추가될 필드 이름(또는 필드 이름의 배열)을 지정합니다.

PageFields : 페이지에 추가되거나 페이지 영역에 추가될 필드 이름(또는 필드 이름의 배열)을 지정합니다.

AddToTable : 피벗 테이블 보고서에만 적용됩니다. True이면 지정한 필드를 보고서에 추가합니다(기존 필드를 바꾸지 않음). False이면 기존 필드를 새 필드로 바꿉니다. 기본값은 False입니다.

 

* 모두 선택사항이고 데이터 타입은 Variant입니다.

 

비고
필드 인수 중 하나는 반드시 지정해야 합니다.

필드 이름은 PivotField 개체의 SourceName 속성에서 반환되는 고유한 이름을 지정합니다.

이 메서드는 OLAP (OLAP: 트랜잭션 처리 대신 쿼리와 보고서 기능을 위해 최적화된 데이터베이스 기술입니다. OLAP 데이터는 테이블 대신 큐브에 계층적으로 구성되고 저장됩니다.) 데이터 원본에서는 사용할 수 없습니다.

PivotTable.PivotFields 메서드

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

 

Syntax
expression.PivotFields (Index)

 

expression : PivotTable 개체를 반환하는 식입니다.

 

Index : 반환할 필드의 이름이나 번호입니다.

비고
OLAP (OLAP: 트랜잭션 처리 대신 쿼리와 보고서 기능을 위해 최적화된 데이터베이스 기술입니다. OLAP 데이터는 테이블 대신 큐브에 계층적으로 구성되고 저장됩니다.) 데이터 원본의 경우에는 숨겨진 필드가 없으므로 반환되는 개체나 컬렉션은 현재 표시된 필드를 반영합니다.

 

위 코드에서는 SalesAmount를 데이터(값 필드)로 설정했습니다.

위 코드를 실행한 결과는 다음과 같습니다.

이 방법도 구식이라 더 이상 설명하기 보다는 다음 글에서 본격적으로 피벗 테이블에 대해 다루겠습니다.

 

관련 글

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

피벗 테이블 생성하기

피벗 테이블 분석

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