实训三用Excel进行统计推断.docx

1、实训三用Excel进行统计推断实训三用Excel进行统计推断目的与要求：理解抽样分布原理，掌握在不同的组织形式下抽取样本的方法，明确抽样标准误差、抽样边际误差之间的区别，重点掌握简单随机抽样组织形式的区间估计方法，会用Excel软件抽取样本、进行区间估计、以及确定样本单位数。一、用Excel抽取样本使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，具体可参见本书有关抽样的章节，编号后，将编号输入工作表。案例12：假定有100个总体单位，每个总体单位给一个编号，共有从1到100个编号，输入工作表后如图5-1所示：图：5-1总体各单位编号表输入各

2、总体单位的编号后，可按以下步骤操作：第一步：单击工具菜单，选择数据分析选项（若无数据分析选项，可在工具菜单下选择加载宏，在弹出的对话框中选择分析工具库，便可出现数据分析选项），打开数据分析对话框，从中选择抽样。如图5-2所示：图5-2数据分析对话框第二步：单击抽样选项，弹出抽样对话框。如图5-3图5-3 抽样对话框第三步：在输入区域框中输入总体单位编号所在的单元格区域，在本例是$A$1:$J$10，系统将从A列开始抽取样本，然后按顺序抽取B列至J列。如果输入区域的第一行或第一列为标志项（横行标题或纵列标题），可单击标志复选框。第四步：在抽样方法项下，有周期和随机两种抽样模式：“周期”模式即所谓

3、的等距抽样，采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，求得取样的周期间隔。如我们要在100个总体单位中抽取12个，则在“间隔”框中输入8。“随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。采用纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可；若采用分类抽样，必须先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合；整群抽样也要先将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的类的所有单位全部进行调查。可以看出，此例的编号输入方法，只适用于等距抽样和纯随机抽样。第四步：指定输出区域，在这里我们输入

4、$A$14，单击确定后，即可得到抽样结果，如图5-4图5-4等距抽样结果二、CONFIDENCE（边际误差）函数CONFIDENCE（alpha, standard-dev, size）返回总体平均值的置信区间。alpha（即）是用于计算置信度的显著水平参数。置信度等于（1），亦即，如果为0.05，则置信水平为0.95。置信度为1.96Standard-dev 数据区域的总体标准差，假设为已知（实际中，总体标准差未知时通常用样本标准差代替）。Size 样本容量（即n）。如果假设等于0.05，则需要计算标准正态分布曲线（10.95）之下的临界值，查表知其临界值为1.96。因此置信区间为： 使用的

5、主要函数如下图：案例13： 均值的区间估计（一）正态总体、方差未知、大样本某饭店在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 2224 32 46 26求在概率90%的保证下，顾客平均消费额的估计区间。解：操作步骤如下：第一步：把数据输入到A1：G7单元格。第二步：选中B9单元格，用鼠标单击工具栏的粘贴函数fx，在“函

6、数分类”单击“统计”，在其右边显示的“函数名”中单击“COUNT”，出现图5-5对话框图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

7、，然后单击确定，即获得边际误差。在B14中输入“=C10-C13”，在B15中输入“=C10+C13”，回车后，便可得顾客平均消费额的置信下限为29.80184，置信上限为34.19816。如图5-7 图5-7 参数估计数据及结果或者，在B9中输入函数“=COUNT（A1：G7）”，获得样本容量(即此例中的数据个数)；在B10中输入“=AVERAGE（A1：G7）”，获得样本平均数；在B11中输入“=STDEVA（A1：G7）”，获得样本标准差；在B12中输入显著性水平值10%；在B13中输入表达式：“=CONFIDENCE（C12，C11，C9）”，回车即得到边际误差；在B14中输入“=C1

8、0-C13”，在B15中输入“=C10+C13”，回车后，便可得顾客平均消费额的置信下限为29.80184，置信上限为34.19816。关于总体方差的估计、总体比例的估计等可按类似方法进行。（二）正态总体、方差未知、小样本某零件加工企业生产一种螺丝钉，对某天加工的零件每隔一定时间抽出一个，共抽取12个，测得其长度（单位：mm）数据如附表中的A2:A13。假定零件长度服从正态分布，试以95%的置信水平估计该企业生产的螺丝钉平均长度的置信区间。 解：为构造区间估计的工作表，我们应在工作表中输入下列内容：A列输入样本数据，B列输入变量名称，C列输入计算公式，D列为C列的计算结果，当输入完公式后，即显

9、示D列结果。用Excel求置信区间 ABCD1样本数据计算指标计算公式计算结果210.94样本数据个数COUNT(A2:A13)12311.91样本均值=AVERAGE(A2:A13)11.074167 410.91样本标准差=STDEV(A2:A13)0.272746 510.94样本平均值的标准差=C4/SQRT(C2)0.078735 611.03置信水平=0.950.95710.97自由度=C2-111811.09t 值=TINV(1-C6,C7)2.200986 911.00 误差范围=C8*C50.173294 1011.16置信下限=C3-C910.900872 1110.94置

10、信上限=C3+C911.2474610 1211.03 1310.97 我们有95%把握认为该企业生产的螺丝钉的平均长度在10.900872mm11.247461mm之间。对于不同的样本数据，只要输入新的样本数据，再对C列公式中的样本数据区域加修改，置信区间就会自动给出。如果需要不同的置信水平，填入相应的数值即可。方差已知时采用正态分布统计量构造置信区间，此时不用计算样本标准差，直接使用总体标准差；B8单元格改为Z值；C8单元格改为“=NORMSINV( (1-C6) /2)”即可。案例14：比例的区间估计以某厂对一批产品的质量进行抽样检验为例，抽样数据和要求如下：采用重复抽样抽取样品200只

11、，样本优质品率为85，试计算当把握程度为90时优质品率的边际误差。我们可以在EXCEL中分别在：B1单元格中输入样本容量200；B2单元格中输入样本比率85；在B3单元格中输入计算样本比率的标准差公式“SQRT（B2*（1B2）”；在B4单元格输入为10；在B5单元格中输入表达式：“=CONFIDENCE（B4，B3，B1）”，回车即得到等于4.15。在B6单元格中输入表达式“B2-B5”，回车即得到置信下限值，80.85%,在B7单元格中输入表达式“B2+B5”，回车即得到置信上限值，89.15%.CONFIDENCE函数的应用如图5-8 图5-8 总体优质品率的区间估计或用鼠标单击工具栏的

12、粘贴函数fx，在“函数分类”单击“统计”，在其右边显示的“函数名”中单击“CONFIDENCE”，在Alpha 中输入 值，此为B4，在Standard-dev中输入样本标准差值，此为B3，在Size中输入样本容量值，此为B1，然后单击确定，即获得边际误差。案例15：估计总体必要的样本容量（一）估计均值的必要样本容量某县进行农村经济情况调查，已知农户平均年收入标准差为30元，要求把握程度（置信度）为95.45%，抽样边际误差为5元，计算应抽取的样本户数。操作步骤：（1）建立“样本容量工作表”，如图5-9中A列；（2）在单元格B1、B2中分别输入抽样边际误差5和置信度95.45%；（3）选中单元

13、格B3，在编辑栏中输入“=NORMSINV（B2）”，回车后单元格B3显示与置信度95.45%对应的Z值1.690145837；（4）在单元格B4中输入标准差30；（5）选中单元格B5，在编辑栏中输入样本容量公式“=（B32*B42）/B12，回车后单元格B5显示102.8373462；（6）选中单元格B6，在编辑栏中输入样本容量取整公式“=CEILING（B5，1）”，回车后单元格B6显示103；计算结果如图5-9。图5-9（二）估计总体比例（成数）的必要样本容量抽查一批产品的合格率，根据过去的资料，产品合格率为98%，若要求把握程度（置信度）为95%，边际误差不超过2%，则应抽取多大容量的

14、样本？操作步骤：（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/B32），回车后单元格B5显示188.2315；（6）选中单元格B6，在编辑栏中输入样本容量取整公式“=CEILING（B5，1）”，回车后单元格B6显示189；计算结果如图5-10。图5-10【实训三上机练习】1. 某商场连续100天的销售额(

15、单位:百元)资料如下:117122124129139107117130122125108131125117122133126122118108110118123126133134127123118112112134127123119113120123127135137114120128124115139128124121要求:采用“纯随机模式”及“周期”模式各抽一个容量为10的样本。2.表5-1包含50个由n=5个测量值组成的随机样本,均选自的总体。表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,32,1,7

16、,2,96,8,5,2,81,5,0,5,85,6,7,8,2,2,0,6,6,34,5,7,7,12,4,9,4,64,6,2,6,23,8,6,0,1,1,9,0,3,23,6,1,8,1,6,7,0,4,31,8,8,2,11,4,4,9,08,9,2,7,09,8,6,2,90,5,9,9,69,0,6,1,77,7,9,8,11,5,0,5,16,8,8,3,54,4,7,5,63,7,3,4,39,2,9,8,77,8,7,7,69,5,7,7,96,6,5,5,64,5,2,6,66,8,9,6,09,3,7,3,97,6,4,4,75,0,6,6,59,3,7,1,33,4,6

17、,7,05,1,1,4,06,5,6,4,23,0,4,9,61,9,6,9,68,4,7,6,92,5,7,7,98,6,8,6,03,0,7,4,15,1,2,3,46,9,4,4,23,0,6,9,7要求：（1）计算每一个样本均值；构造50个样本均值的分配数列，并做出直方图；此图近似代表以容量n=5的样本为基础的抽样分布。（2）计算50个样本均值的平均数和标准差，与总体可以得出什么结论？用经验公式计算其抽样平均误差，与50个样本的标准差比较，可以得出什么结论？（3）将50个n=5的样本一对一地合并，形成25个由n=10个测量值组成的样本；构造25个样本均值的分配数列，并做出直方图；此图近

18、似代表以容量n=10的样本为基础的抽样分布。（4）计算25个样本均值的平均数和标准差，与总体可以得出什么结论？用经验公式计算其抽样平均误差，与25个样本的标准差比较，可以得出什么结论？（5）比较（1）和（3）两个抽样分布的标准差，哪个抽样分布的变异性较小？3.某单位按简单随机重复抽样方式抽取40名职工，对其业务情况进行考核，考核成绩资料如下：68 89 88 84 86 87 75 73 72 68 75 82 99 58 81 54 79 76 95 7671 60 91 65 76 72 76 85 89 92 64 57 83 81 78 77 72 61 70 87要求：（1）根据上述

19、资料按成绩分成以下几组：60分以下，6070分，7080分，8090分，90100分，并根据分组整理成变量分配数列；（2）根据整理后的变量数列，以95.45%的概率保证程度推断全体职工业务考试成绩的区间范围。4.126页127页5.75.11题5.Metropolitan Research有限公司是一家消费者研究组织，它设计调查，对消费者所使用的大量的产品和服务进行评估。在某一项研究中，Metropolitan调查消费者对底特律某一个主要制造商所生产的汽车的性能的满意程度。分发给该制造商所生产的一种最大型号小汽车用户的调查表表明，许多人抱怨该车刚开始传动系统不佳。为了更好地了解传动系统的问题，

20、Metropolitan采用由底特律地区一个修理企业所提供的实际传动系统的维修记录为样本。表4-2数据是50辆汽车传动系统出现故障时所行驶的实际里程的数据：表5-2数据850923260959465774373253464090324645990239323896419421911680392857634366560585861643426197867998598171017699577412135269568743766699840001720692506677098699223566274425672021184445350079294645448681311626937831893417

21、3341852881381145340285586822567753988798要求: （1）用适当的描述统计量汇总传动系统数据。（2）求曾经出现过传动系统问题的汽车总体中在出现传动系统问题时所行驶里程的均值的95%置信区间，并对该区间估计做出管理上的解释。（3）按照一些汽车用户曾经历过的早期传动系统失灵的说法，你的统计结果说明了什么？（4）如果研究公司想在5000英里的允许误差下，估计出现传动系统问题时所行驶里程的均值，则置信度为95%时应选取多大的样本容量？（5）为了更全面地对该传动系统问题做出评价，你还需要收集一些其他什么样的信息？6. Lisa Rae Bock 在1994年开办了一家

22、Bock投资服务公司（Bock Investment Serices，BIS），旨在给南加利福尼亚货币市场提供咨询服务和指导。为了给其目前的客户提供更好的服务并吸引新客户，她每周做一份简讯。为了更好地反映每周对基金管理者电话调查的结果，Lisa正在考虑向简讯中添加一项新内容。为了对提供这种服务的可能性进行调查，以便确定在简讯中应包含哪些类的信息，Lisa选取了45种货币市场基金组成一个简单随机样本，部分数据见表4-3。她们报告了基金的资产、最近7天和最近30天的获益率（Barrons，1994.10.3）。在给基金管理者打电话以获取更多的信息之前，Lisa决定先对已搜集的数据做一些初始分析。要

23、求：（1）用适当的描述统计量汇总货币市场基金的资产和获益率数据。（2）求货币市场基金总体的资产均值、最近7天的获益率和最近30天的获益率的95%置信区间估计。并对该区间估计做出管理上的解释。（3）从Lisa该如何在其每周的简讯中使用这些信息的角度上看，你的统计结果能说明什么？（4）为了向用户提供最有用的信息，你建议Lisa还需要搜集一些其它什么信息表5-3 Bock投资服务公司问题中所用的数据货币市场基金资产/百万美圆过去7日的获益率（%）过去30日的获益率（%）Amcore103.94.104.18Alger156.74.794.73Arch MM/Trust496.54.174.13BT

24、Instit Treas197.84.374.32Benchmark Dir2755.44.544.47Bradford707.63.883.83Capital Cash1.74.294.22Cash Mgt Trust2707.84.144.04Composite122.84.033.91Cowen Standby694.74.254.19Corland217.33.573.51Declaration38.42.672.61Dreyfus4832.84.073.89Elfun81.74.514.41FFB Cash506.24.174.11Federated Master738.74.414

25、.34Fidelity Cash13272.84.514.42Flex-fund172.84.604.48Fortis105.63.873.85Franklin money996.83.973.92Freedom Cash1079.04.074.01Galaxy money801.44.113.96Government Cash409.43.833.82Hanover Cash794.34.324.23Heritage Cash1008.34.084.00Infinity/Alpha53.63.993.91John hancock226.43.933.87Landmark Funds481.3

26、4.284.26Liquid Cash388.94.614.64Mardet Watch 10.64.134.05Merrill Lynch Money27005.64.244.18NCC Funds113.44.224.20Nationwide517.34.224.14Overland291.54.264.17Pierpont money1991.74.504.40Portico Money161.64.284.20Prudential Money Mart6835.14.204.16Reserve Primary1408.03.913.86Schwab Money10531.04.164.07Smith Barney Cash2947.64.164.12Stagecoach1502.24.184.13Strong Money470.24.374.29Transamerica Cash175.54.204.19United Cash 323.73.963.89Woodward Money1330.04.244.21资料来源：Barrons，October3，1994.