Excel在統(tǒng)計中的應(yīng)用與數(shù)據(jù)統(tǒng)計分析

1、Excel在統(tǒng)計中的應(yīng)用Excel與數(shù)據(jù)統(tǒng)計分析一、實驗說明(一中文Excel 簡介Microsoft Excel 是美國微軟公司開發(fā)的Windows 環(huán)境下的電子表格系統(tǒng),它是目前應(yīng)用最為廣泛的辦公室表格處理軟件之一。自Excel 誕生以來 Excel 歷經(jīng)了Excel5.0、Excel95、Excel97 和Excel2000 等不同版本。隨著版本的不斷提高,Excel 軟件的強大的數(shù)據(jù)處理功能和操作的簡易性逐漸走入了一個新的境界,整個系統(tǒng)的智能化程度也不斷提高,它甚至可以在某些方面判斷用戶的下一步操作,使用戶操作大為簡化。Excel 具有強有力的數(shù)據(jù)庫管理功能、豐富的宏命令和函數(shù)、強有力

2、的決策支持工具、圖表繪制功能、宏語言功能、樣式功能、對象連接和嵌入功能、連接和合并功能,并且操作簡捷,這些特性,已使Excel 成為現(xiàn)代辦公軟件重要的組成部分。由于大家對Excel的常用辦公功能都比較熟悉,本實驗重點介紹Excel在統(tǒng)計分析中的應(yīng)用。(二實驗?zāi)康呐c要求本實驗重點介紹Excel在統(tǒng)計分析中的應(yīng)用,包括Excel在描述統(tǒng)計中的應(yīng)用以及Excel在推斷統(tǒng)計中的應(yīng)用,要求學(xué)生熟練掌握運用Excel進行統(tǒng)計分析的方法,并能夠?qū)Ψ治鼋Y(jié)果進行解釋。二、實驗實驗一 Excel 在描述統(tǒng)計中的應(yīng)用實驗?zāi)康募耙笠髮W(xué)生掌握運用Excel進行描述統(tǒng)計分析、繪制各種圖表和運用數(shù)據(jù)透視表工具的技術(shù)。實

3、驗內(nèi)容及步驟(一描述統(tǒng)計分析例1-1:表1-1是1978-2005年我國城鎮(zhèn)居民可支配收入數(shù)據(jù),試求城鎮(zhèn)居民可支配收入時間序列的基本統(tǒng)計量。表1-1 1978-2005年我國城鎮(zhèn)居民可支配收入(元 STEP1:用鼠標點擊工作表中待分析數(shù)據(jù)的任一單元格。STEP2:選擇“工具”菜單的“數(shù)據(jù)分析”子菜單。STEP3:用鼠標雙擊數(shù)據(jù)分析工具中的“描述統(tǒng)計”選項。STEP4:出現(xiàn)“描述統(tǒng)計”對話框,如圖1-1所示。 圖 1-1對話框內(nèi)各選項的含義如下:輸入?yún)^(qū)域:在此輸入待分析數(shù)據(jù)區(qū)域的單元格范圍。分組方式:如果需要指出輸入?yún)^(qū)域中的數(shù)據(jù)是按行還是按列排列,則單擊“行”或“列”。標志位于第一行/列:如果輸

4、入?yún)^(qū)域的第一行中包含標志項(變量名,則選中“標志位于第一行”復(fù)選框;如果輸入?yún)^(qū)域的第一列中包含標志項,則選中“標志位于第一列”。復(fù)選框:如果輸入?yún)^(qū)域沒有標志項,則不選任何復(fù)選框,Excel 將在輸出表中生成適宜的數(shù)據(jù)標志。均值置信度:若需要輸出由樣本均值推斷總體均值的置信區(qū)間,則選中此復(fù)選框,然后在右側(cè)的編輯框中,輸入所要使用的置信度。例如,置信度 95%可計算出的總體樣本均值置信區(qū)間為10,則表示:在5%的顯著水平下總體均值的置信區(qū)間為( X -10, X +10。第 K 個最大/小值:如果需要在輸出表的某一行中包含每個區(qū)域的數(shù)據(jù)的第 k 個最大/小值,則選中此復(fù)選框。然后在右側(cè)的編輯框中,

5、輸入 k 的數(shù)值。輸出區(qū)域:在此框中可填寫輸出結(jié)果表左上角單元格地址,用于控制輸出結(jié)果的存放位置。新工作表:單擊此選項,可在當前工作簿中插入新工作表,并由新工作表的 A1 單元格開始存放計算結(jié)果。如果需要給新工作表命名,則在右側(cè)編輯框中鍵入名稱。新工作簿:單擊此選項,可創(chuàng)建一新工作簿,并在新工作簿的新工作表中存放計算結(jié)果。匯總統(tǒng)計:指定輸出表中生成下列統(tǒng)計結(jié)果,則選中此復(fù)選框。STEP5:填寫完“描述統(tǒng)計”對話框之后,按“確定”按扭即可。結(jié)果如圖1-2所示。 圖1-2 描述統(tǒng)計結(jié)果結(jié)果說明:描述統(tǒng)計工具可生成以下統(tǒng)計指標,按從上到下的順序其中包括樣本的平均值、標準誤差、組中值、眾數(shù)、樣本標準差

6、、樣本方差、峰度值、偏度值、極差、最小值、最大值、樣本總和、樣本個數(shù)和一定顯著水平下總體均值的置信區(qū)間。(二繪圖操作例1-2:仍以例1-1的數(shù)據(jù)為例,繪制城鎮(zhèn)居民可支配收入時間序列的散點圖。STEP1:拖動鼠標選定數(shù)值區(qū)域A2:C12,不包括數(shù)據(jù)上面的標志項。STEP2:選擇“插入”菜單的“圖表”子菜單,進入圖表向?qū)?圖1-3所示。STEP3:選擇“圖表類型”為“散點圖”,然后單擊“下一步”。 圖1-3 圖表向?qū)DSTEP4:確定用于制作圖表的數(shù)據(jù)區(qū)。Excel 將自動把你前面所選定的數(shù)據(jù)區(qū)的地址放入圖表數(shù)據(jù)區(qū)的內(nèi),單擊下一步按鈕,出現(xiàn)圖1-4所示對話框。 圖1-4 作圖過程圖STEP5:填寫

7、圖表標題、X 軸坐標名稱、Y 軸坐標名稱,此處不用填寫,單擊“下一步”。STEP6:選擇圖表輸出的位置,然后單擊“完成”按扭即生成(三數(shù)據(jù)透視表工具數(shù)據(jù)透視表是Excel 中強有力的數(shù)據(jù)列表分析工具。它不僅可以用來作單變量數(shù)據(jù)的次數(shù)分布或總和分析,還可以用來作雙變量數(shù)據(jù)的交叉頻數(shù)分析、總和分析和其它統(tǒng)計量的分析。例1-3:表1-2列出了學(xué)生兩門功課評定結(jié)果,建立學(xué)生兩門功課評定結(jié)果的交叉頻數(shù)表。表1-2 學(xué)生兩門功課評定結(jié)果 操作步驟:STEP1:輸入數(shù)據(jù),選中有數(shù)據(jù)的任一單元格,然后選擇“數(shù)據(jù)”菜單的“數(shù)據(jù)透視表”子菜單,進入數(shù)據(jù)透視表向?qū)?。STEP2:選擇“Microsoft Excel

8、數(shù)據(jù)清單或數(shù)據(jù)庫”為數(shù)據(jù)源。圖1-6所示,單擊“下一步”。 圖1-6STEP3:選擇待分析的數(shù)據(jù)的區(qū)域,一般情況下Excel 會自動根據(jù)當前單元格確定待分析數(shù)據(jù)區(qū)域,因此你只要直接單擊“下一步”按扭即可。STEP4:確定數(shù)據(jù)透視表的結(jié)構(gòu),在此例中,要建立的是一個交叉頻數(shù)表,分別按語文和數(shù)學(xué)的成績對學(xué)生的人數(shù)進行交叉頻數(shù)分析,因此可將三個按扭“學(xué)號”、“語文”、“數(shù)學(xué)”分別拖放到表格的指定部位,并且雙擊“求和項:學(xué)號”,將其改為記數(shù)項,結(jié)果如圖1-7所示。 圖1-7 布局對話框STEP5:選擇數(shù)據(jù)透視表的顯示位置之后,單擊“完成按扭”,可出現(xiàn)如圖1-8所示的數(shù)據(jù)透視表。 圖1-8結(jié)果說明:如圖1

9、-8的結(jié)果所示,數(shù)據(jù)透視表可以作為一個交叉頻數(shù)分析工具。完成數(shù)據(jù)透視表之后,可按需要修改數(shù)據(jù)表的顯示格式。例如,如果想要把表格中的頻數(shù)替換成為百分比數(shù)?？梢杂檬髽擞覔纛l數(shù)的任一單元格,選擇“字段”子菜單,單擊“選項”按扭,將“數(shù)據(jù)顯示方式”替換成為“占總和的百分比”,然后單擊“確定”按扭即可。按同樣方式,可將數(shù)據(jù)透視表修改成為其它不同樣式。實驗二 Excel中的二項分布工具實驗?zāi)康募耙笳莆绽肊xcel的BINOMDIST 的函數(shù)計算二項分布的概率以及累積概率。實驗內(nèi)容及步驟例1-4:一個推銷員打了六個電話,推銷成功的概率是0.3,建立推銷成功次數(shù)的概率分布圖表?？梢园匆韵虏襟E建立推銷成功次

10、數(shù)的概率分布圖表。STEP1:如圖1-9所示,先在Excel之下建立好概率分布表格的框架。 圖 1-9STEP2:如圖1-10所示,先在B7 至F7 單元格分別輸入概率計算公式。 圖1-10STEP3:公式的拷貝。選取B7 至F7 單元格,拖動“填充柄”至F13 單元格即可完成公式的拷貝操作。結(jié)果圖1-11所示。 圖1-11STEP4:下面開始創(chuàng)建二項分布圖表。選取B7 至B13 單元格,選取“插入”菜單的“圖表”子菜單。STEP5:選擇“柱狀圖”,然后單擊“下一步”。STEP6:單擊“系列”標簽,單擊“分類(X軸標志”框,并用鼠標選取A7至A13 單元格為圖表X 軸的軸標,然后單擊“下一步”

11、。STEP7:分別鍵入圖表名稱“二項分布圖”,X 軸名稱“成功次數(shù)”,Y 軸名稱“成功概率”,單擊“完成”按扭即可生成二項分布圖表。結(jié)果說明: 如圖1-11 所示,利用Excel 的BINOMDIST 的函數(shù)可以計算出二項分布的概率以及累積概率。BINOMDIST 函數(shù)可以帶四個參數(shù),各參數(shù)的含義分別是:實驗成功的次數(shù),實驗的總次數(shù),每次實驗中成功的概率,是否計算累積概率。四個參數(shù)是一個邏輯值,如果為TRUE,函數(shù) BINOMDIST 返回累積分布函數(shù),如果為FALSE,返回概率密度函數(shù)。另外,EXCEL還提供了其它分布的函數(shù),如函數(shù)CRITBINOM;函數(shù)HYPGEOMDIST;函數(shù)NEGB

12、INOMDIST:函數(shù)POISSON: 正態(tài)分布函數(shù)NORMDIST:函數(shù)NORMSDIST:函數(shù)NORMSINV:t 分布函數(shù)TDIST:有興趣的同學(xué)可以自己研究。實驗三隨機抽樣工具實驗?zāi)康募耙笳莆绽肊xcel的BINOMDIST 的函數(shù)計算二項分布的概率以及累積概率。實驗內(nèi)容及步驟Excel 中的Rand(函數(shù)可以返回大于等于 0 小于 1 的均勻分布隨機數(shù),Rand(不帶任何參數(shù)運行,每次計算時時都將返回一個新的數(shù)值。RAND(函數(shù)可以被用來作為不重復(fù)抽樣調(diào)查的工具。例1-5:如圖1-12所示有10 個象征性的樣本數(shù)據(jù),欲從中隨機抽取5 個數(shù)據(jù)可按如下步驟操作: 圖1-12STEP1

13、:選擇B2 單元格,輸入公式“=RAND(”并回車。STEP2:拖動B2 單元格右下角的填充柄至B11 單元格,并在B1 單元格輸入標題“RANDOM”。STEP3:選取單元格B2 至B11,右擊選中的區(qū)域選擇“復(fù)制”,再次右擊選中的區(qū)域,選擇“選擇性粘貼”,單擊選項“數(shù)值”后,點擊“確定”按扭。STEP4:選取單元格A2 至B11 單元格,選擇“數(shù)據(jù)”菜單項下的排序子菜單。STEP5:選取“RANDOM”為主要關(guān)鍵字,然后點擊“確定”按扭。排序結(jié)果如圖1-13 所示,A2 至A6 單元格的樣本即為隨機抽取的5 個樣本。 圖1-13實驗四由樣本推斷總體實驗?zāi)康募耙笳莆绽肊xcel的幾個函數(shù)

14、,如求平均函數(shù)AVERAGE、標準差函數(shù)STDEV、T 分布函數(shù)TINV 等的組合使用構(gòu)造一個專門用于實現(xiàn)樣本推斷總體的Excel工作表。實驗內(nèi)容及步驟下面的例子先計算樣本的平均數(shù)和標準差,然后在一定置信水平上估計總體均值的區(qū)間范圍。操作步驟:STEP1:構(gòu)造工作表。如圖1-14所示,首先在各個單元格輸入以下的內(nèi)容,其中左邊是變量名,右邊是相應(yīng)的計算公式。STEP2:為表格右邊的公式計算結(jié)果定義左邊的變量名。選定A4:B6,A8:B8和A10:B15 單元格(先選擇第一部分,再按住CTRL 鍵選取另外兩個部分,選擇“插入”菜單的“名稱”子菜單的“指定”選項,用鼠標點擊“最左列”選項,然后點擊“

15、確定”按扭即可。 圖1-14STEP3:輸入樣本數(shù)據(jù),和用戶指定的置信水平0.95,如圖附-13 所示。STEP4:為樣本數(shù)據(jù)命名。選定D1:D11 單元格,選擇“插入”菜單的“名稱”子菜單的“指定”選項,用鼠標點擊“首行”選項,然后點擊“確定”按扭,得到圖1-15所示的計算結(jié)果。 圖1-15結(jié)果說明:以上例子說明如何交叉組合使用Excel 的公式和函數(shù),以構(gòu)造出一個能實現(xiàn)樣本推斷總體有關(guān)計算的Excel 工作表。實際上,在用Excel 進行數(shù)據(jù)統(tǒng)計處理之時,許多統(tǒng)計功能可以使用和上例類似的方法,通過組合使用Excel 的各類統(tǒng)計函數(shù)和公式加以實現(xiàn)的。實驗五假設(shè)檢驗實驗?zāi)康募耙笳莆绽肊xc

16、el 的正態(tài)分布函數(shù)NORMSDIST、判斷函數(shù)IF 等,構(gòu)造一張能夠?qū)崿F(xiàn)在總體方差已知情況下進行總體均值假設(shè)檢驗的Excel工作表。實驗內(nèi)容及步驟例1-6:利用Excel 的正態(tài)分布函數(shù)NORMSDIST、判斷函數(shù)IF 等,構(gòu)造一張能夠?qū)崿F(xiàn)在總體方差已知情況下進行總體均值假設(shè)檢驗的Excel 工作表。操作步驟:STEP1:構(gòu)造工作表。如圖1-16 所示,首先在各個單元格輸入以下的內(nèi)容,其中左邊是變量名,右邊是相應(yīng)的計算公式。STEP2:為表格右邊的公式計算結(jié)果定義左邊的變量名。選定A3:B4,A6:B8,A10:A11,A13:A15 和A17:B19 單元格,選擇“插入”菜單的“名稱”子菜

17、單的“指定”選項,用鼠標點擊“最左列”選項,然后點擊“確定”按扭即可。 圖1-16STEP3:輸入樣本數(shù)據(jù),以及總體標準差、總體均值假設(shè)、置信水平數(shù)據(jù)。如圖1-17所示。STEP4:為樣本數(shù)據(jù)命名。選定C1:C11 單元格,選擇“插入”菜單的“名稱”子菜單的“指定”選項,用鼠標點擊“首行”選項,然后點擊“確定”按扭,得到如圖1-17中所示的計算結(jié)果。 圖1-17結(jié)果說明:如圖1-17所示,該例子的檢驗結(jié)果不論是單側(cè)還是雙側(cè)均為拒絕Ho 假設(shè)。所以,根據(jù)樣本的計算結(jié)果,在5%的顯著水平之下,拒絕總體均值為35 的假設(shè)。同時由單側(cè)顯著水平的計算結(jié)果還可以看出,在總體均值是35 的假設(shè)之下,樣本均值

18、小于等于31.4 的概率僅為0.。實驗六雙樣本等均值假設(shè)檢驗實驗?zāi)康募耙笳莆绽肊xcel 數(shù)據(jù)分析中提供雙樣本等均值假設(shè)檢驗工具進行假設(shè)檢驗的方法,并能夠解釋實驗結(jié)果。實驗內(nèi)容及步驟例1-7:雙樣本等均值檢驗是在一定置信水平之下,在兩個總體方差相等的假設(shè)之下,檢驗兩個總體均值的差值等于指定平均差的假設(shè)是否成立的檢驗。假設(shè)某工廠為了比較兩種裝配方法的效率,分別組織了兩組員工,每組9 人,一組采用新的裝配方法,另外一組采用舊的裝配方法。18個員工的設(shè)備裝配時間圖1-18 中表格所示。根據(jù)以下數(shù)據(jù),是否有理由認為新的裝配方法更節(jié)約時間? 圖1-18操作步驟:STEP1:選擇“工具”菜單的“數(shù)據(jù)分

19、析”子菜單,雙擊“t-檢驗: 雙樣本等方差假設(shè)”選項,則彈出圖1-19 所示對話框。 圖1-19STEP2:分別填寫變量1 的區(qū)域:$B$1:$B$10,變量2 的區(qū)域: $D$1:$D$10,由于我們進行的是等均值的檢驗,填寫假設(shè)平均差為0,由于數(shù)據(jù)的首行包括標志項選擇標志選項,所以選擇“標志”選項,再填寫顯著水平為0.05,然后點擊“確定”按扭。則可以得到圖1-20所示的結(jié)果。 圖1-20結(jié)果分析:如圖1-20中所示,表中分別給出了兩組裝配時間的平均值、方差和樣本個數(shù)。其中,合并方差是樣本方差加權(quán)之后的平均值,Df 是假設(shè)檢驗的自由度它等于樣本總個數(shù)減2,t 統(tǒng)計量是兩個樣本差值減去假設(shè)平

20、均差之后再除于標準誤差的結(jié)果,“P(T=t單尾”是單尾檢驗的顯著水平,“t 單尾臨界”是單尾檢驗t 的臨界值,“P(T1 之后,都在區(qū)間(-0.438,0.438之間,因此可以認為自相關(guān)函數(shù)在K1 之后截尾,因此我們選用AR(1模型進行數(shù)據(jù)擬合。復(fù)制C2:C20 的數(shù)據(jù),將之以值復(fù)制的形式復(fù)制到D3:D21 的單元格,并在D1 中填入標志項“Z(-1”。選擇“工具”菜單的“數(shù)據(jù)分析”子菜單,雙擊“回歸”選項,彈出回歸分析對話框。按圖附-40 所示的方式填寫對話框。然后單擊“確定”按扭,即可得到AR(1模型的估計結(jié)果。STEP5:按以上操作步驟,可得到圖1-41 所示AR(1模型。 圖 1-41

21、結(jié)果分析:零均值化模型的估計結(jié)果是Z=1.06284*Z(-1,還原成上證指數(shù),最終的時間序列模型是:上證指數(shù)估計值-上證指數(shù)的平均值=1.06284(上一天上證指數(shù)-上證指數(shù)平均值。Excel與數(shù)據(jù)統(tǒng)計分析統(tǒng)計計算與統(tǒng)計分析強調(diào)與計算機密切結(jié)合,Excel與數(shù)據(jù)統(tǒng)計分析旨在提高學(xué)生計算機的綜合運用能力,用統(tǒng)計方法分析問題、解決問題而編寫的。根據(jù)教材內(nèi)容,也可以選擇使用SPSS、QSTAT、Evievs、SAS、MINITAB等統(tǒng)計軟件。第三章統(tǒng)計整理3.1 計量數(shù)據(jù)的頻數(shù)表與直方圖例3.1 (3-1一、指定接受區(qū)域直方圖在應(yīng)用此工具前,用戶應(yīng)先決定分布區(qū)間。否則,Excel將用一個大約等于數(shù)

22、據(jù)集中某數(shù)值的平方根作區(qū)間,在數(shù)據(jù)集的最大值與最小值之間用等寬間隔。如果用戶自己定義區(qū)間,可用2、5或10的倍數(shù),這樣易于分析。對于工資數(shù)據(jù),最小值是100,最大值是298。一個緊湊的直方圖可從區(qū)間100開始,區(qū)間寬度用10,最后一區(qū)間為300結(jié)束,需要21個區(qū)間。這里所用的方法在兩端加了一個空區(qū)間,在低端是區(qū)間“100或小于100”,高端是區(qū)間“大于300”。參考圖3.3,利用下面這些步驟可得到頻率分布和直方圖:1.為了方便,將原始數(shù)據(jù)拷貝到新工作表“指定頻數(shù)直方圖”中。2.在B1單元中輸入“組距”作為一標記,在B2單元中輸入100,B3單元中輸入110,選取B2:B3,向下拖動所選區(qū)域右下

23、角的+到B22單元。3.按下列步驟使用“直方圖”分析工具: (1, 在分析工具框中“直方圖”。如圖4所示。 圖3.1 數(shù)據(jù)分析工具之直方圖對話框1 輸入輸入?yún)^(qū)域:A1:A51接受區(qū)域:B1:B22 (這些區(qū)間斷點或界限必須按升序排列選擇標志2 輸出選項輸出區(qū)域: C1選定圖表輸出 (2Excel將計算出結(jié)果顯示在輸出區(qū)域中。 圖3.2 數(shù)據(jù)分析工具之直方圖對話框Excel將把頻率分布和直方圖放在工作表中,如圖3.3所示,輸出表的C和D列中包括開始指定的界限。這些界限實際上是每一區(qū)間的上限,也就是說,界限實際上是邊界。 圖3.3 頻數(shù)分布與直方圖為了使圖表更像傳統(tǒng)的直方圖和更易于理解,可雙擊圖表并對它做如下修改:1.圖例:因為只有一個系列的數(shù)據(jù)顯示在圖表中,所以不需要圖例。單擊圖例(位于圖表右側(cè)的“頻率”并按Delete鍵。2.圖表區(qū):繪圖區(qū)是以X和Y軸為邊界的矩形區(qū)域。通過在柱形上面單擊可選取繪圖區(qū),單擊鼠標右鍵并選擇繪圖區(qū)格式,將邊框改為無并將區(qū)域改為無,單擊確定。3.條寬:在傳統(tǒng)的直方圖中,柱形是彼此相連接而不是分開的。選擇某個柱形,單擊鼠標右鍵,選擇數(shù)據(jù)系列格式,并單擊選擇標簽,將間