Arbeiten mit PivotTables in Office-Skripts

Artikel
05/03/2024

Mit PivotTables können Sie große Datensammlungen schnell analysieren. Mit ihrer Macht kommt Komplexität. Mit den Office-Skript-APIs können Sie eine PivotTable an Ihre Anforderungen anpassen, aber der Umfang des API-Satzes macht den Einstieg zu einer Herausforderung. In diesem Artikel wird veranschaulicht, wie Allgemeine PivotTable-Aufgaben ausgeführt werden, und wichtige Klassen und Methoden werden erläutert.

Hinweis

Um den Kontext für die von den APIs verwendeten Begriffe besser zu verstehen, lesen Sie zuerst die Excel-PivotTable-Dokumentation. Beginnen Sie mit Create einer PivotTable, um Arbeitsblattdaten zu analysieren.

Objektmodell

Eine vereinfachte Darstellung der Klassen, Methoden und Eigenschaften, die bei der Arbeit mit PivotTables verwendet werden.

Die PivotTable ist das zentrale Objekt für PivotTables in der Office-Skript-API.

Das Workbook-Objekt verfügt über eine Auflistung aller PivotTables. Jedes Arbeitsblatt enthält auch eine PivotTable-Auflistung, die lokal für dieses Blatt ist.
Eine PivotTable enthält PivotHierarchies. Eine Hierarchie kann als Spalte in einer Tabelle betrachtet werden.
PivotHierarchies können als Zeilen oder Spalten (RowColumnPivotHierarchy), Daten (DataPivotHierarchy) oder Filter (FilterPivotHierarchy) hinzugefügt werden.
Jede PivotHierarchy enthält genau ein PivotField. PivotTable-Strukturen außerhalb von Excel können mehrere Felder pro Hierarchie enthalten, sodass dieser Entwurf zur Unterstützung zukünftiger Optionen vorhanden ist. Bei Office-Skripts werden Felder und Hierarchien den gleichen Informationen zugeordnet.
Ein PivotField enthält mehrere PivotItems. Jedes PivotItem ist ein eindeutiger Wert im Feld. Stellen Sie sich jedes Element als Wert in der Tabellenspalte vor. Elemente können auch aggregierte Werte sein, z. B. Summen, wenn das Feld für Daten verwendet wird.
PivotLayout definiert, wie die PivotFields und PivotItems angezeigt werden.
PivotFilter filtert Daten aus der PivotTable nach verschiedenen Kriterien.

Um zu sehen, wie diese Beziehungen in der Praxis funktionieren, laden Sie zunächst die Beispielarbeitsmappe herunter. Diese Daten beschreiben den Absatz von Obst aus verschiedenen Landwirtschaftlichen Betrieben. Dies ist die Grundlage für alle Beispiele in diesem Artikel. Führen Sie die Beispielskripts im gesamten Artikel aus, um PivotTables zu erstellen und zu untersuchen.

Herunterladen der Beispielarbeitsmappe

Eine Sammlung von Obstverkäufen verschiedener Arten von verschiedenen Landwirtschaftlichen Betrieben.

Create einer PivotTable mit Feldern

PivotTables werden mit Verweisen auf vorhandene Daten erstellt. Sowohl Bereiche als auch Tabellen können die Quelle für eine PivotTable sein. Sie benötigen auch einen Ort, an dem sie in der Arbeitsmappe vorhanden sind. Da die Größe einer PivotTable dynamisch ist, wird nur die obere linke Ecke des Zielbereichs angegeben.

Der folgende Codeausschnitt erstellt eine PivotTable basierend auf einem Datenbereich. Die PivotTable verfügt über keine Hierarchien, sodass die Daten noch nicht gruppiert sind.

  const dataSheet = workbook.getWorksheet("Data");
  const pivotSheet = workbook.getWorksheet("Pivot");

  const farmPivot = pivotSheet.addPivotTable(
    "Farm Pivot", /* The name of the PivotTable. */
    dataSheet.getUsedRange(), /* The source data range. */
    pivotSheet.getRange("A1") /* The location to put the new PivotTable. */);

Eine PivotTable mit dem Namen

Hierarchien und Felder

PivotTables sind über Hierarchien organisiert. Diese Hierarchien werden verwendet, um Daten zu pivotieren, wenn sie als einen bestimmten Hierarchietyp hinzugefügt werden. Es gibt vier Arten von Hierarchien.

Zeile: Zeigt Elemente in horizontalen Zeilen an.
Spalte: Zeigt Elemente in vertikalen Spalten an.
Daten: Zeigt Aggregate von Werten basierend auf den Zeilen und Spalten an.
Filter: Dient zum Hinzufügen oder Entfernen von Elementen zur PivotTable.

Einer PivotTable können so viele oder nur wenige Felder zugewiesen sein, die diesen spezifischen Hierarchien zugewiesen sind. Eine PivotTable benötigt mindestens eine Datenhierarchie, um zusammengefasste numerische Daten anzuzeigen, und mindestens eine Zeile oder Spalte zum Pivotieren dieser Zusammenfassung. Der folgende Codeausschnitt fügt zwei Zeilenhierarchien und zwei Datenhierarchien hinzu.

  farmPivot.addRowHierarchy(farmPivot.getHierarchy("Farm"));
  farmPivot.addRowHierarchy(farmPivot.getHierarchy("Type"));
  farmPivot.addDataHierarchy(farmPivot.getHierarchy("Crates Sold at Farm"));
  farmPivot.addDataHierarchy(farmPivot.getHierarchy("Crates Sold Wholesale"));

Eine PivotTable, die den Gesamtumsatz verschiedener Früchte basierend auf dem Land anzeigt, von dem sie stammen.

Layoutbereiche

Jeder Teil der PivotTable wird einem Bereich zugeordnet. Dadurch kann Ihr Skript Daten aus der PivotTable abrufen, um sie später im Skript zu verwenden oder in einem Power Automate-Flow zurückzugeben. Auf diese Bereiche wird über das PivotLayout-Objekt zugegriffen, das von PivotTable.getLayout()abgerufen wird. Das folgende Diagramm zeigt die Bereiche, die von den Methoden in PivotLayoutzurückgegeben werden.

Diagramm, das zeigt, welche Abschnitte einer PivotTable von den Get-Bereichsfunktionen des Layouts zurückgegeben werden.

PivotTable-Gesamtausgabe

Die Position der Gesamtzeile basiert auf dem Layout. Verwenden Sie PivotLayout.getBodyAndTotalRange die letzte Zeile der Spalte, und rufen Sie sie ab, um die Daten aus der PivotTable in Ihrem Skript zu verwenden.

Das folgende Beispiel sucht die erste PivotTable in der Arbeitsmappe und protokolliert die Werte in den Zellen "Gesamtsumme" (wie in der abbildung unten grün hervorgehoben).

Eine PivotTable mit Obstverkäufen mit grün hervorgehobener Zeile

function main(workbook: ExcelScript.Workbook) {
  // Get the first PivotTable in the workbook.
  const pivotTable = workbook.getPivotTables()[0];

  // Get the names of each data column in the PivotTable.
  const pivotColumnLabelRange = pivotTable.getLayout().getColumnLabelRange();

  // Get the range displaying the pivoted data.
  const pivotDataRange = pivotTable.getLayout().getBodyAndTotalRange();

  // Get the range with the "grand totals" for the PivotTable columns.
  const grandTotalRange = pivotDataRange.getLastRow();

  // Print each of the "Grand Totals" to the console.
  grandTotalRange.getValues()[0].forEach((column, columnIndex) => {
    console.log(`Grand total of ${pivotColumnLabelRange.getValues()[0][columnIndex]}: ${grandTotalRange.getValues()[0][columnIndex]}`);
    // Example log: "Grand total of Sum of Crates Sold Wholesale: 11000"
  });
}

Filter und Slicer

Es gibt drei Möglichkeiten, eine PivotTable zu filtern.

FilterPivotHierarchies

FilterPivotHierarchies Fügen Sie eine zusätzliche Hierarchie hinzu, um jede Datenzeile zu filtern. Jede Zeile mit einem herausgefilterten Element wird von der PivotTable und ihren Zusammenfassungen ausgeschlossen. Da diese Filter auf Elementen basieren, funktionieren sie nur für diskrete Werte. Wenn "Klassifizierung" eine Filterhierarchie im Beispiel ist, können Benutzer die Werte "Organisch" und "Konventionell" für den Filter auswählen. Wenn "Crates Sold Wholesale" ausgewählt ist, wären die Filteroptionen die einzelnen Zahlen, z. B. 120 und 150, anstelle von numerischen Bereichen.

FilterPivotHierarchies werden erstellt, wobei alle Werte ausgewählt sind. Dies bedeutet, dass nichts gefiltert wird, bis der Benutzer manuell mit dem Filtersteuerelement interagiert oder ein PivotManualFilter für das Feld festgelegt wird, das FilterPivotHierarchyzum gehört.

Der folgende Codeausschnitt fügt "Klassifizierung" als Filterhierarchie hinzu.

  farmPivot.addFilterHierarchy(farmPivot.getHierarchy("Classification"));

Ein Filtersteuerelement, das

PivotFilters

Das PivotFilters -Objekt ist eine Auflistung von Filtern, die auf ein einzelnes Feld angewendet werden. Da jede Hierarchie genau ein Feld enthält, sollten Sie beim Anwenden von Filtern immer das erste Feld in PivotHierarchy.getFields() verwenden. Es gibt vier Filtertypen.

Datumsfilter: Kalenderdatumsbasierte Filterung.
Bezeichnungsfilter: Filterung von Textvergleichen.
Manueller Filter: Benutzerdefinierte Eingabefilterung.
Wertfilter: Zahlenvergleichsfilterung. Dadurch werden Elemente in der zugeordneten Hierarchie mit Werten in einer angegebenen Datenhierarchie verglichen.

In der Regel wird nur einer der vier Filtertypen erstellt und auf das Feld angewendet. Wenn das Skript versucht, inkompatible Filter zu verwenden, wird ein Fehler mit dem Text "Das Argument ist ungültig oder fehlt oder hat ein falsches Format" ausgelöst.

Der folgende Codeausschnitt fügt zwei Filter hinzu. Der erste ist ein manueller Filter, der Elemente in einer vorhandenen Filterhierarchie "Klassifizierung" auswählt. Der zweite Filter entfernt alle Betriebe, die weniger als 300 "Crates Sold Wholesale" haben. Beachten Sie, dass dies die "Summe" dieser Farmen herausfiltert, nicht die einzelnen Zeilen aus den ursprünglichen Daten.

  const classificationField = farmPivot.getFilterHierarchy("Classification").getFields()[0];
  classificationField.applyFilter({
    manualFilter: { 
      selectedItems: ["Organic"] /* The included items. */
    }
  });

  const farmField = farmPivot.getHierarchy("Farm").getFields()[0];
  farmField.applyFilter({
    valueFilter: {
      condition: ExcelScript.ValueFilterCondition.greaterThan, /* The relationship of the value to the comparator. */
      comparator: 300, /* The value to which items are compared. */
      value: "Sum of Crates Sold Wholesale" /* The name of the data hierarchy. Note the "Sum of" prefix. */
      }
  });

Eine PivotTable, nachdem der Wertfilter und der manuelle Filter angewendet wurden.

Datenschnitte

Datenschnitte filtern Daten in einer PivotTable (oder Standardtabelle). Es handelt sich um bewegliche Objekte im Arbeitsblatt, die eine schnelle Filterung der Auswahl ermöglichen. Ein Slicer funktioniert auf ähnliche Weise wie der manuelle Filter und PivotFilterHierarchy. Elemente aus der PivotField werden so umgeschaltet, dass sie in die PivotTable eingeschlossen oder ausgeschlossen werden.

Der folgende Codeausschnitt fügt einen Slicer für das Feld "Typ" hinzu. Es legt die ausgewählten Elemente auf "Zitrone" und "Lime" fest und verschiebt dann den Slicer um 400 Pixel nach links.

  const fruitSlicer = pivotSheet.addSlicer(
    farmPivot, /* The table or PivotTale to be sliced. */
    farmPivot.getHierarchy("Type").getFields()[0] /* What source to use as the slicer options. */
  );
  fruitSlicer.selectItems(["Lemon", "Lime"]);
  fruitSlicer.setLeft(400);

Ein Datenschnitt, der Daten in einer PivotTable filtert.

Wertfeldeinstellungen für Zusammenfassungen

Ändern Sie, wie die PivotTable Daten mit diesen Einstellungen zusammenfasst und anzeigt. Das Feld in jeder Datenhierarchie kann die Daten auf unterschiedliche Weise anzeigen, z. B. Prozentsätze, Standardabweichungen und relative Vergleiche.

summarize by (Zusammenfassen nach)

Die Standardzusammenfassung eines Datenhierarchiefelds erfolgt als Summe. DataPivotHierarchy.setSummarizeBy ermöglicht es Ihnen, die Daten für jede Zeile oder Spalte auf unterschiedliche Weise zu kombinieren. AggregationFunction listet alle verfügbaren Optionen auf.

Der folgende Codeausschnitt ändert "Crates Sold Wholesale", um anstelle der Summe die Standardabweichung jedes Artikels anzuzeigen.

  const wholesaleSales = farmPivot.getDataHierarchy("Sum of Crates Sold Wholesale");
  wholesaleSales.setSummarizeBy(ExcelScript.AggregationFunction.standardDeviation);

Werte anzeigen als

DataPivotHierarchy.setShowAs wendet eine Berechnung auf die Werte einer Datenhierarchie an. Anstelle der Standardsumme können Sie Werte oder Prozentsätze relativ zu anderen Teilen der PivotTable anzeigen. Verwenden Sie ein ShowAsRule , um festzulegen, wie Datenhierarchiewerte angezeigt werden.

Der folgende Codeausschnitt ändert die Anzeige für "Crates Sold at Farm". Die Werte werden als Prozentsatz der Gesamtsumme für das Feld angezeigt.

  const farmSales = farmPivot.getDataHierarchy("Sum of Crates Sold at Farm");

  const rule : ExcelScript.ShowAsRule = {
    calculation: ExcelScript.ShowAsCalculation.percentOfGrandTotal
  };
  farmSales.setShowAs(rule);

Einige ShowAsRulebenötigen ein anderes Feld oder Element in diesem Feld als Vergleich. Der folgende Codeausschnitt ändert erneut die Anzeige für "Crates Sold at Farm". Dieses Mal zeigt das Feld die Differenz jedes Werts vom Wert der "Lemons" in dieser Zeile an. Wenn ein Betrieb keine Zitronen verkauft hat, wird im Feld "#N/A" angezeigt.

  const typeField = farmPivot.getRowHierarchy("Type").getFields()[0];
  const farmSales = farmPivot.getDataHierarchy("Sum of Crates Sold at Farm");

  const rule: ExcelScript.ShowAsRule = {
    calculation: ExcelScript.ShowAsCalculation.differenceFrom,
    baseField: typeField, /* The field to use for the difference. */
    baseItem: typeField.getPivotItem("Lemon") /* The item within that field that is the basis of comparison for the difference. */
  };
  farmSales.setShowAs(rule);
  farmSales.setName("Difference from Lemons of Crates Sold at Farm");

Freigeben über