超级透视表第二波——KPI分析
作者:小窝来源:部落窝教育发布时间:2023-09-07 14:11:42点击:742
今天我们用超级透视表做KPI分析表,继续感受超级透视表的强大。教程将用到两个新知识点“创建关系”“建立KPI”。
如何在Excel中根据销售明细、销售指标表得到截止到8月的人员销售KPI(意即关键绩效指标key performance indicator)分析表呢?
先说日常方法。
方法1:函数+条件格式
依次用函数公式得到前4项;最后设置条件格式用图标集展示。
方法2:数据透视表+函数+条件格式
两法用到的条件格式设置如下:
日常方法的不足:
(1)比较繁琐;
(2)函数法在数据增加后,更新不便。
下方用Power Pivot超级透视表做KPI分析。
1.创建数据模型
采用《第一次感受超级透视表的强大》中的第一种方法,分别将两张工作表添加到数据模型。
2.创建关系
要同时利用两张表进行分析,则必然要建立关系把两者联系起来。
简单来说,可以用两个表的相同列项来建立关系。
切换到Power Pivot “设计”选项卡,单击“创建关系”按钮创建关系。操作如下:
3.创建两个度量值
KPI是建立在度量值基础上的。与函数法类似,如果要得到完成比,必须有每人的销售总额和目标值。因此建立两个度量值:销售总额、目标。
在Excel中依次单击“Power Pivot”—“度量值”—“新建度量值”创建度量值。
度量值1:销售总额
度量值2:目标
注:估计你会疑惑,为何用SUM求人员销售总和?这是因为透视表可以自动按人员拆分总额,不需要用SUMIF类函数。
4.建立KPI
有了度量值后,即可创建KPI。KPI基本字段“销售总额”,定义目标值选择度量值“目标”。状态阈值按需设置,此处设置为50%和80%。低于50%,如果不采取措施难完成目标,用红色警示;超过80%的,表现优异,用绿色表示。
5.创建数据透视表
建立了KPI但没看到KPI分析表。
别急!当前只是建立了度量值之间的百分比关系和条件格式。
下面在透视表中运用度量值和KPI字段,即可得到KPI分析表。
Step 01 创建透视表。
Step 02 设置透视表字段。
展开透视表字段。前方有红绿灯图标的销售总额(红框圈出来的部分)就是KPI字段。其中的状态字段表示百分比计算以及条件格式。
将表2的“销售人员”字段拖入行中,然后将KPI字段拖入值中,得到KPI分析表。
在值列表框中单击“销售总额 状态”,选择“值字段设置”可以自定义名称如“KPI”。
当前看不到具体的完成度。如果需要看到它,可以新建度量值“完成度”并拖入透视表即可。
6.KPI分析表布局调整和更新
由于是通过透视表制作的,所以KPI分析表的组成和布局可以灵活调整,如下:
如果数据发生了变化,单击“主页”中的“刷新”按钮即可。
以上就是超级透视表第二波,做KPI分析。
本文配套的练习课件请加入QQ群:781412182下载。
做Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
版权申明:
本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。