Arbeiten mit PivotTables mithilfe der Excel JavaScript-API

PivotTables optimieren größere Datasets. Sie ermöglichen die schnelle Bearbeitung gruppierter Daten. Die Excel JavaScript-API ermöglicht Es Ihrem Add-In, PivotTables zu erstellen und mit deren Komponenten zu interagieren. Dieser Artikel beschreibt, wie PivotTables durch die Office JavaScript-API dargestellt werden, und enthält Codebeispiele für wichtige Szenarien.

Wenn Sie mit der Funktionalität von PivotTables nicht vertraut sind, sollten Sie diese als Endbenutzer erkunden. Eine gute Einführung in diese Tools finden Sie unter Erstellen einer PivotTable, um Arbeitsblattdaten zu analysieren .

Wichtig

PivotTables, die mit OLAP erstellt wurden, werden derzeit nicht unterstützt. Es gibt auch keine Unterstützung für PowerPivot.

Objektmodell

Ein Diagramm, das die Beziehung zwischen Objekttypen für PivotTables zeigt, wie in der nachfolgenden Liste und der zugehörigen Referenzdokumentation beschrieben.

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

Sehen wir uns an, wie diese Beziehungen auf einige Beispieldaten angewendet werden. Die folgenden Daten beschreiben den Obstumsatz aus verschiedenen Farmen. Dies ist das Beispiel in diesem Artikel.

Eine Sammlung von Obstumsätzen verschiedener Typen aus verschiedenen Farmen.

Diese Verkaufsdaten der Obstfarm werden verwendet, um eine PivotTable zu erstellen. Jede Spalte, z. B . Types, ist ein PivotHierarchy. Die Hierarchie Typen enthält das Feld Types . Das Feld "Typen " enthält die Elemente "Apple", "Kiwi", " Limonen", " Limonen" und "Orange".

Hierarchien

PivotTables werden basierend auf vier Hierarchiekategorien organisiert: Zeile, Spalte, Daten und Filter.

Die zuvor gezeigten Farmdaten haben fünf Hierarchien: Farmen, Typ, Klassifizierung, in der Farm verkaufte Krates und verkaufte Krates. Jede Hierarchie kann nur in einer der vier Kategorien vorhanden sein. If Type is added to column hierarchies, it cannot also be in the row, data, or filter hierarchies. Wenn Type anschließend zeilenhierarchien hinzugefügt wird, wird er aus den Spaltenhierarchien entfernt. Dieses Verhalten ist unabhängig davon gleich, ob die Hierarchiezuweisung über die Excel Ui oder die Excel JavaScript-APIs erfolgt.

Zeilen- und Spaltenhierarchien definieren, wie Daten gruppiert werden. Beispielsweise gruppiert eine Zeilenhierarchie von Farmen alle Datensätze aus derselben Farm. Die Auswahl zwischen Zeilen- und Spaltenhierarchie definiert die Ausrichtung der PivotTable.

Datenhierarchien sind die Werte, die basierend auf den Zeilen- und Spaltenhierarchien aggregiert werden sollen. Eine PivotTable mit einer Zeilenhierarchie von Farmen und einer Datenhierarchie von Crates SoldSumer zeigt die Summe (standardmäßig) aller unterschiedlichen Verteiler für jede Farm an.

Filterhierarchien enthalten oder schließen Daten aus dem Pivot basierend auf Werten innerhalb dieses gefilterten Typs aus. Eine Filterhierarchie der Klassifizierung mit dem ausgewählten Typ "Bio" zeigt nur Daten für bio-Obst an.

Hier sehen Sie erneut die Farmdaten, zusammen mit einer PivotTable. Die PivotTable verwendet "Farm " und "Typ " als Zeilenhierarchien, "Crates Sold at Farm " und "Crates Sold"- als Datenhierarchien (mit der standardmäßigen Aggregationsfunktion "Summe") und "Klassifizierung " als Filterhierarchie (mit ausgewählter "Organisch ").

Eine Auswahl von Obst-Verkaufsdaten neben einer PivotTable mit Zeilen-, Daten- und Filterhierarchien.

Diese PivotTable kann über die JavaScript-API oder über die Excel Ui generiert werden. Beide Optionen ermöglichen eine weitere Manipulation über Add-Ins.

Erstellen einer PivotTable

PivotTables benötigen einen Namen, eine Quelle und ein Ziel. Die Quelle kann eine Bereichsadresse oder ein Tabellenname sein (übergeben als Range, stringoder Table Typ). Das Ziel ist eine Bereichsadresse (entweder als a Range oder string). Die folgenden Beispiele zeigen verschiedene PivotTable-Erstellungstechniken.

Erstellen einer PivotTable mit Bereichsadressen

await Excel.run(async (context) => {
    // Create a PivotTable named "Farm Sales" on the current worksheet at cell
    // A22 with data from the range A1:E21.
    context.workbook.worksheets.getActiveWorksheet().pivotTables.add(
      "Farm Sales", "A1:E21", "A22");

    await context.sync();
});

Erstellen einer PivotTable mit Range-Objekten

await Excel.run(async (context) => {
    // Create a PivotTable named "Farm Sales" on a worksheet called "PivotWorksheet" at cell A2
    // the data comes from the worksheet "DataWorksheet" across the range A1:E21.
    let rangeToAnalyze = context.workbook.worksheets.getItem("DataWorksheet").getRange("A1:E21");
    let rangeToPlacePivot = context.workbook.worksheets.getItem("PivotWorksheet").getRange("A2");
    context.workbook.worksheets.getItem("PivotWorksheet").pivotTables.add(
      "Farm Sales", rangeToAnalyze, rangeToPlacePivot);

    await context.sync();
});

Erstellen einer PivotTable auf Arbeitsmappenebene

await Excel.run(async (context) => {
    // Create a PivotTable named "Farm Sales" on a worksheet called "PivotWorksheet" at cell A2
    // the data is from the worksheet "DataWorksheet" across the range A1:E21.
    context.workbook.pivotTables.add(
        "Farm Sales", "DataWorksheet!A1:E21", "PivotWorksheet!A2");

    await context.sync();
});

Verwenden einer vorhandenen PivotTable

Auf manuell erstellte PivotTables kann auch über die PivotTable-Auflistung der Arbeitsmappe oder einzelner Arbeitsblätter zugegriffen werden. Der folgende Code ruft eine PivotTable mit dem Namen "My Pivot" aus der Arbeitsmappe ab.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.pivotTables.getItem("My Pivot");
    await context.sync();
});

Hinzufügen von Zeilen und Spalten zu einer PivotTable

Zeilen und Spalten pivotieren die Daten um die Werte dieser Felder.

Durch das Hinzufügen der Farmspalte werden alle Verkäufe um jede Farm herum pivotiert. Adding the Type and Classification rows further breaks down the data based on what fruit was sold and whether it was organic or not.

Eine PivotTable mit einer Farmspalte und Typ- und Klassifizierungszeilen.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Classification"));

    pivotTable.columnHierarchies.add(pivotTable.hierarchies.getItem("Farm"));

    await context.sync();
});

Sie können auch eine PivotTable nur mit Zeilen oder Spalten verwenden.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Classification"));

    await context.sync();
});

Hinzufügen von Datenhierarchien zur PivotTable

Datenhierarchien füllen die PivotTable mit Informationen, die basierend auf den Zeilen und Spalten kombiniert werden können. Durch Hinzufügen der Datenhierarchien von "In Farm verkauften" und " Crates SoldSumer " werden Summen dieser Abbildungen für jede Zeile und Spalte bereitgestellt.

Im Beispiel sind Farm und Typ Zeilen, wobei die Kastenumsätze die Daten sind.

Eine PivotTable, die den Gesamtumsatz verschiedener Obstarten basierend auf der Farm anzeigt, aus der sie stammen.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    // "Farm" and "Type" are the hierarchies on which the aggregation is based.
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));

    // "Crates Sold at Farm" and "Crates Sold Wholesale" are the hierarchies
    // that will have their data aggregated (summed in this case).
    pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold at Farm"));
    pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold Wholesale"));

    await context.sync();
});

PivotTable-Layouts und Abrufen von pivotierten Daten

Ein PivotLayout definiert die Platzierung von Hierarchien und deren Daten. Sie greifen auf das Layout zu, um die Bereiche zu bestimmen, in denen Daten gespeichert werden.

Das folgende Diagramm zeigt, welche Layoutfunktionsaufrufe welchen Bereichen der PivotTable entsprechen.

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

Abrufen von Daten aus der PivotTable

Das Layout definiert, wie die PivotTable im Arbeitsblatt angezeigt wird. Dies bedeutet, dass das PivotLayout Objekt die Bereiche steuert, die für PivotTable-Elemente verwendet werden. Verwenden Sie die vom Layout bereitgestellten Bereiche, um Daten abzurufen, die von der PivotTable gesammelt und aggregiert werden. Verwenden Sie PivotLayout.getDataBodyRange dies insbesondere, um auf die von der PivotTable erzeugten Daten zuzugreifen.

Der folgende Code veranschaulicht, wie die letzte Zeile der PivotTable-Daten durch Durchlaufen des Layouts abgerufen wird (die Gesamtsumme der Spalten "Summe der in der Farm verkauften Kisten" und " Summe der verkauften Krates " im vorherigen Beispiel). Diese Werte werden dann für eine endgültige Summe zusammengefasst, die in Zelle E30 (außerhalb der PivotTable) angezeigt wird.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    // Get the totals for each data hierarchy from the layout.
    let range = pivotTable.layout.getDataBodyRange();
    let grandTotalRange = range.getLastRow();
    grandTotalRange.load("address");
    await context.sync();

    // Sum the totals from the PivotTable data hierarchies and place them in a new range, outside of the PivotTable.
    let masterTotalRange = context.workbook.worksheets.getActiveWorksheet().getRange("E30");
    masterTotalRange.formulas = [["=SUM(" + grandTotalRange.address + ")"]];
    await context.sync();
});

Layouttypen

PivotTables verfügen über drei Layoutstile: Compact, Outline und Tabular. Wir haben in den vorherigen Beispielen die Kompaktformatvorlage gesehen.

In den folgenden Beispielen werden die Gliederungs- bzw. Tabulatorformatvorlagen verwendet. Das Codebeispiel zeigt, wie Sie zwischen den verschiedenen Layouts einen Zyklus ausführen.

Gliederungslayout

Eine PivotTable, die das Gliederungslayout verwendet.

Tabellarisches Layout

Eine PivotTable, die das tabellarische Layout verwendet.

Codebeispiel für PivotLayout-Typwechsel

await Excel.run(async (context) => {
    // Change the PivotLayout.type to a new type.
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.layout.load("layoutType");
    await context.sync();

    // Cycle between the three layout types.
    if (pivotTable.layout.layoutType === "Compact") {
        pivotTable.layout.layoutType = "Outline";
    } else if (pivotTable.layout.layoutType === "Outline") {
        pivotTable.layout.layoutType = "Tabular";
    } else {
        pivotTable.layout.layoutType = "Compact";
    }

    await context.sync();
});

Andere PivotLayout-Funktionen

Standardmäßig passen PivotTables die Zeilen- und Spaltengrößen nach Bedarf an. Dies geschieht, wenn die PivotTable aktualisiert wird. PivotLayout.autoFormat gibt dieses Verhalten an. Alle Zeilen- oder Spaltengrößenänderungen, die ihr Add-In vorgenommen hat, bleiben bestehen, wenn autoFormat sie vorhanden sind false. Darüber hinaus behalten die Standardeinstellungen einer PivotTable alle benutzerdefinierten Formatierungen in der PivotTable bei (z. B. Füllungen und Schriftartänderungen). Legen Sie fest PivotLayout.preserveFormatting , dass false das Standardformat bei der Aktualisierung angewendet wird.

A PivotLayout steuert auch Kopfzeilen- und Zeileneinstellungen insgesamt, wie leere Datenzellen angezeigt werden, und Alternativtextoptionen . Die PivotLayout-Referenz enthält eine vollständige Liste dieser Features.

Im folgenden Codebeispiel werden leere Datenzellen mit der Zeichenfolge "--"angezeigt, der Textkörperbereich wird in einer konsistenten horizontalen Ausrichtung formatiert und sichergestellt, dass die Formatierungsänderungen auch nach der Aktualisierung der PivotTable beibehalten werden.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
    let pivotLayout = pivotTable.layout;

    // Set a default value for an empty cell in the PivotTable. This doesn't include cells left blank by the layout.
    pivotLayout.emptyCellText = "--";

    // Set the text alignment to match the rest of the PivotTable.
    pivotLayout.getDataBodyRange().format.horizontalAlignment = Excel.HorizontalAlignment.right;

    // Ensure empty cells are filled with a default value.
    pivotLayout.fillEmptyCells = true;

    // Ensure that the format settings persist, even after the PivotTable is refreshed and recalculated.
    pivotLayout.preserveFormatting = true;
    await context.sync();
});

Löschen einer PivotTable

PivotTables werden mit ihrem Namen gelöscht.

await Excel.run(async (context) => {
    context.workbook.worksheets.getItem("Pivot").pivotTables.getItem("Farm Sales").delete();
    await context.sync();
});

Filtern einer PivotTable

Die primäre Methode zum Filtern von PivotTable-Daten ist PivotFilters. Datenschnitte bieten eine alternative, weniger flexible Filtermethode.

PivotFilter filtert Daten basierend auf den vier Hierarchiekategorien einer PivotTable (Filter, Spalten, Zeilen und Werte). Es gibt vier Arten von PivotFiltern, die kalenderdatumsbasierte Filterung, Zeichenfolgenparsierung, Zahlenvergleich und Filterung basierend auf einer benutzerdefinierten Eingabe ermöglichen.

Datenschnitte können sowohl auf PivotTables als auch auf reguläre Excel Tabellen angewendet werden. Bei Anwendung auf eine PivotTable funktionieren Datenschnitte wie ein PivotManualFilter und ermöglichen die Filterung basierend auf einer benutzerdefinierten Eingabe. Im Gegensatz zu PivotFiltern verfügen Datenschnitte über eine Excel UI-Komponente. Mit der Slicer Klasse erstellen Sie diese UI-Komponente, verwalten die Filterung und steuern deren visuelle Darstellung.

Filtern mit PivotFiltern

Mit PivotFiltern können Sie PivotTable-Daten basierend auf den vier Hierarchiekategorien (Filter, Spalten, Zeilen und Werte) filtern. Werden im PivotTable-Objektmodell PivotFilters auf ein PivotField angewendet, und jedem PivotField kann mindestens ein Objekt zugewiesen werden PivotFilters. Um PivotFilter auf ein PivotField anzuwenden, muss die entsprechende PivotHierarchy des Felds einer Hierarchiekategorie zugewiesen werden.

Arten von PivotFiltern

Filtertyp Filterzweck JavaScript-API-Referenz für Excel
DateFilter Kalenderdatumsbasierte Filterung. PivotDateFilter
LabelFilter Textvergleichsfilterung. PivotLabelFilter
ManualFilter Benutzerdefinierte Eingabefilterung. PivotManualFilter
ValueFilter Zahlenvergleichsfilterung. PivotValueFilter

Erstellen eines PivotFilters

Um PivotTable-Daten mit einem (z. B. einem Pivot*Filter PivotDateFilter) zu filtern, wenden Sie den Filter auf ein PivotField an. Die folgenden vier Codebeispiele zeigen, wie jeder der vier Typen von PivotFiltern verwendet wird.

PivotDateFilter

Im ersten Codebeispiel wird ein PivotDateFilter auf das PivotField " Datum aktualisiert " angewendet, wobei alle Daten vor 2020-08-01 ausgeblendet werden.

Wichtig

Ein Pivot*Filter PivotField kann nur angewendet werden, wenn das PivotHierarchy-Element dieses Felds einer Hierarchiekategorie zugewiesen ist. Im folgenden Codebeispiel muss die dateHierarchy Kategorie der PivotTable rowHierarchies hinzugefügt werden, bevor sie zum Filtern verwendet werden kann.

await Excel.run(async (context) => {
    // Get the PivotTable and the date hierarchy.
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    let dateHierarchy = pivotTable.rowHierarchies.getItemOrNullObject("Date Updated");
    await context.sync();

    // PivotFilters can only be applied to PivotHierarchies that are being used for pivoting.
    // If it's not already there, add "Date Updated" to the hierarchies.
    if (dateHierarchy.isNullObject) {
        dateHierarchy = pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Date Updated"));
    }

    // Apply a date filter to filter out anything logged before August.
    let filterField = dateHierarchy.fields.getItem("Date Updated");
    let dateFilter = {
        condition: Excel.DateFilterCondition.afterOrEqualTo,
        comparator: {
        date: "2020-08-01",
        specificity: Excel.FilterDatetimeSpecificity.month
        }
    };
    filterField.applyFilter({ dateFilter: dateFilter });
    
    await context.sync();
});

Hinweis

In den folgenden drei Codeausschnitten werden anstelle vollständiger Excel.run Aufrufe nur filterspezifische Auszüge angezeigt.

PivotLabelFilter

Der zweite Codeausschnitt veranschaulicht, wie ein PivotLabelFilter auf das Type PivotField angewendet wird, wobei die LabelFilterCondition.beginsWith Eigenschaft verwendet wird, um Bezeichnungen auszuschließen, die mit dem Buchstaben L beginnen.

    // Get the "Type" field.
    let filterField = pivotTable.hierarchies.getItem("Type").fields.getItem("Type");

    // Filter out any types that start with "L" ("Lemons" and "Limes" in this case).
    let filter: Excel.PivotLabelFilter = {
      condition: Excel.LabelFilterCondition.beginsWith,
      substring: "L",
      exclusive: true
    };

    // Apply the label filter to the field.
    filterField.applyFilter({ labelFilter: filter });
PivotManualFilter

Der dritte Codeausschnitt wendet einen manuellen Filter mit PivotManualFilter auf das Klassifizierungsfeld an und filtert Daten heraus, die die Klassifizierung "Organisch" nicht enthalten.

    // Apply a manual filter to include only a specific PivotItem (the string "Organic").
    let filterField = classHierarchy.fields.getItem("Classification");
    let manualFilter = { selectedItems: ["Organic"] };
    filterField.applyFilter({ manualFilter: manualFilter });
PivotValueFilter

Verwenden Sie zum Vergleichen von Zahlen einen Wertfilter mit PivotValueFilter, wie im endgültigen Codeausschnitt dargestellt. Die PivotValueFilter Daten im FarmpivotField werden mit den Daten in den Crates Sold-PivotFields verglichen, einschließlich nur farmen, deren Summe der verkauften Kisten den Wert 500 überschreitet.

    // Get the "Farm" field.
    let filterField = pivotTable.hierarchies.getItem("Farm").fields.getItem("Farm");
    
    // Filter to only include rows with more than 500 wholesale crates sold.
    let filter: Excel.PivotValueFilter = {
      condition: Excel.ValueFilterCondition.greaterThan,
      comparator: 500,
      value: "Sum of Crates Sold Wholesale"
    };
    
    // Apply the value filter to the field.
    filterField.applyFilter({ valueFilter: filter });

Entfernen von PivotFiltern

Um alle PivotFilter zu entfernen, wenden Sie die clearAllFilters Methode auf jedes PivotField an, wie im folgenden Codebeispiel gezeigt.

await Excel.run(async (context) => {
    // Get the PivotTable.
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.hierarchies.load("name");
    await context.sync();

    // Clear the filters on each PivotField.
    pivotTable.hierarchies.items.forEach(function (hierarchy) {
        hierarchy.fields.getItem(hierarchy.name).clearAllFilters();
    });
    await context.sync();
});

Filtern mit Datenschnitten

Datenschnitte ermöglichen das Filtern von Daten aus einer Excel PivotTable oder Tabelle. Ein Datenschnitt verwendet Werte aus einer angegebenen Spalte oder einem PivotField, um entsprechende Zeilen zu filtern. Diese Werte werden als SlicerItem-Objekte in der Slicergespeichert. Ihr Add-In kann diese Filter anpassen, ebenso wie Benutzer (über die Excel Benutzeroberfläche). Der Datenschnitt befindet sich auf dem Arbeitsblatt auf der Zeichnungsebene, wie im folgenden Screenshot dargestellt.

Ein Datenschnitt, der Daten in einer PivotTable filtert.

Hinweis

Die in diesem Abschnitt beschriebenen Techniken konzentrieren sich auf die Verwendung von Datenschnitten, die mit PivotTables verbunden sind. Die gleichen Techniken gelten auch für die Verwendung von Datenschnitten, die mit Tabellen verbunden sind.

Erstellen eines Datenschnitts

Sie können einen Datenschnitt in einer Arbeitsmappe oder einem Arbeitsblatt mithilfe der Workbook.slicers.add Methode oder Worksheet.slicers.add Methode erstellen. Dadurch wird der SlicerCollection des angegebenen Workbook Objekts Worksheet ein Datenschnitt hinzugefügt. Die SlicerCollection.add Methode hat drei Parameter:

  • slicerSource: Die Datenquelle, auf der der neue Datenschnitt basiert. Dabei kann es sich um eine Zeichenfolge oder eine PivotTableZeichenfolge handeln, Tabledie den Namen oder die ID eines oder Tablemehrerer Elemente PivotTable darstellt.
  • sourceField: Das Feld in der Datenquelle, nach dem gefiltert werden soll. Dabei kann es sich um eine Zeichenfolge oder eine PivotFieldZeichenfolge handeln, TableColumndie den Namen oder die ID eines oder TableColumnmehrerer Elemente PivotField darstellt.
  • slicerDestination: Das Arbeitsblatt, in dem der neue Datenschnitt erstellt wird. Dabei kann es sich um ein Worksheet Objekt oder den Namen oder die ID einer Worksheet. Dieser Parameter ist nicht erforderlich, wenn SlicerCollection über Worksheet.slicers. In diesem Fall wird das Arbeitsblatt der Auflistung als Ziel verwendet.

Im folgenden Codebeispiel wird dem Pivot-Arbeitsblatt ein neuer Datenschnitt hinzugefügt. Die Quelle des Datenschnitts ist die Farm sales PivotTable und filtert mithilfe der Type-Daten . Der Datenschnitt wird auch als "Obstschnitt" für zukünftige Referenzen bezeichnet.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Pivot");
    let slicer = sheet.slicers.add(
        "Farm Sales" /* The slicer data source. For PivotTables, this can be the PivotTable object reference or name. */,
        "Type" /* The field in the data to filter by. For PivotTables, this can be a PivotField object reference or ID. */
    );
    slicer.name = "Fruit Slicer";
    await context.sync();
});

Filtern von Elementen mit einem Datenschnitt

Der Datenschnitt filtert die PivotTable mit Elementen aus der sourceField. Die Slicer.selectItems Methode legt die Elemente fest, die im Datenschnitt verbleiben. Diese Elemente werden als a string[]an die Methode übergeben, die die Schlüssel der Elemente darstellt. Alle Zeilen, die diese Elemente enthalten, verbleiben in der Aggregation der PivotTable. Nachfolgende Aufrufe, um selectItems die Liste auf die in diesen Aufrufen angegebenen Schlüssel festzulegen.

Hinweis

Wenn Slicer.selectItems ein Element übergeben wird, das sich nicht in der Datenquelle befindet, wird ein InvalidArgument Fehler ausgelöst. Der Inhalt kann durch die Eigenschaft überprüft werden, bei der Slicer.slicerItems es sich um eine SlicerItemCollection handelt.

Das folgende Codebeispiel zeigt drei Elemente, die für den Datenschnitt ausgewählt werden: Limonen, Limonen und Orange.

await Excel.run(async (context) => {
    let slicer = context.workbook.slicers.getItem("Fruit Slicer");
    // Anything other than the following three values will be filtered out of the PivotTable for display and aggregation.
    slicer.selectItems(["Lemon", "Lime", "Orange"]);
    await context.sync();
});

Um alle Filter aus dem Datenschnitt zu entfernen, verwenden Sie die Slicer.clearFilters Methode, wie im folgenden Beispiel gezeigt.

await Excel.run(async (context) => {
    let slicer = context.workbook.slicers.getItem("Fruit Slicer");
    slicer.clearFilters();
    await context.sync();
});

Formatieren und Formatieren eines Datenschnitts

Das Add-In kann die Anzeigeeinstellungen eines Datenschnitts über Slicer Eigenschaften anpassen. Im folgenden Codebeispiel wird die Formatvorlage auf SlicerStyleLight6 festgelegt, der Text oben im Datenschnitt auf "Obsttypen" festgelegt, der Datenschnitt an der Position (395, 15) auf der Zeichnungsebene platziert und die Größe des Datenschnitts auf 135 x 150 Pixel festgelegt.

await Excel.run(async (context) => {
    let slicer = context.workbook.slicers.getItem("Fruit Slicer");
    slicer.caption = "Fruit Types";
    slicer.left = 395;
    slicer.top = 15;
    slicer.height = 135;
    slicer.width = 150;
    slicer.style = "SlicerStyleLight6";
    await context.sync();
});

Löschen eines Datenschnitts

Rufen Sie die Slicer.delete Methode auf, um einen Datenschnitt zu löschen. Im folgenden Codebeispiel wird der erste Datenschnitt aus dem aktuellen Arbeitsblatt gelöscht.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.slicers.getItemAt(0).delete();
    await context.sync();
});

Ändern der Aggregationsfunktion

Datenhierarchien haben ihre Werte aggregiert. Bei Datensätzen mit Zahlen ist dies standardmäßig eine Summe. Die summarizeBy Eigenschaft definiert dieses Verhalten basierend auf einem AggregationFunction-Typ .

Die derzeit unterstützten Aggregationsfunktionstypen sind Sum, Count, Average, Max, Min, Product, CountNumbers, , StandardDeviation``StandardDeviationP, Variance``VariancePund Automatic (standard).

In den folgenden Codebeispielen wird die Aggregation in Mittelwerte der Daten geändert.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.dataHierarchies.load("no-properties-needed");
    await context.sync();

    // Change the aggregation from the default sum to an average of all the values in the hierarchy.
    pivotTable.dataHierarchies.items[0].summarizeBy = Excel.AggregationFunction.average;
    pivotTable.dataHierarchies.items[1].summarizeBy = Excel.AggregationFunction.average;
    await context.sync();
});

Ändern von Berechnungen mit einer ShowAsRule

PivotTables aggregieren standardmäßig die Daten ihrer Zeilen- und Spaltenhierarchien unabhängig voneinander. Eine ShowAsRule ändert die Datenhierarchie in Ausgabewerte basierend auf anderen Elementen in der PivotTable.

Das ShowAsRule Objekt hat drei Eigenschaften:

  • calculation: Der Typ der relativen Berechnung, die auf die Datenhierarchie angewendet werden soll (der Standardwert ist none).
  • baseField: Das PivotField in der Hierarchie, das die Basisdaten enthält, bevor die Berechnung angewendet wird. Da Excel PivotTables eine 1:1-Zuordnung von Hierarchie zu Feld haben, verwenden Sie denselben Namen, um sowohl auf die Hierarchie als auch auf das Feld zuzugreifen.
  • baseItem: Das einzelne PivotItem verglichen mit den Werten der Basisfelder basierend auf dem Berechnungstyp. Nicht alle Berechnungen erfordern dieses Feld.

Im folgenden Beispiel wird die Berechnung der Datenhierarchie "Summe der in der Farm verkauften Krates " auf einen Prozentsatz der Spaltensumme festgelegt. Wir möchten weiterhin, dass die Granularität auf die Obsttypebene erweitert wird, daher verwenden wir die Zeilenhierarchie "Type " und ihr zugrunde liegendes Feld. In diesem Beispiel wird auch die Farm als erste Zeilenhierarchie verwendet, sodass in den Farm-Gesamteinträgen der Prozentsatz angezeigt wird, den jede Farm ebenfalls für die Produktion verantwortlich ist.

Eine PivotTable, die die Prozentsätze der Obstumsätze relativ zur Gesamtsumme für einzelne Farmen und einzelne Obsttypen in jeder Farm anzeigt.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    let farmDataHierarchy = pivotTable.dataHierarchies.getItem("Sum of Crates Sold at Farm");

    farmDataHierarchy.load("showAs");
    await context.sync();

    // Show the crates of each fruit type sold at the farm as a percentage of the column's total.
    let farmShowAs = farmDataHierarchy.showAs;
    farmShowAs.calculation = Excel.ShowAsCalculation.percentOfColumnTotal;
    farmShowAs.baseField = pivotTable.rowHierarchies.getItem("Type").fields.getItem("Type");
    farmDataHierarchy.showAs = farmShowAs;
    farmDataHierarchy.name = "Percentage of Total Farm Sales";
});

Im vorherigen Beispiel wurde die Berechnung auf die Spalte relativ zum Feld einer einzelnen Zeilenhierarchie festgelegt. Wenn sich die Berechnung auf ein einzelnes Element bezieht, verwenden Sie die baseItem Eigenschaft.

Das folgende Beispiel zeigt die differenceFrom Berechnung. Es zeigt den Unterschied zwischen den Farm-Crate-Umsatzdatenhierarchieeinträgen relativ zu den Einträgen einer Farm an. The baseField is Farm, so we see the differences between the other farms, as well as breakdowns for each type of like fruit (Type is also a row hierarchy in this example).

Eine PivotTable, die die Unterschiede der Obstumsätze zwischen "A Farms" und den anderen zeigt. Dies zeigt sowohl den Unterschied bei den Gesamtobstumsätzen der Farmen als auch bei den Verkäufen von Obstarten. Wenn "A Farms" keine bestimmte Art von Obst verkauft hat, wird "#N/A" angezeigt.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    let farmDataHierarchy = pivotTable.dataHierarchies.getItem("Sum of Crates Sold at Farm");

    farmDataHierarchy.load("showAs");
    await context.sync();
        
    // Show the difference between crate sales of the "A Farms" and the other farms.
    // This difference is both aggregated and shown for individual fruit types (where applicable).
    let farmShowAs = farmDataHierarchy.showAs;
    farmShowAs.calculation = Excel.ShowAsCalculation.differenceFrom;
    farmShowAs.baseField = pivotTable.rowHierarchies.getItem("Farm").fields.getItem("Farm");
    farmShowAs.baseItem = pivotTable.rowHierarchies.getItem("Farm").fields.getItem("Farm").items.getItem("A Farms");
    farmDataHierarchy.showAs = farmShowAs;
    farmDataHierarchy.name = "Difference from A Farms";
});

Ändern von Hierarchienamen

Hierarchiefelder können bearbeitet werden. Der folgende Code veranschaulicht, wie die angezeigten Namen von zwei Datenhierarchien geändert werden.

await Excel.run(async (context) => {
    let dataHierarchies = context.workbook.worksheets.getActiveWorksheet()
        .pivotTables.getItem("Farm Sales").dataHierarchies;
    dataHierarchies.load("no-properties-needed");
    await context.sync();

    // Changing the displayed names of these entries.
    dataHierarchies.items[0].name = "Farm Sales";
    dataHierarchies.items[1].name = "Wholesale";
});

Weitere Informationen