Excel中函數的應用實例

1.用Excel函數自動排名次

　　筆者認為無論對原數據清單進行排序或篩選最好不要破壞原清單的原貌。EXCEL的函數十分豐富，不用宏，用函數也能解決數值自動排名。方法如下。

　　如第一行為表頭，A列(例如A2:A101，下同)為姓名，B列數據，在C2單元格輸入公式「=IF(A2=0,0,INT(CONCATENATE(INT(B2),200-ROW(A1))))」。公式中ROW(A1)為A1單元格所在的行數即為1，(該公式下拉時依次為2、3、4.....),用200來減是為了CONCATENATE函數中的第2個參數保持3位數，CONCATENATE函數是一個拼合函數這裡把B列的數據和它所在的行數拼合成一個數據。這樣在對它進行排序後該數據包含了它所在行數的信息。CONCATENATE函數INT函數套用是為了把原來的文本變為數字。

　　在D2單元格輸入公式「=LARGE(C:C,ROW(A1))」即對B列數值(包含所在行的信息)按大小排列。

　　在F2單元格(為了與原始清單分開中間空了一列)輸入公式「=IF(D2=0,0,200-RIGHT(D2,3))」，函數RIGHT(D2,3)即為D2單元格數據的後3位數，用200來減即為此數據所在的行數。

　　在G2單元格輸入公式「=IF($F2=0,0,INDEX($A$2:$B$15,$F2,COLUMN(A1)))」，並拖到H2單元格。INDEX函數為引用函數，即根據F2單元格所標明的行數在$A$2:$B$15單元格矩陣中引用姓名及得分。

　　在I2單元格輸入公式「=IF(H2=0,0,IF(H2=H1,I1,ROW(A1)))」，本來G、H列就是按得分大小排列的，但可能有平列名次，所以選用上述公式。

　　最後把C2到I2單元格的公式下拉，程序就完成了。

2.重名檢索與姓氏頻率統計

　　人數較多(例如500人左右)的機關、團體、單位的人事管理部門，或者戶籍管理部門，都會遇到重名的問題。例如筆者所在單位783人就有12人6對重名。在用EXCEL電子表格製作各類管理文件時重名會帶來很多問題(例如以姓名作參數用VLOOKUP函數，來查找該人的信息時就會出錯)。因此有一個方便快速的重名檢索辦法就十分必要。(筆者根據經驗建議凡用EXCEL電子表格進行辦公業務自動化管理的單位，應給每個人設立一個代碼，像居民身份證號碼一樣是終身的唯一的，不要把調離、退休等人員的代碼用於新增人員)。

　　方法如下：

　　先製作空表格：把本文所附的只有2行的表頭打開，下拉菜單「編輯」、點擊「定位」在引用位置欄輸入「B2:H1001」、按「確定」、再下拉菜單「編輯」、點擊「填充」、「向下填充」空表格製作完成。(這是大量填充單元格的最快方法)。然後把姓名清單從A2單元格開始拷貝至A列。這樣檢索程序操作就完成了。用該檢索程序，在奔III733機器上1秒鐘內便完成了783人的重名檢索。

　　下面簡單介紹B2至H2單元格的公式，B2單元格「=IF(A2=0,0,SUBSTITUTE(A2,"",""))」中SUBSTITUTE函數是去掉A2單元格中的名字的前、後、中間的空格，C2單元格「=IF(B2=0,0,IF(ISERROR(VLOOKUP(B2,B3:B$1001,1,FALSE))=TRUE,0,ROW(A1)))」中ISERROR(VLOOKUP(B2,B3:B$1001,1,FALSE))=TRUE,0,ROW(A1))即如在B3到B1001單元格中找不到與B2相同的姓名時為零，否則為從第1個姓名開始計數的行數。意即B3往下有重名時標明行數，否則為零。D2單元格「=LARGE(C:C,ROW(A1))」就是把重名所在行的行數從大到小進行排列。E2單元格「=IF(D2=0,0,INDEX(B$2:B$1001,D2))」就是在B列根據D2單元格標明的行數查找重名的姓名。F2單元格「=IF(E2=0,0,IF(ISERROR(VLOOKUP(E2,E3:E$1001,1,FALSE))=TRUE,ROW(A1),0))」與C2單元格的公式相似，只是根據條件取捨相反。即讓已檢出的重名只出現一次。G2單元格的公式「=IF(ROW(A1)>COUNTIF(F:F,">0"),0,INDEX(E$2:E$1001,LARGE(F:F,ROW(A1))))」就是對F列標明的行數，按大到小進行排列並在E列查找重名的姓名。H2單元格的公式「=IF(G2=0,0,COUNTIF(B:B,G2))」就是對B列在G列列出的重名進行計數。下表為工作表的前三行。

　　此程序稍作改變便能用來統計姓氏的頻數與頻率。

　　方法如下：

　　先製作空表格：把本文所附的只有4行的表頭打開，用上述方法填充B4:l1002單元格.再把姓名清單從A3單元格開始拷貝至A列。這樣姓氏的頻數與頻率統計程序操作就完成了。下表為工作表的前五行。

　　用該程序對筆者所在單位783人統計有160個姓氏，張姓最多有95人出現頻率為12.1%。樣本太少不具全國姓氏的頻數與頻率統計上的意義，但似乎張姓為中國第一大姓。B到F列的公式與重名檢索工作表的公式極相似，G到K列的公式在筆者的「排序與篩選」一文中有詳細說明。

　　筆者在奔III733計算機上製作一張統計10000人姓氏頻數與頻率的空表需時6分37秒，複製這樣一張空表瞬時就能完成，在空表上填充10000人的姓名後統計姓氏頻數與頻率的時間為2分42秒。填充完後文件大小為4996k。筆者所以測試以上時間是筆者有一個強烈的願望：把程序用於全國千分之一到萬分之一抽樣人口即12萬到120萬人的姓氏頻數與頻率的統計。筆者在此請求網友支持，提供你能到的某一群體人員的姓氏或姓名樣本，和所在省市。筆者每收集到1萬個樣本便在網站公布一次姓氏頻數與頻率的統計結果。

3.用Excel函數排序與篩選

Execl本身具有很方便的排序與篩選功能，下拉「數據」菜單即可選擇排序或篩選對數據清單進行排序或篩選。但也有不足，首先無論排序或篩選都改變了原清單的原貌，特別是清單的數據從其它工作錶鏈接來而源數據發生變化時，或清單錄入新記錄時必須從新進行排序或篩選。其次還有局限，例如排序只能最多對三個關鍵字(三列數據)排序，篩選對同一列數據可用「與」、或「或」條件篩選，但對不同列數據只能用「與」條件篩選。例如對某張職工花名冊工作簿，要求篩選出年齡大於25歲且小於50歲或年齡大於50歲或小於25歲都是可行的，如同時要求性別是男的或女的也是可行的。但要求篩選出女的年齡在22歲到45歲，男的年齡在25歲到50歲時Execl本身具有的篩選功能則無能為力了。再者排序與篩選不能結合使用，即不能在排序時根據條件篩選出來的記錄進行排序。例如有一張職工資料清單，其中有的職工已經退休，對在職職工的年齡進行排序時無法剔除已退休職工的數據。

本文試圖用Execl的函數來解決上述問題。

一、用函數實現排序

題目

如有一張工資表，A2:F501,共6列500行3000個單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為津貼、D1為獎金、E1為工資、F1收入合計。現要求對職工收入從多到少排序，且在職工總收入相同時再按工資從多到少排序，在職工總收入和工資相同時再按獎金從多到少排序，在職工職工總收入和工資、獎金相同時再按津貼從多到少排序。

方法

G1單元格填入公式

「=if(F2=0,10^100,INT(CONCATENATE(999-f2,999-e2,999-d2,999-c2)))」，

CONCATENATE是一個拼合函數，可以把30個以下的單元的數據拼合成一個數據，這些被拼合的數據之間用逗號分開。用f2、e2等被拼合的數據用999來減，是為了使它們位數相同。(假定任何一個職工的總收入少於899元)。被拼合成的函數是文本函數，CONCATENATE與INT函數套用是為了使文本轉換為數字。最外層的if函數是排序時用來剔除不進行排序的記錄，在本例中指收入為零的記錄。(在上文提到的職工年齡排序，則公式改為「if(f2="退休",10^100,.....)」,即剔除了退休職工。)

第二步把G1單元格的公式拖放到G500單元格(最簡便的方法是點擊G1單元格後向G1單元格右下方移動滑鼠，見到黑十時雙擊滑鼠就完成了G1到G500的填充)。

第三步在在H2單元填入公式「=MATCH(SMALL(G:G,ROW(A1)),G:G,0)」與第二步一樣拖放到H501單元格。此公式實際上是把三列公式合成一列公式，ROW(A1)即為A1的行數是1，隨著向下拖放依次為2、3、4...,SMALL(G:G,ROW(A1))為G列中最小的數隨著向下拖放依次為第2、第3、..小的數，MATCH(SMALL(G:G,ROW(A1)),G:G,0)即為G列各行的數據中最小、第2、第3小等的數據在第幾行。

第四步把A1至F1單元格的表頭複製到I1至N1單元格，在I2單元格輸入公式「=INDEX($A$2:$F$501,$H2,COLUMN(A$1))」INDEX函數是一個引用函數，即把$A$2:$F$501單元格列陣第$H2行第COLUMN(A$1)列的數據放入I2單元格。然後把I2單元格的公式拖放到N2單元格，點擊N2單元格後向N2單元格右下方移動滑鼠見到黑十時雙擊滑鼠就完成了I2到N501單元格的填充到此全部完成。

以上敘述看似繁雜實際非常簡單，只要把A1至F1的表頭複製到I1至N1單元格，再分別在G1、H2、I2單元格輸入公式然後向下拖放，即使對EXCEL應用不熟練的同志一分鍾內便能完成。

對上述程序稍作變化還可得到更多用度。上面例子數據是從大到小排列的，如H列的函數中的SMALL改為LARGE，上面例子數據就從小到大排列了。如H2單元格的公式改為「=IF(O1=1,MATCH(SMALL(G:G,ROW(A1)),G:G,0),MATCH(LARGE(G:G,ROW(A1)),G:G,0))」

並把H2單元格的公式向下拖放。這樣在O1單元格輸入1上面例子數據是從大到小排列的，O1單元格輸入1以外的數上面例子數據就從小到大排列了。

如在H列前插入若干列，如插入一列，則現在的H列輸入類似G列的公式，例如

「=if(F2=0,10^100,d2)」,現在的I列的公式改為「=IF(P1=1,MATCH(SMALL(G:G,ROW(A1)),G:G,0),

MATCH(SMALL(H:H,ROW(A1)),H:H,0)))」

即在P單元格輸入1以外的值就實現了按獎金大小排序.這樣只要通過改變P1(原來的O1單元格)單元格內容的改變就能立即得到按不同要求的排序。

二、用函數實現篩選

題目

如有一張職工名冊表，A2:F501,共6列500行3000個單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為性別、D1為年齡、E1為學歷、F1職稱。現要求對職工的性別、年齡、學歷、職稱進行交錯篩選，例如要求在同一張表上篩選出1、女的年齡在22歲到45歲，男的年齡在25歲到50歲，2、女博士，3、男博士後。

方法

第一步在G2單元格輸入公式」=IF(OR(AND(C2="女",D2>=22,D2<=45),AND(C2="男",

D2>=25,D2<=50)),ROW(A1),0)「，在H2單元格輸入公式」=IF(AND(C2="女",E2="博士"),

ROW(B1),0)「，在I2單元格輸入公式」=IF(AND(C2="男",E2="博士後"),ROW(B1),0)「。在J2單元格輸入公式「=IF(K$2=1,LARGE(G:G,ROW(A1)),IF(K$2=2,LARGE(H:H,ROW(A1)),

IF(K$2=3,LARGE(I:I,ROW(A1)),0)))」然後用上述提到的方法向下拖放。G、H、I列的公式的含義就是凡符合篩選條件的行記錄下行號否則為零，J列的公式的含義根據K2的數值選擇G、H、I中的一列進行排序並把不合條件的行除去。

第二步在K1單元格輸文字」篩選選擇」，A1到F1表頭複製到L1到Q1，在L2單元格輸入

公式「=IF($J2=0,0,INDEX($A$2:$F$501,$J2,COLUMN(A$1)))」，然後向右拖放到Q2，再向下拖放。INDEX函數的含義上文已說明。

第三步在P1單元格輸入1或2或3便可實現上述三種篩選。

　　2000年中國甲A聯賽程序由4個工資簿組成。

　　第1個工作簿足球2001.xls由9張工作表組成。每個球隊有一個代碼，在代碼表上可查到。本工作簿只需每輪比賽後在比分表上錄入各球隊間的比分，其他各表的統計數據都是自動生成的。在各輪排名表、主客場統計表、勝負表和隊間比表上還有用紅色底色標明的可選項，在這些單元格可輸入你所關注的球隊的代碼來查詢有關該球隊的統計數據。

　　第2個工作簿足球2002.xls是用來統計計算各球隊及隊員進球及進球時間。本工作表需在每輪比賽後在C列錄入輪次、D列錄進球隊代碼、E列錄入隊員號碼、某隊獲得烏龍球時其球員號填入40，I、J列分別錄入所進球在上下半場的時間。這些數據在每輪賽後由中國足協網站www.fa.org.com發布。錄入上述數據後便可通過射手榜、總射手榜、進球時間分布和烏龍球等4張表查閱各類統計數據。由於作者錄入的數據與中國足協發布的統計資料可能有差別，因此本工作簿只供球迷參考。

　　第3個工作簿足球2003.xls是對下一輪各對陣球隊的比分進行預測，前5輪是根據該兩隊上一年主客場的得分能力、第6輪開始根據前5輪主客場的得分能力來預測，沒有考慮其他各類因素因此準確率不高，作者用此程序對98－2000年預測的比分準確率都為14％，勝平負預測準確率為45－50％之間。

　　第4個工作簿足球2004.xls是用來統計計算各球隊及隊員紅黃牌數、處罰和停賽場次。本工作表需在每輪比賽後在C列錄入輪次、D列錄進球隊代碼、E列錄入隊員號碼、I、J、K、L列分別錄入所得黃牌、黃紅牌、紅牌及被處罰定賽場數。這些數據在每輪賽後由中國足協網站www.fa.org.com發布。錄入上述數據後便可通過各隊處罰匯總及停賽名單與場次兩工作表查閱處罰與停賽的統計資料。由於作者對中國足協競賽規則的理解不盡準確與中國足協發布的統計資料可能有差別，因此本工作簿只供球迷參考。