如何用EXCEL制作成績分析的正態(tài)分布圖解讀

1、如何用EXCEL制作成績分析的正態(tài)分布圖摘要：教學評價在學校教育教學工作中的重要地位毋容置疑?？荚囀菍W生進行的一種教育測量，也是對教師教學質(zhì)量、出題水平的評價。特別是數(shù)理統(tǒng)計方法的應(yīng)用，使得我們對學生的教育測量轉(zhuǎn)化為教學評價得到了有效的幫助。本文論述了如何用EXCEL制作考試成績的正態(tài)分成圖，并結(jié)合其它相關(guān)的衡量標準，比如，區(qū)分度，學生成績柱狀分布圖，難度系數(shù)，優(yōu)秀率等，融合于一個圖表中進行分析。這是一種有效的可操作的方法，能讓每一位教師從圖中獲得一種易于接受的直觀認識，并且方便找出教學中存在的問題，并為以后教學改進措施的制定提供有效的幫助。關(guān)鍵詞：教學評價，EXCEL，成績分析，正態(tài)分布。

2、教育評價學是教育科學領(lǐng)域中的一個重要的應(yīng)用性很強的分支學科。在當今世界教育領(lǐng)域中，教育評價、教育基礎(chǔ)理論和教育發(fā)展被認為是三大研究范圍。教育是人類有目的、有計劃、有組織的活動，教育活動涉及教育方案、教育活動的實施、教育活動的參與者等等，要提高學校教育活動的有效性，就必須對這些內(nèi)容進行適當?shù)脑u價。因此，教育評價對于學校教育的改革和發(fā)展，對于學校教育的管理和決策，都有著至關(guān)重要的作用，所以備受各國政府及其教育行政部門的重視。在學校日常工作中，通過教育評價活動來強化管理，已受到人們的廣泛重視。不論是宏觀的教育行政管理還是微觀的學校工作管理，都把教育評價當作一種有效的管理手段。就一所學校而言，管理水平

3、的高低在一定程度上能反映出該校的評價工作開展得怎么樣，而評價水平的高低又能體現(xiàn)出學校領(lǐng)導者的管理水平。實施素質(zhì)教育的關(guān)鍵是教師素質(zhì)的高低。為了提高教師素質(zhì)，教育行政部門和學校都加大了對教師的管理力度，開展了對教師的教學評價工作。通過有效地評價教師，不僅調(diào)動了教師工作的積極性，而且進一步促進了師資隊伍的建設(shè)。所以，要做一個有效的管理者，就要重視教育評價的作用。教學評價是教育評價的重要組成部分。它以考試作為一種基礎(chǔ)性的手段，來收集有關(guān)學生對知識的掌握程度方面的信息；以測驗作為測量的手段，獲得客觀的數(shù)據(jù)，進行進一步的分析、綜合，并作出價值上的判斷。在學校教育教學工作中，從研究的目的出發(fā)開展評價工作，

4、就是要通過評價活動促進教育教學改革實驗的進行，從而提高教育教學的科學研究水平。因此，教學評價將有助于學校及教育工作者自身進行檢查、反思，并主動改進教育教學工作，從而有助于提高教育教學質(zhì)量。教學常規(guī)工作中的段考、期考，不僅僅是為了測量學生的知識掌握程度，我們還應(yīng)該使用現(xiàn)代的數(shù)理統(tǒng)計技術(shù)和現(xiàn)代信息技術(shù)來對考試成績進行仔細、有效的分析，從中找出需要改進的教學問題，并為今后的教學改革提供依據(jù)。因此，我們就需要使用正態(tài)分布曲線來給我們的成績分析提供一個有效的參考。一、如何用EXCEL制作成績分析的正態(tài)分布圖呢？我們先來看一份樣圖：圖1從圖1中，我們可以看到一些信息，橫坐標為從0分到150分，共計151個

5、刻度，縱坐標為人數(shù)(相當于樣本的頻數(shù))。在這里，縱坐標沒有使用“頻率”而是使用“頻數(shù)”來做單位，這是因為我們不是只抽取一部分“樣本（隨機抽取學生）”來進行分析，而是對整個“總體（整個年級）”來進行分析，因此，我們使用頻數(shù)（具體的人數(shù)）來做縱坐標單位，更加具有實際意義，也更為直觀。圖中的黑色柱狀圖是學生成績的分布，粉紅色的曲線是由學生該科成績的平均分和標準方差所決定的正態(tài)分布曲線，左邊的紅色豎線是低分率的線，中間的黃色豎線是及格率的線，右邊的綠色線是優(yōu)秀率的線。上面還附有該科的平均分、難度系數(shù)、區(qū)分度、低分率、及格率、優(yōu)秀率等數(shù)值。相應(yīng)的一些評價參數(shù)的制作方法如下：1、平均分平均分在EXCEL里

6、使用函數(shù)：AVERAGE(Number1,Number2)其中：Number1，number2，是要計算平均值的 130 個參數(shù)。例如：在新的單元格中插入AVERAGE(D3:D923)，“D3:D923”就是學生成績所在的單元格范圍。2、難度系數(shù) 難度系數(shù)是反映試題的難易程度，即考生在一個試題或一份試卷中的失分程度。如滿分150分的試題，考生平均得分108分，平均失分42分，則難度系數(shù)為42/150=0.28。難度系數(shù)的計算公式為：L=1X/W其中，L為難度系數(shù)，X為樣本平均得分，W為試卷總分（一般為100分或150分）。為了簡化試卷難度系數(shù)的計算，在EXCEL中可以用以下簡單的計算公式進行

7、計算：P=平均分/滿分值題目的難度值越大，則題目的難度越??；題目的難度值越小，則題目的難度越大。一般認為，試題的難度指數(shù)在0.3-0.7之間比較合適。整份試卷的平均難度指數(shù)最好掌握在0.5左右，高于0.7和低于0.3的試題不能太多。3、試題區(qū)分度為了簡單計算， 教師可以使用下面的一種方法進行計算區(qū)分度： 先將分數(shù)排序，P1=27高分組的平均分，P2=27低分組的平均分，區(qū)分度D=（P1P2）/滿分值。 區(qū)分度一般在0+1之間，值越大區(qū)分度越好。試題的區(qū)分度在0.4以上表明此題的區(qū)分度很好，0.30.39表明此題的區(qū)分度較好，0.20.29表明此題的區(qū)分度不太好需修改，0.19以下表明此題的區(qū)分

8、度不好，應(yīng)淘汰。 4、低分率、及格率、優(yōu)秀率我們按照低分線、及格線、優(yōu)秀線分別為總分40%、60%、80%來確定，在圖中分別用紅線、黃線、綠線來注明，并且在圖中上方注明對應(yīng)的低分率、及格率、優(yōu)秀率，這樣看起來比較形像直觀。5、學生成績的柱狀分布圖在EXCEL里使用函數(shù)：COUNTIF (range,criteria)，其中range為單元格范圍，直接左鍵拖擊單元格即可選定，criteria為判定標準，可以是數(shù)值（直接輸數(shù)字即可無需引號，如0,1等）或字符（須用英文雙引號括起，如"YES","A","男","老師"）

9、等，例如：我要計算50分的人數(shù),則插入的函數(shù)為COUNTIF(D3:D923,"=50") ，“D3:D923”是學生成績所在的單元格范圍，“=50”則是條件。另外需要注意的地方是，如果考試成績不是整數(shù)，比如：50.5，在使用“=50”的條件下，就不會計算在內(nèi)，這時就必需使用取整函數(shù)：ROUND(number,num_digits)，按指定的位數(shù)對數(shù)值進行四舍五入。為了避免不必要的麻煩，一旦發(fā)現(xiàn)該科成績存在小數(shù)部分，一般都應(yīng)該是取整在先，統(tǒng)計人數(shù)在后。也就是先用ROUND取整，再用COUNTIF計算人數(shù)。從0分到150分的人數(shù)都統(tǒng)計出來后，就可以用EXCEL的“圖表向?qū)А敝?/p>

10、作出相應(yīng)的柱狀分布圖了，如圖2所示。圖2 學生成績的柱狀分布圖6、正態(tài)曲線制作正態(tài)曲線需要使用函數(shù)：NORMDIST(x,mean,standard_dev,cumulative) X 為需要計算其分布的數(shù)值，Mean 為分布的算術(shù)平均值，standard_dev 為分布的標準偏差，Cumulative 為一邏輯值，指明函數(shù)的形式。如果 cumulative 為 TRUE，函數(shù) NORMDIST 返回累積分布函數(shù)；如果為 FALSE，返回概率密度函數(shù)。例如：如果滿分為150分，那么我們就要從0分到150分一共151個分值進行計算正態(tài)曲線的值，然后才能做出對應(yīng)的曲線。假如我們要計算50分，那么在

11、表達式中：D1080*NORMDIST($C$980,D1078,D1079,FALSE)，“$C$980”對應(yīng)的就是50分所在的單元格，“D1078”則對應(yīng)我們事先計算得出的平均分，“D1079”則對應(yīng)我們事先計算得出的標準方差，“FALSE”則是我們需要的概率密度函數(shù)類型?！癉1080”則對應(yīng)這批分析成績的總?cè)藬?shù)，是用來矯正正態(tài)曲線的重要參數(shù)。只有經(jīng)過矯正后，學生成績的柱狀分布圖才能與正態(tài)曲線結(jié)合起來。因為學生成績的柱狀分布圖的縱坐標是人數(shù)，那么柱狀圖的總面積則為成績的總?cè)藬?shù)。而正態(tài)曲線與橫坐標軸包含的面積則為1，所以只有把正態(tài)曲線的值乘以總?cè)藬?shù)，兩者的面積才能相等，這樣兩者才能完美結(jié)合起

12、來。從“頻率=頻數(shù)/樣本數(shù)”這一關(guān)系來說，正態(tài)曲線圖的縱坐標單位是“頻率”，因此乘以“D1080”（ 樣本數(shù)）才可以得到“頻數(shù)”，這樣兩者的縱坐標單位才一致，才能放置于同一個坐標系內(nèi)。從0分到150分的人數(shù)都統(tǒng)計出來后，就可以用EXCEL的“圖表向?qū)А敝谱鞒鱿鄳?yīng)的正態(tài)曲線分布圖了，如圖3所示。圖3 正態(tài)曲線當我們把“學生成績的柱狀分布圖”與“正態(tài)曲線分布圖”共同制作在一個圖表中，就可以得到如圖1所示的效果圖了。在這樣一幅效果圖中，我們建立了一批數(shù)據(jù)頻數(shù)分布柱狀圖與該批數(shù)據(jù)平均值和標準差所確定的正態(tài)分布曲線的對照關(guān)系。有了參照，我們就可以分析這批學生該科考試成績的質(zhì)量了。另外再附加上平均分、難度

13、系數(shù)、區(qū)分度、低分率、及格率、優(yōu)秀率等數(shù)值以后，就可以開始對成績進行更為詳細和完整的分析了。二、做完各科目的成績正態(tài)分布圖以后，對于以下的一些例圖，我們可以嘗試做一些分析。1、從圖4中，我們可以看出試題的區(qū)分度很好，達到0.56，但是從成績分布來說，不符合正態(tài)分布。針對這種情況我們可以采取一些教學上的改進措施，比如：抓基礎(chǔ)題的練習，學生每天的習題量要夠份量，每天要督促學生獨立完成作業(yè),要注重學生平時學習的過程。圖4 2、從圖5中，我們可以看出，該科的總分（120分）用最右邊的綠色豎線來標識，左邊的綠色豎線則是優(yōu)秀線。因為做學生成績分析是所有科目一起做的，為了減少操作的麻煩，也是為了能進行不同科

14、目的橫向比較，雖然不同科目的成績沒有可比性，但是對于他們的教學評價卻有一定的可比性，為了能讓直觀感覺一致性，對于橫坐標的刻度標準應(yīng)該要一致，所以我們統(tǒng)一用150分來確定橫坐標的最大值。這時候，我們只需要在滿分值的地方用一條綠色的堅線來注明。該科的滿分為120分，考慮0分為第一刻度，那么120分就應(yīng)該在121的刻度位置。試題的區(qū)分度在0.37，但是從成績分布來說，應(yīng)該是屬于正偏態(tài)分布。圖5如果頻數(shù)分布的高峰向左偏移，長尾向右側(cè)延伸稱為正偏態(tài)分布（考試分數(shù)集中在低端），也稱右偏態(tài)分布；同樣的，如果頻數(shù)分布的高峰向右偏移，長尾向左延伸則成為負偏態(tài)分布（考試分數(shù)集中在高端），也稱左偏態(tài)分布。圖6 偏態(tài)

15、分布正偏（右偏）負偏（左偏）從這里我們可以了解到，在編制試題的時候，平時測驗的難度設(shè)定要利于學生的學習，同時還要考慮及格率，防止損傷學困生的自尊心，但一定的難度卻能增加區(qū)分度，這對全面了解、掌握學生學習情況有十分重要的作用。除此之外，難度水平的確定還要考慮對分數(shù)分布的影響，一般以正態(tài)分布為前提，有時正偏態(tài)分布更能激發(fā)學生的學習積極性。3、從圖7中，我們可以看出存在兩個峰值，中間凹，兩邊凸，說明兩極分化嚴重。在教學改進措施中，應(yīng)該加強對薄弱學生的關(guān)注，督促檢查他們的學習，從而盡量減小他們與優(yōu)秀學生的差距，在圖中則盡量體現(xiàn)為兩個峰值合為一個峰值。該科的總分（100分）用最右邊的綠色豎線來標識，右邊

16、的綠色豎線則是優(yōu)秀線。我們可以看到粉紅色的正態(tài)曲線已經(jīng)溢出了滿分線。因為該科的平均分比較高，區(qū)分度為0.41，正態(tài)曲線就會比較矮，由于正態(tài)曲線是兩端對稱性的，所以就會存在一端溢出的現(xiàn)象，這也可以說是用正態(tài)曲線來分析成績的一個局限性。因此，當難度系數(shù)在0.5，區(qū)分度不超過0.5的時候用正態(tài)曲線來分析是最合適的。圖7從以上各類情況，我們可以看到，用EXCEL來制作成績分析的正態(tài)布圖，并且結(jié)合難度系數(shù)、區(qū)分度、成績柱狀分布圖、低分率、及格率、優(yōu)秀率等參數(shù)來對學生的考試成績來進行分析，這是一種有效的可操作的方法。通過對多種衡量參數(shù)融合為一體來進行分析，使得我們對教學質(zhì)量的整體把握更為詳細和有效。如果只

17、是單一的學生成績分布圖，沒有正態(tài)曲線來做參考，那也只能是一副圖而已。就相當于數(shù)量沒有單位，就沒有了衡量價值。因此，通過這種可操作的方法來對學生考試成績進行分析，并作出教學評價非常有意義：1、有利于全面貫徹國家的教育方針。2、有利于學校教育教學決策的科學化。3、有利于推進學校管理、評價工作的規(guī)范化。4、有利于提高教師的編制試題能力，有利于促進教育教學質(zhì)量的提高。5、評價作為一種反饋矯正系統(tǒng)，用于教學過程中的每一個環(huán)節(jié)上判斷該過程是否有效；如果無效，必須及時采取什么變革，以確保過程的有效性?？傊?，教學評價的目的，不僅要對教學質(zhì)量狀況做出價值判斷，而且要對教學改革提出設(shè)想，使教學改革沿著正確的方向發(fā)展。在教學改革中，我們教師應(yīng)該可以借助這樣一種分析評價的方法來肯定成績、總結(jié)經(jīng)驗、發(fā)現(xiàn)問題、找出差距，從而有針對性地采取改革措施，從而達到提高教學質(zhì)量的目的。參考文獻：1支敏編著.教育評價的基本原理與運用M.貴陽:貴州人民出版社，2006.82許世紅，胡中鋒著.數(shù)學試卷分析方法M.華東師范大學出版社，2009.10 讀書的好處1、行萬里路，讀萬卷書。2、書山有路勤為徑，學海無涯苦作舟。3、讀書破萬卷，下筆如有神。4、我所學到的任何有價值的知識都是由自學中得來的。達爾文5、少壯不努力，老大徒悲傷。6、黑發(fā)不知勤學早，白首方悔讀書遲。顏真卿7、寶