本來已經(jīng)把學生各學科的成績、總分、名次都排出來了，并按照總分進行了升序排序，但現(xiàn)在又有任課老師過來要求希望能夠把自己學科的前10名的學生姓名及成績找出來。按理說，這個要求并不是很困難，但是麻煩就在于學生各科名次有可能相同，這樣的話，前10名的學生其實不一定是10個人，有可能更多。每個學科都要這么做的話，工作量也不小，所以，還是得靠函數(shù)和公式來幫忙。

　　原始的成績表如圖所示。姓名位于C2:C92單元格，語文成績位于D2:D92單元格區(qū)域。我們就以查找語文學科的前10名成績及學生姓名為例。為方便比較結果，圖中我們已經(jīng)將數(shù)據(jù)按語文成績降序進行了排序，實際操作中是不需要事先排序的。

　　一、名次表的建立

　　前面我們說過，我們不太容易確定排在前10名的學生共有多少，所以，我們需要使用公式將它們找出來。當然，最好順便將名次表填寫出來。完成結果如圖所示。

　　將鼠標定位于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單元格，向下拖動其填充句柄至出現(xiàn)“第11名”為止。

　　這里用到了幾個函數(shù)，感覺上比較復雜。其實思路是這樣的：“ROW(1:1)”的結果是“1”，而“LARGE($D$2:$D$92，1)”的結果是在指定的單元格區(qū)域中最大的一個數(shù);那么公式中“($D$2:$D$92>=LARGE($D$2:$D$92，ROW(1:1)))”可以理解為拿D2:D92單元格區(qū)域中的數(shù)據(jù)與該區(qū)域中最大值比較，大于或等于該值及小于該值的則會分別以“TRUE”、“FALSE”的結果保存在一個數(shù)組中。

　　公式中“COUNTIF($D$2:$D$92，$D$2:$D$92))”部分則會統(tǒng)計D2:D92單元格區(qū)域中每一個數(shù)值出現(xiàn)的次數(shù)，也分別保存到一個數(shù)組中。所以，我們所用公式中“SUMPRODUCT(($D$2:$D$92>=LARGE($D$2:$D$92，ROW(1:1)))/COUNTIF($D$2:$D$92，$D$2:$D$92))”在執(zhí)行時會得到一個類似于“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;…})”的結果。兩個數(shù)組中的對應的數(shù)據(jù)分別相除，再將所有的商相加，正是分數(shù)所對應的名次。這種方法即使名次是并列的，也不會影響顯示效果。

　　至于最外層的TEXT函數(shù)，則是將得到的結果轉換為按指定數(shù)字格式表示的文本。也就是本來內層公式運算的結果是數(shù)字“1”，現(xiàn)在我們將它顯示為“第1名”。

　　二、分數(shù)的查找

　　將鼠標定位于Y3單元格，在編輯欄中輸入如下公式“=INDEX($D$2:$D$92，MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92)，ROW(1:1))，$D$2:$D$92 1/ROW($D$2:$D$92)，0))”，然后按下“Ctrl Shift Enter”快捷鍵，完成數(shù)組公式的輸入。這一步很關鍵的，否則不會出現(xiàn)正確的結果。

　　向下拖動Y3單元格的填充句柄向下至最后一個單元格完成公式的復制。

　　由于D2:D92區(qū)域中有很多數(shù)據(jù)是重復的，這給我們造成了困難。所以，我們要想辦法使每一數(shù)據(jù)都變成唯一。公式中“$D$2:$D$92 1/ROW($D$2:$D$92)”就是給D2:D92區(qū)域中每一個數(shù)據(jù)都加了該數(shù)據(jù)對應行數(shù)的倒數(shù)。由于每一數(shù)據(jù)對應的行數(shù)是不一樣的，這樣，就會使每一數(shù)據(jù)都變成了唯一的值，并保存到了一個數(shù)組中。

　　公式中的“LARGE($D$2:$D$92 1/ROW($D$2:$D$92)，ROW(1:1))”還是返回了上面所得數(shù)組中的最大值。本例中的結果是“{96.5｝”。

　　公式中“MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92)，ROW(1:1))，$D$2:$D$92 1/ROW($D$2:$D$92)，0)”返回的是剛剛得到的最大值在數(shù)組中的位置。本例中的結果是“{1｝”。

　　這樣，其實Excel最后執(zhí)行的查詢就是“INDEX($D$2:$D$92，1)”了，自然可以返回在$D$2:$D$92區(qū)域中的第一個值了。

　　三、姓名的查找

　　將鼠標定位于Z3單元格，在編輯欄中輸入公式“=INDEX($C$2:$C$92，MATCH(LARGE($D$2:$D$92 1/ROW($D$2:$D$92)，ROW(1:1))，$D$2:$D$92 1/ROW($D$2:$D$92)，0))”，同樣按下“Ctrl Shift Enter”快捷鍵完成數(shù)組公式的輸入。

　　向下拖動Z3單元格的填充句柄向下至最后一個單元格完成公式的復制。最后的效果如圖所示。

　　姓名的查找與前面分數(shù)的查找是一樣的。公式本身也沒有什么大的變化。