实训三用Excel进行统计推断

1 实训三实训三 用用 Excel 进行统计推断进行统计推断 目的与要求 理解抽样分布原理 掌握在不同的组织形式下抽取样目的与要求 理解抽样分布原理 掌握在不同的组织形式下抽取样 本的方法 明确抽样标准误差 抽样边际误差之间的区别 重点掌本的方法 明确抽样标准误差 抽样边际误差之间的区别 重点掌 握简单随机抽样组织形式的区间估计方法 会用握简单随机抽样组织形式的区间估计方法 会用 ExcelExcel 软件抽取样软件抽取样 本 进行区间估计 以及确定样本单位数本 进行区间估计 以及确定样本单位数 一 用一 用 ExcelExcel 抽取样本抽取样本 使用 Excel 进行抽样 首先要对各个总体单位进行编号 编号可以按随机 原则 也可以按有关标志或无关标志 具体可参见本书有关抽样的章节 编号 后 将编号输入工作表 案例案例 1212 假定有 100 个总体单位 每个总体单位给一个编号 共有从 1 到 100 个编 号 输入工作表后如图 5 1 所示 图 5 1 总体各单位编号表 输入各总体单位的编号后 可按以下步骤操作 第一步 单击工具菜单 选择数据分析选项 若无数据分析选项 可在工 具菜单下选择加载宏 在弹出的对话框中选择分析工具库 便可出现数据分析 选项 打开数据分析对话框 从中选择抽样 如图 5 2 所示 图 5 2 数据分析对话框 2 第二步 单击抽样选项 弹出抽样对话框 如图 5 3 图 5 3 抽样对话框 第三步 在输入区域框中输入总体单位编号所在的单元格区域 在本例是 A 1 J 10 系统将从 A 列开始抽取样本 然后按顺序抽取 B 列至 J 列 如果 输入区域的第一行或第一列为标志项 横行标题或纵列标题 可单击标志复选 框 第四步 在抽样方法项下 有周期和随机两种抽样模式 周期 模式即所谓的等距抽样 采用这种抽样方法 需将总体单位数除 以要抽取的样本单位数 求得取样的周期间隔 如我们要在 100 个总体单位中 抽取 12 个 则在 间隔 框中输入 8 随机模式 适用于纯随机抽样 分类抽样 整群抽样和阶段抽样 采用 纯随机抽样 只需在 样本数 框中输入要抽取的样本单位数即可 若采用分 类抽样 必须先将总体单位按某一标志分类编号 然后在每一类中随机抽取若 干单位 这种抽样方法实际是分组法与随机抽样的结合 整群抽样也要先将总 体单位分类编号 然后按随机原则抽取若干类作为样本 对抽中的类的所有单 位全部进行调查 可以看出 此例的编号输入方法 只适用于等距抽样和纯随 机抽样 第四步 指定输出区域 在这里我们输入 A 14 单击确定后 即可得到抽 样结果 如图 5 4 图 5 4 等距抽样结果 3 二 二 CONFIDENCECONFIDENCE 边际误差 函数 边际误差 函数 CONFIDENCE alpha standard dev size 返回总体平均值的置信区间 alpha 即 是用于计算置信度的显著水平参数 置信度等于 1 亦即 如果 为 0 05 则置信水平为 0 95 置信度为 1 96 Standard dev 数据区域的总体标准差 假设为已知 实际中 总体标准 差未知时通常用样本标准差代替 Size 样本容量 即 n 如果假设 等于 0 05 则需要计算标准正态分布曲线 1 0 95 之 下的临界值 查表知其临界值为 1 96 因此置信区间为 96 1 n x 使用的主要函数如下图 案例案例 1313 均值的区间估计均值的区间估计 一 正态总体 方差未知 大样本 一 正态总体 方差未知 大样本 某饭店在 7 星期内抽查 49 位顾客的消费额 元 服从正态分布 数据如下 15 24 38 26 30 42 18 30 25 26 34 44 20 35 24 26 34 48 18 28 46 19 30 36 42 24 32 45 36 21 47 26 28 31 42 45 36 24 28 27 32 36 47 53 22 24 32 46 26 求在概率 90 的保证下 顾客平均消费额的估计区间 解 操作步骤如下 第一步 把数据输入到 A1 G7 单元格 第二步 选中 B9 单元格 用鼠标单击工具栏的粘贴函数fx 在 函数分 类 单击 统计 在其右边显示的 函数名 中单击 COUNT 出现图 5 5 对 4 话框 图 5 5 COUNT 函数 在 Value1 中输入或拖入数据区域 A1 G7 单元格 然后单击确定 获得样 本容量 同理 在 B10 中 使用粘贴函数 AVERAGE A1 G7 获得样本平均数 在 B11 中 使用粘贴函数 STDEVA A1 G7 获得样本标准差 在 B12 中输入显著性水平 值 10 在 B13 中 使用粘贴函数 CONFIDENCE 出现图 5 6 对话框 图 5 6 CONFIDENCE 函数 在 Alpha 中输入 值 此为 C12 在 Standard dev 中输入样本标准差值 此为 C11 在 Size 中输入样本容量值 此为 C9 然后单击确定 即获得边际误 差 在 B14 中输入 C10 C13 在 B15 中输入 C10 C13 回车后 便可得 顾客平均消费额的置信下限为 29 80184 置信上限为 34 19816 如图 5 7 5 图 5 7 参数估计数据及结果 或者 在 B9 中输入函数 COUNT A1 G7 获得样本容量 即此例中的 数据个数 在 B10 中输入 AVERAGE A1 G7 获得样本平均数 在 B11 中输入 STDEVA A1 G7 获得样本标准差 在 B12 中输入显著性水平 值 10 在 B13 中输入表达式 CONFIDENCE C12 C11 C9 回车即得到边际 误差 在 B14 中输入 C10 C13 在 B15 中输入 C10 C13 回车后 便可得 顾客平均消费额的置信下限为 29 80184 置信上限为 34 19816 关于总体方差的估计 总体比例的估计等可按类似方法进行 二 正态总体 方差未知 小样本 二 正态总体 方差未知 小样本 某零件加工企业生产一种螺丝钉 对某天加工的零件每隔一定时间抽出一 个 共抽取 12 个 测得其长度 单位 mm 数据如附表中的 A2 A13 假定零 件长度服从正态分布 试以 95 的置信水平估计该企业生产的螺丝钉平均长度 的置信区间 解 为构造区间估计的工作表 我们应在工作表中输入下列内容 A 列输入样本 数据 B 列输入变量名称 C 列输入计算公式 D 列为 C 列的计算结果 当输入 完公式后 即显示 D 列结果 用 Excel 求置信区间 ABCD 1样本数据样本数据计算指标计算指标计算公式计算公式计算结果计算结果 210 94样本数据个数 COUNT A2 A13 12 3 11 91样本均值 AVERAGE A2 A13 11 074167 410 91样本标准差 STDEV A2 A13 0 272746 5 10 94 样本平均值的标 准差 C4 SQRT C2 0 078735 611 03置信水平 0 950 95 710 97自由度 C2 111 811 09t 值 TINV 1 C6 C7 2 200986 911 00 误差范围 C8 C50 173294 1011 16置信下限 C3 C910 900872 1110 94置信上限 C3 C911 2474610 1211 03 1310 97 我们有 95 把握认为该企业生产的螺丝钉的平均长度在 10 900872mm 11 247461mm 之间 对于不同的样本数据 只要输入新的样本数据 再对 C 列公式中的样本数 6 据区域加修改 置信区间就会自动给出 如果需要不同的置信水平 填入相应 的数值即可 方差已知时采用正态分布统计量构造置信区间 此时不用计算样本标准差 直接使用总体标准差 B8 单元格改为 Z 值 C8 单元格改为 NORMSINV 1 C6 2 即可 案例案例 1414 比例的区间估计 比例的区间估计 以某厂对一批产品的质量进行抽样检验为例 抽样数据和要求如下 采用 重复抽样抽取样品 200 只 样本优质品率为 85 试计算当把握程度为 90 时 优质品率的边际误差 我们可以在 EXCEL 中分别在 B1 单元格中输入样本容量 200 B2 单元格中输入样本比率 85 在 B3 单元格中输入计算样本比率的标准差公式 SQRT B2 1 B2 在 B4 单元格输入 为 10 在 B5 单元格中输入表达式 CONFIDENCE B4 B3 B1 回车即得 到等于 4 15 n pp Z 1 2 在 B6 单元格中输入表达式 B2 B5 回车即得到置信下限值 80 85 在 B7 单元格中输入表达式 B2 B5 回车即得到置信上限值 89 15 CONFIDENCE 函数的应用如图 5 8 图 5 8 总体优质品率的区间估计 或用鼠标单击工具栏的粘贴函数fx 在 函数分类 单击 统计 在其 右边显示的 函数名 中单击 CONFIDENCE 在 Alpha 中输入 值 此为 B4 在 Standard dev 中输入样本标准差值 此为 B3 在 Size 中输入样本容量 值 此为 B1 然后单击确定 即获得边际误差 案例案例 1515 估计总体必要的样本容量 估计总体必要的样本容量 7 一 估计均值的必要样本容量 一 估计均值的必要样本容量 某县进行农村经济情况调查 已知农户平均年收入标准差为 30 元 要求把 握程度 置信度 为 95 45 抽样边际误差为 5 元 计算应抽取的样本户数 操作步骤 操作步骤 1 建立 样本容量工作表 如图 5 9 中 A 列 2 在单元格 B1 B2 中分别输入抽样边际误差 5 和置信度 95 45 3 选中单元格 B3 在编辑栏中输入 NORMSINV B2 回车后单元格 B3 显示与置信度 95 45 对应的 Z 值 1 690145837 4 在单元格 B4 中输入标准差 30 5 选中单元格 B5 在编辑栏中输入样本容量公式 B3 2 B4 2 B1 2 回车后单元格 B5 显示 102 8373462 6 选中单元格 B6 在编辑栏中输入样本容量取整公式 CEILING B5 1 回车后单元格 B6 显示 103 计算结果如图 5 9 图 5 9 二 估计总体比例 成数 的必要样本容量 二 估计总体比例 成数 的必要样本容量 抽查一批产品的合格率 根据过去的资料 产品合格率为 98 若要求把 握程度 置信度 为 95 边际误差不超过 2 则应抽取多大容量的样本 操作步骤 操作步骤 1 建立 样本容量工作表 如图 5 10 中 A 列 在单元格 B1 B2 B3 中分别输入合格率 98 置信度 95 边际误差 2 2 在单元格 B4 中输入公式 NORMSINV B2 1 B2 2 回车后单元格 B4 显 示与置信度 95 对应的 Z 值 1 959964 3 在单元格 B5 中输入样本容量公式 B1 1 B1 B4 B3 2 回车后单元 格 B5 显示 188 2315 6 选中单元格 B6 在编辑栏中输入样本容量取整公式 CEILING B5 1 回车后单元格 B6 显示 189 计算结果如图 5 10 8 图 5 10 实训三上机练习实训三上机练习 1 某商场连续 100 天的销售额 单位 百元 资料如下 117122124129139107117130122125 108131125117122133126122118108 110118123126133134127123118112 112134127123119113120123127135 137114120128124115139128124121 要求 采用 纯随机模式 及 周期 模式各抽一个容量为 10 的样本 2 表 5 1 包含 50 个由 n 5 个测量值组成的随机样本 均选自 的总体 25 8 5 4 2 和X 表 5 1 由 n 5 个测量值组成 50 随机样本数据 1 8 0 6 61 6 0 0 93 6 4 2 04 5 3 4 82 3 7 6 3 2 1 7 2 96 8 5 2 81 5 0 5 85 6 7 8 2 2 0 6 6 3 4 5 7 7 12 4 9 4 64 6 2 6 23 8 6 0 1 1 9 0 3 2 3 6 1 8 1 6 7 0 4 31 8 8 2 11 4 4 9 08 9 2 7 0 9 8 6 2 90 5 9 9 69 0 6 1 77 7 9 8 11 5 0 5 1 6 8 8 3 54 4 7 5 63 7 3 4 39 2 9 8 77 8 7 7 6 9 5 7 7 96 6 5 5 64 5 2 6 66 8 9 6 09 3 7 3 9 7 6 4 4 75 0 6 6 59 3 7 1 33 4 6 7 05 1 1 4 0 6 5 6 4 23 0 4 9 61 9 6 9 68 4 7 6 92 5 7 7 9 8 6 8 6 03 0 7 4 15 1 2 3 46 9 4 4 23 0 6 9 7 要求 1 计算每一个样本均值 构造 50 个样本均值的分配数列 并做出直 方图 此图近似代表以容量 n 5 的样本为基础的抽样分布 x 2 计算 50 个样本均值的平均数和标准差 与总体可以得25 8 5 4 2 和X 出什么结论 用经验公式计算其抽样平均误差 与 50 个样本的标准差比较 可 以得出什么结论 3 将 50 个 n 5 的样本一对一地合并 形成 25 个由 n 10 个测量值组成的样 本 构造 25 个样本均值的分配数列 并做出直方图 此图近似代表以容量 n 10 的样本为基础的抽样分布 x 4 计算 25 个样本均值的平均数和标准差 与总体可以得25 8 5 4 2 和X 出什么结论 用经验公式计算其抽样平均误差 与 25 个样本的标准差比较 可 9 以得出什么结论 5 比较 1 和 3 两个抽样分布的标准差 哪个抽样分布的变异性较小 3 某单位按简单随机重复抽样方式抽取 40 名职工 对其业务情况进行考核 考 核成绩资料如下 68 89 88 84 86 87 75 73 72 68 75 82 99 58 81 54 79 76 95 76 71 60 91 65 76 72 76 85 89 92 64 57 83 81 78 77 72 61 70 87 要求 1 根据上述资料按成绩分成以下几组 60 分以下 60 70 分 70 80 分 80 90 分 90 100 分 并根据分组整理成变量分配数列 2 根据整理后的变量数列 以 95 45 的概率保证程度推断全体职工业务考 试成绩的区间范围 4 126 页 127 页 5 7 5 11 题 5 Metropolitan Research 有限公司是一家消费者研究组织 它设计调查 对 消费者所使用的大量的产品和服务进行评估 在某一项研究中 Metropolitan 调查消费者对底特律某一个主要制造商所生产的汽车的性能的满意程度 分发 给该制造商所生产的一种最大型号小汽车用户的调查表表明 许多人抱怨该车 刚开始传动系统不佳 为了更好地了解传动系统的问题 Metropolitan 采用由 底特律地区一个修理企业所提供的实际传动系统的维修记录为样本 表 4 2 数 据是 50 辆汽车传动系统出现故障时所行驶的实际里程的数据 表 5 2 数据 85092326095946577437325346409032464599023932389641 942191168039285763436656058586164342619786799859817 1017699577412135269568743766699840001720692506677098 6992235662744256720211844453500792946454486813116269 378318934173341852881381145340285586822567753988798 要求要求 1 用适当的描述统计量汇总传动系统数据 2 求曾经出现过传动系统问题的汽车总体中在出现传动系统问题时所 行驶里程的均值的 95 置信区间 并对该区间估计做出管理上的解释 3 按照一些汽车用户曾经历过的早期传动系统失灵的说法 你的统计结 果说明了什么 4 如果研究公司想在 5000 英里的允许误差下 估计出现传动系统问题 时所行驶里程的均值 则置信度为 95 时应选取多大的样本容量 5 为了更全面地对该传动系统问题做出评价 你还需要收集一些其他什么 样的信息 6 Lisa Rae Bock 在 1994 年开办了一家 Bock 投资服务公司 Bock Investment Serices BIS 旨在给南加利福尼亚货币市场提供咨询服务和指 10 导 为了给其目前的客户提供更好的服务并吸引新客户 她每周做一份简讯 为了更好地反映每周对基金管理者电话调查的结果 Lisa 正在考虑向简讯中添 加一项新内容 为了对提供这种服务的可能性进行调查 以便确定在简讯中应 包含哪些类的信息 Lisa 选取了 45 种货币市场基金组成一个简单随机样本 部分数据见表 4 3 她们报告了基金的资产 最近 7 天和最近 30 天的获益率 Barrons 1994 10 3 在给基金管理者打电话以获取更多的信息之前 Lisa 决定先对已搜集的数据做一些初始分析 要求 1 用适当的描述统计量汇总货币市场基金的资产和获益率数据 2 求货币市场基金总体的资产均值 最近 7 天的获益率和最近 30 天的 获益率的 95 置信区间估计 并对该区间估计做出管理上的解释 3 从 Lisa 该如何在其每周的简讯中使用这些信息的角度上看 你的统 计结果能说明什么 4 为了向用户提供最有用的信息 你建议 Lisa 还需要搜集一些其它什 么信息 表 5 3 Bock 投资服务公司问题中所用的数据 货币市场基金资产 百万美圆过去 7 日的 获益率 过去 30 日的获益 率 Amcore 103 94 104 18 Alger 156 74 794 73 Arch MM Trust 496 54 174 13 BT Instit Treas 197 84 374 32 Benchmark Dir 2755 44 544 47 Bradford 707 63 883 83 Capital Cash 1 74 294 22 Cash Mgt Trust 2707 84 144 04 Composite 122 84 033 91 Cowen Standby 694 74 254 19 Corland 217 33 573 51 Declaration 38 42 672 61 Dreyfus 4832 84 073 89 Elfun 81 74 514 41 FFB Cash 506 24 174