38 生产计划中欠料表上的公式串联设计

在梳理完成欠料所需求的资源报表后，我们需要用Excel函数把以下图中的各类资源数据全部串联起来，需要实现的目标：

1. 欠料明细表：详细到品号和天的欠料；

2. 更新简单：公式一次设计好后，只需要更新“资源”报表即可一键同步；

01 生产任务领用明细表：

最核心的公式都在这张表上，因为这张表相当于把生产排程表中所需要生产的产品BOM物料清单展开了，所以这张表需要串联排程表中生产的产品需求时间。录入公式：J2 =VLOOKUP(A2,'01 生产计划'!A:D,4,0),这样当把最新的生产计划复制到此，需求日期就自动更新了；

02 对生产任务领用明细表进行数据排序：

很多系统导出的明细表是默认生产任务排序的，实际计算欠料的时候需要把数据进行排序，两个条件非常重要：

1. 按物料品号排序：这样就把相同物料品号的物料放在一起来，当有通用物料的时候再按条件2的排序，就可以按需求扣减了；

2. 按需求时间排序：这里的需求是生产排程需要的，按升序排序，也就是需求时间在前面的优先扣减；

在对数据进行多条件排序的函数是SORTBY，只需要录入函数=SORTBY(A2:J295,D2:D295,1,J2:J295,1)，就可以对原生产任务领用明细表进行排序，结果如下图，可以看出物料CG-001、CG-005是通用物料，其中CG-001是所排的五张任务全部通用；所以当这个物料的库存大于0且需求满足不了所有任务的时候，扣减顺序就显得特别重要了；

03 对仓库资源进行比对：

仓库的物料资源与物料需求资源进行比对，在比对前先用公式引用过来，录入公式V2 =VLOOKUP(O2,'03 仓库现存量'!A:B,2,0)，把3表的仓库现存量明细数据匹配过来，这里默认物料只放一个库位，所以可以用VLOOKUP，如果是一个物料放置多个库位，则需要用到SUMIFS函数；注意CG-001、CG-005，库存的结果都是一样的，但会显示多行；接下来就需要用需求和资源对比，计算出欠料；

04 计算累计欠料：

这里根据欠料原理需求减去现有资源，会发现有问题，只有在物料唯一的情况下才适用，如果是通用的话，就不适用了，如CG-001，WK-003 需求300，资源86，扣减后是214，WK-004，需求150，此时再用86去扣减就是错误了；所以需要用一个累计需求的辅助列来判断扣减；录入公式W2=SUMIFS($S$2:S2,$O$2:O2,O2),下拉填充可得下图：

有了累计需求后，再进行比对，两列相减，录入公式: =V2-W2,可以看到这个结果如下图，这个只能说明这个物料的累计欠料，不是单张欠料；

下图中的结果说明了：

1. 大于等于0代表不欠料；

2. 小于0代表欠料，而且是累计欠料，如WK-003 在9-15日欠214，在9-16，累计欠 364，以此类推；

05 计算单物料单工单欠料：

上图中只是把某个物料的累计欠料，计算出来了，现在还需要单物料单工单的欠料，所以我们需要继续设计公式：

Y2 =IF(X2>=0,0,IF(ABS(X2)

公式说明：大于等于0，返回0，表示不欠料了，否则的话，再用累计欠料的绝对值（ABS是求绝对值的函数）与未领料对比，小于的话，返回累计欠料，大于的话，就返回工单的预计领用量；

06 欠料判断：

最后就是欠料判断，我们录入函数Z2 =IF(Y2=0,"不欠","欠料"),就可以判断出欠料了，通过筛选可以清晰到看到欠料明细；已经实现了精细化管理，可以看到是欠什么料（品号级别），每张生产任务欠料多少（生产任务号）？哪天欠多少？（排程需求日期）。而且因为公式设计的全串联，只需要更新上图的几张表就可以全部自动判断。

07 信息补全：

欠料虽然出来了，但是还有很多信息需要再次补上，明日继续分享，如何串联采购、生产订单、以及分析结果；

我是古哥：

从事制造行业18年，在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化，柔性化计划运营管理专家，擅长通过企业流程优化规范，企业管理、导入计划运营提升企业效率；对提高企业准时交货率，降低企业库存，输出智能制造人才有丰富的经验。学习PMC生产计划，关注古哥计划！23年古哥特训全程班开始预报名招生，23年和古哥一起全方位学习计划运营知识