Excel 怎么用数据透视表做同比环比
逐月数据摊在表里,想算同比增长和环比增长,不用另写一列公式。数据透视表的「值显示方式」里就有现成选项,选对基本字段即可。
月度数据做汇报,同比和环比几乎每次都要算。很多人习惯在旁边加一列公式,手动引用上个月和去年同月的单元格,数据一更新就全错位。数据透视表里其实内置了差异百分比,只要时间字段放对位置就能自动算。
入口在哪
先把数据插成数据透视表,把时间字段拖到行区域,把数值字段拖到值区域。然后右键值区域里的数字,选「值字段设置」,在弹出的对话框里切到「值显示方式」选项卡,那里就是同比环比的入口。
具体步骤
- 确认源数据里有一列真正的日期,而不是文本格式的年月。文本日期透视后无法按时间分组。
- 插入数据透视表,把日期拖到行,把销售额之类的数值拖到值。
- 右键日期字段,选择按年、按月组合,让行标签呈现层级。
- 再拖一次同一个数值字段到值区域,得到第二列数字,用来做增长率。
- 右键第二列,进入值字段设置,把值显示方式改成「差异百分比」。
- 基本字段选日期,基本项选上一个,得到环比;要同比就把基本项换成上一年的同一个月。
几个坑
- 日期列是文本,透视表只会按字符串排序,组合功能灰掉,同比环比都无从下手。
- 把基本项选成「上一个」,在第一行会得到空值,这是正常的,不是公式错。
- 数据里有缺月,环比会跨月计算,结果看起来像异常波动。
- 值区域里放了求和与计数两列,右键时选错了对象,改的是计数列。
- 源数据刷新后新增了月份,如果日期没重新组合,新月份会被归到「其他」里。
怎么验证
随便挑一个月,用计算器手动算一次环比,和透视表结果对照,误差应在小数点后一位以内。再检查去年同期那一行,确认同比的分母取的是去年同月而不是去年任意月。最后刷新一次数据,看新增月份有没有正确进入分组。
常见问题
同比和环比能不能同时放在一张表里
可以,把同一个数值字段拖进值区域两次,分别设成不同的值显示方式,再重命名列标题区分。两张表也可以放在同一张透视表的不同列区。
为什么数字显示成一长串小数
差异百分比默认不套百分比格式。选中这一列,把单元格格式改成百分比并保留一位小数即可。
数据源每月都在变长怎么办
把源数据转成表格再作为透视表数据源,新增行会自动纳入范围,刷新即可,不用每次手动改区域。
一句话总结:日期必须是真日期,拖两次数值字段,一次求和一次设成差异百分比,基本项选上一期就是环比、选上一年同期就是同比。