求助 欠料计算

陈尔东

求助PMC 物控人员自制缺料计算表

hxj119

请上传附件！！！

陈尔东

hxj119 发表于 2024-5-9 12:20
请上传附件！！！

已添加附件， 请各位英雄好汉指点

Excel表哥在此

陈尔东 发表于 2024-5-12 10:29
已添加附件， 请各位英雄好汉指点

难度源于你的表格（不规范）！而不是问题本身——可以，或者说如果，内容不变，轻轻改动一下计录表，问题立马就变得简单了，供你参考吧
每日每品种材料需要量/耗用量=SUMPRODUCT(FILTER(),FILTER())
（欠料，减一下就好了）

陈尔东

Excel表哥在此 发表于 2024-5-13 10:57
难度源于你的表格（不规范）！而不是问题本身——可以，或者说如果，内容不变，轻轻改动一下计录表，问题 ...

你这个方法也行，但是正常排产的话都是按照我发布的那个版本 ，你这个等于还要切换，不是很直观

ggw092

想要一步计算VBA，函数计算分步，把排程表转成一维，FILTER REDUCE堆叠，库存判断欠料，再转二维。

ggw092

用函数就是苦力活

ggw092

全文约3300字；

阅读时间：约10分钟；

听完时间：约18分钟；

MC物料控制员在接到PC计划员制定的MPS成品主生产计划后，必须依据各产品的BOM（物料清单）详细信息，迅速计算出相应零件的缺料情况。这一过程需基于BOM明细精确进行，随后，依据缺料详情立即采取跟进措施。缺料的统计需达到极高的精确度，具体到每份工单以及每一天的需求量。例如，若成品A在5月18日和5月19日的计划生产量分别为400件和600件，则应单独基于这两个数字计算出对应日期下各零件的缺料明细。

一般而言，类似缺料细节的计算工作，若采用先进的信息化工具如APS（高级计划与排程系统），能够迅速完成。但考虑到APS系统的普及程度有限，许多工厂目前仍依赖手动操作电子表格来计算缺料，这凸显了设计一份全自动化的缺料需求表格的重要性。

设计这样一份“全自动”欠料表格，事先必须准备齐全的相关资料，基本要求涵盖以下几点：

• 最新版WPS电子表格软件，需兼容高级函数，如REDUCE、LAMBDA等；
• PC计划员编制的主生产计划（MPS）；
• 来自工艺部门的完整父子件物料清单（BOM）；
• 针对各零件的当前库存详情表，以供MRP（物料需求计划）运算之用。
  

资料准备：

创建一个新的表格，并将其命名为《全自动欠料运算报表》。在该报表中，设立三个不同的工作表。首先，将第一个工作表命名为“1.MPS”，用于记录PC计划员的主生产计划。此计划采用行业标准的“二维排程”格式展示，即表格的垂直轴代表订单，水平轴代表日期，每个单元格则表示特定日期下对应订单的数量，其布局示例如下图所示。

第二个工作表命名为“2.BOM”，用于汇总工厂所有产品的BOM（物料清单）详细信息。此表采用一维结构展现父子件关系，具体为：B列标识成品项目，C列列出与之相应的子件，D列则标明每个子件的使用数量。其布局样式参见下图。

第三个表格命名为“3.库存”，采用一维表格式，专门用来登记零件的现有库存量。该表中，B列记录零件编码，确保每项编码唯一不重复；C列则显示相应的库存数量。表格布局实例请见下图。

设计思路

上述三张基础表格构成了MC物料控制员需持续维护的动态数据库，要求随PC计划员对MPS的调整、工艺员对BOM的更新，或是库存状况的实时变动而即时同步更新。实践中，若BOM相对稳定，则主要关注MPS和库存信息的刷新即可。

接下来，第4个工作表被设计为“运算步骤表”，第5个工作表则是“分析表”。这两张表需充分融入前三个表格数据的实时变动，在构建公式与逻辑时，需预留充足的数据处理容量，以灵活适应未来数据的动态增加与变化。

建立引用

创建一个新的工作表，命名为“4.运算”。在开始运算之前，需迅速从表1至表3中引入所需数据，并为每部分数据预设合适的引用范围。首先，将表1中的二维MPS数据引入，为此预留了8000行的垂直空间，水平方向则保持不变。应用以下公式建立数据间的引用关系：

在B4单元格输入：=TOCOL('1.MPS'!B4:B8000,3)

在C4单元格输入：=TOCOL('1.MPS'!C4:C8000,3)

在D4单元格输入：=TOCOL('1.MPS'!D4:D8000,3)

将E3单元格设定为：= '1.MPS'!E3:P3

E4单元格则使用公式：

=CHOOSEROWS('1.MPS'!E4:P8000,SEQUENCE(ROWS(D4#)))

这些公式分别对应并导入订单编号、成品名称、数量、日期列，以及每天对应订单的具体数量。最终呈现的效果如图所示，实现了数据的有效整合与排列。

接着，以相同的方式将表2的BOM数据和表3的库存信息引入。考虑到BOM表和库存表可能包含大量数据，特地为BOM预留了50000行，库存预留了20000行的空间。通过以下函数实现数据的导入，分别对应BOM表中的“成品编码、子件编码、用量”及库存表中的“物料编码、库存量”：

R4单元格设置公式：=TOCOL('2.BOM'!B4:B50000,3)

S4单元格的公式为：=TOCOL('2.BOM'!C4:C50000,3)

T4单元格则使用：=TOCOL('2.BOM'!D4:D50000,3)

V4单元格的公式是：=TOCOL('3.库存'!B4:B20000,3)

W4单元格设置为：=TOCOL('3.库存'!C4:C20000,3)

如此一来，这些数据便被有效地整合到了新表中，其布局与预期效果相符，如下图所示。

二维转一维

为了便于进行MRP物料需求计算，鉴于二维表在处理此类计算时不够直观便捷，我们需要首先将MPS数据转换成一维表格式。采用以下公式可以高效地实现从二维到一维的转换，构建出适合运算的表格结构：

Z4=TOCOL(IF(E4#>0,B4#,A),3)

AA4=TOCOL(IF(E4#>0,C4#,A),3)

AB4=TOCOL(IF(E4#>0,E4#,A),3)

AC4=TOCOL(IF(E4#>0,E3#,A),3)

通过这些公式，MPS数据被重新组织，形成了一维表的形式，更加利于后续的物料需求计算。转换后的效果如图所示，清晰展示了订单号、成品、数量及对应日期的信息排列。

ggw092

毛需求计算

在完成一维MPS表的构建并结合BOM表后，接下来可以着手进行毛需求的计算。计算方法基于利用FILTER函数从BOM表中筛选出与成品相对应的零件明细，再根据成品的排程数量累加各零件的需求量。首先，在BOM表旁边新增一列作为辅助，用于计算每个成品对应的零件数量，应用以下公式达成这一目的：

在U4单元格填入公式：=COUNTIFS(R4#,R4#)

通过这个公式，我们可以清晰识别每个成品所含零件的数量，例如结果显示产品A包含8个不同零件，产品B则有10个零件。这种设置为后续的精确计算奠定了基础，其展现效果如图所示，直观地反映了成品与零件数量的对应关系。

随后，我们进入复杂的堆叠与计算阶段，旨在整合信息并得出所需结果：

零件数：在AD4单元格，利用XLOOKUP函数从BOM表中匹配成品编码并返回对应的零件数量，公式为：=XLOOKUP(AA4#,R4#,U4#)。这一数据是进行后续堆叠计算的基础。

成品编码、物料编码、用量的提取分别通过以下高级公式实现多对一匹配与堆叠：

成品编码（AF4）：

=DROP(REDUCE("",AA4#,LAMBDA(X,Y,VSTACK(X,FILTER(R4#,R4#=Y)))),1)

物料编码（AG4）：

=DROP(REDUCE("",AA4#,LAMBDA(X,Y,VSTACK(X,FILTER(S4#,R4#=Y)))),1)

用量（AH4）：

=DROP(REDUCE("",AA4#,LAMBDA(X,Y,VSTACK(X,FILTER(T4#,R4#=Y)))),1)

对于订单号、数量、日期的处理，我们通过文本操作和重复来与零件用量匹配：

订单号（AI4）：=TEXTSPLIT(CONCAT(REPT(Z4#&"#",AD4#)),,"#",2)

数量（AJ4）：=TEXTSPLIT(CONCAT(REPT(AB4#&"#",AD4#)),,"#",2)

日期（AK4）：=--TEXTSPLIT(CONCAT(REPT(AC4#&"#",AD4#)),,"#",2)

最后，基于上述准备，计算毛需求，在AL4单元格使用公式：=AJ4#*AH4#，实现每个零件的总需求量计算。

经过这一系列的公式应用与数据处理，最终得到的结果将清晰展示每个零件的毛需求量，其视觉效果直观地呈现在图表中。

数据排序

在完成毛需求的计算之后，紧接着进入欠料运算的准备阶段。为了便于欠料计算，我们首先需要整合毛需求的数据，并对子件（物料编码）进行排序，随后再将排序后的数据拆分。具体操作如下：

物料编码（子件）: 在AO4单元格，利用CHOOSECOLS和SORT函数对合并后的数据按物料编码排序并提取第一列，公式为：

AO4=CHOOSECOLS(SORT(HSTACK(AG4#,AL4#,AK4#)),1)

毛需求: 同样基于排序后的数据集，在AP4单元格提取第二列作为毛需求量，公式为：

AP4=CHOOSECOLS(SORT(HSTACK(AG4#,AL4#,AK4#)),2)

日期: 最后，在AQ4单元格提取排序后数据的第三列，即日期信息，公式为：

AQ4=CHOOSECOLS(SORT(HSTACK(AG4#,AL4#,AK4#)),3)

经过这样的处理步骤，不仅完成了数据的排序，还为后续的欠料计算提供了清晰且有序的数据基础。整理后的数据布局如图所示，清晰展示了物料编码、对应的毛需求量及日期，为欠料分析创造了便利条件。

欠料运算

在进行数据排序后，先把库存引用过来，再用库存减去累计需求，通过累计需求后的运算结果来判断欠料

库存：

=XLOOKUP(AO4#,W4#,X4#)

累计需求：

=SCAN(0,AO4#,LAMBDA(X,Y,LET(A,OFFSET(Y,,1),IF(Y=OFFSET(Y,-1,),X+A,A))))

欠料：

=LET(A,IF(AR4#-AS4#>=0,0,AR4#-AS4#),IF(ABS(A)<AP4#,A,-AP4#))

效果如下图：

一维转二维

至此，我们已获得初步的欠料数据。最后阶段，我们将把一维的欠料信息转换为二维格式，以便实现按天精确追踪每种物料的欠料明细。通过运用数据透视功能的聚合公式，可以达成这一目标：

在合适的位置输入公式

=PIVOTBY(AO4#, AQ4#, AT4#, SUM)

此公式的作用是基于物料编码（AO列）和日期（AQ列），对欠料数量（AT列）进行汇总，从而生成一个二维的欠料明细表，确保了每一天每种子件的欠料量都清晰可见。应用该公式后，得到的表格效果直观展示了期望的欠料数据分布，每行代表一个特定日期，每列对应一种物料编码，单元格内的数值则准确反映了该日期下该物料的欠料总量，正如下图所示。

最后总结：

综上所述，通过一系列精细的操作与公式设计，我们成功构建了一个高度自动化的欠料运算体系，它不仅整合了MPS计划、BOM清单及库存信息，还实现了从数据导入、处理到最终欠料分析的全链条自动化。此系统的核心优势在于其灵活性和准确性，能够随着生产计划和库存状态的变化实时更新，确保物料控制员能迅速识别并应对潜在的供应短缺问题。

该自动化表格的设计，充分展现了现代信息技术在优化传统制造业流程中的力量，特别是利用高级函数简化复杂运算，显著提升了工作效率，减少了人工错误。从二维到一维的数据转换策略巧妙地克服了传统表格处理复杂需求计算的局限性，而最终通过数据透视功能回归的二维欠料明细表，则完美符合了实际管理中对数据可视性和可操作性的高要求。

总之，这份“全自动欠料运算报表”的开发，标志着向智能化物料管理迈出的重要一步。它不仅解决了即时缺料计算的难题，更为企业的生产决策提供了坚实的数据支撑，助力实现精细化管理和高效运作，是迈向智能制造不可或缺的工具之一。随着系统持续迭代与优化，其在提升供应链响应速度、降低成本及增强市场竞争力方面展现出巨大潜力，为企业在激烈的市场竞争中赢得先机。