如何在Google Sheets中高效计算各贷款的累计待付款（含币种区分）

AI 小北

2026-6-24

高效实现贷款待付款计算的数组公式方案

问题背景

在loans工作表中，需为每笔贷款计算待付款金额，要求：

区分贷款ID（C列）和币种（E列）
仅计算到期日（B列）≤今日的贷款
待付款=该贷款ID+币种的累计放款额（当前行及之前同组的金额总和）- 对应ID+币种的累计付款额（来自payments工作表）
结果为正显示数值，否则显示0
必须使用单个数组公式，禁止手动拖拽

原方案的性能问题

第一个公式使用MMULT进行矩阵运算，当数据行数较多时，计算复杂度为O(n²)，导致表格大幅卡顿
第二个公式多次调用SORT+MATCH进行分组累计，重复的排序和匹配操作会累积性能开销，仍存在卡顿问题

高效解决方案

在loans工作表的H1单元格输入以下数组公式：

={"pending amount";
ARRAYFORMULA(
  IF(
    LEN(C2:C)*(B2:B<=TODAY()),
    LET(
      total_loans, SUMIFS(D2:D, C2:C, C2:C, E2:E, E2:E, ROW(D2:D), "<="&ROW(D2:D)),
      total_payments, SUMIFS(Payments!C2:C, Payments!B2:B, C2:C, Payments!D2:D, E2:E),
      pending, total_loans - total_payments,
      IF(pending>0, pending, 0)
    ),
    ""
  )
)}