Excel中RANK函數(shù)的改進和應用

陳煒東

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

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

中圖分類號：TP317.3 ? ? ? ? ?文獻標志碼：A ? ? 文章編號：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)計處理數(shù)據(jù)的方法，大大加快了數(shù)據(jù)處理的速度，提高了辦公效率。人們對于Excel的使用，不僅僅是記錄數(shù)據(jù)，主要還是對數(shù)據(jù)的分析處理。無論是各行各業(yè)各部門的辦公人員，還是教師或學生都離不開辦公應用軟件的使用。教師用EXCEL對學生成績進行管理更普遍，快捷，方便[1]。RANK函數(shù)是Excel自帶的函數(shù)之一，它常用于數(shù)據(jù)的排位，但是RANK函數(shù)限制一次只能對相同類別的數(shù)據(jù)進行排位，不能對多種類別的數(shù)據(jù)同時排位，隨著數(shù)據(jù)類別的增多，需要人工操作RANK函數(shù)的次數(shù)也就相對增多，降低了數(shù)據(jù)處理的效率。

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

1.1 RANK函數(shù)的定義

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

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

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

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

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

1.2 RANK函數(shù)的示例

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

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

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

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

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

表2 ?排位結果說明

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

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

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

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

⑵ 對于空和0的判斷不明顯

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

1.4 本章小結

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

2 改進的FRANK函數(shù)

2.1 FRANK函數(shù)的定義

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

FRANK函數(shù)利用Excel VBA編程的方法對現(xiàn)有的RANK函數(shù)進行改進實現(xiàn)。基于FRANK函數(shù)的功能，給出了這樣的定義：返回一個數(shù)字在數(shù)字列表相同類別數(shù)據(jù)中的排位。其語法結構如表3。

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

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

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

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

2.2 FRANK函數(shù)的應用

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

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

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

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

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

圖3 ?排位結果

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

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

2.4 本章小結

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

3 結束語

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

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

參考文獻：

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

2006.5：157-158

[2] Microsoft Excel 2003 幫助

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

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

[5] 李小遐.Excel VBA在辦公自動化中的應用[J].電子測試，2014.22：

105-106，95