Google Sheets：如何在MAXIFS函数中集成非常规日期并移除辅助列

需求背景 #

需要基于邮箱域名匹配和日期筛选提取数据，日期为带UTC的非常规格式（例如2023-01-10 01:21:45 UTC）。原本通过辅助列D转换日期简化公式，现需将日期转换逻辑直接整合到主公式中，移除对辅助列的依赖。

当前依赖辅助列的公式 #

=filter('Sample Data for LOOKUP'!E:E,'Sample Data for LOOKUP'!D:D=MAXIFS('Sample Data for LOOKUP'!D:D,'Sample Data for LOOKUP'!B:B,"<>",ARRAYFORMULA(REGEXREPLACE('Sample Data for LOOKUP'!A:A,"(.+@)",)),REGEXREPLACE(B2,"(.+@)",)),(REGEXREPLACE(B2,"(.+@)",)=ARRAYFORMULA(REGEXREPLACE('Sample Data for LOOKUP'!A:A,"(.+@)",))),('Sample Data for LOOKUP'!B:B<>""))

原替换尝试失败原因 #

直接用ARRAYFORMULA(DATEVALUE(left(C2:C14,10)))替换辅助列引用时，MAXIFS无法直接处理嵌套生成的数组，导致公式失效。

修改后的无辅助列公式 #

=FILTER(
  'Sample Data for LOOKUP'!E:E,
  ARRAYFORMULA(DATEVALUE(LEFT('Sample Data for LOOKUP'!C:C,10)))=
    MAX(IF(
      ('Sample Data for LOOKUP'!B:B<>"")*
      (REGEXREPLACE('Sample Data for LOOKUP'!A:A,"(.+@)",)=REGEXREPLACE(B2,"(.+@)",)),
      ARRAYFORMULA(DATEVALUE(LEFT('Sample Data for LOOKUP'!C:C,10))),
      0
    )),
  REGEXREPLACE(B2,"(.+@)",)=ARRAYFORMULA(REGEXREPLACE('Sample Data for LOOKUP'!A:A,"(.+@)",)),
  'Sample Data for LOOKUP'!B:B<>""
)

关键调整说明 #

• 用MAX+IF组合替代MAXIFS：先通过IF筛选出B列非空且邮箱域名匹配的记录，再提取对应日期的最大值，适配数组转换后的计算逻辑
• 直接内嵌日期转换：通过ARRAYFORMULA(DATEVALUE(LEFT('Sample Data for LOOKUP'!C:C,10)))从C列的非常规日期中提取日期部分并转换为标准日期格式
• 保留原有核心筛选逻辑：维持邮箱域名匹配（提取@后部分对比）和B列非空的过滤规则

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