如何用vba计算多层bom物料需求
excel表单状态(如图)
A列:客户
B列:成品代码
c列:bom一级
D列:bom二级一直往后，最多6层(bom层级6列)
诉求
用vba根据成品需求，求对应的物料需求(如成品的下阶是半品，半品下阶是材料，半品还有委外层级)，考虑良率，bom用量
该回答引用ChatGPT
假设成品代码在第2行，物料编码在第4列，物料数量在第5列，BOM层级为6级。
以下是实现过程：
1.先将BOM中的物料编码和物料数量分别存入一个二维数组中。代码如下：
Dim arrBom As Variant
Dim i As Long, j As Long
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
arrBom = Range(Cells(1, 1), Cells(lastRow, 7)).Value
For i = 2 To UBound(arrBom, 1)
For j = 3 To UBound(arrBom, 2) Step 2
If arrBom(i, j) <> "" Then
ReDim Preserve arrMaterial(1 To UBound(arrMaterial, 1), 1 To UBound(arrMaterial, 2) + 1)
arrMaterial(1, UBound(arrMaterial, 2)) = arrBom(i, j)
arrMaterial(2, UBound(arrMaterial, 2)) = arrBom(i, j + 1)
End If
Next j
Next i
2、然后根据成品代码，递归查找物料需求。代码如下：
Sub GetMaterialRequirement(ByVal strProductCode As String, ByVal dblProductQty As Double)
Dim arrMatch As Variant
Dim i As Long, j As Long
Dim arrTemp() As Variant
Dim dblChildQty As Double
For i = 1 To UBound(arrMaterial, 2)
If arrMaterial(1, i) = strProductCode Then
If UBound(arrMatch) = -1 Then
ReDim arrMatch(1 To 3, 0)
End If
arrMatch(1, UBound(arrMatch, 2) + 1) = arrMaterial(1, i)
arrMatch(2, UBound(arrMatch, 2) + 1) = arrMaterial(2, i)
arrMatch(3, UBound(arrMatch, 2)) = dblProductQty * arrMaterial(2, i)
If arrMaterial(1, i + 1) <> "" Then
dblChildQty = dblProductQty * arrMaterial(2, i)
GetMaterialRequirement arrMaterial(1, i + 1), dblChildQty
End If
End If
Next i
If UBound(arrMatch) > -1 Then
arrTemp = arrResult
ReDim arrResult(1 To UBound(arrTemp, 1), 1 To UBound(arrTemp, 2) + UBound(arrMatch, 2) + 1)
For i = 1 To UBound(arrTemp, 1)
For j = 1 To UBound(arrTemp, 2)
arrResult(i, j) = arrTemp(i, j)
Next j
Next i
For i = 1 To UBound(arrMatch, 1)
For j = 1 To UBound(arrMatch, 2)
arrResult(i, UBound(arrTemp, 2) + j) = arrMatch(i, j)
Next j
Next i
End If
End Sub
3、最后将结果输出到另一个工作表中。代码如下：
Sub OutputResult()
Sub CalculateBOM()
Dim ws As Worksheet
Dim lastRow As Long
Dim dictBOM As Object
Dim dictLevel As Object
Dim arrLevel() As String
Dim i As Long, j As Long
Dim prodCode As String
Dim reqQty As Double
Dim bomQty As Double
'初始化字典对象
Set dictBOM = CreateObject("Scripting.Dictionary")
Set dictLevel = CreateObject("Scripting.Dictionary")
'指定工作表
Set ws = ThisWorkbook.Sheets("Sheet1")
'确定最后一行
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'遍历每一行数据，建立BOM字典
For i = 2 To lastRow
prodCode = ws.Cells(i, 2).Value '成品代码
If Not dictBOM.exists(prodCode) Then
dictBOM.Add prodCode, CreateObject("Scripting.Dictionary")
End If
dictBOM(prodCode).Add ws.Cells(i, 3).Value, 1 'BOM第一层级
'记录每个BOM层级的列号
For j = 4 To 9 '最多6层
If ws.Cells(i, j).Value <> "" Then
If Not dictLevel.exists(ws.Cells(i, j).Value) Then
dictLevel.Add ws.Cells(i, j).Value, j
End If
Else
Exit For '遇到空单元格退出
End If
Next j
Next i
'输入需要计算的成品代码和需求量
prodCode = InputBox("请输入成品代码", "计算BOM需求")
reqQty = InputBox("请输入需求量", "计算BOM需求")
'初始化数组，记录当前层级
ReDim arrLevel(dictLevel.Count - 1)
For i = 0 To UBound(arrLevel)
arrLevel(i) = dictLevel.keys()(i)
Next i
'递归计算BOM需求
CalculateBOMQty prodCode, reqQty, dictBOM, dictLevel, arrLevel, 0
'输出结果
For i = 0 To UBound(arrLevel)
ws.Cells(1, 11 + i).Value = arrLevel(i) & "需求量"
Next i
ws.Cells(2, 1).Value = prodCode
ws.Cells(2, 2).Value = reqQty
For i = 0 To UBound(arrLevel)
ws.Cells(2, 11 + i).Value = dictLevel(arrLevel(i))
Next i
End Sub
Sub CalculateBOMQty(prodCode As String, reqQty As Double, dictBOM As Object, dictLevel As Object, arrLevel() As String, level As Long)
Dim i As Long
Dim bomCode As String
'递归出口
If level > UBound(arrLevel) Then Exit Sub
'遍历当前层级的所有物料
For Each bomCode In dictBOM(prodCode).keys
'判断是否为当前层级
If dictLevel(bomCode) = dictLevel(arrLevel(level +
如下：
Sub BOM()
'
' BOM 宏
'
'
Dim data As Range, currow As Integer, datarows As Integer
Dim row As Integer, part As Integer
Set data = Range("A1:C10")
datarows = data.Rows.Count
currow = 2
part = 1
For row = 1 To datarows
If Cells(row, 3) = part Then
Cells(currow, 4) = Cells(row, 1)
currow = currow + 1
part = Cells(row, 2)
End If
Next row
End Sub
```bash
你可以使用 VBA 根据成品需求计算对应的物料需求。以下是一种可能的实现方法：
首先，将所有的 BOM 数据加载到 VBA 中，可以使用 ADO 连接到 Excel 表格或直接在 VBA 中使用 Excel 对象模型。
接着，编写一个函数，输入成品代码和所需数量，返回物料需求列表。函数实现的大体思路如下：
a. 找到成品代码所在行，并读取该行的 BOM 数据；
b. 对于每个 BOM 条目，递归计算其下级物料需求，并将其与数量相乘累加到结果中；
c. 返回最终的物料需求列表。
在递归计算 BOM 条目下级物料需求时，你需要考虑以下情况：
a. 如果下级物料是半成品，那么需要继续递归计算其下级物料需求；
b. 如果下级物料是材料，那么直接返回所需数量；
c. 如果下级物料是委外加工件，那么需要在结果中增加对应的委外加工费用，可以使用类似于“材料 * 单价”的方式计算。
由于 BOM 层级最多 6 层，你可以考虑使用一个递归函数，每次递归计算一层 BOM 条目下级物料需求。
这是一个大概的实现思路，你可以根据具体情况进行调整。注意在编写代码时，要注意对 BOM 数据进行检查和处理，避免出现错误的计算结果。
Sub CalculateMaterialRequirement()
'定义变量和常量
Dim targetProductCode As String
Dim startRow As Long
Dim startCol As Long
Const maxBomLevel As Long = 6
Dim wb As Workbook
Dim ws As Worksheet
Dim currentRow As Long
Dim currentCol As Long
Dim currentBomLevel As Long
Dim currentProductCode As String
Dim materialRequirement As Double
'设置变量和常量的值
targetProductCode = "ABC-123"
startRow = 2
startCol = 3
'打开BOM表格
Set wb = Workbooks.Open("C:\BOM.xlsx")
Set ws = wb.Worksheets("Sheet1")
'循环读取BOM表格数据
For currentRow = startRow To ws.Cells(ws.Rows.Count, startCol).End(xlUp).Row
'比较成品代码
currentProductCode = ws.Cells(currentRow, 2).Value
If currentProductCode = targetProductCode Then
'找到需要计算物料需求的行
currentBomLevel = 1
materialRequirement = ws.Cells(currentRow, startCol).Value
For currentCol = startCol + 1 To startCol + maxBomLevel - 1
If Not IsEmpty(ws.Cells(currentRow, currentCol).Value) Then
currentBomLevel = currentBomLevel + 1
materialRequirement = materialRequirement + ws.Cells(currentRow, currentCol).Value
Else
Exit For
End If
Next currentCol
'输出物料需求结果
ws.Cells(currentRow, startCol + maxBomLevel).Value = materialRequirement
End If
Next currentRow
'关闭BOM表格
wb.Close SaveChanges:=False
End Sub
可以使用递归的方式来计算出物料的需求，递归函数可以从顶层开始，依次递归计算下一级的物料需求，直到最底层的材料。
首先，我们可以定义一个函数来递归计算每个成品的物料需求，函数需要输入一个成品的名称、数量和一个物料需求字典（key为物料名称，value为物料需求量），函数返回更新后的物料需求字典。代码如下：
Function CalculateMaterialDemand(productName As String, qty As Double, materialDemand As Scripting.Dictionary) As Scripting.Dictionary
Dim productRange As Range
Dim materialRange As Range
Dim bomLevel1 As String
Dim bomLevel2 As String
Dim bomLevel3 As String
Dim bomLevel4 As String
Dim bomLevel5 As String
Dim bomLevel6 As String
Dim materialQty As Double
Set productRange = Range("A:A").Find(productName)
If Not productRange Is Nothing Then
Set materialRange = productRange.Offset(0, 2)
bomLevel1 = productRange.Offset(0, 2).Value
bomLevel2 = productRange.Offset(0, 3).Value
bomLevel3 = productRange.Offset(0, 4).Value
bomLevel4 = productRange.Offset(0, 5).Value
bomLevel5 = productRange.Offset(0, 6).Value
bomLevel6 = productRange.Offset(0, 7).Value
' Calculate demand for level 1 BOM
If Not materialDemand.Exists(bomLevel1) Then
materialDemand.Add bomLevel1, 0
End If
materialQty = qty * productRange.Offset(0, 8).Value * productRange.Offset(0, 9).Value
materialDemand(bomLevel1) = materialDemand(bomLevel1) + materialQty
' Calculate demand for level 2 BOM
If Not materialDemand.Exists(bomLevel2) Then
materialDemand.Add bomLevel2, 0
End If
materialQty = qty * productRange.Offset(0, 10).Value * productRange.Offset(0, 11).Value
materialDemand(bomLevel2) = materialDemand(bomLevel2) + materialQty
' Calculate demand for level 3 BOM
If Not materialDemand.Exists(bomLevel3) Then
materialDemand.Add bomLevel3, 0
End If
materialQty = qty * productRange.Offset(0, 12).Value * productRange.Offset(0, 13).Value
materialDemand(bomLevel3) = materialDemand(bomLevel3) + materialQty
' Calculate demand for level 4 BOM
If Not materialDemand.Exists(bomLevel4) Then
materialDemand.Add bomLevel4, 0
End If
materialQty = qty * productRange.Offset(0, 14).Value * productRange.Offset(0, 15).Value
materialDemand(bomLevel4) = materialDemand(bomLevel4) + materialQty
' Calculate demand for level 5 BOM
If Not materialDemand.Exists(bomLevel5) Then
materialDemand.Add bomLevel5, 0
End If
materialQty = qty * productRange.Offset(0,
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class BOMCalculator {
public static void main(String[] args) throws IOException {
FileInputStream file = new FileInputStream(new File("data.xlsx"));
// 创建 Workbook 对象
Workbook workbook = WorkbookFactory.create(file);
// 获取第一个工作表
Sheet sheet = workbook.getSheetAt(0);
// 创建 BOM 项列表
List<BOMItem> bomItems = new ArrayList<BOMItem>();
// 读取 Excel 表格数据并将它们转换成 BOM 项
for (Row row : sheet) {
String product = row.getCell(1).getStringCellValue();
String material = row.getCell(2).getStringCellValue();
double rate = row.getCell(3).getNumericCellValue();
double usage = row.getCell(4).getNumericCellValue();
int level = row.getCell(5).getCellType() == Cell.CELL_TYPE_BLANK ? 1 : (int) row.getCell(5).getNumericCellValue();
BOMItem bomItem = new BOMItem(product, material, rate, usage);
// 如果 BOM 层级大于 1，则将该 BOM 项添加到上一级 BOM 项的子项列表中
if (level > 1) {
BOMItem parentItem = bomItems.get(bomItems.size() - 1);
for (int i = 2; i < level; i++) {
parentItem = parentItem.getSubItems().get(parentItem.getSubItems().size() - 1);
}
parentItem.addSubItem(bomItem);
} else { // 否则将该 BOM 项添加到 BOM 项列表中
bomItems.add(bomItem);
}
}
// 关闭文件输入流
file.close();
// 计算物料需求
Map<String, Double> quantities = new HashMap<String, Double>();
calculateMaterials("A001", 100, quantities, bomItems);
// 输出物料需求
for (Map.Entry<String, Double> entry : quantities.entrySet()) {
System.out.println(entry.getKey() + ": " + entry.getValue());
}
}
public static void calculateMaterials(String product, double quantity, Map<String, Double> quantities, List<BOMItem> bomItems) {
for (BOMItem bomItem : bomItems) {
if (!bomItem.getProduct().equals(product)) { // 如果成品不匹配，则继续循环
continue;
}
String material = bomItem.getMaterial(); // 获取物料名称
double rate = bomItem.getRate(); // 获取良率
double usage = bomItem.getUsage(); // 获取 BOM 用量
String subproduct = null;
List<BOMItem> subItems = bomItem.getSubItems();
if (subItems.size() > 0) {
实现这个需求可以考虑使用递归算法，具体实现步骤如下：
1.在VBA中读取成品代码和对应的需求量，例如：
Dim demand As Long
Dim productCode As String
demand = Range("E2").Value
productCode = Range("B2").Value
2.根据成品代码，在BOM表格中找到对应的一级物料代码和对应的用量，例如：
Dim row As Long
Dim firstLevelCode As String
Dim firstLevelUsage As Long
row = Application.WorksheetFunction.Match(productCode, Range("B:B"), 0)
firstLevelCode = Range("C" & row).Value
firstLevelUsage = Range("D" & row).Value
3.如果一级物料代码为空，则返回空值，否则继续递归查找下一级物料的需求量，例如：
Dim demandOfFirstLevel As Long
If firstLevelCode = "" Then
demandOfFirstLevel = 0
Else
demandOfFirstLevel = CalculateDemandOfMaterial(firstLevelCode, firstLevelUsage * demand)
End If
4.递归查找下一级物料的需求量，每一级物料都需要考虑良率和用量，例如：
Function CalculateDemandOfMaterial(materialCode As String, usage As Long) As Long
Dim row As Long
Dim nextLevelCode As String
Dim nextLevelUsage As Long
Dim yieldRate As Double
Dim demand As Long
row = Application.WorksheetFunction.Match(materialCode, Range("B:B"), 0)
yieldRate = Range("E" & row).Value / 100
demand = Round(usage / yieldRate, 0)
If yieldRate = 0 Or demand = 0 Then
CalculateDemandOfMaterial = 0
Else
nextLevelCode = Range("C" & row).Value
nextLevelUsage = Range("D" & row).Value
If nextLevelCode = "" Then
CalculateDemandOfMaterial = demand
Else
CalculateDemandOfMaterial = CalculateDemandOfMaterial(nextLevelCode, nextLevelUsage * demand)
End If
End If
End Function
5.最后将递归查找的结果输出到对应的单元格中，例如：
Range("F2").Value = demandOfFirstLevel
这样，就可以根据成品需求量递归地计算出所有下级物料的需求量，并考虑了良率和BOM用量的影响。
您可以使用VBA编写一个递归函数来计算多层BOM物料需求。以下是一个示例代码，您可以根据自己的数据表结构和计算公式进行调整。
Sub CalculateMaterialDemand()
'定义变量
Dim ws As Worksheet
Dim lr As Long '数据表最后一行
Dim i As Long '行计数器
Dim j As Long '列计数器
Dim bomLevel As Long 'BOM层级
Dim materialQty As Double '物料需求量
Dim materialID As String '物料编号
Dim bomQty As Double 'BOM用量
Dim yield As Double '良率
Dim demand As Double '物料总需求量
'设置工作表
Set ws = ThisWorkbook.Sheets("Sheet1")
'获取最后一行
lr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'循环处理每一行数据
For i = 2 To lr '从第2行开始，第1行为表头
'判断是否为BOM一级
If ws.Cells(i, 3).Value = "" Then
'计算成品需求量
demand = ws.Cells(i, 5).Value
Else
'获取物料编号、BOM用量、BOM层级、良率
materialID = ws.Cells(i, 3).Value
bomQty = ws.Cells(i, 4).Value
bomLevel = ws.Cells(i, 3).Column - 3 'BOM层级为列数减去3
yield = ws.Cells(i, 6).Value
'计算物料需求量
materialQty = demand * bomQty * yield
'输出结果
ws.Cells(i, 7).Value = materialQty '物料需求量
ws.Cells(i, 8).Value = bomLevel 'BOM层级
ws.Cells(i, 9).Value = demand '父级需求量
'递归计算下一层物料需求量
CalculateNextLevel ws, materialID, materialQty, bomLevel + 1, demand
End If
Next i
End Sub
'递归函数
Sub CalculateNextLevel(ws As Worksheet, materialID As String, materialQty As Double, bomLevel As Long, demand As Double)
Dim lr As Long '数据表最后一行
Dim i As Long '行计数器
Dim j As Long '列计数器
Dim childMaterialID As String '子物料编号
Dim childBOMQty As Double '子物料BOM用量
Dim childYield As Double '子物料良率
Dim childMaterialQty As Double '子物料需求量
'获取最后一行
lr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'循环处理每一行数据
For i = 2 To lr '从第2行开始，第1行为表头
'判断是否为当前物料的下一层物料
If ws.Cells(i, bomLevel + 3).Value = materialID Then
'获取子物料编号、BOM
如果对您有帮助，请给与采纳，谢谢。
上面的兄弟解释的差不多了，我直接给代码
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks.Open("C:\path\to\your\file.xlsx")
Set ws = wb.Sheets("Sheet1")
Dim lastRow As Long
Dim i As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
' 在这里执行操作
Next i
Dim bomLevel As Integer
Dim quantity As Integer
Dim yield As Double
Dim bomUsage As Double
Dim finalRequirement As Double
bomLevel = ws.Cells(i, 1).Value
quantity = ws.Cells(i, 2).Value
yield = ws.Cells(i, 3).Value
bomUsage = ws.Cells(i, 4).Value
' 计算最终物料需求
finalRequirement = quantity * bomUsage / yield
' 将结果写入第5列
ws.Cells(i, 5).Value = finalRequirement
以下答案基于ChatGPT与GISer Liu编写：
您可以使用 VBA 编写一个宏来自动计算对应的物料需求。以下是一个示例：
vbnet
Sub CalculateMaterialRequirement()
' 定义变量
Dim productCode As String
Dim quantity As Double
Dim productRow As Integer
Dim material As String
Dim materialRow As Integer
Dim bomLevel As Integer
' 获取成品代码和数量
productCode = InputBox("请输入成品代码", "成品需求")
If productCode = "" Then Exit Sub
quantity = InputBox("请输入成品数量", "成品需求")
If quantity = 0 Then Exit Sub
' 查找成品代码所在行
productRow = 2
Do While Cells(productRow, 2).Value <> productCode
productRow = productRow + 1
If Cells(productRow, 2).Value = "" Then
MsgBox "未找到对应的成品代码", vbCritical
Exit Sub
End If
Loop
' 循环计算每个物料的需求
For bomLevel = 1 To 6
materialRow = productRow
Do While materialRow <= Rows.Count And Cells(materialRow, 2).Value = productCode And Cells(materialRow, 1).Value <> ""
material = Cells(materialRow, bomLevel + 2).Value
If material <> "" Then
quantity = quantity * Cells(materialRow, bomLevel + 8).Value * Cells(materialRow, 9).Value
materialRow = 1
Exit Do
End If
materialRow = materialRow + 1
Loop
If materialRow > Rows.Count Or Cells(materialRow, 1).Value = "" Then Exit For
Debug.Print "Level " & bomLevel & ": " & material & " x " & quantity
Next
End Sub
这个宏会要求您输入成品代码和数量，然后计算每个物料的需求量，并在 VBA 编辑器中输出结果。您可以根据自己的需求对其进行修改，比如将输出结果写入工作表中。
你可以使用VBA来实现这个功能，首先，你需要编写一个函数，根据bom的层级信息来计算出需要每个物料的需求数量。然后，根据客户的需求数量和良率计算出最终需求数量。最后，用VBA编程的形式将计算的需求结果输出到Excel表格中。