楊青

摘 要： EXCEL改變了傳統(tǒng)的數(shù)據(jù)統(tǒng)計過程，運用公式和函數(shù)對數(shù)據(jù)進行復(fù)雜的運算。以學(xué)生成績排名為例，介紹RANK.EQ函數(shù)的定義，分析該函數(shù)的功能原理，詳細介紹函數(shù)中三個參數(shù)的功能，加深對RANK.EQ函數(shù)的理解和掌握。利用RANK.EQ函數(shù)實現(xiàn)單個工作表中的成績排名，對第三個參數(shù)設(shè)置不同的值，可以得到求優(yōu)排名和求差排名；擴展第二個參數(shù)范圍引用實現(xiàn)跨表總成績排名；結(jié)合COUNTIF函數(shù)實現(xiàn)成績?nèi)ブ貜?fù)排名。

關(guān)鍵詞： 排名； RANK.EQ； 跨表； COUNTIF； 去重復(fù)

中圖分類號：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 引言

在成績統(tǒng)計過程中，經(jīng)常會對成績進行排名次。EXCEL對成績的排名可以由排序功能和RANK.EQ函數(shù)來實現(xiàn)。

排序是根據(jù)某些字段的升序和降序?qū)?shù)據(jù)進行排序，數(shù)據(jù)會按指定的順序重新排列，不會產(chǎn)生新的數(shù)據(jù)列，但會改變電子表格中數(shù)據(jù)原來的排列順序。為保證原有數(shù)據(jù)順序不變的情況下，使用RANK.EQ函數(shù)對數(shù)據(jù)進行排名，新增一列字段名，根據(jù)相應(yīng)的字段進行排名，將排名的結(jié)果顯示在新增的字段中。

1 RANK.EQ函數(shù)簡介

1.1 RANK.EQ函數(shù)的定義

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

1.2 RANK.EQ函數(shù)的工作原理

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

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

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

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

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

1.2.2 排名過程分解

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

2 RANK.EQ函數(shù)擴展引用范圍

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

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

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

實際應(yīng)用中，每個班級的學(xué)生數(shù)不相同，要引用各個班級成績工作表中記錄最多的范圍。在本例中

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

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

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

3 去重復(fù)排名

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

圖6中，A1和A2兩位學(xué)生出現(xiàn)相同的成績，排名中出現(xiàn)兩個第二名，跳過第三名直接到第四名，重復(fù)成績的存在影響后續(xù)數(shù)值的排位。

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

在E2單元格中輸入公式“=RANK.EQ（B2，$B$2：$B$5，0）+COUNTIF（$B$2：B2，B2）-1”，即E2=C2+D2-1=2+1-1=2。通過公式復(fù)制，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同學(xué)95分由于是第二次出現(xiàn)，未去重復(fù)前排名與A1同學(xué)并列第二名，去重復(fù)排名后排名變?yōu)槿?。去重?fù)排名可以保證排名數(shù)與總?cè)藬?shù)一致。

4 總結(jié)

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

5 結(jié)束語

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

參考文獻（References）：

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

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

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

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

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