如何用EXCEL制作成績分析的正態(tài)分布圖
教育評價(jià)學(xué)是教育科學(xué)領(lǐng)域中的一個(gè)重要的應(yīng)用性很強(qiáng)的分支學(xué)科。在當(dāng)今世界教育領(lǐng)域中，教育評價(jià)、教育基礎(chǔ)理論和教育發(fā)展被認(rèn)為是三大研究范圍。教育是人類有目的、有計(jì)劃、有組織的活動，教育活動涉及教育方案、教育活動的實(shí)施、教育活動的參與者等等，要提高學(xué)校教育活動的有效性，就必須對這些內(nèi)容進(jìn)行適當(dāng)?shù)脑u價(jià)。因此，教育評價(jià)對于學(xué)校教育的改革和發(fā)展，對于學(xué)校教育的管理和決策，都有著至關(guān)重要的作用，所以備受各國政府及其教育行政部門的重視。
在學(xué)校日常工作中，通過教育評價(jià)活動來強(qiáng)化管理，已受到人們的廣泛重視。不論是宏觀的教育行政管理還是微觀的學(xué)校工作管理，都把教育評價(jià)當(dāng)作一種有效的管理手段。就一所學(xué)校而言，管理水平的高低在一定程度上能反映出該校的評價(jià)工作開展得怎么樣，而評價(jià)水平的高低又能體現(xiàn)出學(xué)校領(lǐng)導(dǎo)者的管理水平。實(shí)施素質(zhì)教育的關(guān)鍵是教師素質(zhì)的高低。為了提高教師素質(zhì)，教育行政部門和學(xué)校都加大了對教師的管理力度，開展了對教師的教學(xué)評價(jià)工作。通過有效地評價(jià)教師，不僅調(diào)動了教師工作的積極性，而且進(jìn)一步促進(jìn)了師資隊(duì)伍的建設(shè)。所以，要做一個(gè)有效的管理者，就要重視教育評價(jià)的作用。
教學(xué)評價(jià)是教育評價(jià)的重要組成部分。它以考試作為一種基礎(chǔ)性的手段，來收集有關(guān)學(xué)生對知識的掌握程度方面的信息；以測驗(yàn)作為測量的手段，獲得客觀的數(shù)據(jù)，進(jìn)行進(jìn)一步的分析、綜合，并作出價(jià)值上的判斷。
在學(xué)校教育教學(xué)工作中，從研究的目的出發(fā)開展評價(jià)工作，就是要通過評價(jià)活動促進(jìn)教育教學(xué)改革實(shí)驗(yàn)的進(jìn)行，從而提高教育教學(xué)的科學(xué)研究水平。因此，教學(xué)評價(jià)將有助于學(xué)校及教育工作者自身進(jìn)行檢查、反思，并主動改進(jìn)教育教學(xué)工作，從而有助于提高教育教學(xué)質(zhì)量。教學(xué)常規(guī)工作中的段考、期考，不僅僅是為了測量學(xué)生的知識掌握程度，我們還應(yīng)該使用現(xiàn)代的數(shù)理統(tǒng)計(jì)技術(shù)和現(xiàn)代信息技術(shù)來對考試成績進(jìn)行仔細(xì)、有效的分析，從中找出需要改進(jìn)的教學(xué)問題，并為今后的教學(xué)改革提供依據(jù)。因此，我們就需要使用
正態(tài)分布曲線來給我們的成績分析提供一個(gè)有效的參考。
從圖1中，我們可以看到一些信息，橫坐標(biāo)為從0分到150分，共計(jì)151個(gè)刻度，縱坐標(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里使用函數(shù)：AVERAGE(Number1,Number2……)其中：Number1，number2，……是要計(jì)算平均值的 1～30 個(gè)參數(shù)。
例如：在新的單元格中插入AVERAGE(
D3:D923)，“
D3:D923”就是學(xué)生成績所在的單元格范圍。
2、難度系數(shù)
難度系數(shù)是反映試題的難易程度，即考生在一個(gè)試題或一份試卷中的失分程度。如滿分150分的試題，考生平均得分108分，平均失分42分，則難度系數(shù)為42/150=0.28。
難度系數(shù)的計(jì)算公式為：L=1—X/W
其中，L為難度系數(shù)，X為樣本平均得分，W為試卷總分（一般為100分或150分）。
為了簡化試卷難度系數(shù)的計(jì)算，在EXCEL中可以用以下簡單的計(jì)算公式進(jìn)行計(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=（P1－P2）/滿分值。
區(qū)分度一般在0～+1之間，值越大區(qū)分度越好。試題的區(qū)分度在0.4以上表明此題的區(qū)分度很好，0.3～0.39表明此題的區(qū)分度較好，0.2～0.29表明此題的區(qū)分度不太好需修改，0.19以下表明此題的區(qū)分度不好，應(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ù)字即可無需引號，如0,1等）或字符（須用英文雙引號括起，如”YES”,”A”,”男”,”老師”）等，
例如：我要計(jì)算50分的人數(shù),則插入的函數(shù)為COUNTIF(
D3:D923,”=50″) ，“
D3:D923”是學(xué)生成績所在的單元格范圍，“=50”則是條件。另外需要注意的地方是，如果考試成績不是整數(shù)，比如：50.5，在使用“=50”的條件下，就不會計(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ū)А敝谱鞒鱿鄳?yīng)的柱狀分布圖了，如圖2所示。
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分，那么在表達(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é)合起來。從“頻率=頻數(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所示。
當(dāng)我們把“學(xué)生成績的柱狀分布圖”與“正態(tài)曲線分布圖”共同制作在一個(gè)圖表中，就可以得到如圖1所示的效果圖了。在這樣一幅效果圖中，我們建立了一批數(shù)據(jù)頻數(shù)分布
柱狀圖與該批數(shù)據(jù)平均值和標(biāo)準(zhǔn)差所確定的
正態(tài)分布曲線的對照關(guān)系。有了參照，我們就可以分析這批學(xué)生該科考試成績的質(zhì)量了。另外再附加上平均分、難度系數(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í)的過程。
2、從圖5中，我們可以看出，該科的總分（120分）用最右邊的綠色豎線來標(biāo)識，左邊的綠色豎線則是優(yōu)秀線。因?yàn)樽鰧W(xué)生成績分析是所有科目一起做的，為了減少操作的麻煩，也是為了能進(jìn)行不同科目的橫向比較，雖然不同科目的成績沒有可比性，但是對于他們的教學(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)分布。
如果頻數(shù)分布的高峰向左偏移，長尾向右側(cè)延伸稱為正偏態(tài)分布（考試分?jǐn)?shù)集中在低端），也稱右偏態(tài)分布；同樣的，如果頻數(shù)分布的高峰向右偏移，長尾向左延伸則成為負(fù)偏態(tài)分布（考試分?jǐn)?shù)集中在高端），也稱左偏態(tài)分布。
從這里我們可以了解到，在編制試題的時(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)識，右邊的綠色豎線則是優(yōu)秀線。我們可以看到粉紅色的正態(tài)曲線已經(jīng)溢出了滿分線。因?yàn)樵摽频钠骄直容^高，區(qū)分度為0.41，正態(tài)曲線就會比較矮，由于正態(tài)曲線是兩端對稱性的，所以就會存在一端溢出的現(xiàn)象，這也可以說是用正態(tài)曲線來分析成績的一個(gè)局限性。因此，當(dāng)難度系數(shù)在0.5，區(qū)分度不超過0.5的時(shí)候用正態(tài)曲線來分析是最合適的。
從以上各類情況，我們可以看到，用EXCEL來制作成績分析的正態(tài)布圖，并且結(jié)合難度系數(shù)、區(qū)分度、成績柱狀分布圖、低分率、及格率、優(yōu)秀率等參數(shù)來對學(xué)生的考試成績來進(jìn)行分析，這是一種有效的可操作的方法。通過對多種衡量參數(shù)融合為一體來進(jìn)行分析，使得我們對教學(xué)質(zhì)量的整體把握更為詳細(xì)和有效。如果只是單一的學(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ì)量的目的。
CDA數(shù)據(jù)分析師考試相關(guān)入口一覽（建議收藏）：
? 想報(bào)名CDA認(rèn)證考試，點(diǎn)擊>>>
“CDA報(bào)名”
了解CDA考試詳情；
? 想學(xué)習(xí)CDA考試教材，點(diǎn)擊>>> “CDA教材” 了解CDA考試詳情；
? 想加入CDA考試題庫，點(diǎn)擊>>> “CDA題庫” 了解CDA考試詳情；
? 想了解CDA考試含金量，點(diǎn)擊>>> “CDA含金量” 了解CDA考試詳情；
京公網(wǎng)安備 11010802034615號
經(jīng)營許可證編號：京B2-20210330
