在 Office 脚本中使用数据透视表
使用数据透视表可以快速分析大型数据集合。 随着它们的力量而来的复杂性。 使用 Office 脚本 API 可以根据需要自定义数据透视表，但 API 集的范围使得入门变得很困难。 本文演示如何执行常见的数据透视表任务，并说明重要的类和方法。
注意
若要更好地了解 API 使用的术语的上下文，请先阅读 Excel 的数据透视表文档。 从Create数据透视表开始，以分析工作表数据。
对象模型
数据透视表是 Office 脚本 API 中数据透视表的中心对象。
- Workbook 对象具有所有数据透视表的集合。 每个 工作表 还包含该工作表本地的数据透视表集合。
- 数据透视表包含 PivotHierarchies。 可将层次结构视为表中的列。
- PivotHierarchies 可以添加为行或列 (RowColumnPivotHierarchy) 、 dataPivotHierarchy) 的数据 (，或 筛选器 (FilterPivotHierarchy) 。
- 每个 PivotHierarchy 只包含一个 PivotField。 Excel 外部的数据透视表结构可能每个层次结构包含多个字段，因此存在此设计以支持将来的选项。 对于 Office 脚本，字段和层次结构映射到相同的信息。
- PivotField 包含多个 PivotItems。 每个 PivotItem 都是字段中的唯一值。 将每个项视为表列中的值。 如果字段用于数据，则项也可以是聚合值，例如 sums。
- 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. */);
层次结构和字段
数据透视表通过层次结构进行组织。 当添加为特定类型的层次结构时，这些层次结构用于透视数据。 有四种类型的层次结构。
- 行：以水平行显示项。
- 列：在垂直列中显示项。
- 数据：显示基于行和列的值聚合。
- 筛选器：在数据透视表中添加或删除项。
数据透视表可以具有分配给这些特定层次结构的任意多或少的字段。 数据透视表需要至少一个数据层次结构来显示汇总的数字数据和至少一行或一列来透视该摘要。 以下代码片段添加了两个行层次结构和两个数据层次结构。
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 流中返回数据。 这些范围是通过从 PivotTable.getLayout()获取的 PivotLayout 对象访问的。 下图显示了 中 PivotLayout的方法返回的范围。
数据透视表总输出
总行的位置基于布局。 使用 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”和“Conventional”的值。 同样，如果选择“已售出的箱子批发”，则筛选器选项将是单个数字，例如 120 和 150，而不是数值范围。
FilterPivotHierarchies 在创建时选择了所有值。 这意味着，在用户手动与筛选器控件交互或在属于 FilterPivotHierarchy的字段上设置 之前PivotManualFilter，不会筛选任何内容。
以下代码片段将“分类”添加为筛选器层次结构。
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);
摘要的值字段设置
使用这些设置更改数据透视表汇总和显示数据的方式。 每个数据层次结构中的字段可以以不同的方式显示数据，例如百分比、标准偏差和相对比较。
Summarize by
数据层次结构字段的默认汇总为总和。 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需要该字段中的另一个字段或项作为比较。 以下代码片段再次更改了“在场出售的箱子”的显示。 这一次，字段将显示每个值与该场行中“Lemons”值的差异。 如果农场没有出售任何柠檬，则田地显示“#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");