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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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