피벗 테이블 생성하기

아래 코드는 M365에서 매크로 기록한 코드입니다.모든 설정이 기록되어서 불필요한 부분이 많습니다.

Sub Macro1()
'
' Macro1 Macro
'

'
    Application.CutCopyMode = False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R131C8", Version:=8).CreatePivotTable TableDestination:= _
        "Sheet3!R3C1", TableName:="PivotTable1", DefaultVersion:=8
    Sheets("Sheet3").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("SalesAmount"), "Sum of SalesAmount", xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Color")
        .Orientation = xlPageField
        .Position = 1
    End With
End Sub

 

하나씩 정리하면서 코드를 다시 작성해 보겠습니다.

피벗 테이블 생성하기

PivotCaches.Create method

새 PivotCache를 만듭니다.

 

Syntax
expression.Create(SourceType, SourceData, Version)

 

expression : PivotCaches 개체를 나타내는 변수입니다.

 

XlPivotTableSourceType enumeration (Excel)

 

XlPivotTableVersionList enumeration (Excel)

 

비고
이 메서드를 사용하여 피벗 캐시를 생성할 때 다음 두 개의 XlPivotTableSourceType 상수는 지원되지 않습니다: xlPivotTable 및 xlScenario. 이 두 상수 중 하나를 제공하면 런타임 오류가 반환됩니다.

SourceType이 xlExternal이 아닌 경우 SourceData 인수가 필요합니다. 이 인수는 Range 개체(SourceType이 xlConsolidation 또는 xlDatabase인 경우) 또는 WorkbookConnection 개체(SourceType이 xlExternal인 경우)를 전달해야 합니다.

Range 개체를 전달할 때 문자열을 사용하여 통합 문서, 워크시트 및 셀 범위를 지정하거나 명명된 범위를 설정하고 이름을 문자열로 전달하는 것이 좋습니다. 범위 개체를 전달하면 예기치 않게 "유형 불일치" 오류가 발생할 수 있습니다.

제공되지 않은 경우 피벗 테이블의 버전은 xlPivotTableVersion12가 됩니다. xlPivotTableVersionCurrent 상수를 사용할 수 없으며, 이 상수를 제공하면 런타임 오류가 반환됩니다.

 

피벗 캐시 : Microsoft Office Excel에서 보고서의 데이터를 저장하는 데 사용하는 컴퓨터의 내부 메모리 영역입니다.

PivotCache.CreatePivotTable method

PivotCache 개체를 기반으로 피벗 테이블 보고서를 만듭니다. PivotTable 개체를 반환합니다.

 

Syntax
expression.CreatePivotTable(TableDestination, TableName, ReadData, DefaultVersion)

 

expression : PivotCache 개체를 나타내는 변수입니다.

 

TableDestination : 피벗 테이블 보고서의 대상 범위(워크시트에서 결과 피벗 테이블 보고서가 배치될 범위)의 왼쪽 위 모서리에 있는 셀입니다. 대상 범위는 표현식으로 지정된 피벗 캐시 개체가 포함된 통합 문서의 워크시트에 있어야 합니다.

TableName : 새 피벗 테이블 보고서의 이름입니다.

ReadData : True로 설정하면 외부 데이터베이스의 모든 레코드가 포함된 피벗 테이블 캐시가 생성되며, 이 캐시는 매우 클 수 있습니다. 데이터를 실제로 읽기 전에 일부 필드를 서버 기반 페이지 필드로 설정하려면 False입니다.

DefaultVersion : 피벗 테이블 보고서의 기본 버전입니다.

 

다음 코드는 피벗 캐시를 만들고 Sheet1의 범위를 이용하여 피벗 테이블을 생성하는 예제입니다.

Sub CreatePivotTable()
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim rngData As Range
    Dim pivotCache As pivotCache
    Dim pivotTable As pivotTable

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

    ' 새로운 워크시트 생성
    Set wsPivot = ThisWorkbook.Sheets.Add
    wsPivot.Name = "PivotSheet"

    ' 데이터 범위 선택
    Set rngData = wsData.Range("A1").CurrentRegion

    ' 피벗 캐시 생성
    Set pivotCache = ThisWorkbook.PivotCaches.Create( _
                     SourceType:=xlDatabase, _
                     SourceData:=rngData)

    ' 피벗 테이블 생성
    Set pivotTable = pivotCache.CreatePivotTable( _
                     TableDestination:=wsPivot.Range("B3"), _
                     TableName:="PivotTable")

End Sub

 

PivotSheet가 이미 존재하면 다시 실행했을때 에러가 납니다.다음 코드는 수정된 코드입니다.

Option Explicit

Sub CreatePivotTable()
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim rngData As Range
    Dim pivotCache As pivotCache
    Dim pivotTable As pivotTable

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

    ' "PivotSheet"라는 이름의 시트가 이미 존재하는지 확인하고 삭제하기
    If WorksheetExists("PivotSheet") Then
        Application.DisplayAlerts = False    ' 경고메세지 무시
        ThisWorkbook.Sheets("PivotSheet").Delete    ' 해당 시트 삭제하기
        Application.DisplayAlerts = True  ' 경고메세지 다시 활성화
    Else
    End If

    Set wsPivot = ThisWorkbook.Sheets.Add  ' 이제 안전하게 피벗테이블을 위한 새로운 시트를 추가할 수 있습니다.
    wsPivot.Name = "PivotSheet"

    ' 데이터 범위 선택
    Set rngData = wsData.Range("A1").CurrentRegion

    ' 피벗 캐시 생성
    Set pivotCache = ThisWorkbook.PivotCaches.Create( _
                     SourceType:=xlDatabase, _
                     SourceData:=rngData)

    ' 피벗 테이블 생성
    Set pivotTable = pivotCache.CreatePivotTable( _
                     TableDestination:=wsPivot.Range("B3"), _
                     TableName:="MyNewPT")

End Sub

Function WorksheetExists(ByVal worksheetName As String) As Boolean
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(worksheetName)

    On Error GoTo 0

    WorksheetExists = Not ws Is Nothing

End Function

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

PivotTable.PivotFields method

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

 

Syntax
expression.PivotFields(Index)

 

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

 

index : 선택사항입니다. 반환할 필드의 이름이나 번호입니다.

PivotField 개체

피벗 테이블 보고서의 필드를 나타냅니다.

 

비고
PivotField 개체는 PivotFields 컬렉션의 구성원입니다. PivotFields 컬렉션에는 숨겨진 필드를 포함하여 피벗 테이블 보고서에 있는 필드가 모두 들어 있습니다.

피벗 테이블 필드를 일부만 반환하는 속성을 사용하는 것이 더 편리한 경우도 있습니다. 다음과 같은 속성을 사용할 수 있습니다.

 

● ColumnFields property
● DataFields property
● HiddenFields property
● PageFields property
● RowFields property
● VisibleFields property

 

PivotField 개체를 하나만 반환하려면 PivotFields(index)를 사용합니다. 여기에서 index는 필드 이름이나 인덱스 번호입니다.

PivotField.Orientation property

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

 

XlPivotFieldOrientation enumeration (Excel)

Syntax
expression.Orientation

 

expression : PivotField 개체를 나타내는 변수입니다.

PivotField.Position property

방향(행, 열, 페이지, 데이터)에 따라 모든 필드 중에서 필드의 위치(첫 번째, 두 번째, 세 번째 등)를 나타내는 Variant 값을 반환하거나 설정합니다.

 

Syntax
expression.Position

 

expression : PivotField 개체를 나타내는 변수입니다.

 

다음 코드는 피벗 테이블을 만들고 필드를 추가한 예제입니다.

Sub CreatePivotTable()
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim rngData As Range
    Dim pivotCache As pivotCache
    Dim pivotTable As pivotTable

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

    ' 새로운 워크시트 생성
    Set wsPivot = ThisWorkbook.Sheets.Add
    wsPivot.Name = "PivotSheet"

    ' 데이터 범위 선택
    Set rngData = wsData.Range("A1").CurrentRegion

    ' 피벗 캐시 생성
    Set pivotCache = ThisWorkbook.PivotCaches.Create( _
                     SourceType:=xlDatabase, _
                     SourceData:=rngData)

    ' 피벗 테이블 생성
    Set pivotTable = pivotCache.CreatePivotTable( _
                     TableDestination:=wsPivot.Range("B3"), _
                     TableName:="PivotTable")

    ' 필드 설정
    With pivotTable
        ' 필드 추가 및 설정
        .PivotFields("Product").Orientation = xlRowField
        .PivotFields("Category").Orientation = xlColumnField
        .PivotFields("Color").Orientation = xlPageField
    End With

End Sub

위 코드를 실행한 화면입니다.

PivotTable.AddDataField method

피벗 테이블 보고서에 데이터 필드를 추가합니다. 새 데이터 필드를 나타내는 PivotField 개체를 반환합니다.

 

Syntax
expression.AddDataField(Field, Caption, Function)

 

expression : PivotTable 개체를 나타내는 변수입니다.

 

Field : 서버의 고유 필드입니다. 소스 데이터가 OLAP(온라인 분석 처리)인 경우 고유 필드는 큐브 필드입니다. 원본 데이터가 비OLAP(비OLAP 원본 데이터)인 경우 고유 필드는 피벗 테이블 필드입니다.

Caption : 피벗 테이블 보고서에서 해당 데이터 필드를 식별하는 데 사용하는 레이블입니다.

Function : 추가된 데이터 필드에서 실행되는 함수입니다. XlConsolidationFunction 열거형 값을 사용할 수 있습니다.

 

* xlDistinctCount는 데이터 모델에 추가한 후에만 사용 가능합니다.(엑셀 2013부터 사용 가능)

XlConsolidationFunction enumeration (Excel)

 

다음 코드는 값 필드에 SalesAmount를 추가한 예제입니다.

Sub CreatePivotTable()
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim rngData As Range
    Dim pivotCache As pivotCache
    Dim pivotTable As pivotTable

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

    ' 새로운 워크시트 생성
    Set wsPivot = ThisWorkbook.Sheets.Add
    wsPivot.Name = "PivotSheet"

    ' 데이터 범위 선택
    Set rngData = wsData.Range("A1").CurrentRegion

    ' 피벗 캐시 생성
    Set pivotCache = ThisWorkbook.PivotCaches.Create( _
                     SourceType:=xlDatabase, _
                     SourceData:=rngData)

    ' 피벗 테이블 생성
    Set pivotTable = pivotCache.CreatePivotTable( _
                     TableDestination:=wsPivot.Range("B3"), _
                     TableName:="PivotTable")

    ' 필드 설정
    With pivotTable
        ' 필드 추가 및 설정
        .PivotFields("Product").Orientation = xlRowField
        .PivotFields("Category").Orientation = xlColumnField
        .PivotFields("Color").Orientation = xlPageField
        .AddDataField .PivotFields("SalesAmount"), "Sum of SalesAmount", xlSum
    End With

    ' 개체 해제
    Set pivotTable = Nothing
    Set pivotCache = Nothing
    Set rngData = Nothing
    Set wsPivot = Nothing
    Set wsData = Nothing
End Sub

위 코드를 실행한 화면입니다.

필드 추가는 다른 방법으로도 만들 수 있습니다.

 

PivotTable.AddFields method

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

 

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

 

expression : PivotTable 개체를 나타내는 변수입니다.

 

RowFields : 행으로 추가되거나 항목 축에 추가될 필드 이름(또는 필드 이름의 배열)을 지정합니다.
ColumnFields : 열에 추가되거나 계열 축에 추가될 필드 이름(또는 필드 이름의 배열)을 지정합니다.
PageFields : 페이지에 추가되거나 페이지 영역에 추가될 필드 이름(또는 필드 이름의 배열)을 지정합니다.
AddToTable : 피벗 테이블 보고서에만 적용됩니다. True이면 지정한 필드를 보고서에 추가합니다(기존 필드를 바꾸지 않음). False이면 기존 필드를 새 필드로 바꿉니다. 기본값은 False입니다.

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

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

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

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

다음 코드는 AddFields 메서드를 이용하여 필드를 추가한 예제입니다.

Sub CreatePivotTable()
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim rngData As Range
    Dim pivotCache As pivotCache
    Dim pivotTable As pivotTable

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

    ' 새로운 워크시트 생성
    Set wsPivot = ThisWorkbook.Sheets.Add
    wsPivot.Name = "PivotSheet"

    ' 데이터 범위 선택
    Set rngData = wsData.Range("A1").CurrentRegion

    ' 피벗 캐시 생성
    Set pivotCache = ThisWorkbook.PivotCaches.Create( _
                     SourceType:=xlDatabase, _
                     SourceData:=rngData)

    ' 피벗 테이블 생성
    Set pivotTable = pivotCache.CreatePivotTable( _
                     TableDestination:=wsPivot.Range("B3"), _
                     TableName:="PivotTable")

    ' 필드 설정
    With pivotTable
        ' 필드 추가 및 설정
        .AddFields RowFields:="Product", ColumnFields:="Category", PageFields:="Color"
        .AddDataField .PivotFields("SalesAmount"), "Sum of SalesAmount", xlSum
    End With

    ' 개체 해제
    Set pivotTable = Nothing
    Set pivotCache = Nothing
    Set rngData = Nothing
    Set wsPivot = Nothing
    Set wsData = Nothing
End Sub

 

부분합 표시/숨기기

PivotField.Subtotals property

지정한 필드에 표시되는 부분합을 반환하거나 설정합니다. 이 속성은 데이터 필드가 아닌 필드에만 적용됩니다. 읽기/쓰기가 가능한 Variant입니다.

 

Syntax
expression.Subtotals(Index)

 

expression : PivotField 개체를 나타내는 변수입니다.

 

Parameters

Index : 부분합 인덱스를 지정합니다. 지정하지 않으면 Subtotals 메서드는 각 부분합에 대한 Boolean 값이 들어 있는 배열을 반환합니다.

 

비고

인덱스가 True인 필드에 부분합이 표시됩니다. 인덱스 1(자동)이 True이면 다른 값은 모두 False로 설정됩니다.

 

OLAP (OLAP: 트랜잭션 처리 대신 쿼리와 보고서 기능을 위해 최적화된 데이터베이스 기술입니다. OLAP 데이터는 테이블 대신 큐브에 계층적으로 구성되고 저장됩니다.) 데이터 원본의 경우 Index는 1(자동)로만 반환되거나 설정됩니다. 반환되는 배열에는 항상 첫째 배열 요소에 대해 True나 False가 들어 있으며, 그 밖의 모든 요소에 대해서는 False가 들어 있습니다. 요소 값이 모두 False인 배열은 부분합이 없음을 나타냅니다.

 

매크로 기록을 하면 도움말에 있는 것처럼 각 부분합에 대한 Boolean 값이 들어 있는 배열을 반환합니다.

ActiveSheet.PivotTables("PivotTable1").PivotFields("Category").Subtotals = _
        Array(True, False, False, False, False, False, False, False, False, False, False, False)

 

 

다음 코드는 Index를 지정해서 원하는 값만 부분합을 설정한 예제입니다.

Sub Macro1()
'
' Macro1
'

'
    With ActiveSheet.PivotTables("PivotTable").PivotFields("Category")
        .Subtotals(2) = True
        .Subtotals(3) = True
    End With

End Sub

다음 코드는 특정 필드의 모든 부분합을 해제합니다. 1은 자동이라 2부터 순환하도록 했습니다.

Sub RemoveSubtotals()
    Dim pt As pivotTable
    Dim pf As PivotField
    Dim i As Long
    
    ' 피벗 테이블 설정
    Set pt = ActiveSheet.PivotTables("PivotTable")
    
    ' 필드명 설정
    Set pf = pt.PivotFields("Category")
    
    ' 부분합 비활성화
    For i = 2 To 12
        pf.Subtotals(i) = False
    Next i
End Sub

PivotTable.SubtotalLocation method

이 메서드는 기존의 모든 PivotFields에 대한 부분합 위치를 변경합니다. 부분합 위치를 변경하면 개요 형식의 필드에만 즉각적인 시각적 효과가 있지만 테이블 형식의 필드에 대해서도 설정됩니다.

 

Syntax
expression.SubtotalLocation (Location)

 

expression : PivotTable 개체를 나타내는 변수입니다.

Location : 필수입니다. 데이터 형식은 xlSubtotalLocationType(워크시트에서 부분합이 표시되는 위치를 지정)입니다.

XlSubtotalLocationType enumeration (Excel)

 

 

Sub MoveSubtotalToBottom()
'
' MoveSubtotalToBottom Macro
'

' 그룹 하단에 모든 부분합 표시
    ActiveSheet.PivotTables("PivotTable1").SubtotalLocation = xlAtBottom
End Sub

 

 

Sub MoveSubtotalToTop()
'
' MoveSubtotalToTop Macro
'

' 그룹 상단에 모든 부분합 표시
    ActiveSheet.PivotTables("PivotTable1").SubtotalLocation = xlAtTop
End Sub

총 합계 설정/해제

PivotTable.RowGrand property

True이면 피벗 테이블 보고서에 행의 총합계가 표시됩니다. 읽기/쓰기가 가능한 Boolean입니다.

 

Syntax
expression.RowGrand

 

expression : PivotTable 개체를 나타내는 변수입니다.

 

PivotTable.ColumnGrand property

True이면 피벗 테이블 보고서에 열에 대한 총합계가 표시됩니다. 읽기/쓰기가 가능한 Boolean입니다.

 

Syntax
expression.ColumnGrand

 

expression : PivotTable 개체를 나타내는 변수입니다.

 

아래에 나열된 코드는 매크로 기록한 코드입니다.

Sub HideRowAndColumnGrandTotals()
'
' HideRowAndColumnGrandTotals Macro
'

' 행 및 열의 총합계 해제
    With ActiveSheet.PivotTables("PivotTable")
        .ColumnGrand = False
        .RowGrand = False
    End With
End Sub

 

 

Sub ShowRowAndColumnGrandTotals()
'
' ShowRowAndColumnGrandTotals Macro
'

' 행 및 열의 총합계 설정
    With ActiveSheet.PivotTables("PivotTable")
        .ColumnGrand = True
        .RowGrand = True
    End With
End Sub

 

 

Sub ShowRowGrandTotal()
'
' ShowRowGrandTotal Macro
'

' 행의 총합계만 설정
    ActiveSheet.PivotTables("PivotTable").RowGrand = True
End Sub

 

 

Sub ShowColumnGrandTotal()
'
' ShowColumnGrandTotal Macro
'

' 열의 총합계만 설정
    ActiveSheet.PivotTables("PivotTable").ColumnGrand = True
End Sub

 

보고서 레이아웃 설정

PivotTable.RowAxisLayout method

이 메서드는 기존의 모든 PivotField 필드에 대한 레이아웃 옵션을 동시에 설정하는 데 사용됩니다.

 

Syntax
expression.RowAxisLayout(RowLayout)

 

expression : PivotTable 개체를 나타내는 변수입니다.

 

Parameters

RowLayout : 필수입니다.데이터 형식은 XlLayoutRowType입니다.

 

XlLayoutRowType enumeration (Excel)

 

비고
이 메서드는 일괄 적용되므로 레이아웃 옵션이 설정될 수 없는 PivotField가 있을 경우 모든 필드의 레이아웃 옵션이 변경되지 않고 피벗 테이블이 변경되지 않습니다.

 

아래 나열된 코드는 매크로 기록한 코드입니다.

Sub SetCompactRowLayout()
'
' SetCompactRowLayout Macro
'

' 압축 형식으로 표시
    ActiveSheet.PivotTables("PivotTable").RowAxisLayout xlCompactRow
End Sub

 

Sub SetOutlineRowLayout()
'
' SetOutlineRowLayout Macro
'

' 개요 형식으로 표시
    ActiveSheet.PivotTables("PivotTable").RowAxisLayout xlOutlineRow
End Sub

 

Sub SetTabularRowLayout()
'
' SetTabularRowLayout Macro
'

' 테이블 형식으로 표시
    ActiveSheet.PivotTables("PivotTable").RowAxisLayout xlTabularRow
End Sub

PivotTable.RepeatAllLabels method

지정한 피벗 테이블의 모든 PivotField에 대해 항목 레이블을 반복할지 여부를 지정합니다.

 

Syntax
expression.RepeatAllLabels(Repeat)

 

expression : PivotTable 개체를 나타내는 변수입니다.

 

Repeat : 피벗 테이블 보고서의 모든 필드 항목 레이블을 반복할지 여부를 지정합니다. 데이터 형식은 XlPivotFieldRepeatLabels입니다.

 

XlPivotFieldRepeatLabels enumeration (Excel)

 

비고
RepeatAllLabels 메서드를 사용하는 것은 피벗 테이블 도구 디자인 탭의 보고서 레이아웃 드롭다운 목록에 있는 모든 항목 레이블 반복 및 항목 레이블 반복 안 함 명령에 해당합니다.

단일 PivotField에 대해 항목 레이블을 반복할지 여부를 지정하려면 RepeatLabels 속성을 사용합니다.

 

다음 코드는 매크로 기록한 코드입니다.

Sub RepeatAllLabels()
'
' RepeatAllLabels Macro
'

' 모든 항목 레이블 반복
    ActiveSheet.PivotTables("PivotTable").RepeatAllLabels xlRepeatLabels
End Sub

 

Sub DoNotRepeatLabels()
'
' DoNotRepeatLabels Macro
'

' 항목 레이블 반복 안 함
    ActiveSheet.PivotTables("PivotTable").RepeatAllLabels xlDoNotRepeatLabels

End Sub

 

PivotField.RepeatLabels property

지정한 PivotField에 대해 피벗 테이블에서 항목 레이블이 반복되는지 여부를 반환하거나 설정하며 읽기/쓰기가 가능합니다.

 

Syntax
expression.RepeatLabels

 

expression : PivotField 개체를 나타내는 변수입니다.

비고
True이면 항목 레이블이 지정한 PivotField에 대해 반복되며 False이면 그렇지 않습니다.

RepeatLabels 속성의 설정은 PivotTable에 있는 필드에 대한 필드 설정 대화 상자에서 레이아웃 및 인쇄 탭의 항목 레이블 반복 확인란에 해당합니다.

 

한 번의 작업으로 피벗 테이블의 모든 PivotField에 대해 항목 레이블 반복 여부를 지정하려면 RepeatAllLabels 메서드를 사용합니다.

다음 코드는 Category필드를 선택하고 매크로 기록한 예제입니다.

Sub RepeatLabels()
'
' RepeatLabels Macro
'

'
    ActiveSheet.PivotTables("PivotTable").PivotFields("Category").RepeatLabels = _
        True
End Sub

 

빈 행

PivotField.LayoutBlankLine property

True이면 피벗 테이블 보고서에서 지정한 행 필드 다음에 빈 행이 삽입됩니다. 기본값은 False입니다. 읽기/쓰기가 가능한 Boolean입니다.

 

Syntax
expression.LayoutBlankLine

 

expression : PivotField 개체를 나타내는 변수입니다.

 

비고

이 속성은 모든 피벗 테이블 필드에 설정할 수 있지만 지정한 필드가 가장 안쪽(최하위 수준) 행 필드가 아닌 경우에만 빈 행이 표시됩니다. OLAP 데이터 원본이 아닌 경우 필드를 다시 정렬하거나 필드를 피벗 테이블 보고서에 추가하여도 이 속성의 값은 변하지 않습니다.

피벗 테이블 보고서에 있는 빈 행에 데이터를 입력할 수 없습니다.

 

다음 코드는 빈 행 - 각 항목 다음에 빈 줄 삽입을 매크로 기록한 코드입니다.

Sub InsertBlankLinesToPivotTable()
'
' Insert blank lines to the pivot table.
'

'
    ActiveSheet.PivotTables("PivotTable").PivotFields("Product").LayoutBlankLine = _
        True
    ActiveSheet.PivotTables("PivotTable").PivotFields("Category").LayoutBlankLine _
        = True
    ActiveSheet.PivotTables("PivotTable").PivotFields("Subcategory"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable").PivotFields("ModelName").LayoutBlankLine _
        = True
    ActiveSheet.PivotTables("PivotTable").PivotFields("Color").LayoutBlankLine = _
        True
    ActiveSheet.PivotTables("PivotTable").PivotFields("Quantuty").LayoutBlankLine _
        = True
    ActiveSheet.PivotTables("PivotTable").PivotFields("Price").LayoutBlankLine = _
        True
    ActiveSheet.PivotTables("PivotTable").PivotFields("SalesAmount"). _
        LayoutBlankLine = True
   
End Sub

 

아래와 같이 코드를 수정할 수 있습니다.

Sub InsertBlankLinesToPivotTable()
'
' Inserts blank lines to the pivot table.
'

'
    Dim pivotTable As pivotTable
    Dim Field As PivotField
    
    Set pivotTable = ActiveSheet.PivotTables("PivotTable")

    For Each Field In pivotTable.PivotFields
        Field.LayoutBlankLine = True
    Next Field
End Sub

 

실험을 해보니 Product 필드가 가장 먼저 적용되어서 더 이상 반복을 하지 않습니다.

 

다음 코드는 Category 필드에만 적용한 예제입니다.

Sub LayoutBlankLine()
'
' LayoutBlankLine Macro
'

'
    ActiveSheet.PivotTables("PivotTable").PivotFields("Category").LayoutBlankLine = _
    True

End Sub

 

피벗 테이블 스타일 옵션

PivotTable.ShowTableStyleRowHeaders property

행 머리글이 피벗 테이블에 표시되어야 하는 경우 ShowTableStyleRowHeaders 속성은 True로 설정되며 읽기/쓰기가 가능한 Boolean입니다.

 

Syntax
expression.ShowTableStyleRowHeaders

 

expression : PivotTable 개체를 나타내는 변수입니다.

Sub ToggleTableStyleRowHeaders()
'
' ToggleTableStyleRowHeaders Macro
'

' 행 머리글
    ActiveSheet.PivotTables("PivotTable").ShowTableStyleRowHeaders = False
    ActiveSheet.PivotTables("PivotTable").ShowTableStyleRowHeaders = True
    
End Sub

 

PivotTable.ShowTableStyleColumnHeaders property

열 머리글이 피벗 테이블에 표시되어야 하는 경우 ShowTableStyleColumnHeaders 속성은 True로 설정되며 읽기/쓰기가 가능한 Boolean입니다.

 

Syntax
expression.ShowTableStyleColumnHeaders

 

expression : PivotTable 개체를 나타내는 변수입니다.

Sub ToggleTableStyleColumnHeaders()
'
' ToggleTableStyleColumnHeaders Macro
'

' 열 머리글
    ActiveSheet.PivotTables("PivotTable").ShowTableStyleColumnHeaders = False
    ActiveSheet.PivotTables("PivotTable").ShowTableStyleColumnHeaders = True
   
End Sub

PivotTable.ShowTableStyleRowStripes property

ShowTableStyleRowStripes 속성은 짝수 행의 서식이 홀수 행과 다른 줄무늬 행을 표시합니다. 그 결과 피벗 테이블을 읽기 쉬워지며 읽기/쓰기가 가능한 Boolean입니다.

 

Syntax
expression.ShowTableStyleRowStripes

 

expression : PivotTable 개체를 나타내는 변수입니다.

Sub ToggleTableRowStripes()
 '
 ' ToggleTableRowStripes Macro
 '

 ' 줄무늬 행
     ActiveSheet.PivotTables("PivotTable").ShowTableStyleRowStripes = True
     ActiveSheet.PivotTables("PivotTable").ShowTableStyleRowStripes = False
  
End Sub

PivotTable.ShowTableStyleColumnStripes property

ShowTableStyleColumnStripes 속성은 짝수 열의 서식이 홀수 열과 다른 줄무늬 열을 표시하며 그 결과 피벗 테이블을 읽기가 쉬워집니다. 읽기/쓰기가 가능한 Boolean입니다.

 

Syntax
expression.ShowTableStyleColumnStripes

 

expression : PivotTable 개체를 나타내는 변수입니다.

Sub ToggleColumnStripes()
  '
  'ToggleColumnStripes Macro
  '

  ' 줄무늬 열
      ActiveSheet.PivotTables("PivotTable").ShowTableStyleColumnStripes = True
      ActiveSheet.PivotTables("pivottable").ShowTableStyleColumnStripes = False
End Sub

 

피벗 테이블 스타일

PivotTable.TableStyle2 property

TableStyle2 속성은 현재 피벗 테이블에 적용되는 피벗 테이블 스타일을 지정하며 읽기/쓰기가 가능합니다.

 

Syntax
expression.TableStyle2

 

expression : PivotTable 개체를 나타내는 변수입니다.

 

비고
이 속성은 TableStyle이라는 기존 속성이 있으므로 TableStyle2라고 합니다.

Sub ApplyPivotStyleLight16()
'
' ApplyPivotStyleLight16 Macro
'

'
    ActiveSheet.PivotTables("PivotTable").TableStyle2 = "PivotStyleLight16"
 
End Sub

 

피벗 테이블 스타일 지우기

Sub ClearTableStyle()
'
' ClearTableStyle Macro
'

'
    ActiveSheet.PivotTables("PivotTable").TableStyle2 = ""
End Sub

 

관련 글

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

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

피벗 테이블 분석

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