1.用Excel函數(shù)自動排名次
筆者認(rèn)為無論對原數(shù)據(jù)清單進(jìn)行排序或篩選最好不要破壞原清單的原貌。EXCEL的函數(shù)十分豐富，不用宏，用函數(shù)也能解決數(shù)值自動排名。方法如下。
如第一行為表頭，A列(例如A2:A101，下同)為姓名，B列數(shù)據(jù)，在C2單元格輸入公式“=IF(A2=0,0,INT(CONCATENATE(INT(B2),200-ROW(A1))))”。公式中ROW(A1)為A1單元格所在的行數(shù)即為1，(該公式下拉時依次為2、3、4.....),用200來減是為了CONCATENATE函數(shù)中的第2個參數(shù)保持3位數(shù)，CONCATENATE函數(shù)是一個拼合函數(shù)這里把B列的數(shù)據(jù)和它所在的行數(shù)拼合成一個數(shù)據(jù)。這樣在對它進(jìn)行排序后該數(shù)據(jù)包含了它所在行數(shù)的信息。CONCATENATE函數(shù)INT函數(shù)套用是為了把原來的文本變?yōu)閿?shù)字。
在D2單元格輸入公式“=LARGE(C:C,ROW(A1))”即對B列數(shù)值(包含所在行的信息)按大小排列。
在F2單元格(為了與原始清單分開中間空了一列)輸入公式“=IF(D2=0,0,200-RIGHT(D2,3))”，函數(shù)RIGHT(D2,3)即為D2單元格數(shù)據(jù)的后3位數(shù)，用200來減即為此數(shù)據(jù)所在的行數(shù)。
在G2單元格輸入公式“=IF($F2=0,0,INDEX($A$2:$B$15,$F2,COLUMN(A1)))”，并拖到H2單元格。INDEX函數(shù)為引用函數(shù)，即根據(jù)F2單元格所標(biāo)明的行數(shù)在$A$2:$B$15單元格矩陣中引用姓名及得分。
在I2單元格輸入公式“=IF(H2=0,0,IF(H2=H1,I1,ROW(A1)))”，本來G、H列就是按得分大小排列的，但可能有平列名次，所以選用上述公式。
最后把C2到I2單元格的公式下拉，程序就完成了。
人數(shù)較多(例如500人左右)的機關(guān)、團體、單位的人事管理部門，或者戶籍管理部門，都會遇到重名的問題。例如筆者所在單位783人就有12人6對重名。在用EXCEL電子表格制作各類管理文件時重名會帶來很多問題(例如以姓名作參數(shù)用VLOOKUP函數(shù)，來查找該人的信息時就會出錯)。因此有一個方便快速的重名檢索辦法就十分必要。(筆者根據(jù)經(jīng)驗建議凡用EXCEL電子表格進(jìn)行辦公業(yè)務(wù)自動化管理的單位，應(yīng)給每個人設(shè)立一個代碼，像居民身份證號碼一樣是終身的唯一的，不要把調(diào)離、退休等人員的代碼用于新增人員)。
方法如下：
先制作空表格：把本文所附的只有2行的表頭打開，下拉菜單“編輯”、點擊“定位”在引用位置欄輸入“B2:H1001”、按“確定”、再下拉菜單“編輯”、點擊“填充”、“向下填充”空表格制作完成。(這是大量填充單元格的最快方法)。然后把姓名清單從A2單元格開始拷貝至A列。這樣檢索程序操作就完成了。用該檢索程序，在奔III733機器上1秒鐘內(nèi)便完成了783人的重名檢索。
下面簡單介紹B2至H2單元格的公式，B2單元格“=IF(A2=0,0,SUBSTITUTE(A2,"",""))”中SUBSTITUTE函數(shù)是去掉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個姓名開始計數(shù)的行數(shù)。意即B3往下有重名時標(biāo)明行數(shù)，否則為零。D2單元格“=LARGE(C:C,ROW(A1))”就是把重名所在行的行數(shù)從大到小進(jìn)行排列。E2單元格“=IF(D2=0,0,INDEX(B$2:B$1001,D2))”就是在B列根據(jù)D2單元格標(biāo)明的行數(shù)查找重名的姓名。F2單元格“=IF(E2=0,0,IF(ISERROR(VLOOKUP(E2,E3:E$1001,1,FALSE))=TRUE,ROW(A1),0))”與C2單元格的公式相似，只是根據(jù)條件取舍相反。即讓已檢出的重名只出現(xiàn)一次。G2單元格的公式“=IF(ROW(A1)>COUNTIF(F:F,">0"),0,INDEX(E$2:E$1001,LARGE(F:F,ROW(A1))))”就是對F列標(biāo)明的行數(shù)，按大到小進(jìn)行排列并在E列查找重名的姓名。H2單元格的公式“=IF(G2=0,0,COUNTIF(B:B,G2))”就是對B列在G列列出的重名進(jìn)行計數(shù)。下表為工作表的前三行。
此程序稍作改變便能用來統(tǒng)計姓氏的頻數(shù)與頻率。
方法如下：
先制作空表格：把本文所附的只有4行的表頭打開，用上述方法填充B4:l1002單元格.再把姓名清單從A3單元格開始拷貝至A列。這樣姓氏的頻數(shù)與頻率統(tǒng)計程序操作就完成了。下表為工作表的前五行。
筆者在奔III733計算機上制作一張統(tǒng)計10000人姓氏頻數(shù)與頻率的空表需時6分37秒，復(fù)制這樣一張空表瞬時就能完成，在空表上填充10000人的姓名后統(tǒng)計姓氏頻數(shù)與頻率的時間為2分42秒。填充完后文件大小為4996k。筆者所以測試以上時間是筆者有一個強烈的愿望：把程序用于全國千分之一到萬分之一抽樣人口即12萬到120萬人的姓氏頻數(shù)與頻率的統(tǒng)計。筆者在此請求網(wǎng)友支持，提供你能到的某一群體人員的姓氏或姓名樣本，和所在省市。筆者每收集到1萬個樣本便在網(wǎng)站公布一次姓氏頻數(shù)與頻率的統(tǒng)計結(jié)果。
本文試圖用Execl的函數(shù)來解決上述問題。
一、用函數(shù)實現(xiàn)排序
題目
如有一張工資表，A2:F501,共6列500行3000個單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為津貼、D1為獎金、E1為工資、F1收入合計?，F(xiàn)要求對職工收入從多到少排序，且在職工總收入相同時再按工資從多到少排序，在職工總收入和工資相同時再按獎金從多到少排序，在職工職工總收入和工資、獎金相同時再按津貼從多到少排序。
方法
“=if(F2=0,10^100,INT(CONCATENATE(999-f2,999-e2,999-d2,999-c2)))”，
CONCATENATE是一個拼合函數(shù)，可以把30個以下的單元的數(shù)據(jù)拼合成一個數(shù)據(jù)，這些被拼合的數(shù)據(jù)之間用逗號分開。用f2、e2等被拼合的數(shù)據(jù)用999來減，是為了使它們位數(shù)相同。(假定任何一個職工的總收入少于899元)。被拼合成的函數(shù)是文本函數(shù)，CONCATENATE與INT函數(shù)套用是為了使文本轉(zhuǎn)換為數(shù)字。最外層的if函數(shù)是排序時用來剔除不進(jìn)行排序的記錄，在本例中指收入為零的記錄。(在上文提到的職工年齡排序，則公式改為“if(f2="退休",10^100,.....)”,即剔除了退休職工。)
對上述程序稍作變化還可得到更多用度。上面例子數(shù)據(jù)是從大到小排列的，如H列的函數(shù)中的SMALL改為LARGE，上面例子數(shù)據(jù)就從小到大排列了。如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上面例子數(shù)據(jù)是從大到小排列的，O1單元格輸入1以外的數(shù)上面例子數(shù)據(jù)就從小到大排列了。
如在H列前插入若干列，如插入一列，則現(xiàn)在的H列輸入類似G列的公式，例如
“=if(F2=0,10^100,d2)”,現(xiàn)在的I列的公式改為“=IF(P1=1,MATCH(SMALL(G:G,ROW(A1)),G:G,0),
MATCH(SMALL(H:H,ROW(A1)),H:H,0)))”
即在P單元格輸入1以外的值就實現(xiàn)了按獎金大小排序.這樣只要通過改變P1(原來的O1單元格)單元格內(nèi)容的改變就能立即得到按不同要求的排序。
二、用函數(shù)實現(xiàn)篩選
題目
如有一張職工名冊表，A2:F501,共6列500行3000個單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為性別、D1為年齡、E1為學(xué)歷、F1職稱?，F(xiàn)要求對職工的性別、年齡、學(xué)歷、職稱進(jìn)行交錯篩選，例如要求在同一張表上篩選出1、女的年齡在22歲到45歲，男的年齡在25歲到50歲，2、女博士，3、男博士后。
方法
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列的公式的含義根據(jù)K2的數(shù)值選擇G、H、I中的一列進(jìn)行排序并把不合條件的行除去。
公式“=IF($J2=0,0,INDEX($A$2:$F$501,$J2,COLUMN(A$1)))”，然后向右拖放到Q2，再向下拖放。INDEX函數(shù)的含義上文已說明。
2000年中國甲A聯(lián)賽程序由4個工資簿組成。
第1個工作簿足球2001.xls由9張工作表組成。每個球隊有一個代碼，在代碼表上可查到。本工作簿只需每輪比賽后在比分表上錄入各球隊間的比分，其他各表的統(tǒng)計數(shù)據(jù)都是自動生成的。在各輪排名表、主客場統(tǒng)計表、勝負(fù)表和隊間比表上還有用紅色底色標(biāo)明的可選項，在這些單元格可輸入你所關(guān)注的球隊的代碼來查詢有關(guān)該球隊的統(tǒng)計數(shù)據(jù)。
第2個工作簿足球2002.xls是用來統(tǒng)計計算各球隊及隊員進(jìn)球及進(jìn)球時間。本工作表需在每輪比賽后在C列錄入輪次、D列錄入球隊代碼、E列錄入隊員號碼、某隊獲得烏龍球時其球員號填入40，I、J列分別錄入所進(jìn)球在上下半場的時間。這些數(shù)據(jù)在每輪賽后由中國足協(xié)網(wǎng)站www.fa.org.com發(fā)布。錄入上述數(shù)據(jù)后便可通過射手榜、總射手榜、進(jìn)球時間分布和烏龍球等4張表查閱各類統(tǒng)計數(shù)據(jù)。由于作者錄入的數(shù)據(jù)與中國足協(xié)發(fā)布的統(tǒng)計資料可能有差別，因此本工作簿只供球迷參考。
第3個工作簿足球2003.xls是對下一輪各對陣球隊的比分進(jìn)行預(yù)測，前5輪是根據(jù)該兩隊上一年主客場的得分能力、第6輪開始根據(jù)前5輪主客場的得分能力來預(yù)測，沒有考慮其他各類因素因此準(zhǔn)確率不高，作者用此程序?qū)?8－2000年預(yù)測的比分準(zhǔn)確率都為14％，勝平負(fù)預(yù)測準(zhǔn)確率為45－50％之間。
第4個工作簿足球2004.xls是用來統(tǒng)計計算各球隊及隊員紅黃牌數(shù)、處罰和停賽場次。本工作表需在每輪比賽后在C列錄入輪次、D列錄入球隊代碼、E列錄入隊員號碼、I、J、K、L列分別錄入所得黃牌、黃紅牌、紅牌及被處罰定賽場數(shù)。這些數(shù)據(jù)在每輪賽后由中國足協(xié)網(wǎng)站www.fa.org.com發(fā)布。錄入上述數(shù)據(jù)后便可通過各隊處罰匯總及停賽名單與場次兩工作表查閱處罰與停賽的統(tǒng)計資料。由于作者對中國足協(xié)競賽規(guī)則的理解不盡準(zhǔn)確與中國足協(xié)發(fā)布的統(tǒng)計資料可能有差別，因此本工作簿只供球迷參考。
聯(lián)系客服
