Excel中RANK函數(shù)的改進(jìn)和應(yīng)用

陳煒東

摘 ?要： 在數(shù)據(jù)處理方面Excel表現(xiàn)了它的優(yōu)勢(shì)，它改變了傳統(tǒng)的數(shù)據(jù)統(tǒng)計(jì)過程。但是Excel自帶的函數(shù)已越來越不能滿足人們?nèi)找嬖鲩L(zhǎng)的需求。RANK函數(shù)是Excel中常用的排位函數(shù)，一次只能對(duì)單一類別的數(shù)據(jù)進(jìn)行排位，不能對(duì)多種類別的數(shù)據(jù)同時(shí)排位。研究了在RANK函數(shù)功能的基礎(chǔ)上利用Excel VBA編程的方法重構(gòu)新的排位函數(shù)，即FRANK函數(shù)，以解決RANK函數(shù)在實(shí)際應(yīng)用中的不足，并以FRANK函數(shù)在班級(jí)成績(jī)排名中的應(yīng)用為例，進(jìn)一步體現(xiàn)其改進(jìn)的優(yōu)勢(shì)及可行性。

關(guān)鍵詞： Excel; 數(shù)據(jù)處理; RANK; VBA; FRANK; 成績(jī)排名

中圖分類號(hào)：TP317.3 ? ? ? ? ?文獻(xiàn)標(biāo)志碼：A ? ? 文章編號(hào)：1006-8228（2015）07-51-02

Improvement and application of RANK function in Excel

Chen Weidong

（Zhengda High School， Haimen， Jiangsu 226100， China）

Abstract： In the field of data processing， Microsoft Excel has demonstrated its advantages and changed the traditional statistical method， but the function of Excel is increasingly unable to meet the growing demand of people. RANK is a commonly used ranking function in Excel， can only rank a single category of data at a time. In order to solve the problem in the practical application， this paper proposes a new function named FRANK which is studied on the basis of RANK function and developed with Excel VBA， and taking the application of FRANK function in class performance ranking as an example， further demonstrates the improved advantage and the feasibility.

Key words： Excel; data analysis; RANK; VBA; FRANK; sort the scores

0 引言

數(shù)據(jù)處理軟件Excel的出現(xiàn)改變了以往統(tǒng)計(jì)處理數(shù)據(jù)的方法，大大加快了數(shù)據(jù)處理的速度，提高了辦公效率。人們對(duì)于Excel的使用，不僅僅是記錄數(shù)據(jù)，主要還是對(duì)數(shù)據(jù)的分析處理。無(wú)論是各行各業(yè)各部門的辦公人員，還是教師或?qū)W生都離不開辦公應(yīng)用軟件的使用。教師用EXCEL對(duì)學(xué)生成績(jī)進(jìn)行管理更普遍，快捷，方便[1]。RANK函數(shù)是Excel自帶的函數(shù)之一，它常用于數(shù)據(jù)的排位，但是RANK函數(shù)限制一次只能對(duì)相同類別的數(shù)據(jù)進(jìn)行排位，不能對(duì)多種類別的數(shù)據(jù)同時(shí)排位，隨著數(shù)據(jù)類別的增多，需要人工操作RANK函數(shù)的次數(shù)也就相對(duì)增多，降低了數(shù)據(jù)處理的效率。

1 現(xiàn)有的RANK函數(shù)

1.1 RANK函數(shù)的定義

在Microsoft Excel幫助文檔中給出了RANK函數(shù)的定義：返回一個(gè)數(shù)字在數(shù)字列表中的排位[2]。數(shù)字的排位是其大小與列表中其他值的比值（如果列表已排序，則數(shù)字的排位就是它當(dāng)前的位置）。其語(yǔ)法如表1所示。

表1 ?RANK函數(shù)的語(yǔ)法

[語(yǔ)法結(jié)構(gòu)＼&RANK（number，ref，order）＼&參數(shù)定義＼&number＼&為需要找到排位的數(shù)字＼&ref＼&為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用。ref 中的非數(shù)值型參數(shù)將被忽略＼&order＼&為一數(shù)字，指明排位的方式＼&]

·如果order為0（零）或省略，Microsoft Excel對(duì)數(shù)字的排位是基于ref為按照降序排列的列表。

·如果order不為零，Microsoft Excel 對(duì)數(shù)字的排位是基于ref為按照升序排列的列表。

1.2 RANK函數(shù)的示例

如果將示例復(fù)制到空白工作表中，可能更易于理解該示例。操作方法如下：

⑴ 創(chuàng)建空白工作簿或工作表;

⑵ 按要求填入數(shù)據(jù)。如圖1;

⑶ 在任意其他空白單元格中填入排位函數(shù)，得出對(duì)應(yīng)值的排位結(jié)果，解析如表2。

圖1 ?填入的數(shù)據(jù)

表2 ?排位結(jié)果說明

1.3 RANK函數(shù)的不足之處

RANK函數(shù)可以實(shí)現(xiàn)排位的功能，得出每個(gè)數(shù)據(jù)在所處數(shù)據(jù)列表中的位置，但在學(xué)校的學(xué)生成績(jī)數(shù)據(jù)處理中，要想快速高效，還可以做一些改進(jìn)。

⑴ 不能實(shí)現(xiàn)多類別的同時(shí)排位

從上面的介紹以及操作結(jié)果可以看出，RANK函數(shù)識(shí)別的是用戶所選擇的區(qū)域，也就是參數(shù)ref所指向的范圍，一次只能對(duì)所選區(qū)域的單一類別進(jìn)行排位。在處理學(xué)生成績(jī)數(shù)據(jù)時(shí)，有一項(xiàng)是各班學(xué)生在班級(jí)中的排名情況，利用RANK函數(shù)處理，一次只能對(duì)一個(gè)班級(jí)進(jìn)行排位，班級(jí)的數(shù)量決定著要重復(fù)操作的次數(shù)。班級(jí)越多，操作RANK函數(shù)的次數(shù)也就越多，而且也更容易出錯(cuò)，大大降低了工作效率。

⑵ 對(duì)于空和0的判斷不明顯

如果ref數(shù)據(jù)區(qū)間中的數(shù)據(jù)至少有一項(xiàng)的數(shù)值為0，那么在進(jìn)行排位的過程中，Excel就會(huì)自動(dòng)將區(qū)間內(nèi)為空的單元格作為0進(jìn)行排位;如果ref數(shù)據(jù)區(qū)間中沒有數(shù)值為0的單元格，但存在空單元格，那么系統(tǒng)就給出空單元格的排位結(jié)果為“#N/A”（值不可用）的提示。但是在學(xué)生成績(jī)排名的實(shí)際應(yīng)用中，考慮到這樣一種情況，就是考試統(tǒng)計(jì)名單中同時(shí)存在沒有考試的學(xué)生和參加考試但是成績(jī)?yōu)?的學(xué)生，RANK函數(shù)不能明確區(qū)分。

1.4 本章小結(jié)

介紹RANK函數(shù)的定義、語(yǔ)法結(jié)構(gòu)以及使用方法，結(jié)合示例進(jìn)行分析，指出RANK函數(shù)的缺陷，RANK函數(shù)不能實(shí)現(xiàn)多類別的同時(shí)排位，而且對(duì)于空和0的判斷也不能滿足要求，需要進(jìn)行改進(jìn)，以提高實(shí)際數(shù)據(jù)處理的效率。

2 改進(jìn)的FRANK函數(shù)

2.1 FRANK函數(shù)的定義

VBA（Visual Basic for Application）是微軟開發(fā)的，在其桌面應(yīng)用程序中執(zhí)行通用的自動(dòng)化（OLE）任務(wù)的編程語(yǔ)言[3]。Excel VBA提供的功能能夠在應(yīng)用程序使用的過程中執(zhí)行，并且能夠?qū)⑹褂肰BA代碼編寫的通用程序和自定義函數(shù)制作成通用的加載項(xiàng)，在不同的場(chǎng)合重復(fù)使用，進(jìn)一步提高了數(shù)據(jù)處理的效率[4]。

FRANK函數(shù)利用Excel VBA編程的方法對(duì)現(xiàn)有的RANK函數(shù)進(jìn)行改進(jìn)實(shí)現(xiàn)?；贔RANK函數(shù)的功能，給出了這樣的定義：返回一個(gè)數(shù)字在數(shù)字列表相同類別數(shù)據(jù)中的排位。其語(yǔ)法結(jié)構(gòu)如表3。

表3 ?FRANK函數(shù)的語(yǔ)法

[語(yǔ)法結(jié)構(gòu)＼&FRANK （number， category， fref， valcol， catcol， order）＼&參數(shù)定義＼&number＼&為需要找到排位的數(shù)字＼&category＼&為當(dāng)前數(shù)字對(duì)應(yīng)所在的類別＼&fref＼&為數(shù)組或?qū)?shù)據(jù)區(qū)域的引用。fref數(shù)字列表中的非數(shù)值型參數(shù)將被忽略＼&valcol＼&為一數(shù)字，表示當(dāng)前排位數(shù)字的數(shù)字列表位于fref中的列號(hào)＼&catcol＼&為一數(shù)字，表示當(dāng)前排位數(shù)字的類別列表位于fref中的列號(hào)＼&order＼&為一數(shù)字，指明排位的方式＼&]

·如果order為0（零）或省略，Microsoft Excel對(duì)數(shù)字的排位是基于fref為按照降序排列的列表。

·如果order不為零，Microsoft Excel對(duì)數(shù)字的排位是基于fref為按照升序排列的列表。

2.2 FRANK函數(shù)的應(yīng)用

通過Excel VBA編程實(shí)現(xiàn)FRANK的功能，并應(yīng)用到如下實(shí)例中。操作方法如下：

⑴ 創(chuàng)建空白工作簿或工作表;

⑵ 按要求填入數(shù)據(jù)。如圖2;

圖2 ?填入的數(shù)據(jù)

⑶ 在排位所在的C列填入函數(shù)，首先在C2處填寫=FRANK（B2，A2，$A$2：$B$9，2，1），然后將自動(dòng)填充柄向下拉動(dòng)到C9處，完成各個(gè)數(shù)據(jù)在當(dāng)前類別中的降序排位。完成后如圖3所示。

圖3 ?排位結(jié)果

2.3 FRANK函數(shù)的優(yōu)勢(shì)

對(duì)圖3排位結(jié)果分析可知，班級(jí)中有“1班”和“2班”兩種類別，假如要用RANK函數(shù)進(jìn)行排位，首先要將數(shù)據(jù)按照班級(jí)列進(jìn)行排序，然后分別對(duì)“1班”和“2班”使用RANK函數(shù)得出排位結(jié)果。而利用新的FRANK函數(shù)則不需要排序，只要對(duì)一個(gè)數(shù)字進(jìn)行排位后，利用自動(dòng)填充柄即可完成所有數(shù)據(jù)排位，效率上提高了，尤其體現(xiàn)在多類別的情況下;而且我們可以發(fā)現(xiàn)對(duì)于同時(shí)出現(xiàn)0和空單元格的情況，F(xiàn)RANK函數(shù)也能區(qū)分，并以“#N/A”標(biāo)注空單元格。由此可見，F(xiàn)RANK函數(shù)很好地彌補(bǔ)了RANK函數(shù)的缺陷，大大提高了數(shù)據(jù)處理的效率。

2.4 本章小結(jié)

針對(duì)RANK函數(shù)在實(shí)際應(yīng)用中不足，對(duì)RANK函數(shù)進(jìn)行改進(jìn)，采用VBA編程實(shí)現(xiàn)FRANK函數(shù)的功能，介紹改進(jìn)后的FRANK函數(shù)的定義和結(jié)構(gòu)。同時(shí)結(jié)合實(shí)例應(yīng)用的分析，體現(xiàn)FRANK函數(shù)的可行性和優(yōu)勢(shì)。

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

Excel內(nèi)置了很多有用的函數(shù)，本文就RANK函數(shù)的定義、功能及應(yīng)用進(jìn)行了研究，分析其存在的一些缺陷，然后結(jié)合FRANK函數(shù)的應(yīng)用技巧，并結(jié)合班級(jí)成績(jī)排名的應(yīng)用實(shí)例，說明FRANK函數(shù)能夠區(qū)分同一類別中同時(shí)出現(xiàn)0和空格的情況，而且最主要的是能夠同時(shí)對(duì)多種類別的數(shù)據(jù)進(jìn)行排位，大大節(jié)省了時(shí)間，提高了工作效率。

Excel有更多功能值得應(yīng)用，例如Excel在其VBA功能的支持下能夠高效率地實(shí)現(xiàn)數(shù)據(jù)處理的自動(dòng)化，將工作人員從簡(jiǎn)單而重復(fù)的數(shù)據(jù)處理工作中解脫出來，更能通過VBA編程對(duì)Excel進(jìn)行二次開發(fā)，實(shí)現(xiàn)很多高級(jí)功能，提高辦公效率[5]。

參考文獻(xiàn)：

[1] 張敏.基于EXCEL函數(shù)的學(xué)生成績(jī)分析與管理[J].農(nóng)業(yè)網(wǎng)絡(luò)信息，

2006.5：157-158

[2] Microsoft Excel 2003 幫助

[3] 伍云輝.Excel VBA辦公應(yīng)用開發(fā)詳解[M].電子工業(yè)出版社，2008.

[4] Excel Home.Excel VBA實(shí)戰(zhàn)技巧精粹[M].人民郵電出版社，2008.

[5] 李小遐.Excel VBA在辦公自動(dòng)化中的應(yīng)用[J].電子測(cè)試，2014.22：

105-106，95