Trabajar con tablas dinámicas mediante la API de JavaScript de Excel

Las tablas dinámicas simplifican conjuntos de datos más grandes. Permiten la manipulación rápida de datos agrupados. La API de JavaScript de Excel permite que el complemento cree tablas dinámicas e interactúe con sus componentes. En este artículo se describe cómo se representan las tablas dinámicas mediante la API de JavaScript de Office y se proporcionan ejemplos de código para escenarios clave.

Si no está familiarizado con la funcionalidad de las tablas dinámicas, considere la posibilidad de explorarlas como usuario final. Consulte Creación de una tabla dinámica para analizar los datos de la hoja de cálculo para obtener una buena introducción a estas herramientas.

Importante

Actualmente no se admiten las tablas dinámicas creadas con OLAP. Tampoco hay compatibilidad con Power Pivot.

Modelo de objetos

La tabla dinámica es el objeto central de las tablas dinámicas en la API de JavaScript de Office.

• Workbook.pivotTables y Worksheet.pivotTables son PivotTableCollections que contienen las tablas dinámicas en el libro y la hoja de cálculo, respectivamente.
• Una tabla dinámica contiene una clase PivotHierarchyCollection que tiene varias instancias de PivotHierarchies.
• Estas pivotHierarchies se pueden agregar a colecciones de jerarquías específicas para definir cómo pivota los datos de la tabla dinámica (como se explica en la sección siguiente).
• Una pivotHierarchy contiene una clase PivotFieldCollection que tiene exactamente un pivotfield. Si el diseño se expande para incluir tablas dinámicas OLAP, esto puede cambiar.
• Un campo dinámico puede tener uno o varios PivotFilters aplicados , siempre y cuando la pivotHierarchy del campo esté asignada a una categoría de jerarquía.
• Un pivotfield contiene un PivotItemCollection que tiene varios pivotItems.
• Una tabla dinámica contiene un elemento PivotLayout que define dónde se muestran los campos dinámicos y los elementos dinámicos en la hoja de cálculo. El diseño también controla algunos valores de visualización de la tabla dinámica.

Veamos cómo se aplican estas relaciones a algunos datos de ejemplo. En los datos siguientes se describen las ventas de frutas de varias granjas de servidores. Será el ejemplo a lo largo de este artículo.

Estos datos de ventas de granjas de frutas se usarán para crear una tabla dinámica. Cada columna, como Types, es .PivotHierarchy La jerarquía Tipos contiene el campo Tipos . El campo Tipos contiene los elementos Apple, Kiwi, Lemon, Lime y Orange.

Hierarchies

Las tablas dinámicas se organizan en función de cuatro categorías de jerarquía: fila, columna, datos y filtro.

Los datos de la granja de servidores mostrados anteriormente tienen cinco jerarquías: Granjas, Tipo, Clasificación, Cajas vendidas en granja y Cajas vendidas al por mayor. Cada jerarquía solo puede existir en una de las cuatro categorías. Si type se agrega a las jerarquías de columna, tampoco puede estar en las jerarquías de fila, datos o filtros. Si Type se agrega posteriormente a las jerarquías de filas, se quita de las jerarquías de columna. Este comportamiento es el mismo si la asignación de jerarquía se realiza a través de la interfaz de usuario de Excel o las API de JavaScript de Excel.

Las jerarquías de filas y columnas definen cómo se agruparán los datos. Por ejemplo, una jerarquía de filas de granjas de servidores agrupará todos los conjuntos de datos de la misma granja de servidores. La elección entre la jerarquía de filas y columnas define la orientación de la tabla dinámica.

Las jerarquías de datos son los valores que se van a agregar en función de las jerarquías de filas y columnas. Una tabla dinámica con una jerarquía de filas de granjas de servidores y una jerarquía de datos de Crates Sold Wholesale muestra la suma total (de forma predeterminada) de todas las frutas diferentes para cada granja de servidores.

Las jerarquías de filtro incluyen o excluyen datos de la tabla dinámica en función de los valores dentro de ese tipo filtrado. Una jerarquía de filtros de Clasificación con el tipo Orgánico seleccionado solo muestra los datos de la fruta orgánica.

Aquí están los datos de la granja de servidores de nuevo, junto con una tabla dinámica. La tabla dinámica usa Farm y Type como jerarquías de filas, Crates Sold at Farm y Crates Sold Wholesale como jerarquías de datos (con la función de agregación predeterminada de sum) y Classification como jerarquía de filtros (con Organic seleccionado).

Esta tabla dinámica se puede generar a través de la API de JavaScript o a través de la interfaz de usuario de Excel. Ambas opciones permiten una manipulación adicional a través de complementos.

Creación de una tabla dinámica

Las tablas dinámicas necesitan un nombre, un origen y un destino. El origen puede ser una dirección de intervalo o un nombre de tabla (pasado como un Rangetipo , stringo Table ). El destino es una dirección de intervalo (dado como o Rangestring). En los ejemplos siguientes se muestran varias técnicas de creación de tablas dinámicas.

Creación de una tabla dinámica con direcciones de intervalo

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();
});

Creación de una tabla dinámica con objetos Range

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();
});

Creación de una tabla dinámica en el nivel de libro

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();
});

Uso de una tabla dinámica existente

Las tablas dinámicas creadas manualmente también son accesibles a través de la colección de tablas dinámicas del libro o de hojas de cálculo individuales. El código siguiente obtiene una tabla dinámica denominada My Pivot del libro.

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

Adición de filas y columnas a una tabla dinámica

Las filas y columnas pivotan los datos alrededor de los valores de esos campos.

La adición de la columna Granja de servidores pivota todas las ventas alrededor de cada granja de servidores. Al agregar las filas Tipo y Clasificación , se desglosan aún más los datos en función de la fruta que se vendió y de si era orgánica o no.

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();
});

También puede tener una tabla dinámica con solo filas o columnas.

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();
});

Adición de jerarquías de datos a la tabla dinámica

Las jerarquías de datos rellenan la tabla dinámica con información para combinar en función de las filas y columnas. Al agregar las jerarquías de datos de los contenedores vendidos en granja y los contenedores vendidos al por mayor , se proporcionan sumas de esas cifras para cada fila y columna.

En el ejemplo, tanto Farm como Type son filas, con las ventas de caja como datos.

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();
});

Diseños de tabla dinámica y obtención de datos dinámicos

Un elemento PivotLayout define la ubicación de las jerarquías y sus datos. Tiene acceso al diseño para determinar los intervalos donde se almacenan los datos.

En el diagrama siguiente se muestran las llamadas de función de diseño correspondientes a los intervalos de la tabla dinámica.

Obtener datos de la tabla dinámica

El diseño define cómo se muestra la tabla dinámica en la hoja de cálculo. Esto significa que el PivotLayout objeto controla los intervalos utilizados para los elementos de tabla dinámica. Use los intervalos proporcionados por el diseño para obtener los datos recopilados y agregados por la tabla dinámica. En concreto, use PivotLayout.getDataBodyRange para acceder a los datos generados por la tabla dinámica.

En el código siguiente se muestra cómo obtener la última fila de los datos de tabla dinámica pasando por el diseño (el total general de las columnas Suma de cajas vendidas en granja de servidores y Suma de cajas vendidas al por mayor en el ejemplo anterior). A continuación, esos valores se suman para un total final, que se muestra en la celda E30 (fuera de la tabla dinámica).

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();
});

Tipos de diseño

Las tablas dinámicas tienen tres estilos de diseño: Compacto, Esquema y Tabular. Hemos visto el estilo compacto en los ejemplos anteriores.

En los ejemplos siguientes se usan los estilos esquema y tabular, respectivamente. En el ejemplo de código se muestra cómo realizar un ciclo entre los diferentes diseños.

Diseño de esquema

Diseño tabular

Ejemplo de código de conmutador de tipo PivotLayout

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();
});

Otras funciones PivotLayout

De forma predeterminada, las tablas dinámicas ajustan los tamaños de fila y columna según sea necesario. Esto se hace cuando se actualiza la tabla dinámica. PivotLayout.autoFormat especifica ese comportamiento. Los cambios de tamaño de fila o columna realizados por el complemento persisten cuando autoFormat es false. Además, la configuración predeterminada de una tabla dinámica mantiene cualquier formato personalizado en la tabla dinámica (como rellenos y cambios de fuente). false Establézcalo PivotLayout.preserveFormatting en para aplicar el formato predeterminado cuando se actualice.

También PivotLayout controla la configuración de encabezados y filas totales, cómo se muestran las celdas de datos vacías y las opciones de texto alternativo . La referencia PivotLayout proporciona una lista completa de estas características.

El ejemplo de código siguiente hace que las celdas de datos vacías muestren la cadena "--", da formato al intervalo de cuerpo a una alineación horizontal coherente y garantiza que los cambios de formato permanezcan incluso después de actualizar la tabla dinámica.

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();
});

Eliminación de una tabla dinámica

Las tablas dinámicas se eliminan mediante su nombre.

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

Filtrar una tabla dinámica

El método principal para filtrar datos de tabla dinámica es con PivotFilters. Las segmentaciones ofrecen un método de filtrado alternativo y menos flexible.

PivotFilters filtra los datos en función de las cuatro categorías de jerarquía de una tabla dinámica (filtros, columnas, filas y valores). Hay cuatro tipos de PivotFilters, lo que permite el filtrado basado en fechas del calendario, el análisis de cadenas, la comparación de números y el filtrado en función de una entrada personalizada.

Las segmentaciones de datos se pueden aplicar a tablas dinámicas y tablas regulares de Excel. Cuando se aplica a una tabla dinámica, las segmentaciones de datos funcionan como pivotManualFilter y permiten el filtrado en función de una entrada personalizada. A diferencia de pivotFilters, las segmentaciones de datos tienen un componente de interfaz de usuario de Excel. Con la Slicer clase , se crea este componente de interfaz de usuario, se administra el filtrado y se controla su apariencia visual.

Filtrar con PivotFilters

Los filtros dinámicos permiten filtrar los datos de tabla dinámica en función de las cuatro categorías de jerarquía (filtros, columnas, filas y valores). En el modelo de objetos de tabla dinámica, PivotFilters se aplican a un campo dinámico y cada PivotField uno puede tener uno o varios asignados PivotFilters. Para aplicar PivotFilters a un campo dinámico, la pivothierarchía correspondiente del campo debe asignarse a una categoría de jerarquía.

Tipos de pivotfilters

Tipo de filtro	Propósito de filtro	Referencia de la API de JavaScript de Excel
DateFilter	Filtrado basado en fechas del calendario.	PivotDateFilter
LabelFilter	Filtrado de comparación de texto.	PivotLabelFilter
ManualFilter	Filtrado de entrada personalizado.	PivotManualFilter
ValueFilter	Filtrado de comparación de números.	PivotValueFilter

Creación de un objeto PivotFilter

Para filtrar datos de tabla dinámica con ( Pivot*Filter como ), PivotDateFilteraplique el filtro a un campo dinámico. En los cuatro ejemplos de código siguientes se muestra cómo usar cada uno de los cuatro tipos de PivotFilters.

PivotDateFilter

El primer ejemplo de código aplica un pivotDateFilter al campo dinámico de fecha actualizada , ocultando los datos anteriores a 2020-08-01.

Importante

No Pivot*Filter se puede aplicar a un campo dinámico a menos que la pivotHierarchy de ese campo esté asignada a una categoría de jerarquía. En el ejemplo de código siguiente, dateHierarchy debe agregarse a la categoría de rowHierarchies la tabla dinámica antes de que se pueda usar para el filtrado.

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();
});

Nota:

Los tres fragmentos de código siguientes solo muestran extractos específicos del filtro, en lugar de llamadas completas Excel.run .

PivotLabelFilter

El segundo fragmento de código muestra cómo aplicar pivotLabelFilter al campo dinámico de tipo , utilizando la LabelFilterCondition.beginsWith propiedad para excluir las etiquetas que comienzan por la letra L.

    // 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

El tercer fragmento de código aplica un filtro manual con PivotManualFilter al campo Clasificación , filtrando los datos que no incluyen la clasificación Organic.

    // 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

Para comparar números, use un filtro de valor con PivotValueFilter, como se muestra en el fragmento de código final. PivotValueFilter compara los datos del campo dinámico de granja de servidores con los datos del campo dinámico mayorista de cajas vendidas, incluidas solo las granjas cuya suma de cajas vendidas supera el valor 500.

    // 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 });

Eliminación de pivotfilters

Para quitar todos los PivotFilters, aplique el clearAllFilters método a cada PivotField, como se muestra en el ejemplo de código siguiente.

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();
});

Filtrar con segmentaciones de datos

Las segmentaciones de datos permiten filtrar datos desde una tabla o tabla dinámica de Excel. Una segmentación de datos usa valores de una columna especificada o pivotfield para filtrar las filas correspondientes. Estos valores se almacenan como objetos SlicerItem en .Slicer El complemento puede ajustar estos filtros, al igual que los usuarios (a través de la interfaz de usuario de Excel). La segmentación se encuentra encima de la hoja de cálculo de la capa de dibujo, como se muestra en la captura de pantalla siguiente.

Nota:

Las técnicas descritas en esta sección se centran en cómo usar segmentaciones de datos conectadas a tablas dinámicas. Las mismas técnicas también se aplican al uso de segmentaciones de datos conectadas a tablas.

Creación de una segmentación de datos

Puede crear una segmentación de datos en un libro o hoja de cálculo mediante el método o Worksheet.slicers.add el Workbook.slicers.add método . Al hacerlo, se agrega una segmentación de datos a slicerCollection del objeto o Worksheet especificadoWorkbook. El SlicerCollection.add método tiene tres parámetros:

• slicerSource: origen de datos en el que se basa la nueva segmentación de datos. Puede ser una PivotTablecadena , Tableo que representa el nombre o el identificador de o PivotTableTable.
• sourceField: campo del origen de datos por el que se va a filtrar. Puede ser una PivotFieldcadena , TableColumno que representa el nombre o el identificador de o PivotFieldTableColumn.
• slicerDestination: la hoja de cálculo donde se creará la nueva segmentación de datos. Puede ser un Worksheet objeto o el nombre o identificador de un Worksheetobjeto . Este parámetro no es necesario cuando SlicerCollection se tiene acceso a a través de Worksheet.slicers. En este caso, la hoja de cálculo de la colección se usa como destino.

En el ejemplo de código siguiente se agrega una nueva segmentación a la hoja de cálculo dinámica . El origen de la segmentación es la tabla dinámica ventas de la granja de servidores y filtra mediante los datos type . La segmentación de datos también se denomina Fruit Slicer para referencia futura.

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();
});

Filtrar elementos con una segmentación de datos

La segmentación filtra la tabla dinámica con elementos de sourceField. El Slicer.selectItems método establece los elementos que permanecen en la segmentación de datos. Estos elementos se pasan al método como , string[]que representa las claves de los elementos. Las filas que contienen esos elementos permanecen en la agregación de la tabla dinámica. Llamadas posteriores para selectItems establecer la lista en las claves especificadas en esas llamadas.

Nota:

Si Slicer.selectItems se pasa un elemento que no está en el origen de datos, se produce un InvalidArgument error. El contenido se puede comprobar a través de la Slicer.slicerItems propiedad , que es SlicerItemCollection.

En el ejemplo de código siguiente se muestran tres elementos seleccionados para la segmentación de datos: Lemon, Lime y 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();
});

Para quitar todos los filtros de la segmentación de datos, use el Slicer.clearFilters método , como se muestra en el ejemplo siguiente.

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

Estilo y formato de una segmentación de datos

El complemento puede ajustar la configuración de visualización de una segmentación de datos a través Slicer de las propiedades. El ejemplo de código siguiente establece el estilo en SlicerStyleLight6, establece el texto de la parte superior de la segmentación en Fruit Types, coloca la segmentación en la posición (395, 15) en la capa de dibujo y establece el tamaño de la segmentación en 135x150 píxeles.

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();
});

Eliminación de una segmentación de datos

Para eliminar una segmentación de datos, llame al Slicer.delete método . En el ejemplo de código siguiente se elimina la primera segmentación de la hoja de cálculo actual.

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

Cambiar la función de agregación

Las jerarquías de datos tienen sus valores agregados. Para conjuntos de datos de números, se trata de una suma de forma predeterminada. La summarizeBy propiedad define este comportamiento en función de un tipo AggregationFunction .

Los tipos de función de agregación admitidos actualmente son Sum, Count, Average, Max, Min, Product, CountNumbers, StandardDeviationStandardDeviationP, Variance, VariancePy Automatic (el valor predeterminado).

Los ejemplos de código siguientes cambian la agregación para que sean promedios de los datos.

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();
});

Cambiar los cálculos con ShowAsRule

De forma predeterminada, las tablas dinámicas agregan los datos de sus jerarquías de filas y columnas de forma independiente. ShowAsRule cambia la jerarquía de datos a valores de salida basados en otros elementos de la tabla dinámica.

El ShowAsRule objeto tiene tres propiedades:

• calculation: tipo de cálculo relativo que se va a aplicar a la jerarquía de datos (el valor predeterminado es none).
• baseField: campo dinámico de la jerarquía que contiene los datos base antes de aplicar el cálculo. Puesto que las tablas dinámicas de Excel tienen una asignación uno a uno de jerarquía a campo, usará el mismo nombre para acceder a la jerarquía y al campo.
• baseItem: el objeto PivotItem individual comparado con los valores de los campos base en función del tipo de cálculo. No todos los cálculos requieren este campo.

En el ejemplo siguiente se establece que el cálculo de la jerarquía de datos Suma de cajas vendidas en granja de servidores sea un porcentaje del total de la columna. Todavía queremos que la granularidad se extienda al nivel de tipo de fruta, por lo que usaremos la jerarquía de filas De tipo y su campo subyacente. El ejemplo también tiene Farm como la primera jerarquía de filas, por lo que las entradas totales de la granja muestran el porcentaje que cada granja de servidores es responsable de producir también.

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";
});

En el ejemplo anterior se establece el cálculo en la columna, en relación con el campo de una jerarquía de filas individual. Cuando el cálculo se relacione con un elemento individual, use la baseItem propiedad .

En el ejemplo siguiente se muestra el differenceFrom cálculo. Muestra la diferencia de las entradas de la jerarquía de datos de ventas de los contenedores de granja de servidores en relación con las de A Farms. baseField Es Granja, por lo que vemos las diferencias entre las otras granjas, así como los desgloses de cada tipo de fruta similar (El tipo también es una jerarquía de filas en este ejemplo).

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";
});

Cambio de nombres de jerarquía

Los campos de jerarquía son editables. En el código siguiente se muestra cómo cambiar los nombres mostrados de dos jerarquías de datos.

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";
});

Vea también

• Modelo de objetos de JavaScript en Excel para Complementos de Office
• Referencia de la API de JavaScript de Excel