255 WPS新函数案例：复杂的产品欠料运算
2024年03月优秀创作者
2023年最后一天，接到一个工厂生产计划的需求，他们工厂的欠料非常难以计算，因为工厂规模比较小，没有上信息化，所以的物料需求及欠料都是人工运算，今天找到古哥咨询如何快速计算产品的欠料。
需求：表1是需要装配的一些产品，有装配顺序，现在需要根据这些装配的品的零件（表2物料清单）以及零件对应的库存（表3）计算出实际可装配的数量，如果不能装配的话，装配的零件缺口的名称是哪些？装配零件缺口的数量又是多少？
因为总成中的零件有很多是使用相同零件的，实际装配数量中需要考虑按顺序配置对应的零件装配数量体现。比方说装配顺序1号使用的零件500件，但是到装配2号后可能实际零件不足够使用400件的零件，只能够装配300件，缺口100件的装配零件。能够体现到缺口名称和对应缺口数量中。
所以需求通俗点就是告诉生产计划，哪些可以生产，哪些不可以生产，不可以生的欠什么东西？欠多少？
图1
表2是这些装配产品的物料清单，看到这样的物料清单（BOM),也是非常痛苦，这也是中小工厂的一些特点，基础表单不规范，后续做数据分析整理的时候难点重重。
图2
表3是零件的库存，这个零件的库存还算正常，是一个标准的一维数据表。
图3
整理BOM表
表2是零件的物料清单，也就是BOM表，如果希望表1生产计划表和表2的物料清单进行联动，以现在的BOM表是无法联动的，所以需要对现有的BOM表进行整理。整理成一个标准的一维表。
插入辅助列，命名为总成名称，录入函数：
B3=IF(C3="","",IF(C2="零件名称",D2,B2)),下拉填充
函数释义：
这一步的目的是让总成名称放在BOM表的左边，以形成与表1的联动，带出订单需求。
如果单元格C3 为空，则返回空值（""）。
如果单元格C2 的值等于 "零件名称"，则返回 D2：38281040190。
如果单元格C2 的值不等于 "零件名称"，则返回 B2："总成名称"。
这样根据相对引用的原理，下拉填充就把总成名称一键填充到零件的右边了。
根据这个总成，把表1的排程顺序引用过来，录入函数：
=IF(B3="","",XLOOKUP(B3,计划下达!B:B,计划下达!A:A,0))
再通过零件把库存引用过来：
=IF(B3="","",XLOOKUP(D3,库存数量!C:C,库存数量!D:D,0))
这样BOM表就整理好了
最终效果下图4所示：
图4
整理数据
在运算欠料明需要把表2中总成名称对应的零件物料清单的数据进行二次整理，也就是两个约束：
约束1：零件代码一样的放在一起
约束2：相同零件代码中，按排程顺序升序排序，也就是上面需求所说，优先扣减排序在前的总成。
根据这两个约束录入函数：
=SORT(SORT(FILTER(B3:F5000,(B3:B5000<>"")*(D3:D5000<>"-")),5),3)
函数释义：
筛选B列不为空和D列不等于符号“-”的数据，显示的结果是B到F列，再根据这个结果进行排序，第一次排序为第5列，排序方式升序，也就是排程顺序，第二次排序为第3列，也就是零件，排序方式也是升序。两次排序后就相当于上面的两个约束。效果如下图5所示：
图5
上面这个公式是一个大的动态数据，为了方便后续二次写函数，配合CHOOSECOLS来分成一个个单列的动态数组，依次把参数从1录入到5，5个公式：
=CHOOSECOLS(SORT(SORT(FILTER(B3:F5000,(B3:B5000<>"")*(D3:D5000<>"-")),5),3),1)
=CHOOSECOLS(SORT(SORT(FILTER(B3:F5000,(B3:B5000<>"")*(D3:D5000<>"-")),5),3),2)
…………
判断欠料
判断欠料的大逻辑就是库存减去需求，减去需求的同时考虑扣减顺序，如库存400，第一个需求是700，运算后是-300，第二个需求是300，累计欠料就是-600，单个需求就是-300。分别录入以下函数:
订单需求：
=XLOOKUP(I3#,计划下达!B4:B12,计划下达!C4:C12)，需要装配计划的总成数量。
累计需求：
=SUMIFS($M$3:M3,$K$3:K3,K3)，这个是零件对应的累计需求，如果有通用件的话：A和B订单共用C零件， C对应A的需求是200,B的需求是300，累计需求就是500。
累计欠料：
=IF(L3-N3>0,0,L3-N3)，用库存送去累计需求，大于0，显示为0；
单张欠料：
=IF(ABS(O3)<L3,O3,-M3)，按每个独立需求来判断欠料，而不是累计；
实际可装配：
=IF(COUNTIFS($P$3:$P$60,"<0",$H$3#,H3)>0,0,M3)，一个总成必须没有欠料，也就是没为负数的欠料，才能装配，如果没有欠料，返回M3，M3就是总成需求。最终完成的效果如下图6所示：
图6
引用欠料
表1是生产计划排程的主表，这张表上可以把上面根据物料清单计算出的欠料信息信息过来。
实际能够装配数量
=XLOOKUP(B4:B12,零件装配表!I:I,零件装配表!Q:Q)
装配零件缺口名称：
=TEXTJOIN(",",,FILTER(零件装配表!$K$3#,(零件装配表!$I$3#=B4)*(零件装配表!$P$3:$P$60<0)))
装配零件缺口数量：
=TEXTJOIN(",",,FILTER(零件装配表!$P$3:$P$60,(零件装配表!$I$3#=B4)*(零件装配表!$P$3:$P$60<0)))
完成后如下图7所示：
图7
知识点
到了上面这步已经把表1的排程零件对应的欠料信息一一计算出来了，本次的需求核心知识点：
知识点1：筛选IF的相对引用方式，把不规则的数据转换成标准的一维数据；
知识点2：用筛选函数把实际数据包含空行和特殊符号的数据去空。
知识点3：用排序函数把同类型的数据排序一起了
知识点4：利用累计库存减去累计需求来判断是否欠料
知识点5：利用统计函数统计负数来判断物料是否齐套，从而判断可以生产的总成数据；
知识点6：利用筛选函数加合并函数把欠料零件名称和数量引用到表1
我是古哥：
从事制造行本业20年，在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化，柔性化计划运营管理专家，擅长通过企业流程优化规范，企业管理、导入计划运营提升企业效率；对提高企业准时交货率，降低企业库存，输出智能制造人才有丰富的经验。学习PMC生产计划，关注古哥计划！
2024年03月优秀创作者