一、准备带年消耗金额的原始数据表

ABC分析的起点是计算每个物料的年消耗金额（单价 × 年用量）。这个数值决定了后续的排序和累计计算，必须确保准确。

操作步骤如下：

• 在一个新的Excel工作表中，粘贴库存明细数据，至少包含物料编码、单价和年用量三列。
• 若没有“年消耗金额”列，请创建：在D1单元格输入“年消耗金额”，在D2输入公式 =B2*C2（假设B列为单价，C列为年用量），然后双击填充柄应用到所有行。

完成后，建议选中整个数据区域，按Ctrl+T将其转换为“表格”。这能让后续的公式引用和数据刷新更方便。

二、构建基础透视表并添加累计占比字段

数据透视表本身不直接提供累计百分比计算，但可以通过在源数据表中先行计算来实现。

具体操作：

1. 在源表格右侧新增两列，例如E列“排序序号”，F列“累计占比%”。
2. 首先，按年消耗金额降序排列所有数据，这是计算累计占比的基础。
3. 在F2单元格输入累计占比公式，例如：=SUM($D$2:D2)/SUM($D$2:$D$1000)（请将$D$1000替换为你数据实际的最后一行行号）。
4. 将F列单元格格式设置为百分比。这样，F列就会动态显示每一行物料金额占总金额的累计比例。

三、在透视表中实现ABC动态分类标记

有了累计占比，划分ABC类就水到渠成。通常的阈值是：

• A类：累计占比前70%-80%
• B类：接下来的15%-25%
• C类：剩余的5%-10%

这个阈值可根据管理需求微调。

操作步骤：

1. 在源数据表新增一列，例如G列，命名为“ABC类别”。
2. 在G2单元格输入IF函数公式，例如：=IF(F2<=0.8,"A",IF(F2<=0.95,"B","C"))。公式含义：累计占比≤80%为A，>80%且≤95%为B，其余为C。
3. 将公式下拉填充，每个物料便有了分类标签。
4. 最后，刷新数据透视表，将“ABC类别”字段拖到“行”区域，把“物料编码”和“年消耗金额”分别拖到“值”区域进行计数和求和。透视表即可清晰汇总展示A、B、C三类物料的数量和资金占比。

四、用数据透视图可视化帕累托曲线

图表能更直观地揭示规律。帕累托图是ABC分析的经典可视化工具，它结合了柱形图（单个物料金额）和折线图（累计占比）。

操作步骤：

1. 基于源数据，插入一个数据透视图，选择“簇状柱形图”作为起点。
2. 将“物料编码”拖到轴，“年消耗金额”拖到值，得到金额柱形图。
3. 关键一步：右键图表，选择“更改系列图表类型”，为累计占比系列选择“折线图”，并勾选“次坐标轴”。

至此，一张标准的帕累托分析图就初步成型，你可以清晰地看到少数物料贡献大部分金额的“二八效应”。

五、通过切片器实现交互式ABC筛选

分析的目的在于指导行动。切片器能帮助你快速聚焦于某一类物料进行深入分析。

操作步骤：

1. 确保源数据是表格格式。
2. 在数据透视表工具的“分析”选项卡下，点击“插入切片器”，勾选“ABC类别”字段。屏幕上会出现带有A、B、C按钮的控件。
3. 点击“A”，透视表和透视图会瞬间联动，只显示A类物料信息。
4. 按住Ctrl键还能多选，例如同时查看A类和B类，从而将管理精力聚焦在高价值物料上，暂时过滤掉C类物料的干扰。

通过以上五个步骤，你就能在Excel中，以数据透视表为核心，完成从数据准备、分类计算到可视化呈现和交互筛选的完整ABC分析了。这套方法逻辑清晰，可重复、可调整，能真正为库存优化决策提供有力支持。

《夸克》非常好用的免费AI浏览器

下载APP查看