最新

Excel 怎么在数据透视表里加计算字段

透视表里加计算字段算利润率这类派生指标,说清公式写法、总计行算法差异和刷新后失效怎么修。

数据透视表能求和能计数,但利润率、人均产出这类指标在源表里根本没有这一列。多数人的做法是在透视表旁边另起一列手算,结果一刷新、一加筛选条件,旁边那列就整个错位。计算字段就是为这个场景准备的,它把公式写进透视表定义里,跟着汇总方式一起算。

入口在哪

  • 点透视表里任意一个单元格,功能区会多出「数据透视表分析」,旧版本叫「分析」。
  • 在「字段、项目和集」的下拉里选「计算字段」,弹出的对话框就是入口。
  • 菜单名各版本不完全一样,在功能区右上角搜索框输入「计算字段」最省事。

具体步骤

  1. 先把源数据的列名整理干净。计算字段的公式要直接引用列名,带空格或特殊符号的列名容易出问题。
  2. 点透视表内任一单元格,打开「字段、项目和集」下的「计算字段」。
  3. 名称填指标名,例如毛利率,不要和已有的源表字段重名。
  4. 公式框先输等号,再从下方字段列表双击插入字段名,不要手打单元格地址。
  5. 需要除法时把分母用 IFERROR 包住,或者在式子里先判断分母,避免除零报错。
  6. 点「添加」再「确定」,新字段自动进入字段列表,勾上它就出现在透视表里,再右键数值区域把格式改成百分比。

几个坑

  • 公式里不能写 SUM,也不能引用单元格地址,只能用字段名。写成带 SUM 的式子会直接提示公式无效,这一点很多教程没讲。
  • 总计行的算法和你手算不一样。计算字段在总计行是用汇总后的分子除汇总后的分母,属于加权结果;旁边逐行算再求平均是简单平均。明细行数不均时两者差得很多,对账时要说明用的是哪一种。
  • 计算项和计算字段不是一回事。计算项作用在某个字段的成员之间,比如华东减华南;计算字段作用在整列上。用错地方会得到完全不合理的数字。
  • 源表改了列名,计算字段会失效,刷新时提示找不到字段,改列名之前先备份,或者改完重新建一次。另外计算字段不会跟着复制粘贴到别处,复制出去的是数值,公式仍然活在透视表的定义里。

怎么验证

挑一行明细手算一遍,比如某月利润除以销售额,看是否等于透视表里对应的那一格;再把同一份明细用 SUMIFS 汇总后算一次,和总计行对照。两个结果一致,才说明公式引用的字段是对的。

常见问题

为什么字段列表里找不到我要用的列

常见原因是这一列没被纳入透视表的数据源区域,或者列名重复、列名为空。回到「更改数据源」确认区域覆盖了整张表,把重复列名改掉,刷新后字段才会出现。

计算字段里怎么做除法不出错

分母为 0 时普通除法会显示错误值。用 IFERROR 包住整个式子,出错时返回 0 或空;也可以在公式里先判断分母是否大于 0,再决定是否相除。

能不能在计算字段里引用另一个计算字段

可以,前提是被引用的那个字段已经添加到透视表里。后建的计算字段能引用先建的,反过来不行;删除被引用的字段会让引用它的公式一起报错,删之前先把公式记下来。

一句话总结:派生指标用计算字段写进透视表,别在旁边手算,注意公式只认字段名且总计行按汇总值算。