處理學(xué)生成績時所分析的項目包括參考班級，參考人數(shù)、平均分，及格人數(shù)、及格率、優(yōu)秀人數(shù)、優(yōu)秀率，最高分、最低分以及各分數(shù)段所占的人數(shù)：600分以上人數(shù)、500～599 分人數(shù)、400～499 人數(shù)、300～399 人數(shù)、300 分以下人數(shù)。這個統(tǒng)計比較復(fù)雜，且工作量大，如果用人工計算，不但效率低，且出錯機率大，而用EXCEL函數(shù)可以很好解決這個問題。

　　新建空白Excel工作簿，將自動建立的Sheet1、Sheet2、Sheet3工作表重命名為成績表、分析表、分數(shù)段

統(tǒng)計表。其中各科考試成績表用于輸入各科成績。

　　(一)設(shè)計各科成績表

　　在各科考試成績表第一行中輸入分析名稱，如“** 中學(xué)2007～2008 學(xué)年度第一學(xué)期高一月考成績表”，在第二行從A1單元格開始順序輸入記錄字段名：考號、姓名、班級、語文、數(shù)學(xué)、英語、物理、化學(xué)等。

假設(shè)全級學(xué)生有674 人，10個班,所以從第3行到第676 行用來輸入所有學(xué)生各科的成績。

1.    求總分：主要用SUM函數(shù)，其語法格式為SUM(Ref)，此處Ref為參與計算的單元格區(qū)域。例如：SUM(B2:E2)是表示求B2、C2、

D2、E2四個單元格內(nèi)數(shù)字的和。另外還用到SUMIF函數(shù)，語法格式為SUMIF(Range,Criteria,Sum_range)，其功能是根據(jù)指定條件對若干單元格求和，參數(shù)Range表示引用，用于條件判斷的單元格區(qū)域。Criteria表示數(shù)字、表達式或文本，指出哪些單元格符合被相加求和的條件。Sum_range表示引用，需要求和的實際單元格。注意：Criteria如果是文本，那么引號應(yīng)該是半角的，而不是全角的，否則會出錯！

在O3單元格中輸入=sum(F3:N3),便可求出第一個同學(xué)韓威的總分,然后選中O3單元格,當(dāng)右下角出現(xiàn)小”+”字圖標(biāo)時時使用拖拉大法往下自動填充,或左鍵雙擊即可.

2．排名次：Microsoft Excel中用RANK函數(shù)求名次（排序）,它的語法為:

RANK(Number，Ref，Order)   返回一個數(shù)字在數(shù)字列表中的排位。
        Number 為需要找到排位的數(shù)字。
        Ref 為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用。Ref 中的非數(shù)值型參數(shù)將被忽略。如果使用公式復(fù)制，最好使用絕對地址。如

=RANK(E2,$E$2:$E$50)。注意，其中的標(biāo)點符號用英文半角。
        Order 為一數(shù)字，指明排位的方式。如果order為0（零）或省略，Microsoft Excel 對數(shù)字的排位是基于ref為按照降序排列的列表。如果order不為零，Microsoft Excel對數(shù)字的排位是基于ref為按照升序排列的列表。

在計算的過程中我們需要注意兩點：首先當(dāng)RANK函數(shù)中的Number不是一個數(shù)時，其返回值為“#VALUE!”，影響美觀。另外，Excel有時將空白單元格當(dāng)成是數(shù)值“0”處理，造成所有成績空缺者都是最后一名，看上去也很不舒服。此時，可將上面的公式

“=RANK(E2,$E$2:$E$50)”改為“=IF(ISNUMBER(E2),RANK(E2,$e$2:$E$50),"")”。其含義是先判斷E2單元格里面有沒有數(shù)值，如果有則計算名次，沒有則空白。其次當(dāng)使用RANK函數(shù)計算名次時，相同分數(shù)算出的名次也相同，使用RANK()函數(shù)排名的兩種情況:

(1)同分同名次,但后續(xù)名次空缺
假定E列為成績，F(xiàn)列為名次，F(xiàn)2單元格公式如下：
=RANK(E2,E:E) 或=rank(e2,$E$2:$E$10000)
這種方法，分數(shù)相同時名次相同，隨后的名次將空缺。
例如：兩個人99分，并列第2名，則第3名空缺，接下來是第4名。
(2)同分同名次,但后續(xù)名次不空缺
假定成績在E列,則在F2單元格中輸入公式：
=SUMPRODUCT((INDIRECT("E$2:E$"&MAX(IF(E$2:E$10000<>"",ROW(E$2:E$10000))))>E2)*(1/COUNTIF(INDIRECT("E$2:E$"&MAX(IF

(E$2:E$10000<>"",ROW(E$2:E$10000)))),INDIRECT("E$2:E$"&MAX(IF(E$2:E$10000<>"",ROW(E$2:E$10000)))))))+1
公式以Ctrl+Shift+Enter三鍵結(jié)束。
通常我們在排名次時大多采用的是同分同名次,允許后續(xù)名次空缺.

（1）排總名次：在P3單元格輸入=RANK(O3,$O$3:$O$676)，回車，便得到韓威同學(xué)在全年級的總分名次，然后選中P3單元格,當(dāng)右下

角出現(xiàn)小“+”字圖標(biāo)時時使用拖拉大法往下自動填充,或左鍵雙擊即可.

（2）排班名次：在Q3單元格中輸入=SUMPRODUCT(($C$3:$C$376=$C3)*($O3<$O$3:$O$676))+1,然后按Ctrl+Shift+Enter，選中Q3單元格,當(dāng)右下角出現(xiàn)小“+”字圖標(biāo)時時使用拖拉大法往下自動填充,或左鍵雙擊即可.
 (二)設(shè)計成績分析表

　　下面就以語文科分析表設(shè)計為例進行描述，其他科的分析相似操作。

　　1.設(shè)計分析表 如圖2所示.

　　將A1：R1 合并并居中，輸入“** 中學(xué)2007～2008 學(xué)年度第一學(xué)期高一月考成績分析表”。用同樣的方法將A2：I2 合并并居

中，輸入“科目：語文”其他文字按表格內(nèi)容輸入即可。然后從A5至A14依次輸入1,2,3……10班級名.

　　2.求語文科各班參考人數(shù)

　　我們在這里可采用COUNTIF函數(shù)，COUNTIF 函數(shù)用于計算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目，所以在B5單元格中輸入“=COUNTIF(成績表!$C$3: 成績表!$C$676,A5)”， 即可求出1班的參考人數(shù)。然后選中B5 單元格，拖動B5 單元格右下角的小“+”字圖標(biāo)至B14 單元格(復(fù)制公式)，這樣，就能自動得到其他各班的參考人數(shù)。最后在B15 單元格中輸入“=SUM(B5:B14)”，用于分析全年級參考總?cè)藬?shù)。

　　3.求各班及格人數(shù)

我們在這里可采用數(shù)組公式進行多重標(biāo)準(zhǔn)單元計算。首先在D5 單元格中輸入“=SUM(if((成績表!$C$3: 成績表!$C$676=A5)*( 成績!$F$3: 成績表!$F$676,>=90),1,0))”，按下SHIFT＋CTRL鍵敲回車，Excel會自動在公式兩側(cè)加上大括號,生成數(shù)組公式。然后對D5至D14單元格進行向下自動填充操作。最后在D15 中輸入“=SUM(D5:D14)”，求出全級及格人數(shù)。公式中的1表示當(dāng)條件為真時返回結(jié)果計為1個,即及格1人,否則為0;

　　4.求各班及格率

我們可在E5 單元格中輸入“=IF(B5=0,0,D5/B54)*100”，為避免分母為0的錯誤，用了IF 函數(shù)。公式的作用為：若1班參考人數(shù)不為0，則及格率為及格人數(shù)除以參考人數(shù)后乘以100。然后對E5 至E14 單元格進行向下自動填充操作。然后在E15 中輸入Average(E5:E14),算出全年級的平均合格率。

5.按及格率班級排名次

在F5單元格中輸入 =RANK(E5,$E$5:$E$14),然后往下自動填充.得到各班的及格率名次.

　　6.求各班優(yōu)秀人數(shù)

　 首先在I5單元格中輸入“=SUM(IF((成績表!$C$3: 成績表!$C$676=A5)*( 成績表! $ F $ 3 : 成績表!$F$676,>=100),1,0))”然后對I5至I14單元格進行向下自動填充操作。最后在I15單元格中輸入“=SUM(I5:I14)”，以求得全年級優(yōu)秀人數(shù)。    如果規(guī)定全年級20%以上的學(xué)生為優(yōu)秀人數(shù)，可以這樣算：

=SUM(IF((成績表!$C$3:成績表!$C$676=A5)*(成績表!$F$3:成績表!$F$676>=LARGE(成績表!$F$3:$F$676,INT(0.2*COUNT(成績表!$F$3:$F$676)))),1,0))

其中LARGE(成績表!$F$3:$F$676,INT(0.2*COUNT(成績表!$F$3:$F$676)))所求的是全年級語文分數(shù)前20%中最低的一個同學(xué)的分數(shù)，SUM(IF((成績表!$C$3: 成績表!$C$676=A5)*( 成績表! $ F $ 3 : 成績表!$F$676,”>=”LARGE(成績表!$F$3:$F$676,INT(0.2*COUNT(成績表!$F$3:$F$676))))則是求出1班語文學(xué)科高于或等于這個同學(xué)分數(shù)的人數(shù).　　

8.求各班優(yōu)秀率

跟求合格率類似，首先在J5單元格中輸入“=IF(B5=0,0,I5/B5)*100”，為避免分母為0 的錯誤，用了IF 函數(shù)。公式的作用為：若107班參考人數(shù)不為0，則優(yōu)生率為優(yōu)生人數(shù)除以參考人數(shù)后乘以100。然后對J5 至J14 單元格進行向下自動填充操作。最后在J15中輸入“=average(J5:J14)”算出全年級的平均優(yōu)秀率。

9.求各班平均分

　求平均分用AVERAGE函數(shù)，其語法格式為AVERAGE(Ref)，此處Ref為參與計算的單元格區(qū)域。例如AVERAGE(F2:F50)是求F2:F50區(qū)域內(nèi)數(shù)字的平均值。默認情況下，Excel 2003會忽略掉空白的單元格，但是它不忽略數(shù)值為0的單元格，要想忽略數(shù)值為0的單元格需要用到COUNTIF函數(shù)，其語法為COUNTIF(Range,Criteria)，其含義是計算某個區(qū)域中滿足給定條件的單元格數(shù)目。本例求F2:F50的平均分，如果忽略數(shù)值為0的單元格可以這樣計算：SUM(F2:F50)/COUNTIF(F2:F50,"<>0")。另外如果要求去掉幾個最高分和幾個最低分然后取平均分的話，用到LARGE和SMALL函數(shù)，其語法格式為LARGE(array,k)，含義是返回數(shù)組中第k個最大值，SMALL(array,k)

的含義是返回數(shù)組中第k個最小值。如果我們求F2:F50中去掉兩個最高分和兩個最低分之后的平均分可以這樣計算：“SUM(F2:F50)-LARGE(F2:F50,1)-LARGE(F2:F50,2)-SMALL(F2:F50,1)-SMALL(F2:F50,2))/COUNTIF(F2:F50-4)”。

在N5單元格中輸入 =SUMIF(成績表!$C$3:成績表!$C$676,A5,成績表!$F$3:成績表!$F$676)/B5,然后向下自動填充,便可得到各班的語文平均分,最后在N15單元格中輸入 =AVERAGE(N5:N14),得到全年級語文平均分.

　　10.求各班最高分

求最高分可用MAX函數(shù)，這個函數(shù)的作用是求出參數(shù)中的最大值。所以在R5單元格中輸入“=MAX(IF(成績表!$C$3: 成績表!$C$676=A5,成績表!$F$3:成績表!$F$676))” 按下SHIFT ＋CTRL鍵敲回車，生成數(shù)組公式。即可算出1班語文科中的最高分。然后向下自動填充操作。最后在R15單元格中輸入“=MAX(R5:R14)”，以求得全年級的最高分。

(三)設(shè)計分數(shù)段統(tǒng)計表

　將A1：F1 合并并居中，輸入“** 中學(xué)2007～2008 學(xué)年度第一學(xué)期高一月考分數(shù)段統(tǒng)計表”。其他文字按表格內(nèi)容輸入即可。然后從A3至A12依次輸入1,2,3……10班級名.

　　1.求總分在600分以上的人數(shù)

計算分數(shù)段人數(shù)仍可用數(shù)組的方法，可在C3單元格中輸入 “=SUM(IF(成績表!$C$3: 成績表!$C$676=A3)*(成績表! $ O $ 3 : 成績表!$O$676>=600),1,0)”，按下SHIFT＋ CTRL 鍵敲回車，然后進行向下自動填充操作。然后在C13單元格中輸入“=SUM(C3:C12)”，以求得全年級600 分以上的人數(shù)。

　　2.求500～599 分數(shù)段人數(shù)

　　求各班總分在500～599 分數(shù)段人數(shù)，將用到三個條件進行標(biāo)準(zhǔn)單元計算。首先在D3中輸入=SUM(IF((成績表!$C$3:成績表!$C$676=A3)*(成績表!$O$3: 成績表!$O$676<600)*(成績表!$O$3: 成績表!$O$676>=500),1,0))按下SHIFT＋CTRL鍵敲回車，生成數(shù)組公式。然后進行向下自動填充操作。最后在D13 單元格中輸入“=SUM(D3:D12)”，求出全年級總分在500～599 分數(shù)段人數(shù)。

　　3.求400～499 分數(shù)段人數(shù)

　　求各班總分在400～499分數(shù)段人數(shù)，類似上述方法，只改變條件即可，首先在E3 中輸入=SUM(IF((成績表!$C$3:成績表!$C$676=A3)*(成績表!$O$3: 成績表!$O$676<500)*(成績表!$O$3: 成績表!$O$676>=400),1,0)),按下SHIFT ＋ CTRL 鍵敲回車，生成數(shù)組公式。然后進行向下自動填充操作。最后在E13單元格中輸入“=SUM(E3:E12)”，求出全年級400～499 分數(shù)段人數(shù)。

　　同樣的操作，可以分別分析各班總分各分數(shù)段人數(shù)。

　進行分數(shù)段統(tǒng)計的另外兩種方法:
用Excel怎樣統(tǒng)計出學(xué)生成績各分數(shù)段內(nèi)的人數(shù)分布時,很多文章都推薦使用CountIF函數(shù)，可是每統(tǒng)計一個分數(shù)段都要寫一條函數(shù)，十分麻煩。例如，要統(tǒng)計高一1班的語文成績的分數(shù)段人數(shù)分布情況,可在在T76:T80內(nèi)統(tǒng)計顯示F3:F72內(nèi)小于60分、60至70之間、70至80之間、80至90之間、90至100之間的分數(shù)段內(nèi)人數(shù)分布情況，要輸入以下5條公式：

1. 在C58內(nèi)輸入公式統(tǒng)計少于60分的人數(shù)：=CountIF(F3:F74,"<60")

2. 在C59內(nèi)輸入公式統(tǒng)計90分至100之間的人數(shù)：=CountIF(F3:F74,">=90")

3. 在C60內(nèi)輸入公式統(tǒng)計80至90之間的人數(shù)：=CountIF(F3:F74,">=80")-CountIF(F3:F74,">=90")，

4. 在C61內(nèi)輸入公式統(tǒng)計70到80之間的人數(shù)：=CountIF(F3:F74,">=70")-CountIF(F3:F74,">=80")，

5. 在C62內(nèi)輸入公式統(tǒng)計60到70之間的人數(shù)：=CountIF(F3:F74,">=60")-CountIF(F3:F74,">=70")。

如果要把0至10之間、10至20之間、20至30……90至100、100至110、110至120、120至130、130至140、140至150分之間這么多個分數(shù)段都統(tǒng)計出來，就要寫上十幾條公式了。

其實，Excel已經(jīng)為我們提供了一個進行頻度分析的FreQuency數(shù)組函數(shù)，它能讓我們用一條數(shù)組公式就輕松地統(tǒng)計出各分數(shù)段的人數(shù)分布。例如，我們要統(tǒng)計出總分在O3:O676區(qū)域內(nèi)高一1班至高一10班10個班級，300分以下，300至400，400至500，500至600，600分以上每個分數(shù)段內(nèi)的人數(shù)分布：如圖4

1. 在A4:A8內(nèi)輸入：0、299、399、499、599。

2. 用鼠標(biāo)選擇區(qū)域C4至C9，在編輯欄內(nèi)輸入“=FREQUENCY(IF(成績表!$C$3:$C$676=C2,成績表!$O$3:$O$676,""),$A$4:$A$8)”。

3. 按“Crtl+Shift+Enter”組合鍵產(chǎn)生數(shù)組公式“={ =FREQUENCY(IF(成績表!$C$3:$C$676=C2,成績表!

$O$3:$O$676,""),$A$4:$A$8)}”，這里要注意“{ }”不能手工鍵入，必須按下“Crtl+Shift+Enter”組合鍵由系統(tǒng)自動產(chǎn)生。完成后C4:C9將顯示如圖所示的分數(shù)分布情況。然后向右自動填充，得到其它各個班的分數(shù)段人數(shù)分布情況。用CountIF函數(shù)統(tǒng)計分數(shù)段的方法流傳很廣，但效率并不高,而用FREQUENCY()函數(shù)就非常方便且高效

函數(shù)FREQUENCY 主要功能是以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。

　　其語法格式：FREQUENCY(data_array,bins_array)

　　參數(shù)說明：Data_array表示用來計算頻率的一組數(shù)據(jù)或單元格區(qū)域；Bins_array表示為前面數(shù)組進行分隔一列數(shù)值?！　?
 

4.創(chuàng)建圖表:

按以上方法創(chuàng)建好工作表，設(shè)置好打開權(quán)限密碼和修改密碼，保護好里面的數(shù)據(jù)，這樣就可以快速準(zhǔn)確地對學(xué)生成績進行管理了。既減輕了教師的工作負擔(dān)，又提高了準(zhǔn)確性和工作效率。

(四)創(chuàng)建模板

打開文件菜單,選擇”另存為”,在”保存類型”中選擇模板(*.XLT),文件名為”成績統(tǒng)計分析.XLT”, )”。以后要用的時候，直接雙擊打開就可以了。如果經(jīng)常要用，把模板存放在桌面上比較方便。