EXCEL在分布擬合中的應(yīng)用①

李 晗

(吉林師范大學(xué)數(shù)學(xué)學(xué)院，吉林四平 136000)

0 引言

Excel作為一款優(yōu)秀的數(shù)據(jù)處理軟件，同時(shí)也是一款實(shí)用的統(tǒng)計(jì)分析軟件.它在應(yīng)用統(tǒng)計(jì)學(xué)中發(fā)揮著巨大作用.宋廷山等人的著作[1]充分地說明了這一事實(shí).本文將以南京地區(qū)58年來的降雨量為例，以Excel為工具，給出它在分布擬合中的應(yīng)用方法，數(shù)據(jù)來源于58238號(hào)氣象站所觀測(cè)到的南京地區(qū)1951～2008年這58年間的逐日降水資料.

國(guó)內(nèi)學(xué)者認(rèn)為降雨量的變化規(guī)律一般服從正態(tài)分布或?qū)?shù)正態(tài)分布[2]，然而由于不同地區(qū)的地形、地貌及氣候條件一般不同，因此在處理具體問題時(shí)，仍需要具體的分析判斷.為了便于說明，我們先將樣本數(shù)據(jù)匯總成年降雨量表，并升序存入Sample.xls中，其中工作表的第一列是序號(hào)1～58，第二列是南京地區(qū)58年間的年降雨量樣本數(shù)據(jù)(單位:毫米)的升序排列，記樣本數(shù)據(jù)為Xi.

1 利用EXCEL對(duì)樣本進(jìn)行描述統(tǒng)計(jì)

在Excel軟件中打開Sample.xls工作表，執(zhí)行“工具”→“數(shù)據(jù)分析”→“描述統(tǒng)計(jì)”，在彈出的“描述統(tǒng)計(jì)”對(duì)話框中，將“輸入?yún)^(qū)域”設(shè)置為$B$1:$B$58，“輸出區(qū)域”設(shè)置為 $G$1，單擊“確定”可得描述統(tǒng)計(jì)見表1.

表1 樣本的描述統(tǒng)計(jì)表

從表1中可以看出，樣本的均值為1051.54，方差為57178.92.樣本的偏度系數(shù)為 0.75，說明樣本的分布為右偏而非對(duì)稱的，樣本峰度系數(shù)為1.46，其值不為零，可以初步判定樣本不服從正態(tài)分布.

圖1 (a)樣本數(shù)據(jù)的正態(tài)概率紙散點(diǎn)圖 (b)樣本數(shù)據(jù)的正態(tài)概率紙線性回歸

2 利用EXCEL對(duì)樣本進(jìn)行正態(tài)概率紙檢驗(yàn)

為了進(jìn)一步判斷樣本是否服從正態(tài)分布，下面利用正態(tài)概率紙方法進(jìn)行檢驗(yàn).

Step1.計(jì)算樣本的累計(jì)頻率

圖2 樣本頻率直方圖

Step2.計(jì)算樣本的累計(jì)頻率對(duì)應(yīng)的正態(tài)分布分位數(shù)

在“D1”單元格中輸入“=NORMINV(C1，0，1)”并回車，然后將光標(biāo)定位在“D1”單元格右下角，向下拖動(dòng)鼠標(biāo)，完成樣本累計(jì)頻率所對(duì)應(yīng)的正態(tài)分布分位數(shù)的計(jì)算.記相應(yīng)的分位點(diǎn)為Yi.

Step3.繪制散點(diǎn)圖

以Xi為橫坐標(biāo)，Yi為縱坐標(biāo)利用Excel繪制散點(diǎn)圖.將光標(biāo)定位到Sample.xls工作表的“I1”單元格，執(zhí)行“插入”→“圖表”→“XY散點(diǎn)圖”命令，選擇“平滑線散點(diǎn)圖”，單擊“下一步”，在“數(shù)據(jù)區(qū)域”中輸入數(shù)據(jù)區(qū)域?yàn)椤?Sheet1!$B$1:$B$58，Sheet1!$D$1:$D$58”，單擊“確定”，便可得到樣本數(shù)據(jù)在正態(tài)概率紙圖上的散點(diǎn)圖，如圖1(a)所示.

Step4.繪制Xi和Yi的回歸直線

執(zhí)行“工具”→“數(shù)據(jù)分析”→“回歸”命令，在彈出的“回歸”對(duì)話框中設(shè)置Y值輸入?yún)^(qū)域?yàn)?“$D$1:$D$58”，X值輸入?yún)^(qū)域?yàn)?“$B$1:$B$58”，輸出區(qū)域?yàn)?“$F$27:$N$37”.可得回歸直線如圖1(b)所示.

從圖1可以看出，樣本兩端數(shù)據(jù)明顯偏離了回歸直線，從而可以斷定，樣本不服從正態(tài)分布.

3 樣本分布函數(shù)的確定

為了確定樣本的分布函數(shù)，先利用Excel軟件繪制樣本的直方圖，再進(jìn)行進(jìn)一步的推斷和檢驗(yàn).

Step1.利用Excel繪制直方圖

由表1知樣本最小值和最大值分別為534.6和1825.8，以530為起點(diǎn)，以50為組距進(jìn)行分組共分26組.在“B60”單元格中輸入530，在“B61”單元格中輸入580并向下拖動(dòng)鼠標(biāo)，直至數(shù)據(jù)填充到1830.將此數(shù)據(jù)作為分組的接收區(qū)域.執(zhí)行“工具”→“數(shù)據(jù)分析”→“直方圖”命令，在彈出的“直方圖”對(duì)話框中設(shè)置輸入?yún)^(qū)域?yàn)?“$B$1:$B$58”，接受區(qū)域?yàn)?“$B$63:$B$89”，輸出區(qū)域?yàn)?“$C$63:$E$89”，并勾選“累計(jì)百分率”和“圖表輸出”兩項(xiàng)，得到頻率分布表(略)和直方圖如圖2所示.

Step2.對(duì)樣本進(jìn)行對(duì)數(shù)變換

由圖2初步推測(cè)樣本服從對(duì)數(shù)正態(tài)分布.為了進(jìn)一步檢驗(yàn)結(jié)論的正確性，需先對(duì)樣本數(shù)據(jù)進(jìn)行對(duì)數(shù)變換，若變換后的數(shù)據(jù)服從正態(tài)分布，則可以斷定原樣本服從對(duì)數(shù)正態(tài)分布[3].在 Sample.xls工作表的“E1”單元格中輸入“=ln(B1)”并向下拖動(dòng)單元格完成對(duì)樣本進(jìn)行對(duì)數(shù)變換.

Step3.檢驗(yàn)對(duì)變換后樣本的正態(tài)性

再次應(yīng)用本文第2節(jié)中的方法對(duì)變換后的樣本數(shù)據(jù)進(jìn)行正態(tài)性檢驗(yàn).得到變換后數(shù)據(jù)的正態(tài)概率紙散點(diǎn)圖如圖3(a)所示和正態(tài)概率紙線性回歸圖如圖3(b)所示.

從圖3可以看出，對(duì)數(shù)變換后的樣本幾乎在處在同一直線上，從而可以斷定，原樣本服從對(duì)數(shù)正態(tài)分布.

Step4.估計(jì)分布參數(shù)

文獻(xiàn)[4]討論了對(duì)數(shù)正態(tài)分布的最大似然估計(jì)方法.設(shè)總體X服從參數(shù)為μ和σ2的對(duì)數(shù)正態(tài)分布，X1，X2，…，Xn是來自總體X的簡(jiǎn)單隨機(jī)樣本，記μ和σ2的最大似然估計(jì)量分別為，根據(jù)文獻(xiàn)[4]的結(jié)果，有

圖3 (a)對(duì)數(shù)樣本的正態(tài)概率紙散點(diǎn)圖 (b)對(duì)數(shù)樣本的正態(tài)概率紙回歸直線

在Matlab中對(duì)樣本數(shù)據(jù)進(jìn)行Kolmogorov－Smirnov檢驗(yàn)，p值為0.46，這進(jìn)一步說明了本文結(jié)果的正確性.

[1]宋廷山，吳風(fēng)慶，尉雪波.應(yīng)用統(tǒng)計(jì)學(xué):以Excel為分析工具[M].成都:西南財(cái)經(jīng)大學(xué)出版社，2006.

[2]袁興仁.蘭州地區(qū)年降水量的分布函數(shù)[J].西北師范大學(xué)學(xué)報(bào)(自然科學(xué)版)，1990(2):69－71.

[3]茆詩(shī)松，程依明，濮曉龍.概率論與數(shù)理統(tǒng)計(jì)教程[M].北京:高等教育出版社，2004:120，363.

[4]于洋，孫月靜.對(duì)數(shù)正態(tài)分布參數(shù)的最大似然估計(jì)[J].九江學(xué)院學(xué)報(bào)，2007(6).