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

精選優(yōu)質(zhì)文檔-----傾情為你奉上精選優(yōu)質(zhì)文檔-----傾情為你奉上專心---專注---專業(yè)專心---專注---專業(yè)精選優(yōu)質(zhì)文檔-----傾情為你奉上專心---專注---專業(yè)Excel在統(tǒng)計(jì)中的應(yīng)用Excel與數(shù)據(jù)統(tǒng)計(jì)分析一、實(shí)驗(yàn)說明(一中文Excel簡(jiǎn)介MicrosoftExcel是美國微軟公司開發(fā)的Windows環(huán)境下的電子表格系統(tǒng),它是目前應(yīng)用最為廣泛的辦公室表格處理軟件之一。自Excel誕生以來Excel歷經(jīng)了Excel5.0、Excel95、Excel97和Excel2000等不同版本。隨著版本的不斷提高,Excel軟件的強(qiáng)大的數(shù)據(jù)處理功能和操作的簡(jiǎn)易性逐漸走入了一個(gè)新的境界,整個(gè)系統(tǒng)的智能化程度也不斷提高,它甚至可以在某些方面判斷用戶的下一步操作,使用戶操作大為簡(jiǎn)化。Excel具有強(qiáng)有力的數(shù)據(jù)庫管理功能、豐富的宏命令和函數(shù)、強(qiáng)有力的決策支持工具、圖表繪制功能、宏語言功能、樣式功能、對(duì)象連接和嵌入功能、連接和合并功能,并且操作簡(jiǎn)捷,這些特性,已使Excel成為現(xiàn)代辦公軟件重要的組成部分。由于大家對(duì)Excel的常用辦公功能都比較熟悉,本實(shí)驗(yàn)重點(diǎn)介紹Excel在統(tǒng)計(jì)分析中的應(yīng)用。(二實(shí)驗(yàn)?zāi)康呐c要求本實(shí)驗(yàn)重點(diǎn)介紹Excel在統(tǒng)計(jì)分析中的應(yīng)用,包括Excel在描述統(tǒng)計(jì)中的應(yīng)用以及Excel在推斷統(tǒng)計(jì)中的應(yīng)用,要求學(xué)生熟練掌握運(yùn)用Excel進(jìn)行統(tǒng)計(jì)分析的方法,并能夠?qū)Ψ治鼋Y(jié)果進(jìn)行解釋。二、實(shí)驗(yàn)實(shí)驗(yàn)一Excel在描述統(tǒng)計(jì)中的應(yīng)用實(shí)驗(yàn)?zāi)康募耙笠髮W(xué)生掌握運(yùn)用Excel進(jìn)行描述統(tǒng)計(jì)分析、繪制各種圖表和運(yùn)用數(shù)據(jù)透視表工具的技術(shù)。實(shí)驗(yàn)內(nèi)容及步驟(一描述統(tǒng)計(jì)分析例1-1:表1-1是1978-2005年我國城鎮(zhèn)居民可支配收入數(shù)據(jù),試求城鎮(zhèn)居民可支配收入時(shí)間序列的基本統(tǒng)計(jì)量。表1-11978-2005年我國城鎮(zhèn)居民可支配收入(元STEP1:用鼠標(biāo)點(diǎn)擊工作表中待分析數(shù)據(jù)的任一單元格。STEP2:選擇“工具”菜單的“數(shù)據(jù)分析”子菜單。STEP3:用鼠標(biāo)雙擊數(shù)據(jù)分析工具中的“描述統(tǒng)計(jì)”選項(xiàng)。STEP4:出現(xiàn)“描述統(tǒng)計(jì)”對(duì)話框,如圖1-1所示。圖1-1對(duì)話框內(nèi)各選項(xiàng)的含義如下:輸入?yún)^(qū)域:在此輸入待分析數(shù)據(jù)區(qū)域的單元格范圍。分組方式:如果需要指出輸入?yún)^(qū)域中的數(shù)據(jù)是按行還是按列排列,則單擊“行”或“列”。標(biāo)志位于第一行/列:如果輸入?yún)^(qū)域的第一行中包含標(biāo)志項(xiàng)(變量名,則選中“標(biāo)志位于第一行”復(fù)選框;如果輸入?yún)^(qū)域的第一列中包含標(biāo)志項(xiàng),則選中“標(biāo)志位于第一列”。復(fù)選框:如果輸入?yún)^(qū)域沒有標(biāo)志項(xiàng),則不選任何復(fù)選框,Excel將在輸出表中生成適宜的數(shù)據(jù)標(biāo)志。均值置信度:若需要輸出由樣本均值推斷總體均值的置信區(qū)間,則選中此復(fù)選框,然后在右側(cè)的編輯框中,輸入所要使用的置信度。例如,置信度95%可計(jì)算出的總體樣本均值置信區(qū)間為10,則表示:在5%的顯著水平下總體均值的置信區(qū)間為(X-10,X+10。第K個(gè)最大/小值:如果需要在輸出表的某一行中包含每個(gè)區(qū)域的數(shù)據(jù)的第k個(gè)最大/小值,則選中此復(fù)選框。然后在右側(cè)的編輯框中,輸入k的數(shù)值。輸出區(qū)域:在此框中可填寫輸出結(jié)果表左上角單元格地址,用于控制輸出結(jié)果的存放位置。新工作表:單擊此選項(xiàng),可在當(dāng)前工作簿中插入新工作表,并由新工作表的A1單元格開始存放計(jì)算結(jié)果。如果需要給新工作表命名,則在右側(cè)編輯框中鍵入名稱。新工作簿:單擊此選項(xiàng),可創(chuàng)建一新工作簿,并在新工作簿的新工作表中存放計(jì)算結(jié)果。匯總統(tǒng)計(jì):指定輸出表中生成下列統(tǒng)計(jì)結(jié)果,則選中此復(fù)選框。STEP5:填寫完“描述統(tǒng)計(jì)”對(duì)話框之后,按“確定”按扭即可。結(jié)果如圖1-2所示。圖1-2描述統(tǒng)計(jì)結(jié)果結(jié)果說明:描述統(tǒng)計(jì)工具可生成以下統(tǒng)計(jì)指標(biāo),按從上到下的順序其中包括樣本的平均值、標(biāo)準(zhǔn)誤差、組中值、眾數(shù)、樣本標(biāo)準(zhǔn)差、樣本方差、峰度值、偏度值、極差、最小值、最大值、樣本總和、樣本個(gè)數(shù)和一定顯著水平下總體均值的置信區(qū)間。(二繪圖操作例1-2:仍以例1-1的數(shù)據(jù)為例,繪制城鎮(zhèn)居民可支配收入時(shí)間序列的散點(diǎn)圖。STEP1:拖動(dòng)鼠標(biāo)選定數(shù)值區(qū)域A2:C12,不包括數(shù)據(jù)上面的標(biāo)志項(xiàng)。STEP2:選擇“插入”菜單的“圖表”子菜單,進(jìn)入圖表向?qū)?圖1-3所示。STEP3:選擇“圖表類型”為“散點(diǎn)圖”,然后單擊“下一步”。圖1-3圖表向?qū)DSTEP4:確定用于制作圖表的數(shù)據(jù)區(qū)。Excel將自動(dòng)把你前面所選定的數(shù)據(jù)區(qū)的地址放入圖表數(shù)據(jù)區(qū)的內(nèi),單擊下一步按鈕,出現(xiàn)圖1-4所示對(duì)話框。圖1-4作圖過程圖STEP5:填寫圖表標(biāo)題、X軸坐標(biāo)名稱、Y軸坐標(biāo)名稱,此處不用填寫,單擊“下一步”。STEP6:選擇圖表輸出的位置,然后單擊“完成”按扭即生成(三數(shù)據(jù)透視表工具數(shù)據(jù)透視表是Excel中強(qiáng)有力的數(shù)據(jù)列表分析工具。它不僅可以用來作單變量數(shù)據(jù)的次數(shù)分布或總和分析,還可以用來作雙變量數(shù)據(jù)的交叉頻數(shù)分析、總和分析和其它統(tǒng)計(jì)量的分析。例1-3:表1-2列出了學(xué)生兩門功課評(píng)定結(jié)果,建立學(xué)生兩門功課評(píng)定結(jié)果的交叉頻數(shù)表。表1-2學(xué)生兩門功課評(píng)定結(jié)果操作步驟:STEP1:輸入數(shù)據(jù),選中有數(shù)據(jù)的任一單元格,然后選擇“數(shù)據(jù)”菜單的“數(shù)據(jù)透視表”子菜單,進(jìn)入數(shù)據(jù)透視表向?qū)?。STEP2:選擇“MicrosoftExcel數(shù)據(jù)清單或數(shù)據(jù)庫”為數(shù)據(jù)源。圖1-6所示,單擊“下一步”。圖1-6STEP3:選擇待分析的數(shù)據(jù)的區(qū)域,一般情況下Excel會(huì)自動(dòng)根據(jù)當(dāng)前單元格確定待分析數(shù)據(jù)區(qū)域,因此你只要直接單擊“下一步”按扭即可。STEP4:確定數(shù)據(jù)透視表的結(jié)構(gòu),在此例中,要建立的是一個(gè)交叉頻數(shù)表,分別按語文和數(shù)學(xué)的成績(jī)對(duì)學(xué)生的人數(shù)進(jìn)行交叉頻數(shù)分析,因此可將三個(gè)按扭“學(xué)號(hào)”、“語文”、“數(shù)學(xué)”分別拖放到表格的指定部位,并且雙擊“求和項(xiàng):學(xué)號(hào)”,將其改為記數(shù)項(xiàng),結(jié)果如圖1-7所示。圖1-7布局對(duì)話框STEP5:選擇數(shù)據(jù)透視表的顯示位置之后,單擊“完成按扭”,可出現(xiàn)如圖1-8所示的數(shù)據(jù)透視表。圖1-8結(jié)果說明:如圖1-8的結(jié)果所示,數(shù)據(jù)透視表可以作為一個(gè)交叉頻數(shù)分析工具。完成數(shù)據(jù)透視表之后,可按需要修改數(shù)據(jù)表的顯示格式。例如,如果想要把表格中的頻數(shù)替換成為百分比數(shù)?？梢杂檬髽?biāo)右擊頻數(shù)的任一單元格,選擇“字段”子菜單,單擊“選項(xiàng)”按扭,將“數(shù)據(jù)顯示方式”替換成為“占總和的百分比”,然后單擊“確定”按扭即可。按同樣方式,可將數(shù)據(jù)透視表修改成為其它不同樣式。實(shí)驗(yàn)二Excel中的二項(xiàng)分布工具實(shí)驗(yàn)?zāi)康募耙笳莆绽肊xcel的BINOMDIST的函數(shù)計(jì)算二項(xiàng)分布的概率以及累積概率。實(shí)驗(yàn)內(nèi)容及步驟例1-4:一個(gè)推銷員打了六個(gè)電話,推銷成功的概率是0.3,建立推銷成功次數(shù)的概率分布圖表?？梢园匆韵虏襟E建立推銷成功次數(shù)的概率分布圖表。STEP1:如圖1-9所示,先在Excel之下建立好概率分布表格的框架。圖1-9STEP2:如圖1-10所示,先在B7至F7單元格分別輸入概率計(jì)算公式。圖1-10STEP3:公式的拷貝。選取B7至F7單元格,拖動(dòng)“填充柄”至F13單元格即可完成公式的拷貝操作。結(jié)果圖1-11所示。圖1-11STEP4:下面開始創(chuàng)建二項(xiàng)分布圖表。選取B7至B13單元格,選取“插入”菜單的“圖表”子菜單。STEP5:選擇“柱狀圖”,然后單擊“下一步”。STEP6:單擊“系列”標(biāo)簽,單擊“分類(X軸標(biāo)志”框,并用鼠標(biāo)選取A7至A13單元格為圖表X軸的軸標(biāo),然后單擊“下一步”。STEP7:分別鍵入圖表名稱“二項(xiàng)分布圖”,X軸名稱“成功次數(shù)”,Y軸名稱“成功概率”,單擊“完成”按扭即可生成二項(xiàng)分布圖表。結(jié)果說明:如圖1-11所示,利用Excel的BINOMDIST的函數(shù)可以計(jì)算出二項(xiàng)分布的概率以及累積概率。BINOMDIST函數(shù)可以帶四個(gè)參數(shù),各參數(shù)的含義分別是:實(shí)驗(yàn)成功的次數(shù),實(shí)驗(yàn)的總次數(shù),每次實(shí)驗(yàn)中成功的概率,是否計(jì)算累積概率。四個(gè)參數(shù)是一個(gè)邏輯值,如果為TRUE,函數(shù)BINOMDIST返回累積分布函數(shù),如果為FALSE,返回概率密度函數(shù)。另外,EXCEL還提供了其它分布的函數(shù),如函數(shù)CRITBINOM;函數(shù)HYPGEOMDIST;函數(shù)NEGBINOMDIST:函數(shù)POISSON:正態(tài)分布函數(shù)NORMDIST:函數(shù)NORMSDIST:函數(shù)NORMSINV:t分布函數(shù)TDIST:有興趣的同學(xué)可以自己研究。實(shí)驗(yàn)三隨機(jī)抽樣工具實(shí)驗(yàn)?zāi)康募耙笳莆绽肊xcel的BINOMDIST的函數(shù)計(jì)算二項(xiàng)分布的概率以及累積概率。實(shí)驗(yàn)內(nèi)容及步驟Excel中的Rand(函數(shù)可以返回大于等于0小于1的均勻分布隨機(jī)數(shù),Rand(不帶任何參數(shù)運(yùn)行,每次計(jì)算時(shí)時(shí)都將返回一個(gè)新的數(shù)值。RAND(函數(shù)可以被用來作為不重復(fù)抽樣調(diào)查的工具。例1-5:如圖1-12所示有10個(gè)象征性的樣本數(shù)據(jù),欲從中隨機(jī)抽取5個(gè)數(shù)據(jù)可按如下步驟操作:圖1-12STEP1:選擇B2單元格,輸入公式“=RAND(”并回車。STEP2:拖動(dòng)B2單元格右下角的填充柄至B11單元格,并在B1單元格輸入標(biāo)題“RANDOM”。STEP3:選取單元格B2至B11,右擊選中的區(qū)域選擇“復(fù)制”,再次右擊選中的區(qū)域,選擇“選擇性粘貼”,單擊選項(xiàng)“數(shù)值”后,點(diǎn)擊“確定”按扭。STEP4:選取單元格A2至B11單元格,選擇“數(shù)據(jù)”菜單項(xiàng)下的排序子菜單。STEP5:選取“RANDOM”為主要關(guān)鍵字,然后點(diǎn)擊“確定”按扭。排序結(jié)果如圖1-13所示,A2至A6單元格的樣本即為隨機(jī)抽取的5個(gè)樣本。圖1-13實(shí)驗(yàn)四由樣本推斷總體實(shí)驗(yàn)?zāi)康募耙笳莆绽肊xcel的幾個(gè)函數(shù),如求平均函數(shù)AVERAGE、標(biāo)準(zhǔn)差函數(shù)STDEV、T分布函數(shù)TINV等的組合使用構(gòu)造一個(gè)專門用于實(shí)現(xiàn)樣本推斷總體的Excel工作表。實(shí)驗(yàn)內(nèi)容及步驟下面的例子先計(jì)算樣本的平均數(shù)和標(biāo)準(zhǔn)差,然后在一定置信水平上估計(jì)總體均值的區(qū)間范圍。操作步驟:STEP1:構(gòu)造工作表。如圖1-14所示,首先在各個(gè)單元格輸入以下的內(nèi)容,其中左邊是變量名,右邊是相應(yīng)的計(jì)算公式。STEP2:為表格右邊的公式計(jì)算結(jié)果定義左邊的變量名。選定A4:B6,A8:B8和A10:B15單元格(先選擇第一部分,再按住CTRL鍵選取另外兩個(gè)部分,選擇“插入”菜單的“名稱”子菜單的“指定”選項(xiàng),用鼠標(biāo)點(diǎn)擊“最左列”選項(xiàng),然后點(diǎn)擊“確定”按扭即可。圖1-14STEP3:輸入樣本數(shù)據(jù),和用戶指定的置信水平0.95,如圖附-13所示。STEP4:為樣本數(shù)據(jù)命名。選定D1:D11單元格,選擇“插入”菜單的“名稱”子菜單的“指定”選項(xiàng),用鼠標(biāo)點(diǎn)擊“首行”選項(xiàng),然后點(diǎn)擊“確定”按扭,得到圖1-15所示的計(jì)算結(jié)果。圖1-15結(jié)果說明:以上例子說明如何交叉組合使用Excel的公式和函數(shù),以構(gòu)造出一個(gè)能實(shí)現(xiàn)樣本推斷總體有關(guān)計(jì)算的Excel工作表。實(shí)際上,在用Excel進(jìn)行數(shù)據(jù)統(tǒng)計(jì)處理之時(shí),許多統(tǒng)計(jì)功能可以使用和上例類似的方法,通過組合使用Excel的各類統(tǒng)計(jì)函數(shù)和公式加以實(shí)現(xiàn)的。實(shí)驗(yàn)五假設(shè)檢驗(yàn)實(shí)驗(yàn)?zāi)康募耙笳莆绽肊xcel的正態(tài)分布函數(shù)NORMSDIST、判斷函數(shù)IF等,構(gòu)造一張能夠?qū)崿F(xiàn)在總體方差已知情況下進(jìn)行總體均值假設(shè)檢驗(yàn)的Excel工作表。實(shí)驗(yàn)內(nèi)容及步驟例1-6:利用Excel的正態(tài)分布函數(shù)NORMSDIST、判斷函數(shù)IF等,構(gòu)造一張能夠?qū)崿F(xiàn)在總體方差已知情況下進(jìn)行總體均值假設(shè)檢驗(yàn)的Excel工作表。操作步驟:STEP1:構(gòu)造工作表。如圖1-16所示,首先在各個(gè)單元格輸入以下的內(nèi)容,其中左邊是變量名,右邊是相應(yīng)的計(jì)算公式。STEP2:為表格右邊的公式計(jì)算結(jié)果定義左邊的變量名。選定A3:B4,A6:B8,A10:A11,A13:A15和A17:B19單元格,選擇“插入”菜單的“名稱”子菜單的“指定”選項(xiàng),用鼠標(biāo)點(diǎn)擊“最左列”選項(xiàng),然后點(diǎn)擊“確定”按扭即可。圖1-16STEP3:輸入樣本數(shù)據(jù),以及總體標(biāo)準(zhǔn)差、總體均值假設(shè)、置信水平數(shù)據(jù)。如圖1-17所示。STEP4:為樣本數(shù)據(jù)命名。選定C1:C11單元格,選擇“插入”菜單的“名稱”子菜單的“指定”選項(xiàng),用鼠標(biāo)點(diǎn)擊“首行”選項(xiàng),然后點(diǎn)擊“確定”按扭,得到如圖1-17中所示的計(jì)算結(jié)果。圖1-17結(jié)果說明:如圖1-17所示,該例子的檢驗(yàn)結(jié)果不論是單側(cè)還是雙側(cè)均為拒絕Ho假設(shè)。所以,根據(jù)樣本的計(jì)算結(jié)果,在5%的顯著水平之下,拒絕總體均值為35的假設(shè)。同時(shí)由單側(cè)顯著水平的計(jì)算結(jié)果還可以看出,在總體均值是35的假設(shè)之下,樣本均值小于等于31.4的概率僅為0.。實(shí)驗(yàn)六雙樣本等均值假設(shè)檢驗(yàn)實(shí)驗(yàn)?zāi)康募耙笳莆绽肊xcel數(shù)據(jù)分析中提供雙樣本等均值假設(shè)檢驗(yàn)工具進(jìn)行假設(shè)檢驗(yàn)的方法,并能夠解釋實(shí)驗(yàn)結(jié)果。實(shí)驗(yàn)內(nèi)容及步驟例1-7:雙樣本等均值檢驗(yàn)是在一定置信水平之下,在兩個(gè)總體方差相等的假設(shè)之下,檢驗(yàn)兩個(gè)總體均值的差值等于指定平均差的假設(shè)是否成立的檢驗(yàn)。假設(shè)某工廠為了比較兩種裝配方法的效率,分別組織了兩組員工,每組9人,一組采用新的裝配方法,另外一組采用舊的裝配方法。18個(gè)員工的設(shè)備裝配時(shí)間圖1-18中表格所示。根據(jù)以下數(shù)據(jù),是否有理由認(rèn)為新的裝配方法更節(jié)約時(shí)間?圖1-18操作步驟:STEP1:選擇“工具”菜單的“數(shù)據(jù)分析”子菜單,雙擊“t-檢驗(yàn):雙樣本等方差假設(shè)”選項(xiàng),則彈出圖1-19所示對(duì)話框。圖1-19STEP2:分別填寫變量1的區(qū)域:$B$1:$B$10,變量2的區(qū)域:$D$1:$D$10,由于我們進(jìn)行的是等均值的檢驗(yàn),填寫假設(shè)平均差為0,由于數(shù)據(jù)的首行包括標(biāo)志項(xiàng)選擇標(biāo)志選項(xiàng),所以選擇“標(biāo)志”選項(xiàng),再填寫顯著水平α為0.05,然后點(diǎn)擊“確定”按扭。則可以得到圖1-20所示的結(jié)果。圖1-20結(jié)果分析:如圖1-20中所示,表中分別給出了兩組裝配時(shí)間的平均值、方差和樣本個(gè)數(shù)。其中,合并方差是樣本方差加權(quán)之后的平均值,Df是假設(shè)檢驗(yàn)的自由度它等于樣本總個(gè)數(shù)減2,t統(tǒng)計(jì)量是兩個(gè)樣本差值減去假設(shè)平均差之后再除于標(biāo)準(zhǔn)誤差的結(jié)果,“P(T<=t單尾”是單尾檢驗(yàn)的顯著水平,“t單尾臨界”是單尾檢驗(yàn)t的臨界值,“P(T<=t雙尾”是雙尾檢驗(yàn)的顯著水平,“t雙尾臨界”是雙尾檢驗(yàn)t的臨界值。由下表的結(jié)果可以看出t統(tǒng)計(jì)量均小于兩個(gè)臨界值,所以,在5%顯著水平下,不能拒絕兩個(gè)總體均值相等的假設(shè),即兩種裝配方法所耗時(shí)間沒有顯著的不同。Excel中還提供了以下類似的假設(shè)檢驗(yàn)的數(shù)據(jù)分析工具,它們的名稱和作用如下:“t-檢驗(yàn):雙樣本異方差假設(shè)”“t-檢驗(yàn):成對(duì)雙樣本均值分析”“z-檢驗(yàn):雙樣本均值分析”實(shí)驗(yàn)七正態(tài)性的卡方檢驗(yàn)實(shí)驗(yàn)?zāi)康募耙笳莆绽肊xcel進(jìn)行總體卡方分布檢驗(yàn)、總體泊松分布、總體超幾何分布等其它分布的檢驗(yàn)的方法步驟,并能夠解釋實(shí)驗(yàn)結(jié)果。實(shí)驗(yàn)內(nèi)容及步驟卡方檢驗(yàn)檢驗(yàn)可以用來判斷所觀測(cè)的樣本是否來自某一特定分布的總體,這種檢驗(yàn)亦稱為一致性檢驗(yàn)。例1-8:已知某樣本的相關(guān)統(tǒng)計(jì)量和分組頻數(shù)分布如圖1-21所示,試用卡方檢驗(yàn)判斷該樣本是否來自一正態(tài)總體。圖1-21操作步驟:STEP1:創(chuàng)建變量名。選定A3:C4單元格,選擇“插入”菜單的“名稱”子菜單的“指定”選項(xiàng),用鼠標(biāo)點(diǎn)擊“首行”選項(xiàng),然后點(diǎn)擊“確定”按扭即可。STEP2:計(jì)算預(yù)期正態(tài)概率值。如圖1-22表中所示,在D6單元格輸入標(biāo)志項(xiàng),在D7:D11單元格輸入公式,分別計(jì)算各組的預(yù)期正態(tài)概率值,在D12計(jì)算累積概率值。圖1-22STEP3:計(jì)算預(yù)期頻數(shù)值。如圖1-23所示,在E6單元格輸入標(biāo)志項(xiàng),在E7:E11單元格輸入公式,分別計(jì)算各組的預(yù)期頻數(shù),在E12計(jì)算累積頻數(shù)值。圖1-23STEP4:計(jì)算X2統(tǒng)計(jì)量。如圖1-24所示,在F6單元格輸入標(biāo)志項(xiàng),在F7:F11分別輸入計(jì)算公式,分別計(jì)算X2值,在E12計(jì)算X2平方和,這項(xiàng)就是最后計(jì)算出的X2統(tǒng)計(jì)量。在E13單元格輸入標(biāo)志項(xiàng)“卡方統(tǒng)計(jì)量”,為以后的引用作準(zhǔn)備。先選中F12、F13兩個(gè)單元格,選擇“插入”菜單的“名稱”子菜單的“指定”選項(xiàng),用鼠標(biāo)點(diǎn)擊“尾行”選項(xiàng),然后點(diǎn)擊“確定”按扭即可。圖1-24STEP5:如圖1-25所示,分別在A14到B20單元格輸入自由度、X2概率值、置信水平、臨界值、X2檢驗(yàn)結(jié)果幾項(xiàng)的標(biāo)志值及計(jì)算公式。其中的自由度=區(qū)間分段數(shù)-正態(tài)分布參數(shù)個(gè)數(shù)-1=5-2-1=2。圖1-25STEP6:結(jié)果如圖1-26所示。圖1-26結(jié)果分析:如圖1-26所示,按照以上操作步驟可以得到表中的計(jì)算結(jié)果。按同樣的方法可以作總體泊松分布、總體超幾何分布等其它分布的檢驗(yàn)。此類統(tǒng)計(jì)應(yīng)用也是由Excel各類公式和函數(shù)綜合使用而實(shí)現(xiàn)的,為了以后使用方便,和上面的一些例子一樣,一般需要將整個(gè)表格的計(jì)算框架和標(biāo)志項(xiàng)羅列好,再保存成文件,以后只要對(duì)數(shù)據(jù)項(xiàng)稍作修改即可很快得到計(jì)算結(jié)果。如果對(duì)Excel宏語言較為熟悉,還可以將它編成一個(gè)宏語言程序,加入Excel的工具欄,這樣以后使用起來更為方便。實(shí)驗(yàn)八列聯(lián)表分析實(shí)驗(yàn)?zāi)康募耙罅新?lián)表分析經(jīng)常用來判斷同一個(gè)調(diào)查的對(duì)象的兩個(gè)特性之間是否存在明顯相關(guān)關(guān)系。該實(shí)驗(yàn)要求學(xué)生掌握利用Excel實(shí)現(xiàn)列聯(lián)表分析的方法步驟,并能夠解釋實(shí)驗(yàn)結(jié)果。實(shí)驗(yàn)內(nèi)容及步驟例1-9:如圖1-27所示,表中是某裝修公司的調(diào)查報(bào)告,試用列聯(lián)表分析方法分析在顧客的所在地區(qū)和所選房子的地板類型之間是否存在明顯的相關(guān)關(guān)系。圖1-27操作步驟:STEP1:建立期望頻數(shù)表。如圖1-28所示,先建立期望頻數(shù)表的框架,然后在B10單元格輸入公式“=B$7*$F4/$F$7”,再利用“填充柄”將公式復(fù)制到表格的其它單元格,最后利用Excel的求和函數(shù)sum計(jì)算行和與列和。圖1-28STEP2:計(jì)算X2概率值。在A15單元格輸入標(biāo)志項(xiàng)“卡方概率值”,先點(diǎn)擊B15單元格,從“插入”菜單中“函數(shù)”子菜單,選擇“統(tǒng)計(jì)函數(shù)”中的“CHITEST”函數(shù),單擊“確定按扭,然后在彈出的對(duì)話框中分別添入實(shí)際頻數(shù)范圍“B4:E6”和預(yù)期頻數(shù)范圍“B10:E12”。最后單擊“確定”按扭即可得到計(jì)算結(jié)果1.3E-07,如圖1-29所示。圖1-29STEP3:建立X2統(tǒng)計(jì)表。如圖1-30所示,先建立表格的框架,然后在B20單元格輸入公式“=(B4-B10^2/B10”,再利用填充柄將公式復(fù)制到表格的其它單元格。最后計(jì)算X2卡方統(tǒng)計(jì)量,分別在A24與B24單元輸入標(biāo)志項(xiàng)與計(jì)算公式。圖1-30STEP4:進(jìn)行假設(shè)檢驗(yàn)。如圖1-31所示,分別輸入置信水平、臨界值和假設(shè)檢驗(yàn)的結(jié)果其中CHIINV函數(shù)的自由度=(第一類屬性的分類數(shù)-1*(第二類屬性的分類數(shù)-1=(3-1*(4-1=6。圖1-31結(jié)果分析:以上的操作步驟完成整個(gè)列聯(lián)表的分析。其中,B15單元格的卡方概率值與B24單元格的卡方統(tǒng)計(jì)量是表格的兩個(gè)重要計(jì)算結(jié)果。其中卡方概率值等于1.3E-07表明:如果總體的兩類屬性,即所在地區(qū)和所選地板類型,是不相關(guān)的,那么得到以上觀察的樣本的概率是0.。這個(gè)概率幾乎接近于0,所以可以認(rèn)為總體的這兩個(gè)屬性是顯著相關(guān)的。實(shí)驗(yàn)九單因素方差分析實(shí)驗(yàn)?zāi)康募耙笳莆绽肊xcel進(jìn)行單因素方差分析、無重復(fù)雙因素方差分析、有重復(fù)雙因素方差分析的步驟和方法,并能夠解釋實(shí)驗(yàn)結(jié)果。實(shí)驗(yàn)內(nèi)容及步驟單因素方差分析可用于檢驗(yàn)兩個(gè)或兩個(gè)以上的總體均值相等的假設(shè)是否成立。此方法是對(duì)雙均值檢驗(yàn)(如t-檢驗(yàn)的擴(kuò)充。檢驗(yàn)假定總體是服從正太分布的,總體方差是相等的,并且隨機(jī)樣本是獨(dú)立的。這種工具適用于完全隨機(jī)化試驗(yàn)的結(jié)果分析。例1-10:如圖1-32中所示,一產(chǎn)品制造商雇傭銷售人員向銷售商打電話。制造商想比較四種不同電話頻率計(jì)劃的效率,他從銷售人員中隨機(jī)選出32名,將他們隨機(jī)分配到4種計(jì)劃中,在一段時(shí)期內(nèi)記錄他們的銷售情況已經(jīng)在表中列出,試問其中是否有一種計(jì)劃會(huì)帶來較高的銷售水平。圖1-32操作步驟:STEP1:選擇“工具”菜單的“數(shù)據(jù)分析”子菜單,雙擊“方差分析:單因素方差分析”選項(xiàng),彈出單因素方差分析對(duì)話框。STEP2:按圖1-33所示方式填寫對(duì)話框。圖1-33STEP3:單擊“確定”按扭,得到圖1-34的計(jì)算結(jié)果。圖1-34結(jié)果分析:圖1-34中表格的第二部分則是方差分析的結(jié)果。SS列分別給出了四個(gè)分組的組間方差、組內(nèi)方差以及總方差,DF列分別給出了對(duì)應(yīng)方差的自由度,MS列是平均值方差,由SS除于DF得到,它是總體方差的兩個(gè)估計(jì)值。F列是F統(tǒng)計(jì)量的計(jì)算結(jié)果,如果四個(gè)總體均值相等的假設(shè)成立的化,它應(yīng)該服從F分布,即近似為1,它是最終的計(jì)算結(jié)果,通過將它與一定置信水平下的F臨界值Fcrit比較,可以判斷均值相等的假設(shè)是否成立,在本例中,1.67761小于第二步0.94668,所以不能拒絕四個(gè)總體均值相等的假設(shè)。P-value列,是單尾概率值,表明如果四個(gè)總體均值相等的假設(shè)成立的化,得到如上樣本結(jié)果的概率是19.442%,即得到以上樣本并不是小概率事件,同樣也得到不能拒絕四個(gè)總體均值相等的假設(shè)的結(jié)論。按相似方法可進(jìn)行無重復(fù)雙因素方差分析,有重復(fù)雙因素方差分析。實(shí)驗(yàn)十線性回歸分析實(shí)驗(yàn)?zāi)康募耙笳莆绽肊xcel計(jì)算相關(guān)系數(shù)、進(jìn)行線性回歸分析的步驟和方法,并能夠解釋實(shí)驗(yàn)結(jié)果。實(shí)驗(yàn)內(nèi)容及步驟(一線性回歸分析線性回歸分析通過對(duì)一組觀察值使用“最小二乘法”直線擬合,用來分析單個(gè)因變量是如何受一個(gè)或幾個(gè)自變量影響的。例1-11:如圖1-35所示,我國1987至1997年布匹的人均產(chǎn)量和人均紗產(chǎn)量,試用線性回歸分析的方法分析兩組數(shù)據(jù)之間的關(guān)系,并計(jì)算二者之間的相關(guān)系數(shù)。圖1-35操作步驟:STEP1:選擇“工具”菜單的“數(shù)據(jù)分析”子菜單,雙擊“回歸”選項(xiàng),彈出回歸分析對(duì)話框。STEP2:按如下方式填寫對(duì)話框:X值輸入?yún)^(qū)域?yàn)?B$1:$B$12,Y值輸入?yún)^(qū)域?yàn)?C$1:$c$12,并選擇“標(biāo)志”和“線性擬合圖”兩個(gè)復(fù)選框STEP3:單擊“確定”按扭即可,結(jié)果如圖1-36所示。圖1-36結(jié)果分析:結(jié)果可以分為四個(gè)部分,第一部分是回歸統(tǒng)計(jì)的結(jié)果包括多元相關(guān)系數(shù)、可決系數(shù)R2、調(diào)整之后的相關(guān)系數(shù)、回歸標(biāo)準(zhǔn)差以及樣本個(gè)數(shù)。第二部分是方差分析的結(jié)果包括可解釋的離差、殘差、總離差和它們的自由度以及由此計(jì)算出的F統(tǒng)計(jì)量和相應(yīng)的顯著水平。第三部分是回歸方程的截距和斜率的估計(jì)值以及它們的估計(jì)標(biāo)準(zhǔn)誤差、t統(tǒng)計(jì)量大小雙邊拖尾概率值、以及估計(jì)值的上下界。根據(jù)這部分的結(jié)果可知回歸方程為Y=8.46433*X-18.288。第四部分是樣本散點(diǎn)圖,其中藍(lán)色的點(diǎn)是樣本的真實(shí)散點(diǎn)圖,紅色的點(diǎn)是根據(jù)回歸方程進(jìn)行樣本歷史模擬的散點(diǎn)。如果覺得散點(diǎn)圖不夠清晰可以用鼠標(biāo)拖動(dòng)圖形的邊界達(dá)到控制圖形大小的目的。用相同的方法可以進(jìn)行多元線性方程的參數(shù)估計(jì),還可以在自變量中引入虛擬變量以增加方程的擬合程度。對(duì)于非線性的方程的參數(shù)估計(jì),可以在進(jìn)行樣本數(shù)據(jù)的線性化處理之后,再按以上步驟進(jìn)行參數(shù)估計(jì)。(二相關(guān)系數(shù)計(jì)算實(shí)驗(yàn)內(nèi)容及步驟STEP1:用鼠標(biāo)點(diǎn)擊表中待分析數(shù)據(jù)的任一單元格。STEP2:選擇“工具”菜單的“數(shù)據(jù)分析”子菜單。STEP2:用鼠標(biāo)雙擊數(shù)據(jù)分析工具中的“相關(guān)系數(shù)”選項(xiàng)。STEP4:填寫完“相關(guān)系數(shù)”對(duì)話框,單擊“確定”按扭即可得到各個(gè)變量的相關(guān)系數(shù)矩陣,結(jié)果如圖1-37所示。圖1-37結(jié)果說明:圖1-37下三角矩陣計(jì)算出三個(gè)變量x,y,z兩兩之間的相關(guān)系數(shù),如變量x,y之間的相關(guān)系數(shù)為:0.,所以可以判斷x,y之間存在著較高的正線性相關(guān)關(guān)系。實(shí)驗(yàn)十二自回歸模型的識(shí)別與估計(jì)實(shí)驗(yàn)?zāi)康募耙笳莆绽肊xcel對(duì)時(shí)間序列的自回歸模型進(jìn)行識(shí)別與估計(jì)的步驟和方法,并能夠解釋實(shí)驗(yàn)結(jié)果。實(shí)驗(yàn)內(nèi)容及步驟例1-12:如圖1-38所示是自1999年4月1日起的20個(gè)交易日內(nèi)的上證指數(shù)的時(shí)間序列,試用自回歸模型加以擬合。圖1-38操作步驟:STEP1:數(shù)據(jù)的零均值化處理。如圖1-39中所示,在C1中輸入序列名“Z”,在C2中輸入公式“=上證指數(shù)-AVERAGE(上證指數(shù)”,然后在C2單元格中,拖動(dòng)Excel“填充柄”將公式復(fù)制到C3至C22單元格,即可生成上證指數(shù)的零均值化序列。STEP2:計(jì)算自相關(guān)函數(shù)。在E1和F1單元格分別輸入標(biāo)志項(xiàng)Lag和ac,在E2到E9單元格中分別輸入置后期數(shù)1至8。在F2單元格輸入計(jì)算自相關(guān)函數(shù)的公式“=SUMPRODUCT(OFFSET(C$2,0,0,20-E2,OFFSET(C3,0,0,20-E2/VAR($C$2:$C$21/19”,然后利用“填充柄”將F2單元格公式復(fù)制到F3:F9單元格,結(jié)果如圖1-40所示。圖1-39STEP3:計(jì)算偏自相關(guān)函數(shù)。計(jì)算偏自相關(guān)函數(shù)的步驟較為復(fù)雜,必須利用Excel的逆矩陣等函數(shù)求解Yule-Walker方程組,由于我們選擇了置后期數(shù)為8,為了求解偏自相關(guān)函數(shù),我們必須求解8個(gè)Yule-Walker方程組。首先,利用自相關(guān)函數(shù)的計(jì)算結(jié)果,填寫H2:O9范圍內(nèi)的對(duì)稱矩陣如圖1-40中H2:O9單元格所示。其次,利用Excel數(shù)組公式分別求解8個(gè)方程組的結(jié)果,結(jié)果分別放在φ1i至φ8i的八列之中,第一個(gè)方程組的結(jié)果放在H12中,第二個(gè)方程組的結(jié)果放在I12:I13中,第三個(gè)方程組的結(jié)果放在J12:J14中,以此類推。所輸入的8個(gè)數(shù)組公式分別為:“MMULT(MINVERSE(OFFSET(H2,0,0,1,1,OFFSET(F2,0,0,1”,“MMULT(MINVERSE(OFFSET(H2,0,0,2,2,OFFSET(F2,0,0,2”,“MMULT(MINVERSE(OFFSET(H2,0,0,3,3,OFFSET(F2,0,0,3”,“MMULT(MINVERSE(OFFSET(H2,0,0,4,4,OFFSET(F2,0,0,4”,“MMULT(MINVERSE(OFFSET(H2,0,0,5,5,OFFSET(F2,0,0,5”,“MMULT(MINVERSE(OFFSET(H2,0,0,6,6,OFFSET(F2,0,0,6”,“MMULT(MINVERSE(OFFSET(H2,0,0,7,7,OFFSET(F2,0,0,7”,“MMULT(MINVERSE(OFFSET(H2,0,0,8,8,OFFSET(F2,0,0,8”。(說明1.在Excel中輸入數(shù)組公式時(shí),先用鼠標(biāo)選定所有需放置結(jié)果的單元格地址范圍然后輸入數(shù)組公式,例如“=MMULT(MINVERSE(OFFSET(H2,0,0,2,2,OFFSET(F2,0,0,2”,然后同時(shí)按下“CTRL+SHIFT+回車”三個(gè)按鍵,完成數(shù)組公式的輸入,公式會(huì)自動(dòng)加上一對(duì)大括號(hào),它由Excel自動(dòng)添入。STEP2:以上數(shù)組公式中包含的各個(gè)函數(shù)的含義及其用法請(qǐng)參看附表1。最后,將每一個(gè)方程組的最后一個(gè)解,用值復(fù)制的方式復(fù)制到pac這一列,即可得到8個(gè)偏自相關(guān)系數(shù)。如圖附-39,表中H12:O19單元格的8列分別給出了8個(gè)數(shù)組公式計(jì)算的結(jié)果,F12:F19單元格的內(nèi)容即是所要求解的8個(gè)偏自相關(guān)系數(shù)。圖1-40STEP4:模型的識(shí)別與估計(jì)。自相關(guān)函數(shù)序列呈明顯拖尾性,偏自相關(guān)函數(shù)序列在k>1之后,都在區(qū)間(-0.438,0.438之間,因此可以認(rèn)為自相關(guān)函數(shù)在K>1之后截尾,因此我們選用AR(1模型進(jìn)行數(shù)據(jù)擬合。復(fù)制C2:C20的數(shù)據(jù),將之以值復(fù)制的形式復(fù)制到D3:D21的單元格,并在D1中填入標(biāo)志項(xiàng)“Z(-1”。選擇“工具”菜單的“數(shù)據(jù)分析”子菜單,雙擊“回歸”選項(xiàng),彈出回歸分析對(duì)話框。按圖附-40所示的方式填寫對(duì)話框。然后單擊“確定”按扭,即可得到AR(1模型的估計(jì)結(jié)果。STEP5:按以上操作步驟,可得到圖1-41所示AR(1模型。圖1-41結(jié)果分析:零均值化模型的估計(jì)結(jié)果是Z=1.06284*Z(-1,還原成上證指數(shù),最終的時(shí)間序列模型是:上證指數(shù)估計(jì)值-上證指數(shù)的平均值=1.06284(上一天上證指數(shù)-上證指數(shù)平均值。Excel與數(shù)據(jù)統(tǒng)計(jì)分析統(tǒng)計(jì)計(jì)算與統(tǒng)計(jì)分析強(qiáng)調(diào)與計(jì)算機(jī)密切結(jié)合,《Excel與數(shù)據(jù)統(tǒng)計(jì)分析》旨在提高學(xué)生計(jì)算機(jī)的綜合運(yùn)用能力,用統(tǒng)計(jì)方法分析問題、解決問題而編寫的。根據(jù)教材內(nèi)容,也可以選擇使用SPSS、QSTAT、Evievs、SAS、MINITAB等統(tǒng)計(jì)軟件。第三章統(tǒng)計(jì)整理3.1計(jì)量數(shù)據(jù)的頻數(shù)表與直方圖例3.1(3-1一、指定接受區(qū)域直方圖在應(yīng)用此工具前,用戶應(yīng)先決定分布區(qū)間。否則,Excel將用一個(gè)大約等于數(shù)據(jù)集中某數(shù)值的平方根作區(qū)間,在數(shù)據(jù)集的最大值與最小值之間用等寬間隔。如果用戶自己定義區(qū)間,可用2、5或10的倍數(shù),這樣易于分析。對(duì)于工資數(shù)據(jù),最小值是100,最大值是298。一個(gè)緊湊的直方圖可從區(qū)間100開始,區(qū)間寬度用10,最后一區(qū)間為300結(jié)束,需要21個(gè)區(qū)間。這里所用的方法在兩端加了一個(gè)空區(qū)間,在低端是區(qū)間“100或小于100”,高端是區(qū)間“大于300”。參考圖3.3,利用下面這些步驟可得到頻率分布和直方圖:1.為了方便,將原始數(shù)據(jù)拷貝到新工作表“指定頻數(shù)直方圖”中。2.在B1單元中輸入“組距”作為一標(biāo)記,在B2單元中輸入100,B3單元中輸入110,選取B2:B3,向下拖動(dòng)所選區(qū)域右下角的+到B22單元。3.按下列步驟使用“直方圖”分析工具:(1,在分析工具框中“直方圖”。如圖4所示。圖3.1數(shù)據(jù)分析工具之直方圖對(duì)話框1輸入輸入?yún)^(qū)域:A1:A51接受區(qū)域:B1:B22(這些區(qū)間斷點(diǎn)或界限必須按升序排列選擇標(biāo)志