[{"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"}]}]
此函数从数据透视表返回可见数据。它查询数据透视表并根据数据透视表结构获取特定数据,而不是单元格引用。使用此函数的主要优点是它确保返回正确的数据,即使数据透视表布局发生变化。
=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);
}