举个工作中的数据分析例子,左边是销售流水数据,现在要求我们分析部分重点产品的销售数量,金额,均价,最低价,最高价,如下所示:
方法1、直接求解
如果要直接求解,我们需要使用SUMIFS函数公式来计算金额和数量,用MINIFS和MAXIFS来计算销售单价数据。
例如,当我们需要计算金额时,使用的公式是:
=SUMIFS(E:E,B:B,G2)
计算其它指标的公式这里就不列了
这样直接求解对各种函数公式的要求较高。
方法2:数据透视表+Xlookup
我们可以选中销售明细中的任意一个单元格,然后插入数据透视表
然后在数据透视表的选项中
把商品放在行标签
把数量,金额放入值
把单价,连续拖动3次放到值中
然后点击,销售单价的值字段设置
分别对3个销售单价的值汇总方式进行修改,如下所示
所有商品的这些指标数据已经计算出来了,这个时候,如果我们只需要其中几个商品的这些数据,只需要输入公式:
=XLOOKUP($G2,Sheet1!$A:$A,Sheet1!B:B)
向右填充,向下填充,得到了所有的结果
数据透视表里面计算的均价是不能用的,我们手动的更改一下均价的公式,使用总金额/总数量
=I2/H2
不仅仅是计算这些指标,有时候其它指标直接用函数公式很难求出来的时候,可以先用数据透视表求解,然后再XLOOKUP匹配过来即可。
你学会了么?动手试试吧!