Hello，大家好！今天，EK将通过客户给定的订单，告诉大家如何生成生产需求和物料需求。当然，因为每个客户给定的数据格式不一样，可能会有不同的方法，但是总体来说思路还是一致的。
这里先给大家看一下完成的效果图。可以看到我们，可以通过需求周数的变动可以获取对应的需求。包括，产品需求、包材、原料和工时。
图1 效果图
首先来看一下，客户给定的订单。这里EK将里面会涉及到隐私的内容全部替换过了。如下图所示：
图2 周需求
这里给大家还准备了两个表。
零件信息
图3 零件信息
BOM
图4 BOM
大家也可以按照EK的方法，使用自己的演示数据。
以上为我们所需的数据源。下面我们来进行数据的处理。
1、新增一个工作表-“汇总信息”，如下图所示。
图5 汇总信息
2、对“汇总信息”的L1：Q1单元格进行数据验证
图6 数据验证
这里是给动态生成需求制作扳机。
3、在“辅助表”中I1单元格中填入汇总，J1和O1对应引用“汇总信息”表中的L1：Q1单元格。如下图
图7 数据引用
在I2中写入公式：=SUM(J2:O2)
在J2中写入公式：=IFERROR(VLOOKUP($B2,周需求!$D:$Z,MATCH(辅助表!J$1,周需求!$D$2:$Z$2,0),0),0)
在对单元格进行自动填充。如下图：
图8 生成需求
这里，EK为大家简单分析一下公式J2的公式：
MATCH(辅助表!J$1,周需求!$D$2:$Z$2,0) 代表的是在“周需求”表中D2：Z2单元格中等于J1单元格时的列数（是从D列开始算的），$是为了公式的重用。
VLOOKUP($B2,周需求!$D:$Z,MATCH(辅助表!J$1,周需求!$D$2:$Z$2,0),0) 代表的在“周需求”中的D：Z单元格区域的首列查找“辅助表”中单元格B2的数据，并返回指定的数据所在行中的 MATCH(辅助表!J$1,周需求!$D$2:$Z$2,0) 列出的数据。
IFERROR（函数，0）为了防错。
4、在辅助表中的P1：Y1 添加标题如下图：
图9 添加标题
分别写入公式：
P2：=INDEX(BOM!$E:$E,MATCH(辅助表!$B2,BOM!$D:$D,0))
Q2：=INDEX(BOM!$G:$G,MATCH(辅助表!$B2,BOM!$D:$D,0))
R2：=Q2*$I2
S2：=OFFSET(INDEX(BOM!$E:$E,MATCH(辅助表!$B2,BOM!$D:$D,0)),1,0)
T2：=OFFSET(INDEX(BOM!$G:$G,MATCH(辅助表!$B2,BOM!$D:$D,0)),1,0)
U2：=T2*$I2
V2：=OFFSET(INDEX(BOM!$E:$E,MATCH(辅助表!$B2,BOM!$D:$D,0)),2,0)
W2：=OFFSET(INDEX(BOM!$G:$G,MATCH(辅助表!$B2,BOM!$D:$D,0)),2,0)
X2：=W2*$I2
Y2：=I2/H2
Z2：=I2*D2/1000
效果图如下：
图10 原料用量
5、“汇总信息”表中：A2：J2单元格添加公式
A2：=IFERROR(INDEX(辅助表!$A$2:$A$38,SMALL(IF(辅助表!$I$2:$I$38=0,"",ROW(辅助表!$A$2:$A$38)-ROW(辅助表!$A$2) 1),ROW(辅助表!$A2)-ROW(辅助表!$A$2) 1)),0)
公式为数组公式。需要利用CTRL ALT ENTER三键一起按
B2：=IFERROR(VLOOKUP($A2,辅助表!$A:$I,COLUMN(B:B),0),0)
C2：=IFERROR(VLOOKUP($A2,辅助表!$A:$I,COLUMN(C:C),0),0)
D2：=IFERROR(VLOOKUP($A2,辅助表!$A:$I,COLUMN(I:I),0),0)
然后向下填充，如下图：
图11 产品需求
这里，我们看到下面未匹配成功的数据显示为0.不是很好看。这里，我们可以把0 设置为空。如下图：
图12 优化零值显示
6、最后我们对数据表进行原料和包材需求的汇总。
图13 原料和包材需求的整合
分别写入公式：
L4：=SUMIF(辅助表!$G:$G,汇总信息!$K4,辅助表!Y:Y)
L11：=SUMIF(辅助表!E:E,汇总信息!$K11,辅助表!Z:Z)
M11:=L11/N11/60
L15：=SUMIF(辅助表!P:P,K15,辅助表!R:R) SUMIF(辅助表!S:S,K15,辅助表!U:U) SUMIF(辅助表!V:V,K15,辅助表!X:X)
M15：=VLOOKUP(K15,BOM!E:F,2,0)
效果图如下：
图14 完成图
完成以后，就可以在我们自己指定的时间点内进行需求分析了，可以获得产品、原料、工时和包材的需求，效果图见图1 。当然这是针对于特定的需求表。因为，客户的需求相对来说格式都是固定的。我们在处理完一次以后，我们可以重用这张表格。
最后，感谢小伙伴们的观看，我是EK。如果上述内容能够帮助到你们，希望你们能点赞，关注，评论，转发，你们不清楚的地方我会尽力为大家解答，谢谢大家的支持。我会给大家带来更多关于EXCEL的小技巧。
评论 (0)