实际数据量大,因此用一个简单的数据举例,请求算法。
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的公式下拉或右拉,跳过第二步即可。
我正在验算。请给邮箱,我把这原始数据发给您?
追答移步wps论坛的wps forwindows栏目发帖求助,同步上传样本文档,我会在那里关注你的,wps解答支持团的其他朋友,同样也会给予支持。
样本文档中,可用手工模拟一组答案,以便准确理解你的需求。
这个是4个订单,每个订单BOM就是M那几颗料。
追答哦,那你也可以直接用公式把它关联上吧
不过我觉得还是做套表比较直观灵活
我觉得对于简单的几个订单,这方法应该可以用。
但是对于1000个以上订单,10000以上的料件,就没法用了。
另外,实际库存中,可能有很多项目,有很多不同的BOM。
用这个办法工作量非常大。你觉得呢?
所以呢,要用ALL-IN-ONE BOM才行啊,并且物料多了以后,你公司要有ERP系统,可以从系统中自动更新库存数量甚至是ECN后的BOM,或者干脆请人做一个类似自动跑MPS的外挂即可。
追问这样工作量大,牵扯人力物力太大。ERP系统二次开发,也来不及了。
只要这个简单表格,通过简单的数据透视,简单算法,能得到就可以了。
我几个月前做过,大概用了二次数据透视,外加一两个简单算法,很快就解决了。
可是当时没总结,一时又找不到当时的记录。因此发帖寻求更好更快的办法。
已经发送。多谢啦。
追答水平有限,我只能帮你把缺少物料的数据全部提取出来,至于制作成你需要的表格就只能手动用数据透视表来做了。
追问数据透视我也做过。但是函数设置上有点困难。
缺少的物料可以做差,然后用Vlookup查询,可是目标是按照订单和物料归类。
我把做好的表格给你发过去了,你收下,我感觉速度还是很快的,不需要用函数,而且数据透视表会自动归类,不需要你做处理了
已经邮件回复,请继续核算数据是否异常。