用Excel解决数理统计问题

用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，斜率为