在學(xué)校工作，要用EXCEL對(duì)學(xué)生的成績(jī)進(jìn)行名次計(jì)算。但是出現(xiàn)了一些問(wèn)題，最后使用了比較麻煩的方法才把名次算出來(lái)。下面講一下過(guò)程。
首先成績(jī)表是這樣的：
一、rank函數(shù)
第一個(gè)想到的方法是使用EXCEL中自帶的rank函數(shù)。rank函數(shù)可以計(jì)算出單元格在某數(shù)列中的排名。函數(shù)編輯好之后對(duì)名次列(G列)進(jìn)行公式填充。
使用rank函數(shù)后的效果，紅框中代表的是函數(shù)：
但是我們?nèi)绻麑?duì)名次進(jìn)行一個(gè)排序。就發(fā)現(xiàn)結(jié)果很奇怪，如圖所示:
再仔細(xì)看編輯欄中的函數(shù)，就發(fā)現(xiàn)問(wèn)題所在。原來(lái)在使用rank函數(shù)進(jìn)行填充的時(shí)候函數(shù)中的數(shù)據(jù)列也跟著改變。
這個(gè)問(wèn)題比較好解決，將函數(shù)中的相對(duì)引用轉(zhuǎn)化成絕對(duì)引用就可以了(更改方法即是在行列中間插入一個(gè)"$"符號(hào)，例如A3更改為A$3)，更改好了以后的圖：
到這里筆者認(rèn)為已經(jīng)達(dá)成目標(biāo)了，但是對(duì)名次列進(jìn)行排序后又出現(xiàn)問(wèn)題了：
注意紅框中的內(nèi)容，有兩個(gè)同學(xué)并列第八名，但是往下就沒(méi)有了第九名，直接到了第十名，并且只要有成績(jī)并列的名次都出現(xiàn)了相同的問(wèn)題。
可能rank函數(shù)不適用多個(gè)數(shù)值的排名。于是放棄rank函數(shù)，重新思考。
二、if函數(shù)+選擇性粘貼
既然rank函數(shù)失敗了，那么就轉(zhuǎn)向新的思考方向。新辦法的思路是這樣：
1.對(duì)總成績(jī)進(jìn)行降序排序，并且確定第一名。
2.使用if函數(shù)，以總成績(jī)第二名的同學(xué)為例。
⑴如果第二名同學(xué)的成績(jī)比第一名同學(xué)的成績(jī)小，那么第二名同學(xué)的名次就等于第一名同學(xué)的名次再加一。
⑵如果第二名同學(xué)的成績(jī)不比第一名同學(xué)的成績(jī)小，那么第二名同學(xué)的名次就等于第一名同學(xué)的名次。
3.以學(xué)號(hào)列為主要關(guān)鍵字進(jìn)行排序，恢復(fù)表格的原始順序。
1.對(duì)總成績(jī)進(jìn)行降序排列，在成績(jī)最高的同學(xué)名次單元格上輸入1：
2.在第二名同學(xué)的名次單元格中輸入if函數(shù)，if(F3<F2,G2+1,G2)
該函數(shù)的目的在于，如果第二名同學(xué)的總成績(jī)(F3)小于第一名同學(xué)的總成績(jī)(F2)，即(F3<F2)。那么第二名同學(xué)的名次等于第一名同學(xué)的名次加一(G2+1）；如果不小于，那么就與第一名同學(xué)的名次相等(G2)。隨后填充公式，發(fā)現(xiàn)剛才rank函數(shù)的問(wèn)題已經(jīng)解決，并列第八名的兩位同學(xué)的下一個(gè)同學(xué)就為正常的第九名。
3.最后一步即是要將表格按照學(xué)號(hào)進(jìn)行排序，恢復(fù)表格的原始數(shù)據(jù)排列。
可是排序時(shí)又出現(xiàn)了問(wèn)題：
問(wèn)題在于筆者計(jì)算排名的時(shí)候使用的是函數(shù)，重新進(jìn)行排序以后順序變動(dòng)，無(wú)法找準(zhǔn)if函數(shù)處理后得到的值造成的。
解決這個(gè)問(wèn)題的辦法是將函數(shù)得到的值直接轉(zhuǎn)化為數(shù)字，再進(jìn)行排列。
先選中名次列 -> 復(fù)制 -> 右鍵 -> 選擇性粘貼，在對(duì)話框中選擇數(shù)值
右鍵->選擇性粘貼
選擇數(shù)值
選擇數(shù)值以后名次列的函數(shù)全部轉(zhuǎn)化為數(shù)值
編輯欄中的函數(shù)變?yōu)閿?shù)字
最后將學(xué)號(hào)列作為關(guān)鍵字進(jìn)行升序排列，表格恢復(fù)到原始狀態(tài)，并且排名列也填好，問(wèn)題得以解決。
總結(jié):由于筆者對(duì)于EXCEL了解不是很多，對(duì)函數(shù)部分了解得也太少。所以暫時(shí)只想到這個(gè)辦法。但是這個(gè)方法始終過(guò)于繁瑣，可能使用某個(gè)函數(shù)或者某幾個(gè)函數(shù)嵌套的方法或者在EXCEL中嵌入VB語(yǔ)言可以實(shí)現(xiàn)一步到位的計(jì)算名次。希望知道更簡(jiǎn)單方法的朋友可以告之，也歡迎大家和我討論好的方法。
聯(lián)系客服
