Excel 重复名次也可以查姓名成绩

　　当老师的，对分析学生成绩大概有瘾。这不，本来我们已经把学生各学科的成绩、总分、名次都排出来了，并按照总分进行了升序排序，但现在又有任课老师过来要求希望能够把自己学科的前10名的学生姓名及成绩找出来。按理说，这个要求并不是很困难，但是麻烦就在于学生各科名次有可能相同，这样的话，前 10名的学生其实不一定是10个人，有可能更多。每个学科都要这么做的话，工作量也不小，所以，还是得靠函数和公式来帮忙。

图1 原始成绩表

　　一、名次表的建立

　　前面我们说过，我们不太容易确定排在前10名的学生共有多少，所以，我们需要使用公式将它们找出来。当然，最好顺便将名次表填写出来。完成结果如图2所示。

　　将鼠标定位于X3单元格，然后在编辑栏输入公式“=TEXT(SUMPRODUCT(($D$2:$D$92>=LARGE($D$2: $D$92,ROW(1:1)))/COUNTIF($D$2:$D$92,$D$2:$D$92)),"第G/通用格式名")”，回车后就可以得到“第 1名”的结果。选定X3单元格，向下拖动其填充句柄至出现“第11名”为止。

　　这里用到了几个函数，感觉上比较复杂。其实思路是这样的：“ROW(1:1)”的结果是“1”，而“LARGE($D$2:$D$92,1)” 的结果是在指定的单元格区域中最大的一个数；那么公式中“($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))”可以理解为拿D2:D92单元格区域中的数据与该区域中最大值比较，大于或等于该值及小于该值的则会分别以“TRUE”、“FALSE”的结果保存在一个数组中。

　　公式中“COUNTIF($D$2:$D$92,$D$2:$D$92))”部分则会统计D2:D92单元格区域中每一个数值出现的次数，也分别保存到一个数组中。所以，我们所用公式中“SUMPRODUCT(($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1: 1)))/COUNTIF($D$2:$D$92,$D$2:$D$92))”在执行时会得到一个类似于“SUMPRODUCT({TRUE; FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;…} /{1;1;2;2;1;2;2;1;2;2;2;2;1;…})”的结果。两个数组中的对应的数据分别相除，再将所有的商相加，正是分数所对应的名次。这种方法即使名次是并列的，也不会影响显示效果。