Google Sheets周度流动性表：recurring costs匹配求和公式求助

核心公式（单单元格，可拖动填充） #

将以下公式放入流动性表的第一个数据单元格（例如B2），然后横向/纵向拖动填充整个数据区域：

=LET(
  costName, $A2,
  targetDate, B$1,
  costData, FILTER('Recurring Costs'!$A:$Z, 'Recurring Costs'!$A:$A=costName),
  dates, FLATTEN(INDEX(costData, 0, 5):INDEX(costData, 0, COLUMNS(costData))),
  amounts, INDEX(costData, 0, 4),
  SUM(IF(TEXT(dates, "yyyy-mm-dd")=TEXT(targetDate, "yyyy-mm-dd"), amounts, 0))
)

按周汇总适配（如果首行是周标识） #

如果你的流动性表首行是周编号（例如2024-W01），将公式中的日期匹配部分改为周格式：

=LET(
  costName, $A2,
  targetWeek, TEXT(B$1, "yyyy-ww"),
  costData, FILTER('Recurring Costs'!$A:$Z, 'Recurring Costs'!$A:$A=costName),
  dates, FLATTEN(INDEX(costData, 0, 5):INDEX(costData, 0, COLUMNS(costData))),
  amounts, INDEX(costData, 0, 4),
  SUM(IF(TEXT(dates, "yyyy-ww")=targetWeek, amounts, 0))
)

一键生成全表（无需拖动填充） #

如果希望一次性生成所有单元格的结果，使用以下数组公式（同样放入B2）：

=ARRAYFORMULA(
  MAP(A2:A, B1:Z1, LAMBDA(costName, targetDate,
    IF(ISBLANK(costName) OR ISBLANK(targetDate),,
      LET(
        costData, FILTER('Recurring Costs'!$A:$Z, 'Recurring Costs'!$A:$A=costName),
        dates, FLATTEN(INDEX(costData, 0, 5):INDEX(costData, 0, COLUMNS(costData))),
        amounts, INDEX(costData, 0, 4),
        SUM(IF(TEXT(dates, "yyyy-mm-dd")=TEXT(targetDate, "yyyy-mm-dd"), amounts, 0))
      )
    )
  ))

公式逻辑说明 #

1. LET函数：定义变量简化公式，避免重复计算
   • costName：当前行对应的费用名称（引用A列）
   • targetDate/targetWeek：当前列对应的目标日期/周（引用首行）
   • costData：从周期性成本表筛选出当前费用的所有行数据
2. FLATTEN函数：将当前费用的多列账单日期转换为一维数组，方便统一匹配
3. SUM+IF：判断每个账单日期是否匹配目标日期/周，匹配则累加对应金额

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