Excel在分布擬合中的應用研究

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

【關鍵詞】Excel；數據分析；擬合；決策

1.引言

假設檢驗是建立在假定樣本來自總體是正態分布的基礎上。當沒有這個假定或該假設不成立時，這些檢驗的結論就可能被質疑。為了解決該問題，統計學家創立了無須上述假定的各種非參數檢驗（nonparametric tests）。包括檢驗兩個中位數差異的WILCOXON等級和檢驗和檢驗多個總體具有相等中位數的Kruskal-Wallis等級檢驗。Excel中插件PHStat都支持這些檢驗，本文應用Excel對數據模型進行檢驗。

在許多決策模型中，可從歷史記錄或者通過一些方式收集經驗數據。例如現存的記錄可能提供了一些機器故障和維修次數的數據，或者觀察者可以在銀行或郵局收集服務時間的數據，以表1抽樣數據（來自正態分布）為例，我們可以以這些數據通過直方圖構建經驗分布。

2.應用直方圖進行數據分析

表1中的數據做直方圖：選菜單“工具”—“數據分析”，在分析工具中選“直方圖”（如果Excel未安裝數據分析功能，依次選擇“工具”-“加載宏”，加載“分析數據庫”。加載成功后，可以在“工具”下拉菜單中看到“數據分析”選項了）。直方圖選項卡的“輸入區域”選中要分析的數據，在Excel中表1的數據是一列；“接受區域”就是組距，Excel中增加一列數據“1.5 2 2.5 3 3.5 4 4.5”，選中就可以了，再選中“圖表輸出”選項，確定就生成了直方圖，如圖1所示。如果這些數據代表了決策模型中的一個很重要的變量，我們可能期望對這個數據進行理論分布擬合并驗證統計意義上的擬合優度，那么就能夠將該理論分布應用到模型分析中。

表1 50個觀測樣本

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

為了選擇恰當的理論分布，可從檢查直方圖開始，觀察其形狀是否近似于正態分布，但從直方圖分布不是對稱的，我們很容易認為是由于樣本量小的原因，因此收集更多的信息。

描述統計量可以為我們尋找分布的性質提供線索，平均數、中位數、標準差和變異系數通?？梢云鸬阶饔?。例如，正態分布的數據應該具有相當低的變異系數，然而，如果均值非常小這可能是不正確的，對于正態分布的數據，也期望平均數和中位數近似相等。在Excel中，我們應用“工具”—“數據分析”—“描述統計”來分析一下數據，得到表2，如表2所示，變異系數=標準差/平均數=0.76/2.99=0.25相當低，偏度系數為0.4，同樣也很低，表明分布基本上是對稱的，平均數和中位數非常接近，所以這些數字表明樣本可能來自正態分布，盡管直方圖看上去不是非?！罢龖B”。

表2 樣本數據描述統計量

平均2.9904

標準誤差0.1076832

中位數2.91

眾數4.67

標準差0.7614352

方差0.5797835

峰度-0.413582

偏度0.4027168

區域2.99

最小值1.68

最大值4.67

求和149.52

觀測數50

最大（1）4.67

最?。?）1.68

置信度（95.0%）0.2163975

圖1 表1數據直方圖

3.應用另一種方式——構建概率圖證明數據是否來自正態分布

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

圖2 正態概率圖

4.結論

從以上結果可以看出，因為樣本量小而且數據不規則，我們拒絕數據是來自正態分布的假設，而我們用的數據是有Excel從正態分布中產生的，由于樣本量小和較高的抽樣誤差，獲得好的擬合優度是困難的，正如本例所顯示的。于是，就實際決策模型而言，努力獲得具有代表性的數據樣本是非常重要的。

參考文獻

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

[2]王春峰，李剛.基于分布擬合法的VAR估計[J].管理工程學報，2012，4.

[3]李筠，祝勇.數據處理的Beta分布擬合法[A].第二屆全國信息獲取與處理學術會議[C].2004，8.

[4]叢浩哲，王俊驊，方守恩，童世鑫.交通事件持續時間分布擬合及其加速消散模型[J].同濟大學學報（自然科學版），2011，39.

[5]張建龍，林清泉.GH分布族下資產收益分布擬合優度比較——基于中國證券指數高頻數據的實證研究[J].數學的實踐與認識，2010，40（21）.

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

作者簡介：郭志軍（1978—），男，遼寧新民人，碩士，遼寧對外經貿學院副教授，研究方向：高等數學教學，最優化研究，應用數學。