使用EXCEL進行統(tǒng)計分析資料12

1、使用EXCEL進行統(tǒng)計分析六西格瑪管理方法給我們的組織帶來了管理上新的認識，其中包含了 如方差分析、假設(shè)檢驗、回歸分析、基本統(tǒng)計等大量的數(shù)理工具，但問題 也隨之而來，那就是大量的數(shù)據(jù)處理，這時我們就會想到用軟件來幫助完 成大量的運算。現(xiàn)在應(yīng)用于這一方面的軟件很多，如 MINITAB、SPSS、SAS 等，但是大多為英文軟件，在這里我要介紹的就是用EXCE啲數(shù)據(jù)分析與規(guī)劃求解來幫助您進行簡單的數(shù)據(jù)分析。EXCEL的數(shù)據(jù)分析工具為中文界面，操作簡單，在一定程度上能給予大家?guī)椭５谝还?jié) 加載宏EXCEL的數(shù)據(jù)處理除了提供了很多的函數(shù)外，另外還有兩個功能就是數(shù)據(jù) 分析和規(guī)劃求解，但這兩個工具必須加載相

2、應(yīng)的宏后才能使用，操作步驟 為工具加載宏，出現(xiàn)如圖 1-1 的對話框，選擇分析工具庫和規(guī)劃求解， 點擊確定后，在工具菜單欄內(nèi)出現(xiàn)了這兩個分析工具。圖 1-1第二節(jié) 隨機數(shù)據(jù)與基本分析 在上一節(jié)通過加載宏在工具欄內(nèi)增加了兩個數(shù)據(jù)分析庫，數(shù)據(jù)分析與規(guī)劃 求解，在本節(jié)將介紹數(shù)據(jù)分析工具庫中的隨機數(shù)生成器和描述統(tǒng)計。 操作工具數(shù)據(jù)分析，出現(xiàn)如圖 2-1 的對話框，對話框包含了方差分析、 相關(guān)系數(shù)、協(xié)方差分析等十幾種分析工具， 但一般處理對象為簡單的數(shù)據(jù)。 圖 2-1在對話框內(nèi)選擇隨機數(shù)發(fā)生器，將出現(xiàn)如圖 2-2 的對話框，對話框提供了 相應(yīng)的隨機數(shù)產(chǎn)生及存儲的條件命令，具體如下：圖 2-2 1變量個數(shù)

3、：需要形成幾組數(shù)據(jù)。2隨機數(shù)個數(shù)：每組數(shù)據(jù)包含的數(shù)據(jù)個數(shù)。3分布：選擇隨機形成的數(shù)據(jù)期望服從的概率分布類型，包括均勻分布、 正態(tài)分布、二項分布、離散分布等。4參數(shù)：輸入不同分布的參數(shù)，如正態(tài)分布要求提供平均值及標準偏差。5隨機數(shù)基數(shù)：輸入隨機數(shù)的基數(shù)。6輸出選項：選擇輸出區(qū)域?qū)⑹闺S機數(shù)據(jù)在同一工作表的制定位置放置，選擇新工作表組將使隨機數(shù)據(jù)放置在同一工作薄的新工作表中，選擇新工作薄將生成一個新的工作薄來存儲數(shù)據(jù)或分析結(jié)果，以上選擇將出現(xiàn)在后 續(xù)的各種分析工具對話框中，操作意義相同。操作工具數(shù)據(jù)分析，在對話框內(nèi)選擇描述統(tǒng)計，將出現(xiàn)如圖 2-3 的對話 框，對話框的內(nèi)容解釋如下：圖 2-31輸入?yún)^(qū)

4、域：輸入數(shù)據(jù)所在的區(qū)域，可以包含數(shù)據(jù)的標志，注意分析的 數(shù)據(jù)單個總體應(yīng)包含在一列或一欄中。2分組方式：提供了兩種選擇，如果單個數(shù)據(jù)總體在同一行內(nèi)，請選擇 逐行，如果在列中，則選擇逐列。3在選取數(shù)據(jù)區(qū)域時如果選擇了數(shù)據(jù)標志，請選擇標志位于第一行。4輸出選項：前三項內(nèi)容如前所述，后四項選擇內(nèi)容供選擇數(shù)據(jù)分析的 內(nèi)容。示例，用隨機數(shù)發(fā)生器生成 3 組正態(tài)數(shù)據(jù)，每組數(shù)據(jù)容量為 20，數(shù)據(jù)的平均值為 5，標準偏差為 0.2 ，并對形成的數(shù)據(jù)作描述性統(tǒng)計。 選擇數(shù)據(jù)分析工具庫的隨機數(shù)發(fā)生器，在對話框中變量個數(shù)欄輸入3，在隨機數(shù)個數(shù)欄選擇 20，分布類型下拉表中選擇正態(tài)分布， 參數(shù)平均只欄輸 入 5 ，標準

5、偏差欄輸入 0.2 ，隨機數(shù)基數(shù)輸入 0 ，結(jié)果如圖 2-4 ，確定后再 工作表中輸出結(jié)果如圖 2-5 ，其中的列標志為提前輸入。圖 2-4 圖 2-5 選擇數(shù)據(jù)分析工具庫中的描述統(tǒng)計對上述形成的數(shù)據(jù)進行分析，在對話框 中輸入?yún)^(qū)域選擇隨機數(shù)據(jù)所在的表格區(qū)域，分組方式按要求為逐列，因為 包含標志所以選擇標志位于第一行，選擇輸出位置及匯總統(tǒng)計，均值置信 度為 95%及最大和最小值。選擇如圖 2-6 所示，單擊確定，輸出如圖 2-7 的分析結(jié)果，注意其中的眾數(shù)由于不存在而顯示為#N/A。圖 2-6 圖 2-7 第三節(jié) 方差分析在數(shù)據(jù)分析工具庫中提供了 3 種基本類型的方差分析：單因素方差分析、 雙因

6、素?zé)o重復(fù)試驗和可重復(fù)試驗的方差分析，本節(jié)將分別介紹這三種方差 分析的應(yīng)用： 單因素方差分析 在進行單因素方差分析之前，須先將試驗所得的數(shù)據(jù)按一定的格式輸入到 工作表中，其中每種水平的試驗數(shù)據(jù)可以放在一行或一列內(nèi)，具體的格式 如圖 3-1 ，圖中每個水平的試驗數(shù)據(jù)結(jié)果放在同一行內(nèi)圖 3-13-1數(shù)據(jù)輸入完成以后，操作工具數(shù)據(jù)分析，選擇數(shù)據(jù)分析工具對話框內(nèi)的 單因素方差分析，出現(xiàn)如圖 3-2 的對話框，對話框的內(nèi)容如下： 1輸入?yún)^(qū)域：選擇分析數(shù)據(jù)所在區(qū)域，可以選擇水平標志，針對圖 的數(shù)據(jù)進行分析時選取（綠色）和黃色區(qū)域。2分組方式：提供列與行的選擇，當同一水平的數(shù)據(jù)位于同一行時選擇 行，位于同一列

7、時選擇列，本例選擇行。3 如果在選取數(shù)據(jù)時包含了水平標志，則選擇標志位于第一行，本例選 取。4. a :顯著性水平，一般輸入 0.05，即95%勺置信度。圖 3-25. 輸出選項:按需求選擇適當勺分析結(jié)果存儲位置。按圖 3-2 輸入選項后，對圖 3-1 勺數(shù)據(jù)分析勺結(jié)果如圖 3-3 所示。 圖 3-3雙因素?zé)o重復(fù)試驗方差分析 與單因素方差分析類似，在分析前需將試驗數(shù)據(jù)按一定勺格式輸入工作表 中，如圖 3-4 所示:圖 3-4 數(shù)據(jù)輸入完成以后，操作工具數(shù)據(jù)分析，選擇數(shù)據(jù)分析工具庫中勺雙因 素?zé)o重復(fù)方差分析，出現(xiàn)如圖 3-5 勺對話框，對話框勺內(nèi)容如下:1. 輸入?yún)^(qū)域:選擇數(shù)據(jù)所在區(qū)域，可以包含

8、因素水平標志。2. 果數(shù)據(jù)輸入時選擇了因素水平標志，請選擇標志按鈕。3 顯著性水平a：根據(jù)實際情況輸入，一般選擇0.05 4輸出選項：按需要選擇分析結(jié)果存儲的位置。圖 3-5分析圖 3-4 中的數(shù)據(jù)，對話框如圖 3-5 輸入，分析結(jié)果輸出如圖 3-6 ： 圖 3-6雙因素可重復(fù)方差分析 雙因素可重復(fù)方差分析與雙因素?zé)o重復(fù)方差分析數(shù)據(jù)輸入的區(qū)別在于對 重復(fù)試驗數(shù)據(jù)的處理，如圖 3-7 所示，就是將重復(fù)試驗的數(shù)據(jù)疊加起來。 圖 3-7 數(shù)據(jù)輸入完成以后，操作工具數(shù)據(jù)分析，選擇數(shù)據(jù)分析工具庫中的雙因 素可重復(fù)方差分析，出現(xiàn)如圖 3-8 的對話框，對話框的內(nèi)容基本與雙因素 無重復(fù)方差分析相同，區(qū)別在于

9、每一樣本的行數(shù)選項，在此輸入重復(fù)試驗 的次數(shù)即可。3-8若須對圖 3-7 所示數(shù)據(jù)進行方差分析時，按圖 3-8 的顯示輸入即可，在輸 入?yún)^(qū)域選擇數(shù)據(jù)所在區(qū)域及因素水平標志，在每一樣本的行數(shù)處輸入3，即每種組合重復(fù) 3次試驗， 顯著性水平選擇 0.05。在輸出選項中可以按照 需求選擇分析結(jié)果儲存的位置。選擇確定以后分析結(jié)果如圖 3-9 所示。 圖 3-9第四節(jié) 相關(guān)分析與協(xié)方差分析EXCEL數(shù)據(jù)分析工具庫中提供了多因素數(shù)據(jù)的兩兩對應(yīng)得相關(guān)分析與協(xié)方 差分析，分析結(jié)果分別為相關(guān)系數(shù)矩陣和協(xié)方差矩陣。相關(guān)系數(shù)分析 在進行分析前先將數(shù)據(jù)按圖 4-1的格式輸入EXCEL工作表中，并對輸入的 數(shù)據(jù)進行相關(guān)

10、分析和協(xié)方差分析。圖 4-1 操作工具數(shù)據(jù)分析，在出現(xiàn)的數(shù)據(jù)分析工具欄中選擇相關(guān)系數(shù)，將出現(xiàn) 如圖 4-2 的對話框，對話框內(nèi)容如下：1輸入?yún)^(qū)域：選取圖 4-1 數(shù)據(jù)表中的綠色和黃色區(qū)域，表示標志與數(shù)據(jù)。2分組方式：根據(jù)數(shù)據(jù)輸入的方式選擇逐行或逐列，此例選擇逐列。3由于數(shù)據(jù)選擇時包含了標志，所以要勾選標志位于第一行。4根據(jù)需要選擇輸出的位置。圖 4-2將信息按圖 4-2 輸入對話框后，選擇確定，輸出結(jié)果如圖 4-3 所示。圖 4-3協(xié)方差分析數(shù)據(jù)輸入依圖 4-1 例，在數(shù)據(jù)分析內(nèi)選擇協(xié)方差，出現(xiàn)的對話框及輸入方 法與相關(guān)系數(shù)分析相同，輸入完成確定后，分析結(jié)果如圖 4-4 所示。圖 4-4第五節(jié)

11、 假設(shè)檢驗假設(shè)檢驗是我們常用的數(shù)據(jù)分析工具， 其方法是運用統(tǒng)計工具對設(shè)定的 H0 原假設(shè)做出判斷，在 EXCE啲數(shù)據(jù)分析工具庫中，主要包括 F檢驗、t-檢 驗和z-檢驗3種，其中t-檢驗又根據(jù)兩個樣本間的方差情況分為等、異方差檢驗。F 檢驗 雙樣本方差分析為了能進行分析，先用隨機數(shù)發(fā)生器在EXCEL工作表內(nèi)生成兩組服從正態(tài)分布的數(shù)據(jù)，注意標志為另外輸入，生成的數(shù)據(jù)如圖 5-1 所示。5-1在數(shù)據(jù)分析工具庫中選擇 F-檢驗雙樣本方差分析，出現(xiàn)如圖5-2的對話框， 對話框的內(nèi)容及對生成的數(shù)據(jù)分析操作如下：1. 變量1的區(qū)域：在數(shù)據(jù)表中選擇第一變量的區(qū)域，本例為$A$1:$A$21。2 變量 2的區(qū)

12、域： 在數(shù)據(jù)表中選擇第二變量的區(qū)域，本例為 $B$1 :$B$2 1 。從上述選擇可以看出，分析對象的兩個變量可以具有不同的樣本量。3. 如果在選擇變量 1 區(qū)域和變量 2 區(qū)域時，包含了數(shù)據(jù)的標志，則選擇 標志框。4. 顯著性水平a： 般選擇 0.05或0.01，本例選擇0.05。5. 輸出選項：根據(jù)需要選擇分析結(jié)果存儲的位置。進行上述輸入過程后，產(chǎn)生分析結(jié)果于相應(yīng)得工作表位置，如圖5-3，可以根據(jù)其中的P值判斷，也可以根據(jù)F計算值與臨界值的比較來判定，其 中若P值小于0.05或F計算值大于臨界值，則說明兩樣本的方差存在顯 著差異，反之則認為兩樣本間的方差差異不具備統(tǒng)計顯著性。圖 5-2圖

13、5-3t- 檢驗 雙樣本等方差或異方差檢驗t- 檢驗雙樣本等方差或異方差檢驗主要檢驗兩個樣本的均值差異顯著性， 其中前者條件為兩個樣本的方差相等，后者條件為不相等，其操作流程基 本相同，現(xiàn)僅以雙樣本等方差分析為例對圖 5-1 產(chǎn)生的數(shù)據(jù)進行分析。 在數(shù)據(jù)分析數(shù)據(jù)庫中選擇 t- 檢驗雙樣本等方差檢驗， 出現(xiàn)如圖 5-3 的對話 框，對話框內(nèi)容及分析的輸入內(nèi)容如下：圖 5-31 變量 1、2 區(qū)域的輸入與 F 檢驗相同。2 假設(shè)平均差，若輸入 0 則原假設(shè)為兩樣本均值無顯著差異。3 如果在選擇數(shù)據(jù)是包含標志，則選擇標志復(fù)選框。4 輸入顯著性水平。按以上操作后輸出內(nèi)容如圖 5-4 。可以根據(jù)其中的

14、P 值判斷，也可以根據(jù)t 計算值與臨界值的比較來判定， 其中若 P 值小于 0.05 或 t 計算值大于臨 界值，則說明兩樣本的均值存在顯著差異，反之則認為兩樣本間的均值差 異不具備統(tǒng)計顯著性。圖 5-4t- 檢驗 雙樣本平均差檢驗 雙樣本平均差檢驗區(qū)別于上述兩種檢驗的特點在于，兩個樣本的總體方差 已知，由于這點的局限性，其實際運用意義不大。其功能對話框如圖 5-5 ， 對話框內(nèi)容區(qū)別于 t- 檢驗就是對已知兩個樣本方差的輸入。圖 5-5第十節(jié) 回歸與規(guī)劃求解 回歸分析在試驗設(shè)計數(shù)據(jù)處理有非常重要的作用，例如正交設(shè)計、均勻設(shè)計、配方設(shè)計、復(fù)合設(shè)計都需要通過回歸分析來尋找因素與響應(yīng)變量間的 關(guān)系

15、，而EXCELS數(shù)據(jù)分析工具庫中就提供了回歸分析的工具。通過回歸 分析，一般會得到因素與響應(yīng)變量間的擬合方程，那么怎樣根據(jù)擬合方程 來確定最優(yōu)試驗條件呢？這時就可以使用數(shù)據(jù)分析工具庫中的規(guī)劃求解 工具?；貧w分析在進行回歸分析前，須將試驗結(jié)果數(shù)據(jù)按規(guī)定的格式輸入EXCEL表格中?，F(xiàn)假設(shè)為了解某指標 Y與因素XI、X2之間的影響關(guān)系，進行了 10次試驗， 試驗數(shù)據(jù)結(jié)果輸入 EXCEL表中如圖10-1 o圖 10-1試驗數(shù)據(jù)輸入完成后，在數(shù)據(jù)分析工具庫中選擇回歸，出現(xiàn)如圖10-2 的對話框，對話框內(nèi)容如下：圖 10-21 Y 值輸入?yún)^(qū)域：選擇響應(yīng)變量試驗結(jié)果數(shù)據(jù)所在的區(qū)域，可以包含標志。2. X值輸

16、入?yún)^(qū)域：選擇因素取值數(shù)據(jù)所在的區(qū)域，可以包含標志。.3. 如果選擇數(shù)據(jù)時包含了標志則選擇標志復(fù)選框。4. 如果強制擬合線通過坐標系原點則選擇常數(shù)為零復(fù)選框。5. 置信度：分析置信度，一般選擇 95%o6. 輸出選項：根據(jù)需要選擇分析結(jié)果輸出的位置。7 殘差選項：根據(jù)需要可選擇分析結(jié)果中包含殘差和標準殘差以及殘差 圖與線形擬合圖 第 9 頁8 如果希望輸出正態(tài)概率圖則選擇響應(yīng)得復(fù)選框。為了分析圖10-1中的數(shù)據(jù)，按圖10-2的內(nèi)容輸入對話框中，分析的數(shù)據(jù)結(jié)果如圖10-3、10-4，圖形結(jié)果如圖10-5、10-6所示。圖 10-3圖 10-4圖 10-5圖 10-6規(guī)劃求解假設(shè)現(xiàn)經(jīng)試驗設(shè)計及數(shù)據(jù)分

17、析后得出，某試驗指標Y與變量X1、X2之間的回歸擬合方程為 Y =150+20X1+30X2-46X1*X2-13X12-35X22,且擬合很好， 現(xiàn)需確定最優(yōu)的試驗條件。根據(jù)要求用極值法確定最優(yōu)條件，可得方程組如下：20-46X2-26X1=030-46X1-70X2=0首先在 EXCEL工作表中設(shè)計輸入如圖10-7的內(nèi)容，其中C3=B3,B2=20-46*D3-26*D2，B3=30-46*D2-70*D3，D2 D3 為求解值放置的單元格。其中C3=B3的意義是選擇B3中的公式為目標函數(shù)，另外的函數(shù)為約厶A丄BCD1因素M 巨1標函數(shù)求解2XI203X23030束條件。圖 10-7選擇工具菜單欄內(nèi)的規(guī)劃求解，出現(xiàn)如圖10-8的對話框，對話框內(nèi)容為:1. 設(shè)置目標單元格，此例選擇 C3,并選擇條件，本例為等于 02可變單元格：放置解得單元格3約束：編輯求解約束，