在好多共用件下依据成品BOM和库存算出最多配套量

aman1516 · 发表于 2016-7-23 23:03

三坛老窖发表于 2016-7-23 10:22
请测试！
--------------------------

学习了，就是
Do Until Proj(i, 1) <> BOM(j, 1)
 ……
 Loop
中的i, j与后面的
 '扣减库存
 For k = p To j - 1
 dicInv(BOM(k, 2)) = dicInv(BOM(k, 2)) - temp * BOM(k, 5)
 Next k
里面的k, p, j 有点理不清，习惯用for next 双循环直观些

aman1516 · 发表于 2016-7-23 23:09

本帖最后由 aman1516 于 2016-7-23 23:13 编辑

按金蝶导出的标准BOM格式，及按三坛老窖老师的代码改了一下，结果是出来了，但我是多转换一次数组格式实现的，请教下三坛老窖老师，能否直接按金蝶导出的BOM格式优化代码，详见附件：

Option Explicit
Sub pt()
Dim Inv, bom1, Proj, dicInv, BOM
Dim i, j, d, k1, k2, temp, r, r1, r2, z, m, n, p
'读取数据
bom1 = Sheet1.Range("A1:H" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1) 'BOM
ReDim BOM(1 To UBound(bom1), 1 To UBound(bom1, 2) + 1)
z = 0
For m = 2 To UBound(bom1)
If bom1(m, 2) = "代码" Then
r1 = m + 4
For n = r1 To UBound(bom1)
If bom1(n, 1) = "" Then
r2 = n - 1
Exit For
End If
Next
For k1 = r1 To r2
z = z + 1
BOM(z, 1) = bom1(m + 1, 2)
For k2 = 2 To UBound(bom1, 2) + 1
BOM(z, k2) = bom1(k1, k2 - 1)
Next
Next
End If
Next
'Sheet1.Range("N2:Z" & Rows.Count).ClearContents
'Sheet1.Range("N2").Resize(z, UBound(BOM, 2)) = BOM
Sheet2.Range("F2:F" & Rows.Count).ClearContents
Inv = Sheet2.Range("A2:E" & Sheet2.Range("A" & Rows.Count).End(xlUp).Row) '物料即时库存
Proj = Sheet3.Range("A2:C" & Sheet3.Range("A" & Rows.Count).End(xlUp).Row) '生产计划产品
'创建库存字典
Set dicInv = CreateObject("Scripting.Dictionary")
For d = 1 To UBound(Inv)
dicInv(Inv(d, 1)) = Inv(d, 5)
Next
'计算各产品的最多可生产数量
For i = 1 To UBound(Proj)
'求取该产品的最多可生产数量
temp = 9999999#
For j = 1 To UBound(BOM)
If Proj(i, 1) = BOM(j, 1) Then
If dicInv.exists(BOM(j, 2)) And temp > dicInv(BOM(j, 2)) / BOM(j, 6) Then
temp = dicInv(BOM(j, 2)) / BOM(j, 6)
End If
End If
Next
Proj(i, 3) = temp
For p = 1 To UBound(BOM)
If Proj(i, 1) = BOM(p, 1) Then
dicInv(BOM(p, 2)) = dicInv(BOM(p, 2)) - temp * BOM(p, 6)
End If
Next
Next
'输出所有产品的最多可生产数量和剩余库存
Sheet2.Range("F2:F" & Rows.Count).ClearContents
Sheet2.Range("F2:F" & UBound(Inv) + 1) = Application.WorksheetFunction.Transpose(dicInv.items)
Sheet3.Range("A2").Resize(UBound(Proj), 3) = Proj
End Sub

复制代码

库存可配套产品数2016 - 副本.rar (55.02 KB, 下载次数: 72)

三坛老窖 · 发表于 2016-7-25 15:02

aman1516 发表于 2016-7-23 23:09
按金蝶导出的标准BOM格式，及按三坛老窖老师的代码改了一下，结果是出来了，但我是多转换一次数组格式实 ...

在金蝶导出的标准BOM格式基础上，不经转换直接计算最多可生产的数量，优化的代码如下：

Sub pt_1()
Dim Inv, Prod, dicInv, BOM
Dim i&, j&, k&, p&, temp#
'读取数据
BOM = Sheet1.Range("A1:H" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1) 'BOM
Inv = Sheet2.Range("A2:E" & Sheet2.Range("A" & Rows.Count).End(xlUp).Row) '物料即时库存
Prod = Sheet3.Range("A2:C" & Sheet3.Range("A" & Rows.Count).End(xlUp).Row) '生产计划产品
'创建库存字典
Set dicInv = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Inv)
dicInv(Inv(i, 1)) = Inv(i, 5)
Next i
'计算各产品的最多可生产数量
For i = 1 To UBound(Prod)
'求取该产品的最多可生产数量
temp = 999999999#
For j = 1 To UBound(BOM)
If Prod(i, 1) = BOM(j, 2) Then
For k = j + 3 To UBound(BOM)
If Len(BOM(k, 1)) = 0 Then GoTo tmp
'If BOM(k, 1) = "4.01.26.02.001" Then Stop
If dicInv.exists(BOM(k, 1)) Then
If temp > dicInv(BOM(k, 1)) / BOM(k, 5) Then temp = dicInv(BOM(k, 1)) / BOM(k, 5)
Else
temp = 0: GoTo tmp
End If
Next k
End If
Next j
tmp:
Prod(i, 3) = Int(temp)
'扣减库存
For p = j + 3 To UBound(BOM)
If Len(BOM(p, 1)) = 0 Then Exit For
dicInv(BOM(p, 1)) = dicInv(BOM(p, 1)) - Prod(i, 3) * BOM(p, 5)
Next p
Next i
'输出所有产品的最多可生产数量和剩余库存
Sheet2.Range("F2:F" & UBound(Inv) + 1) = Application.WorksheetFunction.Transpose(dicInv.items)
Sheet3.Range("A2").Resize(UBound(Prod), 3) = Prod
End Sub

复制代码

三坛老窖 · 发表于 2016-7-25 15:39

在好多共用件且产品BOM为单层的前提下，依据现有库存计算出多个产品的最多配套量这样的问题，使用BOM矩阵，则更为直观和易于理解，且也容易计算求解。
附件中的sheet1工作表，就是依据BOM矩阵，使用公式和函数求解的。
（sheet1中使用了辅助列，本人不擅长使用函数，若是函数高手，则可省却辅助列）
-----------------------------------------------------

库存可配套产品数20160725.rar (69.29 KB, 下载次数: 235)

aman1516 · 发表于 2016-7-25 16:10

本帖最后由 aman1516 于 2016-7-25 16:42 编辑

三坛老窖发表于 2016-7-25 15:02
在金蝶导出的标准BOM格式基础上，不经转换直接计算最多可生产的数量，优化的代码如下：

忘记退出第2个循环了，难怪...
这是我原来的思路，参考老师的代码现修正如下：

Option Explicit
Sub pt()
Dim Inv, Bom, Proj, dicInv
Dim i, j, d, k, temp, r, r1, r2, m, n, p
'读取数据
Bom = Sheet1.Range("A1:H" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1) 'BOM
Inv = Sheet2.Range("A2:E" & Sheet2.Range("A" & Rows.Count).End(xlUp).Row) '物为即时库存
Proj = Sheet3.Range("A2:C" & Sheet3.Range("A" & Rows.Count).End(xlUp).Row) '生产计划产品
'创建库存字典
Set dicInv = CreateObject("Scripting.Dictionary")
For d = 1 To UBound(Inv)
dicInv(Inv(d, 1)) = Inv(d, 5)
Next
'计算产品最多可生产数量
For i = 1 To UBound(Proj)
'求取各产品最多可生产数量
temp = 9999999#
For j = 1 To UBound(Bom)
If Proj(i, 1) = Bom(j, 2) Then
r1 = j + 3
For m = r1 To UBound(Bom)
If Bom(m, 1) = "" Then
r2 = m - 1
Exit For
End If
Next
For n = r1 To r2
If dicInv.exists(Bom(n, 1)) Then
If temp > dicInv(Bom(n, 1)) / Bom(n, 5) Then temp = dicInv(Bom(n, 1)) / Bom(n, 5)
End If
Next
Exit For
End If
Next
Proj(i, 3) = Int(temp)
'扣减库存
For p = r1 To r2
dicInv(Bom(p, 1)) = dicInv(Bom(p, 1)) - Proj(i, 3) * Bom(p, 5)
Next p
Next
'输出各产品最多可生产数及剩余库存
Sheet2.Range("F2:F" & Rows.Count).ClearContents
Sheet2.Range("F2:F" & UBound(Inv) + 1) = Application.WorksheetFunction.Transpose(dicInv.items)
Sheet3.Range("A2").Resize(UBound(Proj), 3) = Proj
End Sub

复制代码

非常感谢！！学习了

xiyi0519 · 发表于 2021-5-12 23:17

aman1516 发表于 2016-7-23 23:09
按金蝶导出的标准BOM格式，及按三坛老窖老师的代码改了一下，结果是出来了，但我是多转换一次数组格式实 ...

这个对使用金蝶用户的有福了，可是我们的BOM清单和这个差别太大啦我们是列显示的，金蝶的是一个配方一段列示的

xiyi0519 · 发表于 2021-5-13 19:18

aman1516 发表于 2016-7-23 23:09
按金蝶导出的标准BOM格式，及按三坛老窖老师的代码改了一下，结果是出来了，但我是多转换一次数组格式实 ...

能帮忙做个转换BOM格式VBA代码吗？

可配套模板.rar (41.65 KB, 下载次数: 9)

lanyf · 发表于 2021-5-26 14:16

三坛老窖发表于 2016-7-25 15:39
在好多共用件且产品BOM为单层的前提下，依据现有库存计算出多个产品的最多配套量这样的问题，使用BOM矩阵， ...

三坛老窖老师，如您所说的，单层BOM的前提下，矩阵BOM比较直观，若是多层BOM，如何根据BOM进行成套率的计算呢？有好的方法吗？

15899681383 · 发表于 2021-8-23 14:47

lanyf 发表于 2021-5-26 14:16
三坛老窖老师，如您所说的，单层BOM的前提下，矩阵BOM比较直观，若是多层BOM，如何根据BOM进行成套率的 ...

多阶BOM进行成套率计算由方法了吗？

		自动登录	找回密码
密码			免费注册

[求助] 在好多共用件下依据成品BOM和库存算出最多配套量