Excel实现垫片组合最短路径求解：最少垫片数达成目标尺寸

Hey there! I totally get that you're not a professional coder and need a straightforward way to find the least number of washers to hit your target size in Excel. Let's walk through two reliable methods—one using Excel's built-in Solver (fixed to prioritize minimal count) and a simple VBA macro for guaranteed optimal results.

Method 1: Fix Excel Solver to Find the Optimal Solution #

Your initial Solver result was suboptimal because it likely stopped at the first feasible solution instead of minimizing the washer count. Here's how to configure it correctly:

Step 1: Set Up Your Spreadsheet #

First, lay out your data like this:

Step 2: Configure Solver #

1. Go to the Data tab → click Solver (if you don't see it, enable it first via File → Options → Add-Ins → Manage: Excel Add-Ins → Check "Solver Add-in" → OK).
2. In the Solver Parameters window:
   • Set Objective: Select cell D8 (Total Count) and choose Min (we want to minimize the number of washers).
   • By Changing Variable Cells: Select B1:B6 (the quantity of each washer).
   • Add Constraints:
     • Click Add → Set cell D7 (Total Size) equal to D9 (Target) → OK.
     • Click Add → Select B1:B6 → Choose int (integer, since you can't use partial washers) → OK.
     • Click Add → Select B1:B6 → Choose >= → Enter 0 (can't use negative washers) → OK.
3. Tweak Solver Options (critical for finding the best solution):
   • Click Options → Under All Methods, check "Ignore Integer Constraints during first iteration" (speeds up solving).
   • If you're using the Evolutionary method (better for integer optimization), increase the Maximum Time to 10 seconds or more to let it search thoroughly.
4. Click Solve → Select "Keep Solver Solution" → OK.

For your target of 83mm, this will return 1 white (51mm) + 1 green (32mm) with a total count of 2—exactly the optimal solution you want.

Method 2: Simple VBA Macro (Guaranteed Optimal Results) #

If Solver still doesn't behave as expected, a basic VBA macro will brute-force the minimal combination (since your washer sizes are limited, this runs quickly).

Step 1: Add the Macro #

1. Press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project Explorer → Insert → Module.
3. Paste this code:

Sub FindMinWashers()
    ' Get target size from cell D9 (adjust if you use a different cell)
    Dim targetSize As Integer
    targetSize = Range("D9").Value
    
    ' List of washer sizes (matches your A1:A6 order)
    Dim washerSizes As Variant
    washerSizes = Array(51, 44, 38, 32, 26, 13)
    
    ' Initialize variables to track the best solution
    Dim minTotal As Integer
    minTotal = targetSize \ washerSizes(5) ' Worst case: all small washers
    Dim bestQuantities As Variant
    ReDim bestQuantities(UBound(washerSizes))
    
    ' Iterate through all possible combinations (from largest to smallest to speed up)
    Dim q1 As Integer, q2 As Integer, q3 As Integer, q4 As Integer, q5 As Integer, q6 As Integer
    For q1 = 0 To targetSize \ washerSizes(0)
        For q2 = 0 To (targetSize - q1 * washerSizes(0)) \ washerSizes(1)
            For q3 = 0 To (targetSize - q1 * washerSizes(0) - q2 * washerSizes(1)) \ washerSizes(2)
                For q4 = 0 To (targetSize - q1 * washerSizes(0) - q2 * washerSizes(1) - q3 * washerSizes(2)) \ washerSizes(3)
                    For q5 = 0 To (targetSize - q1 * washerSizes(0) - q2 * washerSizes(1) - q3 * washerSizes(2) - q4 * washerSizes(3)) \ washerSizes(4)
                        Dim remaining As Integer
                        remaining = targetSize - (q1 * washerSizes(0) + q2 * washerSizes(1) + q3 * washerSizes(2) + q4 * washerSizes(3) + q5 * washerSizes(4))
                        
                        ' Check if remaining size is divisible by the smallest washer
                        If remaining Mod washerSizes(5) = 0 Then
                            q6 = remaining \ washerSizes(5)
                            Dim totalCount As Integer
                            totalCount = q1 + q2 + q3 + q4 + q5 + q6
                            
                            ' Update best solution if this combination uses fewer washers
                            If totalCount < minTotal Then
                                minTotal = totalCount
                                bestQuantities(0) = q1
                                bestQuantities(1) = q2
                                bestQuantities(2) = q3
                                bestQuantities(3) = q4
                                bestQuantities(4) = q5
                                bestQuantities(5) = q6
                            End If
                        End If
                    Next q5
                Next q4
            Next q3
        Next q2
    Next q1
    
    ' Write the best solution back to the spreadsheet (B1:B6)
    Range("B1:B6").Value = Application.Transpose(bestQuantities)
    Range("D8").Value = minTotal
End Sub

Step 2: Run the Macro #

1. Go back to your spreadsheet and enter your target size in cell D9.
2. Go to the Developer tab → Click Macros → Select FindMinWashers → Click Run.

The macro will fill in the optimal quantities in B1:B6 and show the minimal count in D8.

Quick Manual Greedy Check #

If you want a no-tool way to estimate quickly:

• Start with the largest washer, use as many as possible without exceeding the target.
• Take the remaining size and repeat with the next largest washer.
• If you end up with a remainder that can't be covered by the smallest washer, reduce the count of the previous washer by 1 and try again.

For 83mm: 51mm ×1 leaves 32mm, which is exactly one green washer—done in 2 steps!

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