Excel 数据透视表怎么做：从入门到常见问题

数据透视表是 Excel 对「帮我汇总一下」这句话的回答。你把它指向一张明细表——订单、工单、流水——然后把字段拖进四个区域，它就能按你想要的任意维度算出合计、计数和平均值，全程不用写一个公式。很多人对数据透视表敬而远之，是因为第一次做出来的结果莫名其妙。本教程先带你做一个「不出错」的透视表，再讲四个真正让它好用的功能，最后把最常见的报错一次讲清楚。

开始之前：源数据要「干净」

数据透视表需要一张一维明细表：只有一行表头、每行一条记录、没有空行、数据中间没有「合计」行、没有合并单元格。本文用一张销售表做示例：

很多人的表格是「二维报表」的样子：月份横着排成 12 列，区域竖着排。这种表适合看，不适合做透视——需要先把它转换成上面这种一行一条记录的格式（Excel 里可以用 数据 → 来自表格/区域 打开 Power Query，再用「逆透视列」完成）。

在插入透视表之前，先做一件事：点中数据里任意一个单元格，按 Ctrl+T（Mac 上是 Cmd+T），把它转成「表格」（也叫超级表）。这是本文最重要的一个习惯——基于「表格」建立的透视表，在你新增行之后会自动把新数据纳入范围；基于普通区域建立的透视表则不会。「透视表不显示新数据」是最常见的投诉，没有之一。

第一步：插入数据透视表

点中表格内任意单元格 → 插入 → 数据透视表 → 选择「新工作表」→ 确定。左边会出现一个空白的透视表，右边出现 数据透视表字段 窗格：上半部分是你的列名，下半部分是四个区域：筛选、列、行、值。

WPS 表格： 同样是 插入 → 数据透视表（也可以在 数据 → 数据透视表 找到），右侧窗格叫「数据透视表」，四个区域名称为「筛选器、列、行、值」，拖拽方式与 Excel 相同。

第二步：把字段拖到「行」和「值」

把 区域 拖到「行」，把 销售额 拖到「值」。你就得到了按区域汇总的销售额：

这就是一个数据透视表。之后的一切都只是在它的基础上做优化。

如果「值」区域显示的是 计数项:销售额 而不是 求和项，说明销售额这一列里至少有一个单元格是文本（比如从系统导出的「文本型数字」）。临时办法是点击该字段 → 值字段设置 → 选择「求和」；根本办法是把这一列转成真正的数字，见 清洗 Excel 脏数据。

第三步：加入第二个维度

把 产品 拖到「列」。透视表变成一张交叉表：区域在左、产品在上、销售额在格子里，两个方向都有总计。原本要写十几个 SUMIFS 才能算出来的数字，一次拖拽就有了（什么时候用公式更合适，可以看 数据分析常用的 Excel 公式）。

如果把产品从「列」拖到「行」、放在区域下面，就会得到一个分级的大纲结构。两种排布是同一份数据，哪个更好读就用哪个。

第四步：按月分组日期

把 日期 拖到「行」。新版 Excel 会自动把日期分组为「年 / 季度 / 月」。如果显示的仍是一个个具体日期，就在透视表里右键任意日期 → 组合 → 选择「月」（数据跨年的话同时选「年」）。

把区域从「行」里移走，你就得到了每月销售额——三次拖拽就做出一张趋势表。这种分组用公式写起来很别扭，而透视表天生就会。

如果「组合」提示「选定区域不能分组」，几乎一定是日期列里混入了文本型日期或空白单元格。用 数据 → 分列 → 完成 或 DATEVALUE 把它们转成真正的日期，再刷新透视表。

第五步：格式和命名

• 右键某个值 → 数字格式 → 选择「货币」或「数值」并勾选千位分隔符。在值字段上设置格式，会同时作用于所有单元格，刷新后新出现的单元格也一样。
• 点击「求和项:销售额」这个标题，直接改成「销售额」（注意不能和源数据列名完全相同，可以写成「销售额 」或「销售额合计」）。
• 设计 → 报表布局 → 以表格形式显示，再选择 重复所有项目标签，就能得到一张没有空白单元格的平表，方便复制到别处使用。

进阶：四个值得学的功能

占总计的百分比

把「销售额」再拖一次到「值」。右键第二个 → 值显示方式 → 总计的百分比（如果要看每个区域内部的占比，选「列汇总的百分比」或「父级汇总的百分比」）。这样每个区域同时显示金额和占比。

非重复计数

「每个区域卖了多少种不同的产品？」普通的计数是在数行数。要做非重复计数，插入透视表时勾选 将此数据添加到数据模型，之后 值字段设置 里就会出现 非重复计数。

切片器

数据透视表分析 → 插入切片器 → 产品。 你会得到一组可点击的按钮来筛选透视表，比「筛选」区域友好得多。一个切片器还能同时控制多个透视表：右键切片器 → 报表连接。

排序和前 N 名

右键某个区域 → 排序 → 降序，按销售额从高到低排列。要看「销售额前 3 的产品」，点击产品字段的下拉箭头 → 值筛选 → 前 10 项，把 10 改成 3。

四个省时间的小技巧

• 双击查看明细。 双击透视表里任意一个数值，Excel 会新建一个工作表，列出组成这个数的所有原始行。核对「华东为什么是 ¥87,000」时，这是最快的办法。
• 计算字段。 想要「客单价 = 销售额 / 数量」，不需要回源数据加列：数据透视表分析 → 字段、项目和集 → 计算字段，输入 =销售额/数量 即可。注意计算字段是在汇总之后再相除，得到的是加权平均，而不是每行单价的简单平均。
• 数据透视图。 选中透视表 → 数据透视表分析 → 数据透视图，图表会跟着透视表的筛选和切片器一起变化，适合做周报。
• 推荐的数据透视表。 不知道从哪里开始时，插入 → 推荐的数据透视表 会根据你的数据给出几种现成的布局，选一个再调整。

新手最容易犯的错误

• 在透视表里直接改数字。 透视表的数值是只读的，想改结果只能改源数据再刷新。
• 源数据有空白列标题。 任何一列没有标题，插入透视表时就会报「数据透视表字段名无效」。给每一列都写上标题。
• 把多个月份的表分成多个工作表。 透视表一次只能读一个区域。先把各月数据合并到一张表里、加一列「月份」，再做透视。
• 忘记透视表不会自动更新。 改了源数据却没刷新，拿着旧数字去汇报，是最常见的尴尬。

WPS 表格和 Excel 的差异

大多数操作在 WPS 表格里是相通的，但有几处容易卡住：

• 菜单位置： WPS 的「分析」「设计」选项卡在选中透视表后出现，名称和 Excel 略有不同，切片器在 分析 → 插入切片器。
• 非重复计数： WPS 表格的数据透视表通常没有「添加到数据模型」这个选项，非重复计数不能直接在值字段里选。常见替代做法是先 数据 → 重复项 → 删除重复项 生成一个去重后的副本，再计数；或者在较新版本里用 =COUNTA(UNIQUE(...)) 公式。
• 刷新： 同样是右键 → 刷新，或 分析 → 全部刷新。WPS 里同样建议先把源数据转成表格（插入 → 表格），否则新增的行不会被纳入。
• 文件格式： 带透视表的文件建议保存为 .xlsx，便于在 Excel 和 WPS 之间来回打开。

用透视表回答真实问题

学会了拖字段，下面这几个日常问题都可以直接套用：

把这张表存下来，遇到新问题时先想清楚「按什么分组、算什么指标、筛选什么条件」，对应地放进行、值和筛选，就不会乱。

最常见的三个问题

1. 新增的行没有出现。 透视表读取的是一个固定范围。解决：把源数据转成表格（Ctrl+T），每次修改后右键透视表 → 刷新。如果源数据已经是表格、新行仍然不出现，到 数据透视表分析 → 更改数据源，确认它指向的是表格名称（如 表1），而不是 $A$1:$E$100 这样的固定区域。

2. 出现一行「(空白)」。 源数据中有些行的区域是空的。要么补全，要么把「(空白)」筛选掉——但你要知道这些行存在，因为它们没有被算进任何一个区域的合计里。

3. 合计比实际翻了一倍。 源数据里夹着「小计」或「合计」行，透视表把它们也加了进去。把这些行从源数据里删掉——透视表会自己算合计。

什么时候不该用数据透视表

数据透视表适合探索和做报表。以下情况它不是最佳工具：某个数字需要固定放在看板的某个单元格里（用 SUMIFS）；逻辑需要逐个单元格审计；或者这是一个你以后再也不会问的一次性问题。

对于一次性的问题，可以把表格上传到 ChatExcel，直接问 「按区域和产品汇总销售额」，几秒钟就能得到交叉表；像 「1 月到 3 月哪个区域增长最多？」 这种透视表布局没法直接回答的问题也一样。结果可以显示成可排序的表格或图表并下载。如果你想在工作簿里长期保留这个透视表，就问 「这个用数据透视表怎么做？」，它会按你的真实列名告诉你每个字段该放在哪里。ChatExcel 为付费订阅（Plus 每月 $29 起），详见 价格页。