Excel 根据成绩来提取前10学生信息，当有并列名次时，则要把所有的都提取出来，但只提取前10名，怎么办？

Question

根据一个年级的总成绩表来提取前10学生信息，作为光荣榜，当有并列名次时，则要把所有的都提取出来，比如前10名有11人则提取11人，但只提取前10名，怎么办？公式如何编写？
表1中是原始成绩，在表2中按名次由高到低自动排列，公式怎么写，请高手指教！！
表1：
学号 姓名 成绩 名次
0701 学生1 85 5
0702 学生2 82 8
0703 学生3 96 2
0704 学生4 56 17
0705 学生5 72 12
0706 学生6 83 6
0707 学生7 65 15
0708 学生8 57 16
0709 学生9 67 14
0710 学生10 82 8
0711 学生11 83 6
0712 学生12 54 18
0713 学生13 34 20
0714 学生14 98 1
0715 学生15 92 4
0716 学生16 75 11
0717 学生17 68 13
0718 学生18 82 8
0719 学生19 93 3
0720 学生20 46 19
表二：
名次 学号 姓名 成绩
1 ？B2单元格 ？C2 D2 （本行是第一名学生信息）
2
3
4
5
6
7
8
9
10
11
12
13
其中B2,C2,D2单元格公式怎么写？第6和第7名成绩重复，要求先出现的排前

Answer 1

假设你的数据在SHEET1第一行是标题
数据在A-D列

现在第一步 在A列前插入一列，使得数据在B-E列
A2=IF(COUNTIF(D$2:D$1000,D2)>1,D2-ROW()/10000,D2)
向下填充公式，这里支持999人中选择前10名（分值不能有0.01分，如88.23分）
隐藏A列

结果在Sheet2

B2=VLOOKUP(LARGE(Sheet1!$A$2:$A$1000,$A2),Sheet1!$A$2:$D$1000,2,FALSE)
C2=VLOOKUP(LARGE(Sheet1!$A$2:$A$1000,$A2),Sheet1!$A$2:$D$1000,3,FALSE)
D2=VLOOKUP(LARGE(Sheet1!$A$2:$A$1000,$A2),Sheet1!$A$2:$D$1000,4,FALSE)

填充公式

Answer 2

不需要表二；更不需要用什么公式去硬性地找出相应名次的学生信息，其实很简单就可以实现你想要的结果，就是把表一中的名次一列剪切到学号列前面，选中名次 学号 姓名 成绩四个格子，点数据——筛选——自动筛选，就会出现四个小倒立的三角形，在名次的那个三角形上点一下，按升序排列，就ok了。我说的够清楚了吧。这个名次不知道你是不是用rank函数自动得出的，如果是，成绩相同则名次也相同，并且有两个第6的话就不会再有第7了。

Answer 3

你排名次的时候，如果有两个第一名，会有第二名吗？

如果有：
A1=名次 B1(空) C1=学号 D1=姓名 E1=成绩
A2=1
A3=IF(B2<COUNTIF(表1!D:D,A2),A2,A2+1)
往下拷。

B2=COUNTIF($A$2:$A2,$A2)
往下拷。

C3=INDEX(表1!$A:$D,SMALL(IF(表1!$D$2:$D$100=$A2,ROW(表1!$A$2:$A$100)),$B2),COLUMN()-2)
输完公式按Ctrl+Shift+Enter
C3={INDEX(表1!$A:$D,SMALL(IF(表1!$D$2:$D$100=$A2,ROW(表1!$A$2:$A$100)),$B2),COLUMN()-2)}
把公式拷到 D2 和 E2
再往下拷。

如果有两个第一名，就没有第二名
只要把 A3 改成
=IF(B2<COUNTIF(表1!D:D,A2),A2,A2+B2)
往下拷就可以了

Answer 4

非要公式？
你选中表1数据--点数据---排序--按升序--确定

这样就排完了，然后复制前10名到表二