Trabajar con tablas dinámicas mediante la API Excel JavaScript

Las tablas dinámicas simplifican los conjuntos de datos más grandes. Permiten la manipulación rápida de datos agrupados. La API Excel JavaScript permite al complemento crear tablas dinámicas e interactuar con sus componentes. En este artículo se describe cómo se representan las tablas dinámicas mediante la API Office JavaScript 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 Create a PivotTable to analyze worksheet data for a good primer on these tools.

Importante

Actualmente no se admiten 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 de la API Office JavaScript.

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

Una colección de ventas de frutas de diferentes tipos de granjas de servidores.

Los datos de ventas de esta granja de frutas se usarán para crear una tabla dinámica. Cada columna, como Types, es un 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, datosy filtro.

Los datos de granja de servidores mostrados anteriormente tienen cinco jerarquías: Farms, Type, Classification, Crates Sold at Farm y Crates Sold Wholesale. Cada jerarquía solo puede existir en una de las cuatro categorías. Si Type se agrega a las jerarquías de columnas, tampoco puede estar en las jerarquías de fila, datos o filtro. Si Type se agrega posteriormente a las jerarquías de filas, se quita de las jerarquías de columnas. Este comportamiento es el mismo si la asignación de jerarquía se realiza Excel la interfaz de usuario o las API Excel JavaScript.

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 diferentes frutas de cada granja.

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

Estos son 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 suma) y Classification como jerarquía de filtros (con Organic seleccionado).

Una selección de datos de ventas de frutas junto a una tabla dinámica con jerarquías de filas, datos y filtros.

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

Crear una tabla dinámica

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

Crear una tabla dinámica con direcciones de intervalo

Excel.run(function (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");

    return context.sync();
});

Crear una tabla dinámica con objetos Range

Excel.run(function (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.
    var rangeToAnalyze = context.workbook.worksheets.getItem("DataWorksheet").getRange("A1:E21");
    var rangeToPlacePivot = context.workbook.worksheets.getItem("PivotWorksheet").getRange("A2");
    context.workbook.worksheets.getItem("PivotWorksheet").pivotTables.add(
      "Farm Sales", rangeToAnalyze, rangeToPlacePivot);

    return context.sync();
});

Crear una tabla dinámica en el nivel del libro

Excel.run(function (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");

    return context.sync();
});

Usar una tabla dinámica existente

También se puede acceder a las tablas dinámicas creadas manualmente a través de la colección de tablas dinámicas del libro o de hojas de cálculo individuales. El siguiente código obtiene una tabla dinámica denominada My Pivot del libro.

Excel.run(function (context) {
    var pivotTable = context.workbook.pivotTables.getItem("My Pivot");
    return context.sync();
});

Agregar filas y columnas a una tabla dinámica

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

Agregar 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.

Tabla dinámica con una columna farm y filas Type y Classification.

Excel.run(function (context) {
    var 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"));

    return context.sync();
});

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

Excel.run(function (context) {
    var 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"));

    return context.sync();
});

Agregar 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. Agregar las jerarquías de datos de los crates vendidos en granja de servidores y los crates vendidos al por mayor proporciona sumas de esas cifras para cada fila y columna.

En el ejemplo, Farm y Type son filas, con las ventas de cajas como datos.

Tabla dinámica que muestra las ventas totales de diferentes frutas en función de la granja de servidores de la que provenían.

Excel.run(function (context) {
    var 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"));

    return context.sync();
});

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

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

El diagrama siguiente muestra qué llamadas de función de diseño corresponden a los intervalos de la tabla dinámica.

Diagrama que muestra qué secciones de una tabla dinámica devuelven las funciones de intervalo get del diseño.

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 PivotLayout el objeto controla los intervalos usados para los elementos de tabla dinámica. Use los intervalos proporcionados por el diseño para obtener datos recopilados y agregados por la tabla dinámica. En concreto, se PivotLayout.getDataBodyRange usa para obtener acceso a los datos generados por la tabla dinámica.

El siguiente código muestra cómo obtener la última fila de los datos de tabla dinámica mediante 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, estos valores se suman para un total final, que se muestra en la celda E30 (fuera de la tabla dinámica).

Excel.run(function (context) {
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    // Get the totals for each data hierarchy from the layout.
    var range = pivotTable.layout.getDataBodyRange();
    var grandTotalRange = range.getLastRow();
    grandTotalRange.load("address");
    return context.sync().then(function () {
        // Sum the totals from the PivotTable data hierarchies and place them in a new range, outside of the PivotTable.
        var masterTotalRange = context.workbook.worksheets.getActiveWorksheet().getRange("E30");
        masterTotalRange.formulas = [["=SUM(" + grandTotalRange.address + ")"]];
    });
});

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.

Los ejemplos siguientes usan los estilos de esquema y tabular, respectivamente. En el ejemplo de código se muestra cómo recorrer los distintos diseños.

Diseño de esquema

Una tabla dinámica con el diseño de esquema.

Diseño tabular

Tabla dinámica con el diseño tabular.

Ejemplo de código de modificador de tipo PivotLayout

Excel.run(function (context) {
    // Change the PivotLayout.type to a new type.
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.layout.load("layoutType");
    return context.sync().then(function () {
        // 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";
        }
    
        return 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). Se PivotLayout.preserveFormatting establece para aplicar el formato predeterminado cuando se false actualice.

También controla la configuración de encabezado y fila total, cómo se muestran las celdas de datos vacías y PivotLayout 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.

Excel.run(function (context) {
    var pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
    var 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;
    return context.sync();
});

Eliminar una tabla dinámica

Las tablas dinámicas se eliminan con su nombre.

Excel.run(function (context) {
    context.workbook.worksheets.getItem("Pivot").pivotTables.getItem("Farm Sales").delete();
    return context.sync();
});

Filtrar una tabla dinámica

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

Los filtros dinámicos filtran 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 filtros dinámicos, que permiten el filtrado basado en fechas de 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 tanto a tablas dinámicas como a tablas Excel normales. Cuando se aplica a una tabla dinámica, las segmentaciones de datos funcionan como un PivotManualFilter y permiten el filtrado en función de una entrada personalizada. A diferencia de pivotFilters, las segmentaciones de datos tienen un Excel de interfaz de usuario . Con la Slicer clase, creas este componente de interfaz de usuario, administras el filtrado y controlas su apariencia visual.

Filtrar con filtros dinámicos

Los filtros dinámicos permiten filtrar 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, se aplican a un campo dinámico y cada uno puede PivotFilters tener asignado uno o más PivotField PivotFilters . Para aplicar PivotFilters a un campo dinámico, la pivotHierarchy correspondiente del campo debe asignarse a una categoría de jerarquía.

Tipos de filtros dinámicos

Tipo de filtro Propósito de filtro Referencia de la API de JavaScript de Excel
DateFilter Filtrado basado en fechas de 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

Crear un filtro dinámico

Para filtrar los datos de tabla dinámica Pivot*Filter con un (por ejemplo, PivotDateFilter un ), aplique el filtro a un campo dinámico. Los cuatro ejemplos de código siguientes muestran cómo usar cada uno de los cuatro tipos de filtros dinámicos.

PivotDateFilter

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

Importante

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

Excel.run(function (context) {
    // Get the PivotTable and the date hierarchy.
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    var dateHierarchy = pivotTable.rowHierarchies.getItemOrNullObject("Date Updated");
    
    return context.sync().then(function () {
        // 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.
        var filterField = dateHierarchy.fields.getItem("Date Updated");
        var dateFilter = {
          condition: Excel.DateFilterCondition.afterOrEqualTo,
          comparator: {
            date: "2020-08-01",
            specificity: Excel.FilterDatetimeSpecificity.month
          }
        };
        filterField.applyFilter({ dateFilter: dateFilter });
        
        return context.sync();
    });
});

Nota

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

PivotLabelFilter

El segundo fragmento de código muestra cómo aplicar un PivotLabelFilter al campo dinámico de tipo, usando la propiedad para excluir etiquetas que empiezan por la LabelFilterCondition.beginsWith letra L.

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

    // Filter out any types that start with "L" ("Lemons" and "Limes" in this case).
    var 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 datos que no incluyen la clasificación Organic.

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

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

    // Get the "Farm" field.
    var filterField = pivotTable.hierarchies.getItem("Farm").fields.getItem("Farm");
    
    // Filter to only include rows with more than 500 wholesale crates sold.
    var 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 });

Quitar filtros dinámicos

Para quitar todos los filtros dinámicos, aplique el método a cada campo dinámico, como clearAllFilters se muestra en el siguiente ejemplo de código.

Excel.run(function (context) {
    // Get the PivotTable.
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.hierarchies.load("name");
    
    return context.sync().then(function () {
        // Clear the filters on each PivotField.
        pivotTable.hierarchies.items.forEach(function (hierarchy) {
          hierarchy.fields.getItem(hierarchy.name).clearAllFilters();
        });
        return context.sync();
    });
});

Filtrar con segmentaciones de datos

Las segmentaciones de datos permiten filtrar los datos Excel tabla dinámica o tabla. 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 . El complemento puede ajustar estos filtros, al igual que los usuarios ( a través dela interfaz Excel usuario). La segmentación de datos se encuentra en la parte superior de la hoja de cálculo de la capa de dibujo, como se muestra en la siguiente captura de pantalla.

Una segmentación de datos de filtrado en una tabla dinámica.

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.

Crear una segmentación de datos

Puede crear una segmentación de datos en un libro o hoja de cálculo mediante el Workbook.slicers.add método o Worksheet.slicers.add método. Al hacerlo, se agrega una segmentación de datos a slicerCollection del objeto Workbook o Worksheet especificado. 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 PivotTable cadena , , o que representa el nombre o el identificador de un o Table PivotTable Table .
  • sourceField: campo del origen de datos por el que se va a filtrar. Puede ser una PivotField cadena , , o que representa el nombre o el identificador de un o TableColumn PivotField TableColumn .
  • slicerDestination: hoja de cálculo donde se creará la nueva segmentación de datos. Puede ser un Worksheet objeto o el nombre o el identificador de un Worksheet . Este parámetro no es necesario cuando se tiene acceso SlicerCollection a él 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 de datos a la hoja de cálculo dinámica. El origen de la segmentación de datos es la tabla dinámica Ventas de la granja de servidores y se filtra mediante los datos Type. La segmentación de datos también se denomina Fruit Slicer para referencia futura.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Pivot");
    var 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";
    return 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[] un , 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 selectItems para 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: Lima, Lima y Naranja.

Excel.run(function (context) {
    var 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"]);
    return 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.

Excel.run(function (context) {
    var slicer = context.workbook.slicers.getItem("Fruit Slicer");
    slicer.clearFilters();
    return context.sync();
});

Aplicar estilo y formato a una segmentación de datos

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

Excel.run(function (context) {
    var 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";
    return context.sync();
});

Eliminar 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 datos de la hoja de cálculo actual.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.slicers.getItemAt(0).delete();
    return context.sync();
});

Función de agregación de cambios

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 y Max Min Product CountNumbers StandardDeviation StandardDeviationP Variance VarianceP Automatic (el valor predeterminado).

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

Excel.run(function (context) {
    var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.dataHierarchies.load("no-properties-needed");
    return context.sync().then(function() {

        // 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;
        return context.sync();
    });
});

Cambiar cálculos con un ShowAsRule

Las tablas dinámicas, de forma predeterminada, agregan los datos de sus jerarquías de filas y columnas de forma independiente. Un 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: El campo dinámico de la jerarquía que contiene los datos base antes de aplicar el cálculo. Dado Excel las tablas dinámicas tienen una asignación uno a uno de jerarquía a campo, usará el mismo nombre para tener acceso tanto a la jerarquía como al campo.
  • baseItem: 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 suma de cajas vendidas en la jerarquía de datos de la granja de servidores sea un porcentaje del total de columnas. Todavía queremos que la granularidad se extienda al nivel de tipo de fruto, por lo que usaremos la jerarquía de filas Tipo y su campo subyacente. El ejemplo también tiene Farm como la jerarquía de primera fila, por lo que las entradas totales de la granja muestran el porcentaje que cada granja de servidores es responsable de producir también.

Tabla dinámica que muestra los porcentajes de ventas de frutas con relación al total general de granjas de servidores individuales y tipos de frutas individuales dentro de cada granja.

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

    farmDataHierarchy.load("showAs");
    return context.sync().then(function () {

        // Show the crates of each fruit type sold at the farm as a percentage of the column's total.
        var 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 relaciona 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 jerarquía de datos de ventas de la caja de la granja de servidores con respecto a las de granjas de servidores A. 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).

Tabla dinámica que muestra las diferencias de ventas de frutas entre "Granjas de servidores" y las demás. Esto muestra tanto la diferencia en las ventas totales de frutas de las granjas como en las ventas de tipos de frutas. Si "Granjas de servidores" no ha vendido un tipo determinado de fruta, se muestra "#N/A".

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

    farmDataHierarchy.load("showAs");
    return context.sync().then(function () {
        // 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).
        var 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";
    });
});

Cambiar nombres de jerarquía

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

Excel.run(function (context) {
    var dataHierarchies = context.workbook.worksheets.getActiveWorksheet()
        .pivotTables.getItem("Farm Sales").dataHierarchies;
    dataHierarchies.load("no-properties-needed");
    return context.sync().then(function () {
        // changing the displayed names of these entries
        dataHierarchies.items[0].name = "Farm Sales";
        dataHierarchies.items[1].name = "Wholesale";
    });
});

Vea también