如何在Google Sheets中计算含全额与分期的月度收款金额

假设你的数据表结构如下（可根据实际列调整引用）：

• A列：付款记录日期
• C列：分期期数（全额付款留空或填1）
• D列：单笔付款金额

方法：拆分分期记录后按月汇总 #

使用ARRAYFORMULA+FLATTEN将每笔分期拆分成对应月份的付款条目，再用QUERY按月统计总金额：

=QUERY(
  ARRAYFORMULA(
    {
      // 生成所有付款对应的月份（当月第一天，用于分组）
      FLATTEN(
        IF(
          ISBLANK(C2:C),
          EOMONTH(A2:A, 0) + 1,
          EOMONTH(A2:A, SEQUENCE(1, C2:C, 0, 1)) + 1
        )
      ),
      // 生成对应每个月份的付款金额
      FLATTEN(
        IF(
          ISBLANK(C2:C),
          D2:D,
          D2:D * SEQUENCE(1, C2:C, 1, 0)
        )
      )
    }
  ),
  "SELECT Col1, SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1 FORMAT Col1 'yyyy-MM', SUM(Col2) '#,##0.00 EUR'",
  0
)

公式说明 #

1. 拆分分期月份：
   • 全额付款：仅保留记录日期的当月第一天（EOMONTH(A2:A, 0) +1）
   • 分期付款：根据期数N，生成从记录当月开始的连续N个月份的第一天（SEQUENCE(1, C2:C, 0,1)生成0到N-1的序列，配合EOMONTH得到对应月份）
2. 拆分金额：
   • 全额付款：直接取原金额
   • 分期付款：重复N次原金额（SEQUENCE(1, C2:C,1,0)生成N个1，乘以原金额得到等额的N笔金额）
3. 汇总统计：
   • QUERY按月份分组，求和金额，并格式化日期为yyyy-MM、金额为带欧元符号的格式

适配调整 #

如果你的数据表用单独列区分付款类型（如B列是「全额付款」/「分期付款」），可将公式中的ISBLANK(C2:C)替换为B2:B = "全额付款"。

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