Trabajar con tablas dinámicas en Scripts de Office

Artículo
05/03/2024

Las tablas dinámicas permiten analizar rápidamente grandes colecciones de datos. Con su poder viene la complejidad. Las API de scripts de Office le permiten personalizar una tabla dinámica para satisfacer sus necesidades, pero el ámbito del conjunto de API hace que empezar sea un desafío. En este artículo se muestra cómo realizar tareas comunes de tabla dinámica y se explican clases y métodos importantes.

Nota:

Para comprender mejor el contexto de los términos utilizados por las API, lea primero la documentación de tabla dinámica de Excel. Comience con Create una tabla dinámica para analizar los datos de la hoja de cálculo.

Modelo de objetos

Imagen simplificada de las clases, métodos y propiedades que se usan al trabajar con tablas dinámicas.

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

El objeto Workbook tiene una colección de todas las tablas dinámicas. Cada hoja de cálculo también contiene una colección de tabla dinámica que es local a esa hoja.
Una tabla dinámica contiene PivotHierarchies. Una jerarquía se puede considerar como una columna de una tabla.
PivotHierarchies se puede agregar como filas o columnas (RowColumnPivotHierarchy), datos (DataPivotHierarchy) o filtros (FilterPivotHierarchy).
Cada pivotHierarchy contiene exactamente un campo dinámico. Las estructuras de tabla dinámica fuera de Excel pueden contener varios campos por jerarquía, por lo que este diseño existe para admitir opciones futuras. En el caso de los scripts de Office, los campos y las jerarquías se asignan a la misma información.
Un campo dinámico contiene varios elementos PivotItem. Cada pivotItem es un valor único en el campo. Piense en cada elemento como un valor de la columna de tabla. Los elementos también pueden ser valores agregados, como sumas, si el campo se usa para los datos.
PivotLayout define cómo se muestran los pivotfields y pivotitems.
PivotFilters filtra los datos de la tabla dinámica con criterios diferentes.

Para ver cómo funcionan estas relaciones en la práctica, empiece por descargar el libro de ejemplo. Esos datos describen las ventas de frutas de varias granjas. Es la base de todos los ejemplos de este artículo. Ejecute los scripts de ejemplo a lo largo del artículo para crear y explorar tablas dinámicas.

Descarga del libro de ejemplo

Colección de ventas de frutas de diferentes tipos de granjas.

Create una tabla dinámica con campos

Las tablas dinámicas se crean con referencias a datos existentes. Tanto los intervalos como las tablas pueden ser el origen de una tabla dinámica. También necesitan un lugar para existir en el libro. Dado que el tamaño de una tabla dinámica es dinámico, solo se especifica la esquina superior izquierda del intervalo de destino.

El siguiente fragmento de código crea una tabla dinámica basada en un intervalo de datos. La tabla dinámica no tiene jerarquías, por lo que los datos aún no se agrupan de ninguna manera.

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

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

Una tabla dinámica denominada

Jerarquías y campos

Las tablas dinámicas se organizan mediante jerarquías. Esas jerarquías se usan para dinamizar los datos cuando se agregan como un tipo específico de jerarquía. Hay cuatro tipos de jerarquías.

Fila: muestra elementos en filas horizontales.
Columna: muestra elementos en columnas verticales.
Datos: muestra agregados de valores basados en las filas y columnas.
Filtro: agrega o quita elementos de la tabla dinámica.

Una tabla dinámica puede tener tantos o como pocos de sus campos asignados a estas jerarquías específicas. Una tabla dinámica necesita al menos una jerarquía de datos para mostrar datos numéricos resumidos y al menos una fila o columna en la que pivotar ese resumen. El siguiente fragmento de código agrega dos jerarquías de filas y dos jerarquías de datos.

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

Tabla dinámica que muestra las ventas totales de diferentes frutas basadas en la granja de la que proceden.

Intervalos de diseño

Cada parte de la tabla dinámica se asigna a un intervalo. Esto permite que el script obtenga datos de la tabla dinámica para usarlos más adelante en el script o para devolverlos en un flujo de Power Automate. Se obtiene acceso a estos intervalos a través del objeto PivotLayout adquirido de PivotTable.getLayout(). En el diagrama siguiente se muestran los intervalos devueltos por los métodos de PivotLayout.

Diagrama en el que se muestran las secciones de una tabla dinámica devueltas por las funciones get range del diseño.

Salida total de tabla dinámica

La ubicación de la fila total se basa en el diseño. Use PivotLayout.getBodyAndTotalRange y obtenga la última fila de la columna para usar los datos de la tabla dinámica en el script.

En el ejemplo siguiente se encuentra la primera tabla dinámica del libro y se registran los valores de las celdas "Grand Total" (como se resalta en verde en la imagen siguiente).

Tabla dinámica que muestra las ventas de frutas con la fila Grand Total resaltada en verde.

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

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

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

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

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

Filtros y segmentaciones de datos

Hay tres maneras de filtrar una tabla dinámica.

FilterPivotHierarchies

FilterPivotHierarchies agregue una jerarquía adicional para filtrar cada fila de datos. Cualquier fila con un elemento filtrado se excluye de la tabla dinámica y sus resúmenes. Dado que estos filtros se basan en elementos, solo funcionan en valores discretos. Si "Clasificación" es una jerarquía de filtros en el ejemplo, los usuarios pueden seleccionar los valores "Orgánico" y "Convencional" para el filtro. Del mismo modo, si se selecciona "Cajas vendidas al por mayor", las opciones de filtro serían los números individuales, como 120 y 150, en lugar de rangos numéricos.

FilterPivotHierarchies se crean con todos los valores seleccionados. Esto significa que no se filtra nada hasta que el usuario interactúa manualmente con el control de filtro o se establece en PivotManualFilter el campo que pertenece a FilterPivotHierarchy.

El siguiente fragmento de código agrega "Clasificación" como jerarquía de filtros.

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

Control de filtro que usa

PivotFilters

El PivotFilters objeto es una colección de filtros aplicados a un único campo. Dado que cada jerarquía tiene exactamente un campo, siempre debe usar el primer campo en PivotHierarchy.getFields() al aplicar filtros. Hay cuatro tipos de filtro.

Filtro de fecha: filtrado basado en fechas del calendario.
Filtro de etiqueta: filtrado de comparación de texto.
Filtro manual: filtrado de entrada personalizado.
Filtro de valor: filtrado de comparación de números. Esto compara los elementos de la jerarquía asociada con los valores de una jerarquía de datos especificada.

Normalmente, solo se crea uno de los cuatro tipos de filtros y se aplica al campo. Si el script intenta usar filtros incompatibles, se produce un error con el texto "El argumento no es válido o falta o tiene un formato incorrecto".

El siguiente fragmento de código agrega dos filtros. El primero es un filtro manual que selecciona los elementos de una jerarquía de filtros "Clasificación" existente. El segundo filtro quita las granjas que tienen menos de 300 "Cajas vendidas al por mayor". Tenga en cuenta que esto filtra la "suma" de esas granjas de servidores, no las filas individuales de los datos originales.

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

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

Una tabla dinámica después de aplicar el filtro de valor y el filtro manual.

Segmentación de datos

Las segmentaciones filtran los datos de una tabla dinámica (o una tabla estándar). Son objetos que se pueden mover en la hoja de cálculo que permiten filtrar rápidamente las selecciones. Una segmentación funciona de forma similar al filtro manual y PivotFilterHierarchy. Los elementos de PivotField se alternan para incluirlos o excluirlos de la tabla dinámica.

El siguiente fragmento de código agrega una segmentación para el campo "Type". Establece los elementos seleccionados para que sean "Lemon" y "Lime" y, a continuación, mueve la segmentación de 400 píxeles a la izquierda.

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

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

Configuración de campo de valor para resúmenes

Cambie la forma en que la tabla dinámica resume y muestra los datos con esta configuración. El campo de cada jerarquía de datos puede mostrar los datos de maneras diferentes, como porcentajes, desviaciones estándar y comparaciones relativas.

Resumir por

El resumen predeterminado de un campo de jerarquía de datos es una suma. DataPivotHierarchy.setSummarizeBy permite combinar los datos de cada fila o columna de una manera diferente. AggregationFunction enumera todas las opciones disponibles.

El siguiente fragmento de código cambia "Cajas vendidas al por mayor" para mostrar la desviación estándar de cada elemento, en lugar de la suma.

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

Mostrar valores como

DataPivotHierarchy.setShowAs aplica un cálculo a los valores de una jerarquía de datos. En lugar de la suma predeterminada, puede mostrar valores o porcentajes relativos a otras partes de la tabla dinámica. Use para ShowAsRule establecer cómo se muestran los valores de jerarquía de datos.

El siguiente fragmento de código cambia la presentación de "Cajas vendidas en granja". Los valores se mostrarán como un porcentaje del total general del campo.

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

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

Algunos ShowAsRulenecesitan otro campo o elemento en ese campo como comparación. El siguiente fragmento de código cambia de nuevo la presentación de "Cajas vendidas en granja". Esta vez, el campo mostrará la diferencia de cada valor con respecto al valor de "Lemons" de esa fila de granja de servidores. Si una granja no ha vendido ningún limón, el campo muestra "#N/A".

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

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

Compartir a través de