excel自动实现多层级BOM物料分解

xiuluolyf

输入产品月度需求，用excel分解得出最终使用的所有原料数量；目前主要使用vlookup逐级引用，遇到多级BOM，非常繁琐（实际的BOM有上万行），各位高手有没有好的方法，可以实现一次分解到最底层物料，不用多次分解。

cpagood

VBA应该可以。

opiona

代码适用于三级工序：成品、半成品、零件
需求中可以有：成品、半成品、零件，
代码的区别是：开头分别为：03/02/01，
也可以是其他形式，在代码中相应修改
原理：
1、将需求的成品03根据BOM分解为02和01
2、将需求中直接是：半成品02
3、将步骤1和2中的半成品02合并，再根据Bom分解为：零件01
4、需求中直接就是：零件01
5、将步骤1中03分解出来的01、步骤3中02分解出来的01、步骤4中直接是01 全部汇总求和

部分代码如下：

1. '*********************************
   
2. '*******  北极狐工作室出品  ******
   
3. '*******  QQ：14885553      ******
   
4. '*********************************
   
5. 
   
6. Sub Opiona()
   
7. 
   
8. 'On Error Resume Next    '// 发生错误，自动执行下一句，就是忽略错误
   
9. Application.ScreenUpdating = False '//关闭屏幕刷新
   
10. Application.DisplayAlerts = False '//关闭系统提示
    
11. t = Timer   '//开始时间
    
12. 
    
13.     Set SH0 = Sheets("BOM示例")  '//数据源
    
14.     Set SH1 = Sheets("需求")     '//需求
    
15.     Set SH2 = Sheets("所有原料数量")
    
16.     SH2.Range("A2:Z65536").ClearContents
    
17.    
    
18.     Str_coon = "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & ThisWorkbook.FullName      '//OFFICE2003
    
19.     StrSQL = ""
    
20. 
    
21.    
    
22.     Rem 需求表中03转化为02和01
    
23.     StrSQL1 = StrSQL1 & "SELECT 子物料编码,子单位,SUM(单耗AA) AS 单耗AC,SUM(单耗BA) AS 单耗AD FROM ("
    
24.     StrSQL1 = StrSQL1 & "SELECT A.物料编码,B.子物料编码,B.子单位,A.计划AA*B.单耗 AS 单耗AA,A.计划BA*B.单耗 AS 单耗BA FROM ("
    
25.     StrSQL1 = StrSQL1 & "SELECT 物料编码,[" & SH1.Cells(1, 4) & "] AS 计划AA,[" & SH1.Cells(1, 5) & "] AS 计划BA FROM [" & SH1.Name & "$] WHERE MID(物料编码,1,2)='03'"
    
26.     StrSQL1 = StrSQL1 & ") AS A LEFT JOIN ("
    
27.     StrSQL1 = StrSQL1 & "SELECT 物料编码,子物料编码,子单位,单耗 FROM [" & SH0.Name & "$]"
    
28.     StrSQL1 = StrSQL1 & ") AS B ON A.物料编码=B.物料编码"
    
29.     StrSQL1 = StrSQL1 & ") GROUP BY 子物料编码,子单位"
    
30.    
    
31.    
    
32.     Rem 全部01汇总：
    
33.     StrSQL = StrSQL & "SELECT 子物料编码 AS 物料编码,子单位 AS 单位,SUM(单耗BC) AS 计划AA,SUM(单耗BD) AS 计划BB FROM ("
    
34.    
    
35.     Rem 02转换为01：
    
36.     StrSQL = StrSQL & "SELECT D.子物料编码,D.子单位,C.单耗AC*D.单耗 AS 单耗BC,C.单耗AD*D.单耗 AS 单耗BD FROM ("
    
37.    
    
38.     Rem 03提取后结果中的02
    
39.     StrSQL = StrSQL & "SELECT 子物料编码 AS 物料编码,单耗AC,单耗AD FROM (" & StrSQL1 & ") WHERE MID(子物料编码,1,2)='02'"
    
40.     StrSQL = StrSQL & " UNION ALL "
    
41.     Rem 需求表中直接是02的
    
42.     StrSQL = StrSQL & "SELECT 物料编码,[" & SH1.Cells(1, 4) & "] AS [单耗AC],[" & SH1.Cells(1, 5) & "] AS [单耗AD] FROM [" & SH1.Name & "$] WHERE MID(物料编码,1,2)='02'"
    
43.    
    
44.     StrSQL = StrSQL & ") AS C LEFT JOIN ("
    
45.     StrSQL = StrSQL & "SELECT 物料编码,子物料编码,子单位,单耗 FROM [" & SH0.Name & "$] WHERE MID(物料编码,1,2)='02'"
    
46.     StrSQL = StrSQL & ") AS D ON C.物料编码=D.物料编码"
    
47.    
    
48.     Rem 03提取后结果中的01
    
49.     StrSQL = StrSQL & " UNION ALL "
    
50.     StrSQL = StrSQL & "SELECT 子物料编码,子单位,单耗AC AS 单耗BC,单耗AD AS 单耗BD FROM (" & StrSQL1 & ") WHERE MID(子物料编码,1,2)='01'"
    
51.    
    
52.     Rem 需求表中直接是01的
    
53.     StrSQL = StrSQL & " UNION ALL "
    
54.     StrSQL = StrSQL & "SELECT 物料编码 AS 子物料编码,单位 AS 子单位,[" & SH1.Cells(1, 4) & "] AS 单耗BC,[" & SH1.Cells(1, 5) & "] AS 单耗BD FROM [" & SH1.Name & "$] WHERE MID(物料编码,1,2)='01'"
    
55.   
    
56.     StrSQL = StrSQL & ") GROUP BY 子物料编码,子单位 ORDER BY 子物料编码"
    
57.    
    
58.     SQLARR = GET_SQL_To_Arr(StrSQL, Str_coon, False)
    
59.     SH2.Range("B2").Resize(UBound(SQLARR, 1) + 1, UBound(SQLARR, 2) + 1) = SQLARR
    
60.    
    
61.     Rem 序号
    
62.     For I = 2 To SH2.Range("B65536").End(3).Row
    
63.         SH2.Cells(I, 1) = I - 1
    
64.     Next I
    
65.    
    
66. Application.ScreenUpdating = True '//恢复屏幕刷新
    
67. Application.DisplayAlerts = True '//恢复系统提示
    
68. MsgBox "一共用时：" & Format(Timer - t, "#0.0000") & " 秒", , "北极狐提示!!"  '//提示所用时间
    
69. End Sub

复制代码

t13564865256

opiona 发表于 2015-9-25 17:05
代码适用于三级工序：成品、半成品、零件
需求中可以有：成品、半成品、零件，
代码的区别是：开头分别为 ...

我的个天，你这是根据他的写出来的吗？

opiona

完整代码见附件： BOM示例三级.rar (22.39 KB, 下载次数: 1257)

2015-9-25 17:12 上传

点击文件名下载附件

BOM表有点改动，标题不能有重复

Anniezyl

厉害，收藏了

h749600173

opiona 发表于 2015-9-25 17:12
完整代码见附件：

BOM表有点改动，标题不能有重复

请教OPIONA,如果BOM有8~9阶，应如何操作？如果可以，在扣除库存后，把每一层的成品，半品，物料的需求分别计算出来，谢谢！

opiona

h749600173 发表于 2018-4-15 11:19
请教OPIONA,如果BOM有8~9阶，应如何操作？如果可以，在扣除库存后，把每一层的成品，半品，物料的需求分 ...

找到高手的递归方式，没有级别限制的！
BOM各层需求计算,需要扣除每层的库存和在线后再往下展开需求-原始.rar (29.58 KB, 下载次数: 1025)

2018-4-15 11:58 上传

点击文件名下载附件

我做的附件还没加入扣除库存

BOM拆解-基本代码.rar (23.55 KB, 下载次数: 685)

2018-4-15 11:50 上传

点击文件名下载附件

h749600173

opiona 发表于 2018-4-15 11:53
找到高手的递归方式，没有级别限制的！

谢谢OPIONA！

h749600173

h749600173 发表于 2018-4-16 19:51
谢谢OPIONA！

OPIONA，再次谢谢你。请帮按附件的内容及要求写个代码，实现库存 (缺料表)中的那个要求。谢谢！