如何在 Excel 中进行 ABC 分析：5 个简单步骤
ABC分析根据年度使用价值将库存物品分为三类。A类物品是少数占资金大头的物品。C类物品是多数但占资金极少的物品，B类物品则介于两者之间。在 Excel 中，您只需一张表格即可完成此操作：年度价值、所占比例、累计总计以及分配每个类别的公式。
本指南将解释这些类别的含义、五个 Excel 步骤、一个实际案例以及如何绘制结果图表。它还涵盖了如何选择分界点，以及在分析完成后如何处理每个类别。
什么是ABC分析
ABC分析是一种决定哪些物品最值得关注的方法。它基于一个简单的规律：极少数的物品占用了大部分的资金支出。
卫生管理科学（MSH）2012年关于分析和控制药品支出的一个章节对此进行了清晰的描述。它指出，“相对较少数量的物品占了年度消耗价值的大部分。”它补充道：“对这种现象的分析被称为帕累托分析，或者更常用的是，ABC分析。”
同一章节解释说，物品“可以根据其年度使用价值分为三个类别（A、B和C）”。无论您库存的是药品、备件还是零售产品，该方法都是相同的。
有一点很容易被忽略。这些类别并不是永久性的标签。MSH指出，“如果使用模式发生变化，在下一次进行ABC分析时，该物品可能会落入不同的类别。”因此，ABC分析最好作为一项常规检查，而不是一次性的项目。
A、B和C类别的含义
MSH章节给出了每个类别的典型范围：
| 类别 | 物品占比 | 年度价值占比 | 通常意味着 |
|---|---|---|---|
| A | 10% 到 20% | 75% 到 80% | 少数物品，大部分资金 |
| B | 10% 到 20% | 15% 到 20% | 中间群体 |
| C | 60% 到 80% | 5% 到 10% | 多数物品，极少资金 |
这些是典型范围，并非硬性规则。MSH表示“这些界限具有一定的灵活性”。相反，在其示例中，将A类物品设定为累计占资金 70% 的物品。
决定类别的价值是年度消耗价值：一年内使用的单位数量乘以单位成本。一个使用量巨大的廉价物品可能会被归入A类。而一个一年只使用一次的昂贵物品可能会被归入C类。
《美国商业教育杂志》（American Journal of Business Education）2014年的一篇文章对仅使用价值进行分类提出了质疑。它指出，教科书“侧重于将金额作为唯一标准”，并建议增加其他标准。不过对于初步筛选，MSH章节所使用的方法依然是价值。
开始之前您需要准备什么
在 Excel 中进行ABC分析，每个物品只需要几列数据：
- 物品名称或 SKU。 每个物品占一行。
- 年度使用或采购数量。 每个物品使用相同的 12 个月周期。
- 单位成本。 单个单位的成本，使用与您计数相同的单位。
MSH强调了周期的匹配性：“确保所有物品使用相同的审查周期，以避免无效的比较。”它还建议对成本和数量使用相同的基本单位，例如单片或单盒，而不是混合不同的包装规格。
如果您的数据来自库存或采购系统，请将其导出为 CSV 或 Excel 文件。删除该周期内没有活动的物品，或者保留它们并预期它们会被归入C类。
如何在 Excel 中进行ABC分析
以下五个步骤遵循 MSH 章节中的方法，并适配了 Excel 公式。该示例在第 1 行放置标题，在第 2 行放置表头，在第 3 到 12 行放置 10 个物品。A、B 和 C 列分别保存物品名称、年度数量和单位成本。
步骤 1：列出物品、数量和单位成本
输入或粘贴每个物品的数据，每个物品占一行，包括其名称、年度数量和单位成本。在第 2 行添加表头，以便稍后对表格进行排序。
在继续操作之前检查数据。寻找空白成本、负数数量和重复的 SKU，因为每一个都会扭曲总计。对每列进行快速筛选通常可以找到这些问题。
如果以不同的价格多次购买了同一物品，请使用一个一致的成本。MSH指出，当实际单位成本难以追踪时，“加权平均值或 FIFO（先进先出）平均值”是最准确的替代方案。
步骤 2：计算年度价值及其占总计的比例
在 D 列中，将数量乘以成本以获得每个物品的年度价值。在 D3 中输入 =B3*C3 and 并向下填充公式。
在 E 列中，将每个价值除以所有价值的总和以获得其占比。在 E3 中输入 =D3/SUM($D$3:$D$12) 并向下填充。美元符号可在复制公式时保持总计范围固定。将 E 列格式化为保留两位小数的百分比。
MSH推荐这种精度是有原因的。用它的话来说，“几个物品在价值上可能非常接近，而且许多物品可能占总价值的不到 1%。”
步骤 3：按价值对物品进行排序，从大到小
选择整个表格（包括表头），并按 D 列从大到小进行排序。在 Excel 中，操作为“数据”，然后“排序”，将排序列设为 D 列，顺序设为“降序”。
如果您更喜欢使用公式，SORT 函数可以返回排序后的副本。Microsoft 的语法为 =SORT(array,[sort_index],[sort_order],[by_col])，其中排序顺序为 -1 表示降序。对于此表格，=SORT(A3:E12,4,-1) 会按第四列排序，最高价值排在最前。
完成此步骤后，年度价值最高的物品将排在最顶部。这种顺序使得下一步中的累计总计具有实际意义。
步骤 4：添加累计百分比
在 F 列中，添加占比的累计总计。在 F3 中输入 =SUM($E$3:E3) 并向下填充。范围的第一部分保持固定，第二部分每次向下增加一行。
最后一行应该显示 100%。如果不是，请检查 D 列和 E 列中是否存在空白单元格或文本值。
这一列是ABC分析的核心。它显示了每行上方的物品共同占总价值的比例。
步骤 5：分配 A、B 和 C 类别
在 G 列中，使用公式为每个物品标记类别。如果分界点设为 80% 和 95%，请在 G3 中输入以下公式并向下填充：
=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")
IFS 函数会按顺序检查每个条件并返回第一个匹配项。Microsoft 自己的示例也使用了相同的模式，将 TRUE 作为最后的兜底条件。累计占比达到 80% 的物品归为 A，达到 95% 的物品归为 B，其余归为 C。
最后，使用 =COUNTIF(G3:G12,"A") 统计每个类别的数量，对 B 和 C 也是如此。将统计结果与上述典型范围进行比较。如果 A 类物品的数量太大或太小，超出了您团队的管理能力，请调整分界点。
一个实际案例
这是一个包含 10 个物品的说明性表格，已按年度价值排序。这些数字仅为示例，并非来自真实公司的数据。
| 物品 | 年度数量 | 单位成本 | 年度价值 | 占比 | 累计 | 类别 |
|---|---|---|---|---|---|---|
| SKU-01 | 1,200 | $45.00 | $54,000 | 36.00% | 36.00% | A |
| SKU-02 | 3,000 | $12.00 | $36,000 | 24.00% | 60.00% | A |
| SKU-03 | 500 | $40.00 | $20,000 | 13.33% | 73.33% | A |
| SKU-04 | 8,000 | $1.50 | $12,000 | 8.00% | 81.33% | B |
| SKU-05 | 2,000 | $4.00 | $8,000 | 5.33% | 86.67% | B |
| SKU-06 | 600 | $10.00 | $6,000 | 4.00% | 90.67% | B |
| SKU-07 | 1,000 | $5.00 | $5,000 | 3.33% | 94.00% | B |
| SKU-08 | 1,500 | $3.00 | $4,500 | 3.00% | 97.00% | C |
| SKU-09 | 700 | $5.00 | $3,500 | 2.33% | 99.33% | C |
| SKU-10 | 400 | $2.50 | $1,000 | 0.67% | 100.00% | C |
年度总价值为 $150,000。三个物品（占列表的 30%）占了价值的 73.33%，被归入 A 类。四个物品落入 B 类，最后三个物品（占价值的 6%）落入 C 类。
有两个细节值得注意。SKU-04 的数量是目前为止最多的，但其低廉的成本使其被归入 B 类。而且由于只有 10 个物品，各类别所占的比例不会与典型范围完全吻合，这对于简短的列表来说是正常现象。
如何绘制结果图表
图表可以让您在会议中轻松展示这一规律。MSH建议将累计百分比与物品编号进行对应绘制，从而得出我们熟悉的 ABC 曲线。
Excel 拥有用于此目的的内置图表。Microsoft 将帕累托图描述为“既包含按降序排列的柱形，又包含代表累计总百分比的折线”的图表。要创建一个帕累托图，请选择物品名称和年度价值，然后选择“插入”、“插入统计图表”，最后选择“帕累托图”。
在您的分界点（例如 80% 和 95%）处添加两条水平线或标签，以便观看者能够看到每个类别的起点。我们关于如何使用 AI 制作帕累托图的指南对图表本身进行了更深入的介绍。
选择您的分界点
没有唯一的正确分界点。MSH解释说，选择“取决于数量 and 价值在列表物品中的分散程度”。它还取决于“ABC分析结果将如何被使用”。
管理能力是实际的限制。MSH直接指出：“将物品分配到 A 类必须基于管理能力。”如果您的团队每月只能密切审查 50 个物品，那么包含 300 个物品的 A 类就失去了其存在的意义。
几种常见的方法：
- 价值分界点。 价值累计达到 80% 的归为 A，达到 95% 的归为 B，其余归为 C。这就是上面使用的方法。
- 物品数量分界点。 按价值排名前 20% 的物品归为 A，接下来的 30% 归为 B，其余归为 C。
- 固定列表。 一些团队将 A 类设定为前 25 或 50 个物品，而不管它们占价值的比例是多少。
无论您选择哪种方法，请将其记录下来并每次都使用它。只有在分界点保持不变的情况下，将本季度的类别与上季度的类别进行比较才有意义。
如何处理每个类别
ABC分析的重点是将精力花在资金占用最多的地方。MSH章节列出了几种利用分析结果的方法：
- 更频繁地订购 A 类物品。 MSH表示，“更频繁且以更小的数量”订购 A 类物品“应该会降低库存持有成本”。
- 优先谈判 A 类物品的价格。 根据该章节，“在分析中被归为 A 类的物品，其价格降低可以带来显著的资金节省”。
- 更频繁地盘点 A 类库存。 MSH指出，“循环库存盘点应以 ABC 分析为指导，对 A 类物品进行更频繁的盘点。”
- 密切关注 A 类订单状态。 A 类物品的意外短缺可能会导致成本高昂的紧急采购。
C 类物品可以采用更简单的规则，例如更大批量、更低频率的订购以及更少的盘点次数。B 类则介于两者之间。如果您担心滞销品，我们关于如何识别滞销库存的指南可以与此分析很好地结合使用。
利用 AI 更快地完成
一旦数据清理干净，Excel 步骤只需几分钟。但清理导出的数据并每季度重复这项工作需要更长的时间。
AI 工作区可以在一个请求中完成算术计算和排序。将导出的库存或采购数据上传到 Powerdrill Bloom，并用自然语言请求根据您的分界点进行 ABC 分析。要求提供每个物品的年度价值、占比、累计百分比和类别，外加一张帕累托图。
然后像检查任何电子表格一样检查它。将年度总价值与您自己计算的总和进行核对，并抽查每个类别中的两个物品。我们的 Excel AI assistant 页面更详细地介绍了此类电子表格工作。要更广泛地了解预测工具，请参阅这篇关于用于库存和需求预测的最佳 AI 工具的综述。
要避免的常见错误
- 混合时间周期。 一个物品使用 12 个月的数据，而另一个使用 6 个月，这会使占比失去意义。
- 使用数量而非价值。 类别取决于数量乘以成本，而不仅仅是数量。
- 在计算累计总计前忘记排序。 在未排序的列表上计算累计百分比会将物品归入错误的类别。
- 将类别视为永久性的。 每个季度或每年重新运行分析，因为物品会在不同类别之间移动。
- 忽略管理能力的分界点。 如果 A 类列表太长而无法密切管理，那么它得到的关注不会比 B 类多。
- 忽略关键的廉价物品。 如果低价值物品用尽，仍可能导致工作停滞。MSH章节将 ABC 分析与对至关重要、必不可少和非必需物品的单独评级结合使用。
当您的物品列表来自混乱的导出数据时，您可以尝试 Powerdrill Bloom 来构建第一个 ABC 表格和图表。
常见问题解答
什么是库存管理中的 ABC 分析？
ABC分析根据年度消耗价值将物品分为三类。A 类物品是少数占价值大头的物品。C 类物品是多数但占价值极少的物品，B 类则介于两者之间。它有助于团队将控制精力集中在资金占用最多的地方。
如何在 Excel 中计算 ABC 分析？
将每个物品的年度数量乘以单位成本，然后除以总计以获得每个物品的占比。按价值从大到小排序，添加占比的累计总计，并使用诸如 IFS 的公式分配类别。80% 和 95% 的分界点符合 MSH 章节中的典型范围。
ABC分析的百分比是多少？
一个常见的指导原则是，A 类包含 10% 到 20% 的物品和 75% 到 80% 的价值。B 类包含另外 10% 到 20% 的物品和 15% 到 20% 的价值。C 类包含 60% 到 80% 的物品 and 和 5% 到 10% 的价值。
Excel 中 ABC 分类的公式是什么？
假设累计百分比在 F 列，数据从第 3 行开始，使用 =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")。将 0.8 和 0.95 更改为符合您自己分界点的值。嵌套的 IF 公式也可以完成同样的工作。
为什么 ABC 分析很重要？
它展示了大部分库存资金的去向，以便团队能够更密切地管理这些物品。典型用途包括更频繁地订购 A 类物品、优先谈判其价格以及更频繁地盘点它们。它还可以标记与计划不符的支出。
来源： Management Sciences for Health, MDS-3 第 40 章：分析和控制药品支出 · Ravinder 和 Misra，用于库存管理的 ABC 分析 (2014) · Microsoft 支持，SORT 函数 · Microsoft 支持，IFS 函数 · Microsoft 支持，创建帕累托图。