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