Excel在分布擬合中的應(yīng)用研究

【摘要】Excel中的數(shù)據(jù)分析功能十分強(qiáng)大，可以對(duì)數(shù)據(jù)進(jìn)行分析得出數(shù)據(jù)的來(lái)源屬于哪類(lèi)分布，進(jìn)而對(duì)數(shù)據(jù)進(jìn)行哪一分布的擬合，為我們對(duì)實(shí)際模型進(jìn)行決策分析。就實(shí)際決策模型而言，努力獲得具有代表性的數(shù)據(jù)樣本是非常重要的。

【關(guān)鍵詞】Excel；數(shù)據(jù)分析；擬合；決策

1.引言

假設(shè)檢驗(yàn)是建立在假定樣本來(lái)自總體是正態(tài)分布的基礎(chǔ)上。當(dāng)沒(méi)有這個(gè)假定或該假設(shè)不成立時(shí)，這些檢驗(yàn)的結(jié)論就可能被質(zhì)疑。為了解決該問(wèn)題，統(tǒng)計(jì)學(xué)家創(chuàng)立了無(wú)須上述假定的各種非參數(shù)檢驗(yàn)（nonparametric tests）。包括檢驗(yàn)兩個(gè)中位數(shù)差異的WILCOXON等級(jí)和檢驗(yàn)和檢驗(yàn)多個(gè)總體具有相等中位數(shù)的Kruskal-Wallis等級(jí)檢驗(yàn)。Excel中插件PHStat都支持這些檢驗(yàn)，本文應(yīng)用Excel對(duì)數(shù)據(jù)模型進(jìn)行檢驗(yàn)。

在許多決策模型中，可從歷史記錄或者通過(guò)一些方式收集經(jīng)驗(yàn)數(shù)據(jù)。例如現(xiàn)存的記錄可能提供了一些機(jī)器故障和維修次數(shù)的數(shù)據(jù)，或者觀察者可以在銀行或郵局收集服務(wù)時(shí)間的數(shù)據(jù)，以表1抽樣數(shù)據(jù)（來(lái)自正態(tài)分布）為例，我們可以以這些數(shù)據(jù)通過(guò)直方圖構(gòu)建經(jīng)驗(yàn)分布。

2.應(yīng)用直方圖進(jìn)行數(shù)據(jù)分析

表1中的數(shù)據(jù)做直方圖：選菜單“工具”—“數(shù)據(jù)分析”，在分析工具中選“直方圖”（如果Excel未安裝數(shù)據(jù)分析功能，依次選擇“工具”-“加載宏”，加載“分析數(shù)據(jù)庫(kù)”。加載成功后，可以在“工具”下拉菜單中看到“數(shù)據(jù)分析”選項(xiàng)了）。直方圖選項(xiàng)卡的“輸入?yún)^(qū)域”選中要分析的數(shù)據(jù)，在Excel中表1的數(shù)據(jù)是一列；“接受區(qū)域”就是組距，Excel中增加一列數(shù)據(jù)“1.5 2 2.5 3 3.5 4 4.5”，選中就可以了，再選中“圖表輸出”選項(xiàng)，確定就生成了直方圖，如圖1所示。如果這些數(shù)據(jù)代表了決策模型中的一個(gè)很重要的變量，我們可能期望對(duì)這個(gè)數(shù)據(jù)進(jìn)行理論分布擬合并驗(yàn)證統(tǒng)計(jì)意義上的擬合優(yōu)度，那么就能夠?qū)⒃摾碚摲植紤?yīng)用到模型分析中。

表1 50個(gè)觀測(cè)樣本

1.812.434.22.753.38

4.113.242.692.372.8

2.272.723.93.12.25

4.673.142.12.174.18

3.584.312.793.262

2.544.213.742.122.86

3.593.233.252.073.3

2.222.802.671.684.67

2.962.833.183.033.2

2.173.382.691.753.16

為了選擇恰當(dāng)?shù)睦碚摲植迹蓮臋z查直方圖開(kāi)始，觀察其形狀是否近似于正態(tài)分布，但從直方圖分布不是對(duì)稱的，我們很容易認(rèn)為是由于樣本量小的原因，因此收集更多的信息。

描述統(tǒng)計(jì)量可以為我們尋找分布的性質(zhì)提供線索，平均數(shù)、中位數(shù)、標(biāo)準(zhǔn)差和變異系數(shù)通常可以起到作用。例如，正態(tài)分布的數(shù)據(jù)應(yīng)該具有相當(dāng)?shù)偷淖儺愊禂?shù)，然而，如果均值非常小這可能是不正確的，對(duì)于正態(tài)分布的數(shù)據(jù)，也期望平均數(shù)和中位數(shù)近似相等。在Excel中，我們應(yīng)用“工具”—“數(shù)據(jù)分析”—“描述統(tǒng)計(jì)”來(lái)分析一下數(shù)據(jù)，得到表2，如表2所示，變異系數(shù)=標(biāo)準(zhǔn)差/平均數(shù)=0.76/2.99=0.25相當(dāng)?shù)停认禂?shù)為0.4，同樣也很低，表明分布基本上是對(duì)稱的，平均數(shù)和中位數(shù)非常接近，所以這些數(shù)字表明樣本可能來(lái)自正態(tài)分布，盡管直方圖看上去不是非常“正態(tài)”。

表2 樣本數(shù)據(jù)描述統(tǒng)計(jì)量

平均2.9904

標(biāo)準(zhǔn)誤差0.1076832

中位數(shù)2.91

眾數(shù)4.67

標(biāo)準(zhǔn)差0.7614352

方差0.5797835

峰度-0.413582

偏度0.4027168

區(qū)域2.99

最小值1.68

最大值4.67

求和149.52

觀測(cè)數(shù)50

最大（1）4.67

最小（1）1.68

置信度（95.0%）0.2163975

圖1 表1數(shù)據(jù)直方圖

3.應(yīng)用另一種方式——構(gòu)建概率圖證明數(shù)據(jù)是否來(lái)自正態(tài)分布

概率圖表示了累計(jì)概率線（縱軸）以便累計(jì)分布的圖成為一條直線。這可由正態(tài)概率圖表示正態(tài)分布來(lái)說(shuō)明，概率線可以使我們檢查假定分布的效度，點(diǎn)越接近于直線，擬合假定分布的情況越好。跟據(jù)表1樣本數(shù)據(jù)我們可以用Excel中的插件PHStat來(lái)產(chǎn)生的正態(tài)分布概率圖，選擇菜單“PHStat”—“Probability prod.Distributions”——“Normal Probability Plot”，輸入數(shù)據(jù)后確定，再添加“趨勢(shì)線”得到圖2，盡管數(shù)據(jù)看上去近似直線，但數(shù)據(jù)存在非線性對(duì)稱，很難得出確定性結(jié)論。

圖2 正態(tài)概率圖

4.結(jié)論

從以上結(jié)果可以看出，因?yàn)闃颖玖啃《覕?shù)據(jù)不規(guī)則，我們拒絕數(shù)據(jù)是來(lái)自正態(tài)分布的假設(shè)，而我們用的數(shù)據(jù)是有Excel從正態(tài)分布中產(chǎn)生的，由于樣本量小和較高的抽樣誤差，獲得好的擬合優(yōu)度是困難的，正如本例所顯示的。于是，就實(shí)際決策模型而言，努力獲得具有代表性的數(shù)據(jù)樣本是非常重要的。

參考文獻(xiàn)

[1]詹姆斯.R.埃文斯，戴維.L.奧爾森.數(shù)據(jù)、模型與決策（第2版）[M].中國(guó)人民大學(xué)出版社，2006，6.

[2]王春峰，李剛.基于分布擬合法的VAR估計(jì)[J].管理工程學(xué)報(bào)，2012，4.

[3]李筠，祝勇.數(shù)據(jù)處理的Beta分布擬合法[A].第二屆全國(guó)信息獲取與處理學(xué)術(shù)會(huì)議[C].2004，8.

[4]叢浩哲，王俊驊，方守恩，童世鑫.交通事件持續(xù)時(shí)間分布擬合及其加速消散模型[J].同濟(jì)大學(xué)學(xué)報(bào)（自然科學(xué)版），2011，39.

[5]張建龍，林清泉.GH分布族下資產(chǎn)收益分布擬合優(yōu)度比較——基于中國(guó)證券指數(shù)高頻數(shù)據(jù)的實(shí)證研究[J].數(shù)學(xué)的實(shí)踐與認(rèn)識(shí)，2010，40（21）.

[6]張焱，孔瑩瑩.基于Sandia數(shù)據(jù)庫(kù)的SAR圖像分布擬合研究[J].電腦知識(shí)與技術(shù)，2013，1.

作者簡(jiǎn)介：郭志軍（1978—），男，遼寧新民人，碩士，遼寧對(duì)外經(jīng)貿(mào)學(xué)院副教授，研究方向：高等數(shù)學(xué)教學(xué)，最優(yōu)化研究，應(yīng)用數(shù)學(xué)。