年级、班级前三

（第一）的数组列出来。INDIRECT("C$1:C$"&COUNTA(C:C))=G2 这个的意思把范围 INDIRECT("C$1:C$"&COUNTA(C:C)) 的值与G2做对比。如果相等，就返回 就返回0。第二步，判断这个数组，如果这个数组，正确的时候，就返回它的行坐 标，错误的时候，就返回0。 IF(INDIRECT("C$1:C$"&COUNTA(C:C))=G2,ROW(INDIRECT("B$1:B$"&COUNTA(B:B))),0) ROW(INDIRECT("B$1:B$"&COUNTA(B:B))) 这个其实就是得到一个行值的数组。 INDIRECT("B$1:B$"&COUNTA(B:B)) 是有值区域，row之后就是这些有值区域的行值 得到的数组。 现在我们得到了一个数组，这个数组里面，如果是等于了第一的，行坐标就不等 于0。 当然，你点公式下面的公式求值，你就能看到，实际上这时候数组以及变成了 {0,0,0,0,5,0,0,8,0}这样类似的数组。说明什么呢？这代表第5个和第8 第三步，既然我们已经得了这两个位置，那么我们就要返回这两个位置上的名字。 利用LARGE来一个一个的返回出现的位置。第一个最大值是 8，那么我们返回 后利用INDEX 来返回B列第8个，就直接得到名字了。为什么要用LARGE SMALL呢？因为最大值时8然后到5。最小值是很多个0之后才到8和5 虑0的情况。而8和5我们不用考虑先后顺序。所以直接用LARGE。 第四步，我们已经返回了第一个，怎么返回第二个？这就用到了前面已知条件里 面的年级排名了。排名本来应该是1、2、3这样的，但是因为有出现相同分数的情 况，所以有了1、1、3这样的情况。ROW(D1)-E2+1这个意思就是原本的行数减去名 次+1就等于在得到的数组里面的实际排名。举个例子你就明白。比如： 第一个第一名。那么ROW(D1)=1，E2=1。ROW(D2)-E3+1=1 那么如果数组是 {0,0,0,0,5,0,0,8,0} 返回值就是 8 第二个第一名。那么ROW(D2)=2，E3=1。ROW(D2)-E3+1=2 那么如果数组是 {0,0,0,0,5,0,0,8,0} 返回值就是 5 因为这儿已经是第二大的了。 第三名。那么ROW(D3)=3，E3=3。ROW(D2)-E3+1=1 这时候又是下一个数组最大的。 当然，这个式子的前提是分数里面有值。所以加了个 if判断G2是否为空。 这样我们这个式子就写完了。 班别里面大多数的公式与年级的一样。只是多了个判断。比如： 姓名里面的公式： {=IF(K3<>"",INDEX(B:B,LARGE(IF((INDIRECT("C$1:C$"&COUNTA(C:C))=K3)*(INDIRECT("A $1:A$"&COUNTA(A:A))=J$1),ROW(INDIRECT("B$1:B$"&COUNTA(B:B))),0),ROW(D1) I3+1)),"")} 多了：“*(INDIRECT("A$1:A$"&COUNTA(A:A))=J$1) ”这个，其实就是增加了一个必 须是J$1所对应的班级而已。 得分这个公式看起来很长，但是实际情况是因为式子返回了 0，我们需要把 况去掉。所以加了一个判断，看起来长。 实际的核心公式是： {=LARGE(IFERROR((INDIRECT("A$1:A$"&COUNTA(A:A))=$J$1)*INDIRECT("C$1:C$"&CO UNTA(C:C)),0),ROW(D1))} 这个INDIRECT("A$1:A$"&COUNTA(A:A))=$J$1 的意思是符合 $J$1的数组，返回值只
排名以及第一张表（A、B、C
表里面得分是最高的那个 这个的意思把范围
这个的意思把范围 果相等，就返回1，不相等 正确的时候，就返回它的行坐
("B$1:B$"&COUNTA(B:B))),0) 得到一个行值的数组。 后就是这些有值区域的行值
于了第一的，行坐标就不等
上这时候数组以及变成了 个和第8个是第一名。 要返回这两个位置上的名字。 ，那么我们返回8，然 为什么要用LARGE而不用 8和5。我们不用考 。 这就用到了前面已知条件里 但是因为有出现相同分数的情 个意思就是原本的行数减去名 你就明白。比如： 那么如果数组是
那么如果数组是
时候又是下一个数组最大的。 是否为空。
断。比如：
UNTA(C:C))=K3)*(INDIRECT("A OUNTA(B:B))),0),ROW(D1)-
个，其实就是增加了一个必 ，我们需要把0的情
J$1)*INDIRECT("C$1:C$"&CO 的数组，返回值只
年级排名 1 2 3 4 5 5 5 5 9 10 11 12 13 14 15 16 16 16 19 20 21 22 23 24 25
姓名 张16 张12 张27 张11 张24 张9 张8 张6 张21 张20 张19 张18 张17 张15 张14 张23 张13 张7 张10 张5 张4 张3 张22 张26 张25
班级 3 1 1 1 1 1 1 1 2 2 2 2 2 3 3 3 3 3 3 4 4 4 4 4 4
姓名 张3 张4 张5 张6 张7 张8 张9 张10 张11 张12 张13 张14 张15 张16 张17 张18 张19 张20 张21 张22 张23 张24 张25 张26 张27
得分 60 61 62 80 70 80 80 67 82 98 70 71 72 99 74 75 76 77 78 50 70 80 45 47 88
W(D1)),"")}
这个函数。目的是把前N名的
这个范围应该是C列中，有值 杂度。那么这个范围怎么找 们需要固定。所以是“C$1”。 是缺考也必须要填入值，所 COUNTA(C:C)。这 为列值。那就是 行坐标。这样，这个区域我们 值。不能直接作为区域引用。 CT("C$1:C$"&COUNTA(C:C))。 出现两个第一名的情况下，第 1,2,3的话，会把并列 个值，我们用ROW(D1)。这 知道电子表格在向下拖动公 ，那么这个值需要变化，拉 是不会变动的。大家可以试
C$"&COUNTA(C:C)),ROW(D1))} 。当我们的排名数超过了的 来解决。这个函数的功能 值，如果不正常，那么返回 个参数，我们写""。这个代 的话，默认变成返回值是0。 ，这儿需要的是""。
个参数是条件，第二个参数 数的排名。当然了，2,ROW(INDIREC
得分 99 98 88 82 80 80 80 80 78 77 76 75 74 72 71 70 70 70 67 62 61 60 50 47 45
班别 排名 1 1 1 4 5 6 7
1 姓名 张9 张8 张6 张7 张10 张5 张4 得分 80 80 80 70 67 62 61 重难点： 年级排名的分使用的公式： {=IFERROR(LARGE(INDIRECT("C$1:C$"&COUNTA(C:C)),ROW(D1)),"")} 需要注意的是，这个公式需要输入 =IFERROR(LARGE(INDIRECT("C$1:C$"&COUNTA(C:C)),ROW(D1)),"") 光标还在输入框的时候，同时按下 Ctrl+Shift+Enter
第一步，返回第几最大值函数：LARGE 我们需要得到的是排名第几的分数，所以需要用上面这个函数。目的是把前 分数显示出来。 LARGE有两个参数，第一个参数数排名的区域范围。这个范围应该是 得地方。没有值的，我们不再用来计算，减少时间复杂度。那么这个范围怎么找 呢？范围开始，肯定是“C1”了，当然，这个行值我们需要固定。所以是“ 到什么时候结束呢？到没有值得时候。这个表，要求是缺考也必须要填入值，所 以不考虑出现其中有空值的情况。统计C列有值单元格使用函数COUNTA(C:C) 样，结束的单元格应该是以值单元格作为行值， C列为列值。那就是 “"C$"&COUNTA(C:C)”。加“$”同样的目的，固定行坐标。这样，这个区域我们 就的出来了 "C$1:C$"&COUNTA(C:C) 。这是一个文本值。不能直接作为区域引用。 我们用 INDIRECT 函数把这个文本转换为引用。INDIRECT("C$1:C$"&COUNTA(C:C)) 第二个参数是排名数值。比如第一名，就是1。如果出现两个第一名的情况下，第 一次出现的是1，第二次出现的是2。也就是说，我们这儿是1,2,3的话，会把并列 第一的两个以及第三名的分数给出来了。所以，第二个值，我们用 个的意思是返回D1的行值。之所以是D1，因为大家都知道电子表格在向下拖动公 式的时候，行值自动变化。我们需要把前十名列出来，那么这个值需要变化，拉 下一格的时候，这个需要变成2。如果直接填1的话，是不会变动的。大家可以试 试 “=1”和“=row(d1)”向下拉动的区别。 这样，我们就得到这个公式：{=LARGE(INDIRECT("C$1:C$"&COUNTA(C:C)),ROW(D1))} 第二步，细心的同学会发现，这个函数有出错的情况。当我们的排名数超过了的 时候，会出现错误值 #NUM! 。所以，我们需要IFERROR来解决。这个函数的功能 是，如果第一个参数返回值正常，那么返回它本身的值，如果不正常，那么返回 第二个参数。第一个参数我们写上面那个公式。第二个参数，我们写 表空值。当然，也可以什么都不写，不过什么都不写的话，默认变成返回值是 大家测试下“=if(1,"",)”与“=if(1,,)”的区别。所以说，这儿需要的是 这样，我们就把年级排名里面的得分从最高到最低排序好了。