这次分享一份非常有用，但有点烧脑的Excel模型 —— 平均价格分析模型。
这个模型非常适合企业收入对价格敏感的行业。当然你也可以把它拓展到其它指标和场景。为节约大家时间，先展示下这份自动化的模板。如果在工作中用得上，再详细了解后面的原理。在文末你可以找到模板的下载方式。
先把背景介绍一下，如果手里有这么一份数据，有两年的产品销售收入明细数据：
- 维度包括：年月、产品、渠道、身份
- 度量包括：销量、毛销售额、税、折扣返点、还有净销售额，其中净销售额=毛销售额+税+折扣返点
现在需要对两年的净销售额差额进行分析，需要量化分析出：
销量
毛销售额
税费
折扣返点
以及各个产品、渠道、省份，对净销售额的影响是多少？
如果你说，这个简单，对每个因素进行单独的对比分析，拉几个数据透视表就可以搞定。简单的分析某个因素当然可以。但是，如果我们想要的是这样的一个结果。
各个因素的影响具有可比性，而且它们相加刚好等于两年间的差异。这就是这篇文章要解决的问题。
在《双兔傍地走，安能辨我是雄雌？用Excel实现多维度的因素分析》这篇文章中，我们有展示过这么一个动画。
销售额的差异等于量差+价差
销量的影响就是量差，公式是：量差=（今年销量-去年销量）*去年价格
价差，也就是平均单价的变化。一般的，对平均单价产生影响的因素有两个。
第一个是产品单价的变化。在物理意义上可以理解为，各个产品单价发生变化，总体的平均单价必然变化；
另一个是产品结构的变化。在物理意义上可以理解为，各个产品的单价不变，产品结构发生变化导致平均单价变化。
价格差从物理意义上，我们可以理解为如果以本期的价格卖基期的产品，平均价格变化多少，可以使用这样的公式来计算。
结构差比较难理解。在物理意义上可以理解为，多卖高于平均价格的产品，有利于提高平均价格，反之亦然。结构差与价格差有很大的不同。因为结构差可以有很多层级，具体多少有它需要分析的维度决定。例如这个例子需要分析区域和产品的结构差，那么就有两级结构差。分析的先后顺序对结果将产生影响，因此将影响大的维度放前面。例如这个例子应该把产品放在区域前面才更合适。
这些公式够复杂了。可在这个案例中有三个价格（毛单价、税单价、折扣返点单价），以及三层结构（产品、渠道、区域）。因此一共需要计算，三个价格差，以及每层每种价格的结构差（3*3=9）。也就是12个结果。
如果是手工计算这些，这些计算确实复杂。
但借助Excel，合理搭建模型，这件事情其实非常简单。借助Power Query + 数据透视表 + 切片器，很轻松就能实现一份前面展示的自动化模板。这份模板既有总体的概览，方便展示给管理层，又有详细级别的数据明细，方便业务做分析改进。
你可以关注微信公众号【Tanxindata】，回复【精彩模板】，获得这个模板。
如何精进Excel水平？从邮件小工具讲起zhuanlan.zhihu.com
扫一扫
2827
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
