《实训三用Excel进行统计推断.doc》由会员分享，可在线阅读，更多相关《实训三用Excel进行统计推断.doc（11页珍藏版）》请在金锄头文库上搜索。
1、实训三用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。“随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。采用纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可；若采用分类抽样，必须先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合；整群抽样也要先将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的类的所有单位全部进行调查。可以看出，此例的编号输入方法，只适用于等距抽样和纯随机抽样。第四步：指定输出区域，在这里我们输入$A$14，单击确定后，即可得
4、到抽样结果，如图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。因此置信区间为：使用的主要函数如下图：案例13： 均值
5、的区间估计（一）正态总体、方差未知、大样本某饭店在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中输入“=C10-C13”，在B15中输入“=
8、C10+C13”，回车后，便可得顾客平均消费额的置信下限为29.80184，置信上限为34.19816。关于总体方差的估计、总体比例的估计等可按类似方法进行。（二）正态总体、方差未知、小样本某零件加工企业生产一种螺丝钉，对某天加工的零件每隔一定时间抽出一个，共抽取12个，测得其长度（单位：mm）数据如附表中的A2:A13。假定零件长度服从正态分布，试以95%的置信水平估计该企业生产的螺丝钉平均长度的置信区间。 解：为构造区间估计的工作表，我们应在工作表中输入下列内容：A列输入样本数据，B列输入变量名称，C列输入计算公式，D列为C列的计算结果，当输入完公式后，即显示D列结果。用Excel求置信区
9、间 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置信上限=C3+C911.2474
10、610 1211.03 1310.97 我们有95%把握认为该企业生产的螺丝钉的平均长度在10.900872mm11.247461mm之间。对于不同的样本数据，只要输入新的样本数据，再对C列公式中的样本数据区域加修改，置信区间就会自动给出。如果需要不同的置信水平，填入相应的数值即可。方差已知时采用正态分布统计量构造置信区间，此时不用计算样本标准差，直接使用总体标准差；B8单元格改为Z值；C8单元格改为“=NORMSINV( (1-C6) /2)”即可。案例14：比例的区间估计以某厂对一批产品的质量进行抽样检验为例，抽样数据和要求如下：采用重复抽样抽取样品200只，样本优质品率为85，试计算当把
11、握程度为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 总体优质品率的区间估计或用鼠标单击工具栏的粘贴函数fx，在“函数分类”单击
12、“统计”，在其右边显示的“函数名”中单击“CONFIDENCE”，在Alpha 中输入 值，此为B4，在Standard-dev中输入样本标准差值，此为B3，在Size中输入样本容量值，此为B1，然后单击确定，即获得边际误差。案例15：估计总体必要的样本容量（一）估计均值的必要样本容量某县进行农村经济情况调查，已知农户平均年收入标准差为30元，要求把握程度（置信度）为95.45%，抽样边际误差为5元，计算应抽取的样本户数。操作步骤：（1）建立“样本容量工作表”，如图5-9中A列；（2）在单元格B1、B2中分别输入抽样边际误差5和置信度95.45%；（3）选中单元格B3，在编辑栏中输入“=NOR
13、MSINV（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%，则应抽取多大容量的样本？操作步骤：（1）建立“样本
14、容量工作表”，如图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天的销售额(单位:百元)资料如下:117122124129139107117130122125108131125117122133126122118108110118123126133134127123118112112134127123119113120123127135137114120128124115139128124121要求:采用“纯随机模式”及“周期”模式各抽一个容量为10的样本。2.表5-1包含50个
