Ｅｘｃｅｌ在統計中的應用分析

[提 要] 在傳統的統計分析中，經常用到一些專門的統計軟件，但這些專門軟件往往價格不扉，難于推廣#65377;而在目前應用廣泛的Excel中有專門用于描述統計方面的函數，可以直接利用這些函數計算一些統計指標#65377;Excel有功能強大的VBA，用它定義一些函數，不但使用方便，可針對不同的數據進行統計計算，還可處理一些Excel內部函數不能處理的統計數據#65377;“分析工具庫”是Excel中一個專門用于統計的工具，其中的“描述統計”可以一次輕松獲得多個統計指標，從而可利用Excel達到統計分析的目的#65377;

[關鍵詞] Excel;描述統計;函數;宏

[作者簡介]張占軍(1969-)，男，華北電力大學研究生院在讀研究生#65377;(河北保定 071003)

Excel是大家熟知的Office組件，擁有豐富的函數，其中專門用于統計方面的函數就有近百個#65377;Ex-cel強大的數據計算功能，不僅能處理普通的數據運算，而且也能勝任處理專門的統計業務，如統計學中的數據描述#65380;概率分布#65380;參數估計#65380;假設檢驗#65380;方差分析等等，都可以用Excel來完成#65377;因此，對于絕大多數統計分析來說，用Excel替代昂貴的專門統計軟件，就可以輕松完成統計分析的任務#65377;本文中，筆者以描述統計數據分析為例，來說明利用Excel處理統計專業數據的途徑和步驟#65377;

一#65380;統計函數

在描述統計中，描述數據集中趨勢的統計指標有:眾數#65380;中位數#65380;均值#65380;調和平均數#65380;幾何平均數;描述數據離散程度的統計指標有:極值#65380;全距#65380;四分位差#65380;方差#65380;標準差等#65377;在未分組資料中，可直接用Excel函數計算這些統計指標#65377;如根據表1中的數據，計算某企業30名職工工資的眾數，可在存放結果的單元格中輸入= MODE(A2：F6)，按確認鍵，結果立刻出現在當前單元格中，比起手工計算查找來說又快又準確#65377;同樣，計算其他統計指標時，在“插入函數”窗口的“函數類別”下拉文本框中選擇“統計”類，然后在函數文本框中選擇相關函數，使用即可，和使用Excel常用函數沒有區別#65377;當選取某個函數時，在“插入函數”窗口的下面有幫助信息，簡要說明該函數的功能及如何使用該函數#65377;

二#65380;用VBA處理統計數據

由于函數只能應用于處理未分組資料，但對于分組的統計數據，不能直接使用Excel內部函數求得，需要多步計算，過程較繁瑣#65377;但可以用VAB定義一個函數，使用這個函數，就像使用Excel內部函數一樣，輸入參數，點擊確定，頃刻就可得到結果;再者可以在不同的地方#65380;針對不同的數據進行計算，可謂一勞永逸#65377;定義這樣一個函數需要一些簡單的VBA編程知識，但并不很難#65377;如表2中所示資料，是對表1數據的分組(“組距上限”列各數值是各組組距的上限值，一定要這樣輸入，也是為了更好的使用FREQUENCY()函數)，輸入頻數或用FREQUENCY()計算頻數(計算后，通過“復制”“選擇性粘貼”粘貼成數值)#65377;順次執行菜單“工具”“宏”“Visual Basic編輯器”，打開Visual Basic編輯器，選擇菜單“插入”“模塊”，然后再選擇菜單“插入”“過程”，在對話框中選擇“函數”單選按鈕，輸入“函數名稱”，如:get-Mode，點擊確定，然后在彈出的窗口中輸入如下代碼:

Public Function get Mode (your Range As Range)

Dim max Frequency， lower Limit， class Interval， interval1， intervall2 As Single

Dim row，col As Integer＇定義變量:行號，列號

Set interval Range = your Range .Find (Application .Worksheet Function. Max (your Range))

row = interval Range. row ＇眾數組所在行

col = interval Range. Column ＇眾數組所在列

max Frequency = interval Range. Value ＇最大頻數

lower Limit = Application. Cells(row - 1， col - 1).Value + 1 ＇組下限

class Interval = Application. Cells(row， col - 1).Value - Application. Cells(row - 1， col - 1).Value ＇組距

interval1 = max Frequency - Application. Cells(row - 1， col).Value

interval2 = max Frequency - Application. Cells(row + 1， col).Value

get Mode = lower Limit + (interval1 / (interval1 + interval2)) * class Interval ＇應用眾數公式

End Function

最后保存退出，這樣計算分組數據眾數的函數“get Mode”就完成了#65377;使用方法和使用Excel內部函數一樣，選擇“用戶定義”函數，就會看到get Mode函數，選擇它并在參數框中輸入頻數的單元格區域，如表2中的“C3:C7”點擊“確定”，眾數就計算出來了#65377;將計算結果和手工計算的結果比較，結果是一致的#65377;使用這個函數同樣可以直接計算其他分組數據的眾數#65377;

上面是一個應用VBA計算集中趨勢描述指標的例子，下面看一個如何用VBA計算離散程度指標的例子#65377;

計算標準差，未分組數據可直接用函數STDEV()或STDEVP()求得，若要求分組數據的標準差或帶有權數的標準差，就不太容易了，但同樣可以用自定義函數的方法解決之#65377;如表3是對表1數據的分組，求職工收入的標準差#65377;按照前面自定義函數的制作過程，定義如下函數:

Public Function get Stdev (interval Range As Range， avg As Single)

Dim i， row， col As Integer ＇定義變量:計數，行號，列號

row = interval Range. row

col = interval Range. Column

For i = row To row + interval Range. Rows. Count - 1

g = g + Cells(i， col)

h = h + (Application. Cells(i， col - 1) - avg) * (Application. Cells(i， col - 1) - avg) * Cells(i， col)

Next i

Get Stdev = Sqr(h / g)

End Function

簡單的幾行代碼，函數就定義好了#65377;使用時輸入參數為權數范圍“C3：C7”和平均值“B9”，點擊確定，就得出職工收入的標準差#65377;

計算分組數據的其他統計指標，同樣可以自己定義函數，定義時雖然有些費事，但使用方便，計算快速#65377;

三#65380;描述統計工具

前面介紹的都是利用函數計算單個統計指標，使用Excel的“分析工具庫”中的“描述統計”，可以一次計算若干個統計指標，更方便快捷，還可以同時產生圖表#65377;“分析工具庫”在工具菜單下，如果沒有，則要運行“安裝”程序來加載“分析工具庫”，安裝完畢之后，通過“工具”菜單中的“加載宏”命令，在“加載宏”對話框中選擇并啟動它#65377;下面來看如何用它做描述統計#65377;

仍以前面職工工資收入為例，將數據排成一列，如表4中的A列(圖中未顯示全部數據)，然后選擇菜單“工具”“數據分析庫”“描述統計”，彈出描述統計對話框#65377;在輸入區域文本框中輸入數據所在單元格，如“A2:A32”;在分組方式中，依據輸入區域中的數據是按行還是按列排列，單擊“行”或“列”，在這兒選擇“列”;如果輸入區域的第一行中包含標志項(變量名)，則選中“標志位于第一行”復選框;如果輸入區域的第一列中包含標志項，則選中“標志位于第一列”復選框;如果輸入區域沒有標志項，則不選任何復選框，Excel將在輸出表中生成適宜的數據標志，這里選中“標志位于第一行”;“輸出選項”，前三項依據輸出結果存放的位置不同，來選擇以本表中某個單元格開始的區域，或輸出到新工作表或新工作簿;“匯總統計”，指定輸出表中生成“平均值#65380;標準誤差#65380;中值#65380;眾數#65380;標準偏差#65380;方差#65380;峰值#65380;偏斜度#65380;極差(全距)最小值#65380;最大值#65380;總和#65380;樣本個數”統計結果，則選中此復選框;“均值置信度”:若需要輸出由樣本均值推斷總體均值的置信區間，則選中此復選框，然后在右側的編輯框中，輸入所要使用的置信度;“第K個最大/小值”:在輸出表中包含輸入區域數據的第k個最大/小值，若選中此復選框，在右側的編輯框中，輸入k的數值#65377;所有這些都設置好后，點擊確定，結果即可出來，如表4中的C列與D列#65377;

使用Excel內部函數和分析工具庫，處理統計數據，簡單的使用就可輕松獲得計算結果;使用VBA來自定義函數，處理一些Excel內部函數無法直接處理的數據，更是靈活#65380;便捷#65377;

[參考文獻]

[1]安維默.統計電算化[M].北京:中國統計出版社，2000.

[責任編輯:李麗娜]