[{"id":"b66f8b1e-cc14-4e89-9679-abd5687d283d","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"569bb90a-ea68-46c6-96f1-ab151c120714","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5eb52f08-2d1a-4362-9ffc-4871bdc10f3f","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"341dd607-b97d-4d70-bde2-53acda6b6c95","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"05349273-414f-4208-9ea2-c4fc8f4ea2cb","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"86089f76-b778-4d52-821e-6f27de3df613","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b81e4fd6-1fc5-43a0-a258-b6e16a5cbec6","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8aa8ce31-43e4-438e-951f-241608435260","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"37343f41-6ec2-4c7e-b21d-2cc18d5ce1e0","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5915e52f-64f8-4146-b8bd-81bead6324a3","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"836ba889-af9e-460d-a4cc-c24d922795f2","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4ae14b06-bb68-4394-a210-a46b8f028346","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d8f42066-e9dc-4411-bdcf-43b1a203370c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"7ef86f16-b1a0-49f7-9592-612b9be02b25","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b29c7775-a9a4-451e-a1b5-01d19ed5ca5e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"e56f3989-8f81-46af-90fa-a4813eeb976f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"2526c963-f170-45a8-923e-91b0712a9810","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"24575cf0-501a-44f9-8426-c40f8f4b5552","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1a6f8d7f-acd6-42be-8c4f-f464c6218381","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"82b176fd-5cab-498c-909e-8fa7d29c38d8","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1708b3a9-4f37-44a8-8f0e-f9a2d2e5d940","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"c9ac246e-29fb-4bc4-8231-8439795bb590","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"94f2a5c3-2539-436a-af75-23fbbd1a3957","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d99594f4-2d40-4df4-9419-ba2ca6aa3f7f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"fd66e72d-0f10-4f57-9807-6db26290ab2e","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8f344863-503d-4bc3-a594-3815e7d55f5c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"9be82601-de9d-4c18-948a-23ab6f4dd431","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"6495f3c0-b463-47e0-b08a-ca949672211e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b4bede08-3f08-4839-ba4a-abc7ac195bde","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4afcdfc0-3ff5-4f2b-a223-f8fc042a5bbe","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"422062c4-fa40-4771-a86f-008efe6d86e5","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d87e8013-86a7-4840-8d25-6f62e14eb4ac","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a6acedfd-4043-4c64-a5d1-aec3326df9e7","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"dbdc0b24-06c4-48b9-8d6c-7455119dc773","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a3856849-954a-4cfc-96a6-382e530d3638","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4a39306b-ffa5-433d-80a3-28e41f929b72","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]}]
        
(Showing Draft Content)

GETPIVOTDATA

此函数从数据透视表返回可见数据。它查询数据透视表并根据数据透视表结构获取特定数据,而不是单元格引用。使用此函数的主要优点是它确保返回正确的数据,即使数据透视表布局发生变化。

语法

=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)

参数

此函数具有以下参数:

参数 描述
data_field 包含要检索的数据的数据透视表字段的名称。这需要用双引号引起来。
pivot_table 对数据透视表中任何单元格、单元格区域或命名单元格区域的引用。此信息用于确定哪个数据透视表包含您要检索的数据。
field1, item1, field2, item2.. [可选] 最多 126 对描述要检索的数据的字段名称和项目名称。这些对可以按任何顺序排列。

用法

您可以通过在要返回值的单元格中使用 =(等号)来输入 GETPIVOTDATA 函数。您也可以将此功能与其他功能一起使用。

备注:

使用 GETPIVOTTABLE 函数时,注意以下几点:

  • #REF! 在以下情况下会出现错误值:- 如果给定的 pivot_table 引用与数据透视表无关。

  • 如果提供了 data_field、[field] 或 [item] 参数的无效字段。

  • 如果参数不描述可见字段,或者它们包含不显示筛选数据的报表筛选。

  • 当有 sigma 值时,data_field 将包含 calc 类型,否则将仅使用字段名称。

返回值

从数据透视表返回可见数据。

示例

下图显示了 GETPIVOTDATA 函数的用法。

以下示例代码显示了 GETPIVOTDATA 函数的用法。

$(document).ready(function () {
     // 初始化Spread
     var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 2 });
     spread.suspendPaint();
     // 获取表
     var pivotLayoutSheet = spread.getSheet(0);
     var dataSourceSheet = spread.getSheet(1);
     // 设置表名称
     pivotLayoutSheet.name("PivotLayout");
     dataSourceSheet.name("DataSource");
     // 设置行数
     dataSourceSheet.setRowCount(245);
     // 设置数据源
     dataSourceSheet.setArray(0, 0, pivotDB_UseCase);
     // table中添加数据源
     dataSourceSheet.tables.add('tableSales', 0, 0, 245, 8);
     spread.resumePaint();
     // 初始化数据透视表
     initPivotTable(pivotLayoutSheet);
     // 自动调整两个工作表中的列
     autoFit(pivotLayoutSheet);
     autoFit(dataSourceSheet);
 });

 function initPivotTable(sheet) {
     //添加数据透视表
     var myPivotTable = sheet.pivotTables.add("myPivotTable", "tableSales", 0, 0, GC.Spread.Pivot.PivotTableLayoutType.tabular, GC.Spread.Pivot.PivotTableThemes.dark3);
     myPivotTable.suspendLayout();
     //显示数据透视表的 rowHeader 和 columnHeader
     myPivotTable.options.showRowHeader = true;
     myPivotTable.options.showColumnHeader = true;
     // 添加列字段
     myPivotTable.add("Category", "Category", GC.Spread.Pivot.PivotTableFieldType.columnField);
     // 添加行字段
     myPivotTable.add("Region", "Region", GC.Spread.Pivot.PivotTableFieldType.rowField);
     myPivotTable.add("City", "City", GC.Spread.Pivot.PivotTableFieldType.rowField);
     // 添加 SubtotalType Sum
     myPivotTable.add("Quantity", "Sum of quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
     // 添加筛选字段
     myPivotTable.add("OrderDate", "OrderDate", GC.Spread.Pivot.PivotTableFieldType.filterField);
          
     var formula1 = '=GETPIVOTDATA("Quantity",$A$3)';
     sheet.setValue(3, 10, 'Total Quantity');
     sheet.setStyle(3, 10, 'introSec');
     sheet.setFormula(3, 11, formula1);

     var formula2 = '=GETPIVOTDATA("Quantity",$A$3,"Category","Bakery")';
     sheet.setValue(4, 10, 'Total Bakery Quantity');
     sheet.setStyle(4, 10, 'introSec');
     sheet.setFormula(4, 11, formula2);

     var formula3 = '=GETPIVOTDATA("Quantity",$A$3,"Region","West")';
     sheet.setValue(5, 10, 'West Region');
     sheet.setStyle(5, 10, 'introSec');
     sheet.setFormula(5, 11, formula3);

     var formula4 = '=GETPIVOTDATA("Quantity",$A$3,"Region","East","City","Jersey")';
     sheet.setValue(6, 10, 'Quantity in Jersey');
     sheet.setStyle(6, 10, 'introSec');
     sheet.setFormula(6, 11, formula4);
     myPivotTable.resumeLayout();
     return myPivotTable;
 }

 function autoFit(sheet) {
     // 自动调整列
     let columnCount = sheet.getColumnCount();
     for (let i = 0; i < columnCount; i++) {
         sheet.autoFitColumn(i);
            }
        }

GETPIVOTDATA 函数还支持溢出,这意味着它可以使用其结果填充多个单元格。 但是, allowDynamicArray 属性应设置为 true 以观察溢出结果。 下图显示了一个数据透视表,它将用于使用 GETPIVOTDATA 函数提取一些有意义的结果。

下图显示了在上述数据透视表中使用 GETPIVOTDATA 函数 结果溢出到多个单元格时的输出。

以下示例代码显示了使用 GETPIVOTDATA 函数获取溢出到多个单元格的结果;

function setGetPivotDataFunction(sheet) {
    spread.options.allowDynamicArray = true;
    var formula = '=GETPIVOTDATA("Sum of quantity",$B$2,"City",{"Jersey";"San Francisco";"Seattle";"Washington, DC"},"Category",C11:C14,"Qt",E3:F3)';
    sheet.setStyle(1, 9, 'introSec');
    sheet.addSpan(1, 9, 2, 8, GC.Spread.Sheets.SheetArea.viewport);
    sheet.setValue(1, 9, 'Returns the spill range of total Bakery, Beverages, Chocolates and Snacks quantity sold in Jersey, San Francisco, Seattle and Washington, DC respectively in the Qtr2 & Qtr3');
    sheet.getCell(1, 9).wordWrap(true);
    sheet.setStyle(4, 9, 'formula');
    sheet.addSpan(4, 9, 2, 8, GC.Spread.Sheets.SheetArea.viewport);
    sheet.getCell(4, 9).wordWrap(true);
    sheet.setValue(4, 9, formula);
    sheet.setFormula(7, 9, formula);
}