EXCEL中RANK.EQ函數引用范圍擴展及去重復排名

楊青

摘 要： EXCEL改變了傳統的數據統計過程，運用公式和函數對數據進行復雜的運算。以學生成績排名為例，介紹RANK.EQ函數的定義，分析該函數的功能原理，詳細介紹函數中三個參數的功能，加深對RANK.EQ函數的理解和掌握。利用RANK.EQ函數實現單個工作表中的成績排名，對第三個參數設置不同的值，可以得到求優排名和求差排名；擴展第二個參數范圍引用實現跨表總成績排名；結合COUNTIF函數實現成績去重復排名。

關鍵詞： 排名； RANK.EQ； 跨表； COUNTIF； 去重復

中圖分類號：TP317.3 文獻標志碼：A 文章編號：1006-8228（2018）09-45-03

Abstract： Excel changed the traditional data statistics process， and used formulas and functions to perform complex operations on data. This paper introduces the definition of RANK.EQ function， analyzes its function principle， introduces the functions of its three parameters in detail， and deepens the understanding and mastery of the RANK.EQ function. Using the RANK.EQ function， can achieve the ranking in a single worksheet， setting the third parameters with different values， can get the best ranking and the poor ranking， extending the second parameter range， can achieve the total score ranking of the cross tables， and combining with COUNTIF function can solve the score duplicate ranking.

Key words： ranking； RANK.EQ； cross table； COUNTIF； solution of duplicate

0 引言

在成績統計過程中，經常會對成績進行排名次。EXCEL對成績的排名可以由排序功能和RANK.EQ函數來實現。

排序是根據某些字段的升序和降序對數據進行排序，數據會按指定的順序重新排列，不會產生新的數據列，但會改變電子表格中數據原來的排列順序。為保證原有數據順序不變的情況下，使用RANK.EQ函數對數據進行排名，新增一列字段名，根據相應的字段進行排名，將排名的結果顯示在新增的字段中。

1 RANK.EQ函數簡介

1.1 RANK.EQ函數的定義

RANK.EQ函數是屬于RANK函數的分支函數，返回某數字在一列數字中相對于其他數字的大小排名；如果多個數字排名相同，則返回該組數字最佳排名。函數語法及參數說明如表1所示。

1.2 RANK.EQ函數的工作原理

RANK.EQ屬于統計類型的函數，主要用于數字排名，是指數字列表中每個數字依次與該列表中其他數字進行比較，得出每個數字在該列表中的最終排名。

1.2.1 單個工作表排名公式分析

現運用RANK.EQ函數對單張表中的成績從高分到低分進行排名，得到的排名如圖1所示。C2單元格中的公式“=RANK.EQ（B2，$B$2：$B$5，0）”表示A1學生的成績在整個班級中的排名為3。公式中第一個參數B2表示成績列中92所在的單元格地址；第二個參數$B$2：$B$5表示查找排名所在的絕對地址引用，即排名的范圍始終在92，95，99，90四個數字之間；第三個參數0（可以省略不寫）表示按照成績降序排名，即排名是按照成績列中四個數字從高到低進行排名。其余學生的排名可以拖動填充柄進行數據填充。

圖2中D2單元格中的公式“=RANK.EQ（B2，$B$2：$B$5，1）”表示A1學生的成績在整個班級排名中為倒數第2。D2和C2單元格中的公式只有第三個參數不同，公式中第三個參數1（可以是0以外的其他任何數字）表示按照成績升序排名，即排名是按照成績列中四個數字由低到高進行排名。

需要注意的是本例中RANK.EQ函數的第二個參數“$B$2：$B$5”應使用絕對值引用，表示需要排名的數字列表始終在一個區域內，任何一個數的排名都應該在這個區域中進行，否則排名將出現混亂。

1.2.2 排名過程分解

圖 1中按分數從高到低排名，A1同學的成績92分別與成績列中其他數字比較，其中92小于95，99，即表示有兩位同學的成績大于92，故92排名第3；A2同學的成績95分別與成績列中其他數字比較，其中95只小于99，即表示僅有一位同學的成績大于95，故95排名第2；A3同學的成績99分別與成績列中其他數字比較，99大于其他三個數92、95、90，即表示沒有同學的成績大于99，故99排名第一；A4同學的成績90分別與成績列中其他數字比較，其中90小于其他三個數92，95，99，即表示有三位同學的成績大于90，故90排名第4，整個排名過程如圖3所示。

2 RANK.EQ函數擴展引用范圍

整個年級的成績分布在多張工作表中，對每個班級的成績排名在單張工作表中進行，若對整個年級的成績排名則需要跨越多張工作表進行。運用RANK.EQ函數不僅可以實現班級排名還能實現多個班級的跨表總排名。

對于班級排名，引用范圍在一張工作表內，這是常見的范圍引用。如圖4和圖5所示，1班和2班的班級排名都顯示在C列。1班A1學生的班級排名可通過C2單元格中的公式“=RANK.EQ（B2，$B$2：$B$5，0）”實現；2班B1學生的班級排名可通過C2單元格中的公式“=RANK.EQ（B2，$B$2：$B$4，0）”實現。其余學生的班級排名可以拖動填充柄進行數據填充。

兩個班級的總排名，引用范圍涉及兩張工作表，因此需要擴展引用范圍，實現跨表排名。如圖4和圖5所示，1班和2班的班級總排名都顯示在D列。1班A1學生的年級總排名公式與2班B1學生的年級總排名公式均為“=RANK.EQ（B2，'1班：2班'！$B$2：$B$5，0）”。公式中第二個參數范圍引用為“1班：2班'！$B$2：$B$5”，涉及“1班”和“2班”兩張表中的“$B$2：$B$5”數據列，表示1班和2班的引用范圍都是“$B$2：$B$5”區域，即每一個學生依次和兩個班級中所有學生的成績比較，最后得出該生在年級中的總排名。

實際應用中，每個班級的學生數不相同，要引用各個班級成績工作表中記錄最多的范圍。在本例中

1班工作表共有4條記錄，存放在“$B$2：$B$5”區域中；2班工作表共有3條記錄，存放在“$B$2：$B$4”區域中，數據引用的范圍以最大記錄數1班的4條為基數，也就是“$B$2：$B$5”單元格地址。

整個公式的編輯過程：首先，選擇名稱是“1班”的工作表，點擊A1學生所在的“總排名”單元格D2，然后在編輯欄輸入“=RANK.EQ（B2，'1班：2班'！$B$2：$B$5，0）”，接著拖動填充柄進行數據填充；其次，選擇名稱是“2班”的工作表，點擊B1學生所在的“總排名”單元格D2，然后在編輯欄輸入“=RANK.EQ（B2，'1班：2班'！$B$2：$B$5，0）”，接著拖動填充柄進行數據填充，跨表完成成績總排名。

有關‘1班：2班的輸入技巧：可以按住Shift鍵，用鼠標依次單擊“1班”和“2班”工作表，其中“'”和“：”在英文狀態下輸入。

3 去重復排名

在運用Rank.EQ函數排名的過程中，如果出現相同數字，則顯示為同一排名。

圖6中，A1和A2兩位學生出現相同的成績，排名中出現兩個第二名，跳過第三名直接到第四名，重復成績的存在影響后續數值的排位。

RANK.EQ函數與COUNTIF函數組合應用可以解決重復排名問題。COUNTIF函數是Excel中對指定區域中符合指定條件的單元格計數的一個函數。圖 7中 D2單元格中的公式為“=COUNTIF（$B$2：B2，B2）”，$B$2：B2是一個動態區域，拖動填充柄進行數據填充，可以判定當前成績第幾次出現。D3單元格中的2就表示A2同學的95分是第二次出現。

在E2單元格中輸入公式“=RANK.EQ（B2，$B$2：$B$5，0）+COUNTIF（$B$2：B2，B2）-1”，即E2=C2+D2-1=2+1-1=2。通過公式復制，E3單元格中公式為“=RANK.EQ（B3，$B$2：$B$5，0）+COUNTIF（$B$2：B3，B3）-1”，即E3=C3+D3-1=2+2-1=3，從圖 7中C3和E3列的排名可以得出A2同學95分由于是第二次出現，未去重復前排名與A1同學并列第二名，去重復排名后排名變為三。去重復排名可以保證排名數與總人數一致。

4 總結

EXCEL函數功能強大，結合學生成績表分析RANK.EQ函數功能及原理；通過擴展引用范圍實現跨表年級總排名；結合COUNTIF函數，實現學生成績排名去重復。RANK.EQ函數實現排名后，更改成績表中任一學生的成績，排名將會自動更新，實現動態排名。掌握函數的應用，大大節省了時間，提高了工作效率。

5 結束語

EXCEL是微軟OFFICE組件中的一員，其內置函數庫非常豐富，但根據需求差異，未必能全部滿足，因此需要通過函數之間的組合使用實現某些功能。深度掌握EXCEL能輕松解決日常生活和工作中的實際問題，大大提高工作效率，全面解放雙手，真正實現辦公自動化。

參考文獻（References）：

[1] 陳煒東.Excel中RANK函數的改進和應用[J].計算機時代，2015.7：51-52，56

[2] 王俊京，趙冉.如何使用Excel中的RANK函數為數據排名次[J].技術與市場，2011.18（7）.

[3] 加力戈.Excel中對數據進行分組中國式排名方法分析[J].經營管理者，2013.8X：P367

[4] 互聯網+計算機教育研究院.2016WORD EXCEL PPT商務辦公（第1版）[J].人民郵電出版社，2017.6.

[5] 侯冬梅.計算機應用基礎（第二版）[J].中國鐵道出版社，2014.