excel与数理统计

152Excel与数理统计一实验目的学习、掌握用Excel中求置信区间，作假设检验，作方差分析和回归分析.二实验的准备在微软Office的Excel中有许多函数用于数据处理,其中有些涉及数理统计,使用非常方便.Excel在原安装中可能没有“数据分析”菜单，建立“数据分析”的步骤是：由“工具”菜单中选择“加载宏”，在弹出的加载宏对话框中选定“分析工具库”和“分析数据库－VBA函数”，确定后“工具”菜单中增加了“数据分析”子菜单.其中有“描述统计”，“协方差”，“相关系数”，“回归”，“方差分析”，“Z－检验”，“T－检验”，“F－检验”等工具.三实验内容1.一般统计a)平均数Excel计算平均数用AVERAGE函数，其格式如下：=AVERAGE(数据1，数据2，…，数据30)例如输入=AVERAGE(1,2,3,4,5)则得到平均数3.若要得到位于工作表中E3至E12这组数据的平均数，则输入=AVERAGE(E3：E12)b)样本标准差样本标准差的定义是1)(2nxxsiExcel计算样本标准差的函数是STDEV，其格式如下=STDEV(数据1，数据2，…，数据30)例如输入=STDEV(3,5,6,4,6,7,5)则得到这组数据的样本标准差1.35.输入=STDEV(E3：E12)则得到位于E3至E12的这组数据的样本标准差.c)样本方差样本方差的定义是1531)(22nxxsiExcel计算样本方差使用VAR函数，格式为=VAR(数据1，数据2，…，数据30)例如输入=VAR(3,5,6,4,6,7,5)则得到这组数据的样本方差1.81.输入=VAR(E3：E12)则得到位于E3至E12的这组数据的样本方差.2.区间估计a)估计均值已知方差,估计均值时,使用函数CONFIDENCE,它的格式是:CONFIDENCE(显著性水平,总体标准差,样本容量)计算结果是nz2/.再用样本均值加减这个值,即得总体均值的置信区间.如果已知方差,则先用函数SQRT计算平方根,得标准差,再代入.如果已知一组样本值,则还要用函数AVERAGE计算样本均值,然后才能计算置信区间.例1已知样本容量25n,总体的标准差100,样本均值950X.取05.0.求均值的置信区间.解在Excel的一个单元(例如A1)内输入=CONFIDENCE(0.05,100,25)用鼠标点击其它任意单元,则公式所在单元显示39.19922.这就是nz2/的值.然后,在另一个单元格中输入=950-A1则显示910.8008.这是置信区间的左端点.同样方法可计算置信区间的右端点,即得均值的置信区间.例2对某种钢材的抗剪强度进行了10次测试，测得结果如下(单位:MPa)578,572,570,568,572,570,570,596,584,572.若已知抗剪强度服从正态分布),(2N，且252,求的95％的置信区间.解打开Excel的一个新工作表.在单元格B2，C2，…，K2内分别输入数据：578，572，570，…，572.在单元格B3内输入=AVERAGE(B2:K2)得到输出2.575X.在单元格B4内输入=STDEV(B2:K2)得到输出702.8S.在单元格B5内输入=CONFIDENCE(0.05,5,10)得到输出nz2/=5.394.在单元格B6内输入154=B3-B5得到置信下限为572.101，在单元格B7内输入=B3+B5得到置信上限为578.299.因此置信区间为(572.101,578.299)未知方差,估计均值时,没有这样的可以直接计算的函数,需要一步一步计算.例3设总体服从正态分布.已知样本容量16n,样本均值75.503X，样本标准差2022.6S.取05.0.求均值的区间估计.解打开Excel的一个新工作表.先用函数TINV求T分布的分位点,它的格式是＝TINV(显著性水平,自由度1n)在单元格B2内输入=TINV(0.05,15)则这个单元将显示2.131451.这就是)15()1(025.02/tnt的值.在单元格B3内输入=B2*6.2022/SQRT(16)显示3.304921.这是nSnt)1(2/的值.在单元格B4内输入=503.75-B3得到置信下限为500.4451,在单元格B3内输入=503.75+B3得到置信上限为507.0549.因此置信区间为(500.4451,507.0549)例4在例2中，设方差未知，求的95％的置信区间.解在例2中已经算得2.575x,702.8S.而样本容量为10.沿用例2中的工作表.在单元格E4中输入=TINV(0.05,9)得到)9()1(025.02/tnt=2.26216,在单元格E5中输入=E4*B4/SQRT(10)得到nSnt)1(2/=6.22539,在单元格E6中输入=B3-E5得到置信下限为568.975，在单元格E7中输入=B3+E5得到置信上限为581.425.因此置信区间为(568.975,581.425).注意:TINV(n,)给出的是T分布的上2/分位点.b)估计方差估计方差时，要用到2分布或F分布求2分布的上分位点的函数为CHIINV,它的格式为155=CHIINV(2/或者2/1,自由度1n)例5设总体服从正态分布.已知样本容量9n.样本标准差007.0S.取05.0.求总体方差的区间估计.解打开Excel的一个新工作表，在单元格B2中输入=CHIINV(0.025,8)显示17.53454()8(2025.0).在单元格C2中输入=CHIINV(0.975,8)显示2.179725()8(2975.0).然后用公式)1()1(,)1()1(22/1222/2nSnnSn计算置信区间.在单元格B3中输入=8*0.007^2/B2显示0.00002236,在单元格C3中输入=8*0.007^2/C2显示0.0001798,因此总体方差的置信区间为(0.00002236,0.0001798).此外,函数FINV可以计算F分布的上分位点,从而求方差比的置信区间.3.假设检验a)单个正态总体方差未知时均值的t检验由于没有一个函数一次完成单个正态总体方差未知时均值的检验，需要分几步计算.所用的检验统计量为nS/XT0可以用一般统计中介绍的方法计算检验统计量T的观察值，再用区间估计中介绍的方法得到T分布的上2/分位点(双边检验时)，比较统计量T的观察值t和T分布的上2/分位点(拒绝域为：2/||tt),便可得到检验结果.例6设某一引擎制造商新生产某一种引擎，将生产的引擎装入汽车内进行速度测试，得到行使速度如下：250238265242248258255236245261254256246242247256258259262263该引擎制造商宣称引擎的平均速度每小时高于250km，请问样本数据在显著性水平为0.025时是否和他的声明相抵触？解(1)打开Excel的一个新工作表.在单元格B3：F6输入样本数据，如下表ABCDEFG1引擎速度测试2325023826524224842582552362452615254256246242247625625825926226371568平均速度252.059标准差8.6418510样本数201112t值1.0608713t值2.093(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)得到025.0t的值为2.093.现在的检验问题是：250:0H;250:1H.拒绝域为025.0tt,由上面的计算得到093.206087.1025.0tt,因此检验的结果是不拒绝原假设.即无充分证据显示支持引擎制造商声明.b)两个正态总体方差相等时均值差的t检验为检验两个正态总体方差相等(但未知)时均值之差的假设：0210:dH021:dHa所用的检验统计量为2102111)(nnSdxxtw(自由度为21nn-2的t分布)Excel在计算时，使用“工具”，“数据分析”，“t-检验：双样本等方差假设”，就得到输出结果.例7某化工试验中要考虑温度对产品断裂韧度的影响，在C070，C080条件下分别作了8次重复试验，侧得断裂韧度的数据如下：(单位：Mpa/m2)C070时20.518.819.820.921.519.521.021.2C080时17.72.0320.018.819.020.120.219.1断裂韧度可以认为服从正态分布.若已知两种温度的方差相等，1．问数学期望是否可以认为相等(05.0)？2．求两种温度时的数学期望差的置信区间(05.0).解1.(1)打开Excel的一个新工作表.在单元格A1中输入标记“70oC”，在单元格157B1中输入标记“80oC”.从A2到A9输入70oC时的数据，从B2到B9输入80oC时的数据.(2)选定“工具”、“数据分析…”.(3)选定“t-检验：双样本等方差假设”.(4)选择“确定”，显示一个对话框.(5)在“变量1区域”输入A1：A9.(6)在“变量2区域”输入B1：B9.(7)选中“输出区域”，并在框内输入D2，表示输出结果将放置于D2右下方的单元格中.(8)打开“标志”复选框.如果在“变量1区域”输入A2：A9，在“变量2区域”输入B2：B9，则不打开“标志”复选框.(9)在“)(A”内填临界值为0.05.(10)在“假设平均差”内填0.(11)选择“确定”，得到结果如下表所示:ABCDEFG170度C80度C220.517.7t-检验:双样本等方差假设318.820.3419.820变量1变量2520.918.8平均20.417.1275621.519方差0.88571430.828571719.520.1观测值888212.02合并方差0.8571429921.219.1假设平均差010df1411tStat2.160246912P(T=t)单尾0.024290113t单尾临界1.761309214P(T=t)双尾0.048580315t双尾临界2.144788616在单元格E11中，显示统计量t的值为2.160247，而在单元格E15中显示了临界值为2.14479，由于2.1602472.14479，表示拒绝原假设:认为两种温度下的数学期望不相等.2.利用上图所示的结果，也可以得到两个正态总体方差未知(但相等)时均值差的区间估计.158由于检验统计量2111nnSYXTw,现在已知YXT,,的值，因此TYXnnSw2111.在单元格H5中输入=(E5-F5)/E11显示0.46291(=2111nnSw)，再在单元格H6中输入=H5*E15显示0.9928442(=)2(11212/21nntnnSw),再在单元格H8中输入=E5-F5-H6显示0.0071558(置信下限)，再在单元格H9中输入=E5-F5＋H6显示1.9928442(置信上限)，因此得到均值差的置信区间为(0.0071558,1.9928442).注解在本例的Excel输出表中，单元格E12给出了单边检验时的p值：0.0242901，单元格E14给出了双边检验时的p值：0.0485803.P-值的定义是：在原假设成立的条件下，检验统计量取其观察值及比观察值更极端的值(沿着对立假设方向)的概率.P-值也称作“观察”到的显著性水平.P-值越小，反对原假设的证据越强.通常若P低于5％，称