Excel 在推断统计中的应用2.docx

Excel 在推断统计中的应用2.docx

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

Excel在推断统计中的应用2

三、随机抽样的工具……………………第1页

四、由样本推断总体……………………第2页

五、假设检验……………………………第3页

六、双样本等均值假设检验……………第4页

七、正态性的X2检验…………………第5页

三、随机抽样的工具

（一）简介：

Excel中的Rand（）函数可以返回大于等于0小于1的均匀分布随机数，Rand（）不带任何参数运行，每次计算时时都将返回一个新的数值。

RAND（）函数可以被用来作为不重复抽样调查的工具。

（二）操作步骤：

如图附-11所示有10个象征性的样本数据，欲从中随机抽取5个数据可按如下步骤操作：

      图附-11

1．选择B2单元格，输入公式“=RAND（）”并回车。

2．拖动B2单元格右下角的填充柄至B11单元格，并在B1单元格输入标题“RANDOM”。

3.选取单元格B2至B11，右击选中的区域选择“复制”，再次右击选中的区域，选择“选择性粘贴”，单击选项“数值”后，点击“确定”按扭。

4.选取单元格A2至B11单元格，选择“数据”菜单项下的排序子菜单。

5.选取“RANDOM”为主要关键字，然后点击“确定”按扭。

排序结果如图附-12所示，A2至A6单元格的样本即为随机抽取的5个样本。

     图附-12

（三）结果说明：

1.以上进行的是不重复随机抽样，可以用类似的方法，利用Excel的RANDBETWEEN（TOP,BOTTOM）函数实现总体的重复随机抽样。

RANDBETWEEN（TOP,BOTTOM）函数可随机返回介于TOP与BOTTOM之间的整数，抽取此整数对编号的样本可作为总体的重复随机抽样的结果。

2.RAND（）函数返回的是0与1之间均匀的随机数，利用Excel数据分析工具中的随机数发生器，可以生成用户指定类型分布的随机数。

例如0-1正态分布的随机数，指定γ参数的迫松分布的随机数等。

3.利用Excel易于产生各类型随机数的特性，可以用类似的方法方便的进行随机数字模拟试验与随机游走模拟试验。

四、由样本推断总体

（一）简介：

利用Excel的几个函数，如求平均函数AVERAGE、标准差函数STDEV、T分布函数TINV等的组合使用可以构造出一个专门用于实现样本推断总体的Excel工作表。

以下例子先计算样本的平均数和标准差，然后在一定置信水平上估计总体均值的区间范围。

（二）操作步骤：

1．构造工作表。

如图附-13所示，首先在各个单元格输入以下的内容，其中左边是变量名，右边是相应的计算公式。

2.为表格右边的公式计算结果定义左边的变量名。

选定A4:

B6,A8:

B8和A10:

B15单元格（先选择第一部分，再按住CTRL键选取另外两个部分），选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“最左列”选项,然后点击“确定”按扭即可。

         图附-13

3.输入样本数据，和用户指定的置信水平0.95,如图附-13所示。

4.为样本数据命名。

选定D1:

D11单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定”按扭，得到图附-14所示的计算结果。

       图附-14

（三）结果说明：

以上例子说明如何交叉组合使用Excel的公式和函数，以构造出一个能实现样本推断总体有关计算的Excel工作表。

实际上，在用Excel进行数据统计处理之时，许多统计功能可以使用和上例类似的方法，通过组合使用Excel的各类统计函数和公式加以实现的。

五、假设检验

（一）简介：

假设检验是统计推断中的重要内容。

以下例子利用Excel的正态分布函数NORMSDIST、判断函数IF等，构造一张能够实现在总体方差已知情况下进行总体均值假设检验的Excel工作表。

（二）操作步骤：

1．构造工作表。

如图附-15所示，首先在各个单元格输入以下的内容，其中左边是变量名，右边是相应的计算公式。

2.为表格右边的公式计算结果定义左边的变量名。

选定A3:

B4,A6:

B8，A10:

A11,A13:

A15和A17:

B19单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“最左列”选项,然后点击“确定”按扭即可。

      图附-15

2．输入样本数据，以及总体标准差、总体均值假设、置信水平数据。

如图附-16所示。

3．为样本数据命名。

选定C1:

C11单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定”按扭，得到如图附-16中所示的计算结果。

      图附-16

（三）结果说明：

如图附-16所示，该例子的检验结果不论是单侧还是双侧均为拒绝H0假设。

所以，根据样本的计算结果，在5%的显著水平之下,拒绝总体均值为35的假设。

同时由单侧显著水平的计算结果还可以看出，在总体均值是35的假设之下，样本均值小于等于31.4的概率仅为0.020303562。

六、双样本等均值假设检验

（一）简介：

双样本等均值检验是在一定置信水平之下,在两个总体方差相等的假设之下，检验两个总体均值的差值等于指定平均差的假设是否成立的检验。

我们可以直接使用在Excel数据分析中提供双样本等均值假设检验工具进行假设检验。

以下通过一例说明双样本等均值假设检验的操作步骤。

例子如下，某工厂为了比较两种装配方法的效率，分别组织了两组员工，每组9人，一组采用新的装配方法，另外一组采用旧的装配方法。

18个员工的设备装配时间图附-17中表格所示。

根据以下数据，是否有理由认为新的装配方法更节约时间？

         图附-17

（二）操作步骤：

以上例子可按如下步骤进行假设检验。

1.选择“工具”菜单的“数据分析”子菜单，双击“t-检验:

双样本等方差假设”选项，则弹出图附-18所示对话框。

         图附-18

2.分别填写变量1的区域：

$B$1:

$B$10，变量2的区域：

$D$1:

$D$10，由于我们进行的是等均值的检验，填写假设平均差为0，由于数据的首行包括标志项选择标志选项，所以选择“标志”选项，再填写显著水平α为0.05,然后点击“确定”按扭。

则可以得到图附-19所示的结果。

（三）结果分析：

如图附-19中所示，表中分别给出了两组装配时间的平均值、方差和样本个数。

其中，合并方差是样本方差加权之后的平均值,Df是假设检验的自由度它等于样本总个数减2，t统计量是两个样本差值减去

         图附-19

   假设平均差之后再除于标准误差的结果，“P（T<=t）单尾”是单尾检验的显著水平，“t单尾临界”是单尾检验t的临界值，“P（T<=t）双尾”是双尾检验的显著水平，“t双尾临界”是双尾检验t的临界值。

由下表的结果可以看出t统计量均小于两个临界值，所以，在5%显著水平下，不能拒绝两个总体均值相等的假设，即两种装配方法所耗时间没有显著的不同。

   Excel中还提供了以下类似的假设检验的数据分析工具，它们的名称和作用如下：

1．“t-检验：

双样本异方差假设”：

此分析工具可以进行双样本studentt-检验，与双样本等方差假设检验不同，该检验是在两个数据集的方差不等的前提假设之下进行两总体均值差额的检验，故也称作异方差t-检验。

可以使用t-检验来确定两个样本均值实际上是否相等。

当进行分析的样本个数不同时，可使用此检验。

如果某一样本组在某次处理前后都进行了检验，则应使用“成对检验”。

2．“t-检验：

成对双样本均值分析”：

此分析工具可以进行成对双样本学生氏t-检验，用来确定样本均值是否不等。

此t-检验并不假设两个总体的方差是相等的。

当样本中出现自然配对的观察值时，可以使用此成对检验，例如，对一个样本组进行了两次检验，抽取实验前的一次和实验后的一次。

3．“z-检验：

双样本均值分析”：

此分析工具可以进行方差已知的双样本均值z-检验。

此工具用于检验两个总体均值之间存在差异的假设。

例如，可以使用此检验来确定两种汽车模型性能之间的差异情况。

七、正态性的X2检验

（一）简介：

X2 检验可以用来判断所观测的样本是否来自某一特定分布的总体，这种检验亦称为一致性检验。

以下例子，已知某样本的相关统计量和分组频数分布如图附-20所示，试图用X2 检验判断该样本是否来自一正态总体。

         图附-20

（二）操作步骤

1．创建变量名。

选定A3:

C4单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定”按扭即可。

2.计算预期正态概率值。

如图附-21表中所示，在D6单元格输入标志项，在D7:

D11单元格输入公式，分别计算各组的预期正态概率值，在D12计算累积概率值。

          图附-21

3.计算预期频数值。

如图附-22所示，在E6单元格输入标志项，在E7:

E11单元格输入公式，分别计算各组的预期频数，在E12计算累积频数值。

       图附-22

4.计算X2 统计量。

如图附-23所示，在F6单元格输入标志项，在F7:

F11分别输入计算公式，分别计算X2 值，在E12计算X2 平方和,这项就是最后计算出的X2 统计量。

在E13单元格输入标志项“卡方统计量”，为以后的引用作准备。

先选中F12、F13两个单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“尾行”选项,然后点击“确定”按扭即可。

      图附-23

5.如图附-24所示，分别在A14到B20单元格输入自由度、X2 概率值、置信水平、临界值、X2检验结果几项的标志值及计算公式。

其中的自由度=区间分段数-正太分布参数个数-1=5-2-1=2。

    图附-24

（三）结果分析：

如图附-25所示，按照以上操作步骤可以得到表中的计算结果。

            图附-25

    按同样的方法可以作总体泊松分布、总体超几何分布等其它分布的检验。

此类统计应用也是由Excel各类公式和函数综合使用而实现的,为了以后使用方便，和上面的一些例子一样，一般需要将整个表格的计算框架和标志项罗列好，再保存成文件，以后只要对数据项稍作修改即可很快得到计算结果。

如果对Excel宏语言较为熟悉，还可以将它编成一个宏语言程序，加入Excel的工具栏，这样以后使用起来更为方便。