Excel 利用帕累托分析销售贡献量（二八定律）

1、帕累托图（Pareto Chart），又称“二八定律图”，是一种常用于业务分析的工具，通过图形化的方式，直观地显示出导致问题的关键因素和其相对重要性。

2、帕累托图通常由两个坐标轴组成：

• 左侧纵轴表示问题数量或成本，右侧纵轴则表示问题所占比例。

• 水平轴则按照问题的分类进行划分，如产品类别、客户投诉原因等。

3、结论：

通过观察帕累托图，可以发现少数因素导致了绝大多数的问题。基于这个发现，我们可以采取有针对性的改进措施，优先解决那些导致问题最多的关键因素，从而提高企业的效率和质量。

二、效果图

三、业务需求

某自行车厂商提供了近几年的自行车销售数据，希望使用帕累托分析帮助找出配件中销量贡献最多的产品子类别。

分析业务需求：

• 方法：利用帕累托分析——制作帕累托图；
• 指标：配件中销售贡献——销售额；
• 目标：找出产品子类别。

四、制定操作步骤

操作步骤：

1、收集数据：按照产品子类别进行统计（数据透视），并记录下每种分类中的销售额。

2、计算累积值：将产品子类别按照销售额从大到小排序，并计算出每个分类的累积值。

3、绘制帕累托图：在左侧纵轴上绘制产品子类别的销售额，右侧纵轴上绘制销售额的累计占比。

五、根据操作步骤画帕累托图

1、收集数据

1.1 处理《原始数据》，增加字段”销售额“

筛选《原始数据》中的产品类别为配件，将筛选出的数据插入到新建表《数据筛选》中，并计算销售额：单价*订单数量。

1.2 利用数据透视表，实现分组求和

在《数据筛选》中使用快捷键”CTRL+T“创建表，点击插入—插入数据透视表—新工作表—重命名工作表为”数据透视“—勾选产品子类别、销售额。

2、计算累积值

将《数据透视》表中计算出的数据复制到新建的《统计表》中，将产品子类别按照销售额从大到小排序，并利用公式计算占比：类别销售额/总销售额，最后修改格式为”百分比“。另外计算一下累计占比。

3、绘制帕累托图

• 选中表格插入组合图。

• 右键点击“选择数据”，选择系列的数据。
  

• 修改系列”产品子类别“的间距、填充颜色和边框样式。修改折线图的图表类型和节点颜色。
  

• 在累计百分比添加辅助列“0.00”，重新设置累计百分比系列的取值。这一步有个难点就是调整折线图的横坐标为从0开始：图表设计——添加图表元素——坐标轴——次要横坐标——右键调整次要横坐标的坐标轴位置：在刻度线上；主（次）刻度线类型：无；标签位置：无。
  

• 添加8020系列为散点图，纵坐标为80%，横坐标懒得计算可以大概估计一下，这里估计为4.7。（横坐标总长度为10）。
  

• 用”误差线“修饰散点图。
  

• 简单修饰图表。
  

六、得出结论

1、结论：

配件中销量贡献最多的产品子类别：

• 按照80%的累计贡献量：车胎和内胎、头盔、挂架、自行车支架；
• 按照65%的累计贡献量：车胎和内胎、头盔。

2、建议：

• 稳定车胎和内胎、头盔的销售量和质量；
• 扩充挂架和自行车支架的销售量。