使用数据透视表可以快速分析大型数据集合。 随着它们的力量而来的复杂性。 使用 Office 脚本 API 可以根据需要自定义数据透视表，但 API 集的范围使得入门变得很困难。 本文演示如何执行常见的数据透视表任务，并说明重要的类和方法。

若要更好地了解 API 使用的术语的上下文，请先阅读 Excel 的数据透视表文档。 首先 创建数据透视表以分析工作表数据 。

数据透视表 是 Office 脚本 API 中数据透视表的中心对象。

若要了解这些关系在实践中的工作原理，请从下载示例工作簿开始。 该数据描述了不同农场的水果销售情况。 它是本文中所有示例的基础。 在整个文章中运行示例脚本，以创建和浏览数据透视表。

下载示例工作簿

创建包含字段的数据透视表

使用对现有数据的引用创建数据透视表。 区域和表都可以是数据透视表的源。 它们还需要在工作簿中存在一个位置。 由于数据透视表的大小是动态的，因此仅指定目标范围的左上角。

以下代码片段基于一系列数据创建数据透视表。 数据透视表没有层次结构，因此数据尚未以任何方式分组。

  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
PivotFilters
Slicers
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);
Excel 中 Office 脚本的基础知识
Office 脚本 API 参考
			即将推出：在整个 2024 年，我们将逐步取消以“GitHub 问题”作为内容的反馈机制，并将其替换为新的反馈系统。 有关详细信息，请参阅：https://aka.ms/ContentUserFeedback。		

        
提交和查看相关反馈