Excel需求：查找客户未结清的最早债务日期

问题分析 #

你需要针对客户债务/付款表格，找到客户尚未结清的最早债务日期——即付款总额覆盖到的最后一笔债务的日期（剩余未结清部分来自该笔债务）。以客户A为例，总付款800 CU覆盖了500 CU的10月债务和300 CU的11月债务，剩余100 CU未结清的部分来自11月5日的债务，因此需要返回该日期。

假设表格结构 #

假设数据位于A2:C10：

• A列：客户姓名
• B列：日期（需确保是Excel可识别的日期格式）
• C列：债务(+)/付款(-)

要查询的客户姓名输入在E2单元格。

方案1：适用于Excel 365/2021（支持动态数组） #

使用LET+SCAN+XLOOKUP组合公式，逻辑清晰且高效：

=LET(
    cust, E2,
    data, FILTER(A2:C10, A2:A10=cust),
    sorted_data, SORT(data, 2, 1),
    date_list, INDEX(sorted_data,,2),
    amount_list, INDEX(sorted_data,,3),
    cumulative_debt, SCAN(0, amount_list, LAMBDA(prev, curr, prev + IF(curr>0, curr, 0))),
    total_payment, SUM(IF(amount_list<0, -amount_list, 0)),
    XLOOKUP(TRUE, cumulative_debt>total_payment, date_list, "", 1, 1)
)

公式说明：

1. FILTER：筛选出指定客户的所有记录
2. SORT：按日期升序排序记录（确保债务/付款按时间顺序计算）
3. SCAN：计算累计债务金额（仅累加正数的债务，忽略付款）
4. SUM(IF(...))：计算客户的总付款金额（将负数付款转为正数求和）
5. XLOOKUP：找到第一个累计债务超过总付款的日期，即为未结清的最早债务日期

方案2：适用于旧版Excel（不支持动态数组） #

使用数组公式（输入后按Ctrl+Shift+Enter确认）：

=INDEX(B:B, MIN(IF(SUMIFS(C:C,A:A,E2,B:B,"<="&B:B,C:C,">0")>ABS(SUMIFS(C:C,A:A,E2,C:C,"<0")), ROW(B:B))))

公式说明：

1. SUMIFS(...)：计算每个日期及之前的累计债务总额
2. ABS(SUMIFS(...))：计算客户的总付款绝对值
3. IF(...)：筛选出累计债务超过总付款的所有行号
4. MIN(...)：取最小的行号（对应最早的日期）
5. INDEX：返回该日期

验证示例（客户A） #

• 总付款：300+400+100=800 CU
• 累计债务到10月1日：500 CU（小于800，已完全覆盖）
• 累计债务到11月5日：500+400=900 CU（大于800，剩余100 CU未结清）
• 公式返回05.11.2022，符合需求

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