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

1、 HYPERLINK 用Excel函函數(shù)快速統(tǒng)計計學生期中考考試成績期中考試結(jié)束，各各學科考試成成績迅速匯總總到班主任這這里。這時候候，班主任最最忙的，就是是要迅速統(tǒng)計計各學科成績績的各項指標標，比如平均均分、最高分分、優(yōu)秀率、及及格率以及各各學科分數(shù)的的頻率統(tǒng)計等等等。雖然現(xiàn)現(xiàn)在普遍都在在使用Excel進行這項工工作，不過，要要想使這項工工作能夠高效效準確地完成成，那還得請請Excel函數(shù)來幫忙忙才行。匯總到班主主任這里的成成績表如圖1所示，各科科成績分布在在C2:C95單元格區(qū)域域。我們先在在K2:Q15單元格區(qū)域域建立如圖2所示表格用用以存放各項項統(tǒng)計結(jié)果。先點擊M33單元格，輸輸入如下

2、公式式：=AVERRAGE(CC2:C955)，回車后即即可得到語文文平均分。點擊M4單單元格，輸入入公式：=MAX(C$2:CC$95)，回車即可可得到語文成成績中的最高高分。優(yōu)秀率是計計算分數(shù)高于于或等于85分的學生的的比率。點擊擊M5單元格，輸輸入公式：=COUNNTIF(CC$2:C$95,=85)/COUNNT(C$22:C$955)，回車所得得即為語文學學科的優(yōu)秀率率。點擊M6單單元格，輸入入公式：=COUNNTIF(CC$2:C$95,=60)/COUNNT(C$22:C$955)，回車所得得即為及格率率。選中M3:M6單元格，拖動動填充句柄向向右填充公式式至Q6單元格，松開開鼠

3、標，各學學科的統(tǒng)計數(shù)數(shù)據(jù)就出來了了。再選中M55:Q6單元格區(qū)域域，點擊菜單單命令“格式單元格”，打開“單元格格式”對話框。點點擊“數(shù)字”選項卡，在在左側(cè)“分類”列表中選擇“百分比”，如圖3所示，確定定后可將M5:Q6單元格區(qū)域域的數(shù)據(jù)轉(zhuǎn)變變成百分比形形式。至于各科分分數(shù)段人數(shù)的的統(tǒng)計，那得得先選中M8:M115單元格，在在編輯欄中輸輸入公式：=FREQQUENCYY(C$2:C$95,$K$8:$K$155)。然后按下“Ctrl+Shiftt+Enteer”快捷鍵，可可以看到在公公式的最外層層加上了一對對大括號?，F(xiàn)現(xiàn)在，我們就就已經(jīng)得到了了語文學科各各分數(shù)段人數(shù)數(shù)了。在K列中的那些些數(shù)字，就

4、是是我們統(tǒng)計各各分數(shù)段時的的分數(shù)分界點點?，F(xiàn)在再選中中M8:M115單元格，拖拖動其填充句句柄向右至Q列，那么，其其它學科的分分數(shù)段人數(shù)也也立即顯示在在我們眼前了了。最終的結(jié)果果如圖4所示。如果果覺得K列的數(shù)據(jù)有有礙觀瞻，那那么可以選中中它們，然后后設置它們的的字體顏色為為白色就可以以了。在學校的教學過過程中，對學學生成績的處處理是必不可可少的，為了了在教學中提提高成績，我我們需要對學學生的考試成成績進行認真真的分析，這這就要求我們們算出與之相相關(guān)的一些數(shù)數(shù)值：像每一一個同學的總總分及班名次次、級名次，各各科分數(shù)的平平均分，各科科的優(yōu)秀率及及及格率等等等，如果用Excel來處理這些些數(shù)據(jù)則非常

5、常簡單，下面面就我在實際際工作中的一一點兒經(jīng)驗，簡簡單談一談用用Excel處理學生成績績。 一、排列名名次 要用到RANK函數(shù)，它是Excel中計算序數(shù)數(shù)的主要工具具，它的語法法為：RANK(Numbeer,Reff,Ordeer)，其中Numbeer為參與計算算的數(shù)字或含含有數(shù)字的單單元格，Ref是對參與計計算的數(shù)字單單元格區(qū)域的的絕對引用，Order是用來說明明排序方式的的數(shù)字(如果Order為零或省略略，則以降序序方式給出結(jié)結(jié)果，反之按按升序方式)。例如：在E2:E550單元格區(qū)域域中存放著某某一個班的總總分，那么計計算總分名次次的方法是：在F2單元格中輸輸入“=RANKK(E2,$E$

6、2:$E$50)”按回車鍵可算算出E2單元格內(nèi)總總分在班內(nèi)的的名次，我們們再選定F2單元格，把把鼠標指針移移 動到填充柄上上按下鼠標左左鍵向下拖動動鼠標即可算算出其他總分分在班內(nèi)的名名次。 在計算的過過程中我們需需要注意兩點點：首先當RANK函數(shù)中的Numbeer不是一個數(shù)數(shù)時，其返回回值為“#VALUUE!”，影響美觀觀。另外，Excel有時將空白白單元格當成成是數(shù)值“0”處理，造成成所有成績空空缺者都是最最后一名，看看上去也很不不舒服。此時時，可將上面面的公式“=RANKK(E2,$E$2:$E$50)”改為“=IF(IISNUMBBER(E22),RANNK(E2,$e$2:$E$500

7、),)”。其含義是是先判斷E2單元格里面面有沒有數(shù)值值，如果有則則計算名次，沒沒有則空白。其其次當使用RANK函數(shù)計算名名次時，相同同分數(shù)算出的的名次也相同同，這會造成成后續(xù)名次的的空缺，但這這并不影響我我們的工作。同同樣的道理，我我們也可以算算出一個學生生的總分在年年級內(nèi)的名次次以及各科的的班名次和年年級名次，但但是必須注意意參與計算的的數(shù)字單元格格區(qū)域不一樣樣。 二、求各種種分數(shù) 求總分：主主要用SUM函數(shù)，其語語法格式為SUM(RRef)，此處Ref為參與計算的單元元格區(qū)域。例例如：SUM(B22:E2)是表示求B2、C2、D2、E2四個單元格格內(nèi)數(shù)字的和和。另外還用到SUUMIF函數(shù)，

8、語法法格式為SUMIFF(Rangge,Criiteriaa,Sum_rangee)，其功能是是根據(jù)指定條條件對若干單單元格求和，參參數(shù)Range表示引用，用用于條件判斷斷的單元格區(qū)區(qū)域。Criteeria表示數(shù)字、表表達式或文本本，指出哪些些單元格符合合被相加求和和的條件。Sum_rrange表示引用，需需要求和的實實際單元格。注注意：Criteeria如果是文本本，那么引號號應該是半角角的，而不是是全角的，否否則會出錯！求平均均分：用AVERAAGE函數(shù)，其語語法格式為AAVERAGGE(Reff)，此處Ref為參與計算算的單元格區(qū)區(qū)域。例如AVERAAGE(F22:F50)是求F2:F5

9、50區(qū)域內(nèi)數(shù)字的的平均值。默默認情況下，Excell 20022會忽略掉空空白的單元格格，但是它不不忽略數(shù)值為為0的單元格，要要想忽略數(shù)值值為0的單元格需需要用到COUNTTIF函數(shù)，其語語法為COUNTTIF(Raange,CCriterria)，其含義是是計算某個區(qū)區(qū)域中滿足給給定條件的單單元格數(shù)目。本本例求F2:F550的平均分，如如果忽略數(shù)值值為0的單元格可可以這樣計算算：SUM(FF2:F500)/COUUNTIF(F2:F550,0)。另外如果果要求去掉幾幾個最高分和和幾個最低分分然后取平均均分的話，用用到LARGE和SMALL函數(shù)，其語語法格式為LARGEE(arraay,k)，

10、含義是返返回數(shù)組中第第k個最大值，SMALLL(arraay,k)的含義是返返回數(shù)組中第第k個最小值。如如果我們求F2:F550中去掉兩個個最高分和兩兩個最低分之之后的平均分分可以這樣計計算：“SUM(FF2:F500)-LARRGE(F22:F50,1)-LAARGE(FF2:F500,2)-SSMALL(F2:F550,1)-SMALLL(F2:FF50,2)/COUUNTIF(F2:F550-4)”。求最最高分、最低低分：MAX和MIN函數(shù)，語法法格式分別為為MAX(RRef)和MIN(RRef)，如上例中中求F2:F550的最高分和和最低分，應應該這樣：MAX(F22:F50)和MIN

11、(FF2:F500)。 三、求及格格率、優(yōu)秀率率求及格格率：及格率率即一個班級級中某一科大大于等于60分的比例，例例如：B2:B550中是某一個個班的語文成成績，可以這這樣求及格率率：COUNTTIF(B22:B50,=60)/COUNNT(B2:B50)。求優(yōu)優(yōu)秀率：例如如：B2:B550存放的是初初一一班的語語文期末考試試成績，B2:B5500存放的是初初一全年級語語文考試的成成績，如果規(guī)規(guī)定全年級20%的學生為優(yōu)優(yōu)秀，那么初初一一班語文文的優(yōu)秀率應應該這樣計算算：“COUNTTIF(B22:B50,=&LLARGE(B2:B5500,INNT(0.22 COUNNT(B2:B500)/C

12、COUNT(B2:B550)”，其中LARGEE(B2:BB500,IINT(0.2 COUUNT(B22:B5000)所求的是全全年級語文分分數(shù)前20%中最低的一一個同學的分分數(shù)，COUNTTIF(B22:B50,=&LLARGE(B2:B5500,INNT(0.22*COUNNT(B2:B500)則是求出了了初一一班語語文高于或等等于這個同學學分數(shù)的人數(shù)數(shù)，最后再除除以初一一班班的總?cè)藬?shù)COUNTT(B2:BB50)，所得就是是初一一班的的語文優(yōu)秀率率，如果想求求其他班其他他科目的優(yōu)秀秀率，道理都都是一樣。1考試混合編編，成績統(tǒng)一一理-老方法遇到到新問題 關(guān)于使用Excel進行學生成成績處

13、理，已已經(jīng)是老話題題了。但在實實際工作中還還是會有很多多新問題，例例如，現(xiàn)在很很多學校都是是全年級各班班混在一起考考試，以防考考試改卷中的的不正當競爭爭。而統(tǒng)計成成績時，則是是將已判分但但未拆封的考考卷統(tǒng)一交到到教務處，先先按座位號順順序(每本考卷的的自然順序)錄入各科分分數(shù)，再分析析統(tǒng)計出全年年級各科成績績。舉例說明明，如圖1(記錄11至830隱藏了)，要統(tǒng)計二(1)班優(yōu)秀人數(shù)數(shù)，傳統(tǒng)做法法就是先按考考試號排序，再再通過公式“=COUNNTIF(分數(shù)!D2:DD69,=96)”求出。它的的弊端是要手手工逐個修改改 “D2:D669”這個參數(shù)中中的兩個行號號(2和69)，這可是一一項工作量很很

14、大的工作。當當然，簡單的的方法還是有有的，往下看看吧。 圖1 原始始成績表 2初初步準備-考試號里提提班級 如圖1，從B列的考試號號中取出前三三位(班級編號)放在S列，即在單單元格S2輸入公式“=LEFTT(B2,33)”，然后雙擊(或拖動)S2單元格右下下角的填充柄柄即可。 3再再做輔表-班級等級二二合一 在圖1所示的工作作簿中再新建建一工作表，并并將其命名為為“等級”，在單元格A1中輸入公式“=分數(shù)!A1”，回車，選選定A1，按住A1右下角的填填充柄向右下下拖至C840單元格，將“分數(shù)”工作表中的的姓名、考號號、座位號引引用到“等級”工作表中(注意，千萬萬不能復制粘粘貼過來，這這樣不能保持

15、持兩表數(shù)據(jù)的的一致性)。再選定C1，按住C1右下角的填充充柄向右拖至至L1單元格，將將語文、數(shù)學學等9個學科科目目引用過來。接接著，在D2單元格中輸輸入IF嵌套公式“=IF(分數(shù)!D2=96,分數(shù)!$S2&a,IF(分數(shù)!D2=72,分數(shù)!$S2&b,IF(分數(shù)!D2448,分數(shù)!$S2&d,分數(shù)!$S2&c)”。D2單元格中公公式的含義是是：看“分數(shù)!D2”單元格中的的分數(shù)(即“分數(shù)”工作表中李李悅的語文分分數(shù))是否大于等等于96。如果是，則則在D2單元格中填填入“201a”“分數(shù)”工作表中S2單元格中的的字符“201”加上“a”(“201”表示二(1)班，“a”表示成績等等級為“優(yōu)秀”)；

16、如果不是(即小于96)，再看是否否大于等于72。如果是，則則在D2單元格中填填入“201b”；如果不是(即小于72)，再看是否否小于48。如果是，則則在D2單元格中填填入“201d”；如果不是(即小于72大于48)，則在D2單元格中填填入“201c”。最后按住D2單元格右下下角的填充柄柄向右下拖至至L840單元格，就就可以將每個個學生各科成成績的等級及及所屬班級都都填好了 4最最終統(tǒng)計-所需數(shù)據(jù)瞬瞬間齊 輔表制好之之后，言歸正正傳回到“統(tǒng)計”工作表(如圖2)中，在A17到E28單元格區(qū)域中中利用自動填填充功能再制制作一小塊輔輔助數(shù)據(jù)(如圖2)。 圖2 “統(tǒng)統(tǒng)計”工作表 萬事俱俱備，下面開開始班

17、級總?cè)巳藬?shù)及優(yōu)秀率率、及格率等等的統(tǒng)計了。仍仍以二(1)班優(yōu)秀率為為例，現(xiàn)在就就改用這樣的的公式了“COUNTTIF(等級!$D:$D,$B117)”，即對“等級”工作表中D列所有單元元格進行統(tǒng)計計(等級!$D:$D)，找出值為“201a”(本工作表即“統(tǒng)計”工作表的$B17的值，代表表二(1)班優(yōu)秀率)的單元格數(shù)數(shù)目。 具體做法如如下： (1)班級總?cè)藬?shù)(在B4單元格中輸輸入)：“=COUNNTIF(分數(shù)!$S:$S,A177)”； (2)優(yōu)秀人數(shù)（在在C4單元格中輸輸入)：“=COUNNTIF(等級!$D:$DD,$B177)”； (3)優(yōu)秀率（在D4單元格中輸輸入)：“=C4/$B4100”； (4)及格人數(shù)（在在E4單元格中輸輸入)：“=COUNTTIF(等級!$D:$D,$B117)+COOUNTIFF(等級!$D:$D,$C117)”； (5)及格率(在F4單元格中輸輸入)：“=E4/$B4100”； (6)低分人數(shù)(在G4單元格中輸輸入)：”=COUNNTIF(等級!