实验 用Excel解决统计问题.doc

6实验 用Excel解决统计问题 一 实验目的 学习、掌握用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) 样本标准差 样本标准差的定义是 Excel计算样本标准差的函数是STDEV，其格式如下 =STDEV(数据1，数据2，…，数据30) 例如输入 =STDEV(3,5,6,4,6,7,5) 则得到这组数据的样本标准差1.35. 输入 =STDEV(E3：E12) 则得到位于E3至E12的这组数据的样本标准差. c) 样本方差 样本方差的定义是 Excel计算样本方差使用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 已知样本容量, 总体的标准差, 样本均值.取. 求均值的置信区间. 解 在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. 若已知抗剪强度服从正态分布，且,求的95％的置信区间. 解 打开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 设总体服从正态分布. 已知样本容量, 样本均值，样本标准差. 取. 求均值的区间估计. 解 打开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中，设方差未知，求的95％的置信区间. 解 在例2中已经算得, .而样本容量为10. 沿用例2中的工作表. 在单元格E4中输入 =TINV(0.05, 9) 得到=2.26216, 在单元格E5中输入 =E4*B4/SQR