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

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

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

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

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

5、刻度，縱坐標(biāo)為人數(shù)(相當(dāng)于樣本的頻數(shù))。在這里，縱坐標(biāo)沒有使用“頻率”而是使用“頻數(shù)”來做單位，這是因?yàn)槲覀儾皇侵怀槿∫徊糠帧皹颖荆S機(jī)抽取學(xué)生）”來進(jìn)行分析，而是對整個(gè)“總體（整個(gè)年級）”來進(jìn)行分析，因此，我們使用頻數(shù)（具體的人數(shù)）來做縱坐標(biāo)單位，更加具有實(shí)際意義，也更為直觀。圖中的黑色柱狀圖是學(xué)生成績的分布，粉紅色的曲線是由學(xué)生該科成績的平均分和標(biāo)準(zhǔn)方差所決定的正態(tài)分布曲線，左邊的紅色豎線是低分率的線，中間的黃色豎線是及格率的線，右邊的綠色線是優(yōu)秀率的線。上面還附有該科的平均分、難度系數(shù)、區(qū)分度、低分率、及格率、優(yōu)秀率等數(shù)值。相應(yīng)的一些評價(jià)參數(shù)的制作方法如下：1、平均分平均分在EXCEL里

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

7、計(jì)算：P=平均分/滿分值題目的難度值越大，則題目的難度越??；題目的難度值越小，則題目的難度越大。一般認(rèn)為，試題的難度指數(shù)在0.3-0.7之間比較合適。整份試卷的平均難度指數(shù)最好掌握在0.5左右，高于0.7和低于0.3的試題不能太多。3、試題區(qū)分度為了簡單計(jì)算， 教師可以使用下面的一種方法進(jìn)行計(jì)算區(qū)分度： 先將分?jǐn)?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、學(xué)生成績的柱狀分布圖在EXCEL里使用函數(shù)：COUNTIF (range,criteria)，其中range為單元格范圍，直接左鍵拖擊單元格即可選定，criteria為判定標(biāo)準(zhǔn)，可以是數(shù)值（直接輸數(shù)字即可無需引號(hào)，如0,1等）或字符（須用英文雙引號(hào)括起，如"YES","A","男","老師"）

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

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

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

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

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

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

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

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

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