excel-vba
Tabele przestawne

Uwagi

Tworzenie tabeli przestawnej

Jedną z najpotężniejszych możliwości w programie Excel jest użycie tabel przestawnych do sortowania i analizowania danych. Używanie VBA do tworzenia i manipulowania osiami przestawnymi jest łatwiejsze, jeśli rozumiesz związek tabel przestawnych z pamięciami przestawnymi oraz sposób odwoływania się i używania różnych części tabel.

Najprościej mówiąc, dane źródłowe są Range danych w Worksheet . Ten obszar danych MUSI identyfikować kolumny danych z wierszem nagłówka jako pierwszym wierszem w zakresie. Po utworzeniu tabeli przestawnej użytkownik może przeglądać i zmieniać dane źródłowe w dowolnym momencie. Zmiany mogą jednak nie zostać automatycznie lub natychmiast odzwierciedlone w samej tabeli przestawnej, ponieważ istnieje pośrednia struktura przechowywania danych zwana pamięcią podręczną przestawną, która jest bezpośrednio połączona z samą tabelą przestawną.

Jeśli potrzebnych jest wiele tabel przestawnych, opartych na tych samych danych źródłowych, pamięć podręczna przestawna może być ponownie użyta jako wewnętrzny magazyn danych dla każdej z tabel przestawnych. Jest to dobra praktyka, ponieważ oszczędza pamięć i zmniejsza rozmiar pliku Excel do przechowywania.

Na przykład, aby utworzyć tabelę przestawną na podstawie danych źródłowych pokazanych na powyższych rysunkach:

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

Odnośniki Obiekt tabeli przestawnej MSDN

Zakresy tabeli przestawnej

Te doskonałe źródła referencyjne zawierają opisy i ilustracje różnych zakresów w tabelach przestawnych.

Bibliografia

• Odwoływanie się do zakresów tabeli przestawnej w VBA - z bloga technicznego Jona Peltiera
• Odwoływanie się do zakresu tabeli przestawnej programu Excel za pomocą VBA - z Globaliconnect Excel VBA

Dodawanie pól do tabeli przestawnej

Dwie ważne rzeczy, na które należy zwrócić uwagę podczas dodawania pól do tabeli przestawnej, to Orientacja i Pozycja. Czasami programista może założyć, gdzie pole jest umieszczone, więc zawsze jest wyraźniejsze zdefiniowanie tych parametrów. Te działania wpływają tylko na daną tabelę przestawną, a nie na pamięć podręczną przestawną.

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

Formatowanie danych tabeli przestawnej

Ten przykład zmienia / ustawia kilka formatów w obszarze zakresu danych ( DataBodyRange ) danej tabeli przestawnej. Dostępne są wszystkie parametry formatowalne w standardowym Range . Formatowanie danych wpływa tylko na samą tabelę przestawną, a nie na pamięć podręczną przestawną.

UWAGA: właściwość nosi nazwę TableStyle2 ponieważ właściwość TableStyle nie jest członkiem właściwości obiektu 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