Google Sheets多条件ArrayFormula、Lookup与Filter公式调试求助

问题分析 #

你原公式的核心问题有两个：

• 逻辑运算符错误：用+表示逻辑或，但你需要两个条件同时满足，应该用*表示逻辑与
• 数组维度不匹配：在ArrayFormula中直接用Result!A6:A（多行）和Deals表日期列（多行）比较，会导致维度冲突，无法正确匹配每行的日期条件

完整自动填充公式（在Result表对应单元格输入） #

=ARRAYFORMULA(
  BYROW(A6:A, LAMBDA(current_date,
    IF(current_date="",,
      SUM(
        LET(
          deals_raw, IFERROR(SPLIT(REGEXREPLACE(C6:C, " x", "|"), ", ")),
          deal_codes, INDEX(deals_raw, 0, 1),
          qty_values, INDEX(deals_raw, 0, 2)*1,
          valid_deals, FILTER(Deals!B6:D, (Deals!E6:E="all-day")*(Deals!I6:I<=current_date)*(Deals!J6:J>=current_date)),
          matched_values, XLOOKUP(deal_codes, valid_deals[Deal Code], valid_deals[Value], 0),
          matched_values*qty_values
        )
      )
    )
  ))
)

公式拆解 #

1. 逐行日期处理：通过BYROW(A6:A, LAMBDA(current_date, ...))遍历Result表每行日期，确保每个日期单独匹配条件，避免数组维度冲突
2. 拆分交易代码与数量：
   • REGEXREPLACE(C6:C, " x", "|")：将"交易代码 x数量"格式替换为"交易代码|数量"
   • SPLIT(..., ", ")：按逗号拆分每个交易条目
   • INDEX分别提取交易代码列和数量列，同时将数量转为数值类型
3. 筛选符合条件的交易：
   • FILTER(Deals!B6:D, (Deals!E6:E="all-day")*(Deals!I6:I<=current_date)*(Deals!J6:J>=current_date))：筛选出Type为"all-day"且当前日期在起止日期范围内的有效交易
4. 计算行总和：
   • XLOOKUP用交易代码匹配有效交易的价值，无匹配时返回0
   • 价值与数量相乘后求和，得到该行的总交易金额

关键修正点 #

• 将原公式的+改为*，确保两个条件同时满足
• 用BYROW封装逻辑，让每行日期独立处理匹配，解决数组维度不匹配问题
• 拆分交易代码和数量后再分别计算，逻辑更清晰且避免错误

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