用Excel进行统计推断.doc

如有你有帮助，请购买下载，谢谢！

第三部分 用 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 88 ． 11 28 ． 19 98 ． 13 6 ． 10 6 ． 14 8 ．

6 88 ． 11 78 ． 20 98 ． 24 4 ． 12 3 ． 14 8 ．

1页

如有你有帮助，请购买下载，谢谢！

6 88 ． 13 68 ． 13 6 ． 30 3 ． 14 6 ． 14 8 ．

8 28 ． 14 98 ． 14 7 ． 96 ． 14 6 ． 17 4 ．

96 ． 15 68 ． 15 8 ． 96 ． 12 9 ． 5 38 ．

10 18 ． 15 68 ． 20 5 ． 10 6 ． 14 8 ， 7 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％信赖度。
Standard dev 为此数据的总体标准差，且假定为已知。
Size 代表样本大小。
以搭车为例:"50 位乘客，每天搭乘火车的平均时间为 60 分钟，总体标准差为 10 分钟，试
计算总体平均数的 95％信赖区间为多少?"
首先必须先算出信赖区间，接着才可以计算出信赖极限范围。
利用 CONFIDENCE 计算信赖极限范围的操作方法如下：
打开"乘车时间"表
步骤 1：移动鼠标选择要计算信赖区间的单元格 B9，然后输入"=CONFIDENCE(1-$B$3，
$B$4，$B$5)" ， 再按下回车键。
步骤 2：移动鼠标选择单元格 B10，然后输入"=B6-B9" 再按下回车键。 ，

2页

如有你有帮助，请购买下载，谢谢！

步骤 3：移动鼠标选择单元格 B11，然后输入"=B6+B9" ，再按下回车键。 完成后，画
面显示如下： 完成后，得知每人每天搭乘火车的平均时间为 57．至 62．分钟。
(三)小样本条件下，单一总体均值的置信区间估计 （用 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
， 年底很多类型的国产轿车价格都比年中有所下降，有关调查人

3页