本指南介绍如何使用 Google 表格 API 在电子表格中创建数据透视表,以及这样做的原因。
什么是数据透视表?
数据透视表提供了一种在电子表格中汇总数据的方式,可以在新表格中显示汇总结果的同时,自动对数据进行聚合、排序、计数或求平均值。数据透视表可以用作针对源数据集的某种查询。此源数据存在于电子表格中的其他位置,并且数据透视表会显示处理后的数据视图。
例如,请参考以下销售数据集:
A | B | C | D | E | F | G | |
1 | 商品类别 | 型号 | 费用 | 数量 | 区域 | 销售人员 | 发货日期 |
2 | 摩天轮 | W-24 | 20.50 美元 | 4 | 西 | 白丝 | 2016 年 3 月 1 日 |
3 | 门 | D-01X 星 | 15.00 美元 | 2 | 南 | 阿米尔 | 2016 年 3 月 15 日 |
4 | 引擎 | ENG-0134 | ¥100.00 | 1 | 北 | 卡门 | 2016 年 3 月 20 日 |
5 | Frame | FR-0B1 | 34.00 美元 | 8 | 东 | Hannah | 2016 年 3 月 12 日 |
6 | 面板 | P-034 | 6.00 美元 | 4 | 北 | 德温 | 2016 年 4 月 2 日 |
7 | 面板 | P-052 | 11.50 美元 | 7 | 东 | Erik | 2016 年 5 月 16 日 |
8 | 摩天轮 | W-24 | 20.50 美元 | 11 | 南 | Sheldon | 2016 年 4 月 30 日 |
9 | 引擎 | ENG-0161 | 330.00 美元 | 2 | 北 | 杰西 | 2016/7/2 |
10 | 门 | D-01Y 天 | 29.00 美元 | 6 | 西 | 阿曼多 | 2016 年 3 月 13 日 |
11 | Frame | FR-0B1 | 34.00 美元 | 9 | 南 | 尤利安纳 | 2016/02/27 |
12 | 面板 | P-102 | 3.00 美元 | 15 | 西 | 卡门 | 2016/4/18 |
13 | 面板 | P-105 | 8.25 美元 | 13 | 西 | 杰西 | 2016/6/20 |
14 | 引擎 | ENG-0211 | 283.00 美元 | 1 | 北 | 阿米尔 | 2016/6/21 |
15 | 门 | D-01X 星 | 15.00 美元 | 2 | 西 | 阿曼多 | 2016 年 7 月 3 日 |
16 | Frame | FR-0B1 | 34.00 美元 | 6 | 南 | 卡门 | 7/15/2016 |
17 | 摩天轮 | W-25 | 20.00 美元 | 8 | 南 | Hannah | 2016 年 5 月 2 日 |
18 | 摩天轮 | W-11 | 29.00 美元 | 13 | 东 | Erik | 2016 年 5 月 19 日 |
19 | 门 | D-05 | 17.70 美元 | 7 | 西 | 白丝 | 2016/6/28 |
20 | Frame | FR-0B1 | 34.00 美元 | 8 | 北 | Sheldon | 2016 年 3 月 30 日 |
您可以使用数据透视表创建报告,显示每个地区每个型号的销量:
如需了解用于生成此数据透视表的源代码,请参阅下面的示例部分。
将数据透视表放入电子表格后,用户可以使用 Google 表格界面以交互方式更改摘要的结构和详细信息。
使用数据透视表
数据透视表定义与工作表上的单个单元格关联。虽然其呈现的外观在高度和宽度上都占用了很多单元格,但其以编程方式位于单个单元格坐标上。此单元格会成为所呈现数据透视表的左上角,其水平和垂直范围由其定义确定。
添加数据透视表
如需添加数据透视表,请使用 batchUpdate 方法,并提供 updateCells 请求。您可以使用此请求提供 PivotTable定义作为单元格的内容,如下所示:
"updateCells": {
"rows": {
"values": [{
"pivotTable": MyPivotTable
},
"start": {
"sheetId": sheetId,
"rowIndex": 0,
"columnIndex": 0
},
"fields": "pivotTable"
}
此操作会将 MyPivotTable
所描述的数据透视表放置在指定的工作表中,且左上角位于单元格 A1
上。(数据透视表的高度和宽度是动态变化的;您只需指定原点)。
通过 PivotTable类型,您可以指定:
- 源数据范围
- 一个或多个字段,其数据将构成数据透视表的行
- 一个或多个字段,其数据将构成数据透视表的列
- 过滤和汇总条件
- 数据透视表布局
修改和删除数据透视表
没有用于修改或删除数据透视表的明确请求。请改为使用包含不同单元格内容的 updateCells 请求:
- 如要修改数据透视表,请创建一个经过修改的 PivotTable定义,并使用它更新单元格,与添加新数据透视表类似。
- 如要删除数据透视表,请将单元格更新为空值。如需查看示例,请参阅删除数据透视表示例。
用例
数据透视表有多种不同用途,涉及统计分析、ERP 应用、财务报告等众多领域。经典数据透视表用例包括以下内容:
- 总销售额(按区域和季度)
- 按职位和地点细分的平均工资
- 突发事件数量(按产品和一天中的时间细分)
数据透视表的潜在应用数量庞大,以编程方式生成数据透视表的功能也十分强大。您可以生成支持交互式探索但针对特定情形量身定制的数据透视表,例如:
- 探索最近 24 小时内的突发事件数据
- 查看/分析与当前所选账号对应的汇总数据
- 查看当前用户所属地区的销售数据
示例
此示例根据数据集创建了一个数据透视表,以生成此页面简介部分中所示的“按区域显示的型号”报告。如需查看其他示例,请参阅数据透视表示例页面。