基于参数化的装配线平衡全可能性求解及VBA代码优化问询

Great question! Your current code works for the fixed case of 3 workers and 10 processes, but hardcoding nested loops and worker variables makes it impossible to scale. Let's refactor this to be fully parameterized, using a dynamic approach to generate all possible process-to-worker assignments without hardcoding anything.

The Core Problem with Your Current Code #

Your original code uses 10 nested For loops (one per process) and hardcodes 3 worker variables. This breaks as soon as you change the number of processes or workers—you'd have to add/remove loops and rewrite the worker-tracking logic every time. Instead, we need a way to generate all possible assignment combinations dynamically, then calculate the worker times for each combination.

Solution Approach #

We can model each assignment as a base-N number where:

• N = number of workers (each digit represents a worker assignment for a process)
• Number of digits = number of processes
• Each digit (1 to N) tells us which worker handles that process

This lets us iterate from 0 to (N^M)-1 (where M = number of processes) and convert each integer to base-N to get a full assignment plan. We'll also optimize performance by reading all process-time data into a memory array instead of using repeated VLookup calls.

Full Parameterized VBA Code #

Sub rapor_calistir()
    Dim wsData As Worksheet, wsRapor As Worksheet
    Dim ProcessCount As Integer, WorkerCount As Integer
    Dim totalCombinations As Double
    Dim i As Long, j As Integer
    Dim allocation() As Integer
    Dim workerTimes() As Double
    Dim dataArr() As Variant
    Dim totalTime As Double
    Dim outputRow As Integer
    Dim tempNum As Long, timeVal As Double
    
    ' Initialize worksheet references
    Set wsData = ThisWorkbook.Sheets("Data")
    Set wsRapor = ThisWorkbook.Sheets("Rapor")
    
    ' Dynamically read parameters from Data sheet
    ' Assumes Data sheet has:
    ' - Column A = Process IDs (1,2,...M) starting at A2 (A1 is header)
    ' - Columns B to ... = Worker times (B1 = "Worker 1", C1 = "Worker 2", etc.)
    ProcessCount = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row - 1 ' Subtract 1 for header row
    WorkerCount = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column - 1 ' Subtract 1 for process ID column
    
    ' Clear previous results and write headers
    wsRapor.Range("A2:Z1048576").ClearContents
    wsRapor.Cells(1, 1) = "Index"
    For j = 1 To ProcessCount
        wsRapor.Cells(1, j + 1) = "Process " & j & " → Worker"
    Next j
    wsRapor.Cells(1, ProcessCount + 2) = "Total Time"
    For j = 1 To WorkerCount
        wsRapor.Cells(1, ProcessCount + 2 + j) = "Worker " & j & " Time"
    Next j
    
    ' Load all time data into memory array (way faster than repeated VLookup)
    dataArr = wsData.Range("A1").CurrentRegion.Value
    
    ' Calculate total number of combinations (WorkerCount^ProcessCount)
    totalCombinations = WorkerCount ^ ProcessCount
    
    ' Guard against impossible-to-process large combination counts
    If totalCombinations > 1000000 Then
        MsgBox "Warning: " & Format(totalCombinations, "#,##0") & " combinations is too large to process efficiently. Reduce process or worker count.", vbExclamation
        Exit Sub
    End If
    
    ' Initialize arrays for tracking assignments and worker times
    ReDim allocation(1 To ProcessCount)
    ReDim workerTimes(1 To WorkerCount)
    outputRow = 2
    
    ' Enumerate every possible assignment combination
    For i = 0 To totalCombinations - 1
        tempNum = i
        
        ' Convert current number to base-WorkerCount to get assignment plan
        ' We loop from last process to first to build the allocation array correctly
        For j = ProcessCount To 1 Step -1
            allocation(j) = (tempNum Mod WorkerCount) + 1 ' +1 to shift from 0-based to 1-based worker IDs
            tempNum = tempNum \ WorkerCount
        Next j
        
        ' Calculate total time and individual worker times
        totalTime = 0
        Erase workerTimes
        ReDim workerTimes(1 To WorkerCount)
        For j = 1 To ProcessCount
            ' Get time from data array: row = j+1 (skip header), column = allocation(j)+1 (skip process ID column)
            timeVal = dataArr(j + 1, allocation(j) + 1)
            workerTimes(allocation(j)) = workerTimes(allocation(j)) + timeVal
            totalTime = totalTime + timeVal
        Next j
        
        ' Write results to Rapor sheet
        wsRapor.Cells(outputRow, 1) = outputRow - 1 ' Index number
        ' Write assignment plan
        For j = 1 To ProcessCount
            wsRapor.Cells(outputRow, j + 1) = allocation(j)
        Next j
        ' Write total time
        wsRapor.Cells(outputRow, ProcessCount + 2) = totalTime
        ' Write individual worker times
        For j = 1 To WorkerCount
            wsRapor.Cells(outputRow, ProcessCount + 2 + j) = workerTimes(j)
        Next j
        
        outputRow = outputRow + 1
    Next i
    
    ' Log completion time
    wsRapor.Range("Q1") = Now()
    MsgBox "Done! Generated " & Format(totalCombinations, "#,##0") & " assignment plans.", vbInformation
End Sub

Key Improvements & Explanations #

1. Dynamic Parameter Reading

   • The code automatically pulls the number of processes from Column A of the Data sheet (counts how many rows have process IDs)
   • Pulls the number of workers from the number of columns in the Data sheet (subtracts 1 for the process ID column)
   • No hardcoding of 3 workers or 10 processes!

2. Efficient Data Handling

   • Reads all time data into a memory array (dataArr) once, instead of using VLookup in every loop iteration. This drastically speeds up the code for large numbers of combinations.

3. Dynamic Combination Generation

   • Uses base conversion to generate every possible assignment plan without nested loops. For example, with 2 workers and 3 processes, the number 5 (decimal) converts to 101 in base 2, meaning Process 1 → Worker 1, Process 2 → Worker 1, Process 3 → Worker 1.

4. Scalable Worker Time Tracking

   • Uses an array (workerTimes) instead of hardcoded variables (worker1, worker2, etc.). This works for any number of workers.

Critical Note: Combination Explosion #

This approach enumerates all possible assignments, which is only feasible for small values of processes and workers. For example:

• 10 processes + 3 workers = 59,049 combinations (manageable)
• 15 processes + 4 workers = 1,073,741,824 combinations (impossible to process)

If you need to handle larger cases, you'll want to switch to a heuristic optimization algorithm (like genetic algorithms or simulated annealing) instead of brute-force enumeration.

内容的提问来源于stack exchange，提问作者svorm