運(yùn)用Excel進(jìn)行學(xué)生成績的統(tǒng)計(jì)分析

1、q 學(xué)習(xí)，能夠運(yùn)用Excel進(jìn)行學(xué)生成績的統(tǒng)計(jì)分析。q 統(tǒng)計(jì)各分?jǐn)?shù)段的人數(shù)及比例q 圖示成績分布（餅圖繪制）q 學(xué)生總成績及名次q 成績等級及積點(diǎn)計(jì)算q 試卷質(zhì)量評價(jià)q 示例1：用IF函數(shù)計(jì)算成績等級q =90A 80-89B 70-79C 60-69D =85,A,IF(C2=75,B,IF(C2=60,C,D),IF(ISBLANK(C2),缺考,輸入有誤)q ISBLANK值為空白單元格q ISNUMBER值為數(shù)字q ISTEXT值為文本q INDEX函數(shù)q INDEX(reference,row_num,column_num,area_num) q 返回引用中指定單元格或單元格區(qū)域的引

2、用q Reference為對一個(gè)或多個(gè)單元格區(qū)域的引用q Row_num為引用中某行的行序號(hào)，函數(shù)從該行返回一個(gè)引用q Column_num為引用中某列的列序號(hào)，函數(shù)從該列返回一個(gè)引用q MATCH( )q 返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置q MATCH(lookup_value，lookup_array，match_type)q Lookup_value為需要在數(shù)據(jù)表中查找的數(shù)值，它可以是數(shù)值(或數(shù)字、文本或邏輯值)、對數(shù)字、文本或邏輯值的單元格引用。q Lookup_array是可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域q Match_type 為數(shù)字-1、0或1 ，它說明

3、Excel 如何在lookup_array 中查找lookup_value。q 如果match_type 為1，函數(shù)MATCH 查找小于或等于lookup_value 的最大數(shù)值q 如果match_type 為0，函數(shù)MATCH 查找等于lookup_value 的第一個(gè)數(shù)值。q 如果match_type 為-1，函數(shù)MATCH 查找大于或等于lookup_value 的最小數(shù)值。 q 示例7：用INDEX和MATCH函數(shù)計(jì)算成績等級q =INDEX(H$2:H$11,MATCH(1,(C2=F$2:F$11)*(C2104)/COUNTA(C$2:C$46)，利用十字柄往右自動(dòng)填充，把大于某

4、值的分?jǐn)?shù)改一下，因?yàn)榭偡植灰粯?，上面的例子是總?30分的，假設(shè)總分100分的，把104改成80即可，這是優(yōu)秀率。同理在單元格N5中輸入=COUNTIF(C$2:C$46,78)/COUNTA($C$2:$C$46)即可算出合格率。在格式/單元格格式/數(shù)值分類中，選擇“百分比”，即出“%“符號(hào)。統(tǒng)計(jì)前三名在單元格N6中輸入=LARGE(C$2:C$46,1)，或=MAX(C$2:C$46,1)往右自動(dòng)填充得出各科成績最高分=LARGE(C$2:C$46,2)可得各科第二名成績=LARGE(C$2:C$46,3)可得各科第三名成績統(tǒng)計(jì)后三名在單元格N6中輸入=SMALL(C$2:C$46,1)，

5、或=MIN(C$2:C$46,1)往右自動(dòng)填充得出各科成績最低分=SMALL(C$2:C$46,2)可得各科倒數(shù)第二名成績=SMALL(C$2:C$46,3)可得各科倒數(shù)第三名成績第四、利用if函數(shù)，對科目成績進(jìn)行等級劃分，在單元格K2中輸入=IF(D2=78,B,C),A)使用if函數(shù)進(jìn)行判斷，數(shù)學(xué)成績達(dá)到優(yōu)秀的，等級劃分為“A”，在合格與優(yōu)秀之間的，等級為“B”，不合格的，等級為“C”。二、利用excel統(tǒng)計(jì)函數(shù)進(jìn)行數(shù)據(jù)分析對于一組數(shù)據(jù)，只獲得一些常規(guī)的總分、排名，優(yōu)秀率和合格率還遠(yuǎn)遠(yuǎn)不夠，對于數(shù)據(jù)之間的分布規(guī)律還沒表達(dá)出來。這時(shí)可以使用Excel提供的統(tǒng)計(jì)函數(shù)來實(shí)現(xiàn)。例如AVERAGE(

6、平均值)、STDEV(樣本標(biāo)準(zhǔn)差)、VAR(樣本方差)、KURT(峰度系數(shù))、SKEW(偏度系數(shù))、MEDIAN(中位數(shù)，即在一組數(shù)據(jù)中居于中間的數(shù))、MODE(眾數(shù)，即在一組數(shù)據(jù)中出現(xiàn)頻率最高的數(shù)值)等。第一、計(jì)算樣本方差=VAR(C2:C46)利用十字柄往右自動(dòng)填充即可完成各個(gè)科目的樣本方差計(jì)算。第二、計(jì)算標(biāo)準(zhǔn)差=STDEV(C2:C46)利用十字柄往右自動(dòng)填充即可完成各個(gè)科目的標(biāo)準(zhǔn)差計(jì)算。樣本中各數(shù)據(jù)與樣本平均數(shù)的差的平方的平均數(shù)叫做樣本方差, 樣本方差的算術(shù)平方根叫做樣本標(biāo)準(zhǔn)差。樣本方差和樣本標(biāo)準(zhǔn)差都是衡量一個(gè)樣本波動(dòng)大小的量，樣本方差或樣本標(biāo)準(zhǔn)差越大，樣本數(shù)據(jù)的波動(dòng)就越大。結(jié)果分析：

7、我們看到數(shù)學(xué)和英語的樣本方差和標(biāo)準(zhǔn)差較大，數(shù)學(xué)科目的標(biāo)準(zhǔn)差為26.84，英語的標(biāo)準(zhǔn)差更是達(dá)到了33.137，說明對于數(shù)學(xué)和英語，學(xué)生的掌握程度參差不齊，差別較大。第三、計(jì)算峰度系數(shù)=KURT(C2:C46)利用十字柄往右自動(dòng)填充即可完成各個(gè)科目的峰度系數(shù)計(jì)算。峰度系數(shù)（Kurtosis）用來度量數(shù)據(jù)在中心聚集程度。在正態(tài)分布情況下，峰度系數(shù)值是0。正的峰度系數(shù)說明觀察量更集中，有比正態(tài)分布更長的尾部；負(fù)的峰度系數(shù)說明觀測量不那么集中，有比正態(tài)分布更短的尾部，類似于矩形的均勻分布。結(jié)果分析：從上述表中數(shù)據(jù)可以看書語文和作文科目數(shù)值較大，8.5291和22.25，說明大部分學(xué)生掌握較好，成績均比平

8、均分高。第四、計(jì)算偏度系數(shù)=SKEW(C2:C46)利用十字柄往右自動(dòng)填充即可完成各個(gè)科目的峰度系數(shù)計(jì)算。偏度系數(shù)（Skewness）用來度量分布是否對稱。正態(tài)分布左右是對稱的，偏度系數(shù)為0。較大的正值表明該分布具有右側(cè)較長尾部。較大的負(fù)值表明有左側(cè)較長尾部。結(jié)果分析：語文和作文科目呈現(xiàn)為較小的負(fù)數(shù)，說明較多分布在左側(cè)。三、利用excel柱形圖直觀分析第一、首先得先找個(gè)能統(tǒng)計(jì)某區(qū)域內(nèi)數(shù)據(jù)的頻率分布函數(shù)，這里我們用FREQUENCY(data_array,bins_array)函數(shù)，data_array表示用來計(jì)算頻率的區(qū)域，bins_array表示為前面數(shù)組進(jìn)行分割一列數(shù)值。各個(gè)科目數(shù)據(jù)分?jǐn)?shù)段

9、分隔點(diǎn)為：max值, (max值+均值)/2，均值, (min值+均值)/2，min值在單元格R33中輸入=FREQUENCY($C$2:$C$46,R26:V26)，按下Ctrl+Shift+Enter組合鍵確認(rèn)，往右自動(dòng)填充，即完成語文科目各個(gè)分?jǐn)?shù)段的人數(shù)統(tǒng)計(jì)。第二、插入圖表選擇圖表向?qū)е械闹螆D，數(shù)據(jù)區(qū)域選擇為上述統(tǒng)計(jì)好的人數(shù)，即“分布1”區(qū)域，完成柱形圖。此方法可以較為直觀地提供各個(gè)分?jǐn)?shù)段人數(shù)的分布情況，這與上述峰度系數(shù)和偏度系數(shù)的計(jì)算是相輔相成的。各個(gè)科目的柱形圖如下： 語文科目的數(shù)據(jù)分布圖 數(shù)學(xué)科目的數(shù)據(jù)分布圖 英語科目的數(shù)據(jù)分布圖 政治科目的數(shù)據(jù)分布圖 歷史科目的數(shù)據(jù)分布圖 作文

10、科目的數(shù)據(jù)分布圖四、分析與評價(jià)統(tǒng)計(jì)規(guī)律表明，學(xué)生的智力水平，包括學(xué)習(xí)能力，實(shí)際動(dòng)手能力等呈正態(tài)分布。因而正常的考試成績分布應(yīng)基本服從正態(tài)分布，柱形圖中形狀即為“中間高，兩邊低”。從上述幾個(gè)數(shù)據(jù)和柱形圖分析來看，對于語文和作文，峰度系數(shù)為較大的正數(shù)，柱形圖上絕大部分分布均在左側(cè)，學(xué)生掌握得較好；政治和歷史在分布上也較為積極；數(shù)學(xué)和英語除了標(biāo)準(zhǔn)差較大，柱形圖上分布也不均勻，學(xué)生掌握程度相差太大。個(gè)性化時(shí)代使得學(xué)校不再是用克隆的方法批量生產(chǎn)標(biāo)準(zhǔn)件的地方，必須看到學(xué)生之間的差異是客觀存在的，只有根據(jù)學(xué)生的實(shí)際情況，因材施教，才能切實(shí)提高學(xué)校教學(xué)質(zhì)量。第一：對語文、作文、政治和歷史科目，開展“提優(yōu)補(bǔ)差”

11、活動(dòng)。利用前面各科成績等級的“ABC”分類，可以直觀的看出學(xué)生各科的平衡，根據(jù)“水桶理論”針對性重點(diǎn)是輔導(dǎo)，和家長溝通也很直接。對“C類”學(xué)生采取正面引導(dǎo)和心理溝通，多表揚(yáng)、多鼓勵(lì)，幫助他們盡早樹立自信心，同時(shí)對“B類”學(xué)生要時(shí)時(shí)加以鞭策，居安思危。第二：對數(shù)學(xué)、英語科目，可根據(jù)“ABC”等級開展“走班教育”。所謂“走班教學(xué)”就是根據(jù)大綱和教材要求，針對不同類型的學(xué)生，設(shè)計(jì)不同層次的教學(xué)目標(biāo)，提出不同層次的學(xué)習(xí)要求，給予不同層次的教學(xué)與輔導(dǎo)，進(jìn)行不同層次的檢測，以達(dá)到他們各自的最佳狀態(tài)。對不同的層次，作為教師，對哪些知識(shí)點(diǎn)必須詳盡講解，讓學(xué)生盡快掌握，必須做到心中有數(shù)。結(jié)語：excel作為一個(gè)很好的工具，讓我們更容易地對數(shù)字進(jìn)行分析，在數(shù)字中發(fā)現(xiàn)問題，從而有的放矢的探究教學(xué)中。班主任老師不能僅僅關(guān)注總分、排名和平均分，也不能根據(jù)單一的分?jǐn)?shù)評