告别手动盘点：SCAN加REUDCE函数自动化公式精准预测缺货时间

优秀创作者

全文约2600字；

阅读时间：约8分钟；

听完时间：约16分钟；

在2024年6月28日，工厂的生产物料控制（PMC）部门收到了销售团队提交的零件需求计划表。该表格清晰地列出了未来数日各种零件的需求详情，其中B列标识零件编号（如零件1、零件2等），而C列与D列则分别记录了各零件所需的时间和数量。

另一份关键资料是表2，它汇总了当前的零件库存情况：F列展示了零件编号的具体信息，G列则标注了相应零件的现有库存量。面对这份需求计划，仓库管理人员的任务是迅速识别并预警任何即将出现的库存短缺日期。以零件1为例，其在6月28日和29日的需求量分别是700和400件，而实际库存仅有900件，因此预计在6月29日会出现短缺。

鉴于零件种类繁多，人工逐一检查并确定每个零件的最早欠料日期不仅耗时且劳动强度大。为此，亟需一个自动化公式来高效计算各个零件的最早潜在缺货时间，从而提升工作效率。

设计思路

考虑到零件需求表中，相同零件在多个日期各有不同的需求量，我们需要对每个零件的需求数量进行累计（以零件1为例，汇总其所有指定日期的需求）。一旦获得零件的累计需求总量，接下来将该零件的固定库存量（以900件计）从累计需求量中扣除，以此来判定是否存在欠料情况。

举例来说，若零件1的累计需求量为700件和1100件，而库存量为900件，通过库存减去累计需求的计算，我们可得第一次需求满足后剩余200件，但第二次需求则导致库存短缺200件，即结果为200件余量和-200件欠量。这样的计算方法能直观展示在不同时间点上的库存满足程度及短缺情况。

基于上述计算结果，我们利用逻辑判断来识别哪些需求导致库存不足，即判断结果为小于零的情况，这会得到一组布尔值（FALSE表示不欠料，TRUE表示欠料）。随后，运用VLOOKUP函数根据这些逻辑值（FALSE和TRUE）在预先定义的查找区域中进行匹配，查找区域明确标记了与逻辑值相对应的日期——例如，FALSE对应6月28日，TRUE对应6月29日。通过这种方式，VLOOKUP能够准确返回欠料发生的日期，即29日，从而高效地确定了缺货的具体时间点。

筛选数据

首先，针对零件1，我们需要筛选出其对应的所有需求日期及需求数量。实现这一目标的步骤是在合适的位置输入以下公式： =FILTER(C3:D19,B3:B19=F3)

公式解释：

此公式利用FILTER函数来选取数据范围C3:D19中的行，条件是该行在列B（零件编号列）的值等于指定的零件编号（在本例中是单元格F3中零件1的编号）。换句话说，这个公式的作用是从原始数据集中筛选出所有零件1的记录，这些记录包括了它们的需求日期（列C）和需求数量（列D）。这样，我们就能够专注于零件1的需求详情，为进一步的分析和计算做准备，比如计算累计需求量和判断欠料日期。

累计需求

第二步，为了计算零件1的累计需求总量，需在合适位置插入以下公式：

=LET(a,FILTER(C3:D19,B3:B19=F3),SCAN(0,TAKE(a,,-1),SUM))

公式解释：

此公式运用了LET函数来定义变量并简化表达，步骤如下：

定义变量a：首先通过FILTER函数筛选出与零件1相对应的所有行（包含日期和需求量），并将这部分数据赋给变量a。

提取需求量列：利用TAKE(a, , -1)从变量a中仅提取最后一列的数据，即零件1的每日需求量。

累计求和：通过SCAN函数对提取出的需求量实施累积求和。SCAN的初始值设为0，意味着从零开始累加。对于需求量序列中的每一项，它都会加上前一项的总和，从而得出到当前位置为止的累计需求量。

综上所述，此公式旨在计算零件1在不同日期的累计需求总量，为后续判断库存是否能满足需求提供必要的数据基础。

库存判断

第三步，为了获取零件1的库存量并计算其相对于累计需求的剩余或短缺量，请在适当位置输入以下公式：

=LET(a,FILTER(C3:D19,B3:B19=F3),b,SCAN(0,TAKE(a,,-1),SUM),VLOOKUP(F3,F3:G10,2,0)-b)

公式解释：

定义变量a：依旧使用FILTER函数筛选出零件1的所有需求记录，存储于变量a中。

累计需求求和：通过之前定义的方式，使用SCAN函数对零件1的需求量进行累加，并将结果存储在变量b中。

查找零件库存：运用VLOOKUP函数在范围F3:G10中查找零件1的库存量。这里，F3是零件编号的参考值，查找范围的第二列（索引号2）包含了库存量，FALSE或0作为最后一个参数确保查找是精确匹配。

计算库存余量：最后，从查找到的库存量中减去累计需求量b，得到的结果将是零件1在满足需求后的剩余库存量或短缺量。

此公式综合运用了数据筛选、累计计算和查找引用功能，旨在快速准确地评估零件1的库存状况，判断是否存在库存短缺及短缺的程度。

缺货日期

为了根据库存是否能满足需求来确定短缺发生的具体日期，我们在适当位置使用如下公式：

=LET(a,FILTER(C3:D19,B3:B19=F3),b,SCAN(0,TAKE(a,,-1),SUM),XLOOKUP(1=1,VLOOKUP(F3,F3:G10,2,0)-b<0,TAKE(a,,1)))

公式解释：

定义变量a：筛选零件1对应的需求日期和数量

定义变量b：零件1的累计需求

XLOOKUP(...):

查找值：1=1,返回 TRUE，

查找区域：VLOOKUP(F3,F3:G10,2,0)-b<0，库存减去累计需求的结果（200；-200）进行小于零判断，返回（FLASE、TRUE)

返回区域：TAKE(a,,1)，也就是零件1的需求日期（6月28日;6月29日），最终返回6月29日

堆叠数据

如果只要填充公式的话，上面的公式结果已经可以快速的判断出零件的缺货日期，如果追求一键计算的话，可以录入以下公式进行动态数组填充：

=DROP(REDUCE("",F3:F10,LAMBDA(m,n,LET(a,FILTER(C3:D19,B3:B19=n),b,SCAN(0,TAKE(a,,-1),SUM),VSTACK(m,XLOOKUP(1=1,VLOOKUP(n,F3:G10,2,)-b<0,TAKE(a,,1),""))))),1)

函数解释：

REDUCE 函数：此函数用于迭代处理数组F3:F10中的每个元素（零件编号），初始值设为空字符串""。对于数组中的每个零件编号n，执行一个自定义的LAMBDA函数。

LAMBDA 函数：这是一个用户自定义的函数，它接收两个参数：累计结果m和当前循环中的零件编号n。对于每个n，进行以下操作：

LET 函数：引入了两个局部变量：

a: 使用FILTER函数根据当前零件编号n从范围C3:D19中筛选出对应的需求记录。

b: 使用SCAN函数对筛选出的需求量进行累计求和，起始值为0。

VSTACK 和 XLOOKUP 结合：这一部分的目标是找到并返回短缺日期。

DROP 函数：由于REDUCE的初始调用会产生一个不必要的空值（因为初始值是空字符串），DROP函数的第一个参数是前面REDUCE的结果，第二个参数1表示从结果数组中删除第一个元素，确保返回的结果只包含实际计算出的短缺日期。

效果如下图所示:

最后总结

通过上述详尽的步骤和公式应用，我们成功构建了一套自动化工具，该工具能够显著提升PMC部门在面对复杂零件需求计划时的响应速度和决策效率。不仅限于零件1，此方法通用性强，能为任何零件提供准确的库存短缺预警，确保生产计划的顺利执行。

我们从数据筛选出发，精确提取了特定零件的需求信息，随后通过累计需求计算与库存量的实时比对，精确诊断出潜在的库存缺口。利用逻辑判断与高级查找函数（如XLOOKUP），我们不仅定位了确切的缺货日期，还通过动态数组功能，实现了一键式批量处理不同零件的库存短缺分析，大大减轻了管理人员的工作负担，提高了整个供应链的响应敏捷性。

总结而言，此解决方案通过智能化手段优化了传统的人工审核流程，确保了生产计划与实际库存状况的紧密衔接。它不仅提升了库存管理的精细化水平，还为企业决策提供了即时、准确的数据支持，是向智能制造与数字化管理转型的重要实践。通过持续优化此类工具，企业能够在日益激烈的市场竞争中保持灵活高效的运营能力，为实现可持续发展奠定坚实的基础。

389 销售王者速查手册：WPS高级公式助力月末冠军统计

388 从新手到高手：小明的职场晋级之路——月销售汇总攻略

397 PMC一招搞定任务分配！如何用WPS智能识别多项目负责人？

396 告别繁琐手工整理：一键重塑采购报价单的自动化攻略

395 智能整理：一键自动化解决 PMC 客户信息混乱难题

394 仓库管理智慧升级：动态数组与高级公式引领高效新时代

393 小企业大智慧：用WPS表格新神器，让仓储管理效率飙升300%

391 万条记录也能秒算！揭秘小型企业仓储表格的极致优化之路

390 神奇公式，一键解决！WPS表格如何助力仓管实现商品编号自动化

389 告别手动时代：WPS自动化电子表格助力小微企精准计算物料需求

388 PMC生产计划排程革命：高效解锁数据的秘密，迈向一维分析新时代

387 自动化WPS表格助力PMC：高效追踪7日销售绩效