Excel在推断统计中的应用.pdf

1 Excel 在推断统计中的应用一、二项分布工具二、其它分布的函数三、随机抽样的工具四、由样本推断总体五、假设检验六、双样本等均值假设检验七、正态性的X2检验一、二项分布工具（一）简介：在Excel 中想要计算二项分布的概率分布、累积概率，需要利用Excel 的工作表函数 BINOMDIST。函数 BINOMDIST 适用于固定次数的独立实验，实验的结果只包含成功或失败二种情况，且每次实验成功的概率固定不变。例如，已知次品概率的情况下，函数BINOMDIST 可以计算 10 个产品中发现2 个次品的概率。以下例子说明如何在Excel 中计算二项分布的概率，以及如何建立二项分布图表。（二）操作步骤：例子如下所示，一个推销员打了六个电话，推销成功的概率是0.3，那么可以按以下步骤建立推销成功次数的概率分布图表。1 如图附-8 所示，先在Excel 之下建立好概率分布表格的框架。图附-8 2 如图附-9 所示,先在 B7 至 F7 单元格分别输入概率计算公式。2 图附-9 3 公式的拷贝。选取B7 至 F7 单元格，拖动“填充柄”至F13 单元格即可完成公式的拷贝操作。结果图附-10 所示。图附-10 4下面开始创建二项分布图表。选取B7 至 B13 单元格，选取“插入”菜单的“图表”子菜单。5选择“柱状图”，然后单击“下一步”。6.单击“系列”标签，单击“分类(X)轴标志”框，并用鼠标选取A7至 A13 单元格为图表X 轴的轴标，然后单击“下一步”。7.分别键入图表名称“二项分布图”，X 轴名称“成功次数”，Y 轴名称“成功概率”，单击“完成”按扭即可生成二项分布图表。（三）结果说明:如图附-10 所示，利用 Excel 的 BINOMDIST 的函数可以计算出二项分布的概率以及累积概率。BINOMDIST 函数可以带四个参数，各参数的含义分别是：实验成功的次数，实验的总次数，每次实验中成功的概率，是否计算累积概率。第四个参数是一个逻辑值，如果为 TRUE，函数 BINOMDIST 返回累积分布函数，如果为FALSE，返回概率密度函数。二、其它分布的函数（一）函数CRITBINOM：1 说明：函数 CRITBINOM 可称为 BINOMDIST 的逆向函数，它返回使累积二项式分布概率P(X=x)大于等于临界概率值的最小值。2语法：CRITBINOM(trials,probability_s,alpha)Trials：贝努利实验次数。Probability_s：一次试验中成功的概率。Alpha：临界概率。3举例:CRITBINOM(6,0.5,0.75)等于 4，表明如果每次试验成功的概率为0.5,那么 6 次试验中成功的次数小于等于4 的概率恰好超过或等于0.75。（二）函数HYPGEOMDIST：1说明：函数HYPGEOMDIST 返回超几何分布。给定样本容量、总体容量和样本总体中成功的次数，函数 HYPGEOMDIST 返回样本取得给定成功次数的概率。使用函数HYPGEOMDIST 可以解决有限总体的问题，其中每个观察值或者为成功或者为失败，且给定样本区间的所有子集有相等的发生概率。3 2.语法：HYPGEOMDIST(sample_s,number_sample,population_s,number_population)Sample_s：样本中成功的次数。Number_sample：样本容量。Population_s：样本总体中成功的次数。Number_population：样本总体的容量。3.举例：容器里有20 块巧克力，8 块是焦糖的，其余12 块是果仁的。如果从中随机选出4 块，下面函数计算式计算出只有一块是焦糖巧克力的概率：HYPGEOMDIST(1,4,8,20)=0.363261。（三）函数NEGBINOMDIST：1说明：函数 NEGBINOMDIST 返回负二项式分布。当每次试验成功概率固时，函数 NEGBINOMDIST 返回在到达指定次数成功之前，出现n 次失败的概率。此函数与二项式分布相似，只是它的成功次数固定，试验总数为变量。与二项分布类似的是，试验次数被假设为自变量。2 语法:NEGBINOMDIST(number_f,number_s,probability_s)Number_f:失败次数。Number_s:成功的临界次数。Probability_s:成功的概率。3 举例：例如，如果要找出5 个反应敏捷的人，且已知具有这种特征的候选人的概率为0.3。以下公式将计算出在找到5 个合格候选人之前，需要面试10 个候选人的概率：NEGBINOMDIST(10,5,0.3)=0.06871（四）函数POISSON:1说明：函数POISSON 返回泊松分布。泊松分布通常用于预测一段时间内事件发生指定次数的概率，比如一分钟内通过收费站的轿车的数量为n 的概率。2语法：POISSON(x,mean,cumulative)X：事件数。Mean：期望值。Cumulative：为一逻辑值，确定所返回的概率分布形式。如果 cumulative 为 TRUE，函数 POISSON 返回累积分布函数，即，随机事件发生的次数在0 和 x 之间（包含 0 和 1）；如果为 FALSE，则返回概率密度函数，即，随机事件发生的次数恰好为x。3 举例：POISSON(2,5,FALSE)=0.084224 表明，若某一收费站每分种通过的轿车平均数量为5 辆，那么某一分钟通只2 辆的概率为 0.084224。（五）正态分布函数NORMDIST：1说明：正态分布在模拟现实世界过程和描述随机样本平均值的不确定度时有广泛的用途。函数 NORMDIST 返回给定平均值和标准偏差的正态分布的累积函数。同样可以用类似“七”中的方法，利用 NORMDIST 函数建立正态分布密度函数图，这里不再赘述。2.语法：NORMDIST(x,mean,standard_dev,cumulative)X：为需要计算其分布的数值。Mean：分布的算术平均值。Standard_dev：分布的标准偏差。Cumulative：为一逻辑值，指明函数的形式。如果cumulative 为 TRUE，函数 NORMDIST 返回累积分布函数；如果为FALSE，返回概率密度函数。3举例：例如，公式NORMDIST(6,5,2,0)返回平均值为5、标准差为 2 的正态函数当X=6 时概率密度函数的数值，公式NORMDIST（60，50，4，1）返回平均值为50、标准差为4 的正态分布函数当 X=60 时累积分布函数的数值。（六）函数 NORMSDIST：1说明：函数NORMSDIST 返回标准正态分布的累积函数。2 语法：NORMSDIST(z)4 Z 为需要计算其分布的数值。3举例：NORMSDIST(0)=0.5（七）函数NORMSINV：1说明：函数NORMSINV 返回标准正态分布累积函数的逆函数。2 语法：NORMSINV(probability)Probability：正态分布的概率值。3举例：NORMSINV(0.5)=0（八）t 分布函数 TDIST:1说明：函数 TDIST 返回 student 的 t 分布数值。T 分布用于小样本数据集合的假设检验。使用此函数可以代替t 分布的临界值表。2语法：TDIST(x,degrees_freedom,tails)X：为需要计算分布的数字。Degrees_freedom：为表示自由度的整数。Tails：指明返回的分布函数是单尾分布还是双尾分布。如果tails=1，函数 TDIST 返回单尾分布。如果tails=2，函数 TDIST 返回双尾分布。3 举例：TDIST(1.96,60,2)=0.054645三、随机抽样的工具（一）简介：Excel 中的 Rand()函数可以返回大于等于0 小于 1 的均匀分布随机数，Rand()不带任何参数运行，每次计算时时都将返回一个新的数值。RAND()函数可以被用来作为不重复抽样调查的工具。（二）操作步骤：如图附-11 所示有 10 个象征性的样本数据，欲从中随机抽取5 个数据可按如下步骤操作：图附-11 图附-12 1 选择 B2 单元格，输入公式“=RAND()”并回车。2拖动 B2 单元格右下角的填充柄至B11 单元格，并在B1 单元格输入标题“RANDOM”。3.选取单元格B2 至 B11，右击选中的区域选择“复制”，再次右击选中的区域，选择“选择性粘贴”，单击选项“数值”后，点击“确定”按扭。4.选取单元格A2 至 B11 单元格，选择“数据”菜单项下的排序子菜单。5.选取“RANDOM”为主要关键字，然后点击“确定”按扭。排序结果如图附-12 所示，A2 至 A6 单元格的样本即为随机抽取的5 个样本。5（三）结果说明：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 键选取另外两个部分)，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“最左列”选项,然后点击“确定”按扭即可。图附-133.输入样本数据，和用户指定的置信水平0.95,如图附-13 所示。4.为样本数据命名。选定D1:D11 单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定”按扭，得到图附-14 所示的计算结果。（三）结果说明：以上例子说明如何交叉组合使用Excel 的公式和函数，以构造出一个能实现样本推断总体有关计算的Excel 工作表。实际上，在用Excel 进行数据统计处理之时，许多统计功能可以使用和上例类似的方法，通过组合使用Excel 的各类统计函数和公式加以实现的。6 图附-14五、假设检验（一）简介：假设检验是统计推断中的重要内容。以下例子利用Excel 的正态分布函数NORMSDIST、判断函数 IF 等，构造一张能够实现在总体方差已知情况下进行总体均值假设检验的Excel 工作表。（二）操作步骤：1构造工作表。如图附-15 所示，首先在各个单元格输入以下的内容，其中左边是变量名，右边是相应的计算公式。2.为表格右边的公式计算结果定义左边的变量名。选定A3:B4,A6:B8，A10:A11,A13:A15 和A17:B19 单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“最左列”选项,然后点击“确定”按扭即可。7 图附-152输入样本数据，以及总体标准差、总体均值假设、置信水平数据。如图附-16 所示。3为样本数据命名。选定C1:C11 单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定”按扭，得到如图附-16 中所示的计算结果。图附-16（三）结果说明：如图附-16 所示，该例子的检验结果不论是单侧还是双侧均为拒绝H0 假设。所以，根据样本的计算结果，在5%的显著水平之下,拒绝总体均值为35 的假设。同时由单侧显著水平的计算结果还可以看出，在总体均值是35 的假设之下，样本均值小于等于31.4 的概率仅为 0.020303562。六、双样本等均值假设检验（一）简介：双样本等均值检验是在一定置信水平之下,在两个总体方差相等的假设之下，检验两个总体均值的差值等于指定平均差的假设是否成立的检验。我们可以直接使用在Excel 数据分析中提供双样本等均值假设检验工具进行假设检验。以下通过一例说明双样本等均值假设检验的操作步骤。例子如下，某工厂为了比较两种装配方法的效率，分别组织了两组员工，每组9 人，一组采用新的装配方法，另外一组采用旧的装配方法。18 个员工的设备装配时间图附-17 中表格所示。根据以下数据，是否有理由认为新的装配方法更节约时间？8 图附-17（二）操作步骤：以上例子可按如下步骤进行假设检验。1.选择“工具”菜单的“数据分析”子菜单，双击“t-检验:双样本等方差假设”选项，则弹出图附-18 所示对话框。图附-182.分别填写变量1 的区域：$B$1:$B$10，变量 2 的区域：$D$1:$D$10，由于我们进行的是等均值的检验，填写假设平均差为0，由于数据的首行包括标志项选择标志选项，所以选择“标志”选项，再填写显著水平为 0.05,然后点击“确定”按扭。则可以得到图附-19 所示的结果。（三）结果分析：如图附-19 中所示，表中分别给出了两组装配时间的平均值、方差和样本个数。其中，合并方差是样本方差加权之后的平均值,Df 是假设检验的自由度它等于样本总个数减2，t 统计量是两个样本差值减去9 图附-19假设平均差之后再除于标准误差的结果，“P(T=t)单尾”是单尾检验的显著水平，“t 单尾临界”是单尾检验t 的临界值，“P(T=t)双尾”是双尾检验的显著水平，“t 双尾临界”是双尾检验 t 的临界值。由下表的结果可以看出t 统计量均小于两个临界值，所以，在5%显著水平下，不能拒绝两个总体均值相等的假设，即两种装配方法所耗时间没有显著的不同。Excel 中还提供了以下类似的假设检验的数据分析工具，它们的名称和作用如下：1“t-检验：双样本异方差假设”：此分析工具可以进行双样本student t-检验，与双样本等方差假设检验不同，该检验是在两个数据集的方差不等的前提假设之下进行两总体均值差额的检验，故也称作异方差t-检验。可以使用t-检验来确定两个样本均值实际上是否相等。当进行分析的样本个数不同时，可使用此检验。如果某一样本组在某次处理前后都进行了检验，则应使用“成对检验”。2“t-检验：成对双样本均值分析”：此分析工具可以进行成对双样本学生氏t-检验，用来确定样本均值是否不等。此 t-检验并不假设两个总体的方差是相等的。当样本中出现自然配对的观察值时，可以使用此成对检验，例如，对一个样本组进行了两次检验，抽取实验前的一次和实验后的一次。3“z-检验：双样本均值分析”：此分析工具可以进行方差已知的双样本均值z-检验。此工具用于检验两个总体均值之间存在差异的假设。例如，可以使用此检验来确定两种汽车模型性能之间的差异情况。七、正态性的X2检验（一）简介：X2 检验可以用来判断所观测的样本是否来自某一特定分布的总体，这种检验亦称为一致性检验。以下例子，已知某样本的相关统计量和分组频数分布如图附-20 所示，试图用X2 检验判断该样本是否来自一正态总体。10 图附-20（二）操作步骤1创建变量名。选定A3:C4 单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定”按扭即可。2.计算预期正态概率值。如图附-21 表中所示，在D6 单元格输入标志项，在D7:D11 单元格输入公式，分别计算各组的预期正态概率值，在D12 计算累积概率值。图附-21 3.计算预期频数值。如图附-22 所示，在 E6 单元格输入标志项，在E7:E11 单元格输入公式，分别计算各组的预期频数，在E12 计算累积频数值。图附-2211 4.计算 X2 统计量。如图附-23 所示，在 F6 单元格输入标志项，在F7:F11 分别输入计算公式，分别计算 X2 值，在 E12 计算 X2 平方和,这项就是最后计算出的X2 统计量。在 E13 单元格输入标志项“卡方统计量”，为以后的引用作准备。先选中F12、F13 两个单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“尾行”选项,然后点击“确定”按扭即可。图附-235.如图附-24 所示，分别在A14 到 B20 单元格输入自由度、X2 概率值、置信水平、临界值、X2 检验结果几项的标志值及计算公式。其中的自由度=区间分段数-正太分布参数个数-1=5-2-1=2。图附-24（三）结果分析：如图附-25 所示，按照以上操作步骤可以得到表中的计算结果。按同样的方法可以作总体泊松分布、总体超几何分布等其它分布的检验。此类统计应用也是由 Excel 各类公式和函数综合使用而实现的,为了以后使用方便，和上面的一些例子一样，一般需要将整个表格的计算框架和标志项罗列好，再保存成文件，以后只要对数据项稍作修改即可很快得到计算结果。如果对 Excel 宏语言较为熟悉，还可以将它编成一个宏语言程序，加入 Excel 的工具栏，这样以后使用起来更为方便。12 图附-25“F-检验：双样本方差分析”分析工具此分析工具可以进行双样本F-检验，用来比较两个样本总体的方差。例如，可以对参加游泳比赛的两个队的时间记分进行F-检验，查看二者的样本方差是否不同。“t-检验：成对双样本均值分析”分析工具此分析工具及其公式可以进行成对双样本学生氏t-检验，用来确定样本均值是否不等。此t-检验并不假设两个总体的方差是相等的。当样本中出现自然配对的观察值时，可以使用此成对检验，例如对一个样本组进行了两次检验，抽取实验前的一次和实验后的一次。“t-检验：双样本等方差假设”分析工具此分析工具可以进行双样本学生氏t-检验。此 t-检验先假设两个数据集的平均值相等，故也称作齐次方差 t-检验。可以使用t-检验来确定两个样本均值实际上是否相等。“t-检验：双样本异方差假设”分析工具此分析工具及其公式可以进行双样本学生氏t 检验。此 t-检验先假设两个数据集的方差不等，故也称作异方差t-检验。可以使用t-检验来确定两个样本均值实际上是否相等。当进行分析的样本组不同时，可使用此检验。如果某一样本组在某次处理前后都进行了检验，则应使用“成对检验”。“z-检验：双样本均值分析”分析工具此分析工具可以进行方差已知的双样本均值z-检验。此工具用于检验两个总体均值之间存在差异的假设。例如，可以使用此检验来确定两种汽车模型性能之间的差异情况。13“抽样分析”分析工具此分析工具以输入区域为总体构造总体的一个样本。当总体太大而不能进行处理或绘制时，可以选用具有代表性的样本。如果确认输入区域中的数据是周期性的，还可以对一个周期中特定时间段中的数值进行采样。例如，如果输入区域包含季度销售量数据，以四为周期进行取样，将在输出区域中生成某个季度的样本。“回归分析”分析工具此工具通过对一组观察值使用“最小二乘法”直线拟合，进行线形回归分析。本工具可用来分析单个因变量是如何受一个或几个自变量影响的。例如，观察某个运动员的运动成绩与一系列统计因素的关系，如年龄、身高和体重等。在操作时，可以基于一组已知的体能统计数据，并辅以适当加权，对尚未进行过测试的运动员的表现作出预测。“直方图”分析工具在给定工作表中数据单元格区域和接收区间的情况下，计算数据的个别和累积频率，用于统计有限集中某个数值元素的出现次数。例如，在一个有20 名学生的班级里，可以确定以字母打分（如A、B-等）所得分数的分布情况。直方图表会给出字母得分的边界，以及在最低边界与当前边界之间某一得分出现的次数。出现频率最多的某个得分即为数据组中的众数。“Anova：单因素方差分析”分析工具此分析工具通过简单的方差分析(anova)，对两个以上样本均值进行相等性假设检验（抽样取自具有相同均值的样本空间）。此方法是对双均值检验（如t-检验）的扩充。