Excel - 排名前 3 名的学生

Excel - 排名前 3 名的学生

我的问题的屏幕截图

这是与我的问题相关的图片。谢谢。

答案1

如果您有带有动态数组和SORTFILTER函数的 Excel O365,则可以使用:

(请注意,我将姓名/分数数据转换为桌子并使用结构化引用。如果您愿意,可以将其转换为常规引用)

F4: =ROW(INDEX($A:$A,1):INDEX($A:$A,COUNTA(G4#)))
G4: =INDEX(SORT(FILTER(tblStudRank,tblStudRank[Marks]>=LARGE(tblStudRank[Marks],3)),2,-1),0,1)

在此处输入图片描述

简要算法:

  • SORT表格Marks并返回所有前三个值
  • 1..COUNT of values returned为排名生成一个数组

编辑:以上返回的结果类似于您的#2。

要返回像 #1 这样的结果,请使用

F4: =IFERROR(SUMPRODUCT((tblStudRank[Marks]> XLOOKUP(G4,tblStudRank[Student Name],tblStudRank[Marks]))/COUNTIF(tblStudRank[Marks],tblStudRank[Marks]))+1,"")

并向下填充

G4: =INDEX(SORT(FILTER(tblStudRank,tblStudRank[Marks]>=LARGE(UNIQUE(tblStudRank[Marks]),3)),2,-1),0,1)

在此处输入图片描述

答案2

此方法适用范围无限,因为适用于任何版本的 Excel

在此处输入图片描述

怎么运行的:

  • 单元格中的公式S148

    =SUMPRODUCT((R148<=$R$148:$R$157)/COUNTIF($R$148:$R$157,$R$148:$R$157))
    
  • 单元格中的公式T148

    =IF(ROW(A1)<=4,SMALL(S$148:S$157,ROW(A1)),"")

  • 单元格中的最终数组(CSE)公式U148

    {=IFERROR(INDEX(Q:Q,SMALL(IF(S$148:S$157=T148,ROW(S$148:S$157)),COUNTIF(T$148:T148,T148))),"")}
    
  • 完成公式Ctrl+Shift+Enter

  • 根据需要调整公式中的单元格引用。

相关内容