EXCEL分析工具库教程Word格式文档下载.doc
《EXCEL分析工具库教程Word格式文档下载.doc》由会员分享，可在线阅读，更多相关《EXCEL分析工具库教程Word格式文档下载.doc（79页珍藏版）》请在冰点文库上搜索。
重庆三峡学院关文忠
1.随机数发生器主要功能
“随机数发生器”分析工具可用几个分布之一产生的独立随机数来填充某个区域。
可以通过概率分布来表示总体中的主体特征。
例如，可以使用正态分布来表示人体身高的总体特征，或者使用双值输出的伯努利分布来表示掷币实验结果的总体特征。
2.随机数发生器对话框简介
执行如下命令：
“数据｜分析｜数据分析｜随机数发生器”，弹出随机数发生器对话框（图2 1）。
图2 1随机数发生器对话框
该对话框中的参数随分布的选择而有所不同，其余均相同。
变量个数：
在此输入输出表中数值列的个数。
随机数个数：
在此输入要查看的数据点个数。
每一个数据点出现在输出表的一行中。
分布：
在此单击用于创建随机数的分布方法。
包括以下几种：
均匀分布、正态分布、伯努利分布、二项式、泊松、模式、离散。
具体应用将在第3部分举例介绍。
随机数基数：
在此输入用来产生随机数的可选数值。
可在以后重新使用该数值来生成相同的随机数。
输出区域：
在此输入对输出表左上角单元格的引用。
如果输出表将替换现有数据，Excel会自动确定输出区域的大小并显示一条消息。
新工作表：
单击此选项可在当前工作簿中插入新工作表，并从新工作表的A1单元格开始粘贴计算结果。
若要为新工作表命名，请在框中键入名称。
新工作簿：
单击此选项可创建新工作簿并将结果添加到其中的新工作表中。
3.随机数发生器应用举例
3.1.均匀随机数的产生
均匀：
以下限和上限来表征。
其变量是通过对区域中的所有数值进行等概率抽取而得到的。
普通的应用使用范围0到1之间的均匀分布。
相当于工作表函数：
“=a+RAND（）*（b-a）”，与RANDBETWEEN（a,b）”的区别是，RANDBETWEEN产生的是离散型随机数，而随机数发生器产生的是连续型随机数。
离散型函数产生可重复随机数，若想产生无重复随机数，应使用连续型，再从中利用RANK函数产生整型。
通常在进行抽样设计时要产生无重复的整型均匀随机数。
例：
在编号为1至20之间随机抽取10个无重复的均匀随机数。
数据｜分析｜数据分析｜随机数发生器｜“分布”选择均匀，产生对话框（图2 2）：
图2 2均匀随机数对话框
单击“确定”生成连续型随机数（如图2 3A列）。
随机数公式显示模式
图2 3产生随机数
由图可见，所产生的是连续型随机数，若四舍五入取整，在B1单元格输入公式“=ROUND（A1,0）”，并复制到B1:
B10，得到整型随机数（图2 3B列）。
由图可见，数字7出现了两次，为可重复随机数。
在统计调查时，不能对同一调查对象调查两次，应产生无重复随机数。
处理的办法如下：
在A列对总体进行编号；
在B2输入如图所示公式，生产0至1之间的均匀随机数，并复制到B3:
B21；
C列显示样本序号；
选择D2:
D11单元格区域，输入D2单元格所示公式，按住Ctrl+Shift不放再按回车键，生成随机数。
该随机数是无重复的。
当然也可由VLOOKUP函数实现，所处从略。
图2 4无重复随机数的产生（普通模式与公式显著模式）
3.2.正态随机数的产生
正态分布描述：
图2 5正态分布描述
图2 6正态分布曲线
正态：
以平均值和标准偏差来表征，相当于工作表函数“=NORMINV（rand（）,mu,sigma）”
产生10行8列来自均值为100、标准差为10的总体随机数。
“数据｜分析｜数据分析｜随机数发生器”，选择“分布”为“正态”，设置对话框如下：
图2 7随机数发生器对话框的正态分布设置
单击“确定”生成随机数如下：
图2 8产生的正态分布随机数
3.3.产生0-1分布随机数
伯努利：
以给定的试验中成功的概率（p值）来表征。
伯努利随机变量的值为0或1。
等价于函数：
“=IF（RAND（）
”.
产生5列10行的成功概率为0.5的0-1随机数。
验证概率的频率法定义。
数据｜分析｜数据分析｜随机数发生器｜“分布”选择柏努利，设置对话框如下：
图2 90-1随机数对话框
单击“确定”生成随机数（图2 9A至E列）。
在G列输入累积的试验度数；
H2输入公式，统计正态朝上的次数（1的个数）；
I2求得频率；
鼗H2:
I2复制到H3:
I21单元格区域（图2 10、图2 11）。
以H列为横坐标，I列为纵坐标，绘制不带标志点的折线型散点图（图2 12）。
由图可见，随机试验次数的增加，频率逐步趋于0.5.
图2 10产生的0-1分布随机数（公式显示模式）
图2 11产生的0-1分布随机数
图2 12频率法概率定义的验证
3.4.产生二项分布随机数
二项式：
以一系列试验中成功的概率（p值）来表征。
例如，可以按照试验次数生成一系列伯努利随机变量，这些变量之和为一个二项式随机变量。
二项分布描述：
图2 13二项分布描述
图2 14二项分布曲线
某射手中靶的概率为0.8，每次射击10发子弹，射击10次，模拟每次中靶的次数。
“数据｜分析｜数据分析｜随机数发生器”，选择“分布”为“二项”，设置对话框如下：
图2 15随机数发生器对话框的二项分布设置
图2 16产生的二项分布随机数
3.5.产生泊松分布随机数
泊松：
以值λ来表征，λ等于平均值的倒数。
泊松分布经常用于表示单位时间内事件发生的次数，例如，汽车到达收费停车场的平均速率。
其描述如下：
图2 17泊松分布描述
图2 18泊松分布曲线
某加油站，平均每小时前来加油的车辆为10辆，试进行100次模拟，并求其分布情况。
“数据｜分析｜数据分析｜随机数发生器”，选择“分布”为“泊松”，设置对话框如下：
图2 19随机数发生器对话框的泊松分布设置
图2 20产生的泊松分布随机数
求得最大值，最小值，确定组限，利用frequency函数统计频数，并求频率如下图。
图2 21频数统计（公式显示模式）
图2 22频数统计
3.6.产生重复序列
模式：
以下界和上界、步幅、数值的重复率和序列的重复率来表征。
在生物遗传学中常用到重复序列。
EXCEL的“模式”所产生的重复序列是按相同步长产生的重复序列。
如：
下列对话框设置：
图2 23重复序列对话框
可产生的重复序列为：
112233112233112233
3.7.产生离散随机数
离散：
以数值及相应的概率区域来表征。
该区域必须包含两列，左边一列包含数值，右边一列为与该行中的数值相对应的发生概率。
所有概率的和必须为1。
例如：
某商品销售情况根据某干时期统计如下（经验分布）：
销售量
10
15
20
25
30
35
40
概率
0.05
0.10
0.25
0.30
0.15
试进行80次模拟。
（1）在A列和B列输入参数（经验分布）
图2 24离散（经验分布）随机数的产生
（2）数据｜分析｜数据分析｜随机数发生器｜离散，设置如下：
图2 25离散分布对话框
（3）单击确定，在C1:
M8产生80个随机数。
（4）对产生的随机数利用frequency函数统计频数，并求频率（见O:
Q列）。
第三节．抽样
“抽样”分析工具以数据源区域为总体，从而为其创建一个样本。
当总体太大而不能进行处理或绘制时，可以选用具有代表性的样本。
如果确认数据源区域中的数据是周期性的，还可以仅对一个周期中特定时间段中的数值进行采样。
例如，如果数据源区域包含季度销售量数据，则以四为周期进行采样，将在输出区域中生成与数据源区域中相同季度的数值。
1.1.随机抽样
（1）打开一张工作表，输入总体编号或总体标志值（本例A2:
J11单元格区域）。
图3 1随机抽样
（2）数据｜分析｜数据分析｜抽样，弹出抽样对话框：
图3 2随机抽样对话框设置
单击“确定”生成随机样本（图3 1L列）。
注意，该样本是可重复抽样，重复率与总体单位数成反比，与样本量成正比。
1.2.周期抽样
从1至10编号按固定周期间隔分别为2、3、4、5抽样。
图3 3周期抽样对话框设置
单击“确定”抽得样本（D列），取间隔依次取3、4、5,输出区域依次改为E2、F2、G2,得随机数如图图3 4E、F、G列。
图3 4周期抽取的样本
该种抽样类似等距抽样，但不同的是统计学中的等距抽样是在第1组进行简单随机抽样，以后的样本等于首样本位置依次加组距的k倍。
第四节．直方图
1.直方图的功能
“直方图”分析工具可计算数据单元格区域和数据接收区间的单个和累积频率。
此工具可用于统计数据集中某个数值出现的次数，其功能基本上相当于函数FREQUENCY。
所不同的是可以添加累积百分比、百分比排序及插入图表等。
需要注意的是，该工具只能对数值型标志进行统计，且各组频数是包含组上限的。
如统计学生成绩，若组限确定为“60以下、60-70、70-80、80-90、90-100”则统计结果将60分划分为不及格组之中。
因此可根据最小分值差确定上限，如“0-59.5,…”,更强大的数据整理工具可使用“数据透视表”工具。
2.直方图工具的使用
对图中的数据按组数7进行等距分组，利用直方图工具统计频数。
图4 1统计分组观测值数据
操作步骤：
（1）先确定组上限
图4 2组上限的确定（公式显著模式）
利用工作表函数在H1和H2单元格求得最大和最小值；
H3求得全距R，H4为确定的组数，H5计算组距。
J2为第1组上限＝最小值+组距；
其他各组上限均等于前组上限+组距。
图4 3组上限
（2）调用直方图工具
在EXCEL表格中进行如下操作：
“数据｜分析｜数据分析｜直方图”，弹出直方图工具对话框。
图4 4直方图对话框设置
输入区域：
观测值所在的单元格区域。
接收区域：
组上限所有的单元格区域。
标志：
如果数据源区域的第一行或第一列中包含标志项，请选中此复选框。
在此输入对输出表左上角单元格的引用，可在当前工作表中输入结果。
在当前工作簿中插入新工作表，并从新工作表的A1单元格开始粘贴计算结果。
击此选项可创建新工作簿并将结果添加到其中的新工作表中。
柏拉图（排序直方图）：
选中此复选框可在输出表中按频率的降序来显示数据。
累积百分比：
选中此复选框可在输出表中生成一列累积百分比值，并在直方图中包含一条累积百分比线。
图表输出：
选中此选项可在输出表中生成一个嵌入直方图。
单击“确定”生成如下分析结果报告。
图4 5直方图统计分组结果
第五节．描述统计
1.描述统计工具的功能
2.EXCEL统计描述工具的使用
对如下19个数据，利用统计描述工具求各统计指标：
27，98，91，38，73，2，100，58，98，44，51，5，43，3，87，95，57。
（1）先输入数据（如图2A列）
（2）从“数据”选项卡选择“数据分析”，选择“统计描述”，单击“确定”弹出对话框如下：
图5 1统计描述对话框
“描述统计”对话框各选项含义如下：
数据源区域：
在此输入待分析数据区域的单元格引用。
引用必须由两个或两个以上按列或行排列的相邻数据区域组成。
分组方式：
若要指示数据源区域中的数据是按行还是按列排列，请单击“行”或“列”。
标志位于第一行/标准位于第一列：
如果数据源区域的第一行中包含标志项，请选中“标志位于第一行”复选框。
如果数据源区域的第一列中包含标志项，请选中“标志位于第一列”复选框。
如果数据源区域中没有标志项，则该复选框将被清除。
MicrosoftOfficeExcel将在输出表中生成适当的数据标志。
平均数置信度：
如果需要在输出表的某一行中包含平均数的置信度，请选中此选项。
在框中，输入要使用的置信度。
例如，数值95%可用来计算在显著性水平为5%时的平均数置信度。
第K大值：
如果需要在输出表的某一行中包含每个数据区域中的第k大值，请选中此选项。
在框中，输入k的数字。
如果输入1，则该行将包含数据集中的最大值。
第K小值：
如果需要在输出表的某一行中包含每个数据区域中的第k小值，请选中此选项。
如果输入1，则该行将包含数据集中的最小值。
此工具将为每个数据集产生两列信息。
左边一列包含统计标志，右边一列包含统计值。
根据所选择的“分组方式”选项，Excel将为数据源区域中的每一行或每一列生成一个两列的统计表。
汇总统计：
如果需要Excel在输出表中为下列每个统计结果生成一个字段，请选中此选项。
这些统计结果有：
平均值、标准误差（相对于平均值）、中值、众数、标准偏差、方差、峰值、偏斜度、极差（全距）、最小值、最大值、总和、计数、最大值（#）、最小值（#）和置信度。
（3）单击“确定”生成统计描述结果
图5 2统计描述分析结果
第六节．排位与百分比排位
“排位与百分比排位”分析工具可以产生一个数据表，在其中包含数据集中各个数值的顺序排位和百分比排位。
该工具用来分析数据集中各数值间的相对位置关系。
该工具使用工作表函数RANK和PERCENTRANK。
10名同学统计学考试成绩如下：
编号 1 2 3 4 5 6 7 8 9 10
成绩 88 90 85 76 91 60 55 85 85 86
试进行排位和百分比排位。
（1）在EXCEL中输入数据（图6 1B列）
图6 1排位与百分比排位结果
（2）数据｜分析｜数据分析｜排位与百分比排位，弹出对话框如下：
图6 2排位与百分比排位对话框设置
（3）单击“确定”生成排位结果如图6 1D:
G列。
其中的百分比排位为:
小于该值的个数/（小于该值的个数+大于该值的个数）
如88,小于该值的有7个，大于该值的有2个，百分比排位为7/9＝77.78%，该工具截去了十分位数。
第七节．Z检验：
双样本平均差检验
1.Z检验：
双样本均值差检验概述
（1）假设条件
l两个样本是独立的样本
l正态总体或非正态总体大样本（样本量不小于30）
l两样本方差已知
（2）检验统计量及其分布、原假设及拒绝域
表7 1z检验原假设、统计量及拒绝域
2.Z检验工具的使用
对如下两样本标准差均为10,试以0.05的显著水平检验两样本均值是否相等。
序号
1
2
3
4
5
6
7
8
9
X
50
43
53
38
41
45
49
Y
47
66
58
54
44
59
62
（1）在EXCEL中输入数据（图7 2A:
C列）。
（2）数据｜分析｜数据分析｜z检验：
双样本平均差检验，设置对话框如下。
图7 1z检验：
双样本平均差检验对话框
（2）单击“确定”生成分析报告。
图7 2检验结果
本问题是检验两样本均值是否相等，故为双尾检验。
由分析报告可见，截尾概率为0.001756<
0.05，拒绝均值相等的原假设。
第八节．t检验：
成对双样本平均值
1.t检验：
成对双样本平均值检验概述
l两个总体配对差值构成的总体服从正态分布
l配对差是由总体差随机抽样得来的
l数据配对或匹配（重复测量（前/后））
2.检验：
成对双样本平均值工具的应用
对如下成对数据检验X的均值是否大于Y的均值。
图8 1数据资料
（1）数据｜分析｜数据分析｜t检验：
成对双样本平均值，弹出对话框并设置如下：
图8 2平均值成对双样本检验对话框
（2）单击“确定”得检验结果报告：
图8 3检验结果
图8 4单边t检验拒绝域
第九节．t检验：
双样本等方差假设检验概述
l两个独立的小样本
l两总体都是正态总体
l两总体方差未知，但值相等
表9 1z检验原假设、统计量及拒绝域
2.t检验：
双样本等方差假设工具的应用
对如下数据检验X与Y的均值，假设两总体方差相等，检验两总体均值是否存在显著差异（显著水平0.05）。
图9 1数据资料
图9 2单等方差检验对话框
报告结果显示，双尾P值0.84>
0.05不拒绝原假设，即认为两总体均值无显著差异。
图9 3检验结果报告
第十节．t检验：
1.t检验：
双样本异方差假设检验概述
·
两总体都是正态总体
两总体方差未知，且值不等
表10 1z检验原假设、统计量及拒绝域
2.t检验：
双样本异方差假设工具应用
对如下数据检验X与Y的均值，假设两总体方差不等，检验两总体均值是否存在显著差异（显著水平0.05）。
图10 1数据资料
图10 2异方差检验对话框
（2）单击“确定”得检验结果报告。
由报告可见，双尾截尾概率（P值）为0.85>
0.05不拒绝原假设，即两样本总体均值无显著差异。
我们关注的是P值，当该值小于显著水平时，图中的P值值远小于0.05,效应显著。
图10 3检验结果报告
第11节．F检验：
双样本方差齐性检验
1.F检验简介
F检验又叫方差齐性检验。
从两研究总体中随机抽取样本，要对这两个样本进行比较的时候，首先要判断两总体方差是否相同，即方差齐性。
若两总体方差相等，则直接用t检验，若不等，可采用秩和检验等方法。
其中要判断两总体方差是否相等，就可以用F检验。
F检验法是英国统计学家Fisher提出的，主要通过比较两组数据的方差S2，以确定他们的精密度是否有显著性差异。
至于两组数据之间是否存在系统误差，则在进行F检验并确定它们的精密度没有显著性差异之后，再进行t检验。
查F分布临界值表得临界值Fα，如果F<
Fα表明两组数据没有显著差异；
F≥Fα表明两组数据存在显著差异。
若能得到F所对应的截尾概率（P值），则P值小于显著水平时差异显著。
F分布函数描述见（图10 3）,分布曲线见（图11 2）.
图11 1F分布基本概念
图11 2F分布曲线
图11-2蓝色部分为面积为F分布累积概率＝1-α；
红色部分的概率则为α，横轴为F值。
2.F检验：
双样本方差工具的使用
对如下数据，利用EXCEL的F检验工具检验两组数据方差是否有显著差异。
51
42
48
36
61
46
（1）在EXCEL中输入数据。
图11 3数据资料
（2）从“数据”选项卡选择“数据分析”，选择“F检验：
双样本方差”，单击“确定”弹出对话框如下：
图11 4F检验对话框
（3）单击“确定”得到输出结果（图11 5）
图11 5F检验结果
由图3可见，F统计量＝1.488,F临界值为3.1789，F0.05，没有落入否定域，不拒绝原假设。
第12节．单因素方差分析
1.单因素方差分析基本理论
（1）单因素方差分析的概念
单因素方差分析，是指对单因素试验结果进行分析，检验因素对试验结果有无显著性影响的方法。
单因素方差分析是两个样本平均数比较的引伸，它是用来检验多个平均数之间的差异，从而确定因素对试验结果有无显著性影响的一种统计方法。
。
因素：
影响研究对象的某一指标、变量。
水平：
因素变化的各种状态或因素变化所分的等级或组别。
单因素试验：
考虑的因素只有一个的试验叫单因素试验。
例如，将抗生素注入人体会产生抗生素与血浆蛋白质结合的现象，以致减少了药效。
下表列出了5种常用的抗生素注入到牛的体内时，抗生素与血浆蛋白质结合的百分比。
现需要在显著性水平α=0.05下检验这些百分比的均值有无显著的差异。
设各总体服从正态分布，且方差相同。
表12 1试验数据
青霉素
四环素
链霉素
红霉素
氯霉素
29.6
27.3
5.8
21.6
29.2
24.3
32.6
6.2
17.4
32.8
28.5
30.8
11.0
18.3
25.0
32.0
34.8
8.3
19.0
24.2
在这里，试验的指标是抗生素与血浆蛋白质结合的百分比，抗生素为因素
升级会员 