Excel 2019：获取矩阵最小行中首个1所在列的替代公式

替代公式1：数组公式（需按Ctrl+Shift+Enter输入） #

=MATCH(1,INDEX(MyRange,MIN(IF(MyRange=1,ROW(MyRange)-ROW(INDEX(MyRange,1,1))+1,"")),0),0)+COLUMN(INDEX(MyRange,1,1))-1

公式说明： #

• ROW(INDEX(MyRange,1,1)) 获取区域首行行号，以此计算每个单元格在区域内的相对行号
• IF(MyRange=1,ROW(MyRange)-ROW(INDEX(MyRange,1,1))+1,"") 筛选出所有值为1的单元格对应的相对行号，非1单元格返回空文本
• MIN(...) 从筛选结果中取最小值，得到存在1的最小行（区域内相对行）
• INDEX(MyRange,相对行号,0) 提取该行的所有列数据
• MATCH(1,提取的行数据,0) 找到该行中首个1的相对列号
• 最后加上区域首列列号减1，转换为绝对列号（若只需区域内相对列号，可去掉+COLUMN(INDEX(MyRange,1,1))-1部分）

替代公式2：无需数组输入（用AGGREGATE函数） #

=MATCH(1,INDEX(MyRange,AGGREGATE(15,6,(ROW(MyRange)-ROW(INDEX(MyRange,1,1))+1)/(MyRange=1),1),0),0)+COLUMN(INDEX(MyRange,1,1))-1

公式说明： #

• AGGREGATE(15,6,(ROW(MyRange)-ROW(INDEX(MyRange,1,1))+1)/(MyRange=1),1) 利用AGGREGATE忽略错误值的特性，直接提取存在1的最小相对行号（无需数组输入）
• 后续步骤和公式1一致，提取目标行并找到首个1的列号，转换为绝对列号

示例验证（以A2:G5为例） #

• 区域中存在1的最小行是第3行（对应区域内相对行2）
• 提取该行（A3:G3）后，MATCH找到首个1在第5列（E列），加上首列列号1减1，最终结果为5，符合需求

这两个公式都避开了TEXTJOIN的字符数限制，可轻松处理包含10000+元素的大区域。

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