如何在Excel中获取矩阵前3大值对应的行ID和列ID？

方法1：Excel 365/2021（动态数组版本） #

如果你的Excel支持动态数组函数，可直接用以下公式一次性生成完整结果（行标题、列标题、数值）：

• 在空白单元格输入公式，会自动溢出3行3列的结果：

  =LET(
      data,B2:D4,
      row_headers,A2:A4,
      col_headers,B1:D1,
      flattened,TOCOL(HSTACK(row_headers,TOCOL(data,,1),TOROW(col_headers,1,1))),
      sorted,SORTBY(flattened,INDEX(flattened,,2),-1),
      TAKE(sorted,3)
  )
  

  变量说明：
  • data：替换为你的数值矩阵区域
  • row_headers：替换为行标题区域
  • col_headers：替换为列标题区域

如果已经用LARGE函数得到了前3大值（比如存于F2:F4），可单独提取对应标题：

• 行标题（G2单元格，下拉填充）：

  =INDEX(A2:A4,AGGREGATE(15,6,(ROW(B2:D4)-ROW(B2)+1)/(B2:D4=F2),COUNTIF($F$2:F2,F2)))
  

• 列标题（H2单元格，下拉填充）：

  =INDEX(B1:D1,AGGREGATE(15,6,(COLUMN(B2:D4)-COLUMN(B2)+1)/(B2:D4=F2),COUNTIF($F$2:F2,F2)))
  

方法2：旧版Excel（无动态数组） #

旧版Excel需使用数组公式（输入后按Ctrl+Shift+Enter确认生效）：

• 行标题（G2单元格，数组公式，下拉填充）：

  =INDEX($A$2:$A$4,SMALL(IF($B$2:$D$4=F2,ROW($B$2:$D$4)-ROW($B$2)+1),COUNTIF($F$2:F2,F2)))
  

• 列标题（H2单元格，数组公式，下拉填充）：

  =INDEX($B$1:$D$1,SMALL(IF($B$2:$D$4=F2,COLUMN($B$2:$D$4)-COLUMN($B$2)+1),COUNTIF($F$2:F2,F2)))
  

关键说明 #

• 所有公式中的单元格范围需根据你的实际表格调整
• 公式支持处理重复最大值的情况，会依次匹配不同位置的行/列标题，避免重复指向同一个单元格

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