Excel實例:在間隔數據中排定名次

 　　用Excel表格做了學生考試成績匯總表，格式如圖1所示。需要根據AA列中相應科目的成績排出名次，並將結果放到AB列相應的單元格中。

　　圖1

　　排定名次要使用RANK函數，其語法是“RANK(number,ref,order)”其中，參數“number”是需要找到排位的數字;而參數“ref”則是數字列表的引用;第三個參數如果省略則是按降序排列，這正是我們需要的。比如公式“=RANK(A3,A2:A6)”的意思就是要得到A3單元格數據在A2:A6單元格數據中的排名。

　　顯然，在本例中要使用RANK函數，但有一個問題是必須要解決的。假設我們要針對語文學科的總分來排名次。學生們的語文總成績分布在AA4、AA12、AA20……等單元格中，所處單元格區域並不連續，每8行有一個成績。怎樣才能將它們選中，並作為排名的區域呢?

　　這個問題也曾經困擾了我很長時間，最後發現利用求余數函數可以使問題得到順利解決。不過，有些准備工作是要做的。

　　還是以語文成績的排名為例。

　　先將AA列的數據復制到別的位置，等准備工作完成後再粘貼回來。

　　先選中AA4單元格，輸入公式“=1/(MOD(ROW(),8)-4)”，回車後即可發現出現“#DIV/0!”的錯誤提示。拖動該單元格填充句柄向下至AA379，則會從AA4單元格開始，每8行出現相同的錯誤提示，其它各行均為數字。

　　點擊功能區“開始”選項卡“編輯”功能組“查找和選擇”按鈕，在彈出的菜單中點擊“定位條件”命令，打開“定位條件”對話框。選中“公式”單選項，並只保留選中隨後出現的“錯誤”復選項，如圖2所示。確定後就可以發現，凡是出現錯誤的提示的單元格就處於被選中狀態了。

　　圖2

　　現在點擊功能區“公式”選項卡“定義的名稱”功能組“定義名稱”按鈕，在打開的“新建名稱”對話框的“名稱”輸入框中輸入“ymzf”。確定關閉對話框。

　　以後只要我們在名稱框中輸入“ymzf”，回車，就可以再次選中AA列中全部語文學科對應的單元格了，如圖3所示。

　　圖3

　　按照上面的方法，只要能讓錯誤提示分別出現在相應學科所在行，那麼就可以利用定位條件來選中它們。因為語文學科所處的單元格所在行除以8的余數為4，所以我們采用公式“=1/(MOD(ROW(),8)-4)”制造了除數為0的錯誤提示。那麼數學、英語等其它學科則可以分別根據其行數除以8的余數不同，重復上面的操作過程，只是將公式分母中“-4”分別變成“-5”、“-6”、“-7”、“-0”、“1”、“2”、“3”就可以了。將所到的各學科區域分別以“sxzf”、“yyzf”等名稱命名。

　　但准備工作僅僅做這些還是不夠的。因為我們排出的名次應該放在AB列而不是AA列。所以我們還要在再用上面的

　　方法在AB列中選中各學科對應的區域，並分別以“ymmc”、“sxmc”、“yymc”等名稱命名，以便將來在這些區域中輸入不同的公式。

　　至此，我們的准備工作才算是完成了。現在我們可以將臨時放到別處的總分粘貼回AA列單元格中了，再剩下的事兒就是用RANK函數排名的問題了。咱還是先根據語文成績排名吧。

　　先在名稱欄輸入“ywmc”，回車，將AB列語文學科所對應的單元格全部選中，此時AB372單元格會處於被激活狀態。我們只要在編輯欄輸入公式“=RANK(AA372,ywzf)”，並按下“Ctrl+Enter”就可以在全部選中的單元格中輸入公式並得到名次結果了。最後的結果如圖1所示。

　　其它學科的名次排定依此法辦理。夠簡單吧?

　　至此，我們針對各學科的排名工作就算是大功告成了。