星期四, 25 4 月, 2024

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

做数据分析的朋友,应该都听过/用过二八定律,据说是100+年前意大利的一位叫帕累托的大神发现的,他认为:

在任何一组东西中,最重要的只占其中一小部分,约为20%,其余80%尽管是多数,却是次要的

那么,帕累托大神的这个发现,对于我们的分析有哪些帮助呢?

举些例子,你就明白了:

  • 公司有100款产品,其中20款产品贡献了80%的业绩
  • 客诉类型有100种,其中20种类型占了80%的客诉量
  • 店铺有100个VIP客户,其中20个贡献了80%的业绩
  • 公司有100个员工,其中的20人获得了企业80%的工资额
  • 假设Excel有100个功能,你80%的时间只用到其中的20个

……二八定律的例子太多了,我们先想想,以上这些定律给了我们什么提示?

  • 这20款产品要重点呵护吧?
  • 这20类客诉要重点梳理解决吧?
  • 这20个VIP要悉心管理吧?
  • 这20个员工要能担当重任吧?
  • 这20个功能要熟练使用吧?

也就是说,通过二八定律,可以辅助我们找到管理重点,这样你就可以为这些重点对象投入相对更多的资源。在资源相对有限的情况下,这样关注重点的管理方法,可以让你的投入产出效率更高。

这里要注意的是,虽然定律的名字是叫二八,但我们在实际的数据分析中,通过历史数据算出来的结果往往不一定刚好是20%、80%,但这并不影响我们去使用它。因为二八定律给我们提供了一个思路,帮我们找出重点的管理对象。

好,相信通过上面的内容,大家都明白了什么是二八定律,以及它的意义了。但是刚有思路还不够,我们其实还要掌握怎么去实操,怎么去探索!(否则就可能是纸上谈兵了~)

接下来我们来讲讲,怎么用Excel去做实操,来探索二八定律。假设公司有以下这30个产品,取去年全年的销售额,得到的数据如下:

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

Excel2016及以上版本

如果你是Excel2016以上版本,那真是太简单了,因为Excel已经内置了这种图表。直接选中以上的数据源,你就能一键生成帕累托图!

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

没错!不用排序,不用写公式,选中数据源,只需要3步就搞定了!通过观察图表,可以看到S1~S7这7款产品,就贡献了80%的业绩。

如果你要精确算出比例,需要用7/30=23.3%,所以你从这个图中得出:23.3%的产品贡献了80%的业绩!所以这7款产品你是不是就得重点对待了~

Excel2013及以下版本

这些版本因为没有内置的帕累托图表,所以操作相对复杂一些,但复杂并不等于一无是处,其实也意味着可自定义,即灵活性就会更强。(继续看下去你就明白了!)

以下是实现的步骤:

1、对销售额字段进行降序排列

降序排列的作用,就是把销售额高的产品放在前面,即是把重点产品放到前面来,这是基础的数据整理。

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

2、增加计算字段[累计百分比]

我们的思路是用柱形较+折线图的组合,去实现帕累托图,所以要手工增加一个计算字段[累计百分比],作为折线图的数据源,这里可以用函数来构造,如下图所示。(这公式是滚动计算的常用套路)

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

3、选中全部的数据源,插入柱形图

这个步骤插入的柱形图,是会有2个数据系列的,如下图中你能看到图例中有 [销售额][累计百分比],但因为 [累计百分比] 的值相对 [销售额] 来说太小了,所以你无法看到 [累计百分比] 的柱子。

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

4、把 [累计百分比] 系列调整为次坐标轴

前面默认生成的图表,都是共用左边的坐标轴为主坐标轴的,现在我们要把 [累计百分比] 这个系列调整为次坐标轴,即是单独设置它的比例尺映射,以便它能正常地呈现在图表中。

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

5、把 [累计百分比] 系列的图表类型调整为折线图

如果你是Excel2013,界面会和下方图中的展示一样。

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

如果你是Excel2010及以下版本,则只需要选择折线图即可,如下图所示:

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

6、完善细节

因为这里Excel默认次坐标轴的最大值为1.2,所以建议调整为1,以便能更直观显示累计百分比的曲线。

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

Excel默认柱形较的间隙比较大,影响我们的关注主体,这里建议调整为5%以内即可。

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

7、增加80%的标记线(这就是自定义图表的好处)

这里需要增加3个辅助列,分别是:

[和80%的距离] :用于计算 [累计百分比] 这一列中每个值离80%的距离,公式为:

=ABS([@累计百分比]-80%)

[辅助系列1] :用于计算出离80%最近的那一个点,以便构造纵向标记线,公式为:

=IF([@[和80%的距离]]=MIN([和80%的距离]),[@累计百分比],NA())

[辅助系列2] :用于构造出大于“最近点”的数据系列,以便构造出横向标记线,公式为:

=IF([@累计百分比]<SUMIF([辅助系列1],”>0″),NA(),SUMIF([辅助系列1],”>0″))

以上公式为表格引用写法,主要为了方便大家阅读,也可以用单元格区域写法。构造的结果如下图所示:

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

[辅助系列1] 的处理:把这个系列的数据添加到图表中,图表类型为“带数据标记的折线图”,并改为次坐标轴;然后添加垂直百分比误差线,设置为100%

[辅助系列2] 的处理:把这个系列的数据添加到图表中,图表类型为“折线图”,并改为次坐标轴;

处理好以上2步后,你就能实现下图的效果了,可以一目了然地看到80%的销售额的点,而且是可以根据你数据源的变化而自动变化的喔!

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

还不够完美?那再完善一下,我们把具体的比例也计算出来,在旁边做一个辅助区域,这样做:

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

单元格H3的公式是:

=MATCH(SUMIF(数据源[辅助系列1],”>0″),数据源[累计百分比],0)/COUNTA(数据源[商品代码])

单元格K3的公式是:

=SUMIF(数据源[辅助系列1],”>0″)

然后把这个单元格区域,复制一下,粘贴成链接的图片,如果你的版本没有这个功能,就用“照相机”吧,具体你可以百度下,各版本通用。

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

然后就可以把图片移动到图表中的合适位置,完美展示了一个完整的帕累托图~

做一张自动化的帕累托图,帮你轻松找出管理的重点 | 附实操步骤

好,就这样完成了一个帕累托图的制作,当然重点是这个图表还是自动化的,下次你更新了数据源,图表也会自动更新!这正是我要的菜!

自动化是我们一直的追求,因为虽然实现的过程可能会有麻烦,但我们就是来解决麻烦事的,这也是我们的学习的动力,也是你在职场当中的差异竞争力。在这个过程当中,你的思维,技能也得到了锻炼,何乐而不为呢?

相关文章

财保研习社高端交流群

spot_img

热 门 文 章