用Excel软件解决数理统计问题

1、实验六 用Excel软件解决数理统计问题一 实验目的学习、掌握用Excel中求置信区间，作假设检验，作方差分析和回归分析.二 实验的准备在微软Office的Excel中有许多函数用于数据处理, 其中有些涉及数理统计, 使用非常方便. Excel在原安装中可能没有“数据分析”菜单，建立“数据分析”的步骤是：由“工具”菜单中选择“加载宏”，在弹出的加载宏对话框中选定“分析工具库”和“分析数据库VBA函数”，确定后“工具”菜单中增加了“数据分析”子菜单. 其中有“描述统计”，“协方差”，“相关系数”，“回归”，“方差分析”，“Z检验”，“T检验”，“F检验”等工具.三 实验内容1. 一般统计a) 平

2、均数Excel计算平均数用AVERAGE函数，其格式如下：=AVERAGE(数据1，数据2，数据30)例如输入=AVERAGE(1,2,3,4,5)则得到平均数3. 若要得到位于工作表中E3至E12这组数据的平均数，则输入=AVERAGE(E3：E12)b) 样本标准差样本标准差的定义是Excel计算样本标准差的函数是STDEV，其格式如下=STDEV(数据1，数据2，数据30)例如输入=STDEV(3,5,6,4,6,7,5)则得到这组数据的样本标准差1.35. 输入=STDEV(E3：E12)则得到位于E3至E12的这组数据的样本标准差.c) 样本方差样本方差的定义是Excel计算样本方差

3、使用VAR函数，格式为=VAR(数据1，数据2，数据30)例如输入=VAR(3,5,6,4,6,7,5)则得到这组数据的样本方差1.81. 输入=VAR(E3：E12)则得到位于E3至E12的这组数据的样本方差.2. 区间估计a) 估计均值 已知方差, 估计均值时, 使用函数CONFIDENCE, 它的格式是:CONFIDENCE (显著性水平, 总体标准差, 样本容量)计算结果是. 再用样本均值加减这个值, 即得总体均值的置信区间. 如果已知方差, 则先用函数SQRT计算平方根, 得标准差, 再代入.如果已知一组样本值, 则还要用函数AVERAGE计算样本均值, 然后才能计算置信区间.例1

4、已知样本容量, 总体的标准差, 样本均值.取. 求均值的置信区间.解 在Excel的一个单元 (例如A1) 内输入=CONFIDENCE(0.05, 100, 25)用鼠标点击其它任意单元, 则公式所在单元显示39.19922. 这就是的值. 然后,在另一个单元格中输入=950-A1则显示910.8008. 这是置信区间的左端点. 同样方法可计算置信区间的右端点, 即得均值的置信区间.例2 对某种钢材的抗剪强度进行了10次测试，测得结果如下(单位: MPa)578, 572, 570, 568, 572, 570, 570, 596, 584, 572. 若已知抗剪强度服从正态分布，且,求的9

5、5的置信区间.解 打开Excel的一个新工作表. 在单元格B2，C2，K2内分别输入数据：578，572，570，572. 在单元格B3内输入=AVERAGE(B2: K2)得到输出. 在单元格B4内输入=STDEV(B2: K2)得到输出. 在单元格B5内输入=CONFIDENCE(0.05, 5, 10)得到输出=5.394. 在单元格B6内输入=B3-B5得到置信下限为572.101，在单元格B7内输入=B3+B5得到置信上限为578.299. 因此置信区间为(572.101, 578.299)未知方差, 估计均值时, 没有这样的可以直接计算的函数, 需要一步一步计算.例3 设总体服从正

6、态分布. 已知样本容量, 样本均值，样本标准差. 取. 求均值的区间估计.解 打开Excel的一个新工作表. 先用函数TINV求T分布的分位点, 它的格式是TINV(显著性水平,自由度)在单元格B2内输入=TINV(0.05, 15)则这个单元将显示2.131451. 这就是的值. 在单元格B3内输入=B2*6.2022/SQRT(16)显示3.304921. 这是的值.在单元格B4内输入=503.75-B3得到置信下限为500.4451, 在单元格B3内输入=503.75+B3得到置信上限为507.0549.因此置信区间为(500.4451, 507.0549)例4 在例2中，设方差未知，求

7、的95的置信区间.解 在例2中已经算得, .而样本容量为10. 沿用例2中的工作表. 在单元格E4中输入=TINV(0.05, 9)得到=2.26216, 在单元格E5中输入=E4*B4/SQRT(10)得到=6.22539, 在单元格E6中输入=B3-E5得到置信下限为568.975，在单元格E7中输入=B3+E5得到置信上限为581.425.因此置信区间为(568.975, 581.425).注意: TINV()给出的是T分布的上分位点.b) 估计方差 估计方差时，要用到分布或F分布 求分布的上分位点的函数为CHIINV, 它的格式为=CHIINV(或者,自由度)例5 设总体服从正态分布.

8、 已知样本容量. 样本标准差. 取. 求总体方差的区间估计.解 打开Excel的一个新工作表，在单元格B2中输入=CHIINV(0.025,8)显示17.53454(). 在单元格C2中输入=CHIINV(0.975,8)显示2.179725(). 然后用公式计算置信区间. 在单元格B3中输入=8*0.0072/B2显示0.00002236,在单元格C3中输入=8*0.0072/C2显示0.0001798,因此总体方差的置信区间为(0.00002236,0.0001798).此外, 函数FINV可以计算F分布的上分位点, 从而求方差比的置信区间.3. 假设检验a) 单个正态总体方差未知时均值的

9、t检验由于没有一个函数一次完成单个正态总体方差未知时均值的检验，需要分几步计算.所用的检验统计量为可以用一般统计中介绍的方法计算检验统计量T的观察值，再用区间估计中介绍的方法得到T分布的上分位点(双边检验时)，比较统计量T的观察值t和T分布的上分位点(拒绝域为：),便可得到检验结果.例6 设某一引擎制造商新生产某一种引擎，将生产的引擎装入汽车内进行速度测试，得到行使速度如下：250 238 265 242 248258 255 236 245 261254 256 246 242 247256 258 259 262 263该引擎制造商宣称引擎的平均速度每小时高于250km，请问样本数据在显著

10、性水平为0.025时是否和他的声明相抵触？解 (1) 打开Excel的一个新工作表. 在单元格B3：F6输入样本数据，如下表ABCDEFG1引擎速度测试2325023826524224842582552362452615254256246242247625625825926226378平均速度252.059标准差8.6418510样本数201112t值1.0608713值2.093(2)计算样本平均速度，在单元格D8中输入公式：AVERAGE(B3:F6)得到平均速度252.05.(3)计算标准差，在单元格D9中输入公式：STDEV(B3:F6)得到标准差8.64185.(4)在单元格D10中

11、输入样本数20.(5)在单元格D12中输入T检验值的计算公式：(D8-250)/(D9/SQRT(D10)得到t的值为1.06087.(6)在单元格D13中输入公式TINV(0.05,19)得到的值为2.093.现在的检验问题是：; .拒绝域为,由上面的计算得到,因此检验的结果是不拒绝原假设. 即无充分证据显示支持引擎制造商声明.b) 两个正态总体方差相等时均值差的t检验为检验两个正态总体方差相等(但未知)时均值之差的假设：所用的检验统计量为(自由度为-2的t分布)Excel在计算时，使用“工具”，“数据分析”，“t-检验：双样本等方差假设”，就得到输出结果.例7 某化工试验中要考虑温度对产品

12、断裂韧度的影响，在，条件下分别作了8次重复试验，侧得断裂韧度的数据如下：(单位：Mpa/m)时 20.5 18.8 19.8 20.9 21.5 19.5 21.0 21.2时 17.7 2.03 20.0 18.8 19.0 20.1 20.2 19.1断裂韧度可以认为服从正态分布. 若已知两种温度的方差相等，1 问数学期望是否可以认为相等()？2 求两种温度时的数学期望差的置信区间().解 1.(1) 打开Excel的一个新工作表. 在单元格A1中输入标记“70oC”，在单元格B1中输入标记“80oC”. 从A2到A9输入70oC时的数据，从B2到B9输入80oC时的数据.(2) 选定“工

13、具”、“数据分析”.(3) 选定“t-检验：双样本等方差假设”.(4) 选择“确定”，显示一个对话框.(5) 在“变量1区域”输入A1：A9.(6) 在“变量2区域”输入B1：B9.(7) 选中“输出区域”，并在框内输入D2，表示输出结果将放置于D2右下方的单元格中.(8) 打开“标志”复选框. 如果在“变量1区域”输入A2：A9，在“变量2区域”输入B2：B9，则不打开“标志”复选框.(9) 在“”内填临界值为0.05.(10) 在“假设平均差”内填0.(11) 选择“确定”，得到结果如下表所示:ABCDEFG170度C80度C220.517.7t-检验: 双样本等方差假设318.820.3

14、419.820变量 1变量 2520.918.8平均20.417.1275621.519方差0.88571430.828571719.520.1观测值888212.02合并方差0.8571429921.219.1假设平均差010df1411t Stat2.160246912P(T<=t) 单尾0.024290113t 单尾临界1.761309214P(T<=t) 双尾0.048580315t 双尾临界2.144788616在单元格E11中，显示统计量t的值为2.160247，而在单元格E15中显示了临界值为2.14479，由于2.160247>2.14479，表示拒绝原假设:

15、 认为两种温度下的数学期望不相等.2.利用上图所示的结果，也可以得到两个正态总体方差未知(但相等)时均值差的区间估计.由于检验统计量,现在已知的值，因此.在单元格H5中输入=(E5-F5)/E11显示0.46291(=)，再在单元格H6中输入=H5*E15显示0.9928442(=),再在单元格H8中输入=E5-F5-H6显示0.0071558(置信下限)，再在单元格H9中输入=E5-F5H6显示1.9928442(置信上限)，因此得到均值差的置信区间为(0.0071558,1.9928442).注解 在本例的Excel输出表中，单元格E12给出了单边检验时的p值：0.0242901，单元格E

16、14给出了双边检验时的p值：0.0485803. P-值的定义是：在原假设成立的条件下，检验统计量取其观察值及比观察值更极端的值(沿着对立假设方向)的概率. P-值也称作“观察”到的显著性水平. P-值越小，反对原假设的证据越强. 通常若P低于5，称此结果为统计显著；若P低于1，称此结果为高度显著.c) 两个正态总体方差是否相等的F检验假设两总体服从正态分布，在均值未知时作两样本方差是否相等的检验：检验统计量为(自由度为()的F分布)Excel在计算时，使用“工具”，“数据分析”，“F-检验：双样本方差”，就得到输出结果.例8 由一台自动机床加工某型号零件，现在分别从同一月份上旬和下旬的产品中

17、随意各取若干件，测定其直径，得如下数据(单位:mm)上旬产品：20.5 19.8 19.7 20.4 20.1 20.0 19.0 19.9下旬产品：19.7 20.8 20.5 19.8 19.4 20.6 19.2假设刀具磨损是引起变化的唯一原因. 问检验结果是否表明加工精度显著降低了(0.05)?解 (1) 打开Excel的一个新工作表. 在单元格A1输入“上旬产品”，在单元格B1输入“下旬产品”. 从单元格A2至A9输入上旬产品的数据，从单元格B2至B8输入下旬产品的数据.(2) 选取“工具”、“数据分析”(3) 选取“F-检验：双样本方差”，选择“确定”.(4) “在变量1的区域”输

18、入A1：A9.(5) “在变量2的区域”输入B1：B8.(6) 选中“输出区域”，并在框内输入D2，表示输出结果将放置于D2右下方的单元格中.(7) 打开“标志”复选框. 如果在“变量1区域”输入A2：A9，在“变量2区域”输入B2：B8，则不打开“标志”复选框.(8) 在“”内填临界值为0.05.(9) 选择“确定”，得到结果如下表:ABCDEFG1上旬产品下旬产品220.519.7F-检验 双样本方差分析319.820.8419.720.5上旬产品下旬产品520.419.8平均19.92520620.119.4方差0.2164290.39666772020.6观测值8781919.2df7

19、6919.9F0.54561810P(F<=f) 单尾0.22309811F 单尾临界0.25866812计算出的F值为0.455618(=),注意单元格E11中给出的“F单尾临界”值为0.258668,它是的查表值. 因为0.455618>0.258668，所以不拒绝原假设. 因此检验结果认为下旬产品的加工精度未显著降低.4. 单因素方差分析单因素方差分析的试验数据表如下试验批号因素水平 1 2 j 行平均 单因素方差分析的计算结果可列成单因素方差分析表差异源偏差平方和SS自由度df均方MSF的值FF的临界值组间F >时拒绝组内总计用Excel作单因素方差分析的步骤见下例.

20、例9 对三位同学的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) 选择“确

21、定”，显示“单因素方差分析”对话框.(5) 在“输入区域”框输入A1:C5.(6) 在“分组方式”框选定“逐列”.(7) 选中“标志位于第一行()”.(8) 显著性水平“”采用0.05.(9) 在输出选项中选中“输出区域”，在“输出区域”框中输入A7.(10) 选择“确定”，输出结果如下表所示.ABCDEFG1同学甲同学乙同学丙213.81414.231414.214.2414.113.914513.81414.167方差分析：单因素方差分析89SUMMARY10组计数求和平均方差11同学甲455.713.9250.022512同学乙456.114.0250.01583313同学丙456.51

22、4.1250.009167141516方差分析17差异源SSdfMSFP-valueF crit18组间0.0820.042.5263160.1346424.25649219组内0.142590.0158332021总计0.222511单元格A16：G21中显示的是方差分析表. 17行还有一些符号没有汉化. 符号“df”表示“自由度”，“SS”表示“偏差平方和”，“MS”表示“方差”，“F”为统计量F的值，“P-value"为统计量F的P-值. “F crit”为统计量F的临界值.从方差分析表知：的临界值. 因计算所得的统计量F的值,故接收原假设. 不认为三个同学的100m成绩有显著

23、不同.5. 无重复双因素方差分析双因素无重复试验是不能区分交互作用的. 双因素无重复试验方差分析的试验数据表如下因素B因素A 双因素无重复方差分析的计算结果可列成表方差来源平方和SS自由度df均方MSF 比: F结论行间(因素A)SSRr-1MSR=SSR/(r-1)>(r-1),(r-1)(c-1)时拒绝列间(因素B)SSCc-1MSC=SSC/(c-1)>(c-1),(r-1)(c-1)时拒绝误差SSE(r-1)(c-1)MSE=SSE/(r-1)(c-1)总和SSrc-1用Excel作双因素方差分析的步骤是:（1） 打开Excel后在选定的工作表中设定和输入数据阵. （2）

24、选取“工具”、“数据分析”.（3） 选定“方差分析：无重复双因素分析”选项.（4） 填写“输入区域”框.（5） 打开“标记”复选框(O).（6） 填写显著性水平(A)的值.（7） 填写“输出区域”框.（8） 选择“确定”，得到输出结果.例10 某产品在不同季度和地区的销售量见下表销售量/台地 区1234季度一二三四118120115118200205200202150148148148140135138136分析季度和地区两因素对结果有无显著影响().解 (1)在单元格B1, C1, D1, E1分别输入地区1，地区2，地区3，地区4. 在单元格A2, A3, A4, A5分别输入季度一, 季

25、度二, 季度三, 季度四.(2)在单元格B2, C2, D2, E2分别输入季度一的数据118,200,150,140; 在单元格B3, C3, D3, E3分别输入季度二的数据120,205,148,135; 在单元格B4, C4, D4, E4分别输入季度三的数据115,200,148,138; 在单元格B5, C5, D5, E5分别输入季度四的数据118,202,148,136. (3) 选取“工具”、“数据分析”.(4) 选定“方差分析：无重复双因素分析”.(5) 在“输入区域”框填写A1:E5.(6) 选中“标记”复选框(有对勾).(7) 选定显著性水平的值:0.05.(8) 在输

26、出选项中选中输出区域(O):A8.(9) 选择“确定”，得到输出结果:ABCDEFG8方差分析：无重复双因素分析910SUMMARY计数求和平均方差11季度一46081521202.66712季度二46081521379.33313季度三4601150.251290.91714季度四460415113081516地区14471117.754.2517地区24807201.755.58333318地区34594148.5119地区44549137.254.916667202122方差分析23差异源SSdfMSFP-valueF crit24行8.687532.8958330.6758510.58

27、83343.86253925列15504.1935168.0631206.164.88E-123.86253926误差38.562594.2847222728总计15551.4415由输出结果行间产生的离差平方和为8.6875(单元格B24), 自由度为3(单元格C24), 所以均方和MSR=8.6875/3=2.895833(单元格D24), F检验的统计量的值为0.675851, 这个检验统计量的P值为0.588334(单元格E24), 它太大了. 而临界值为3.862539(单元格G24), 因此不否定原假设: 季度这个因素(行间)对结果无显著差异.又由列间产生离差平方和为15504.1

28、9(单元格B25), 自由度为3(单元格C25), 均方和MSC=15504.19/3= 5168.063(单元格D25), F检验的统计量的值为1206.16(单元格E25), 检验统计量的P值为4.88(单元格F25), 而临界值为3.862539(单元格G25), 因此强烈地否定原假设: 地区这个因素(列间)对结果有显著差异.6. 一元线性回归在理解了一元线性回归的概念以后，可以用Excel直接进行回归分析. 因此避免了复杂的计算过程. 例1较详细地说明了作线性回归的方法和步骤.例11 在钢线碳含量对于电阻的效应的研究中，得到以下的数据碳含量x(%)0.10 0.30 0.40 0.55

29、 0.70 0.80 0.95电阻y(20时，微欧) 15 18 19 21 22.6 23.8 26(1)画出散点图. (2)求线性回归方程. (3)求的方差的无偏估计. (4)检验假设,. (5)若回归效果显著，求b的置信水平为0.95的置信区间.解 (1) 打开Excel的一个新工作表. 在单元格A1输入标记“碳含量”，在单元格B1输入标记“电阻”. 从单元格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) 选中“标志L”，不选中“常数为零”.(8) 选中“置信度F”，在框内确定置信度为95%.(9) 选中“输出区域O”，在框内填入A10.(10) 选中“线性拟合图”.(11) 选择“确定”，得到如下的输出表：ABCDEFGHI1含碳量x电阻y20.11530.31840.41950.552160.722.670.823.880.9526910SUMMAR