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

　　圖1 原始成績表

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

　　一、名次表的建立

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

　　圖2 成績排序

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

　　這里用到了幾個函數(shù)，感覺上比較復(fù)雜。其實思路是這樣的：“ROW(1:1)”的結(jié)果是“1”，而“LARGE($D$2:$D$92,1)”的結(jié)果是在指定的單元格區(qū)域中最大的一個數(shù);那么公式中“($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))”可以理解為拿D2:D92單元格區(qū)域中的數(shù)據(jù)與該區(qū)域中最大值比較，大于或等于該值及小于該值的則會分別以“TRUE”、“FALSE”的結(jié)果保存在一個數(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;…})”的結(jié)果。兩個數(shù)組中的對應(yīng)的數(shù)據(jù)分別相除，再將所有的商相加，正是分數(shù)所對應(yīng)的名次。這種方法即使名次是并列的，也不會影響顯示效果。

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

　　二、分數(shù)的查找

　　將鼠標(biāo)定位于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+Sh

　　ift+Enter”快捷鍵，完成數(shù)組公式的輸入。這一步很關(guān)鍵的，否則不會出現(xiàn)正確的結(jié)果。

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

　　我們還是簡單解釋一下公式的思路。

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

　　公式中的“LARGE($D$2:$D$92+1/ROW($D$2:$D$92),ROW(1:1))”還是返回了上面所得數(shù)組中的最大值。本例中的結(jié)果是“{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ù)組中的位置。本例中的結(jié)果是“{1｝”。

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

　　三、姓名的查找

　　將鼠標(biāo)定位于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單元格的填充句柄向下至最后一個單元格完成公式的復(fù)制。最后的效果如圖3所示。

　　圖3 完成公式的復(fù)制(點擊看大圖)

　　其實您肯定已經(jīng)明白了，姓名的查找與前面分數(shù)的查找是一樣的。公式本身也沒有什么大的變化。所以，明白了前面的方法，要查找別的什么東西也就方便了。

　　其它的學(xué)科可以照此辦理。只要注意變換一下公式中的單元格區(qū)域就可以了，我這里就不羅嗦了。