Option Explicit
Sub CalculateDynamicLoading()
Const totalSteps As Long = 335
Const totalDays As Long = 30
Const dailyStart As Long = 100
Const totalMachines As Long = 20
Dim wsData As Worksheet, wsResults As Worksheet, wsMachine As Worksheet, wsInitialWIP As Worksheet
Dim i As Long, day As Long, machineType As Long, stepIndex As Long
Dim flowArr() As Variant, machineArr() As Variant, initialWIP() As Variant
Dim wip() As Double, output() As Double
Dim machineLoading() As Double, results() As Variant
Dim totalCycleTime As Double
Dim oee As Double, wph As Double, toolCount As Long
Dim machineCapacityPerType() As Double ' 存储每个机台类型的每日产能
Dim dailyProcessQty() As Double ' 每天每个机台类型处理的总数量
' 设置工作表
Set wsData = ThisWorkbook.Sheets("FlowData")
Set wsMachine = ThisWorkbook.Sheets("MachineData")
Set wsResults = ThisWorkbook.Sheets("Results")
Set wsInitialWIP = ThisWorkbook.Sheets("InitialWIP")
wsResults.Cells.Clear
' 读取Flow定义
flowArr = wsData.Range("A2:B" & (1 + totalSteps)).Value
' 读取机台参数(4列:CycleTime, ToolCount, OEE, WPH)
ReDim machineArr(1 To totalMachines, 1 To 4)
ReDim machineCapacityPerType(1 To totalMachines)
For i = 1 To totalMachines
' 数据验证
If wsMachine.Cells(i + 1, 2) <= 0 Then
MsgBox "错误:机台类型" & i & "的Cycle Time必须大于0!", vbCritical
Exit Sub
End If
If wsMachine.Cells(i + 1, 3) <= 0 Then
MsgBox "错误:机台类型" & i & "的Tool Count必须大于0!", vbCritical
Exit Sub
End If
If wsMachine.Cells(i + 1, 4) <= 0 Or wsMachine.Cells(i + 1, 4) > 1 Then
MsgBox "错误:机台类型" & i & "的OEE必须在0-1之间!", vbCritical
Exit Sub
End If
If wsMachine.Cells(i + 1, 5) <= 0 Then
MsgBox "错误:机台类型" & i & "的WPH必须大于0!", vbCritical
Exit Sub
End If
machineArr(i, 1) = wsMachine.Cells(i + 1, 2) ' Cycle Time (小时)
machineArr(i, 2) = wsMachine.Cells(i + 1, 3) ' Tool Count
machineArr(i, 3) = wsMachine.Cells(i + 1, 4) ' OEE
machineArr(i, 4) = wsMachine.Cells(i + 1, 5) ' WPH
' 预先计算每个机台类型的每日产能
machineCapacityPerType(i) = machineArr(i, 3) * machineArr(i, 4) * machineArr(i, 2) * 24
Next i
' 读取初始WIP分布
initialWIP = wsInitialWIP.Range("A2:B" & (1 + totalSteps)).Value
' 初始化结果数组
ReDim machineLoading(1 To totalDays, 1 To totalMachines)
ReDim results(1 To (totalDays * totalMachines) + 1, 1 To 4)
' 初始化WIP和输出数组
ReDim wip(1 To totalSteps)
ReDim output(1 To totalSteps)
' 设置初始WIP
For stepIndex = 1 To totalSteps
wip(stepIndex) = initialWIP(stepIndex, 2)
Next stepIndex
' 主循环:30天模拟
For day = 1 To totalDays
' 重置每日输出
For stepIndex = 1 To totalSteps
output(stepIndex) = 0
Next stepIndex
' 重置每日每个机台类型的处理数量
ReDim dailyProcessQty(1 To totalMachines)
' 处理每个Flow步骤
For stepIndex = 1 To totalSteps
Dim arrivalQty As Double, processQty As Double
' 计算到达量
If stepIndex = 1 Then
arrivalQty = dailyStart + wip(stepIndex)
Else
arrivalQty = output(stepIndex - 1) + wip(stepIndex)
End If
' 获取机台参数
machineType = flowArr(stepIndex, 2)
If machineType < 1 Or machineType > totalMachines Then
MsgBox "无效的机台类型: " & machineType & " (步骤 " & stepIndex & ")", vbCritical
Exit Sub
End If
oee = machineArr(machineType, 3)
wph = machineArr(machineType, 4)
toolCount = machineArr(machineType, 2)
' 计算产能 = OEE × WPH × ToolCount × 24
Dim machineCapacity As Double
machineCapacity = machineCapacityPerType(machineType) ' 直接使用预先计算的产能
' 处理数量计算
processQty = Application.WorksheetFunction.Min(arrivalQty, machineCapacity)
' 更新WIP和输出
wip(stepIndex) = arrivalQty - processQty
output(stepIndex) = processQty
' 累计该机台类型当天的处理数量
dailyProcessQty(machineType) = dailyProcessQty(machineType) + processQty
' 累计总Cycle Time(注意:这里使用CycleTime还是1/WPH?用户要求使用独立WPH,但CycleTime在表中仍然存在,我们这里用1/WPH作为实际处理一个产品的时间?)
' 注意:总CycleTime应该是所有产品在所有步骤上的处理时间之和(不考虑并行)。但这里我们使用表中提供的CycleTime(用户可能希望用这个值)?还是用1/WPH?
' 用户没有明确,但之前的问题中要求使用独立WPH,所以这里应该用1/WPH作为处理时间?
' 然而,在表中,CycleTime和WPH应该是匹配的(即互为倒数),但用户可能输入不匹配的值。为了统一,我们使用1/WPH作为处理时间。
totalCycleTime = totalCycleTime + (processQty * (1 / wph))
Next stepIndex
' 一天结束后,计算每个机台类型的负载率
For machineType = 1 To totalMachines
If machineCapacityPerType(machineType) > 0 Then
machineLoading(day, machineType) = dailyProcessQty(machineType) / machineCapacityPerType(machineType)
Else
machineLoading(day, machineType) = 0
End If
Next machineType
Next day
' 准备结果输出(与之前相同)
results(1, 1) = "Day"
results(1, 2) = "Machine Type"
results(1, 3) = "Utilization"
results(1, 4) = "Status"
Dim rowIndex As Long: rowIndex = 1
For day = 1 To totalDays
For machineType = 1 To totalMachines
rowIndex = rowIndex + 1
results(rowIndex, 1) = day
results(rowIndex, 2) = machineType
results(rowIndex, 3) = Format(machineLoading(day, machineType), "0.00%")
' 标记负载状态
Select Case machineLoading(day, machineType)
Case Is > 0.85: results(rowIndex, 4) = "Overload"
Case Is > 0.7: results(rowIndex, 4) = "High"
Case Is > 0.5: results(rowIndex, 4) = "Normal"
Case Else: results(rowIndex, 4) = "Low"
End Select
Next machineType
Next day
' 输出结果到工作表
wsResults.Range("A1").Resize(rowIndex, 4) = results
' 格式化结果
With wsResults
.Columns("A:D").AutoFit
.Range("A1:D1").Font.Bold = True
.Columns("C").NumberFormat = "0.00%"
End With
' 输出总Cycle Time和WIP状态(注意:这里总CycleTime是用1/WPH计算的)
wsResults.Cells(1, 6).Value = "Total Cycle Time (" & totalDays & " days)"
wsResults.Cells(1, 7).Value = Format(totalCycleTime, "#,##0.00") & " hours"
wsResults.Cells(3, 6).Value = "Initial WIP at Step 1"
wsResults.Cells(3, 7).Value = initialWIP(1, 2)
wsResults.Cells(4, 6).Value = "Initial WIP at Step " & totalSteps
wsResults.Cells(4, 7).Value = initialWIP(totalSteps, 2)
wsResults.Cells(5, 6).Value = "Final WIP at Step 1"
wsResults.Cells(5, 7).Value = wip(1)
wsResults.Cells(6, 6).Value = "Final WIP at Step " & totalSteps
wsResults.Cells(6, 7).Value = wip(totalSteps)
MsgBox "动态Loading计算完成!" & vbCrLf & _
"投片计划: " & totalDays & "天 × " & dailyStart & "片/天" & vbCrLf & _
"初始WIP(Step1): " & initialWIP(1, 2) & vbCrLf & _
"初始WIP(Step" & totalSteps & "): " & initialWIP(totalSteps, 2) & vbCrLf & _
"最终WIP(Step1): " & wip(1) & vbCrLf & _
"最终WIP(Step" & totalSteps & "): " & wip(totalSteps) & vbCrLf & _
"总Cycle Time: " & Format(totalCycleTime, "#,##0.00") & " 小时", vbInformation
End Sub
需要依据该代码,倒推回原有的底表的具体结构,实现运行2个VBA程序,实现底表结构的生成+Loading的计算
最新发布