超级促销周Claude Skills——20% 折扣
Tips

如何在 Excel 中制作库存追踪器:分步指南

Powerdrill Bloom·
如何在 Excel 中制作库存追踪器:分步指南

要在 Excel 中制作库存追踪器,需要构建两张表。第一张表列出您的物品,包含物品 ID、名称、单位成本、期初库存和再订货点等列。第二张表记录每一次变动,包含日期、物品 ID、类型(入库或出库)和数量。然后使用 SUMIFS 函数汇总入库和出库数量,并将它们相减得出在库库存。

添加一个状态列,标记处于或低于再订货点的物品,并使用条件格式突出显示这些行。顶部的简短摘要可以显示总库存价值以及有多少物品需要重新订货。

本指南涵盖了从表格结构到公式和警报的每个步骤。它还通过一个完整示例(一个小型办公用品储藏室)进行演示,并介绍了一种从导出数据构建追踪器的更快方法。步骤参考自 Microsoft 支持页面,读取日期为 2026年11月18日。

库存追踪器的作用

库存追踪器随时回答三个问题:

  • 每种物品我们有多少? 这是在库库存。
  • 它价值多少? 这是基于单位成本的库存价值。
  • 我们需要订购什么? 这些是处于或低于其再订货点的物品。

一个好的追踪器还会保留历史记录。当计数看起来不对时,您可以滚动查看变动日志,找到库存入库或出库的时间。

Excel 中的库存追踪器非常适合中小型库存。它适用于储藏室、小商店、办公用品、备件和活动设备。一旦您需要追踪分布在多个地点的数千件物品,专用的库存系统通常是更好的选择。

规划追踪器:两张表

在输入任何公式之前,先确定库存追踪器的结构。许多简单的追踪器使用一张表,其中只有一个数量列供人们覆盖。这很快就会出问题。没人知道数字是什么时候更改的,也不知道为什么要更改。

更好的设计是将物品列表与变动记录分开:

表格 每一行代表 主要列
物品表 (Items) 产品或零件 物品 ID、名称、类别、单位成本、期初库存、再订货点
变动表 (Movements) 库存变化 日期、物品 ID、类型(入库或出库)、数量、备注

物品表保存很少更改的事实。变动表每天都在增长。物品表中的公式读取变动表并计算当前库存。

这种设计有三个好处。您永远不会覆盖数字。每次更改都有日期。而且您以后可以回答诸如“3 月份出库了多少库存”之类的问题。

它还使库存追踪器在共享时更安全。员工只需向变动表添加行。带有公式的物品表可以保持锁定状态。

将每张表放在各自的工作表中。将工作表命名为“Items”和“Movements”,以便公式易于阅读。

将物品列表构建为表格

从 Items 工作表开始。在第 1 行输入标题:物品 ID、名称、类别、单位成本、期初库存和再订货点。然后为每个物品输入一行。

接下来,将该区域转换为 Excel 表格。Microsoft 解释了原因:“为了使管理和分析一组相关数据更容易,您可以将单元格区域转换为 Excel 表格。” 创建方法如下:

  1. 选择数据中的任意单元格。
  2. 选择开始 > 套用表格格式。
  3. 选择一种表格样式。
  4. 确保选中了“表包含标题”复选框,然后选择确定。

在表格设计选项卡上将表格命名为“Items”。对另一个工作表上的变动日志执行相同操作,并将其命名为“Movements”。

表格在两个方面很有帮助。当您添加行时,它们会自动增长。而且表格列中的公式会自动向下填充。Microsoft 表示,在单元格中输入的公式“会立即应用于该表格列中的所有其他单元格”。

保持物品 ID 短小且唯一,例如 A-101。在所有地方都使用 ID 而不是名称,因为名称的拼写可能会有所不同。

为变动日志添加下拉列表

输入错误是库存计数出现偏差的主要原因。下拉列表可以阻止大部分错误。

Microsoft 用一句话解释了其目的:“使用数据验证来限制用户在单元格中输入的数据类型或值,例如下拉列表。” 对于“类型”列:

  1. 选择 Movements 表中的“类型”列。
  2. 选择数据 > 数据验证。
  3. 在设置选项卡的允许框中,选择序列。
  4. 在来源框中,输入 入库,出库。
  5. 确保选中了单元格内提供下拉箭头,然后选择确定。

对“物品 ID”列执行相同操作,但将来源指向 Items 表的“物品 ID”列。现在,人们只能选择已存在的物品。

下拉列表还能加快输入速度。仓库管理员只需选择物品和类型,然后输入数量,即可在几秒钟内记录一次交付。

您还可以限制“数量”列。在允许框中,选择整数并设置最小值为 1。这将阻止空白和负数输入。

使用 SUMIFS 计算在库库存

现在将计算列添加到 Items 表中。SUMIFS 可以对满足多个条件的数字求和。Microsoft 的语法是:

SUMIFS(求和区域, 条件区域1, 条件1, [条件区域2, 条件2], ...)

添加一列名为“入库总量”。在其第一个单元格中,输入:

=SUMIFS(Movements[数量],Movements[物品 ID],[@[物品 ID]],Movements[类型],"入库")

这会汇总 Movements 表中物品 ID 与当前行匹配且类型为“入库”的所有数量。表格会自动为每个物品向下填充公式。

添加一个“出库总量”列,使用相同的公式,但将“入库”改为“出库”。

然后添加“在库库存”:

=[@[期初库存]]+[@[入库总量]]-[@[出库总量]]

最后,添加“库存价值”:

=[@[在库库存]]*[@[单位成本]]

构建公式后,手动检查一个物品。将 Movements 表筛选为该物品,加总入库和出库行,并将结果与“在库库存”列进行比较。

标记需要重新订货的物品

库存追踪器在告诉您该做什么时最有用。添加一个“状态”列,使用此公式:

=IF([@[在库库存]]<=[@[再订货点]],"重新订货","正常")

现在每一行都会显示“重新订货”或“正常”。根据订单送达所需的时间以及该时间段内的使用量来设置再订货点。

使用条件格式让“重新订货”行脱颖而出:

  1. 选择“状态”列。
  2. 选择开始 > 条件格式 > 突出显示单元格规则 > 文本包含。
  3. 输入重新订货并选择一种填充颜色,例如浅红色。
  4. 选择确定。

Microsoft 指出,当“文本包含”对话框出现时,您“输入要应用的格式选项并单击确定”。每当库存发生变化时,突出显示都会更新。

您还可以在下订单前对“状态”列进行排序或筛选,以仅查看“重新订货”行。

使用 XLOOKUP 查找物品详情

变动日志显示的是物品 ID,这很难阅读。查找列可以在每次变动旁边添加物品名称。

Microsoft 将 XLOOKUP 描述为一种“按行在表格或区域中查找内容”的方法。其语法为:

XLOOKUP(查找值, 查找数组, 返回数组, [未找到值], [匹配模式], [搜索模式])

在 Movements 表中添加一个“名称”列并输入:

=XLOOKUP([@[物品 ID]],Items[物品 ID],Items[名称],"未知 ID")

如果有人输入了不存在的 ID,单元格将显示“未知 ID”。这使得错误很容易被发现。

Microsoft 指出,“XLOOKUP 在 Excel 2016 和 Excel 2019 中不可用”。在这些版本中,请使用 INDEX 和 MATCH,或者将物品 ID 放在第一列并使用 VLOOKUP。

在顶部添加摘要

库存追踪器的最后一部分是摘要。简短的摘要可以帮助任何人无需滚动即可查看库存状态。在 Items 表上方或单独的“Summary”工作表中添加几个单元格。

使用以下公式:

  • 总库存价值: =SUM(Items[库存价值])
  • 需重新订货的物品数: =COUNTIF(Items[状态],"重新订货")
  • 本月出库单位数: =SUMIFS(Movements[数量],Movements[类型],"出库",Movements[日期],">="&DATE(2026,11,1))

COUNTIF 遵循简单的模式。用 Microsoft 的话来说,它询问“你想在哪里看?”和“你想找什么?”

您还可以添加按类别划分的库存价值条形图。选择摘要区域并选择插入 > 图表。保持摘要简洁,包含三到五个关键数字即可。

操作示例:办公用品储藏室

这是一个使用中的库存追踪器的完整示例。一个小办公室追踪五种供应物品。11 月份之前,Items 表如下所示:

物品 ID 名称 单位成本 期初库存 再订货点
A-101 A5 笔记本 $2.50 120 50
A-102 圆珠笔(盒) $6.00 40 20
B-201 打印纸(令) $4.20 80 30
B-202 硒鼓 $45.00 12 5
C-301 运输箱(中号) $0.80 300 100

在 11 月份,Movements 表记录了这些变化。笔记本:出库 85,入库 50。笔:出库 25。纸:出库 60,入库 40。硒鼓:出库 9。箱子:出库 180。

公式运行后,计算列显示:

物品 ID 入库总量 出库总量 在库库存 库存价值 状态
A-101 50 85 85 $212.50 正常
A-102 0 25 15 $90.00 重新订货
B-201 40 60 60 $252.00 正常
B-202 0 9 3 $135.00 重新订货
C-301 0 180 120 $96.00 正常

该示例还说明了为什么变动日志很重要。打印纸看起来还有 60 令,但日志显示一个月内就出库了 60 令。如果这种速度持续下去,打印纸将在几周内达到再订货点。单一的数量列会隐藏这种趋势。

手动检查一行。笔记本起始为 120,增加 50,减少 85,所以 120 + 50 − 85 = 85。这与“在库库存”列相符。

摘要显示总库存价值为 $785.50,有 2 件物品需要重新订货。笔和硒鼓都低于其再订货点,因此它们会出现在本周的订单中。

使用 AI 在 Excel 中制作库存追踪器

手动构建追踪器需要一两个小时。慢的部分通常是数据:清理从销售点系统导出的数据、匹配物品名称以及修复重复的 ID。

AI 工具可以为您完成清理和分析工作。以下是使用 Powerdrill Bloom 的三个步骤。

第 1 步:上传您的 Excel、CSV 或 PDF 文件

上传您的库存导出文件、供应商列表或上个月的盘点表。Excel AI Assistant 可以直接处理电子表格文件。

如果您有物品列表和变动记录,请同时包含两者,即使它们在不同的文件中。

上传库存导出文件以使用 Powerdrill Bloom 在 Excel 中制作库存追踪器

第 2 步:用自然语言要求生成追踪器

描述您的需求。例如:“构建一个包含在库库存、库存价值以及针对 20 个单位或以下的物品设置再订货标记的库存追踪器。” 让它列出本周需要订购的物品。

您还可以提出后续问题,例如哪个类别的资金占用最多。

第 3 步:查看结果并导出

根据您上次的盘点情况检查几个物品的在库数量。然后将清理后的表格导出回 Excel,或者将发现的结果转化为简短的报告或幻灯片发给您的团队。

当库存追踪器滞后时,这非常有用。从销售系统每月导出的数据可以显示实际出库情况,您可以将其与日志进行比较。

日常更新请保留双表设计。当您需要快速清理或进行月度审查时,请使用 AI 步骤。

在导出库存追踪器之前查看在库库存

要避免的常见错误

库存追踪器的大多数问题源于人们输入数据的方式,而不是公式。以下是 Excel 库存追踪器中最常出现的问题。

  • 覆盖数量。 应将每次更改记录为新的变动。
  • 使用名称而非 ID。 “笔”和“笔盒”会变成两个物品。请使用唯一的 ID。
  • 没有期初库存。 没有起始计数,每个在库数字都是错的。
  • 单位混用。 对同一物品,要么追踪“盒”,要么追踪“支”,不要混用。
  • 没有定期盘点。 每月或每季度将追踪器与实物盘点进行比较。
  • 从不审查再订货点。 当使用量或供应商交货时间发生变化时,请更新它们。
  • 删除日志行。 永远不要为了纠正错误而删除变动记录。应添加一条带有备注的修正分录。
  • 公式被覆盖。 保护 Items 工作表,防止有人用数字替换公式。

要查找在货架上存放时间过长的物品,请参阅我们的指南:如何发现呆滞库存。关于预测需求,请参阅我们的 库存和需求预测 AI 工具列表。

当您的库存数据处于混乱的导出状态时,尝试使用 Powerdrill Bloom 将其清理成现成的追踪器。

常见问题解答

如何在 Excel 中创建一个简单的库存追踪器?

创建一个包含物品 ID、名称、单位成本、期初库存和再订货点的 Items 表。添加一个记录每次变化的 Movements 表。然后使用 SUMIFS 计算入库总量、出库总量和在库库存。

Excel 中计算在库库存的公式是什么?

在库库存等于期初库存加上入库总量减去出库总量。使用 SUMIFS 从变动日志中汇总每个物品的入库和出库数量。

如何让 Excel 标记低库存?

添加一个带有 IF 公式的状态列,当在库库存处于或低于再订货点时返回“重新订货”。然后使用条件格式突出显示这些单元格。

我应该多久更新一次库存追踪器?

在每次变动发生时记录,或者至少每天记录一次。然后每月或每季度将追踪器与实物盘点进行比较,并通过调整分录修复任何差距。

我可以在 Excel 中追踪多个地点的库存吗?

可以。在 Movements 表中添加一个“地点”列,并在 SUMIFS 中将“地点”添加为第二个条件。这样每个物品在每个地点都会有一个在库数字。

来源: Microsoft 支持,Excel 表格概述 · Microsoft 支持,对单元格应用数据验证 · Microsoft 支持,SUMIFS 函数 · Microsoft 支持,XLOOKUP 函数 · Microsoft 支持,在 Excel 中使用条件格式突出显示信息 · Microsoft 支持,在 Microsoft Excel 中使用 COUNTIF 函数。