Excel 数据透视表怎么做：分步教程

数据透视表按某个类别（比如 Region）对表格中的行分组，并为每组汇总一个数字（比如 Sales），不需要公式。创建方法：点击数据中的一个单元格，选择 插入 > 数据透视表，按“确定”，然后把 Region 拖到“行”，把 Sales 拖到“值”。下面的表格不是数据透视表：它用公式做出了同样的汇总，你可以看着合计随之变化。

公式重置

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

UNIQUE 把每个区域列出一次，SUMIF 计算其合计：North 455、South 305、East 170，分别占总数 930 的 49%、33% 和 18%。把 C3 改成 185，South 的合计和三个百分比会立刻跟着变化。数据透视表会显示同样的数字，但要等你刷新之后。

如何创建数据透视表

开始之前先检查源数据：一行标题且每列都有名称，每行一条记录，中间没有空行或空列，也没有小计行。

1. 点击数据中的任意单元格。
2. 选择 插入 > 数据透视表（某些版本是 插入 > 数据透视表 > 来自表格/区域）。
3. Excel 会自动填好区域。选择 新工作表，然后按 确定。
4. 出现一个空白的数据透视表，右侧是 数据透视表字段 窗格，列出了你的列标题。
5. 把 Region 拖进 行 框，把 Sales 拖进 值 框。数据透视表会把每个区域显示一次，旁边是“求和项:Sales”，最后还有一行“总计”。
6. 要改变显示内容，在各框之间拖动字段，或把字段拖出窗格。

如果不确定从哪里开始，插入 > 推荐的数据透视表 会根据你的数据给出几种现成的布局。Mac 上的菜单相同：插入 > 数据透视表。

行、列、值和筛选

“数据透视表字段”窗格有四个框，每个数据透视表都是在选择哪一列放进哪个框：

• 行：沿左侧向下排列的类别，每个不同的值占一行（Region）。
• 列：沿顶部横向排列的类别，每个不同的值占一列（Product）。
• 值：为每种组合计算的数字。数字列默认求和；计数、平均值、最大值、最小值等在“值字段设置”中。
• 筛选：用来筛选整个数据透视表的字段，显示为表格上方的下拉列表。

把 Region 放进“行”、Product 放进“列”、Sales 放进“值”，上面数据的数据透视表就是这样：

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

这种布局的公式版本用 UNIQUE 向下列出区域，用 TRANSPOSE(UNIQUE()) 横向列出产品，再用一个以这两个列表为条件的 SUMIFS 计算网格中的每个单元格：

公式重置

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

E2 向下溢出 North、South 和 East，F1 横向溢出 Apple 和 Pear，F2 中的 SUMIFS 填满两者之间 3 行 2 列的网格：每个区域与产品的组合一个合计。把 B5 从 Apple 改成 Pear，East 的两个单元格都会变化。这里的顺序是值第一次出现的顺序；数据透视表则按字母顺序排列标签。

改为计数、平均值或百分比

在数据透视表中，点击“值”框里的字段，选择 值字段设置。值汇总方式 选项卡可在求和、计数、平均值、最大值和最小值之间切换；值显示方式 选项卡可把数字变成 总计的百分比、列汇总的百分比、累计总计等。每一种都有对应的公式：

公式重置

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

North 有 3 笔订单，平均 151.7；South 3 笔，平均 101.7；East 2 笔，平均 85.0。“总计的百分比”一列在本页的第一个表格中。

按某个产品筛选汇总

“筛选”框会在数据透视表上方放一个下拉列表。公式版本是一个带下拉列表的单元格加上 SUMIFS，SUMIFS 比 SUMIF 多一个条件。在 F1 中选一个产品：

公式重置

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

选择 Apple 时，North 显示 215，South 显示 150，East 显示 60。选 Pear 则变为 240、155 和 110。更多条件请看 SUMIFS，在 Excel 中添加列表的方法请看下拉列表一文。

刷新数据透视表

数据透视表保存了一份源数据的副本（数据透视表缓存），源数据中的单元格改变时它不会重新计算。编辑数据之后：

• 右键点击数据透视表中的任意位置并选择 刷新，Windows 上也可以按 Alt+F5。
• 数据 > 全部刷新（Ctrl+Alt+F5）会刷新工作簿中的每个数据透视表。
• 要在每次打开文件时都刷新，右键点击数据透视表，选择 数据透视表选项，在 数据 选项卡中勾选 打开文件时刷新数据。

添加在源区域下方的新行不会被包含进来，即使刷新也一样。在 数据透视表分析 > 更改数据源 中修改区域，或者更好的做法是在创建数据透视表之前把源数据转换为表格：选中数据并按 Ctrl+T，或使用 插入 > 表格。表格会在你添加行时自动扩展，下一次刷新时数据透视表就会把新行包含进来。

用一个公式汇总所有区域

公式重置

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

轮到你了： 在 F2 中用一个公式计算 E2:E4 中列出的每个区域的销售额合计。

检查显示答案

答案会溢出 455、305 和 170。把整个列表 E2:E4 作为 SUMIF 的条件，就会为每个区域返回一个合计，因此无需向下填充。Excel 2019 及更早版本既没有 UNIQUE 也不支持溢出：在 E2:E4 中手动输入区域，再向下填充 =SUMIF($A$2:$A$9,E2,$C$2:$C$9)。不加 $ 符号时，区域会随每一行下移，合计就会出错。

GROUPBY 和 PIVOTBY：一个公式做出数据透视表

Microsoft 365 版 Excel 有两个函数，能用一个公式建出整个汇总，并像普通公式一样自动重新计算，无需刷新。它们需要有效的 Microsoft 365 订阅。对于上面的数据：

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

GROUPBY 接收行字段、值和函数（SUM、COUNTA、AVERAGE、MAX、PERCENTOF）。PIVOTBY 在两者之间多了一个列字段。两者都会对标签排序并添加合计行，和数据透视表一样。

数据透视表还是公式：该用哪个

探索数据、一次性回答某个问题时用数据透视表；放在报表中、供其他公式使用、必须始终保持最新的汇总用公式。要核对数据透视表的数字，用 SUMIFS 重新算出其中一个单元格：如果两者不一致，通常是数据透视表需要刷新，或者它的源区域太短了。