EXCEL公式解决物料齐套方案与实例(含EXCEL公式,三个表)

EXCEL组合函数快速解决物料齐套欠料计算,,,,,,

物料齐套性欠料解决是非常复杂的问题，供应链管理中，我们除了用ERP系统之外，还有没有其它办法？,,,,,,

答案当然是肯定的，以下我们可以通过EXCEL组合与嵌套函数来实现。解决齐套，其根本的要求其实就是要把下阶物料的库存快速地分配到对应的成品料号中，并同时快速地计算分配后仍然差异的数量即可。‍,,,,,,

所以，下阶物料库存数如何依据BOM表架构分配给对应不同的成品料？可依如下步骤得可实现。‍,,,,,,

NO.1.建立下阶物料库存表，如下附表1所示：,,,,,,

NO.,下阶物料,下阶物料库存,,,,

1,B00001,150,,,,

2,B00002,200,,,,

3,B00003,666,,,,

4,B00004,6000,,,,

5,B00005,6000,,,,

6,B00006,6000,,,,

7,B00007,6000,,,,

8,B00008,6000,,,,

9,B00009,6000,,,,

10,B00010,6000,,,,

11,B00011,6000,,,,

12,B00012,6000,,,,

13,B00013,6000,,,,

14,B00014,6000,,,,

15,B00015,6000,,,,

16,B00016,6000,,,,

"NO.2.建立另一张表，也就是计算齐套欠料的主表：

表内容包括：（成品料号+成品需求+下阶物料号+比率用量+分配库存量+欠料），注意务必要把优先的生产料号按先后顺序依次排列，如下附表2：",,,,,,

NO.,成品料号,成品需求,下阶物料,比率用量,分配库存量,欠量

1,C00001,100,B00001,1,,

2,C00002,25,B00002,2,,

3,C00003,30,B00003,1,,

4,C00004,35,B00001,3,,

5,C00005,40,B00002,1,,

6,C00006,45,B00002,2,,

7,C00007,50,B00002,1,,

8,C00008,55,B00001,3,,

9,C00009,60,B00003,1,,

10,C00010,65,B00001,2,,

11,C00011,70,B00001,1,,

12,C00012,75,B00003,1,,

13,C00013,80,B00001,1,,

14,C00014,85,B00001,5,,

15,C00015,90,B00002,1,,

16,C00016,95,B00003,1,,

NO.3.也就是关键一步，如何做到快速把库存分配到对应的成品，须在分配库存列与欠料列输入公式：,,,,,,

"=MIN(C42*E42,VLOOKUP(D42,库存清单!$B:$C,2,0)-SUMIF(D$1:D41,D42,F$1:F41))；这里关键在于用好公式MIN，VLOOKUP,SUMIF等EXCEL组合函数；可参照附表3：",,,,,,

NO.,成品料号,成品需求,下阶物料,比率用量,分配库存量,欠量

1,C00001,100,B00001,1,100,

2,C00002,25,B00002,2,50,

3,C00003,30,B00003,1,30,

4,C00004,35,B00001,3,50,

5,C00005,40,B00002,1,40,

6,C00006,45,B00002,2,90,

7,C00007,50,B00002,1,20,

8,C00008,55,B00001,3,0,

9,C00009,60,B00003,1,60,

10,C00010,65,B00001,2,0,

11,C00011,70,B00001,1,0,

12,C00012,75,B00003,1,75,

13,C00013,80,B00001,1,0,

14,C00014,85,B00001,5,0,

15,C00015,90,B00002,1,0,

16,C00016,95,B00003,1,95,

NO.4.分配完成以后，如何显示是否欠料？欠多少数量？则须在欠量列输入公式：,,,,,,

"IF((F42-C42*E42)=0,"""",""欠""&C42*E42-F42&""PCS"")，这里关键在于用好公式IF与&等功能，如下附表4；",,,,,,

NO.,成品料号,成品需求,下阶物料,比率用量,分配库存量,欠量

1,C00001,100,B00001,1,100,

2,C00002,25,B00002,2,50,

3,C00003,30,B00003,1,30,

4,C00004,35,B00001,3,50,还欠55PCS

5,C00005,40,B00002,1,40,

6,C00006,45,B00002,2,90,

7,C00007,50,B00002,1,20,欠30PCS

8,C00008,55,B00001,3,0,欠165PCS

9,C00009,60,B00003,1,60,

10,C00010,65,B00001,2,0,欠130PCS

11,C00011,70,B00001,1,0,欠70PCS

12,C00012,75,B00003,1,75,

13,C00013,80,B00001,1,0,欠80PCS

14,C00014,85,B00001,5,0,欠425PCS

15,C00015,90,B00002,1,0,欠90PCS

16,C00016,95,B00003,1,95,

"以上简单的四个步骤最关键大于后面两个步骤，用好则可以快速实现