全文约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.操作”。开始操作前，需要快速导入表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保留5万行，库存保留2万行。分别对应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.Inventory'!B4:B20000,3)
单元格 W4 设置为：=TOCOL('3.Inventory'!C4:C20000,3)
这样，数据就会有效地合并到具有预期布局的新表中，如下图所示。
2D 到 1D
为了方便计算MRP物料需求，我们需要先将MPS数据转换成一维表格形式，因为二维表格在处理此类计算时并不直观和方便。可以使用以下公式高效地实现二维到一维的转换，并构建适合计算的表格结构：
Z4=TOCOL(如果(E4#>0,B4#,A),3)
AA4=TOCOL(如果(E4#>0,C4#,A),3)
AB4=TOCOL(如果(E4#>0,E4#,A),3)
AC4=TOCOL(如果(E4#>0,E3#,A),3)
通过这些公式，将MPS数据重新整理成一维表格，更利于后续物料需求计算，转换后的效果如图所示，清晰的显示出订单号、成品、数量以及对应的日期信息的排列。
总需求计算
一维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)pivot ttemplathow to 沙岩堂神豪：以末世战神身份签到开局，最强护国龙帅汽水很好喝，第一朵花蕊
