Excel中将负账户余额分配至正账户实现最小剩余差额的方案咨询

以下两种方案都可满足单/多账户交叉冲抵需求，最终剩余差额控制在0.01元以内： #

方案1：Power Query 无代码实现（适配Excel 2016及以上版本） #

无需编写公式或代码，数据更新后点击刷新即可重新计算，适合规则固定的场景：

• 选中A、B列原始数据，点击「数据」选项卡 → 「自表格/区域」，导入Power Query编辑器，确认第一行作为表头，设置两列表头为账户名、余额
• 拆分正负余额为两个独立表：
  • 正余额表：筛选余额>0，新增列剩余可分配=[余额]
  • 负余额表：筛选余额<0，新增列待分配额=-[余额]（将负数转为正数方便计算）
• 添加自定义匹配列，按优先顺序用正余额剩余额度覆盖负余额待分配额，每次分配后同步扣减两边的剩余/待分配数值，直至待分配额清零
• 按账户名分组聚合，将同一账户对应的所有来源账户、分配金额合并为逗号分隔的文本，导出回Excel即可自动填充C、D列

方案2：VBA脚本实现（适合批量处理上百个账户，执行效率更高） #

支持灵活自定义分配规则，运行一次即可完成全量计算：

使用方法 #

1. 按Alt+F11打开VBA编辑器，右键点击当前工作簿 → 「插入」→ 「模块」
2. 将下方代码粘贴到模块编辑区，保存后关闭编辑器
3. 回到Excel界面按Alt+F8，选择余额冲抵分配脚本运行即可

代码示例 #

Sub 余额冲抵分配()
    Dim lastRow As Long, i As Long, j As Long
    Dim negAmt As Double, posAmt As Double, allocAmt As Double
    ' 获取数据最后一行行号
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    ' 清空C、D列历史数据
    Range("C2:D" & lastRow).ClearContents
    
    ' 遍历所有负余额账户
    For i = 2 To lastRow
        If Cells(i, "B").Value < 0 Then
            negAmt = -Cells(i, "B").Value ' 转换为待分配的正金额
            ' 遍历正余额账户匹配可分配额度
            For j = 2 To lastRow
                If Cells(j, "B").Value > 0 And negAmt > 0.001 Then
                    posAmt = Cells(j, "B").Value
                    allocAmt = WorksheetFunction.Min(negAmt, posAmt)
                    ' 写入分配记录
                    If Cells(i, "C").Value = "" Then
                        Cells(i, "C").Value = Cells(j, "A").Value
                        Cells(i, "D").Value = Round(allocAmt, 2)
                    Else
                        Cells(i, "C").Value = Cells(i, "C").Value & "," & Cells(j, "A").Value
                        Cells(i, "D").Value = Cells(i, "D").Value & "," & Round(allocAmt, 2)
                    End If
                    ' 扣减已分配金额
                    negAmt = negAmt - allocAmt
                    Cells(j, "B").Value = Round(Cells(j, "B").Value - allocAmt, 2)
                End If
            Next j
            ' 标注未分配的剩余差额
            If negAmt > 0.001 Then
                Cells(i, "D").Value = Cells(i, "D").Value & ",剩余未分配:" & Round(negAmt, 2)
            End If
        End If
    Next i
    MsgBox "分配完成，未补足差额已标注在对应行D列末尾"
End Sub

注意：运行脚本前请先备份原始B列余额数据，脚本运行过程中会修改B列数值用于计算剩余可分配额度 #

补充说明 #

如果需要调整分配优先级（比如优先冲抵大额/小额余额），只要提前对B列按需求排序后再执行对应方案即可。

内容的提问来源于stack exchange，提问作者A. Wentworth