excel統(tǒng)計(jì)數(shù)據(jù)分析應(yīng)用講解

Excel數(shù)據(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)算?，F(xiàn)在應(yīng)用于這一方面的軟件很多，如MINITAB、SPS、SAS等，但是大多為英文軟件，在這里我要介紹的就是用EXCEL的數(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è)工具必須加載相應(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-21．變量個(gè)數(shù)：需要形成幾組數(shù)據(jù)。2．隨機(jī)數(shù)個(gè)數(shù)：每組數(shù)據(jù)包含的數(shù)據(jù)個(gè)數(shù)。3．分布：選擇隨機(jī)形成的數(shù)據(jù)期望服從的概率分布類(lèi)型，包括均勻分布、正態(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ū)域：輸入數(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)偏差欄輸入0.2，基數(shù)輸入0，結(jié)果如圖2-4，確定后再工作表中輸出結(jié)果如圖2-5，其中的列標(biāo)志為提前輸入。隨布類(lèi)型下拉表中隨機(jī)數(shù)圖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種基本類(lèi)型的方差分析：?jiǎn)我蛩胤讲罘治觥㈦p因素用：無(wú)重復(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-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ì)圖3-1的數(shù)據(jù)進(jìn)行分析時(shí)選?。ňG色）和黃色區(qū)域。2．分組方式：提供列與行的選擇，當(dāng)同一水平的數(shù)據(jù)位于同一行時(shí)選擇行，位于同一列時(shí)選擇列，本例選擇行。3．如果在選取數(shù)據(jù)時(shí)包含了水平標(biāo)志，則選擇標(biāo)志位于第一行，本例選取。4．α：顯著性水平，一般輸入0.05，即95%的置信度。圖3-25．輸出選項(xiàng)：按需求選擇適當(dāng)?shù)姆治鼋Y(jié)果存儲(chǔ)位置。按圖3-2輸入選項(xiàng)后，對(duì)圖3-1的數(shù)據(jù)分析的結(jié)果如圖3-3所示。圖3-3雙因素?zé)o重復(fù)試驗(yàn)方差分析與單因素方差分析類(lèi)似，在分析前需將試驗(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ū)域，可以包含因素水平標(biāo)志。2．果數(shù)據(jù)輸入時(shí)選擇了因素水平標(biāo)志，請(qǐng)選擇標(biāo)志按鈕。3．顯著性水平α：根據(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)容基本與雙因素?zé)o重復(fù)方差分析相同，區(qū)別在于每一樣本的行數(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)分析和協(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所示。第五節(jié)假設(shè)檢驗(yàn)假設(shè)檢驗(yàn)是我們常用的數(shù)據(jù)分析工具，其方法是運(yùn)用統(tǒng)計(jì)工具對(duì)設(shè)定的H0原假設(shè)做出判斷，在EXCEL的數(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ū)域：在數(shù)據(jù)表中選擇第二變量的區(qū)域，本例為$B$1:$B$21。從上述選擇可以看出，分析對(duì)象的兩個(gè)變量可以具有不同的樣本量。3．如果在選擇變量1區(qū)域和變量2區(qū)域時(shí)，包含了數(shù)據(jù)的標(biāo)志，則選擇標(biāo)志框。4．顯著性水平α：一般選擇0.05或0.01，本例選擇0.05。5．輸出選項(xiàng)：根據(jù)需要選擇分析結(jié)果存儲(chǔ)的位置。進(jìn)行上述輸入過(guò)程后，產(chǎn)生分析結(jié)果于相應(yīng)得工作表位置，如圖5-3，可以也可以根據(jù)F計(jì)算值與臨界值的比較來(lái)判定，其中若P值小于0.05或F計(jì)算值大于臨界值，則說(shuō)明兩樣本的方差存在顯著差異，根據(jù)其中的P值判斷，反之則認(rèn)為兩樣本間的方差差異不具備統(tǒng)計(jì)顯著性。圖5-2圖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ù)其中的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)系，而EXCEL的數(shù)據(jù)分析工具庫(kù)中就提供了回歸分析的工具。通過(guò)回歸分析，一般會(huì)得響應(yīng)變量間的擬合方程，那么怎樣根據(jù)擬合方程來(lái)確定最優(yōu)試驗(yàn)條件工具庫(kù)中的規(guī)劃求解工具。到因素與呢？這時(shí)就可以使用數(shù)據(jù)分析回歸分析在進(jìn)行回歸分析為了解某指標(biāo)Y與因素X1、X2之間的據(jù)結(jié)果輸入EXCEL表中如圖10-1。前，須將試驗(yàn)結(jié)果數(shù)據(jù)按規(guī)定的格式輸入EXCEL表格中?，F(xiàn)假設(shè)影響關(guān)系，進(jìn)行了10次試驗(yàn)，試驗(yàn)數(shù)圖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值輸入?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%。6．輸出選項(xiàng)：根據(jù)需要選擇分析結(jié)果輸出的位置。7．殘差選項(xiàng)：根據(jù)需要可選擇分析結(jié)果中包含殘差和標(biāo)準(zhǔn)殘差以及殘差圖與線形擬合圖。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ù)分析后得出，某試驗(yàn)指標(biāo)Y與變量X1、X2之間的回歸-46X*X-13X-35X，且擬合很好，現(xiàn)需確定最優(yōu)的12擬合方程為Y=150+20X+30X212212試驗(yàn)條件。根據(jù)要求用極值法確定最優(yōu)條件，可得方程組如下：20-46X-26X=02130-46X-70X=012首先在EXCEL工作表中設(shè)計(jì)輸入如圖10-7的內(nèi)容，其中C3=B3，B2=20-46*D3-26*D2，B3==30-46*，D2、D-70*D33為求解值放置的單元格。其中C3=B3的意義是選擇B3中的公式為目標(biāo)函數(shù)，另外的函數(shù)為約束條件。圖10-7選擇