如何获取6×6表格中LARGE函数提取的前10大值对应的行和列标题

前置说明 #

假设你的6×6数值区域为B2:G7，行标题区域为A2:A7，列标题区域为B1:G1，你提取的前10名数值存储在I2:I11区域。

方法1：Excel 365/2021 动态数组版本（一次性生成所有结果） #

直接在空白单元格输入如下公式，会自动生成三列结果，分别对应「前10数值」「匹配行标题」「匹配列标题」：

=LET(
    data,$B$2:$G$7,
    row_h,$A$2:$A$7,
    col_h,$B$1:$G$1,
    top10,LARGE(data,SEQUENCE(10)),
    pos,XMATCH(top10,TOCOL(data)),
    HSTACK(top10,INDEX(row_h,INT((pos-1)/COLUMNS(data))+1),INDEX(col_h,MOD(pos-1,COLUMNS(data))+1))
)

方法2：旧版本Excel（2019及更早，支持数组公式） #

• 选中J2单元格（对应I2数值的行标题位置），输入如下公式，按Ctrl+Shift+Enter三键触发数组计算：
  =INDEX($A$2:$A$7,MIN(IF($B$2:$G$7=I2,ROW($A$2:$A$7)-ROW($A$1),999)))
• 选中K2单元格（对应I2数值的列标题位置），输入如下公式，同样按Ctrl+Shift+Enter三键触发数组计算：
  =INDEX($B$1:$G$1,MIN(IF($B$2:$G$7=I2,COLUMN($B$1:$G$1)-COLUMN($A$1),999)))
• 选中J2、K2单元格，下拉填充到第11行，即可得到所有前10数值对应的标题。

注意事项 #

• 如果数据中存在重复的大数值，上述旧版本公式默认返回第一个匹配到的标题。如果需要提取所有重复值对应的不同标题，可以在公式中加入COUNTIF(I$2:I2,I2)的计数条件，匹配第N次出现的对应位置。
• 公式中的区域引用需要保留$绝对引用符号，避免下拉填充时区域偏移。

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