Excel中含指定子串列的MAX/SUM公式实现及动态范围匹配需求

一、单元格K2：提取含"Date"子串列的最新日期 #

如果你的Excel是365/2021及以上版本，直接用下面的公式就能自动遍历表头，筛选出含"Date"的列并取第二行的最大值：

=MAX(BYCOL(1:1, LAMBDA(header, IF(ISNUMBER(SEARCH("Date", header)), 2:2, ""))))

• 逻辑：BYCOL遍历第一行的所有表头，SEARCH("Date", header)检查表头是否包含"Date"子串，符合条件就返回对应第二行的单元格值，最后用MAX取最大值。

如果是旧版Excel，用数组公式（输入后按Ctrl+Shift+Enter确认）：

=MAX(IF(ISNUMBER(SEARCH("Date", $1:$1)), $2:$2))

• 逻辑：先判断第一行每个表头是否含"Date"，生成布尔数组，对应第二行的单元格值会被保留，最后MAX提取最大值。

要是你想限定在"Date 1"到"Qty 5"的列范围内计算，结合MATCH定位列边界：

=MAX(IF(ISNUMBER(SEARCH("Date", OFFSET($1:$1,0,MATCH("Date 1",$1:$1,0)-1,1,MATCH("Qty 5",$1:$1,0)-MATCH("Date 1",$1:$1,0)+1))), OFFSET($2:$2,0,MATCH("Date 1",$1:$1,0)-1,1,MATCH("Qty 5",$1:$1,0)-MATCH("Date 1",$1:$1,0)+1)))

• 逻辑：先用MATCH找到"Date 1"和"Qty 5"的列号，用OFFSET框定这个范围内的表头和对应第二行数据，再筛选含"Date"的列取最大值。

二、单元格L2：计算含"Qty"子串列的求和 #

同样分版本给出公式：

365/2021版本 #

=SUM(BYCOL(1:1, LAMBDA(header, IF(ISNUMBER(SEARCH("Qty", header)), 2:2, 0))))

旧版Excel数组公式（按Ctrl+Shift+Enter确认） #

=SUM(IF(ISNUMBER(SEARCH("Qty", $1:$1)), $2:$2))

限定"Date 1"到"Qty 5"范围的版本 #

=SUM(IF(ISNUMBER(SEARCH("Qty", OFFSET($1:$1,0,MATCH("Date 1",$1:$1,0)-1,1,MATCH("Qty 5",$1:$1,0)-MATCH("Date 1",$1:$1,0)+1))), OFFSET($2:$2,0,MATCH("Date 1",$1:$1,0)-1,1,MATCH("Qty 5",$1:$1,0)-MATCH("Date 1",$1:$1,0)+1)))

注意：MAXIFS/SUMIFS不适合这个场景，因为这两个函数是针对行维度的条件匹配，而我们需要按列表头的条件筛选列，所以用数组公式或BYCOL函数更合适。

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