用Excel解決數(shù)理統(tǒng)計問題

用Excel解決數(shù)理統(tǒng)計問題在微軟Office的Excel中有許多函數(shù)用于數(shù)據(jù)處理,其中有些涉及數(shù)理統(tǒng)計,使用非常方便。Excel在原安裝中可能沒有“數(shù)據(jù)分析”菜單，建立“數(shù)據(jù)分析”的步驟是：由“工具”菜單中選擇“加載宏”，在彈出的加載宏對話框中選定“分析工具庫”和“分析數(shù)據(jù)庫——VBA函數(shù)”，確定后“工具”菜單中增加了“數(shù)據(jù)分析”子菜單。其中有“描述統(tǒng)計”，“協(xié)方差”，“相關(guān)系數(shù)”，“回歸”，“方差分析”，“Z—檢驗”，“T—檢驗”，“F—檢驗”等工具。常用統(tǒng)計量1．平均數(shù)Excel計算平均數(shù)用AVERGE函數(shù)，其格式如下：=AVERGE（數(shù)據(jù)1，數(shù)據(jù)2，…，數(shù)據(jù)30）例如輸入=AVERGE（1，2，3，4，5）則得到平均數(shù)3，若要得到工作表中位于E3至E12這組數(shù)據(jù)的平均數(shù)，則輸入=AVERGE（E3：E12）2、樣本標(biāo)準(zhǔn)差樣本標(biāo)準(zhǔn)差的定義是Excel計算樣本標(biāo)準(zhǔn)差的函數(shù)是STDEV，其格式如下：=STDEV（數(shù)據(jù)1，數(shù)據(jù)2，…，數(shù)據(jù)30）例如輸入=STDEV（3，5，6，4，7，5）則得到這組數(shù)據(jù)的樣本標(biāo)準(zhǔn)差1.35.輸入=STDEV(E3:E12)則得到工作表中位于E3至E12的這組數(shù)據(jù)的樣本標(biāo)準(zhǔn)差。3、樣本方差樣本方差的定義是Excel計算樣本方差使用VAR函數(shù)，格式為=VAR（數(shù)據(jù)1，數(shù)據(jù)2，…，數(shù)據(jù)30）例如輸入=VAR（3，5，6，4，7，5）則得到這組數(shù)據(jù)的樣本方差1.81.輸入=VAR（E3：E12）則得到工作表中位于E3至E12的這組數(shù)據(jù)的樣本方差。區(qū)間估計估計均值已知方差，估計均值時，使用函數(shù)CONFIDENCE，它產(chǎn)格式是：=CONFIDENCE（顯著性水平α，總體標(biāo)準(zhǔn)差，樣本容量）計算結(jié)果是。再用樣本均值加減這個值，即得總體均值的置信區(qū)間。如果已知方差，則先用函數(shù)SQRT計算平方根，得標(biāo)準(zhǔn)差，再代入。如果已知一組樣本值。則還要用函數(shù)AVERGE計算樣本均值,然后才能計算置信區(qū)間。已知樣本容量，總體的標(biāo)準(zhǔn)差，樣本均值，取.解:在Excel的一個單元(例如A1)內(nèi)輸入=CONFIDENCE(0.05，100，25)用鼠標(biāo)點擊其他任意單元，則公式所在單元顯示39.19922。這就是的值。然后，在另一個單元格中輸入=950-A1則顯示910.8008.這是置信區(qū)間的左端點。同樣方法可計算置信區(qū)間的右端點。即得均值的置信區(qū)間。對某種鋼材的抗剪強度進(jìn)行了10次測試，測得結(jié)果如下（單位：MPa）578,572,570,568,572,570,570,596,584,572若已知抗剪強度服從正態(tài)分布，且，求的95%的置信區(qū)間。解打開Excel的一個新工作表。在單元格B2，C2，…，K2內(nèi)分別輸入數(shù)據(jù)：578，572，570，…，572。在單元格B3內(nèi)輸入=AVERAGE（B2：K2）得到輸出.在單元格B4內(nèi)輸入=STDEV(B2：K2)得到輸出.在單元格B5內(nèi)輸入=CONFIDENCE(0.05，5，10)得到輸出.在單元格B6內(nèi)輸入得到置信下限為572.101,在單元格B7內(nèi)輸入得到置信上限為578.299.因此置信區(qū)間為(572.101，578.299).未知方差，估計均值時，沒有這樣的可以直接計算的函數(shù)，需要一步一步計算。設(shè)總體服從正態(tài)分布，已知樣本容量，樣本均值,樣本標(biāo)準(zhǔn)差取.求均值的區(qū)間估計.解打開Excel的一個新工作表，先用函數(shù)TINV求分布的分位點，它的格式是在單元格B2內(nèi)輸入=TLNV(0.05，15)則這個單元將顯示2.131451.這就是的值，再在單元格B3內(nèi)輸入顯示3.304921。這是的值，在單元格B4內(nèi)輸入得到置信下限為500.4451，再在單元格B5內(nèi)輸入得到置信上限為507.0549.因此置信區(qū)間為(500.4451，507.0549).在例2中，設(shè)方差未知，求的95%的置信區(qū)間。解在例2中已經(jīng)算得，而樣本容量為10。沿用例2中的工作表,在單元格E4中輸入=TLNV(0.05，9)得到，再在單元格E5中輸入=E4*B4/SQRT(10)得到，在單元格E6中輸入得到置信下限為568.975，再在單元格E7中輸入得到置信上限為581.425.因此置信區(qū)間為(568.975，581.425)注意TINV()給出的是T分布的上分位點。2.估計方差估計方差時，要用到分布或F分布。求分布的上分位點的函數(shù)為CHIINV，它的格式為=CHIINV(或者，自由度)例設(shè)總體服從正態(tài)分布，已知樣本容量，樣本標(biāo)準(zhǔn)差。取，求總體方差的區(qū)間估計.解打開Excel的一個新工作表,在單元格B2中輸入=CHIINV(0.025，8)顯示，在單元格C2中輸入=CHIINV(0.975，8)顯示。然后用公式計算置信區(qū)間，在單元格B3中輸入顯示0.00002236，在單元格C3中輸入顯示0.0001798，因此總體方差的置信區(qū)間為(0.00002236，0.0001798).此外，函數(shù)FINV可以計算F分布的上分位點，從而求方差比的置信區(qū)間。假設(shè)檢驗1.單個正態(tài)總體方差未知時均值的t檢驗由于沒有一個函數(shù)一次完成單個正態(tài)總體方差未知時均值的檢驗，需要分幾步計算，所用的檢驗統(tǒng)計量為可以用一般統(tǒng)計中介紹的方法計算統(tǒng)計量T和觀察值，再用區(qū)間估計中介紹的方法得到T分布的上分位點（雙邊檢驗時），比較統(tǒng)計量T的觀察值t和T分布的上分位點（拒絕域為：），便可得到檢驗結(jié)果。例設(shè)某一引擎制造商新生產(chǎn)某一種引擎，將生產(chǎn)的引擎裝入汽車內(nèi)進(jìn)行速度測試，得到行駛速度如下：250238265242248258255236245261254256246242247256258259262263該引擎制造商宣稱引擎的平均速度高于250km/h，請問樣本數(shù)據(jù)在顯著性水平為0.025時是否和他的聲明相抵觸?解(1)打開Excel的一個新工作表，單元格B3：F6輸入樣本數(shù)據(jù)，如下圖（2）計算樣本平均速度，在單元格D8中輸入公式：=AVERAGE（B3：F6）得到平均速度252.05。（3）計算標(biāo)準(zhǔn)差，在單元格D9中輸入公式：=STDEV（B3：F6）得到標(biāo)準(zhǔn)差8.64185。（4）在單元格D10中輸入樣本數(shù)20。（5）在單元格D12中輸入T檢驗值的計算公式：=（D8-250）/（D9/SQRT（D10））得到t的值為1.06087。（6）在單元格D13中輸入公式：=TINV（0.05，19）得到的值為2.093。最后的計算結(jié)果如下表：現(xiàn)在的檢驗問題是：拒絕域為，由上面的計算得到，因此檢驗的結(jié)果是不拒絕原假設(shè)，即無充分證據(jù)顯示支持引擎制造商聲明。2．兩個正態(tài)總體方差相等時均值的t檢驗為檢驗兩個正態(tài)總體方差相等（但未知）時均值之差的假設(shè)：所用的檢驗統(tǒng)計量為Excel在計算時，使用“工具”，“數(shù)據(jù)分析”，“t-檢驗：雙樣本等方差假設(shè)”，就得到輸出結(jié)果。例某化工廠試驗中要考慮溫度對產(chǎn)品斷裂韌度的影響，在70℃，80℃條件下分別作了8次重復(fù)試驗，測得斷裂韌度的數(shù)據(jù)（單位：）70℃80℃時17.72.0320.0斷裂韌度可以認(rèn)為服從正態(tài)分布。若已知兩種溫度的方差相等，問數(shù)學(xué)期望是否可認(rèn)為相等？求兩種溫度時的數(shù)學(xué)期望差的置信區(qū)間。解1.（1）打開Excel的一個新工作表，在單元格A1中輸入標(biāo)記“70度C”，在單元格B1中輸入標(biāo)記“80度C”。從A2到A9輸入70℃時的數(shù)據(jù)，從B2到B9輸入80（2）選定“工具”、“數(shù)據(jù)分析……”（3）選定“t-檢驗：雙樣本等方差假設(shè)”。（4）選擇“確定”，顯示一個對話框。（5）在“變量1區(qū)域”輸入A1：A9。（6）在“變量2區(qū)域”輸入B1：B9。（7）選中“輸出區(qū)域”，并在框內(nèi)輸入D1，表示輸出結(jié)果將放置于從D1開始右下方主單元格中。（8）在“標(biāo)志”復(fù)選框中打上“√”。如果在“變量1區(qū)域”輸入A2：A9，在“變量2區(qū)域”輸入B2：B9，則不選中“標(biāo)志”復(fù)選框。（9）在“”內(nèi)填臨界值為0.05。（10）在“假設(shè)平均差”內(nèi)填0。（11）選擇“確定”，得到結(jié)果如下圖所示。在單元格E10中，顯示統(tǒng)計量t的值為2.160246999，而在單元格E14中顯示了臨界值為2.144786681，由于2.160246999>2.144786681，表示拒絕原假設(shè)，認(rèn)為兩種溫度下的數(shù)學(xué)期望不相等。2．利用上圖所示的結(jié)果，也可以得到兩個正態(tài)總體方差未知（但相等）時均值差的區(qū)間估計。由于檢驗統(tǒng)計量，現(xiàn)在已知，，的值，因此。在單元格H4中輸入顯示，在單元格H5中輸入顯示，在單元格H7中輸入顯示（置信下限），在單元格H9中輸入顯示1.99284331（置信上限），因此得到均值差的置信區(qū)間為（0.00715669，1.99284331）。注解：在本例的Excel輸出表中，單元格E11給出了單邊檢驗時的P-值：0.024290144，單元格E13給出了雙邊檢驗時的P-值：0.048580288，P-值的定義是：在原假設(shè)成立的條件下，檢驗統(tǒng)計量取其觀察值及比觀察值更極端的值（沿著對立假設(shè)方向）的概率。P-值也稱作“觀察”到的顯著性水平。P-值越小，反對原假設(shè)的證據(jù)越強，通常若P低于5%，稱此結(jié)果為統(tǒng)計顯著；若P低于1%，稱此結(jié)果為高度顯著。3．兩個正態(tài)總體方差是否相等的F檢驗假設(shè)兩總體服從正態(tài)分布,在均值未知時作兩樣本方差是否相等的檢驗：檢驗統(tǒng)計量為Excel在計算時，使用“工具”，“數(shù)據(jù)分析”，“F-檢驗：雙樣本方差”，就得到輸出結(jié)果。例由一臺自動機床加工某型號零件，現(xiàn)在分別從同一月份上旬和下旬產(chǎn)品中隨意各取若干件，測定其直徑，得如下數(shù)據(jù)（單位：mm）上旬產(chǎn)品：20.519.819.720.420.120.019.019.9下旬產(chǎn)品：19.720.820.519.819.420.619.2假設(shè)刀具磨損是引起變化的唯一原因，問檢驗結(jié)果是否表明表明精度顯著降低了（）？解（1）打開Excel的一個新工作表，在單元格A1輸入“上旬產(chǎn)品”，在單元格B1輸入“下旬產(chǎn)品”，從單元格A2至A9輸入上旬產(chǎn)品的數(shù)據(jù)，從單元格B2至B8輸入下旬產(chǎn)品的數(shù)據(jù)。（2）選取“工具”、“數(shù)據(jù)分析……”；（3）選取“F-檢驗：雙樣本方差”，選擇“確定”。（4）“在變量1的區(qū)域”輸入A1：A9；（5）“在變量2的區(qū)域”輸入B1：B8；（6）選中“輸出區(qū)域”，并在框內(nèi)輸入D2，表示輸出結(jié)果將放置于D1右下方的單元格中。（7）在“標(biāo)志”復(fù)選框中打上“√”。如果在“變量1區(qū)域”輸入A2：A9，在“變量2區(qū)域”輸入B2：B8，則不選中“標(biāo)志”復(fù)選框。（8）在“”內(nèi)填臨界值為0.05。（9）選擇“確定”，得到結(jié)果如下圖所示。計算出的F值為0.545618（），注意單元格E11中給出的“F單尾臨界”值為0.258668，它是的查表值，因為0.455618>0.258668，所以不拒絕原假設(shè)。因此檢驗結(jié)果認(rèn)為下介產(chǎn)品的加工精度未顯著降低。方差分析單因素方差分析的試驗數(shù)據(jù)如下：試驗批號因素水平12…j…行平均A1…A2………………An單因素方差分析的計算結(jié)果可列成單因素方差分析表：差異源偏差平方和SS自由度df方差MSF的值FF臨界值組間組內(nèi)總計用Excel作單因素方差分析的步驟見下例。例對三個同學(xué)的100m成績進(jìn)行了4次測試，得結(jié)果如下：成績/s測試批次1234同學(xué)甲乙丙13.814.014.214.014.214.214.113.914.013.814.014.1據(jù)此分析這三位同學(xué)的100m成績有無明顯差異？解：（1）打開Excel的一個新工作表，在單元格A1輸入標(biāo)記“同學(xué)甲”，在單元格B1輸入標(biāo)記“同學(xué)乙”，在單元格C1輸入標(biāo)記“同學(xué)丙”，從單元格A2至A5輸入同學(xué)甲的100m成績，從單元格B2至B5輸入同學(xué)乙的100m成績，從單元格C2至C5輸入同學(xué)丙的100m成績。（2）選取“工具”、“數(shù)據(jù)分析…”；（3）選定“單因素方差分析”；（4）選擇“確定”，顯示“單因素方差分析”對話框；（5）在“輸入?yún)^(qū)域”框輸入A1：C5；（6）在“分組方式”框選定“逐列”；（7）選中“標(biāo)志位于第一行（）”；（8）顯著性水平“”采用0.05；（9）在輸出選項中選中“輸出區(qū)域”，在“輸出區(qū)域”框中輸入A7；（10）選擇“確定”，輸出結(jié)果如下圖所示：單元格A16：G21中顯示的是方差分析表，17行還有一些符號沒有漢化；符號“df”表示“自由度”，“SS”表示“偏差平方和”，“MS”表示“方差”，“F”為統(tǒng)計量F的值，“P-value”為統(tǒng)計量F的P-值，“Fcrit”為統(tǒng)計量F的臨界值。從方差分析表知：的臨界值，因計算所得的統(tǒng)計量F的值，故接受原假設(shè)，不認(rèn)為三個同學(xué)的100m成績有顯著不同。一元線性回歸在理解了一元線性回歸的概念以后，可以用Excel直接進(jìn)行回歸分析，因此避免了復(fù)雜的計算過程。例中較詳細(xì)地說明了作線性回歸的方法和步驟。例在鋼線碳含量對于電阻的效應(yīng)的研究中，得到以下數(shù)據(jù)碳含量x(%)0.100.300.400.550.700.800.95電阻y（20℃時）/1518192122.623.826畫出散點圖；（2）求線性回歸方程；（3）求的方差的無偏估計；（4）檢驗假設(shè)；（5）若回歸效果顯著，求的置信水平為0.95的置信區(qū)間。解（1）打開Excel的一個新工作表，在單元格A1輸入標(biāo)記“碳含量x”，在單元格B1輸入標(biāo)記“電阻y”；從單元格A2至A8輸入碳含量的值：0.10，0.30，…，0.95；從單元格B2至B8輸入電阻的值：15，18，…，26。（2）選擇“工具”、“數(shù)據(jù)分析…”；（3）選定“回歸”；（4）選擇“確定”，顯示“回歸”對話框；（5）在“Y值輸入?yún)^(qū)域”輸入B1：B8；（6）在“X值輸入?yún)^(qū)域”輸入A1：A8；（7）選中“標(biāo)志”，不選中“常數(shù)為零”；（8）選中“”，在框內(nèi)確定置信度為95%；（9）選中“輸出區(qū)域0”，在框內(nèi)填入A10；（10）選中“線性擬合圖”；（11）選擇“確定”，得到如下的輸出圖：在上面的輸出表中，省略了“殘差輸出”和“概率輸出”的內(nèi)容。首先單元格A10：B17中的輸出為回歸分析的摘要表，單元格A19：F23中的輸出為線性回歸的方差分析表，符號“df”表示“自由度”，“SS”表示“偏差平方和”，“MS”表示“方差”，“F”為統(tǒng)計量F的值，“SignificanceF”為統(tǒng)計量F的P-值。其次，單元格A25：I27中顯示的是回歸系數(shù)的估計與檢驗?！癈oefficients”表示“系數(shù)”，“Intercept”表示“截距”，“tStat”表示統(tǒng)計量t的觀察值?，F(xiàn)在來回答本題中提出的5個問題：（1）在線性回歸的方差分析表的右邊，可以找到一幅名為“含碳量xLineFitPlot”的圖形，它就是散點圖（見下圖）；（2）從“Coefficients”的下面兩格讀出回歸直線的截距為13.95839，斜率為