求优化公式！

jinsong2255

我想从无序的成绩表格中，筛选出各班的成绩，并按高低排序，公式如下：
=IFERROR(INDEX(A$2:A$17,SMALL(IF(($A$2:$A$17=$H$21)*$G$2:$G$17=LARGE(IF($A$2:$A$17=$H$21,$G$2:$G$17),ROW(1:1))),ROW($1:$16),9^9),IF(SUM(($A$2:$A$17=$H$21)*($G$2:$G$17=LARGE(IF($A$2:$A$17=$H$21,$G$2:$G$17),ROW(1:1))))>1,ROW(1:1),1))),"")

但是当相同的成绩>2对，就不能正常显示了，求各位大神帮助优化一下公式，或者提供一个更高效的公式也行，谢谢！

llpsrnfrw

=IFERROR(OFFSET(C$1,SMALL(IF(($A$2:$A$17=$H$21)*($G$2:$G$17=LARGE(IF($A$2:$A$17=$H$21,$G$2:$G$17),ROW(1:1))),ROW($1:$16),9^9),(COUNTIF($G$20:G20,LARGE(IF($A$2:$A$17=$H$21,$G$2:$G$17),ROW(1:1)))+1)),,,),"")

你公式前半段对的，后半段主要出错在于取重复排名时候出错，后半段改了一下。

具体见附件

zengyidin

供参考，数组公式

1. =INDEX(A:A,--RIGHT(LARGE(IF($A$2:$A$17=$H$21,$G$2:$G$17/1%%%+(17-ROW($2:$17))/1%+ROW($2:$17),99),ROW(A1)),2))&""

复制代码

jinsong2255

llpsrnfrw 发表于 2018-2-11 09:49
=IFERROR(OFFSET(C$1,SMALL(IF(($A$2:$A$17=$H$21)*($G$2:$G$17=LARGE(IF($A$2:$A$17=$H$21,$G$2:$G$17),RO ...

万分感谢，谢谢指导！

jinsong2255

zengyidin 发表于 2018-2-11 10:01
供参考，数组公式

万分感谢，谢谢指导！

jinsong2255

我自己也写了一个无比复杂的！
=IFERROR(INDEX(A$2:A$17,SMALL(IF(($A$2:$A$17=$H$21)*($G$2:$G$17=LARGE(IF($A$2:$A$17=$H$21,$G$2:$G$17),ROW(1:1))),ROW($1:$16),9^9),1+MOD(SUM(($G$20:$G20=LARGE(IF($A$2:$A$17=$H$21,$G$2:$G$17),ROW(1:1)))*1),SUM(($A$2:$A$17=$H$21)*($G$2:$G$17=LARGE(IF($A$2:$A$17=$H$21,$G$2:$G$17),ROW(1:1))))))),"")

为什么我写公式就这么复杂，大神们的都那么短小精干！

hcm19522

{=IFERROR(INDEX(A:A,RIGHT(LARGE(IF($A$2:$A$17=$H$21,$G$2:$G$17/1%%+ROW($2:$17)),ROW(A1)),2)),"")

shenbinglin

=IF(ISNA(OFFSET(G$2,(MATCH(LARGE(($A$2:$A$17=$H$23)*($I$2:$I$17),ROW(O1)),$I$2:$I$17,0))-1,)),"",OFFSET(G$2,(MATCH(LARGE(($A$2:$A$17=$H$23)*($I$2:$I$17),ROW(O1)),$I$2:$I$17,0))-1,))

增加了一个辅助列