43 Excel物料单阶需求计算方法

好多中小工厂在没有上信息化之前都是用Excel计算物料需求，在Excel中计算物料需求的话，也是遵循大原则，就是用产品的BOM（物料清单）乘以需求数量，再合并物料需求。

产品的BOM一般情况下看层级，多层的计算与单层的计算方法不一样，取决于BOM的定额是如何设计的，一般的产品如果自制件比较多的话，产品的层级会比较多。运算相对复杂一点。

今天先分享比较简单的，单阶BOM，就是0层和1层的需求，0层为成品，1层为原料需求；计算需求前准备好以下资料：

1. 产品排程表，也就是物料需求表；

2. 所排产品的物料清单，需要详细标明定额；

01 建立对应模板：

 物料的需求运算最好建立一个模板，这样下次计算欠料就非常方便了，整体思路是：

1. 表1：订单排程表，把排程表复制到这里，目的就是快速分解；

2. 表2：所有产品的BOM，因为排程表的产品是变量，不确定排程哪个产品，所以先把所有的BOM一起放进去；

3. 表3：订单分解BOM表，此表就是把需要排程的产品自动分解BOM，并匹配对应排程的需求表；

4. 表4：物料需求明细表：这张表就是把物料去重复后汇总计算的物料明细表；

以上4张表就是一个简单的毛需求计算表，对于毛需求的定义：为了完成某成品所需求的总的数量。暂时不考虑净需求，因为很多中小工厂的库存不准，考虑净需求意义不大；

02 订单分解模板：

在表1和表2都创建好了后，核心的报表就是订单分解BOM表，就是当把排程放到表1的时候，需要自动分解出指定产品的BOM清单，这里的核心还是“一对多的问题”我们按以下方法来解决：

第一步：判断01排程表中产品在02表中有没有BOM，没有BOM的话就会出错误，录入公式：=VLOOKUP(B2,'02产品BOM表'!A:A,1,0)，返回结果都是正确的，没有找不到的错误提示，证明都有BOM；

第二步：在02的产品BOM表中同样去找01表中的排程表，如果找到了就是有排程的，没有找到的就是没有排程的，利用这个方法可以快速的把有排程和没排程的产品BOM区分出来；

D2=IFERROR(VLOOKUP(A2,'01排程表'!B:B,1,0),"")

第三步：排程订单分解BOM，用筛选函数把刚才02表产品BOM中不为空的筛选出来，就得到所排产品的订单分解BOM; 此函数为动态数组函数。

A2 =FILTER('02产品BOM表'!A2:C52,'02产品BOM表'!D2:D52"")

公式的意思为把02产品BOM表中不等空的筛选出来；

第四步：计算需求，公式为排程数量乘以定额，这里注意VLOOKUP函数的引用关系，需要锁定对应的列；

D2=VLOOKUP($A2,'01排程表'!$B:$H,COLUMN(C1),0)*$C2

公式说明：利用VLOOKUP的批量引用把排程表中产品的排程数量引用过来，再乘以用量，就得到了零件的每日需求表；

第五步：计算零件的汇总需求用量，在表4把零件这一列删除重复项目，再结果汇总函数就可以得出；

A2 =UNIQUE('03订单分解BOM表'!B2:B2000)

动态数组函数,对03表中的零件删除重复项目；范围可以选择大一点，这样后面产品排程多的话也不怕了；

B2 =SUMIFS('03订单分解BOM表'!D:D,'03订单分解BOM表'!$B:$B,$A2)

录入汇总求和函数后，就得到了物料需求明细表；

03 模板总结：

模板设计的整体思路是只需要更新01表和02表，实现03表和04表全自动生成；

平时用这个模板的时候可以从PC中要到排程表，放到01表中，再从技术部门拿到产品的BOM表，放在02表中，就可以实现一键计算出物料需求；

我是古哥：

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