Excel:重復名次也可以查姓名成績

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

　　圖1 原始成績表

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

　　一、名次表的建立

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

　　圖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;…})”的結果。兩個數組中的對應的數據分別相除，再將所有的商相加，正是分數所對應的名次。這種方法即使名次是並列的，也不會影響顯示效果。

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

　　二、分數的查找

　　將鼠標定位於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”快捷鍵，完成數組公式的輸入。這一步很關鍵的，否則不會出現正確的結果。

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

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

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

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

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

　　三、姓名的查找

　　將鼠標定位於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”快捷鍵完成數組公式的輸入。

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

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

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

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