Работа со сводными таблицами в сценариях Office

Статья
05/03/2024

Сводные таблицы позволяют быстро анализировать большие коллекции данных. С их силой приходит сложность. API скриптов Office позволяют настроить сводную таблицу в соответствии с вашими потребностями, но область набора API делает начало работы сложной задачей. В этой статье показано, как выполнять распространенные задачи сводной таблицы, а также описаны важные классы и методы.

Примечание.

Чтобы лучше понять контекст терминов, используемых API, сначала ознакомьтесь с документацией по сводной таблице Excel. Начните с Create сводной таблицы для анализа данных листа.

Объектная модель

Упрощенное представление классов, методов и свойств, используемых при работе со сводными таблицами.

Сводная таблица является центральным объектом для сводных таблиц в API сценариев Office.

Объект Workbook содержит коллекцию всех сводных таблиц. Каждый лист также содержит коллекцию сводной таблицы, которая является локальной для этого листа.
Сводная таблица содержит сводные иерархии. Иерархию можно рассматривать как столбец в таблице.
PivotHierarchy можно добавлять в виде строк или столбцов (RowColumnPivotHierarchy), данных (DataPivotHierarchy) или фильтров (FilterPivotHierarchy).
Каждая сводная иерархия содержит ровно одно сводное поле. Структуры сводных таблиц за пределами Excel могут содержать несколько полей для каждой иерархии, поэтому эта структура существует для поддержки будущих параметров. Для сценариев Office поля и иерархии сопоставляется с одной и той же информацией.
Сводное поле содержит несколько PivotItem. Каждый элемент PivotItem является уникальным значением в поле . Каждый элемент можно рассматривать как значение в столбце таблицы. Элементы также могут быть агрегированными значениями, такими как суммы, если поле используется для данных.
PivotLayout определяет способ отображения PivotFields и PivotItems.
PivotFilters фильтруют данные из сводной таблицы с использованием разных условий.

Чтобы узнать, как работают эти связи на практике, начните с скачивания примера книги. Эти данные описывают продажи фруктов из различных ферм. Это основа для всех примеров, приведенных в этой статье. Запустите примеры скриптов в этой статье, чтобы создать и изучить сводные таблицы.

Скачивание примера книги

Коллекция продаж фруктов разных типов из разных ферм.

Create сводной таблицы с полями

Сводные таблицы создаются со ссылками на существующие данные. Как диапазоны, так и таблицы могут быть источником сводной таблицы. Им также требуется место для существования в книге. Так как размер сводной таблицы является динамическим, указывается только левый верхний угол целевого диапазона.

Следующий фрагмент кода создает сводную таблицу на основе диапазона данных. В сводной таблице нет иерархий, поэтому данные пока не группируются каким-либо образом.

  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. */);

Сводная таблица с именем Farm Pivot без иерархий.

Иерархии и поля

Сводные таблицы упорядочены с помощью иерархий. Эти иерархии используются для сводки данных при добавлении в качестве определенного типа иерархии. Существует четыре типа иерархий.

Строка: отображает элементы в горизонтальных строках.
Столбец: отображает элементы в вертикальных столбцах.
Данные: отображаются статистические выражения значений на основе строк и столбцов.
Фильтр: добавление или удаление элементов из сводной таблицы.

Сводная таблица может содержать столько или несколько полей, назначенных этим конкретным иерархиям. Сводной таблице требуется по крайней мере одна иерархия данных для отображения сводных числовых данных и по крайней мере одна строка или столбец для сводки этой сводки. Следующий фрагмент кода добавляет две иерархии строк и две иерархии данных.

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

Сводная таблица, показывающая общий объем продаж различных фруктов в зависимости от фермы, из которой они пришли.

Диапазоны макета

Каждая часть сводной таблицы сопоставляется с диапазоном. Это позволяет скрипту получать данные из сводной таблицы для последующего использования в скрипте или для возврата в потоке Power Automate. Доступ к этим диапазонам осуществляется через объект PivotLayout , полученный из PivotTable.getLayout(). На следующей схеме показаны диапазоны, возвращаемые методами в PivotLayout.

Схема, показывающая, какие разделы сводной таблицы возвращаются функциями диапазона get макета.

Общие выходные данные сводной таблицы

Расположение строки итогов зависит от макета. Используйте PivotLayout.getBodyAndTotalRange и получите последнюю строку столбца, чтобы использовать данные из сводной таблицы в скрипте.

Следующий пример находит первую сводную таблицу в книге и записывает значения в ячейки "Общий итог" (как выделено зеленым цветом на рисунке ниже).

Сводная таблица, показывающая продажи фруктов с выделенной зеленым цветом строкой

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

Фильтры и срезы

Существует три способа фильтрации сводной таблицы.

FilterPivotHierarchies

FilterPivotHierarchies добавьте дополнительную иерархию для фильтрации каждой строки данных. Любая строка с отфильтрованным элементом исключается из сводной таблицы и ее сводок. Так как эти фильтры основаны на элементах, они работают только с дискретными значениями. Если "Классификация" является иерархией фильтров в примере, пользователи могут выбрать значения "Organic" и "Обычный" для фильтра. Аналогичным образом, если выбран параметр "Ящики продается оптовый", вариантами фильтра будут отдельные числа, например 120 и 150, а не числовые диапазоны.

FilterPivotHierarchies создаются со всеми выбранными значениями. Это означает, что ничего не фильтруется до тех пор, пока пользователь не будет вручную взаимодействовать с элементом управления фильтром PivotManualFilter или не будет задан в поле, принадлежащем FilterPivotHierarchy.

Следующий фрагмент кода добавляет "Classification" в качестве иерархии фильтров.

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

Элемент управления фильтром, использующий классификацию для сводной таблицы.

PivotFilters

Объект PivotFilters представляет собой коллекцию фильтров, применяемых к одному полю. Так как каждая иерархия содержит только одно поле, при применении фильтров всегда следует использовать первое поле в PivotHierarchy.getFields() . Существует четыре типа фильтров.

Фильтр по дате: фильтрация на основе дат календаря.
Фильтр меток: фильтрация сравнения текста.
Фильтр вручную: настраиваемая фильтрация входных данных.
Фильтр значений: фильтрация сравнения чисел. При этом элементы в связанной иерархии сравниваются со значениями в указанной иерархии данных.

Как правило, создается и применяется к полю только один из четырех типов фильтров. Если скрипт пытается использовать несовместимые фильтры, возникает ошибка с текстом "Аргумент недопустим или отсутствует или имеет неправильный формат".

Следующий фрагмент кода добавляет два фильтра. Первый — это ручной фильтр, который выбирает элементы в существующей иерархии фильтров классификации. Второй фильтр удаляет все фермы, которые имеют менее 300 "Ящики продаются оптово". Обратите внимание, что при этом отфильтровывается сумма этих ферм, а не отдельные строки из исходных данных.

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

Сводная таблица после применения фильтра значений и фильтра вручную.

Срезы

Срезы фильтруют данные в сводной таблице (или стандартной таблице). Они являются перемещаемыми объектами на листе, которые позволяют быстро фильтровать выбранные элементы. Срез работает аналогично ручному фильтру и PivotFilterHierarchy. Элементы из PivotField переключаются, чтобы включить или исключить их из сводной таблицы.

Следующий фрагмент кода добавляет срез для поля "Тип". Он задает выбранные элементы "Lemon" и "Lime", а затем перемещает срез на 400 пикселей влево.

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

Срез, фильтрующий данные в сводной таблице.

Параметры полей значений для сводок

Измените способ суммирования и отображения данных в сводной таблице с помощью этих параметров. Поле в каждой иерархии данных может отображать данные по-разному, например проценты, стандартные отклонения и относительные сравнения.

Суммировать по

Суммирование по умолчанию поля иерархии данных — сумма. DataPivotHierarchy.setSummarizeBy позволяет объединять данные для каждой строки или столбца по-разному. AggregationFunction выводит список всех доступных параметров.

В следующем фрагменте кода вместо суммы изменяется значение "Ящики, проданные оптовыми" для отображения стандартного отклонения каждого элемента.

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

Отображение значений как

DataPivotHierarchy.setShowAs применяет вычисление к значениям иерархии данных. Вместо суммы по умолчанию можно отобразить значения или проценты относительно других частей сводной таблицы. Используйте , чтобы задать способ отображения значений ShowAsRule иерархии данных.

Следующий фрагмент кода изменяет отображение параметра "Ящики проданы на ферме". Значения будут отображаться в процентах от общего итога поля.

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

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

Для ShowAsRuleсравнения некоторых из полей требуется другое поле или элемент в этом поле. Следующий фрагмент кода снова изменяет отображение для параметра "Ящики проданы на ферме". На этот раз в поле будет отображаться разница каждого значения от значения "Лимоны" в этой строке фермы. Если ферма не продала лимоны, на поле отображается "#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");

Поделиться через