答案1
如果您有带有动态数组和SORT
和FILTER
函数的 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
根据需要调整公式中的单元格引用。