Excel如何提取单元格区域内每个0值之前的最大值

原公式问题说明 #

你当前使用的=MAX(FREQUENCY(IF(T2:AC2>1,ROW(T2:AC2)),IF(T2:AC21>=1,ROW(T2:AC2))))存在两处错误：一是区间引用不一致，T2:AC21应为和前半段匹配的T2:AC2；二是逻辑规则不符合需求，你需要按0值分割区间后取各区间的最大值，原公式的条件判断逻辑与目标不匹配。

可用公式方案 #

Excel 365/2021及以上版本（动态数组自动溢出结果） #

直接输入以下公式，会自动溢出返回2和6两个结果：

=BYCOL(TEXTSPLIT(TEXTJOIN(",",,T2:AC2),",0,"),LAMBDA(x,MAX(--TEXTSPLIT(x,","))))

逻辑说明：先把整行数据用逗号拼接为完整字符串，再以,0,为分隔符拆分出每个连续非0段的字符串，最后对每个段拆分转数值后取最大值
如果需要单独提取两个结果，可配合INDEX函数：

• 提取第一个最大值2：=INDEX(上述公式,1)
• 提取第二个最大值6：=INDEX(上述公式,2)

旧版Excel（需数组确认） #

输入公式后需同时按Ctrl+Shift+Enter三键触发数组计算，不要直接回车：

• 提取第一个最大值2：

=MAX(INDIRECT("R"&ROW(T2)&"C"&COLUMN(T2)&":R"&ROW(T2)&"C"&MIN(IF(T2:AC2=0,COLUMN(T2:AC2)))-1,FALSE))

• 提取第二个最大值6：

=MAX(INDIRECT("R"&ROW(T2)&"C"&(MIN(IF(T2:AC2=0,COLUMN(T2:AC2)))+1)&":R"&ROW(T2)&"C"&MAX(IF(T2:AC2=0,COLUMN(T2:AC2)))-1,FALSE))

效果验证 #

对你给出的示例行数据1 2 0 1 2 3 4 5 6 0，以上公式均可准确返回目标结果2、6。

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