Excel 怎么在数据透视表里加计算字段
透视表里加计算字段算利润率这类派生指标,说清公式写法、总计行算法差异和刷新后失效怎么修。
数据透视表能求和能计数,但利润率、人均产出这类指标在源表里根本没有这一列。多数人的做法是在透视表旁边另起一列手算,结果一刷新、一加筛选条件,旁边那列就整个错位。计算字段就是为这个场景准备的,它把公式写进透视表定义里,跟着汇总方式一起算。
入口在哪
- 点透视表里任意一个单元格,功能区会多出「数据透视表分析」,旧版本叫「分析」。
- 在「字段、项目和集」的下拉里选「计算字段」,弹出的对话框就是入口。
- 菜单名各版本不完全一样,在功能区右上角搜索框输入「计算字段」最省事。
具体步骤
- 先把源数据的列名整理干净。计算字段的公式要直接引用列名,带空格或特殊符号的列名容易出问题。
- 点透视表内任一单元格,打开「字段、项目和集」下的「计算字段」。
- 名称填指标名,例如毛利率,不要和已有的源表字段重名。
- 公式框先输等号,再从下方字段列表双击插入字段名,不要手打单元格地址。
- 需要除法时把分母用 IFERROR 包住,或者在式子里先判断分母,避免除零报错。
- 点「添加」再「确定」,新字段自动进入字段列表,勾上它就出现在透视表里,再右键数值区域把格式改成百分比。
几个坑
- 公式里不能写 SUM,也不能引用单元格地址,只能用字段名。写成带 SUM 的式子会直接提示公式无效,这一点很多教程没讲。
- 总计行的算法和你手算不一样。计算字段在总计行是用汇总后的分子除汇总后的分母,属于加权结果;旁边逐行算再求平均是简单平均。明细行数不均时两者差得很多,对账时要说明用的是哪一种。
- 计算项和计算字段不是一回事。计算项作用在某个字段的成员之间,比如华东减华南;计算字段作用在整列上。用错地方会得到完全不合理的数字。
- 源表改了列名,计算字段会失效,刷新时提示找不到字段,改列名之前先备份,或者改完重新建一次。另外计算字段不会跟着复制粘贴到别处,复制出去的是数值,公式仍然活在透视表的定义里。
怎么验证
挑一行明细手算一遍,比如某月利润除以销售额,看是否等于透视表里对应的那一格;再把同一份明细用 SUMIFS 汇总后算一次,和总计行对照。两个结果一致,才说明公式引用的字段是对的。
常见问题
为什么字段列表里找不到我要用的列
常见原因是这一列没被纳入透视表的数据源区域,或者列名重复、列名为空。回到「更改数据源」确认区域覆盖了整张表,把重复列名改掉,刷新后字段才会出现。
计算字段里怎么做除法不出错
分母为 0 时普通除法会显示错误值。用 IFERROR 包住整个式子,出错时返回 0 或空;也可以在公式里先判断分母是否大于 0,再决定是否相除。
能不能在计算字段里引用另一个计算字段
可以,前提是被引用的那个字段已经添加到透视表里。后建的计算字段能引用先建的,反过来不行;删除被引用的字段会让引用它的公式一起报错,删之前先把公式记下来。
一句话总结:派生指标用计算字段写进透视表,别在旁边手算,注意公式只认字段名且总计行按汇总值算。