excel-vba
피벗 테이블

비고

피벗 테이블 만들기

Excel에서 가장 강력한 기능 중 하나는 피벗 테이블을 사용하여 데이터를 정렬하고 분석하는 것입니다. 피벗 테이블과 피벗 캐시의 관계를 이해하고 테이블의 다른 부분을 참조하고 사용하는 방법을 이해하면 VBA를 사용하여 피벗을 만들고 조작하는 것이 더 쉽습니다.

가장 기본적인 소스 데이터는 Worksheet 의 데이터 Range 영역입니다. 이 데이터 영역 은 범위의 첫 번째 행으로 헤더 행이있는 데이터 열을 식별 해야 합니다. 피벗 테이블이 만들어지면 사용자는 언제든지 원본 데이터를보고 변경할 수 있습니다. 그러나 피벗 테이블 자체에 직접 연결되는 피벗 캐시라는 중간 데이터 저장소 구조가 있기 때문에 변경 내용이 피벗 테이블 자체에 자동 또는 즉시 반영되지 않을 수 있습니다.

동일한 원본 데이터를 기반으로 여러 피벗 테이블이 필요한 경우 피벗 캐시를 각 피벗 테이블의 내부 데이터 저장소로 다시 사용할 수 있습니다. 이는 메모리를 절약하고 저장을 위해 Excel 파일의 크기를 줄이기 때문에 좋은 방법입니다.

예를 들어, 위 그림에 표시된 소스 데이터를 기반으로 피벗 테이블을 만들려면 다음을 수행하십시오.

Sub test()
    Dim pt As PivotTable
    Set pt = CreatePivotTable(ThisWorkbook.Sheets("Sheet1").Range("A1:E15"))
End Sub

Function CreatePivotTable(ByRef srcData As Range) As PivotTable
    '--- creates a Pivot Table from the given source data and
    '    assumes that the first row contains valid header data
    '    for the columns
    Dim thisPivot As PivotTable
    Dim dataSheet As Worksheet
    Dim ptSheet As Worksheet
    Dim ptCache As PivotCache
    
    '--- the Pivot Cache must be created first...
    Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                  SourceData:=srcData)
    '--- ... then use the Pivot Cache to create the Table
    Set ptSheet = ThisWorkbook.Sheets.Add
    Set thisPivot = ptCache.CreatePivotTable(TableDestination:=ptSheet.Range("A3"))
    Set CreatePivotTable = thisPivot
End Function

참고 MSDN 피벗 테이블 개체

피벗 테이블 범위

이 우수한 참조 소스는 Pivot Tables의 다양한 범위에 대한 설명과 그림을 제공합니다.

참고 문헌

• JBA Peltier의 기술 블로그 에서 VBA의 피벗 테이블 범위 참조
• VBA를 사용하여 Excel 피벗 테이블 범위 참조 - globaliconnect에서 Excel VBA

피벗 테이블에 필드 추가

피벗 테이블에 필드를 추가 할 때 유의해야 할 두 가지 중요한 점은 오리엔테이션 및 위치입니다. 때때로 개발자는 필드가 배치되는 위치를 가정 할 수 있으므로 이러한 매개 변수를 명시 적으로 정의하는 것이 항상 명확합니다. 이러한 작업은 피벗 캐시가 아니라 지정된 피벗 테이블에만 영향을줍니다.

Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField

Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)

With thisPivot
    Set ptField = .PivotFields("Gender")
    ptField.Orientation = xlRowField
    ptField.Position = 1
    Set ptField = .PivotFields("LastName")
    ptField.Orientation = xlRowField
    ptField.Position = 2
    Set ptField = .PivotFields("ShirtSize")
    ptField.Orientation = xlColumnField
    ptField.Position = 1
    Set ptField = .AddDataField(.PivotFields("Cost"), "Sum of Cost", xlSum)
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
End With

피벗 테이블 데이터 서식 지정

이 예제는 주어진 피벗 테이블의 데이터 범위 영역 ( DataBodyRange )에서 여러 형식을 변경 / 설정합니다. 표준 Range 모든 형식화 가능 매개 변수를 사용할 수 있습니다. 데이터 서식을 지정하면 피벗 테이블 자체에만 영향을 미치고 피벗 캐시에는 영향을 미치지 않습니다.

참고 : 속성 이름 TableStyle2 때문에 TableStyle 속성이의 구성원이 아닌 PivotTable 의 개체 속성.

Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField

Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)

With thisPivot
    .DataBodyRange.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)"
    .DataBodyRange.HorizontalAlignment = xlRight
    .ColumnRange.HorizontalAlignment = xlCenter
    .TableStyle2 = "PivotStyleMedium9"
End With