440欠料运算精进:XLOOKUP与BOM清单的智能协同

职场计划有古哥 2024-07-05 17:12:45

全文约1700字;

阅读时间:约4分钟;

听完时间:约8分钟;

回顾昨日的讨论,我们意识到在使用XLOOKUP函数时,当同一组件被多款产品共享,直接从库存中扣除其总需求量,易导致库存量被重复计算,引起计算偏差。

此状况在产品品种繁多时尤为常见。有效的解决方法是,首先应用BOM清单对产品进行细致拆解。之后,依据清单上的用量准确计算出各组件的实际需求。关键步骤在于,需对组件需求进行汇总,避免重复计算。汇总后的总需求应与现有库存量进行比对,以判断库存是否充足,进而识别是否存在物料短缺问题。

引用需求

首要任务是基于产品需求细化BOM(物料清单)需求。为此,应在产品BOM表的旁边增设一列,于该列顶部标注“组件需求”。接着,在该列中应用以下动态数组公式:

=XLOOKUP(E4:E10,B4:B6,C4:C6,0)*G4:G10

公式解释:

查找值:E列包含了BOM清单中组件的编码;

查找数组:B列列举了客户所订购产品的编码;

返回数组:C列记录了客户订购的数量;

需求运算:订单数量乘以G列各产品对组件的用量。。

得到组件需求,效果如下图所示:

汇总需求:

接下来的步骤是汇总组件需求,但在汇总之前,必须先剔除组件列表中的重复项。为此,在适当的位置输入以下公式:

=UNIQUE(F4:F10)

将结果定义为J4#,这将成为后续条件求和函数的筛选条件。

随后,在相邻的单元格中输入汇总公式:

=SUMIFS(H4#,F4:F10,J4#)

公式解释:

指定的求和区域为H4#,即表示“组件需求”的列。

设定的条件区域为F4:F10,这里是包含重复项的“组件”列。

求和条件设置为J4#,这是经由UNIQUE函数处理后,剔除了重复项的组件列表。

计算欠料

汇总后的组件欠料计算遵循与昨日相同的逻辑,即通过库存量减去需求量,以此判断是否出现欠料情况。应用以下动态数组公式实现这一计算:

L4

=XLOOKUP(J4#,O4:O8,P4:P8,0)-K4#

M4

=IF(L4#<0,"欠料","不欠")

得到下图结果:

公式解释:

L4 #单元格中的公式用于计算欠料量。其中,XLOOKUP(J4#, O4:O8, P4:P8, 0) 用于查找与J4#(即组件名称)匹配的库存量,K4# 则代表汇总后的组件需求量。通过将库存量减去需求量,得出的结果即为欠料量。

M4 单元格中的公式用于判断欠料状态。IF(L4# < 0, "欠料", "不欠") 这一条件语句检查L4#(即欠料量)是否小于零。如果欠料量为负数,表明存在欠料情况,公式将返回“欠料”;反之,若欠料量非负,则表示库存充足,公式返回“不欠”。

一键计算

对于追求极致的用户,可以参考以下一键运算函数,供大家学习参考,实际情况还是用辅助列的方法,这样的方法能够细致的显示每一步的逻辑运算步骤。追求自动化的可以考虑一键:

=LET(C,LET(B,LET(A,F4:F10,F,UNIQUE(A),HSTACK(F,BYROW((F=TOROW(A))*TOROW(XLOOKUP(E4:E10,B4:B6,C4:C6,0)*G4:G10),SUM))),HSTACK(INDEX(B,,1),XLOOKUP(INDEX(B,,1),M4:M8,N4:N8,0)-INDEX(B,,2))),HSTACK(C,IF(INDEX(C,,2)<0,"欠料","不欠")))

初始化变量A: 将F4:F10列作为初始数据集,用于后续运算。

创建唯一值列表F: 使用UNIQUE函数从A中提取唯一组件名称,存储在变量F中。

计算组件需求: 利用BYROW函数遍历F中的每个组件,结合XLOOKUP函数,计算每个组件对应的产品需求量,将结果与F并列组合。

汇总库存与需求差异: 利用HSTACK函数,将步骤3中得到的组件列表与通过XLOOKUP函数计算得到的库存量差值组合在一起。

判断欠料状态: 最后,使用IF函数检查汇总后的库存与需求差值是否小于零,从而判断是否存在欠料情况。

此一键式公式通过嵌套多个函数,实现了从原始数据到最终欠料状态判断的自动化处理,极大地简化了工作流程。然而,由于其复杂度较高,对于需要逐步验证或理解计算逻辑的用户,使用辅助列的方法可能更为直观和易于理解。

最后总结

回顾本次讨论,我们深入探讨了在处理多产品、多组件的复杂供应链管理时,如何利用WPS的高级功能,特别是XLOOKUP函数,来精准计算组件需求和欠料情况。从最初认识到直接从库存中扣除需求可能导致的计算偏差,到逐步构建出一套完整的解决方案,我们不仅细化了BOM清单,还通过动态数组公式准确计算了各组件的需求量。

在解决了需求计算的基础问题后,我们引入了UNIQUE函数来消除重复项,确保需求汇总的准确性。通过SUMIFS函数,我们能够对组件需求进行高效汇总,为后续的库存对比和欠料判断奠定了坚实的基础。

欠料计算阶段,我们应用了XLOOKUP函数和IF条件语句,实现了库存与需求的精确对比,快速识别欠料状态,为库存管理和采购决策提供了有力支持。

对于追求效率与自动化的用户,我们还分享了一键式计算公式,虽然其结构复杂,但通过嵌套多个函数,实现了从数据处理到欠料判断的无缝自动化,极大提升了工作效率。不过,我们也强调了辅助列方法的重要性,它不仅便于跟踪每一步运算逻辑,而且在需要逐步验证或理解复杂计算流程时,显得尤为直观和实用。

0 阅读:0

职场计划有古哥

简介:感谢大家的关注