匹配数组中第N大值所在工作表名称的问题咨询

错误根源 #

• LARGE 函数原生支持连续跨工作表的单元格引用语法，因此你使用的 =LARGE('SHEET1:SHEET85'!D25, 1) 可以正常返回结果
• 但 INDEX 函数的第一个参数要求为合法的单元格区域或数组，'SHEET1:SHEET85' 仅能用于跨表单元格引用场景，不能直接作为工作表名称数组传入函数，因此触发报错

可行解决方法 #

方案1：适用于Excel 365/2021版本（无需辅助列） #

直接输入如下公式即可，将公式末尾的1替换为你需要的排名即可（示例为匹配第1大值对应的工作表名）：

=LET(
  sheet_list, TEXTAFTER(GET.WORKBOOK(1),"]",,1),
  d_value_list, BYROW(sheet_list, LAMBDA(x, INDIRECT("'"&x&"'!D25"))),
  target, LARGE(d_value_list,1),
  FILTER(sheet_list, d_value_list=target)
)

注意：使用宏表函数GET.WORKBOOK需要将文件另存为.xlsm格式的启用宏工作簿。

方案2：适用于所有Excel版本（使用辅助列） #

1. 按快捷键Ctrl+F3打开名称管理器，点击「新建」：
   • 名称栏输入：All_Sheets
   • 引用位置输入：=GET.WORKBOOK(1)&T(NOW())
   • 点击确定保存
2. 在空白列A列的A1单元格输入如下公式，下拉填充到A85，生成SHEET1到SHEET85的所有工作表名：

   =INDEX(TEXTAFTER(All_Sheets,"]"),ROW(A1))
   

3. 在B列B1单元格输入如下公式，下拉填充到B85，拉取每个工作表D25单元格的数值：

   =INDIRECT("'"&A1&"'!D25")
   

4. 最后使用如下公式匹配对应排名的工作表名，将1替换为你需要的排名即可：

   =INDEX(A:A,MATCH(LARGE(B:B,1),B:B,0))
   

重复值处理说明 #

如果存在多个工作表D25单元格数值相同的情况，上述公式默认返回第一个匹配到的工作表名。如果需要返回所有匹配的工作表名，Excel 365版本可直接用TEXTJOIN包裹FILTER结果合并输出，旧版可搭配辅助列逐个提取。

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