Google Sheets中ARRAYFORMULA嵌套MAXIFS失效求助

你遇到的问题其实很常见——MAXIFS和ARRAYFORMULA组合时，无法正确逐行迭代数组参数，这就是为什么只有第一行正常，后面全部重复第一行结果的原因。MAXIFS默认会把整个条件数组（比如A2:A）当作一个整体去匹配，而不是对应每一行的单个值。下面给你两种可靠的解决方案：

方案1：用BYROW+LAMBDA逐行处理（推荐，灵活度高） #

1. 获取最新日期（对应你的B列公式） #

替换原来的公式为：

=BYROW(A2:A, LAMBDA(x, 
  IF(x="", "", 
    LET(max_date, MAX(FILTER(Sheet1!A:A, Sheet1!E:E=x)), 
      IF(max_date=0, "", max_date)
    )
  )
))

• 原理：BYROW会遍历A2:A的每一个单元格值x，然后用FILTER筛选出Sheet1中E列等于x的所有日期，再取最大值。LET用来简化变量，避免重复计算。

2. 获取前6个递减的日期（对应C到H列） #

以C列为例（需要小于B列的日期，取第二新的），公式如下：

=BYROW(A2:A&B2:B, LAMBDA(x, 
  IF(INDEX(SPLIT(x,"|"),1)="", "", 
    LET(
      match_val, INDEX(SPLIT(x,"|"),1),
      prev_date, INDEX(SPLIT(x,"|"),2),
      filtered_dates, FILTER(Sheet1!A:A, Sheet1!E:E=match_val, Sheet1!A:A<prev_date),
      IFERROR(LARGE(filtered_dates, 1), "")
    )
  )
))

• 原理：把A列的匹配值和B列的上一个日期用|拼接，在LAMBDA里拆分后分别作为条件。LARGE(filtered_dates, 1)取筛选后日期的第1大值（也就是第二新的），后续D到H列只需要把LARGE的第二个参数改成2、3…6即可。

方案2：用QUERY+VLOOKUP实现数组匹配 #

1. 获取最新日期（B列） #

如果习惯用QUERY，可以用分组查询先得到每个E列对应的最大日期，再用VLOOKUP匹配：

=ARRAYFORMULA(
  IF(A2:A="", "", 
    VLOOKUP(A2:A, 
      QUERY(Sheet1!A:E, "SELECT E, MAX(A) WHERE E IS NOT NULL GROUP BY E"), 
      2, FALSE
    )
  )
)

• 原理：QUERY按Sheet1的E列分组，计算每个组的最大A列日期，返回一个两列的数组；VLOOKUP逐行匹配A2:A的值，获取对应的最大日期。

2. 获取前6个递减的日期（C列示例） #

结合QUERY和BYROW处理日期小于前一列的条件：

=BYROW(A2:A&B2:B, LAMBDA(x, 
  IF(INDEX(SPLIT(x,"|"),1)="", "", 
    LET(
      match_val, INDEX(SPLIT(x,"|"),1),
      prev_date, INDEX(SPLIT(x,"|"),2),
      q_result, QUERY(Sheet1!A:E, 
        "SELECT MAX(A) WHERE E = '"&match_val&"' AND A < date '"&TEXT(prev_date,"yyyy-mm-dd")&"'", 
        2
      ),
      IF(q_result="", "", q_result)
    )
  )
))

• 注意：QUERY中日期需要转换为date 'yyyy-mm-dd'格式，所以用TEXT函数处理前一列的日期。

为什么原来的MAXIFS不行？ #

当你在ARRAYFORMULA中写MAXIFS(Sheet1!A:A, Sheet1!E:E,A2:A)时，MAXIFS不会逐行对应A2:A的每个单元格，而是把整个A2:A数组作为条件去匹配Sheet1的E列，最终返回的是整个数组匹配后的单一最大值，导致所有行都重复第一行的结果。而BYROW或QUERY分组的方式，能确保每个行的条件被独立处理。

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