Excel在統(tǒng)計分析中的應(yīng)用教學(xué)課件

1、Excel在統(tǒng)計分析中的應(yīng)用（基本篇）第1頁，共46頁。Excel統(tǒng)計分析工具庫建立Excel 提供了一組強大的數(shù)據(jù)分析工具分析工具庫 ?！胺治龉ぞ邘臁钡陌惭b： 1.在Excel “工具”菜單中，單擊“加載宏”命令。 2. 由“加載宏”對話框中找到“分析工具庫”選項，將其選中，然后單擊“確定”按鈕，即可完成安裝。 3. 安裝完成后在Excel “工具”菜單下就會新增加“數(shù)據(jù)分析”命令。如果在步驟2“加載宏”對話框中沒有找到“分析工具庫”選項，就必須運行“Microsoft office安裝”程序來加載“分析工具庫”。第2頁，共46頁。第3頁，共46頁。第4頁，共46頁。1 描述統(tǒng)計 Descr

2、iptive Statistics 描述統(tǒng)計的任務(wù)就是描述隨機變量的統(tǒng)計規(guī)律性。常用的描述統(tǒng)計量有數(shù)學(xué)期望、方差、協(xié)方差、相關(guān)系數(shù)、矩等。 數(shù)學(xué)期望、方差是最常用的兩個統(tǒng)計量。 描述統(tǒng)計分析簡單。第5頁，共46頁。描述統(tǒng)計分析統(tǒng)計量計算第6頁，共46頁。2 假設(shè)檢驗 Hypothesis Testing 假設(shè)檢驗就是根據(jù)樣本的信息來判斷總體分布是否具有假設(shè)的特征。例如，已知樣本來自正態(tài)總體，那么是否能說明它是來自均值等于 的正態(tài)分布呢？又如，已知兩個相對獨立的樣本分別來自兩個正態(tài)分布，那么是否能說這兩個總體的均值相同，或方差相同？這些都屬于假設(shè)檢驗問題。第7頁，共46頁。 有時為了比較兩種產(chǎn)品

3、、兩種儀器、兩種方法等等的差異性，常在相同的條件下作對比試驗，得到一批成對的觀察值，然后分析觀察數(shù)據(jù)，從而作出推斷。這種方法稱為逐對比較法。在假設(shè)檢驗中利用t統(tǒng)計量進行檢驗，因而稱為基于成對數(shù)據(jù)的t檢驗或成對觀測值的t檢驗。 成對數(shù)據(jù)資料中的成對數(shù)據(jù)不是相互獨立的，而是存在某種聯(lián)系，因而進行成對數(shù)據(jù)平均數(shù)顯著性檢驗時，應(yīng)從成對數(shù)據(jù)入手。2.1 成對數(shù)據(jù)的假設(shè)檢驗第8頁，共46頁。例題：為了分析某種新型減肥藥劑是否對人具有顯著減肥作用，現(xiàn)隨機選取12位自愿者進行試驗，服藥后，間隔1個療程，分別測其12位自愿者的體重見表2。成對數(shù)據(jù)資料的均值檢驗 自愿者編號123456789101112服藥前體重

4、x1331451001561271221421101361661381101個療程后體重y120122105133108110135105122145128108假設(shè)服藥前后，除服用此減肥藥劑外，其余的生活方式、生活條件均未變化。試問根據(jù)此試驗結(jié)果，能否判斷這種新型減肥藥對人具有顯著減肥作用。 本例為典型的成對觀測值t檢驗問題。第9頁，共46頁。（1）利用Excel提供的統(tǒng)計函數(shù)TTEST來檢驗 有兩種檢驗方法：錄入試驗數(shù)據(jù)；插入統(tǒng)計函數(shù)TTEST，單擊確定按鈕，彈出TTEST對話窗口 本例t檢驗所計算的概率值p0.000604，遠遠小于0.01，那么否定H0，接受HA，即服藥前后自愿者的體重

5、有極限著變化，表明減肥藥劑有極限著減肥效果 。第10頁，共46頁。（2）利用Excel2000提供的“t-檢驗：平均值的成對二樣本分析”工具進行分析 先將觀察數(shù)據(jù)輸入工作表中；選擇工具菜單中的數(shù)據(jù)分析命令，彈出數(shù)據(jù)分析對話框； 在分析工具列表中，選擇t-檢驗：平均值的成對二樣本分析工具，單擊確定按鈕，彈出t-檢驗：平均值的成對二樣本分析對話窗口 。第11頁，共46頁。對檢驗結(jié)果分析，可以看出，樣本的t統(tǒng)計量等于4.74566402，大于t0.05/2的雙邊臨界值2.20098627，也大于t0.01/2的雙邊臨界值3.105815（可查也可計算）。即所以拒絕原假設(shè)H0，在置信度為0.01的情況

6、下，服用新型減肥藥劑的自愿者體重有極顯著差異。因此，可得出結(jié)論，這種新型減肥藥劑對人具有減肥作用，效果極顯著。也可以由概率值P的大小來直接判斷。第12頁，共46頁。現(xiàn)在來討論有關(guān)兩個正態(tài)總體均值和方差的假設(shè)檢驗問題。假設(shè)有兩個相互獨立的樣本，分別來自于正態(tài)總體 和 均未知，試從樣本統(tǒng)計量去推斷總體的均值、方差是否相等，即2.2 雙樣本假設(shè)檢驗 現(xiàn)有兩種茶多糖提取工藝，分別從兩種工藝中各取1個隨機樣本來測定其粗提物中的茶多糖含量，結(jié)果見表4。問兩種工藝的粗提物中茶多糖含量有無顯著差異？ 這是一個典型的雙樣本假設(shè)檢驗問題，下面就方差檢驗和均值檢驗分別給予討論。表4 兩種工藝粗提物中茶多糖含量測定結(jié)

7、果第13頁，共46頁。目的是檢驗兩個樣本的方差是否相等 。2.2.1 雙樣本方差的檢驗F檢驗（1）利用統(tǒng)計函數(shù)進行檢驗FTEST有兩種方法：本例計算的概率值P0.9368529120.05，表明雙樣本的方差無顯著差異,也就是說兩種工藝的粗提物中茶多糖含量的方差相等。第14頁，共46頁。（2）利用Excel 2000提供的“F檢驗：雙樣本方差”工具進行檢驗。第15頁，共46頁。對檢驗結(jié)果分析，可知，Excel 2000只提供了F檢驗的單尾臨界值，而本例屬于雙邊假設(shè)檢驗問題，因此需要查找雙尾臨界值。查F分布表得（ =0.05，n1=5，n2=6）：由F檢驗結(jié)果可得，F(xiàn)1.0443040.05,表明

8、兩個樣本的均數(shù)無顯著差異性，即兩種工藝的粗提物中茶多糖含量無顯著差異。第18頁，共46頁。（2） 利用Excel 2000提供的“t檢驗：雙樣本等方差假設(shè)”工具進行檢驗。第19頁，共46頁。對檢驗結(jié)果分析，可知所以接受原假設(shè)H0，即兩種工藝的粗提物中茶多糖含量無顯著差異。第20頁，共46頁。在以下的情況中，可以利用Z 檢驗分析工具來檢驗兩個總體均值之間是否存在差異。 總體方差及標準差已知，不論樣本數(shù)的大小為多少，我們都可以使用Z檢驗來進行總體平均差（12）的假設(shè)檢驗。 當總體方差及標準差未知的情況，但所抽取的樣本數(shù)大于30時，我們也可以使用Z檢驗來進行總體平均差的假設(shè)檢驗。2.3 Z 檢驗：雙

9、樣本均值分析一般而言，在兩總體方差為已知，總體平均差（12）的統(tǒng)計量，可以用下面的公式求得：第21頁，共46頁。若兩總體方差為未知，總體平均差（12 ）的統(tǒng)計量，可以用下面的公式求得： 當兩總體的樣本數(shù)量小于30時，或者是無法得知兩總體的方差和標準差值時，可以使用t檢驗。 在兩總體方差為未知的情況下，則小樣本總體平均差（12 ）的檢驗統(tǒng)計量為：也就是說，當兩總體方差為已知時，我們使用Z分布來進行檢驗，若總體方差未知時，我們使用t分布來檢驗。第22頁，共46頁。方差分析 Analysis of Variance（ANOVA）3.1 單因素試驗方差分析科學(xué)試驗中僅僅考察一個因素的試驗，稱為單因素試

10、驗。單因素試驗是最簡單的科學(xué)試驗，也是最常用的預(yù)備試驗設(shè)計方法。 例如，以淀粉為原料生產(chǎn)葡萄糖過程中，殘留的許多糖蜜可用于醬色生產(chǎn)。在生產(chǎn)醬色之前應(yīng)盡可能徹底除雜，以保證醬色質(zhì)量。為此對除雜方法進行選擇。今選用5種除雜方法，每種方法做4次試驗，試驗結(jié)果見表5，試分析不同除雜方法的除雜效果有無差異？設(shè)各總體服從正態(tài)分布，且方差相等。第23頁，共46頁。除雜方法（Ai）除雜量（xij）A125.624.425.025.9A227.827.027.028.0A327.027.727.525.9A429.027.327.529.9A520.621.222.021.2本研究的試驗指標是除雜量，除雜方法為

11、試驗因素，不同的5種除雜方法就是試驗因素的五個不同水平。假定除了除雜方法這一試驗因素外，其余的一切試驗條件都相同。這就是單因素試驗。試驗的目的是要分析5種不同除雜方法的除雜效果有無顯著的差異，以確定最佳除雜方法。這是典型的單因素試驗方差分析問題 。第24頁，共46頁。借助于Excel單因素方差分析工具進行分析 第25頁，共46頁。對統(tǒng)計結(jié)果進行分析，可以看出，單元格區(qū)域A13:G17中的數(shù)據(jù)與單因素試驗方差分析表中的各個統(tǒng)計量相對應(yīng)，其中組間為因素A，組內(nèi)為誤差e，總計為總和，差異源為方差來源，SS為平方和，df為自由度，MS為均方（方差），F(xiàn)為F值，P-value為接受原假設(shè)H0的概率（此P

12、值越接近0，說明接受原假設(shè)的可能性就越小，反之亦然），F(xiàn) crit為拒絕域的臨界值方差分析結(jié)果可見F=49.55323F0.01（d4，15）4.89F0.05（4，15） =3.055568，所以，在顯著性水平0.01下拒絕原假設(shè)H0，認為5種不同除雜方法的除雜效果有極顯著差異。但哪幾個方法差異顯著，哪幾個方法差異不顯著，尚需進一步進行多重比較分析，但Excel分析工具尚不能自動完成。由平均數(shù)大小可以初步判斷A4方法的除雜效果理想。 第26頁，共46頁。3.2 雙因素試驗方差分析如果在試驗中只有兩個因素在改變，而其他因素保持不變，則稱為雙因素試驗。雙因素試驗的方差分析就是觀察兩個因素的不同水

13、平對研究對象的影響是否有顯著性差異。根據(jù)是否考慮兩個因素的交互作用，又將雙因素方差分析分為雙因素有重復(fù)試驗的方差分析和雙因素無重復(fù)試驗的方差分析。3.2.1 雙因素有重復(fù)試驗的方差分析 例如，在生產(chǎn)某種金屬材料時，使用了四種原料、三種熱處理溫度。對于每種原料與每種熱處理溫度的組合各生產(chǎn)兩次，產(chǎn)品強度的測定結(jié)果如圖19所示。問原料、處理溫度以及這兩者的交互作用對產(chǎn)品強度是否有顯著的影響（取顯著性水平=0.05）？第27頁，共46頁。第28頁，共46頁。利用Excel 2000提供的方差分析：可重復(fù)雙因素分析工具分析 第29頁，共46頁。樣本為因素A，列為因素B，交互為因素A與B的交互作用，內(nèi)部為

14、誤差，總計為總和，差異源為方差來源，SS為平方和，df為自由度，MS為均方，F(xiàn)為F值，P-value為接受原假設(shè)H0的概率，F(xiàn) crit為拒絕域的臨界值Fa（u，v）。 在顯著性水平0.05下，原料和處理溫度這兩個因素對產(chǎn)品強度的影響都是顯著的，且FI =14.9288遠大于=2.9961，故兩者的交互作用效應(yīng)是高度顯著的。第30頁，共46頁。3.2.2 雙因素無重復(fù)試驗的方差分析某廠現(xiàn)有化驗員3人，擔任該廠牛奶酸度（T）的檢驗。每天從牛奶中抽樣一次進行檢驗，連續(xù)10天的檢驗分析結(jié)果見表10。試分析3名化驗員的化驗技術(shù)有無差異，以及每天的原料牛奶酸度有無差異?；瀱TB1B2B3B4B5B6B7

15、B8B9B10A111.7110.8112.3912.5610.6413.2613.3412.6711.2712.68A211.7810.712.512.3510.3212.9313.8112.4811.612.65A311.6110.7512.412.4110.7213.113.5812.8811.4612.94利用Excel2000提供的“方差分析：無重復(fù)雙因素分析”工具分析 第31頁，共46頁。第32頁，共46頁。4 回歸分析 Regression Analysis設(shè)所研究的對象 受多個因素 的影響，假定影響因素與 的關(guān)系是線性的，則可建立多元線性回歸數(shù)學(xué)模型： 式中 代表影響因素，通常

16、是可以控制或預(yù)先給定的，故稱為解釋變量或自變量； 代表各種隨機因素對 的影響的總和，稱為隨機誤差項，根據(jù)中心極限定理，可以認為它服從正態(tài)分布，即 為所研究的對象，稱為被解釋變量或因變量 第33頁，共46頁。 例如，在改革中，某食品企業(yè)重視科技人才，提供了足夠的科研經(jīng)費，獲得了良好的經(jīng)濟效益。表11是該食品企業(yè)19871998年的經(jīng)濟效益、科研人員、科研經(jīng)費的統(tǒng)計數(shù)據(jù)。根據(jù)這些統(tǒng)計數(shù)據(jù)，試建立企業(yè)經(jīng)濟效益與科研人員、科研經(jīng)費之間的回歸方程。回歸分析舉例年份經(jīng)濟效益（萬元）科研人員（名）科研經(jīng)費（萬元）年份經(jīng)濟效益（萬元）科研人員（名）科研經(jīng)費（萬元）1987406198.519936323813

17、.71988484249.719946854714.419895042610.419957504916.219905202811.319967945018.519915603112.219978665120.319925913312.819989895325本實例有兩個自變量，科研人員（x1）與科研經(jīng)費（x2），因變量為經(jīng)濟效益（y） 第34頁，共46頁。相關(guān)性分析選擇工具菜單中的數(shù)據(jù)分析命令，彈出數(shù)據(jù)分析對話框。 在分析工具列表框中，選相關(guān)系數(shù)工具，這時將出現(xiàn)相關(guān)系數(shù)對話框。第35頁，共46頁?？蒲腥藛T、科研經(jīng)費和經(jīng)濟效益都有較強的相關(guān)性。因此，需要利用回歸分析工具進一步建立關(guān)系式。 第36

18、頁，共46頁。 選擇工具菜單中的數(shù)據(jù)分析命令，彈出數(shù)據(jù)分析對話框。 在分析工具列表框中，選回歸工具，這時，將彈出回歸對話框， 回歸模型的建立操作步驟如下：第37頁，共46頁。第38頁，共46頁。在回歸統(tǒng)計區(qū)域中，給出的 為0.997644，調(diào)整后的 為0.997121，均很接近1，說明 與 的關(guān)系很密切。 檢驗： 檢驗： 在方差分析區(qū)域，給出的 檢驗值為1905.786遠遠大于 =4.26，說明 與 之間的回歸效果非常顯著。檢驗：回歸系數(shù)的檢驗 科研人員與科研經(jīng)費對該企業(yè)的經(jīng)濟效益有顯著影響。優(yōu)化回歸方程為第39頁，共46頁。5 正交試驗結(jié)果分析在實際工作中，常常需要同時考察3個或3個以上的試

19、驗因素，若進行全面試驗，則試驗的規(guī)模很大，往往因試驗條件的限制而難于實施。對于多因素多水平試驗，一般采用部分實施方法來設(shè)計試驗的，如正交試驗設(shè)計、均勻試驗設(shè)計等。正交試驗設(shè)計就是安排多因素試驗、尋求最優(yōu)水平組合的一種高效率試驗設(shè)計方法。它從試驗的全部水平組合中，挑選部分有代表性的水平組合進行試驗，通過對這部分試驗結(jié)果的分析了解全面試驗的情況，找出最優(yōu)的水平組合。 第40頁，共46頁。5.1 極差分析試驗結(jié)果極差分析計算K值計算k值計算極差R繪制因素指標趨勢圖優(yōu)水平因素主次順序優(yōu)組合結(jié) 論第41頁，共46頁。試驗號因 素ABCD液化率 %11（10）1（1）1（20）1（1.5）0212（4）2

20、（35）2(2.5)17313（7）3（50）3(3.5)2442（50）123125223147623122873（90）132183213189332142K141134689K2 87827146K361947254k113.74.315.329.7k22927.323.715.3k320.331.32418優(yōu)水平A2B3C3D1Rj15.3278.714.4主次順序B A D C根據(jù)極差Rj的大小，可以判斷各因素對試驗指標的影響主次。比較各R值大小，可見RBRARDRC，所以因素對試驗指標影響的主次順序是BADC。即加酶量影響最大，其次是加水量和酶解時間，而酶解溫度的影響較小。第42頁，共46頁。試驗結(jié)果方差分析計算各列偏差平方和、自由度列方差分析表，進行F檢驗分析檢驗結(jié)果，寫出結(jié)論5.2 方差分析第43頁，共46頁。實例：自溶酵母提取物是一種多用途食品配料。為探討啤酒酵母的最適自溶條件，安排三因素三水平正交試驗。試驗指標為自溶液中蛋白質(zhì)含量（）。試驗方案及結(jié)果分析見表。試對試驗結(jié)果