如何自动化分配候选至矩阵位置并优化E18总平均距离（含40英里限制）

问题概述 #

需将B列的候选住所分配到第3行的各个位置，已通过自定义VBA计算所有候选-位置对的距离。核心需求：

1. 自动完成最优分配，使E18单元格的总平均距离最小；
2. 尽可能保证分配的候选-位置距离不超过40英里；
3. 支持150+规模的候选/位置数据集，替代手动操作。

解决方案 #

方案1：Excel规划求解（快速实现，无需额外编码） #

利用Excel内置工具快速配置，适合非开发人员：

• 步骤1：定义决策变量：用单独一列（如F列）记录每个候选住所对应的分配位置（名称/索引），初始值可随意填充。
• 步骤2：设置目标：选中E18单元格，设置求解目标为「最小值」。
• 步骤3：添加约束：
  • 为每个候选的分配距离添加约束：对应距离单元格（如C列及以后的距离矩阵单元格）≤40（若需严格优先满足设为硬约束；允许例外则改为软约束，见下方注意事项）。
  • 确保每个候选仅分配到有效位置：通过数据验证限制决策变量为第3行的位置值。
• 步骤4：执行求解：选择「单纯线性规划」或「非线性规划」方法，点击求解。大数据集下可在规划求解选项中调高最大迭代次数、降低精度阈值提升效率。

方案2：VBA自定义优化（灵活适配复杂场景） #

结合现有VBA距离计算逻辑，通过代码调用规划求解或实现自定义算法，适配大规模数据集：

Sub OptimizeCandidateAssignments()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    ' 读取候选与位置数量（根据实际表格结构调整行/列起始）
    Dim candidateRowStart As Integer, locationColStart As Integer
    candidateRowStart = 4 ' 假设候选从第4行开始
    locationColStart = 3 ' 假设位置从C列开始
    
    Dim candidateCount As Integer, locationCount As Integer
    candidateCount = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row - candidateRowStart + 1
    locationCount = ws.Cells(3, ws.Columns.Count).End(xlToLeft).Column - locationColStart + 1
    
    ' 调用Excel规划求解完成优化
    SolverReset
    ' 设置目标：最小化E18的总平均距离
    SolverOk SetCell:=ws.Range("E18"), MaxMinVal:=2, ValueOf:=0, _
        ByChange:=ws.Range("F" & candidateRowStart & ":F" & candidateRowStart + candidateCount - 1)
    ' 添加距离≤40的约束（G列为每个候选分配后的对应距离，需用公式关联）
    SolverAdd CellRef:=ws.Range("G" & candidateRowStart & ":G" & candidateRowStart + candidateCount - 1), _
        Relation:=1, FormulaText:="40"
    ' 执行求解，完成后自动保存模型
    SolverSolve UserFinish:=True
    SolverSave SaveArea:=ws.Range("H1")
End Sub

• 前置准备：需在Excel中启用「规划求解加载项」，并在VBA编辑器的工具引用中勾选「Solver」。
• 优化方向：若内置规划求解效率不足，可实现遗传算法、分支定界等自定义优化逻辑，提升大规模数据集的求解速度。

关键注意事项 #

• 软约束处理：若“尽可能≤40英里”允许少量例外，可修改目标函数为「总平均距离 + 超出40英里的距离×惩罚系数」（如惩罚系数设为100），让求解器优先选择符合距离限制的分配，仅在必要时选择超出项。
• 数据集优化：提前过滤距离超过40英里的候选-位置对，缩小求解空间，提升大数据集下的求解效率。

内容的提问来源于stack exchange，提问作者Ryan D Sullivan