Excel公式实现送礼后人员间具体欠款金额自动核算咨询

第一步：计算人均承担额与个人收支差额 #

假设总支出在A3:C3（对应Larry、John、Chris的总支出），先在D3单元格计算人均金额：

=SUM(A3:C3)/COUNTA(A3:C3)

用COUNTA适配人数变动场景，无需硬写固定人数。

接着在A4计算Larry的收支差额（多付为正，少付为负）：

=A3-$D$3

拖动填充到B4、C4，得到对应结果：

• Larry：$20-$40 = -$20（欠付20）
• John：$35-$40 = -$5（欠付5）
• Chris：$65-$40 = +$25（应收25）

第二步：生成具体欠款明细（Excel 365/2021适用） #

借助动态数组公式可自动生成欠款配对清单。先准备辅助区域：

• E2:E4依次填入姓名：Larry、John、Chris
• F2:F4分别引用对应收支差额：=A4、=B4、=C4

在H2单元格输入以下公式，自动生成明细：

=LET(
    debtors, FILTER(E2:E4, F2:F4<0),
    debts, ABS(FILTER(F2:F4, F2:F4<0)),
    creditors, FILTER(E2:E4, F2:F4>0),
    credits, FILTER(F2:F4, F2:F4>0),
    result, REDUCE("", SEQUENCE(ROWS(debtors)), LAMBDA(a,i,
        REDUCE(a, SEQUENCE(ROWS(credits)), LAMBDA(b,j,
            LET(
                transfer, MIN(INDEX(debts,i), INDEX(credits,j)),
                IF(transfer>0,
                    VSTACK(b, HSTACK(INDEX(debtors,i), "欠", INDEX(creditors,j), "$"&transfer)),
                    b
                )
            )
        ))
    ),
    IFERROR(DROP(result,1), "无欠款")
)

公式逻辑：

1. 筛选出欠款人（差额为负）和收款人（差额为正）
2. 逐个配对双方，每次转付两者中金额较小的部分，直到某一方债务/债权清零
3. 最终输出类似Larry 欠 Chris $15、John 欠 Chris $5的明细

若数据变动（比如Larry总支出变为$100），公式会自动更新：

• 人均金额变为(100+35+65)/3=$66.67
• 收支差额：Larry+$33.33（应收）、John-$31.67（欠付）、Chris-$1.67（欠付）
• 自动生成John 欠 Larry $31.67、Chris 欠 Larry $1.67的结果

第三步：旧版Excel（无动态数组）替代方案 #

若使用无LET/REDUCE函数的旧版Excel，可通过辅助列手动配对：

1. 分别列出欠款人及其欠付额、收款人及其应收额
2. 用公式计算单次转付金额：

=IF(MIN(欠付额, 应收额)>0, 欠款人&" 欠 "&收款人&" $"&MIN(欠付额, 应收额), "")

3. 联动更新剩余的欠付/应收额，筛选非空结果得到明细

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