用Excel进行统计推断.docx

用Excel进行统计推断.docx

《用Excel进行统计推断.docx》由会员分享，可在线阅读，更多相关《用Excel进行统计推断.docx（15页珍藏版）》请在冰豆网上搜索。

用Excel进行统计推断

第三部分用Excel进行统计推断

利用Excel产生随机数

不论是利用抽签，或者是使用随机数表来进行抽样，过程都相当麻烦，尤其是样本数过大时，所必须花费的时间就会更多。

此时，我们可以使用计算机软件来帮助我们进行随机抽样。

操作实例：

使用Excel抽出随机数

使用Excel中的加载宏，从700个元素的总体当中取得100个元素作为抽样的样本。

1）在A1单元格中，输入“抽样样本编号”，A2单元格中输入“1”，然后使用鼠标选取“编辑”-“填充”-“序列”命令，打开“序列”对话框。

2）在“序列”对话框当中的“序列产生在”区域选择“列”，“类型”区域选择“等差序列”，步长值填入“1”，终止值填入“700"，最后单击“确定”按钮即可在单元格A2~A701中填入1—700的数值。

3）接着，选择“工具”-“数据分析”命令，打开“数据分析”对话框。

在对话框中，选择“抽样”，屏幕上则会出现“抽样”对话框。

4）在“抽样”对话框中，“输入区域”指定为先前所填充的序列“A2：

A701”单元格范围。

“抽样方法”指定为“随机”，并输入“样本数”为“100"，输出选项指定为“新工作表组”，表示将抽样出来的数值放置在新工作表中。

最后单击“确定”按钮，您就会看到在工作簿中新增了一个工作表，而工作表中选出了100个抽样数值，

而我们就可以先将总体元素编号，再利用这些Excel所选出来的数值，挑选出100个相对编号的总体单位。

利用EXCEL进行参数估计

工具一数据分析一描述统计――估计总体均值的程序和输出表解读

在EXCEl工作表中，依次点击：

工具一数据分析一描述统计，在对话框中指定数据区域和置信度（默认值为95％，也可自行指定）等其他选项，可得到包括如内容的输出表（空白处应为输出数值，表中删除了一些参数估计不必要的输出，最后一列为作者所加的对输出结果的解释）：

表中对指定置信度所对应的抽样误差范围的输出结果，是基于正态总体、总体方差未知时的公式计算的。

（一）大样本条件下，单一总体均值的置信区间估计.（总体方差未知时）

应用案例1，2004年底北京市私家车拥有量已达到129．8万辆，位居全国之首，据业内人士分析其中国产中低档汽车的比例较大，为了估计目前北京市场个人购车的平均价格，调查人员于某日在北京最大的车市随机抽取36位私人消费购车者，得到他（她）们所购汽车的价格如下（单位：

万元）：

6．8811．2819．9813．610．614．8

6．8811．7820．9824．412．314．8

6．8813．6813．630．314．614．8

8．2814．9814．79．614．617．4

9．615．6815．89．612．95．38

10．1815．6820．510．614，87．38

根据这些调查数据怎样估计总体的平均消费价格?

如果要进一步推断所购买车辆在15万元以上的消费者占有多大比例，应当如何分析呢?

对汽车销售价格的总体均值求90％置信区间，利用Excel在大样本条件下进行单一总体均值的置信区间估计。

具体步骤如下：

打开“年底汽车价格1”表

第1步：

将相关数据输入Excel表格。

第2步：

选择“工具”下拉菜单。

第3步：

选择“数据分析”选项。

第4步：

在分析工具中选择“描述统计”。

第5步：

当对话框出现时，在“输入区域”设置框内键人数据单元格区域A1：

A36，在“输出区域”设置框内键人数据单元格区域B1，并选择“汇总统计”，然后选择“确定”，得到输出结果，此时样本均值出现在单元格C3中，抽样误差的值出现在单元格C4。

第6步：

选择单元格D2，并键人单元格公式=C3-1．645*C4，按下回车键；

选择单元格D3，并键人单元格公式=C3+1．645*C4，按下回车键，

得到输出结果，即汽车销售价格的总体均值90％置信区间为（12．18795，15．02316）。

见“年底汽车价格2”表中的操作及答案。

注：

在第6步中，因为所求的是90％的置信区间，所以单元格公式中使用的值是1．645。

通常，使用者需要键人与所希望的置信水平相对应的z值。

（二）大样本条件下，单一总体均值的置信区间估计.（总体方差已知）

------------------利用CONFIDENCE函数

当抽样数为大样本时，总体方差为已知的情况下，则总体平均数的置信区间为，在Excel中，我们可以利用CONFIDENCE函数计算出置信区间的数值。

语法:

CONFIDENCE（alpha,standard_dev,size）

其中alpha代表用来推算置信度的显著程度。

置信等于100*（1·alpha）％，换言之，0．05的alpha值所指的是95％信赖度。

Standarddev为此数据的总体标准差，且假定为已知。

Size代表样本大小。

以搭车为例:

“50位乘客，每天搭乘火车的平均时间为60分钟，总体标准差为10分钟，试计算总体平均数的95％信赖区间为多少?

”

首先必须先算出信赖区间，接着才可以计算出信赖极限范围。

利用CONFIDENCE计算信赖极限范围的操作方法如下：

打开“乘车时间”表

步骤1：

移动鼠标选择要计算信赖区间的单元格B9，然后输入“=CONFIDENCE（1-$B$3，$B$4，$B$5）”，再按下回车键。

步骤2：

移动鼠标选择单元格B10，然后输入“=B6-B9”，再按下回车键。

步骤3：

移动鼠标选择单元格B11，然后输入“=B6+B9”，再按下回车键。

完成后，画面显示如下：

完成后，得知每人每天搭乘火车的平均时间为57．22819404至62．77180596分钟。

（三）小样本条件下，单一总体均值的置信区间估计（用TINV函数，来求得t值。

）

语法TINV（probability，degrees_freedom）

Probability为双测Student-t分布的概率值。

Degrees_freedom是构成该分布的自由度数目。

选取应用案例1中前26个数据，在小样本条件下利用Excel进行单一总体均值在95％置信度下置信区间的估计。

具体步骤如下：

打开“年底汽车价格1”表

第1步：

将相关数据输入Excel表格。

第2步：

选择“工具”下拉菜单。

第3步：

选择“数据分析”选项。

第4步：

在分析工具中选择“描述统计”。

第5步：

当对话框出现时，在“输入区域”设置框内键人数据单元格区域A2：

A27，在“输出区域”设置框内键人数据单元格区域B1，并选择“汇总统计”和“平均数置信度”。

下来，我们可利用TINV函数，求出0．95置信区间的t值。

第6步：

①首先，在单元格“D4”中，输入“误差容许值”，“E4'’单元格中输入“0．05"，“D5'，单元格中输入“t值”。

②选取E5单元格后，使用鼠标选取菜单中的“插入"-“函数"命令，打开“插入函数"，对话框。

在对话框中，选取函数为“TINV”，并单击“确定”按钮。

③在函数参数对话框中，Probability中输入允许的误差值“0．05’’（即95％的置信度），DeLfreedom中输入“25”，再单击“确定”按钮，即求得t值“2．0595”.

④接下来，我们要计算标准误差值，则我们在单元格“D6'’中，输入“标准误差”。

⑤最后，我们要计算出置信区间的上、下限。

因此，在“D7'’单元格中，输入“置信区间上限值”，“D8'’单元格中，输入“置信区间下限值”。

接着，在“E7'’单元格中，输入置信区间上限值的计算公式“＝C3+E5*E6"，

在“E8”单元格中，输入置信区间下限值的计算公式“=C3-E5*E6"，

即可得出置信区间值介于14.48902~16.07559之间。

见“年底汽车价格”表中的操作及答案。

（四）总体比例区间估计

应用案例2：

品牌认知度置信区间

某食品厂准备上市一种新产品，并配合以相应的广告宣传，企业想通过调查孩子们对其品牌的认知情况来评估广告的效用，以制定下一步的市场推广计划。

他们在该地区随机抽取350个小孩作访问对象，进行儿童消费者行为与消费习惯调查，其中有一个问句是“你听说过这个牌子吗？

”，在350个孩子中，有112个小孩的回答是“听说过”。

根据这个问句，可以分析这一消费群体对该品牌的认知情况。

所以，食品厂市场部经理要求，根据这些样本，给定95％的置信度，估计该地区孩子认知该品牌的比例。

见“比例估计答案”表中的操作及答案。

▪估计总体比例的必要样本容量

（五）大样本条件下两个总体均值之差的置信区间估计

应用案例3，2004年底很多类型的国产轿车价格都比年中有所下降，有关调查人员想对比某地区2004年底与年中私家购车族购车价格的差异，于是在年中新购车者中随机抽取32人，调查得到价格的数据如下：

5．3810．7812．8814．718．8830．37．3811．213．61519．9815．287．9811．7813．6515．820．59．999．3811．7814．615．920．989．3610．312．314．71724．48．9911．3810．28

将以上数据和应用案例1的数据整理如下：

根据以上整理的结果，试以95％的置信水平估计该地区年底与年中私家车平均购买价格差异的置信区间。

解：

由于两个样本相互独立，而且均为大样本，因此也服从正态分布，可应用应用案例1式求得置信区间，并以样本标准差代替未知的总体标准差。

在大样本条件下，利用Excel对两个总体均值之差的置信区间进行估计。

第1步：

将相关数据输入Excel表格。

第2步：

选择“工具”下拉菜单。

第3步：

选择“数据分析”选项。

第4步：

在分析工具中选择“描述统计”。

第5步：

当对话框出现时，在“输入区域”设置框内键人数据单元格区域A1：

B36，在“输出区域”设置框内键人数据单元格区域C1，并选择“汇总统计”，然后选择“确定”，得到输出结果。

此时样本均值出现在单元格D3和F3中，样本标准差的值出现在单元格D7和F7中。

第6步：

选择单元格C17，并键人单元格公式：

sqrt（D7*D7／D15+17*F7／F15），按下回车键，得到一个数值；

选择单元格C18，并键人单元格公式：

（D3—13）—1．96*Cl7，按下回车键，得到下限，选择单元格C19，并键人单元格公式：

（D3—13）十1．96*Cl7，

按下回车键，得到上限，即汽车销售价格的总体均值95％置信区间为（－2．134，2．823）。

用Excel进行假设检验

一、对单一总体均值进行检验

（一）大样本条件下

应用案例4主管部门经理估计投保人的平均年龄是40岁，研究人员从实际投保该险种的人员中随机抽取38人，调查得到他们投保时的年龄数据如下：

24503135434836513537

44462939382334284239

33443646421738294726

3248392734423440

试依据调查结果判断主管经理的估计是否可靠?

这是关于总体投保人的平均年龄是否等于40岁的假设检验问题。

题中随机抽取38人构成样本，由样本数据计算得：

x=37岁，这是否说明总体投保人的平均年龄不等于40岁呢?

利用Excel对单一总体均值进行检验。

具体步骤如下：

见“投保年龄”表。

1．Z检验

第1步：

输入相关数据。

第2步：

选择“工具”下拉菜单。

第3步：

选择“数据分析”选项。

第4步：

在分析工具中选择“描述统计”。

第5步：

当对话框出现时，在“输入区域”设置框内键人数据单元格区域A1：

A38，在“输出区域”设置框内键人数据单元格区域n，并选择“汇总统计”，然后选择“确定”，得到输出结果。

此时样本均值出现在单元格D3中，抽样误差的值出现在单元格D4中（见表7．2）。

第6步：

由于原假设为Ho：

u=40，所以键人数据为40，然后选择“确定”，得到z值。

选择单元格D17，键人公式=（D3—40）／D4，即统计Z。

由于z=—2．29142<—1．96，所以拒绝原假设Ho。

2．p值法

在Z值法的基础求出z值后，我们可以通过Excel的函数功能来求出P值。

具体做法如下：

第1步：

选择“插入”下拉菜单。

第2步：

选择“函数”选项。

第3步：

当粘贴函数对话框出现时，在左侧函数分类窗口选择“统计”，右侧函数名窗口选择“NORMSDIST'’，然后选择“确定”。

第4步：

当“NORMSDIST'’对话框出现时，在Z窗口输入z的值—2．29142（已在Z值法中求出），然后选择“确定”，得到p值（见表7．3）。

在本例中是双侧检验，所以最后的p值为0．010969536X2<0．05，所以拒绝原假设。

见“投保年龄答案”表中的操作及答案。

（二）小样本条件下

1．z检验

应用案例5某工厂采用自动包装机分装产品，假定每包产品的重量服从正态分布，每包标准重量为800克。

某日随机抽查10包，测得每包净重数据如下（单位：

克）：

789、780、794、762、802、813、770、785、810、806。

试在0．05的显著性水平下，检验当天自动包装机工作是否正常?

并报告p值。

利用Excel对单一总体均值进行检验。

具体步骤如下：

第1步：

输入相关数据。

第2步：

选择“工具”下拉菜单。

第3步：

选择“数据分析”选项。

第4步：

在分析工具中选择“描述统计”。

第5步：

当对话框出现时，在“输入区域”设置框内键人数据单元格区域A1：

A10，在“输出区域”设置框内键人数据单元格区域C1，并选择“汇总统计”然后选择“确定”，得到输出结果。

此时样本均值出现在单元格D3中，抽样误差的值出现在单元格D4中（见

表7．4）。

第6步：

计算统计量‘值。

选择单元格A12，键人公式：

（D3—800）／D4，即统计量t。

因为原假设为H0：

u=800，所以键人数据为800，然后选择“确定”。

由a=0．05，查表得临界值t（a，n-1）：

t（o．05,9）=±2．262

由于单元格A12中的值|t|=1．64237<2．262，所以不能拒绝原假设。

即可认为这天自动包装机工作正常。

2、p值法

在t值法的基础求出t值后，我们可以通过Excel的函数功能来求出P值。

具体做法如下：

第1步：

选择“插入”下拉菜单。

第2步：

选择“函数”选项。

第3步：

当粘贴函数对话框出现时，在左侧函数分类窗口选择“统计”，右侧函数名窗口选择“TDIST'’，然后选择“确定”。

第4步：

当“TDIST'’对话框出现时，在X窗口输入t的绝对值1．64237（已在t值法中求出），在Deg—freedom（自由度）窗口输人本例中的自由度9，在Tails窗口中输入2（表明是双侧检验，如果是单侧检验则输入1），然后选择“确定”，得到p值（见表7．5）。

由于p值为0．134>0．05，所以不能拒绝原假设。

二、独立样本的两个总体均值差异的假设检验

（一）大样本条件下

在大样本条件下，进行独立样本的两个总体均值差异的假设检验时，我们可以用样本的方差来代替总体的方差。

应用案例6某汽车公司经理要比较A种与B种两种汽油的性能。

选用同类型汽车，分两组各30辆，试开一周，甲队使用A种汽油，乙队使用B种汽油，记录下每辆汽车每加仑行驶的平均里程，数据如下（公里/加仑）。

试按显著性水平o=0．05，判断两种汽油的公里/加仑指标有无明显差别。

下面我们以本例题：

说明如何在大样本条件下，利用Excel对独立样本的两个总体均

值进行检验。

具体步骤如下：

第1步：

输人相关数据。

第2步：

选择“工具”下拉菜单。

第3步：

选择“数据分析”选项。

第4步：

在分析工具中选择“z—检验：

双样本平均差检验”。

第5步：

当对话框出现时，在“变量1的区域

（1）”设置框内键人数据单元格区域A1：

A30，在

“变量2的区域

（2）”设置框内键人数据单元格区域B1：

B30，在假设平均差设置框内键人0，在“变量1的方差（已知）”设置框内键人数据单元格区域本例题的方差值16，在“变量2的方差（已知）”设置框内键人数据单元格区域本例题的方差值12，在“a（A）”设置框内键人0．05，在“输出区域”设置框内键人数据单元格区域D1，然后选择“确定”，得到最终输出结果。

见表7．6和表7．7。

检验统计量的值z=1．41463出现在单元格E8中。

双边p—值=0．15718出现在单元格Ell中。

由于P值大于0．025，所以不能拒绝原假设。

（二）小样本条件下

以应用案例4（等方差情况下），某生产车间为了比较改进生产工艺前后，工人组装产品的平均用时是否缩短，在改进生产工艺前后各抽取12名工人，调查得到他们某次组装产品的时间数据（单位：

秒）如下：

假设改进生产工艺前后工人组装产品的时间均服从正态分布，且方差相等，试在0．05的显著性水平下检验改进生产工艺后工人的平均组装产品的时间是否比以前显著缩短?

说明如何在小样本条件下，利用Excel对独立样本的两个总体均值进行检验。

具体步骤如下：

第1步：

输入相关数据。

第2步：

选择“工具”下拉菜单。

第3步：

选择“数据分析”选项。

第4步：

在分析工具中选择“t—检验：

双样本等方差检验”。

第5步：

当对话框出现时，在“变量1的区域

（1）”设置框内键人数据单元格区域A1：

A12，在“变量2的区域

（2）”设置框内键人数据单元格区域趴：

B12，在假设平均差设置框内键人0，在“o（A）”设置框内键人0．05，在“输出区域”设置框内键人数据单元格区域D1，然后选择“确定”，得到最终输出结果。

见表7．8和表7．9。

0．021158出现在单元格E11中。

由于p值小于0．05，所以拒绝原假设。

注：

若已知两总体方差不相等，我们只需在第4步中重新作出选择，即在分析工具中选择“t—检验：

双样本异方差检验”。

三、配对样本的两个总体均值差异的假设检验

说明如何在小样本条件下，利用Excel对配对样本的两个总体均值进行检验。

具体步骤如下：

第1步：

输入相关数据。

第2步：

选择“工具”下拉菜单。

第3步：

选择“数据分析”选项。

第4步：

在分析工具中选择“t—检验：

平均值的成对二样本分析”。

第5步：

当对话框出现时，在“变量1的区域

（1）”设置框内键人数据单元格区域A1：

A6，在“变量2的区域

（2）”设置框内键人数据单元格区域趴：

B6，在“假设平均差”设置框内键人0，在“a（A）”设置框内键人0．05，在“输出区域”设置框内键人数据单元格

（注：

可编辑下载，若有不当之处，请指正，谢谢!

）