excel 如何将帕累托分析法运用于制作图表的3个方法

Excel技巧 2021-11-25 12:37:58

想要制作出这样的图表，需要按照以下的3个分析手法的顺序处理。

➊ 排名分析

首先，可以简单地按照消费金额的大小给客户排序。这时，可通过 Excel 的排序功能处理。这样就可以将重要的客户排在名单的最上方。

例如，在提交新产品方案的时候，可以按照这个顺序给客户打电话。如果客户名单是按姓氏排列的话，选择使用下列的哪一种方法，也会大大影响销售的效率。

Excel 的“排序”功能能够帮助我们实现“从最重要的客户开始联络”这样理所当然的想法。

➋ ABC 分析

按照客户分类的销售额数据进行降序排列后，计算出每个客户的消费金额的构成比率。在下一页的表格中，单元格 C1中含有所有客户的消费金额的总和。将 C1和每位客户的消费金额做除法，可以得出结构比率的数据。在单元格 D4中输入下列公式，一直复制粘贴到最后一行。

=C4/$C$1

然后，将结构比率相加，可以得出累计结构比率。

得出每个客户的消费金额的结构比率

得出累计结构比率

像这样，比如就能知道“前3家客户公司占了整体销售额的50%，说明这三家公司是我们重要的客户”。为了明确客户的重要程度，“将客户分成若干小组”这种分析手法就是 ABC 分析法。在此，将累计结构比率大于或等于80%的客户分到 A 组，小于80%的分到 B 组。

可用 IF 函数处理这种分类。在单元格 F4中输入下列公式，一直复制粘贴到最后一行，就能自动分成 A、B 两组了。

=IF(E4<80%,"A","B")

在单元格 F4中输入=IF(E4<80%,"A","B")，分成 A、B 组

➌ 帕累托分析

像这样将客户分为 A 等组与 B 等组之后，接下来就要计算两组的“交易件数”和“总计金额”了。表格则呈现以下形式。

得出 A 组客户与 B 组客户的“交易件数”与“总计金额”

用 COUNTIF 函数计算“A 和 B 各有多少个”。

用 SUMIF 函数得出“A 和 B 的总销售额是多少”。

得出实际数字后，再计算结构比率。A 的销售件数占整体的26%，但销售额却占结构比率的80%。而 B 的销售件数虽然占整体的74%，销售额的结构比率却不足20%。将这个结果制作成图表，就会看到前文中的“按客户分类的销售额结构比率”那样的表。

这样就一目了然了吧？对于这家公司来说，首先必须维护好 A 组客户，当然也不能忽视 B 组。但从优先顺序来说，应该要对 A 组客户予以更多的重视。从经营战略角度来说，

“把提供给 A 组客户的优惠政策介绍给 B 组客户，可以刺激 B 组客户的购买欲，积极升级到 A 组的等级。”

而提出这种战略计划的根据或分析，只需制作一张简单的 Excel 表格。

在削减经费方面也是一样。提取出占据大部分比例的要素后，不对症下药的话也不会有任何改善。如将便利贴裁成一半使用，重复利用打印纸的反面，等等，与其在这种事情上花费大量的时间，还不如多找出“占整体经费80%的支出是什么”。

“社长的租车费用占了很大比例。”
“如果减掉‘管理层的交际费’里面每月的夜总会花销，经费能节省20%。”

这样就能发现一些真正需要改善的地方。不要在费力而不见效的事情上花费大量的时间和精力。为了明确真正应该付出努力的对象，一定要在了解 Excel 的基础技能的基础上，熟练运用各种分析手法。