Excel 进行ABC分析模型示例

一、ABC 分析模型简介

   ABC 分析模型：分清“轻重缓急”的利器

ABC分析模型，又称帕累托分析或二八法则，其核心思想是：少数关键因素通常贡献了大部分价值。在库存管理中，它会根据商品对销售额的贡献度将产品分为三类：

• A类商品：品种占比小，但价值贡献极高（通常是贡献前80%销售额的商品）。需要重点管理、优先补货。

• B类商品：价值和数量都处于中等水平。采用标准化、自动化的常规管理即可。

• C类商品：品种繁多，但价值贡献低。为节约成本，应采取简化流程、降低库存的策略。

使用场景：ABC分析广泛应用于库存管理、客户价值分析、成本控制等领域，帮助管理者将有限的资源投入到最关键的地方，实现效益最大化。

二、示例数据

假设某电商公司有 10 款商品，2025 年销售金额如下：

实际工作中可能数据量很大，但方法完全一样。

三、Excel 操作步骤

第 1 步：按销售金额降序排序

选中数据区域（A1:C11），点击 数据 → 排序 → 主要关键字选择“销售金额”，次序选择“降序”。排序后结果如下（金额从高到低）：

第 2 步：计算累计销售额

在 D2 单元格输入：

=C2

在 D3 单元格输入：

=D2+C3

然后向下拖动填充至 D11。

结果：

总计销售额为 1375 万元。

第 3 步：计算累计占比

在 E2 单元格输入：

=D2/$D$11

设置单元格格式为 百分比，保留 1 位小数。向下拖动填充。

结果：

第 4 步：确定 ABC 分类标准

通常：

• A类：累计占比 ≤ 80%

• B类：80% < 累计占比 ≤ 95%

• C类：累计占比 > 95%

在 F2 单元格输入公式：

=IF(E2<=0.8, "A", IF(E2<=0.95, "B", "C"))

向下拖动填充。

结果：

可以根据业务需要调整阈值（例如 A 类为前 70%，B 类 70%-90% 等）。

第 5 步：可视化呈现（帕累托图）

1. 选中商品名称（B2:B11）销售额(C2:C11)和累计占比（E2:E11）。

2. 插入 组合图：

• 累计占比系列改为 折线图，并勾选 次坐标轴。

• 销售金额系列为 簇状柱形图。

3. 调整折线图的“数据标记”使其更清晰。

4. 在 80% 和 95% 位置添加水平参考线（可通过添加辅助数据系列或手动绘制形状）。

效果：柱形图从高到低排列，折线图反映累计占比，可直观看到 A/B/C 的分界点。

四、常见问题与解决

五、总结

通过上述步骤，你已经可以用 Excel 完成标准的 ABC 分析模型：

1. 排序 → 2. 累计求和 → 3. 计算占比 → 4. IF 分类 → 5. 帕累托图

这个模型不仅适用于商品销售分析，还可以推广到客户价值分析（按消费金额）、库存占用资金分析、供应商绩效分析等场景。掌握了这个方法，你就能在日常工作中快速识别出最关键的 20% 要素，从而集中资源进行管理。