如何用Excel函數(shù)快速統(tǒng)計(jì)學(xué)生期中考試成績(jī)

用Excel函數(shù)快速統(tǒng)計(jì)學(xué)生期中考試成績(jī)期中考試結(jié)束，各學(xué)科考試成績(jī)迅速匯總到班主任這里。這時(shí)候，班主任最忙的，就是要迅速統(tǒng)計(jì)各學(xué)科成績(jī)的各項(xiàng)指標(biāo)，比如平均分、最高分、優(yōu)秀率、及格率以及各學(xué)科分?jǐn)?shù)的頻率統(tǒng)計(jì)等等。雖然現(xiàn)在普遍都在使用Excel進(jìn)行這項(xiàng)工作，不過(guò)，要想使這項(xiàng)工作能夠高效準(zhǔn)確地完成，那還得請(qǐng)Excel函數(shù)來(lái)幫忙才行。匯總到班主任這里的成績(jī)表如圖1所示，各科成績(jī)分布在C2:C95單元格區(qū)域。我們先在K2:Q15單元格區(qū)域建立如圖2所示表格用以存放各項(xiàng)統(tǒng)計(jì)結(jié)果。浮修 -BU至叁莖包噌%，5蝴」等N17——而&姓名C語(yǔ)文英語(yǔ)F理論實(shí)踐.H.，總分I名次2叔皿93_5前r433.5r13碰張，的；9762735艮5*359.5■19145班干第一S5阻5416.5F藝155班張.75弟給558r353.5，罵6E班王J%6753他.54.5r340.KL3175班甲76乳9279,5」千務(wù)及」S班劉一679345巧1399，y<siy,電的：L7-標(biāo)分?jǐn)?shù)段JLN0PQ1也筑計(jì)項(xiàng)目語(yǔ)文數(shù)學(xué)英語(yǔ)‘理設(shè)實(shí)踐3平均分4最高分5優(yōu)秀率及格率7一發(fā)幽…81OC1100 -9：99.990-991009.9S0-S91179.970-791269.960-691359.950-591449.940-49一天朋15沮q其以下先點(diǎn)擊M3單元格，輸入如下公式：二AVERAGE(C2:C95),回車(chē)后即可得到語(yǔ)文平均分。點(diǎn)擊M4單元格，輸入公式：=MAX(C$2:C$95),回車(chē)即可得到語(yǔ)文成績(jī)中的最高分。優(yōu)秀率是計(jì)算分?jǐn)?shù)高于或等于85分的學(xué)生的比率。點(diǎn)擊M5單元格，輸入公式：=COUNTIF(C$2:C$95,">=85")/COUNT(C$2:C$95)，回車(chē)所得即為語(yǔ)文學(xué)科的優(yōu)秀率。點(diǎn)擊M6單元格，輸入公式：=COUNTIF(C$2:C$95,">=60")/COUNT(C$2:C$95)，回車(chē)所得即為及格率。選中M3:M6單元格，拖動(dòng)填充句柄向右填充公式至Q6單元格，松開(kāi)鼠標(biāo)，各學(xué)科的統(tǒng)計(jì)數(shù)據(jù)就出來(lái)了。再選中M5:Q6單元格區(qū)域，點(diǎn)擊菜單命令”格式一單元格”，打開(kāi)“單元格格式”對(duì)話框。點(diǎn)擊“數(shù)字”選項(xiàng)卡，在左側(cè)“分類(lèi)”列表中選擇“百分比”，如圖3所示，確定后可將M5:Q6單元格區(qū)域的數(shù)據(jù)轉(zhuǎn)變成百分比形式。TirxTirx廠數(shù)字…!對(duì)齊等陣.：邊除西篥保護(hù)常數(shù)貨會(huì)日時(shí)粵科文持自用專(zhuān)

題值而計(jì)期間C.常數(shù)貨會(huì)日時(shí)粵科文持自用專(zhuān)

題值而計(jì)期間C.口口弘小數(shù)位數(shù)?:??義

學(xué)本殊定隊(duì)百分?jǐn)?shù)點(diǎn)式顯示單元格的值-編走至于各科分?jǐn)?shù)段人數(shù)的統(tǒng)計(jì)，那得先選中M8:M15單元格，在編輯欄中輸入公式：二FREQUENCY(C$2:C$95,$K$8:$K$15)。然后按下“Ctrl+Shift+Enter”快捷鍵，可以看到在公式的最外層加上了一對(duì)大括號(hào)?，F(xiàn)在，我們就已經(jīng)得到了語(yǔ)文學(xué)科各分?jǐn)?shù)段人數(shù)了。在K列中的那些數(shù)字，就是我們統(tǒng)計(jì)各分?jǐn)?shù)段時(shí)的分?jǐn)?shù)分界點(diǎn)?，F(xiàn)在再選中M8:M15單元格，拖動(dòng)其填充句柄向右至Q歹U，那么，其它學(xué)科的分?jǐn)?shù)段人數(shù)也立即顯示在我們眼前了。最終的結(jié)果如圖4所示。如果覺(jué)得K列的數(shù)據(jù)有礙觀瞻，那么可以選中它們，然后設(shè)置它們的字體顏色為白色就可以了。宋偉— 畫(huà)“ ?|李［g聶宓1婚%，Q8 〉播｛二FREQUENCYK$8：$K$151KLMN0PQ12筑計(jì)項(xiàng)目語(yǔ)文數(shù)學(xué)英語(yǔ)理詒實(shí)線落,平均分6屯.屯］63.1759./3B68.0053.034最高分"77r100r匹「r94rB65優(yōu)秀率r0.0。%rq.68%.捻"ll.S3%1.0頻及裕率r-7B.孔弘SO.-22%r53.r7S.12%"30.71%7分?jǐn)?shù)段E100100,’0 ,r1p笠“或；—心用9g.9，90-990百,：■4:近？10.?四9.80-89.0 .7.?茂22,3：',1179,總TO-T9-25''22-23-14悴明.960-69-46.2Q-243：:英：1913：''節(jié)9.9.50-59，1：8ir20141日14-49/9'4:0-49'14知L5§僅：9-4。以下:G:國(guó)在學(xué)校的教學(xué)過(guò)程中，對(duì)學(xué)生成績(jī)的處理是必不可少的，為了在教學(xué)中提高成績(jī)，我們需要對(duì)學(xué)生的考試成績(jī)進(jìn)行認(rèn)真的分析，這就要求我們算出與之相關(guān)的一些數(shù)值：像每一個(gè)同學(xué)的總分及班名次、級(jí)名次，各科分?jǐn)?shù)的平均分，各科的優(yōu)秀率及及格率等等，如果用Excel來(lái)處理這些數(shù)據(jù)則非常簡(jiǎn)單，下面就我在實(shí)際工作中的一點(diǎn)兒經(jīng)驗(yàn)，簡(jiǎn)單談一談?dòng)肊xcel處理學(xué)生成績(jī)。一、排列名次要用到RANK函數(shù)，它是Excel中計(jì)算序數(shù)的主要工具，它的語(yǔ)法為：RANK(Number,Ref,Order)，其中Number為參與計(jì)算的數(shù)字或含有數(shù)字的單元格，Ref是對(duì)參與計(jì)算的數(shù)字單元格區(qū)域的絕對(duì)引用，Order是用來(lái)說(shuō)明排序方式的數(shù)字(如果Order為零或省略，則以降序方式給出結(jié)果，反之按升序方式)。例如：在E2:E50單元格區(qū)域中存放著某一個(gè)班的總分，那么計(jì)算總分名次的方法是：在F2單元格中輸入“=區(qū)人如@2,$￡$2:$￡$50)”按回車(chē)鍵可算出E2單元格內(nèi)總分在班內(nèi)的名次，我們?cè)龠x定F2單元格，把鼠標(biāo)指針移動(dòng)到填充柄上按下鼠標(biāo)左鍵向下拖動(dòng)鼠標(biāo)即可算出其他總分在班內(nèi)的名次。在計(jì)算的過(guò)程中我們需要注意兩點(diǎn)：首先當(dāng)RANK函數(shù)中的Number不是一個(gè)數(shù)時(shí)，其返回值為"#VALUE!”，影響美觀。另外，Excel有時(shí)將空白單元格當(dāng)成是數(shù)值“0”處理，造成所有成績(jī)空缺者都是最后一名，看上去也很不舒服。此時(shí)，可將上面的公式“=RANK(E2,$E$2:$E$50)”改為“二IF(ISNUMBER(E2),RANK(E2,$e$2:$E$50),〃〃)”。其含義是先判斷E2單元格里面有沒(méi)有數(shù)值，如果有則計(jì)算名次，沒(méi)有則空白。其次當(dāng)使用RANK函數(shù)計(jì)算名次時(shí)，相同分?jǐn)?shù)算出的名次也相同，這會(huì)造成后續(xù)名次的空缺，但這并不影響我們的工作。同樣的道理，我們也可以算出一個(gè)學(xué)生的總分在年級(jí)內(nèi)的名次以及各科的班名次和年級(jí)名次，但是必須注意參與計(jì)算的數(shù)字單元格區(qū)域不一樣。二、求各種分?jǐn)?shù)求總分：主要用SUM函數(shù)，其語(yǔ)法格式為SUM(Ref)，此處Ref為參與計(jì)算的單元格區(qū)域。例如：SUM(B2:E2)是表示求B2、C2、D2、E2四個(gè)單元格內(nèi)數(shù)字的和。另外還用到SUMIF函數(shù)，語(yǔ)法格式為SUMIF(Range,Criteria,Sum_range),其功能是根據(jù)指定條件對(duì)若干單元格求和，參數(shù)Range表示引用，用于條件判斷的單元格區(qū)域。Criteria表示數(shù)字、表達(dá)式或文本，指出哪些單元格符合被相加求和的條件。Sum_range表示引用，需要求和的實(shí)際單元格。注意：Criteria如果是文本，那么引號(hào)應(yīng)該是半角的，而不是全角的，否則會(huì)出錯(cuò)！ 求平均分：用AVERAGE函數(shù)，其語(yǔ)法格式為AVERAGE(Ref)，此處Ref為參與計(jì)算的單元格區(qū)域。例如AVERAGE(F2:F50)是求F2:F50區(qū)域內(nèi)數(shù)字的平均值。默認(rèn)情況下，Excel2002會(huì)忽略掉空白的單元格，但是它不忽略數(shù)值為0的單元格，要想忽略數(shù)值為0的單元格需要用到COUNTIF函數(shù)，其語(yǔ)法為COUNTIF(Range,Criteria)，其含義是計(jì)算某個(gè)區(qū)域中滿足給定條件的單元格數(shù)目。本例求F2:F50的平均分，如果忽略數(shù)值為0的單元格可以這樣計(jì)算：SUM(F2:F50)/COUNTIF(F2:F50,“〈〉0〃)。另外如果要求去掉幾個(gè)最高分和幾個(gè)最低分然后取平均分的話，用到LARGE和SMALL函數(shù)，其語(yǔ)法格式為L(zhǎng)ARGE(array,k)，含義是返回?cái)?shù)組中第k個(gè)最大值，SMALL(array,k)的含義是返回?cái)?shù)組中第k個(gè)最小值。如果我們求F2:F50中去掉兩個(gè)最高分和兩個(gè)最低分之后的平均分可以這樣計(jì)算：“SUM(F2:F50)-LARGE(F2:F50,1)-LARGE(F2:F50,2)-SMALL(F2:F50,1)-SMALL(F2:F50,2))/COUNTIF(F2:F50-4)”。 求最高分、最低分：MAX和MIN函數(shù)，語(yǔ)法格式分別為MAX(Ref)和MIN(Ref)，如上例中求F2:F50的最高分和最低分，應(yīng)該這樣：MAX(F2:F50)和MIN(F2:F50)。三、求及格率、優(yōu)秀率求及格率：及格率即一個(gè)班級(jí)中某一科大于等于60分的比例，例如：B2:B50中是某一個(gè)班的語(yǔ)文成績(jī)，可以這樣求及格率：COUNTIF(B2:B50,“〉=60")/COUNT(B2:B50)。求優(yōu)秀率：例如：B2:B50存放的是初一一班的語(yǔ)文期末考試成績(jī)，B2:B500存放的是初一全年級(jí)語(yǔ)文考試的成績(jī)，如果規(guī)定全年級(jí)20%的學(xué)生為優(yōu)秀，那么初一一班語(yǔ)文的優(yōu)秀率應(yīng)該這樣計(jì)算：“COUNTIF(B2:B50,〃〉二〃&&LARGE(B2:B500,INT(0.2COUNT(B2:B500))))/COUNT(B2:B50)”，其中LARGE(B2:B500,INT(0.2COUNT(B2:B500)))所求的是全年級(jí)語(yǔ)文分?jǐn)?shù)前20%中最低的一個(gè)同學(xué)的分?jǐn)?shù)，COUNTIF(B2:B50,〃〉二〃&&LARGE(B2:B500,INT(0.2*COUNT(B2:B500))))則是求出了初——班語(yǔ)文高于或等于這個(gè)同學(xué)分?jǐn)?shù)的人數(shù)，最后再除以初一一班的總?cè)藬?shù)COUNT(B2:B50)，所得就是初一一班的語(yǔ)文優(yōu)秀率，如果想求其他班其他科目的優(yōu)秀率，道理都是一樣。.考試混合編，成績(jī)統(tǒng)一理一老方法遇到新問(wèn)題關(guān)于使用Excel進(jìn)行學(xué)生成績(jī)處理，已經(jīng)是老話題了。但在實(shí)際工作中還是會(huì)有很多新問(wèn)題，例如，現(xiàn)在很多學(xué)校都是全年級(jí)各班混在一起考試，以防考試改卷中的不正當(dāng)競(jìng)爭(zhēng)。而統(tǒng)計(jì)成績(jī)時(shí)，則是將已判分但未拆封的考卷統(tǒng)一交到教務(wù)處，先按座位號(hào)順序(每本考卷的自然順序)錄入各科分?jǐn)?shù)，再分析統(tǒng)計(jì)出全年級(jí)各科成績(jī)。舉例說(shuō)明，如圖1(記錄H至830隱藏了)，要統(tǒng)計(jì)二⑴班優(yōu)秀人數(shù)，傳統(tǒng)做法就是先按考試號(hào)排序，再通過(guò)公式“二COUNTIF(分?jǐn)?shù)!D2:D69,〃>=96〃)”求出。它的弊端是要手工逐個(gè)修改“D2:D69”這個(gè)參數(shù)中的兩個(gè)行號(hào)(2和69),這可是一項(xiàng)工作量很大的工作。當(dāng)然，簡(jiǎn)單的方法還是有的，往下看吧。

黨.用：LEFT（B：r3）|AD.CDE鋁IJKLJ.N0r0Eb1黃?黨.用：LEFT（B：r3）|AD.CDE鋁IJKLJ.N0r0Eb1黃?看號(hào)固憤號(hào)源文1娘旋M3肖工年W徽出兒卬李丹隼K隨「書(shū)i肉1說(shuō)不 ，IT7J.5M>jBb4ijFW周師-1；原到EHWIEG的幽砌：T|i幻I：14工112叫S蘋(píng)1?網(wǎng)融基事為2足碘期科1179-5h*I口E0輻加Iid祖才」.郭壯.必與eS勸」機(jī)*61中M霜5p；3?飛浮33?ie*]lim；於H3工3底九父;I匚I ：尸-：?1 3D1.叩,m IS * 弋3 I獨(dú)加 2H..1G I：己 I2b 二E. 94-^ 士蒞 」： JK害1JH幽"上飛踴.廬.謔二反禮[CT 'JJB.5? J \7J ： I .,12J8 at 3雞 43 飛國(guó)□?? ? -2L2U J]0. J^-3 iL ,■■■■：力g G- =1；12xl53收‘砧w常"ai2C]見(jiàn) 31 /3 玄￡ M 演 Jl_I：f. ■：■., JI2| 5 -l&? ”占 11 211?2i lii. Eli-s * %室 tit 證 3l*圖1原始成績(jī)表.初步準(zhǔn)備一考試號(hào)里提班級(jí)如圖1,從B列的考試號(hào)中取出前三位（班級(jí)編號(hào)）放在S列，即在單元格S2輸入公式"二LEFT（B2,3）”，然后雙擊（或拖動(dòng)）S2單元格右下角的填充柄即可。.再做輔表一班級(jí)等級(jí)二合一在圖1所示的工作簿中再新建一工作表，并將其命名為“等級(jí)”，在單元格A1中輸入公式“二分?jǐn)?shù)!A1”，回車(chē)，選定人1,按住人1右下角的填充柄向右下拖至C840單元格，將“分?jǐn)?shù)”工作表中的姓名、考號(hào)、座位號(hào)引用到“等級(jí)”工作表中（注意，千萬(wàn)不能復(fù)制粘貼過(guò)來(lái)，這樣不能保持兩表數(shù)據(jù)的一致性）。再選定C1,按住。右下角的填充柄向右拖至L1單元格，將語(yǔ)文、數(shù)學(xué)等9個(gè)學(xué)科科目引用過(guò)來(lái)。接著，在D2單元格中輸入IF嵌套公式“=IF（分?jǐn)?shù)!D2>=96,分?jǐn)?shù)!$S2&&〃a〃，IF（分?jǐn)?shù)!D2>=72,分?jǐn)?shù)!$S2&&〃b〃，IF（分?jǐn)?shù)!D2<48,分?jǐn)?shù)!$S2&&〃d〃,分?jǐn)?shù)！$S2&&〃c〃）））”°D2單元格中公式的含義是：看“分?jǐn)?shù)!D2”單元格中的分?jǐn)?shù)（即“分?jǐn)?shù)”工作表中李?lèi)偟恼Z(yǔ)文分?jǐn)?shù)）是否大于等于96。如果是，則在D2單元格中填入“203”一一“分?jǐn)?shù)”工作表中S2單元格中的字符“201”加上“a”（“201”表示二⑴班，“a”表示成績(jī)等級(jí)為“優(yōu)秀”）；如果不是（即小于96）,再看是否大于等于72。如果是，則在D2單元格中填入“20止”；如果不是（即小于72）,再看是否小于48。如果是，則在D2單元格中填入“20^”；如果不是（即小于72大于48）,則在D2單元格中填入“201?！?。最后按住02單元格右下角的填充柄向右下拖至L840單元格，就可以將每個(gè)學(xué)生各科成績(jī)的等級(jí)及所屬班級(jí)都填好了.最終統(tǒng)計(jì)一所需數(shù)據(jù)瞬間齊輔表制好之后，言歸正傳回到“統(tǒng)計(jì)”工作表（如圖2）中，在A17到E28單元格區(qū)域中利用自動(dòng)填充功能再制作一小塊輔助數(shù)據(jù)（如圖2）。圖2“統(tǒng)計(jì)”工作表萬(wàn)事俱備，下面開(kāi)始班級(jí)總?cè)藬?shù)及優(yōu)秀率、及格率等的統(tǒng)計(jì)了。仍以二：1）班優(yōu)秀率為例，