Excel分期已付金额自动分配：客户切换识别公式咨询

假设你的数据表头在第1行，数据从第2行开始，列对应：

• A列：Customer no
• B列：Installment no
• C列：Installment Value
• D列：Total Paid
• E列：Inst Value Paid（待填充）

适用于Excel 365/2021（动态数组版本） #

在E2单元格输入以下公式，按回车后公式会自动填充到下方对应行：

=LET(
    cust,A2,
    totalPaid,SUMIFS(D:D,A:A,cust),
    priorInstallments,SUMIFS(C:C,A:A,cust,B:B,"<"&B2),
    available,MAX(totalPaid - priorInstallments,0),
    MIN(C2,available)
)

公式说明： #

1. LET 函数定义变量简化逻辑，先获取当前行的客户编号cust；
2. SUMIFS(D:D,A:A,cust) 汇总当前客户的全部总付款金额（解决同一客户仅首行有总付款的问题）；
3. SUMIFS(C:C,A:A,cust,B:B,"<"&B2) 计算当前客户已排序的所有前期分期金额总和；
4. MAX(totalPaid - priorInstallments,0) 算出当前分期可分配的剩余金额（避免出现负数）；
5. MIN(C2,available) 取当前分期金额和可分配金额的较小值，确保不超过分期应缴额。

适用于旧版Excel（无动态数组） #

在E2单元格输入以下公式，手动下拉填充到所有行：

=MAX(0,MIN(C2,SUMIF($A$2:$A$100,A2,$D$2:$D$100)-SUMIFS($C$2:$C1,$A$2:$A1,A2,$B$2:$B1,"<"&B2)))

注：将公式中的$A$2:$A$100和$D$2:$D$100替换为你实际的数据范围。

公式说明： #

• SUMIF($A$2:$A$100,A2,$D$2:$D$100) 汇总当前客户的总付款；
• SUMIFS($C$2:$C1,$A$2:$A1,A2,$B$2:$B1,"<"&B2) 计算当前行之前的同客户分期金额总和（下拉时范围自动扩展）；
• MAX(0,MIN(...)) 确保分配金额非负且不超过当前分期应缴额。

关键前提 #

数据必须按Customer no和Installment no升序排序，确保同一客户的分期按顺序排列，否则公式无法正确计算前期分期总和。

内容的提问来源于stack exchange，提问作者João Scotelaro