楊青

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

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

中圖分類號(hào)：TP317.3 文獻(xiàn)標(biāo)志碼：A 文章編號(hào)：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 引言

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

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

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

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

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

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

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

1.2.1 單個(gè)工作表排名公式分析

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

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

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

1.2.2 排名過(guò)程分解

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

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

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

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

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

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

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

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

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

3 去重復(fù)排名

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

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

RANK.EQ函數(shù)與COUNTIF函數(shù)組合應(yīng)用可以解決重復(fù)排名問(wèn)題。COUNTIF函數(shù)是Excel中對(duì)指定區(qū)域中符合指定條件的單元格計(jì)數(shù)的一個(gè)函數(shù)。圖 7中 D2單元格中的公式為“=COUNTIF（$B$2：B2，B2）”，$B$2：B2是一個(gè)動(dòng)態(tài)區(qū)域，拖動(dòng)填充柄進(jìn)行數(shù)據(jù)填充，可以判定當(dāng)前成績(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。通過(guò)公式復(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ù)功能強(qiáng)大，結(jié)合學(xué)生成績(jī)表分析RANK.EQ函數(shù)功能及原理；通過(guò)擴(kuò)展引用范圍實(shí)現(xiàn)跨表年級(jí)總排名；結(jié)合COUNTIF函數(shù)，實(shí)現(xiàn)學(xué)生成績(jī)排名去重復(fù)。RANK.EQ函數(shù)實(shí)現(xiàn)排名后，更改成績(jī)表中任一學(xué)生的成績(jī)，排名將會(huì)自動(dòng)更新，實(shí)現(xiàn)動(dòng)態(tài)排名。掌握函數(shù)的應(yīng)用，大大節(jié)省了時(shí)間，提高了工作效率。

5 結(jié)束語(yǔ)

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

參考文獻(xiàn)（References）：

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

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

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

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

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