用Excel进行统计推断

〖实训三〗用Excel进行统计推断

目的与要求：理解抽样分布原理，掌握在不同的组织形式下抽取样本的方法，明确抽样标准误差、抽样边际误差之间的区别，重点掌握简单随机抽样组织形式的区间估计方法，会用Excel软件抽取样本、进行区间估计、以及确定样本单位数。

一、用Excel抽取样本

使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，具体可参见本书有关抽样的章节，编号后，将编号输入工作表。

案例12：

假定有100个总体单位，每个总体单位给一个编号，共有从1到100个编号，输入工作表后如图5-1所示：

图：5-1总体各单位编号表

输入各总体单位的编号后，可按以下步骤操作：

第一步：单击工具菜单，选择数据分析选项（若无数据分析选项，可在工具菜单下选择加载宏，在弹出的对话框中选择分析工具库，便可出现数据分析选项），打开数据分析对话框，从中选择抽样。如图5-2所示：

图5-2数据分析对话框

1

第二步：单击抽样选项，弹出抽样对话框。如图5-3

图5-3 抽样对话框

第三步：在输入区域框中输入总体单位编号所在的单元格区域，在本例是$A$1:$J$10，系统将从A列开始抽取样本，然后按顺序抽取B列至J列。如果输入区域的第一行或第一列为标志项（横行标题或纵列标题），可单击标志复选框。

第四步：在抽样方法项下，有周期和随机两种抽样模式： “周期”模式即所谓的等距抽样，采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，求得取样的周期间隔。如我们要在100个总体单位中抽取12个，则在“间隔”框中输入8。

“随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。采用纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可；若采用分类抽样，必须先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合；整群抽样也要先将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的类的所有单位全部进行调查。可以看出，此例的编号输入方法，只适用于等距抽样和纯随机抽样。

第四步：指定输出区域，在这里我们输入$A$14，单击确定后，即可得到抽样结果，如图5-4

图5-4等距抽样结果

2

二、CONFIDENCE（边际误差）函数

CONFIDENCE（alpha, standard-dev, size）返回总体平均值的置信区间。 alpha（即α）是用于计算置信度的显著水平参数。置信度等于（1－α），亦即，如果α为0.05，则置信水平为0.95。置信度为1.96

Standard-dev 数据区域的总体标准差，假设为已知（实际中，总体标准差未知时通常用样本标准差代替）。

Size 样本容量（即n）。

如果假设α等于0.05，则需要计算标准正态分布曲线（1－α＝0.95）之

x?1.96(?下的临界值，查表知其临界值为±1.96。因此置信区间为：

使用的主要函数如下图：

n

)

案例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 22 24 32 46 26

求在概率90%的保证下，顾客平均消费额的估计区间。 解：操作步骤如下：

第一步：把数据输入到A1：G7单元格。 第二步：选中B9单元格，用鼠标单击工具栏的粘贴函数fx，在“函数分类”单击“统计”，在其右边显示的“函数名”中单击“COUNT”，出现图5-5对话框

3

图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-7 参数估计数据及结果

4

或者，在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求置信区间 A B C D 1 样本数据 计算指标 计算公式 计算结果 2 10.94 样本数据个数 ＝COUNT(A2:A13) 12 3 11.91 =AVERAGE(A2:A13) 11.074167 样本均值 4 10.91 =STDEV(A2:A13) 0.272746 样本标准差 5 样本平均值的标10.94 =C4/SQRT(C2) 0.078735 准差 6 11.03 =0.95 0.95 置信水平 7 10.97 =C2-1 11 自由度 8 11.09 =TINV(1-C6,C7) 2.200986 t 值 9 11.00 =C8*C5 0.173294 误差范围 10 11.16 =C3-C9 10.900872 置信下限 11 10.94 =C3+C9 11.2474610 置信上限 12 11.03 13 10.97 我们有95%把握认为该企业生产的螺丝钉的平均长度在10.900872mm～11.247461mm之间。

对于不同的样本数据，只要输入新的样本数据，再对C列公式中的样本数据区域加修改，置信区间就会自动给出。如果需要不同的置信水平，填入相应的数值即可。

5

方差已知时采用正态分布统计量构造置信区间，此时不用计算样本标准差，直接使用总体标准差；B8单元格改为Z值；C8单元格改为“=NORMSINV( (1-C6) /2)”即可。

案例14：比例的区间估计

以某厂对一批产品的质量进行抽样检验为例，抽样数据和要求如下：采用重复抽样抽取样品200只，样本优质品率为85％，试计算当把握程度为90％时优质品率的边际误差。我们可以在EXCEL中分别在：

◆B1单元格中输入样本容量200； ◆B2单元格中输入样本比率85％； ◆在B3单元格中输入计算样本比率的标准差公式“＝SQRT（B2*（1－B2））”；

◆在B4单元格输入α为10％；

◆在B5单元格中输入表达式：“=CONFIDENCE（B4，B3，B1）”，回车即

得到等于4.15％。

◆在B6单元格中输入表达式“B2-B5”，回车即得到置信下限值，80.85%,

在B7单元格中输入表达式“B2+B5”，回车即得到置信上限值，89.15%. CONFIDENCE函数的应用如图5-8

Z?/2p(1?p)n图5-8 总体优质品率的区间估计

或用鼠标单击工具栏的粘贴函数fx，在“函数分类”单击“统计”，在其右边显示的“函数名”中单击“CONFIDENCE”，在Alpha 中输入 α值，此为B4，在Standard-dev中输入样本标准差值，此为B3，在Size中输入样本容量值，此为B1，然后单击确定，即获得边际误差。

案例15：估计总体必要的样本容量

（一）估计均值的必要样本容量

6

某县进行农村经济情况调查，已知农户平均年收入标准差为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。

图5-10

7

【实训三上机练习】

1. 某商场连续100天的销售额(单位:百元)资料如下: 117 122 124 129 139 107 117 108 131 125 117 122 133 126 110 118 123 126 133 134 127 112 134 127 123 119 113 120 137 114 120 128 124 115 139 130 122 123 123 128 122 118 118 127 124 125 108 112 135 121 要求:采用“纯随机模式”及“周期”模式各抽一个容量为10的样本。

2.表5-1包含50个由n=5个测量值组成的随机样本,均选自X?4.5和?2?8.25的总体。 1,8,0,6,6 2,1,7,2,9 4,5,7,7,1 3,6,1,8,1, 9,8,6,2,9 6,8,8,3,5 9,5,7,7,9 7,6,4,4,7 6,5,6,4,2 8,6,8,6,0 表5-1 由n=5个测量值组成50随机样本数据 1,6,0,0,9 3,6,4,2,0 4,5,3,4,8 2,3,7,6,3 6,8,5,2,8 1,5,0,5,8 5,6,7,8,2, 2,0,6,6,3 2,4,9,4,6 4,6,2,6,2 3,8,6,0,1, 1,9,0,3,2 6,7,0,4,3 1,8,8,2,1 1,4,4,9,0 8,9,2,7,0 0,5,9,9,6 9,0,6,1,7 7,7,9,8,1 1,5,0,5,1 4,4,7,5,6 3,7,3,4,3 9,2,9,8,7 7,8,7,7,6 6,6,5,5,6 4,5,2,6,6 6,8,9,6,0 9,3,7,3,9 5,0,6,6,5 9,3,7,1,3 3,4,6,7,0 5,1,1,4,0 3,0,4,9,6 1,9,6,9,6 8,4,7,6,9 2,5,7,7,9 3,0,7,4,1 5,1,2,3,4 6,9,4,4,2 3,0,6,9,7 要求：（1）计算每一个样本均值；构造50个样本均值的分配数列，并做出直方

图；此图近似代表以容量n=5的样本为基础的x抽样分布。

（2）计算50个样本均值的平均数和标准差，与总体X?4.5和?2?8.25可以得出什么结论？用经验公式计算其抽样平均误差，与50个样本的标准差比较，可以得出什么结论？ （3）将50个n=5的样本一对一地合并，形成25个由n=10个测量值组成的样本；构造25个样本均值的分配数列，并做出直方图；此图近似代表以容量n=10的样本为基础的x抽样分布。

（4）计算25个样本均值的平均数和标准差，与总体X?4.5和?2?8.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 76 71 60 91 65 76 72 76 85 89 92 64 57 83 81 78 77 72 61 70 87

8

要求：（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数据 85092 94219 101769 69922 37831 32609 116803 95774 35662 89341 59465 92857 121352 74425 73341 77437 63436 69568 67202 85288 32534 65605 74376 118444 138114 64090 85861 66998 53500 53402 32464 64342 40001 79294 85586 59902 61978 72069 64544 82256 39323 67998 25066 86813 77539 89641 59817 77098 116269 88798 要求: （1）用适当的描述统计量汇总传动系统数据。

（2）求曾经出现过传动系统问题的汽车总体中在出现传动系统问题时所行驶里程的均值的95%置信区间，并对该区间估计做出管理上的解释。

（3）按照一些汽车用户曾经历过的早期传动系统失灵的说法，你的统计结果说明了什么？

（4）如果研究公司想在5000英里的允许误差下，估计出现传动系统问题时所行驶里程的均值，则置信度为95%时应选取多大的样本容量？

（5）为了更全面地对该传动系统问题做出评价，你还需要收集一些其他什么样的信息？

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

9

要求：（1）用适当的描述统计量汇总货币市场基金的资产和获益率数据。 （2）求货币市场基金总体的资产均值、最近7天的获益率和最近30天的获益率的95%置信区间估计。并对该区间估计做出管理上的解释。

（3）从Lisa该如何在其每周的简讯中使用这些信息的角度上看，你的统计结果能说明什么？

（4）为了向用户提供最有用的信息，你建议Lisa还需要搜集一些其它什么信息

表5-3 Bock投资服务公司问题中所用的数据 货币市场基金 资产/百万美圆 过去7日的过去30日的获益获益率（%） 率（%） Amcore 103.9 4.10 4.18 Alger 156.7 4.79 4.73 Arch MM/Trust 496.5 4.17 4.13 BT Instit Treas 197.8 4.37 4.32 Benchmark Dir 2755.4 4.54 4.47 Bradford 707.6 3.88 3.83 Capital Cash 1.7 4.29 4.22 Cash Mgt Trust 2707.8 4.14 4.04 Composite 122.8 4.03 3.91 Cowen Standby 694.7 4.25 4.19 Corland 217.3 3.57 3.51 Declaration 38.4 2.67 2.61 Dreyfus 4832.8 4.07 3.89 Elfun 81.7 4.51 4.41 FFB Cash 506.2 4.17 4.11 Federated Master 738.7 4.41 4.34 Fidelity Cash 13272.8 4.51 4.42 Flex-fund 172.8 4.60 4.48 Fortis 105.6 3.87 3.85 Franklin money 996.8 3.97 3.92 Freedom Cash 1079.0 4.07 4.01 Galaxy money 801.4 4.11 3.96 Government Cash 409.4 3.83 3.82 Hanover Cash 794.3 4.32 4.23 Heritage Cash 1008.3 4.08 4.00 Infinity/Alpha 53.6 3.99 3.91 John hancock 226.4 3.93 3.87 Landmark Funds 481.3 4.28 4.26 Liquid Cash 388.9 4.61 4.64 Mardet Watch 10.6 4.13 4.05 Merrill Lynch Money 27005.6 4.24 4.18 NCC Funds 113.4 4.22 4.20

10

Nationwide Overland Pierpont money Portico Money Prudential Money Mart Reserve Primary Schwab Money Smith Barney Cash Stagecoach Strong Money Transamerica Cash United Cash Woodward Money 517.3 291.5 1991.7 161.6 6835.1 1408.0 10531.0 2947.6 1502.2 470.2 175.5 323.7 1330.0 4.22 4.26 4.50 4.28 4.20 3.91 4.16 4.16 4.18 4.37 4.20 3.96 4.24 4.14 4.17 4.40 4.20 4.16 3.86 4.07 4.12 4.13 4.29 4.19 3.89 4.21 资料来源：Barron’s，October3，1994.

11

Nationwide Overland Pierpont money Portico Money Prudential Money Mart Reserve Primary Schwab Money Smith Barney Cash Stagecoach Strong Money Transamerica Cash United Cash Woodward Money 517.3 291.5 1991.7 161.6 6835.1 1408.0 10531.0 2947.6 1502.2 470.2 175.5 323.7 1330.0 4.22 4.26 4.50 4.28 4.20 3.91 4.16 4.16 4.18 4.37 4.20 3.96 4.24 4.14 4.17 4.40 4.20 4.16 3.86 4.07 4.12 4.13 4.29 4.19 3.89 4.21 资料来源：Barron’s，October3，1994.

11

本文来源：https://www.bwwdw.com/article/h6fv.html