Excel 2016：提取最多5个含最大数值单元格的地址

问题分析 #

你之前的公式通过逐个定位的方式查找最大值单元格地址，逻辑复杂且无法高效处理第三个及以后的重复最大值位置，下面提供更简洁可靠的解决方案。

解决方案公式（数组公式） #

在B1单元格输入以下公式，按 Ctrl+Shift+Enter 确认（Excel会自动添加数组公式的大括号，不要手动输入）：

=IFERROR(SUBSTITUTE(ADDRESS(SMALL(IF($T$1:$T$118=LARGE($T$1:$T$118,1),ROW($T$1:$T$118)),ROW(A1)),20),"$",""),"")

下拉填充至B5，即可自动提取最多5个包含最大值的单元格地址，不足5个时显示空值。

公式拆解 #

• LARGE($T$1:$T$118,1)：获取T列的最大值
• IF($T$1:$T$118=LARGE(...),ROW($T$1:$T$118))：筛选出所有等于最大值的单元格行号，不符合条件的返回错误值
• SMALL(...,ROW(A1))：依次提取筛选出的行号中的第1、2、3...个（下拉时ROW(A1)自动变为ROW(A2)、ROW(A3)等）
• ADDRESS(...,20)：将行号转换为T列的单元格地址（20是T列的列序号）
• SUBSTITUTE(..."$","")：去除地址中的美元符号，得到类似T2的简洁格式
• IFERROR(...)：当没有更多符合条件的单元格时返回空值，避免显示错误

扩展：提取前5大数值的地址（含不同大小） #

如果需要提取T列中前5大的数值对应的单元格地址（无论数值是否重复），可使用以下数组公式：

=IFERROR(SUBSTITUTE(ADDRESS(SMALL(IF($T$1:$T$118>=LARGE($T$1:$T$118,5),ROW($T$1:$T$118)),ROW(A1)),20),"$",""),"")

同样按 Ctrl+Shift+Enter 确认后下拉至B5即可。

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