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

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 軟件的強(qiáng)大的數(shù)據(jù)處理功能和操作的簡易性逐 漸走入了一個新的境界，整個系統(tǒng)的智能化程度也不斷提高，它甚至可以在某些方面判斷用戶的下一步操作，使用戶操作大為簡化。 Excel 具有強(qiáng)有力的數(shù)據(jù)庫管理功能、豐富的宏命令和函數(shù)、強(qiáng)有力的決策支持工具、圖表繪制功能、宏語言功能、樣式功能、對象連接和嵌入功能、連接和合并功能，并且操作簡捷，這些特性，已使 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 2 進(jìn)行統(tǒng)計分析的方法，并能夠?qū)Ψ治鼋Y(jié)果進(jìn)行解釋。 二、實驗 實驗一 Excel 在描述統(tǒng)計中的應(yīng)用 實驗?zāi)康募耙?要求學(xué)生掌握運用 Excel進(jìn)行描述統(tǒng)計分析、繪制各種圖表和運用數(shù)據(jù)透視表工具的技術(shù)。 實驗內(nèi)容及步驟 （一）描述統(tǒng)計分析 例 1-1：表 1-1是 1978-2005年我國城鎮(zhèn)居民可支配收入數(shù)據(jù)，試求城鎮(zhèn)居民可支配收入時間序列的基本統(tǒng)計量。 表 1-1 1978-2005年我國城鎮(zhèn)居民可支配收入 （元） 年份 城鎮(zhèn)居民可 支配收入 年份 城鎮(zhèn)居民可 支配收入 1978 344 1992 2026.6 1979 405 1993 2577.4 1980 477.6 1994 3496.2 1981 500.4 1995 4283 1982 535.3 1996 4838.9 1983 564.6 1997 5160.3 3 1984 652.1 1998 5425.1 1985 739.1 1999 5854 1986 899.6 2000 6280 1987 1002.2 2001 6859.6 1988 1181.4 2002 7702.8 1989 1379 2003 8472.2 1990 1510.2 2004 9421.6 1991 1700.6 2005 10493 STEP1：用鼠標(biāo)點擊工作表中待分析數(shù)據(jù)的任一單元格。 STEP2：選擇“工具”菜單的“數(shù)據(jù)分析”子菜單。 STEP3：用鼠標(biāo)雙擊數(shù)據(jù)分析工具中的“描述統(tǒng)計”選項 。 STEP4：出現(xiàn)“描述統(tǒng)計”對話框，如圖 1-1所示。 4 圖 1-1 對話框內(nèi)各選項的含義如下： 輸入?yún)^(qū)域：在此輸入待分析數(shù)據(jù)區(qū)域的單元格范圍。 分組方式：如果需要指出輸入?yún)^(qū)域中的數(shù)據(jù)是按行還是按列排列，則單擊“行”或“列”。 標(biāo)志位于第一行 /列：如果輸入?yún)^(qū)域的第一行中包含標(biāo)志項 (變量名 )，則選中“標(biāo)志位于第一行”復(fù)選框；如果輸入?yún)^(qū)域的第一列中包含標(biāo)志項，則選中“標(biāo)志位于第一列”。 復(fù)選框：如果輸入?yún)^(qū)域沒有標(biāo)志項，則不選任何復(fù)選框， Excel 將在輸出表中生成適宜的數(shù)據(jù)標(biāo)志。 均值置信度：若 需要輸出由樣本均值推斷總體均值的置信區(qū)間，則選中此復(fù)選框，然后在右側(cè)的編輯框中，輸入所要使用的置信度。例如，置 5 信度 95%可計算出的總體樣本均值置信區(qū)間為 10，則表示：在 5%的顯著水平下總體均值的置信區(qū)間為 ( X -10, X +10）。 第 K 個最大 /小值：如果需要在輸出表的某一行中包含每個區(qū)域 的數(shù)據(jù)的第 k 個最大 /小值，則選中此復(fù)選框。然后在右側(cè)的編輯框中，輸入 k 的數(shù)值。 輸出區(qū)域：在此框中可填寫輸出結(jié)果表左上角單元格地址 ,用于控 制輸出結(jié)果的存放位置。 新工作表：單擊此選項，可在 當(dāng)前工作簿中插入新工作表，并由新工作表的 A1 單元格開始存放計算結(jié)果。如果需要給新工作表命名，則在右側(cè)編輯框中鍵入名稱。 新工作簿：單擊此選項，可創(chuàng)建一新工作簿，并在新工作簿的新工作表中存放計算結(jié)果。 匯總統(tǒng)計：指定輸出表中生成下列統(tǒng)計結(jié)果，則選中此復(fù)選框。 STEP5：填寫完“描述統(tǒng)計”對話框之后，按“確定”按扭即可。結(jié)果如圖 1-2所示。 6 圖 1-2 描述統(tǒng)計結(jié)果 結(jié)果說明：描述統(tǒng)計工具可生成以下統(tǒng)計指標(biāo)，按從上到下的順序其中包括樣本的平均值、標(biāo)準(zhǔn)誤差、組中值、眾數(shù)、 樣本標(biāo)準(zhǔn)差、樣本方差、 峰度值、偏 度值、極差、最小值、最大值、樣本總和、樣本個數(shù)和一定顯著水平下總體均值的置信區(qū)間。 （二）繪圖操作 例 1-2：仍以例 1-1的數(shù)據(jù)為例，繪制城鎮(zhèn)居民可支配收入時間序列的散點圖。 STEP1：拖動鼠標(biāo)選定數(shù)值區(qū)域 A2:C12，不包括數(shù)據(jù)上面的標(biāo)志項。 7 STEP2：選擇“插入”菜單的“圖表”子菜單，進(jìn)入圖表向?qū)?，圖 1-3所示。 STEP3：選擇“圖表類型”為“散點圖”，然后單擊“下一步”。 圖 1-3 圖表向?qū)D STEP4：確定用于制作圖表的數(shù)據(jù)區(qū)。 Excel 將自動把你前面所選 定的數(shù)據(jù)區(qū)的地址放入圖表數(shù)據(jù)區(qū)的內(nèi)，單擊下一步按鈕，出現(xiàn)圖 1-4所示對話框。 8 圖 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)計量的分析。 例 1-3：表 1-2列出了學(xué)生兩門功課評定結(jié)果，建立學(xué)生 兩門功課評定結(jié)果的交叉頻數(shù)表。 表 1-2 學(xué)生兩門功課評定結(jié)果 9 學(xué)號 語文 數(shù)學(xué) 學(xué)號 語文 數(shù)學(xué) 1001 優(yōu) 差 1006 中 良 1002 良 中 1007 中 優(yōu) 1003 中 中 1008 差 良 1004 差 中 1009 良 中 1005 差 差 操作步驟： STEP1： 輸入數(shù)據(jù)，選中有數(shù)據(jù)的任一單元格，然后選擇“數(shù)據(jù)”菜單的“數(shù)據(jù)透視表”子菜單，進(jìn)入數(shù)據(jù)透視表向?qū)А?STEP2： 選擇“ Microsoft Excel 數(shù)據(jù)清單或數(shù)據(jù)庫”為數(shù)據(jù)源。圖 1-6所示，單擊“下一步”。圖 1-6 10 STEP3： 選擇待分析的數(shù)據(jù)的區(qū)域，一般情況下 Excel 會自動根據(jù)當(dāng)前單元格確定待分析數(shù)據(jù)區(qū)域，因此你只要直接單擊“下一步”按扭即可。 STEP4： 確定數(shù)據(jù)透視表的結(jié)構(gòu)，在此例中，要建立的是一個交叉頻數(shù)表，分別按語文和數(shù)學(xué)的成績對學(xué)生的人數(shù)進(jìn)行交叉頻數(shù)分析，因此可將三個按扭“學(xué)號”、“語文”、“數(shù)學(xué)”分別拖放到表格的指定部位，并且雙擊“求和項：學(xué)號”，將其改為記數(shù)項，結(jié)果如圖 1-7所示。 圖 1-7 布局對話框 STEP5：選擇數(shù)據(jù)透視表的顯示位置之后，單擊“完成按扭”，可出現(xiàn)如圖 1-8所示的數(shù)據(jù)透視表。 11 圖 1-8 結(jié)果說明：如圖 1-8的結(jié)果所示，數(shù)據(jù)透視表可以作為一個交叉頻數(shù)分析工具。 完成數(shù)據(jù)透視表之后，可按需要修改數(shù)據(jù)表的顯示格式。例如，如果想要把表格中的頻數(shù)替換成為百分比數(shù)。可以用鼠標(biāo)右擊頻數(shù)的任一單元格，選擇“字段”子菜單，單擊“選項”按扭，將“數(shù)據(jù)顯示方式”替換成為“占總和的百分比”，然后單擊“確定”按扭即可。按同樣方式，可將數(shù)據(jù)透視表修改成為其它不同樣式。 12 實驗二 Excel中的二項分布工具 實驗?zāi)康募耙?掌握利用 Excel的 BINOMDIST 的函數(shù)計算二項分布的概率以及累積概率。 實驗內(nèi)容及步驟 例 1-4：一個推銷員打了六個電話，推銷成功的概率是 0.3，建立推銷成功次數(shù)的概率分布圖表。可以按以下步驟建立推銷成功次數(shù)的概率分布圖表。 STEP1： 如圖 1-9所示，先在 Excel之下建立好概率分布表格的框架。 圖 1-9 STEP2：如圖 1-10所示 ,先在 B7 至 F7 單元格分別輸入概率計算公式。 13 圖 1-10 STEP3：公式的拷貝。選取 B7 至 F7 單元格，拖動“填充柄”至 F13 單元格 即可完成公式的拷貝操作。結(jié)果圖 1-11所示。 圖 1-11 STEP4：下面開始創(chuàng)建二項分布圖表。選取 B7 至 B13 單元格，選取“插入”菜單的“圖表”子菜單。 STEP5：選擇“柱狀圖”，然后單擊“下一步”。 STEP6：單擊“系列”標(biāo)簽，單擊“分類 (X)軸標(biāo)志”框，并用鼠標(biāo)選取A7至 A13 單元格為圖表 X 軸的軸標(biāo)，然后單擊“下一步”。 14 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ù) NEGBINOMDIST：函數(shù) POISSON: 正態(tài)分布函數(shù) NORMDIST：函數(shù)NORMSDIST：函數(shù) NORMSINV： t 分布函數(shù) TDIST: 有興趣的同學(xué)可以自己研究。 15 實驗三 隨機(jī)抽樣工具 實驗?zāi)康募耙?掌握利用 Excel的 BINOMDIST 的函數(shù)計算二項分布的概率以及累積概率。 實驗內(nèi)容及步驟 Excel 中的 Rand()函數(shù)可以返回大于等于 0 小于 1 的均勻分布隨機(jī)數(shù)， Rand()不帶任何參數(shù)運行，每次計算時時都將返回一個新的數(shù)值。RAND()函數(shù)可以被用來作為不重復(fù)抽樣調(diào)查的工具。 例 1-5：如圖 1-12所示有 10 個象征性的樣本數(shù)據(jù)，欲從中隨機(jī)抽取 5 個數(shù)據(jù)可按如下步驟操作： 圖 1-12 STEP1：選擇 B2 單元格，輸入公式“ =RAND()”并回車 。 STEP2：拖動 B2 單元格右下角的填充柄至 B11 單元格，并在 B1 單元格輸入標(biāo)題“ RANDOM”。 STEP3：選取單元格 B2 至 B11，右擊選中的區(qū)域選擇“復(fù)制”，再次右 16 擊選中的區(qū)域，選擇“選擇性粘貼”，單擊選項“數(shù)值” 后，點擊“確定”按扭。 STEP4：選取單元格 A2 至 B11 單元格，選擇“數(shù)據(jù)”菜單項下的排序子菜單。 STEP5：選取“ RANDOM”為主要關(guān)鍵字 ，然后點擊“確定”按扭。排序結(jié)果如圖 1-13 所示， A2 至 A6 單元格的樣本即為隨機(jī)抽取的 5 個樣本。 圖 1-13 17 實驗四 由樣本推斷總體 實驗?zāi)康募耙?掌握利用 Excel的幾個函數(shù)，如求平均函數(shù) AVERAGE、標(biāo)準(zhǔn)差函數(shù) STDEV、T 分布函數(shù) TINV 等的組合使用構(gòu)造一個專門用于實現(xiàn)樣本推斷總體的Excel工作表。 實驗內(nèi)容及步驟 下面的例子先計算樣本的平均數(shù)和 標(biāo)準(zhǔn)差，然后在一定置信水平上估計總體均值的區(qū)間范圍。操作步驟： STEP1：構(gòu)造工作表。如圖 1-14所示，首先在各個單元格輸入以下的內(nèi)容，其中左邊是變量名，右邊是相應(yīng)的計算公式。 STEP2：為表格右邊的公式計算結(jié)果定義左邊的變量名。選定A4:B6,A8:B8和 A10:B15 單元格 (先選擇第一部分，再按住 CTRL 鍵選取另外兩個部分 )，選擇“插入”菜單的“名稱”子菜單的“指定”選項，用鼠標(biāo)點擊“最左列”選項 ,然后點擊“確定”按扭即可。 圖 1-14 18 STEP3：輸入樣本數(shù)據(jù)，和用戶指定的置信水平 0.95,如圖附 -13 所示。 STEP4：為樣本數(shù)據(jù)命名。選定 D1:D11 單元格，選擇“插入”菜單的“名稱”子菜單的“指定”選項，用鼠標(biāo)點擊“首行”選項 ,然后點擊“確定”按扭，得到圖 1-15所示的計算結(jié)果。 圖 1-15 結(jié)果說明：以上例子說明如何交叉組合使用 Excel 的公式和函數(shù)，以構(gòu)造出一個能實現(xiàn)樣本推斷總體有關(guān)計算的 Excel 工作表。實際上，在用Excel 進(jìn)行數(shù)據(jù)統(tǒng)計處理之時，許多統(tǒng)計功能可以使用和上例類似的方法，通過組合使用 Excel 的各類統(tǒng)計函數(shù)和公式加以實現(xiàn)的。 19 實驗五 假設(shè)檢驗 實驗?zāi)康募耙?掌握利用 Excel 的正態(tài)分布函數(shù) NORMSDIST、判斷函數(shù) IF 等，構(gòu)造一張能夠?qū)崿F(xiàn)在總體方差已知情況下進(jìn)行總體均值假設(shè)檢驗的 Excel工作表。 實驗內(nèi)容及步驟 例 1-6：利用 Excel 的正態(tài)分布函數(shù) NORMSDIST、判斷函數(shù) IF 等，構(gòu)造一張能夠?qū)崿F(xiàn)在總體方差已知情況下進(jìn)行總體均值假設(shè)檢驗的 Excel 工作表。 操作步驟： STEP1：構(gòu)造工作表。如圖 1-16 所示，首先在各個單元格輸入以下的內(nèi)容，其中左邊是變量名，右邊是相應(yīng)的計算公式。 STEP2：為表格右邊的公式計算結(jié)果定義左邊的變量名。選定A3:B4,A6:B8， A10:A11,A13:A15 和 A17:B19 單元格，選擇“插入”菜單的“名稱”子菜單的“指定”選項，用鼠標(biāo)點擊“最左列”選項 ,然后點擊“確定”按扭即可。 20 圖 1-16 STEP3：輸入樣本數(shù)據(jù)，以及總體標(biāo)準(zhǔn)差、總體均值假設(shè)、置信水平數(shù)據(jù)。如 圖 1-17所示。 STEP4：為樣本數(shù)據(jù)命名。選定 C1:C11 單元格，選擇“插入”菜單的“名稱”子菜單的“指定”選項，用鼠標(biāo)點擊“首行”選項 ,然后點擊“確定”按扭，得到如圖 1-17中所 示的計算結(jié)果。 圖 1-17 21 結(jié)果說明：如圖 1-17所示，該例子的檢驗結(jié)果不論是單側(cè)還是雙側(cè)均為拒絕 Ho 假設(shè)。所以，根據(jù)樣本的計算結(jié)果，在 5%的顯著水平之下 ,拒絕總體均值為 35 的假設(shè)。同時由單側(cè)顯著水平的計算結(jié)果還可以看出，在總體均值是 35 的假設(shè)之下，樣本均值小于等于 31.4 的概率僅為0.020303562。 22 實驗六 雙樣本等均值假設(shè)檢驗 實驗?zāi)康募耙?掌握利用 Excel 數(shù)據(jù)分析中提供雙樣本等均值假設(shè)檢驗工具進(jìn)行假設(shè)檢驗的方法，并能夠解釋實驗結(jié)果。 實驗內(nèi)容及步驟 例 1-7：雙樣本等均值檢驗是在一定置信水平之下 ,在兩個總體方差相等的假設(shè)之下，檢驗兩個總體均值的差值等于指定平均差的假設(shè)是否成立的檢驗。假設(shè)某工廠為了比較兩種裝配方法的效率，分別組織了兩組員工，每組 9 人，一組采用新的裝配方法，另外一組采用舊的裝配方法。18個員工的設(shè)備裝配時間圖 1-18 中表格所示。根據(jù)以下數(shù)據(jù)，是否有理由認(rèn)為新的裝配方法更節(jié)約時間？ 圖 1-18 操作步驟： STEP1：選擇“工具”菜單的“數(shù)據(jù)分析”子菜單，雙擊“ t-檢驗 : 雙樣本等方差假設(shè)”選項，則彈出圖 1-19 所示對話框。 23 圖 1-19 STEP2： 分別填寫變量 1 的區(qū)域： $B$1:$B$10，變量 2 的區(qū)域：$D$1:$D$10，由于我們進(jìn)行的是等均值的檢驗，填寫假設(shè)平均差為 0，由于數(shù)據(jù)的首行包括標(biāo)志項選擇標(biāo)志選項，所以選擇“標(biāo)志”選項，再填寫顯著水平為 0.05,然后點擊“確定”按扭。則可以得到圖 1-20所示的結(jié)果。 24 圖 1-20 結(jié)果分析：如圖 1-20中所示，表中分別給出了兩組裝配時間的平均值、方差和樣本個數(shù)。其中，合并方差是樣本方差加權(quán)之后的平均值 ,Df 是假設(shè)檢驗的自由度它等 于樣本總個數(shù)減 2， t 統(tǒng)計量是兩個樣本差值減去假設(shè)平均差之后再除于標(biāo)準(zhǔn)誤差的結(jié)果，“ P(T1 之后，都在區(qū)間 (-0.438,0.438)之間，因此可以認(rèn)為自相關(guān)函數(shù)在 K1 之后截尾，因此我們選用 AR(1)模型進(jìn)行數(shù)據(jù)擬合。復(fù)制 C2:C20 的數(shù)據(jù)，將之以值復(fù)制的形式復(fù)制到 D3:D21 的單元格，并在D1 中填入標(biāo)志項“ Z(-1)”。選擇“工具”菜單的“數(shù)據(jù)分析”子菜單，雙擊“回歸”選項，彈出回歸分析對話框。按圖附 -40 所示的方式填寫對話框。然后單擊“確定”按扭，即可得到 AR(1)模型的估計結(jié)果。 STEP5：按以上操作步驟，可得到圖 1-41 所示 AR(1)模型。 42 圖 1-41 結(jié)果分析：零均值化模型的估計結(jié)果是 Z=1.06284*Z(-1),還原成上證指數(shù)，最終的時間序列模型是：上證指數(shù)估計值 -上證指數(shù)的平均值=1.06284(上一天上證指數(shù) -上證指數(shù)平均值 )。 Excel 與數(shù)據(jù)統(tǒng)計分析 統(tǒng)計計算與統(tǒng)計分析強(qiáng)調(diào)與計算機(jī)密切結(jié)合， Excel與數(shù)據(jù)統(tǒng)計分析旨在提高學(xué)生計算機(jī)的綜合運用能力，用統(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ù)據(jù)集中某數(shù)值的平方根作區(qū)間，在數(shù)據(jù)集的最大值與最小值之間用等寬間隔。如果用戶自己定義區(qū)間，可用 2、 5或 10的倍數(shù)，這樣易于分析。 對于工資數(shù)據(jù)，最小值是 100，最大值是 298。一個緊湊的直方圖可從區(qū)間 100開始，區(qū)間寬度用 10，最后一區(qū)間為 300結(jié)束，需要 21個區(qū)間。這里所用的方法在兩 43 端加了一個空區(qū)間，在低端是區(qū)間 “ 100或 小于 100” ，高端是區(qū)間 “ 大于 300” 。 參考圖 3.3，利用下面這些步驟可得到頻率分布和直方圖： 1為了方便，將原始數(shù)據(jù)拷貝到新工作表“指定頻數(shù)直方圖”中。 2在 B1單元中輸入“組距”作為一標(biāo)記，在 B2單元中輸入 100， B3單元中輸入110，選取 B2:B3，向下拖動所選區(qū)域右下角的 +到 B22單元。 3按下列步驟使用“直方圖”分析工具： （ 1）選擇 工具 菜單之 數(shù)據(jù)分析 選項 , 在 分析工具 框中“直方圖”。如圖 4所示。 圖 3.1 數(shù)據(jù)分析工具之直方圖對話框 1) 輸入 輸入?yún)^(qū)域： A1:A51 接受區(qū)域： B1:B22 (這些區(qū)間斷點或界限必須按升序排列 ) 選擇標(biāo)志 2) 輸出選項 輸出區(qū)域 : C1 選定圖表輸出 (2)單擊 確定 ， Excel將計算出結(jié)果顯示在 輸出區(qū)域 中。 44 圖 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ū)，單擊鼠標(biāo)右鍵并選擇繪圖區(qū)格式，將邊框改為 無 并將 區(qū)域 改為 無 ，單擊 確定 。 45 3條寬：在傳統(tǒng)的直方圖中，柱形是彼此相連接而不是分開的。選擇某個柱形，單擊鼠標(biāo)右鍵，選擇 數(shù)據(jù)系列格式 ，并單擊 選擇 標(biāo)簽，將 間距寬度 從 150改為 0，單擊 確定 。 4 X軸標(biāo)志：選取 x軸，單擊鼠標(biāo)右鍵，選擇 坐標(biāo)軸格式 ，單擊 對齊 標(biāo)簽 ，將 方向從自動改為水平文本，在這種設(shè)置下，即使圖表已重置尺寸， x軸標(biāo)記也會變?yōu)樗降模瑔螕?確定 。最后的直方圖 4.6 圖 3.4 修改后的直方圖 二、不指定接受區(qū)域直方圖 在進(jìn)行探索性分析時，為了方便，通常不指定接受區(qū)域作直方圖，步驟如下： （ 1）選擇 工具 菜單之 數(shù)據(jù)分析 選項 , 在 分析工具 框中“直方圖”。如圖 4所示。 1) 輸入 輸入?yún)^(qū)域： A1:A51 接受區(qū)域： (該處為空 ) 選擇標(biāo)志 2) 輸出選項 輸出區(qū)域 : B1 選定圖表輸出 直方圖01234567100 120 140 160 180 200 220 240 260 280 300組距頻率 46 (2)單擊 確定 ，得結(jié)果。 (3)按 前面方法對直方圖進(jìn)行進(jìn)一步修飾即得圖 3.5 圖 3.5 修改后的直方圖 3.2 計數(shù)數(shù)據(jù)的透視表與條圖 例 3.2（ 3-3）數(shù)據(jù)見圖 步驟如下： （ 1）選擇 數(shù)據(jù) 菜單之 數(shù)據(jù)透視表和圖表報告 選項 , 如圖 4所示。 47 (2)選擇數(shù)據(jù)源區(qū)域 （ 3）選定數(shù)據(jù)透視表位置，完成 （ 4）將“性別”作為行字段拖至 G 列，并將“性別”作為數(shù)據(jù)拖至數(shù)據(jù)項處，得下表結(jié)果 48 同理可得“文化程度”的透視表 此時如點擊圖形按鈕，立即得到如下的透視圖 49 （ 5）將“性別”作為行字段拖至行字段處，并將“文化 程度”作為列字段拖至列字段處，將“性別”或“文化程度”作為列字段拖至數(shù)據(jù)字段處得下表結(jié)果 第四章 總量指標(biāo)和相對指標(biāo) 例 4.1 (4-13) 計算步驟： （ 1）計算各廠計劃完成 % E3=D3/C3*100, （ 2） 2000 年實際產(chǎn)量為 1999 年的 % F3=D3/B3*100, 第五章 平均指標(biāo) 5.1 簡單平均數(shù) 例 5.1某組有學(xué)生 10人統(tǒng)計課考試成績?yōu)?65， 82， 76， 80， 82， 86， 84， 88， 95， 50 98分，試求其平均指標(biāo)。 平均數(shù)的計算步驟如下： （ 1） 將數(shù)據(jù)輸入到 A列，根據(jù) Excel提供的公式計算各種平均數(shù) （ 2）用 Ctrl+ 可切換到下面的結(jié)果： 5.2 加權(quán)平均數(shù) 例 5.2（ 5-1）原始數(shù)據(jù)見下圖 A-D列，其中 A、 B列放日產(chǎn)量的下限和上限 平均數(shù)的計算步驟如下： （ 1）計算日產(chǎn)量的組中值 E3=(A3+B3)/2, 51 （ 2）計算每個組段的總產(chǎn)量 F3=C3*E3, G3=D3*E3, （ 3）計算每月的總產(chǎn)量 F8=SUM(F3:F7), G8=Sum(G3:G7), （ 4）計算平均數(shù)公式如下： 均數(shù) F9=F8/C8 G9=G8/D8 眾數(shù) F10=A4+(C4-C3)/(C4-C3+C4-C5)*10 G10=A6+(D6-D5)/(D6-D5+D6-D7)*10 中位數(shù) F11=A4+(C8/2-A4)/C4*10 G11=A5+(D8/2-C5)/D5*10 第六章 變異度指標(biāo) 6.1 簡單變異度指標(biāo) 例 6.1（ 6-1） 變異度指標(biāo)的計算步驟如下： （ 1）將甲乙兩組數(shù)據(jù)輸入到 A, B列，根據(jù) Excel提供的公式計算各種變異度指標(biāo) （ 2）用 Ctrl+ 可切換到下面的公式： 6.2 加權(quán)變異度指標(biāo) 例 6.2（ 6-2）甲品種的原始數(shù)據(jù)見下圖 B-C列，乙品種的原始數(shù)據(jù)見下圖 G-H列 52 下面以甲品種的數(shù)據(jù)計算為例： （ 1）計算單產(chǎn)值 D4=C4/B4, （ 2）計算單產(chǎn)均值 D9=C9/B9 （ 3）計算次數(shù) X離差平方 E4=B4*(D4-$D$9)2 , 并求和 E9=SUM(E4:E8) （ 4）計算標(biāo)準(zhǔn)差： D11=SQRT(E9/B9)=68.91 （ 5）計算變異系數(shù)： D12=D11/D9*100=6.9% 同理可得乙品種的標(biāo)準(zhǔn)差為 162.71, 變異系數(shù)為 16.30% 第七章 抽樣調(diào)查 例 7.1 (7-5) 期望 求 E(X)的公式 B4=SUM(B1:F1)*(B2:F2), 由于此處用到數(shù)組乘積求和，所以要得到結(jié)果，需用 Ctrl+Shift+Enter組合鍵。 例 7.2 (7-6) 二項分布 計算公式： P(5=9)=1-P(x=5)=1-P(x=4) Excel 計算結(jié)果： Excel 計算公式： 54 （ Ctrl+ 互換） 例 7.4 (7-9) 超幾何分布 例 7.5 (7-10) 正態(tài)分布 其中 F2=1-D2, D4=D3-D2 第八章 假設(shè)檢驗 8.1 大樣本 使用正態(tài)分布的假設(shè)檢驗 例 8.1 商店經(jīng)理想為商店的持信用卡的顧客建一新的付款系統(tǒng)，經(jīng)過詳細(xì)的經(jīng)濟(jì)分析，她判定如果新系統(tǒng)每月平均利潤低于 70元的話就不能有效地使用資金。于是隨機(jī)抽取了 200個 月的利潤，其平均月利潤為 66元。如果 0.05，有無充分的證據(jù)說明新系統(tǒng)不是一項節(jié)省資金的系統(tǒng) ?假設(shè)總體的標(biāo)準(zhǔn)偏差為 30元。 圖 8.1 正態(tài)假設(shè)檢驗的標(biāo)記和公式 -已知均值標(biāo)準(zhǔn)差計算公式 55 上圖所示的工作表可用于正態(tài)分布平均值的左尾、右尾和雙尾假設(shè)檢驗。檢驗結(jié)果包括基于 判決法和 P值報告法。輸入樣本大小、樣本平均值和標(biāo)準(zhǔn)偏差作為值、公式或引用，指定假設(shè)的平均值 (Mean)和顯著水平 作為值。 下面各步驟描述了如何建立該工作表： （ 1）打開一新工作表并輸入 B列所示標(biāo)記。 （ 2） 要在 C列的公式使用 B列中的名稱，選取單元 B4:C12，從 插入 菜單中選擇名稱 指定 ，在指定名稱對話框中復(fù)選名稱創(chuàng)建于 最左列 ，單擊 確定 。 （ 3）輸入 C列所示的公式 (按圖所示鍵入公式或通過單擊適當(dāng)?shù)囊衙膯卧迦牒瘮?shù)來建立公式 )。 （ 4） 要得圖 8.1的 A列所示的外觀， 按 Ctrl+。 因為經(jīng)理想知道平均月利潤是否小于 70元，所以備擇假設(shè)為 Hd： Mean 70，零假設(shè)為 Ho： Mean 70或簡單地為 Ho： Mean 70。由于數(shù)據(jù)已經(jīng)總結(jié)過了，可直接在工作表單元中輸入樣本大小 n、樣本平均值、總體的標(biāo)準(zhǔn)偏差 、假設(shè)總體平均值和顯著水平。 56 圖 8.2 正態(tài)假設(shè)檢驗 結(jié)論 ：得到 Z小于 -1.886的概率是 0.0297。如果零假設(shè)為真 (每月平均利潤為 70元 )，得到樣本平均值為 66元或小于它的概率約為 3，即有充分的證據(jù)說明新系統(tǒng)是一項節(jié)省資金的系統(tǒng)。 8.2 小樣本 使 t分布的假設(shè)檢驗 家保險公司用代理的方式支付其客戶，賠償假定每年的平均代理賠償費用為 32000元，如果平均支付費用與計劃不同，就需要對計劃進(jìn)行修改。對一個有 36個代理的樣本，上一年的平均支付費用為 27500元，標(biāo)準(zhǔn)偏差為 8400元，如果整個公司的平 均支付變化與該樣本的情況不同，那么可用管理計劃來修改賠償計劃。根據(jù)這一結(jié)果的 P值，這一樣本能充分說明平均值變化了嗎？ 本例已知均值標(biāo)準(zhǔn)差，下面各步驟描述了如何建立計算工作表： 1打開一新工作表，輸入 A列所示標(biāo)記。 2要在 C列的公式使用 B列中的名稱，選取單元 B4:C13，從 插入 菜單中選擇 名稱 指定 ，在指定名稱對話框中復(fù)選名稱創(chuàng)建于 最左列 ，單擊 確定 。 圖 8.3 已知均值標(biāo)準(zhǔn)差計算公式 -t假設(shè)檢驗的標(biāo)記和公式 57 因為經(jīng)理想知道平均支付是否發(fā)生了變化 (不用指出變化的方向 )，所以備擇假設(shè)為 H1： Mean 32000，零假設(shè)為 Ho： Mean 32000。由于數(shù)據(jù)已經(jīng)總結(jié)過了，可以直接向工作表的單元中輸入樣本大小 n、樣本平均值、總體的標(biāo)準(zhǔn)偏差和假設(shè)總體的平均值。盡管例中未指明顯著水平可輸入為 0.05。 結(jié)論：由于 P=0.00280.05，所以，有足夠的理由拒絕在顯著水平為 5 (雙尾檢驗 )時的零假設(shè)，可得出如下結(jié)論：平均支付值不等于 32000，明確說明平均值改變了。 58 圖 8.4 t假設(shè)檢驗 例 8.3（ 已知原始數(shù)據(jù)）一家制造商生產(chǎn)鋼棒，為了提高質(zhì)量，如果某新的生產(chǎn)工藝生產(chǎn)出的鋼棒的斷裂強(qiáng)度大于現(xiàn)有平均 斷裂強(qiáng)度標(biāo)準(zhǔn)的話，公司將采用該工藝。當(dāng)肪鋼棒的平均斷裂強(qiáng)度標(biāo)準(zhǔn)是 500公斤。對新工藝生產(chǎn)的鋼捧進(jìn)行抽樣， 12件棒材的斷裂強(qiáng)度如下： 502， 496， 510， 508， 506， 498， 512， 497， 515， 503， 510和 506，假設(shè)斷裂強(qiáng)度的分布比較近似于正態(tài)分布，將樣本數(shù)據(jù)畫圖，所畫圖形能表明平均斷裂強(qiáng)度有所提高嗎 ? 圖 8.5顯示了假設(shè)檢驗所需的數(shù)據(jù)。 因為經(jīng)理想檢查是否有提高，備擇假設(shè)為H1： Mean 500，所以用右尾檢驗比較合適。零假設(shè)為 Ho： Mean 500，或簡單地 Ho：Mean=500。如例 8.2所述， D2： D13單元已命名為 Data，單元 B5:B7包含了公式COUNT(Data), AVERAGE(Data)和 STDEV(Data)。盡管例 5.5未指定一顯著水平 , 在 B10單元中入了顯著水平為 0.05。包含了左尾檢驗結(jié)果的 15到 18行被隱藏。 59 圖 8.5 小樣本 t假設(shè)檢驗公式 結(jié)論： P=0.01310.05，說明有充分證據(jù)來拒絕零假設(shè)?？傻贸鋈缦陆Y(jié)論：新工藝在統(tǒng)計上可帶來平均斷裂強(qiáng)度的顯著提高。 圖 8.6 小樣本 t假設(shè)檢驗結(jié)果 第九章 相關(guān)與回歸 簡單線性相關(guān)分析 例 9.1 （ 9-1） 60 1 Excel進(jìn)行相關(guān)分析： （ 1）輸數(shù)據(jù) : 將數(shù)據(jù)輸入 A1:C9單元格。 （ 2）繪制散點圖 : 圖 9.1 簡單相關(guān)系數(shù)及散點圖 3. 計算相關(guān)系數(shù) (1) 選擇 工具 菜單之 數(shù)據(jù)分析 選項 , 在 分析工具 框中“相關(guān)系數(shù)”。 相關(guān)系數(shù)對話框?qū)@示為圖 9.2所示，它帶輸入輸出的提示。 圖 9.2 相關(guān)系數(shù)對話框 1) 輸入 輸入?yún)^(qū)域： B1:C9 分組方式：逐列 選擇標(biāo)志位于第一行 2) 輸出選項 61 輸出區(qū)域 : A13 (2)單擊 確定 ， Excel將計算出結(jié)果顯示在 輸出區(qū)域 中 。 4. 相關(guān)系數(shù)假設(shè)檢驗 (1)在單元格 F14中輸入公式 =B15/SQRT(1-B152)/(8-2) 計算得相關(guān)系數(shù)的 t值為 49.46 (2)在單元格 F15中輸入公式 =TDIST(ABS(F14),B-2,2) 計算得 p=0.0001 (3) 結(jié)論 : 由于 r=-0.9689, 且 p0.05, 所以 , 在 0.05水平上拒絕原假設(shè) , 認(rèn)為產(chǎn)品產(chǎn)量與單位成本 間有負(fù)的線性相關(guān)關(guān)系 9.2 簡單 回歸分析 上面的簡單相關(guān)分析只是說明兩變量之間的線性關(guān)系密切的程度，如果要建立它們之間線性依存的關(guān)系式，就需用回歸分析。 可按下列步驟使用“回歸”分析工具： 1. 輸數(shù)據(jù) : 將數(shù)據(jù)輸入 A1:C9單元格。 2. 回歸分析 : (1) 選擇 工具 菜單之 數(shù)據(jù)分析 選項 , 在 分析工具 框中“回歸”?；貧w對話框?qū)@示為圖 9.3所示， 62 圖 9.3 回歸分析對話框 1) 輸入 Y值輸入?yún)^(qū)域： C1:C9 X值輸入?yún)^(qū)域： B1:B9 標(biāo)志 : 選擇 常數(shù)為零 : 只有當(dāng)用戶想強(qiáng)制使回歸線通過原點 (0,0)時才選此框 置信度 : Excel自動包括了回歸系數(shù)的 95置信區(qū)間。要使用其他置信區(qū)間 , 選擇該框并在 Confidence Levet框中輸入 置信水平 2) 輸出選項 輸出區(qū)域 : D1 3) 殘差 殘差 (R)：選擇此框可得到預(yù)測值和殘差 (Residual)。 殘差圖 (D): 選擇此框可得到殘差和每一 x值的圖表。 標(biāo)準(zhǔn)殘差 (T)：選擇此框可得到標(biāo)準(zhǔn)化的殘差 ,每一 殘差 被估計標(biāo)準(zhǔn)誤差除 )。這一輸出可使曲線較容易分層。 線性擬合圖 (I)：選擇此框可得到一含有 y輸入數(shù)據(jù)和擬合的 y值的散點圖。 4) 正態(tài)概率圖 : 繪制 因變量的 正態(tài)概率圖 63 (2)單擊 確定 ， Excel將計算出結(jié)果顯示在 輸出區(qū)域 中。 圖 9.4 回歸分析結(jié)果 3. 回歸解釋 擬合回歸線的截距和斜率放在圖 9.4的總結(jié)輸出中標(biāo)記有 “Coeffients的左下部。截距系數(shù) 77.30769是線性回歸方程中的常數(shù)項 ， x系數(shù) -0.80769是斜率。回歸方程是 ： y 77.30769-0.80769 * x 圖 9.5 殘差及擬合線 在圖 9.5所示的殘 差 輸出中，預(yù)測 y，有時又稱擬合值，是用這個回歸方程計算的單位成本的估計 值 。殘差是實際值和擬合值之間的差值。 回答 “擬合關(guān)系怎么樣 ”問題的最通用的四個方法是標(biāo)準(zhǔn)誤差， R2， t統(tǒng)計值和方差分析。標(biāo)準(zhǔn)誤差 0.83205顯示在圖 9.4的單元 E7中。作為殘數(shù)的標(biāo)準(zhǔn)偏差，它衡量單位成本在回歸線周圍的分散情況，標(biāo)準(zhǔn)誤差通常稱為估計標(biāo)準(zhǔn)誤差。 64 R2（ R Square），如圖 9.4的單元 E5所示，衡量用回歸線解釋的因變量變化的比例。這一比例必?fù)羰?0和 1之間的一個數(shù)據(jù)，經(jīng)常以百分?jǐn)?shù)表示。這里，約有的 94的單位成本的變化是在線性方程中用產(chǎn)品產(chǎn)量做為預(yù)測因子來解釋的。單元 E6顯示的 Adjusted R square在用附加解釋變量把此模型和其他模型比較時很有用。 第十章 時間序列分析指標(biāo) 例 10.1 (10-2) 1995-2000 的銷售額見下圖 B 列，則其速度分析指標(biāo)計算如下： Excel 計算公式如下 : (Ctrl+ 切換 ) 例 10.2 (10-3) 65 計算步驟： （ 1）各季平均每月總產(chǎn)值計算公式 說明 單元格 公式 一季 B16 = AVERAGE(B2:B4) 二季 B17 = AVERAGE(B5:B7) 三季 B18 = AVERAGE(B8:B10) 四季 B19 = AVERAGE(B11:B13) 全年 B20 = AVERAGE(B2:B13) （ 2）全年平均職工人數(shù)： C16 = (C2/2+C3+C4+C5+C6+C7+C8+C9+C10+C11+C12+C13+C14/2)/13 （ 3）月平均勞動生產(chǎn)率： C17 =B20/C16*10000 年平均勞動生產(chǎn)率： C18 =SUM(B2:B13)/C16*10000 （ 4）全年 職工構(gòu)成指標(biāo)： C19 = (D2/2+D3+D4+D5+D6+D7+D8+D9+D10+D11+D12+D13+D14/2)/ (C2/2+C3+C4+C5+C6+C7+C8+C9+C10+C11+C12+C13+C14/2)*100 例 10.3 (10-5) 66 計算步驟： （ 1）計算每年的增長速度 A2=1+A