​​Excel技巧，​​做一张自动化的帕累托图，帮你轻松找出管理的重点！

做数据分析的朋友，应该都听过/用过二八定律，据说是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([@累计百分比]

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

[辅助系列1]的处理：把这个系列的数据添加到图表中，图表类型为“带数据标记的折线图”，并改为次坐标轴；

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

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

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

单元格H3的公式是：

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

单元格K3的公式是：

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

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

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

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

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

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；