運用Excel進行學生成績的統計分析

1、q 學習，能夠運用Excel進行學生成績的統計分析。q 統計各分數段的人數及比例q 圖示成績分布（餅圖繪制）q 學生總成績及名次q 成績等級及積點計算q 試卷質量評價q 示例1：用IF函數計算成績等級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值為數字q ISTEXT值為文本q INDEX函數q INDEX(reference,row_num,column_num,area_num) q 返回引用中指定單元格或單元格區(qū)域的引

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

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

4、值的分數改一下，因為總分不一樣，上面的例子是總分130分的，假設總分100分的，把104改成80即可，這是優(yōu)秀率。同理在單元格N5中輸入=COUNTIF(C$2:C$46,78)/COUNTA($C$2:$C$46)即可算出合格率。在格式/單元格格式/數值分類中，選擇“百分比”，即出“%“符號。統計前三名在單元格N6中輸入=LARGE(C$2:C$46,1)，或=MAX(C$2:C$46,1)往右自動填充得出各科成績最高分=LARGE(C$2:C$46,2)可得各科第二名成績=LARGE(C$2:C$46,3)可得各科第三名成績統計后三名在單元格N6中輸入=SMALL(C$2:C$46,1)，

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

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

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

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

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

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

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