2018年9月10日,，是第三十四個(gè)教師節(jié),，韓老師恭祝所有老師節(jié)日快樂！今天講一講如何用Excel制作學(xué)生成績正態(tài)分布圖,，送給所有老師,，幫助老師們對學(xué)生成績、分?jǐn)?shù)段分布,、試題難易程度,、教學(xué)效果等進(jìn)行分析。

示例數(shù)據(jù)如下：

結(jié)果如下：

正態(tài)分布圖中：

1. 成績分?jǐn)?shù)段直方圖,，顯示各個(gè)分?jǐn)?shù)段的人數(shù),；
2. 正態(tài)分布曲線的高峰位于平均成績位置,，由平均成績所在處開始，分別向左右兩側(cè)逐漸均勻下降,；
3. 直方圖與曲線的對比分析,，可以得到各種信息。

實(shí)現(xiàn)方法：

第一步：分析成績

對成績進(jìn)行分析,，分析出最大值,、最小值、極差（最大值—最小值）,、成績分段數(shù)量,、分段間距。

下圖是分析的結(jié)果及對應(yīng)公式：

第二步：確定分?jǐn)?shù)段

“分段點(diǎn)”就是確定直方圖的橫軸坐標(biāo)起止范圍和每個(gè)分?jǐn)?shù)段的起止位置,。第一個(gè)分段點(diǎn)要小于等于最小成績,，然后依次加上“分段間距”，直到最后一個(gè)數(shù)據(jù)大于等于最高成績?yōu)橹?。?shí)際分段數(shù)量可能與計(jì)算的“分段數(shù)”稍有一點(diǎn)差別。如下圖：

第一個(gè)分段點(diǎn)7是手工輸入,，在第二個(gè)分段點(diǎn)H2處輸入公式=H2+$D$6,，往下填充，填出各個(gè)分段點(diǎn),。

第三步：計(jì)算段內(nèi)人數(shù)

選中I2:I9,輸入公式：

=FREQUENCY(A2:A44,H2:H9),，三鍵結(jié)束，即可算出各分?jǐn)?shù)段人數(shù),。

第四步：計(jì)算成績正態(tài)分布值

選中J2,，輸入公式：

=NORMDIST(I2,AVERAGE($A$2:$A$44),STDEV($A$2:$A$44),0)，確定并往下填充,，即可得到每個(gè)成績段的正態(tài)分布值,，

正態(tài)分布概率密度正態(tài)分布函數(shù)“NORMDIST”獲取。

NORMDIST 函數(shù)：

• 返回指定平均值和標(biāo)準(zhǔn)偏差的正態(tài)分布函數(shù)

語法：

• NORMDIST(x,mean,standard_dev,cumulative)
• NORMDIST 函數(shù)語法具有下列參數(shù)：

• X

  必需,。 需要計(jì)算其分布的數(shù)值,。

• Mean

  必需。 分布的算術(shù)平均值,。

• standard_dev

  必需,。 分布的標(biāo)準(zhǔn)偏差。

• cumulative

  必需,。 決定函數(shù)形式的邏輯值,。 如果 cumulative 為TRUE，則NORMDIST返回累積分布函數(shù),；如果為FALSE,，則返回概率密度函數(shù),。

在本公式中：

• 以分段點(diǎn)值為“X”來計(jì)算：
• Mean=AVERAGE($A$2:$A$44)(數(shù)據(jù)算術(shù)平均）
• Standard_dev=STDEV($A$2:$A$44)(數(shù)據(jù)的標(biāo)準(zhǔn)方差）
• Cumulative=0(概率密度函數(shù)）

第五步：插入正態(tài)分布圖

【插入】菜單內(nèi)選擇【其他圖表】，在【所有圖表】中找【組合】圖中的【自定義組合】,，將正態(tài)分布值選擇折線,，勾選【次坐標(biāo)軸】，如下：

選定正態(tài)曲線,，【設(shè)置數(shù)據(jù)系列格式】,，勾選【平滑】：

第六步：美化加工

如果想直觀看出人數(shù)對應(yīng)的分?jǐn)?shù)段，可以在數(shù)據(jù)表中將“分?jǐn)?shù)段”列出,，通過添加數(shù)據(jù)添加到橫坐標(biāo)軸,，如下圖：