Excel 2007算法：怎样从库存物料信息，推算出 生产订单需求每个料件的名称和数量？ 也就是推算缺料表？
1. 这是简单的原始物料和生产订单信息。
库存里总共有五种物料，M01,M02,... M05，库存数量在第四列。
生产订单有4个，PP01,PP02,... PP04，对每个物料的需求在第三列。
2. 目标是把每个生产订单缺料的信息列出来，填到如下图？处。
如果库存数量足够，例如M01有7个，4个订单总共需求4个，因此库房不缺料，缺口就是0；
但是M03库房共2个，而4个订单要4个，按顺序供应，订单PP01,02不缺，但是PP03,04就缺料，需要在PP03, PP04这两行，对应M03列显示各缺1个。
另外，像M05，共0个库存，每个订单都需要显示缺1个。
请问有什么算法可以根据库存和订单原始数据，推算出缺料信息？
要求能适用于至少1000个以上生产订单和10000以上个物料。这个例子数据量很少，仅用于举例，目标是为了寻求算法。
为了大家没有误解，我们定义原始数据为sheet1，缺料表为sheet2.
谢谢。 展开
1、将库存现有物料数据存入工作表3(sheet3)，A列为物料代号，B列为库房存量。
2、在sheet1的B2中输入公式：
=INDEX(Sheet2!$C:$C,MATCH($A3&"/"&B$2,Sheet2!$A:$A&"/"&Sheet2!$B:$B,0))-INDEX(Sheet3!$B:$B,MATCH(B$2,Sheet3!$A:$A,0))
以“Ctrl”+“Shift”+“Enter”三键结束公式输入；
3、在sheet1的B3中输入公式：
=INDEX(Sheet2!$C:$C,MATCH($A4&"/"&B$2,Sheet2!$A:$A&"/"&Sheet2!$B:$B,0))+B3
以“Ctrl”+“Shift”+“Enter”三键结束公式输入；
4、选中B3下拉至B6；
5、选中B2:B6，右拉至F列；
6、选中B2:F6 -》》条件格式 -》》条件一(新建规则/只为包含以下内容的单元格设置格式) -》》“单元格数值”--小于等于--0 -》》格式 -》》字体--颜色：选中“白色” -》》点击“确定”
说明：
1、以第二步公式为例：
MATCH($A3&"/"&B$2,Sheet2!$A:$A&"/"&Sheet2!$B:$B,0) 定位符合二个条件的行
即：$A3&"/"&B$2 构成二个条件； Sheet2!$A:$A&"/"&Sheet2!$B:$B 定位区域； 0 精确查找
以三键结束输入公式，形成数组公式
2、第三步，计算在PP01订单后的剩余库存基础上的缺料信息；
3、在未设置条件格式前，可见的负数是库存剩余可用量，通过条件格式的方式予以隐藏，提高表格的可阅读性。
4、第六步中，括号内的内容对应excel2007格式，无括号部分wps表格/excel2003通用。
此思路和公式，可使用于wps表格/excel，已验证。
图中，蓝色字体部分，通过条件格式隐藏，红色字体部分和其他框线标题等，为正常显示值
为了验算简单，我把每个订单对物料的需求设置为1.
根据验算每个订单对M05料件缺口都应该是1，但是这个算法出来，分别是-5,5,20,40,65,这个数据是不对的。
请再次看看，是不是有重复计算的地方？
如需要我把原始Excel表格发给您？
同样为了验算，我将订单数据设置为1-25递增，库存数据在你的数据基础上加10，获得此结果，可自行验算。
1、注意上面回答中说明的第2、3点，该数据是针对订单逐个减少库存的累计数据信息，即：对完成订单1的缺料信息，然后计算完成订单1后继续完成订单2的缺料信息，逐环相扣。
2、如需要针对每个订单单独判断，仅需直接将B3的公式下拉或右拉，跳过第二步即可。
一个物料库存表
一个产品物料单（bom）各种产品的单位产品所需配料清单
订单表
进出流水表
然后做关联设计、公式、透视表等
这个是4个订单，每个订单BOM就是M那几颗料。
哦，那你也可以直接用公式把它关联上吧
不过我觉得还是做套表比较直观灵活
另外，像这种情况下的应用，应该建一个all-in-one BOM比较好用一些。
我觉得对于简单的几个订单，这方法应该可以用。
但是对于1000个以上订单，10000以上的料件，就没法用了。
另外，实际库存中，可能有很多项目，有很多不同的BOM。
用这个办法工作量非常大。你觉得呢?
所以呢，要用ALL-IN-ONE BOM才行啊，并且物料多了以后，你公司要有ERP系统，可以从系统中自动更新库存数量甚至是ECN后的BOM，或者干脆请人做一个类似自动跑MPS的外挂即可。
我有点思路，但是需要验证。
我试试用VBA来解决这个问题
已经发送。多谢啦。
水平有限，我只能帮你把缺少物料的数据全部提取出来，至于制作成你需要的表格就只能手动用数据透视表来做了。
