EXCEL計算名次的問題

1、EXCEL計算名次的問題在學(xué)校工作，要用EXCEL對學(xué)生的成績進(jìn)行名次計算。但是出現(xiàn)了一些問題，最后使用了比較麻煩的方法才把名次算出來。下面講一下過程。首先成績表是這樣的:A匚DEFG字號科目一科目二科目三總成績笆次18066506428560606537560&063斗50606066159C7750715680604058&85707073號9080507310100656070.51110080507512908240711310083&D79.514806050&1166087&Q79.51780805672,8.189060&567.5

2、197040304320806040582190807881.422100875090£238060405824100807081"12510080708125000027丨8085S082.52810085658229so70606930807050師一、rank函數(shù)第一個想到的方法是使用EXCEL中自帶的rank函數(shù)。rank函數(shù)可以計算出單元格在某數(shù)列中的排名。函數(shù)編輯好之后對名次列（G列）進(jìn)行公式填充。使用rank函數(shù)后的效果，紅框中代表的是函數(shù)：在學(xué)校工作，要用EXCEL對學(xué)生的成績進(jìn)行名次計算。但是出現(xiàn)了一些問題，最后使用了比較麻煩的方法才把名次算出來。下面講一

3、下過程。首先成績表是這樣的:A匚DEFG字號科目一科目二科目三總成績笆次18066506428560606537560606349060&066n590175071.5680604058R8570707399080507310100656070.511丨10080507512908240711310083SO79.51480605061166087go79.51780805672.818SO60&567.5197040304320806040582190807881.412210087W905238060405824100807031251008070812600002780S

4、5SO82.52810085&58229SO70506gZi3080705066一、rank函數(shù)第一個想到的方法是使用EXCEL中自帶的rank函數(shù)。rank函數(shù)可以計算出單元格在某數(shù)列中的排名。函數(shù)編輯好之后對名次列（G列）進(jìn)行公式填充。使用rank函數(shù)后的效果，紅框中代表的是函數(shù):G2”匕總GRANK(F2jF2;F5RFG總成績名次p&4n140165対163F396637_71.5285873i24L732470.527175五71257951761貶79.51772,821&7.543!3658取S1.41290.5'2'58301811281

5、120i3382.59j02969176620Q>C000000000000006500566654756546585634Soo7940030500£778665Ds-ig6b6060科77607080t280g2836087s()6()4ofi0808760s()8o857070"8085Qo8060oo0(0(1180o10onA學(xué)123斗56891011121314151718192021?2232425茁27282930:但是我們?nèi)绻麑γ芜M(jìn)行一個排序。就發(fā)現(xiàn)結(jié)果很奇怪，如圖所示G3粹赴矗|=RANK(F3J3:F58)A學(xué)號5962224957586145

6、5152535446403655563138502728483741212425匚目ioo&Gloo90loo7070loo90909080looQOloo!25c808n8580loolooloolno9Q1nowo科D科目二9060&7908770509280878&閱85878240309080608585608560808080E科目三廿509090SO70507278SO&05070&5&0303075&070go&5605072767070FG|名次93.561|飪190.51901187.527012754二4587

7、.6181.4g779.5117614751483.53一87185一二4238期'3983,5806882.5826877.571.681.481_811期一再仔細(xì)看編輯欄中的函數(shù)，就發(fā)現(xiàn)問題所在。原來在使用rank函數(shù)進(jìn)行填充的時候函數(shù)中的數(shù)據(jù)列也跟著改變。這個問題比較好解決，將函數(shù)中的相對引用轉(zhuǎn)化成絕對引用就可以了（更改方法即是在行列中間插入一個"$"符號，例如A3更改為A$3），更改好了以后的圖:<3C鳥丨二RAK（巳鬥麗DEF科目二科弓三總成叢55.9431008010090100702712357353630130724471122S_/55CC1

8、311322111Ar6L435合5J.57524s5o»8?52?5L6l.4LLa8781797了83gg4s83g&82S6777181£££280000500350005002800776657883378786657777a207085720000055050000*n9s88B8B8439s&8s6868s8m1009)孔9)80100汕10065丸88)85801001001001009)100100切61512346065*184555S443S532502728483741212425M107TI123456782222

9、2222到這里筆者認(rèn)為已經(jīng)達(dá)成目標(biāo)了，但是對名次列進(jìn)行排序后又出現(xiàn)問題了:113矗1ABCIEFG1學(xué)號科自科目一科目三總成績名次25910090959151322100S79090.52449909090903545100927287-&46571008780875574090878587S836100828085794610085708358103180907583.58112780858082.5L10122810085658211135190SO788141421_1308078814121524100801.TO81141625100807081必1L7388D8080801

10、61852goS760795H7I19131008360795n20166087807951721371008550775-2012253908060762123548088507522241110080507522258857070732426990fiO50i7324271780805672.&262841100507271.6272959077507L528捫.m./?，4Q一_29注意紅框中的內(nèi)容，有兩個同學(xué)并列第八名，但是往下就沒有了第九名，直接到了第十名，并且只要有成績并列的名次都出現(xiàn)了相同的問題。可能rank函數(shù)不適用多個數(shù)值的排名。于是放棄rank函數(shù)，重新思考。二、i

11、f函數(shù)+選擇性粘貼既然rank函數(shù)失敗了，那么就轉(zhuǎn)向新的思考方向。新辦法的思路是這樣:1. 對總成績進(jìn)行降序排序，并且確定第一名。2. 使用if函數(shù)，以總成績第二名的同學(xué)為例。如果第二名同學(xué)的成績不比第一名同學(xué)的成績小，那么第二名同學(xué)的名次就等于第一名同學(xué)的名次。3. 以學(xué)號列為主要關(guān)鍵字進(jìn)行排序，恢復(fù)表格的原始順序。1.對總成績進(jìn)行降序排列，在成績最高的同學(xué)名次單元格上輸入1：G2i1ACDEF01學(xué)號科目一科目科目三總賊績25910090959土5111312100879090544990909090545100927287.66571008780875740908785&7836

12、100828085946100857t>83.5103180907583.511278085808251228100856582Zf5190807881,42190807881.424100807081r251008070813880808080"H5290876079,519131008360795_16r6087807953710085507755390806076"ZT543088507511100BO5075_258857070r73f2&990805073271780805672.828411006072九629590775071.5_Z1?nnR?

13、712.在第二名同學(xué)的名次單元格中輸入if函數(shù)，if(F3<F2,G2+1,G2)該函數(shù)的目的在于，如果第二名同學(xué)的總成績(F3)小于第一名同學(xué)的總成績(F2)，即(F3<F2)。那么第二名同學(xué)的名次等于第一名同學(xué)的名次加一(G2+1);如果不小于，那么就與第一名同學(xué)的名次相等(G2)。隨后填充公式，發(fā)現(xiàn)剛才rank函數(shù)的問題已經(jīng)解決，并列第八名的兩位同學(xué)的下一個同學(xué)就為正常的第九名。<53A=IFCF3<F2rG2+LG21913100S360795142016608780795142137100855D77.51522539080601761623548088507

14、517241110030507517258857070731826g908D507318271780805672,8192841100607271.62029590775071521.T.11_9。-艮T1u111_Z1.77ABCDEFG1 字號科目一科目二科目三總成叢名次2 593 224 495 456 577 408 361-2-345678890500205005999788&77070277250QJ8998888QJ10010090100100901001068094610J31112780S5808Z59122810085658210135190807881,41114

15、2190&07881.4111524100807Q8112162510080708112173880SO8080131852908760795143.最后一步即是要將表格按照學(xué)號進(jìn)行排序，恢復(fù)表格的原始數(shù)據(jù)排列可是排序時又出現(xiàn)了問題:<52扎=IF(F2<F1G1+1)G1)BCDIEF1G1字號科目一科目科目三名次21806650&#VALU£VALUE!4375606063rWALUE!54如606066r#VALUE!65907750715FLVALUE!76S0504058"#VALUE!8S35707073尸W

16、ALUE!9g90805073FLVALUE!1010100656070>5LVALUE!1111100805075rLVALUE!121290824071r#VALUE!13131008360795rLVALUE!14WSO605061LVALUE!1516&0878079.5"#VALUE!161780SO5672.8Lvalue!1718W60656?.5r#VALUE1181970403D43r#VALUE!192080604058VALUE!202190807881.4*#VALUE!2122100879090.5r#VALUE!222380604058Lva

17、lue!2324100807081F#VALUE!2425100807081"mLUEl25260000rWALUE!2$27808580825r#VALUE!2728100856582F*VALUE»282580706069r#VALUE!293000705066r#VALUEl10L萄亠HD9()T75rWAIIJF'問題在于筆者計算排名的時候使用的是函數(shù)，重新進(jìn)行排序以后順序變動，無法找準(zhǔn)if函數(shù)處理后得到的值造成的解決這個問題的辦法是將函數(shù)得到的值直接轉(zhuǎn)化為數(shù)字，再進(jìn)行排列。先選中名次列->復(fù)制->右鍵->選擇性粘貼，在對話框中選擇數(shù)值詁貼

18、（El撞入期的單元桶舊撿11222222選擇數(shù)值以后名次列的函數(shù)全部轉(zhuǎn)化為數(shù)值1222清護(hù)內(nèi)容理設(shè)置單元格栢式舊“旣當(dāng)陰薦妙右鍵->選擇性粘貼述揮性牯貼全部（單佛胡藥單元®知£)外姜遭列寬兇1SOT)就主©值瞬字格式也有效性驗證迥）無0&魚加畑C跺跌過三單元迪EEElaBIj"LT選擇數(shù)值2A科目名次9590909010092728780568570880go758.5880858098565B21090807881.4908078118070811270BO8112808080906087801485508050751725857070990SO50181780805672.828416072202959077507152112fi?77767516179010090SO6050808S&0&085808782878579.579.5100100878393.5E科目三編輯欄中的函數(shù)變?yōu)閿?shù)字最后將學(xué)號列作為關(guān)鍵字進(jìn)行升序排列，表格恢復(fù)到原始狀態(tài)，并且排名列也填好，問題得以解決10010010010010010010010071.63221008790.5F2學(xué)號59A49545657140836946103111271228瓦51訶2115241625