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

Excel數(shù)據(jù)處理六西格瑪管理方法給我們的組織帶來了管理上新的認識，其中包含了如方差分析、假設檢驗、回歸分析、基本統(tǒng)計等大量的數(shù)理工具，但問題也隨之而來，那就是大量的數(shù)據(jù)處理，這時我們就會想到用軟件來幫助完成大量的運算。現(xiàn)在應用于這一方面的軟件很多，如MINITAB、SPS、SAS等，但是大多為英文軟件，在這里我要介紹的就是用EXCEL的數(shù)據(jù)分析與規(guī)劃求解來幫助您進行簡單的數(shù)據(jù)分析。EXCEL的數(shù)據(jù)分析工具為中文界面，操作簡單，在一定程度上能給予大家?guī)椭５谝还?jié)加載宏EXCEL的數(shù)據(jù)處理除了提供了很多的函數(shù)外，另外還有兩個功能就是數(shù)據(jù)分析和規(guī)劃求解，但這兩個工具必須加載相應的宏后才能使用，操作步驟為工具〉加載宏，出現(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的對話框，對話框包含了方差分析、相關系數(shù)、協(xié)方差分析等十幾種分析工具，但一般處理對象為簡單的數(shù)據(jù)。圖2-1在對話框內(nèi)選擇隨機數(shù)發(fā)生器，將出現(xiàn)如圖2-2的對話框，對話框提供了相應的隨機數(shù)產(chǎn)生及存儲的條件命令，具體如下：圖2-21．變量個數(shù)：需要形成幾組數(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ù)或分析結果，以上選擇將出現(xiàn)在后續(xù)的各種分析工具對話框中，操作意義相同。操作工具〉數(shù)據(jù)分析，在對話框內(nèi)選擇描述統(tǒng)計，將出現(xiàn)如圖2-3的對話框，對話框的內(nèi)容解釋如下：圖2-31．輸入?yún)^(qū)域：輸入數(shù)據(jù)所在的區(qū)域，可以包含數(shù)據(jù)的標志，注意分析的數(shù)據(jù)單個總體應包含在一列或一欄中。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，標準偏差欄輸入0.2，基數(shù)輸入0，結果如圖2-4，確定后再工作表中輸出結果如圖2-5，其中的列標志為提前輸入。隨布類型下拉表中隨機數(shù)圖2-4圖2-5選擇數(shù)據(jù)分析工具庫中的描述統(tǒng)計對上述形成的數(shù)據(jù)進行分析，在對話框中輸入?yún)^(qū)域選擇隨機數(shù)據(jù)所在的表格區(qū)域，分組方式按要求為逐列，因為包含標志所以選擇標志位于第一行，選擇輸出位置及匯總統(tǒng)計，均值置信度為95%及最大和最小值。選擇如圖2-6所示，單擊確定，輸出如圖2-7的分析結果，注意其中的眾數(shù)由于不存在而顯示為#N/A。圖2-6圖2-7第三節(jié)方差分析在數(shù)據(jù)分析工具庫中提供了3種基本類型的方差分析：單因素方差分析、雙因素用：無重復試驗和可重復試驗的方差分析，本節(jié)將分別介紹這三種方差分析的應單因素方差分析在進行單因素方差分析之前，須先將試驗所得的數(shù)據(jù)按一定的格式輸入到工作表中，其中每種水平的試驗數(shù)據(jù)可以放在一行或一列內(nèi)，具體的格式如圖3-1，圖中每個水平的試驗數(shù)據(jù)結果放在同一行內(nèi)。圖3-1數(shù)據(jù)輸入完成以后，操作工具〉數(shù)據(jù)分析，選擇數(shù)據(jù)分析工具對話框內(nèi)的單因素方差分析，出現(xiàn)如圖3-2的對話框，對話框的內(nèi)容如下：1．輸入?yún)^(qū)域：選擇分析數(shù)據(jù)所在區(qū)域，可以選擇水平標志，針對圖3-1的數(shù)據(jù)進行分析時選取（綠色）和黃色區(qū)域。2．分組方式：提供列與行的選擇，當同一水平的數(shù)據(jù)位于同一行時選擇行，位于同一列時選擇列，本例選擇行。3．如果在選取數(shù)據(jù)時包含了水平標志，則選擇標志位于第一行，本例選取。4．α：顯著性水平，一般輸入0.05，即95%的置信度。圖3-25．輸出選項：按需求選擇適當?shù)姆治鼋Y果存儲位置。按圖3-2輸入選項后，對圖3-1的數(shù)據(jù)分析的結果如圖3-3所示。圖3-3雙因素無重復試驗方差分析與單因素方差分析類似，在分析前需將試驗數(shù)據(jù)按一定的格式輸入工作表中，如圖3-4所示：圖3-4數(shù)據(jù)輸入完成以后，操作工具〉數(shù)據(jù)分析，選擇數(shù)據(jù)分析工具庫中的雙因素無重復方差分析，出現(xiàn)如圖3-5的對話框，對話框的內(nèi)容如下：1．輸入?yún)^(qū)域：選擇數(shù)據(jù)所在區(qū)域，可以包含因素水平標志。2．果數(shù)據(jù)輸入時選擇了因素水平標志，請選擇標志按鈕。3．顯著性水平α：根據(jù)實際情況輸入，一般選擇0.05。4．輸出選項：按需要選擇分析結果存儲的位置。圖3-5分析圖3-4中的數(shù)據(jù)，對話框如圖3-5輸入，分析結果輸出如圖3-6：圖3-6雙因素可重復方差分析雙因素可重復方差分析與雙因素無重復方差分析數(shù)據(jù)輸入的區(qū)別在于對重復試驗數(shù)據(jù)的處理，如圖3-7所示，就是將重復試驗的數(shù)據(jù)疊加起來。圖3-7數(shù)據(jù)輸入完成以后，操作工具〉數(shù)據(jù)分析，選擇數(shù)據(jù)分析工具庫中的雙因素可重復方差分析，出現(xiàn)如圖3-8的對話框，對話框的內(nèi)容基本與雙因素無重復方差分析相同，區(qū)別在于每一樣本的行數(shù)選項，在此輸入重復試驗的次數(shù)即可。3-8若須對圖3-7所示數(shù)據(jù)進行方差分析時，按圖3-8的顯示輸入即可，在輸入?yún)^(qū)域選擇數(shù)據(jù)所在區(qū)域及因素水平標志，在每一樣本的行數(shù)處輸入3，即每種組合重復3次試驗，顯著性水平選擇0.05。在輸出選項中可以按照需求選擇分析結果儲存的位置。選擇確定以后分析結果如圖3-9所示。圖3-9第四節(jié)相關分析與協(xié)方差分析EXCEL數(shù)據(jù)分析工具庫中提供了多因素數(shù)據(jù)的兩兩對應得相關分析與協(xié)方差分析，分析結果分別為相關系數(shù)矩陣和協(xié)方差矩陣。相關系數(shù)分析在進行分析前先將數(shù)據(jù)按圖4-1的格式輸入EXCEL工作表中，并對輸入的數(shù)據(jù)進行相關分析和協(xié)方差分析。圖4-1操作工具〉數(shù)據(jù)分析，在出現(xiàn)的數(shù)據(jù)分析工具欄中選擇相關系數(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輸入對話框后，選擇確定，輸出結果如圖4-3所示。圖4-3協(xié)方差分析數(shù)據(jù)輸入依圖4-1例，在數(shù)據(jù)分析內(nèi)選擇協(xié)方差，出現(xiàn)的對話框及輸入方法與相關系數(shù)分析相同，輸入完成確定后，分析結果如圖4-4所示。第五節(jié)假設檢驗假設檢驗是我們常用的數(shù)據(jù)分析工具，其方法是運用統(tǒng)計工具對設定的H0原假設做出判斷，在EXCEL的數(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ū)域：在數(shù)據(jù)表中選擇第二變量的區(qū)域，本例為$B$1:$B$21。從上述選擇可以看出，分析對象的兩個變量可以具有不同的樣本量。3．如果在選擇變量1區(qū)域和變量2區(qū)域時，包含了數(shù)據(jù)的標志，則選擇標志框。4．顯著性水平α：一般選擇0.05或0.01，本例選擇0.05。5．輸出選項：根據(jù)需要選擇分析結果存儲的位置。進行上述輸入過程后，產(chǎn)生分析結果于相應得工作表位置，如圖5-3，可以也可以根據(jù)F計算值與臨界值的比較來判定，其中若P值小于0.05或F計算值大于臨界值，則說明兩樣本的方差存在顯著差異，根據(jù)其中的P值判斷，反之則認為兩樣本間的方差差異不具備統(tǒng)計顯著性。圖5-2圖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．假設平均差，若輸入0則原假設為兩樣本均值無顯著差異。3．如果在選擇數(shù)據(jù)是包含標志，則選擇標志復選框。4．輸入顯著性水平。按以上操作后輸出內(nèi)容如圖5-4?？梢愿鶕?jù)其中的P值判斷，也可以根據(jù)t計算值與臨界值的比較來判定，其中若P值小于0.05或t計算值大于臨界值，則說明兩樣本的均值存在顯著差異，反之則認為兩樣本間的均值差異不具備統(tǒng)計顯著性。圖5-4t-檢驗雙樣本平均差檢驗雙樣本平均差檢驗區(qū)別于上述兩種檢驗的特點在于，兩個樣本的總體方差已知，由于這點的局限性，其實際運用意義不大。其功能對話框如圖5-5，對話框內(nèi)容區(qū)別于t-檢驗就是對已知兩個樣本方差的輸入。圖5-5第十節(jié)回歸與規(guī)劃求解回歸分析在試驗設計數(shù)據(jù)處理有非常重要的作用，例如正交設計、均勻設計、配方設計、復合設計都需要通過回歸分析來尋找因素與響應變量間的關系，而EXCEL的數(shù)據(jù)分析工具庫中就提供了回歸分析的工具。通過回歸分析，一般會得響應變量間的擬合方程，那么怎樣根據(jù)擬合方程來確定最優(yōu)試驗條件工具庫中的規(guī)劃求解工具。到因素與呢？這時就可以使用數(shù)據(jù)分析回歸分析在進行回歸分析為了解某指標Y與因素X1、X2之間的據(jù)結果輸入EXCEL表中如圖10-1。前，須將試驗結果數(shù)據(jù)按規(guī)定的格式輸入EXCEL表格中?，F(xiàn)假設影響關系，進行了10次試驗，試驗數(shù)圖10-1試驗數(shù)據(jù)輸入完成后，在數(shù)據(jù)分析工具庫中選擇回歸，出現(xiàn)如圖10-2的對話框，對話框內(nèi)容如下：圖10-21．Y值輸入?yún)^(qū)域：選擇響應變量試驗結果數(shù)據(jù)所在的區(qū)域，可以包含標志。2．X值輸入?yún)^(qū)域：選擇因素取值數(shù)據(jù)所在的區(qū)域，可以包含標志。.3．如果選擇數(shù)據(jù)時包含了標志則選擇標志復選框。4．如果強制擬合線通過坐標系原點則選擇常數(shù)為零復選框。5．置信度：分析置信度，一般選擇95%。6．輸出選項：根據(jù)需要選擇分析結果輸出的位置。7．殘差選項：根據(jù)需要可選擇分析結果中包含殘差和標準殘差以及殘差圖與線形擬合圖。8．如果希望輸出正態(tài)概率圖則選擇響應得復選框。為了分析圖10-1中的數(shù)據(jù)，按圖10-2的內(nèi)容輸入對話框中，分析的數(shù)據(jù)結果如圖10-3、10-4，圖形結果如圖10-5、10-6所示。圖10-3圖10-4圖10-5圖10-6規(guī)劃求解假設現(xiàn)經(jīng)試驗設計及數(shù)據(jù)分析后得出，某試驗指標Y與變量X1、X2之間的回歸-46X*X-13X-35X，且擬合很好，現(xiàn)需確定最優(yōu)的12擬合方程為Y=150+20X+30X212212試驗條件。根據(jù)要求用極值法確定最優(yōu)條件，可得方程組如下：20-46X-26X=02130-46X-70X=012首先在EXCEL工作表中設計輸入如圖10-7的內(nèi)容，其中C3=B3，B2=20-46*D3-26*D2，B3==30-46*，D2、D-70*D33為求解值放置的單元格。其中C3=B3的意義是選擇B3中的公式為目標函數(shù)，另外的函數(shù)為約束條件。圖10-7選擇