《EXCEL分析工具库教程.doc》由会员分享，可在线阅读，更多相关《EXCEL分析工具库教程.doc（79页珍藏版）》请在三一办公上搜索。
1、EXCEL分析工具库教程第一节：分析工具库概述重庆三峡学院 关文忠“分析工具库”实际上是一个外部宏（程序）模块，它专门为用户提供一些高级统计函数和实用的数据分析工具。利用数据分析工具库可以构造反映数据分布的直方图；可以从数据集合中随机抽样，获得样本的统计测度；可以进行时间数列分析和回归分析；可以对数据进行傅立叶变换和其他变换等。本讲义均在Excel2007环境下进行操作。1.1. 分析工具库的加载与调用打开一张Excel表单，选择“数据”选项卡，看最右边的“分析”选项中是否有“数据分析”，若没有，单击左上角的图标，单击最下面的“Excel选项”，弹出“Excel选项”对话框，在左侧列表中选择“
2、加载项”，在下方有“管理：Excel加载项转到”，单击“转到”，勾选“分析工具库”（加载数据分析工具）和“分析工具库-VBA”（加载分析工具库所需要的VBA函数）（图 11），单击确定，则“数据分析”出现在“数据分析”中。图 11 加载分析工具库1.2. 分析工具库的功能分类分析工具库内置了19个模块，可以分为以下几大类：表 11 随机发生器功能列表分类工具模块抽样设计随机数发生器抽样数据整理直方图参数估计描述统计排位与百分比排位假设检验z-检验：双样本均值差检验t-检验：平均值的成对二样本分析t-检验：双样本等方差假设t-检验：双样本异方差假设F检验：双样本方差检验方差分析方差分析：单因素方
3、差分析方差分析：无重复双因素方差分析方差分析：可重复双因素方差分析相关与回归分析相关系数协方差回归时间序列预测移动平均指数平滑傅利叶分析第二节随机数发生器重庆三峡学院 关文忠1. 随机数发生器主要功能“随机数发生器”分析工具可用几个分布之一产生的独立随机数来填充某个区域。可以通过概率分布来表示总体中的主体特征。例如，可以使用正态分布来表示人体身高的总体特征，或者使用双值输出的伯努利分布来表示掷币实验结果的总体特征。2. 随机数发生器对话框简介执行如下命令：“数据分析数据分析随机数发生器”，弹出随机数发生器对话框（图 21）。图 21随机数发生器对话框该对话框中的参数随分布的选择而有所不同，其余
4、均相同。变量个数：在此输入输出表中数值列的个数。随机数个数：在此输入要查看的数据点个数。每一个数据点出现在输出表的一行中。分布：在此单击用于创建随机数的分布方法。包括以下几种：均匀分布、正态分布、伯努利分布、二项式、泊松、模式、离散。具体应用将在第3部分举例介绍。随机数基数：在此输入用来产生随机数的可选数值。可在以后重新使用该数值来生成相同的随机数。输出区域：在此输入对输出表左上角单元格的引用。如果输出表将替换现有数据，Excel 会自动确定输出区域的大小并显示一条消息。新工作表：单击此选项可在当前工作簿中插入新工作表，并从新工作表的 A1 单元格开始粘贴计算结果。若要为新工作表命名，请在框中
5、键入名称。新工作簿：单击此选项可创建新工作簿并将结果添加到其中的新工作表中。3. 随机数发生器应用举例3.1. 均匀随机数的产生均匀：以下限和上限来表征。其变量是通过对区域中的所有数值进行等概率抽取而得到的。普通的应用使用范围 0 到 1 之间的均匀分布。相当于工作表函数：“= a+RAND()*(b-a)”，与RANDBETWEEN (a,b)”的区别是，RANDBETWEEN产生的是离散型随机数，而随机数发生器产生的是连续型随机数。离散型函数产生可重复随机数，若想产生无重复随机数，应使用连续型，再从中利用RANK函数产生整型。通常在进行抽样设计时要产生无重复的整型均匀随机数。例：在编号为1
6、至20之间随机抽取10个无重复的均匀随机数。数据分析数据分析随机数发生器“分布”选择均匀，产生对话框（图 22）：图 22 均匀随机数对话框单击“确定”生成连续型随机数（如图 23 A列）。随机数 公式显示模式图 23 产生随机数由图可见，所产生的是连续型随机数，若四舍五入取整，在B1单元格输入公式“=ROUND(A1,0)”，并复制到B1:B10，得到整型随机数（图 23 B列）。由图可见，数字7出现了两次，为可重复随机数。在统计调查时，不能对同一调查对象调查两次，应产生无重复随机数。处理的办法如下：在A列对总体进行编号；在B2输入如图所示公式，生产0至1之间的均匀随机数，并复制到B3:B2
7、1；C列显示样本序号；选择D2:D11单元格区域，输入D2单元格所示公式，按住Ctrl+Shift不放再按回车键，生成随机数。该随机数是无重复的。当然也可由VLOOKUP函数实现，所处从略。图 24 无重复随机数的产生（普通模式与公式显著模式）3.2. 正态随机数的产生正态分布描述：图 25 正态分布描述图 26 正态分布曲线正态：以平均值和标准偏差来表征，相当于工作表函数“=NORMINV(rand(),mu,sigma)”例：产生10行8列来自均值为100、标准差为10的总体随机数。“数据分析数据分析随机数发生器”，选择“分布”为“正态”，设置对话框如下：图 27 随机数发生器对话框的正态
8、分布设置单击“确定”生成随机数如下：图 28 产生的正态分布随机数3.3. 产生0-1分布随机数伯努利：以给定的试验中成功的概率（p 值）来表征。伯努利随机变量的值为 0 或 1。等价于函数：“=IF(RAND()”.例：产生5列10行的成功概率为0.5的0-1随机数。验证概率的频率法定义。数据分析数据分析随机数发生器“分布”选择柏努利，设置对话框如下：图 29 0-1随机数对话框单击“确定”生成随机数（图 29 A至E列）。在G列输入累积的试验度数；H2输入公式，统计正态朝上的次数（1的个数）；I2求得频率；鼗H2:I2复制到H3:I21单元格区域（图 210、图 211）。以H列为横坐标，
9、I列为纵坐标，绘制不带标志点的折线型散点图（图 212）。由图可见，随机试验次数的增加，频率逐步趋于0.5.图 210 产生的0-1分布随机数（公式显示模式）图 211产生的0-1分布随机数图 212 频率法概率定义的验证3.4. 产生二项分布随机数二项式：以一系列试验中成功的概率（p 值）来表征。例如，可以按照试验次数生成一系列伯努利随机变量，这些变量之和为一个二项式随机变量。二项分布描述：图 213 二项分布描述图 214 二项分布曲线例：某射手中靶的概率为0.8，每次射击10发子弹，射击10次，模拟每次中靶的次数。“数据分析数据分析随机数发生器”，选择“分布”为“二项”，设置对话框如下：
10、图 215 随机数发生器对话框的二项分布设置单击“确定”生成随机数如下：图 216 产生的二项分布随机数3.5. 产生泊松分布随机数泊松：以值 来表征， 等于平均值的倒数。泊松分布经常用于表示单位时间内事件发生的次数，例如，汽车到达收费停车场的平均速率。其描述如下：图 217 泊松分布描述图 218 泊松分布曲线例：某加油站，平均每小时前来加油的车辆为10辆，试进行100次模拟，并求其分布情况。“数据分析数据分析随机数发生器”，选择“分布”为“泊松”，设置对话框如下：图 219 随机数发生器对话框的泊松分布设置单击“确定”生成随机数如下：图 220 产生的泊松分布随机数求得最大值，最小值，确定
11、组限，利用frequency函数统计频数，并求频率如下图。图 221 频数统计（公式显示模式）图 222 频数统计3.6. 产生重复序列模式：以下界和上界、步幅、数值的重复率和序列的重复率来表征。在生物遗传学中常用到重复序列。EXCEL的“模式”所产生的重复序列是按相同步长产生的重复序列。如：下列对话框设置：图 223 重复序列对话框可产生的重复序列为：1122331122331122333.7. 产生离散随机数离散：以数值及相应的概率区域来表征。该区域必须包含两列，左边一列包含数值，右边一列为与该行中的数值相对应的发生概率。所有概率的和必须为 1。例如：某商品销售情况根据某干时期统计如下（经
12、验分布）：销售量10152025303540概率0.050.100.250.300.150.100.05试进行80次模拟。（1）在A列和B列输入参数（经验分布）图 224 离散（经验分布）随机数的产生（2）数据分析数据分析随机数发生器离散，设置如下：图 225 离散分布对话框（3）单击确定，在C1:M8产生80个随机数。（4）对产生的随机数利用frequency函数统计频数，并求频率（见O:Q列）。第三节抽样重庆三峡学院 关文忠“抽样”分析工具以数据源区域为总体，从而为其创建一个样本。当总体太大而不能进行处理或绘制时，可以选用具有代表性的样本。如果确认数据源区域中的数据是周期性的，还可以仅对一
13、个周期中特定时间段中的数值进行采样。例如，如果数据源区域包含季度销售量数据，则以四为周期进行采样，将在输出区域中生成与数据源区域中相同季度的数值。1.1. 随机抽样（1）打开一张工作表，输入总体编号或总体标志值（本例A2:J11单元格区域）。图 31 随机抽样（2）数据分析数据分析抽样，弹出抽样对话框：图 32 随机抽样对话框设置单击“确定”生成随机样本（图 31L列）。注意，该样本是可重复抽样，重复率与总体单位数成反比，与样本量成正比。1.2. 周期抽样例：从1至10编号按固定周期间隔分别为2、3、4、5抽样。图 33 周期抽样对话框设置单击“确定”抽得样本（D列），取间隔依次取3、4、5,
14、输出区域依次改为E2、F2、G2,得随机数如图图 34E、F、G列。图 34 周期抽取的样本该种抽样类似等距抽样，但不同的是统计学中的等距抽样是在第1组进行简单随机抽样，以后的样本等于首样本位置依次加组距的k倍。第四节直方图重庆三峡学院 关文忠1. 直方图的功能“直方图”分析工具可计算数据单元格区域和数据接收区间的单个和累积频率。此工具可用于统计数据集中某个数值出现的次数，其功能基本上相当于函数FREQUENCY。所不同的是可以添加累积百分比、百分比排序及插入图表等。需要注意的是，该工具只能对数值型标志进行统计，且各组频数是包含组上限的。如统计学生成绩，若组限确定为“60以下、60-70、70
15、-80、80-90、90-100”则统计结果将60分划分为不及格组之中。因此可根据最小分值差确定上限，如“0-59.5,”,更强大的数据整理工具可使用“数据透视表”工具。2. 直方图工具的使用例：对图中的数据按组数7进行等距分组，利用直方图工具统计频数。图 41 统计分组观测值数据操作步骤：（1）先确定组上限图 42 组上限的确定（公式显著模式）利用工作表函数在H1和H2单元格求得最大和最小值；H3求得全距R，H4为确定的组数，H5计算组距。J2为第1组上限最小值+组距；其他各组上限均等于前组上限+组距。图 43 组上限（2）调用直方图工具在EXCEL表格中进行如下操作：“数据分析数据分析直方
16、图”，弹出直方图工具对话框。图 44 直方图对话框设置输入区域：观测值所在的单元格区域。接收区域：组上限所有的单元格区域。标志：如果数据源区域的第一行或第一列中包含标志项，请选中此复选框。输出区域：在此输入对输出表左上角单元格的引用，可在当前工作表中输入结果。新工作表：在当前工作簿中插入新工作表，并从新工作表的 A1 单元格开始粘贴计算结果。若要为新工作表命名，请在框中键入名称。新工作簿：击此选项可创建新工作簿并将结果添加到其中的新工作表中。柏拉图(排序直方图)：选中此复选框可在输出表中按频率的降序来显示数据。累积百分比：选中此复选框可在输出表中生成一列累积百分比值，并在直方图中包含一条累积百
17、分比线。图表输出：选中此选项可在输出表中生成一个嵌入直方图。单击“确定”生成如下分析结果报告。图 45 直方图统计分组结果 第五节描述统计重庆三峡学院 关文忠1.描述统计工具的功能2.EXCEL统计描述工具的使用例：对如下19个数据，利用统计描述工具求各统计指标：27，98，91，38，73，2，100，58，98，44，51，5，43，3，87，95，57。（1）先输入数据（如图2 A列）（2）从“数据”选项卡选择“数据分析”，选择“统计描述”，单击“确定”弹出对话框如下：图 51 统计描述对话框“描述统计”对话框各选项含义如下：数据源区域：在此输入待分析数据区域的单元格引用。引用必须由两个
18、或两个以上按列或行排列的相邻数据区域组成。分组方式：若要指示数据源区域中的数据是按行还是按列排列，请单击“行”或“列”。标志位于第一行/标准位于第一列：如果数据源区域的第一行中包含标志项，请选中“标志位于第一行”复选框。如果数据源区域的第一列中包含标志项，请选中“标志位于第一列”复选框。如果数据源区域中没有标志项，则该复选框将被清除。Microsoft Office Excel 将在输出表中生成适当的数据标志。平均数置信度：如果需要在输出表的某一行中包含平均数的置信度，请选中此选项。在框中，输入要使用的置信度。例如，数值 95% 可用来计算在显著性水平为 5% 时的平均数置信度。第 K 大值：
19、如果需要在输出表的某一行中包含每个数据区域中的第 k 大值，请选中此选项。在框中，输入 k 的数字。如果输入 1，则该行将包含数据集中的最大值。第 K 小值：如果需要在输出表的某一行中包含每个数据区域中的第 k 小值，请选中此选项。在框中，输入 k 的数字。如果输入 1，则该行将包含数据集中的最小值。输出区域：在此输入对输出表左上角单元格的引用。此工具将为每个数据集产生两列信息。左边一列包含统计标志，右边一列包含统计值。根据所选择的“分组方式”选项，Excel 将为数据源区域中的每一行或每一列生成一个两列的统计表。新工作表：单击此选项可在当前工作簿中插入新工作表，并从新工作表的 A1 单元格开
20、始粘贴计算结果。若要为新工作表命名，请在框中键入名称。新工作簿：单击此选项可创建新工作簿并将结果添加到其中的新工作表中。汇总统计：如果需要 Excel 在输出表中为下列每个统计结果生成一个字段，请选中此选项。这些统计结果有：平均值、标准误差（相对于平均值）、中值、众数、标准偏差、方差、峰值、偏斜度、极差（全距）、最小值、最大值、总和、计数、最大值 (#)、最小值 (#) 和置信度。（3）单击“确定”生成统计描述结果图 52 统计描述分析结果第六节排位与百分比排位重庆三峡学院 关文忠“排位与百分比排位”分析工具可以产生一个数据表，在其中包含数据集中各个数值的顺序排位和百分比排位。该工具用来分析数
21、据集中各数值间的相对位置关系。该工具使用工作表函数 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中输入数据（图 61 B列）图 61 排位与百分比排位结果（2）数据分析数据分析排位与百分比排位，弹出对话框如下：图 62 排位与百分比排位对话框设置（3）单击“确定”生成排位结果如图 61D:G列。其中的百分比排位为:小于该值的个数/（小于该值的个数+大于该值的个数）如88,小于该值的有7个，大于该值的有2个，百分比排位
22、为7/977.78%，该工具截去了十分位数。第七节Z检验：双样本平均差检验重庆三峡学院 关文忠1.Z检验：双样本均值差检验概述（1）假设条件l 两个样本是独立的样本l 正态总体或非正态总体大样本（样本量不小于30）l 两样本方差已知（2）检验统计量及其分布、原假设及拒绝域表 71 z检验原假设、统计量及拒绝域2.Z检验工具的使用例：对如下两样本标准差均为10,试以0.05的显著水平检验两样本均值是否相等。序号12345678910X35504353384140454149Y47664358415444595962（1）在EXCEL中输入数据（图 72A:C列）。（2）数据分析数据分析z检验：双
23、样本平均差检验，设置对话框如下。图 71 z检验：双样本平均差检验对话框（2）单击“确定”生成分析报告。图 72 检验结果本问题是检验两样本均值是否相等，故为双尾检验。由分析报告可见，截尾概率为0.0017560.05不拒绝原假设，即认为两总体均值无显著差异。图 93 检验结果报告第十节t检验：双样本异方差假设重庆三峡学院 关文忠1.t检验：双样本异方差假设检验概述（1）假设条件 两总体都是正态总体 两总体方差未知，且值不等（2）检验统计量及其分布、原假设及拒绝域表 101 z检验原假设、统计量及拒绝域2.t检验：双样本异方差假设工具应用例：对如下数据检验X与Y的均值，假设两总体方差不等，检验
24、两总体均值是否存在显著差异（显著水平0.05）。图 101 数据资料（1）数据分析数据分析t检验：成对双样本平均值，弹出对话框并设置如下：图 102 异方差检验对话框（2）单击“确定”得检验结果报告。由报告可见，双尾截尾概率（P值）为0.850.05不拒绝原假设，即两样本总体均值无显著差异。我们关注的是P值，当该值小于显著水平时，图中的P值值远小于0.05,效应显著。图 103 检验结果报告第11节F检验：双样本方差齐性检验重庆三峡学院 关文忠1.F检验简介F检验又叫方差齐性检验。从两研究总体中随机抽取样本，要对这两个样本进行比较的时候，首先要判断两总体方差是否相同，即方差齐性。若两总体方差相
25、等，则直接用t检验，若不等，可采用秩和检验等方法。其中要判断两总体方差是否相等，就可以用F检验。F检验法是英国统计学家Fisher提出的，主要通过比较两组数据的方差 S2，以确定他们的精密度是否有显著性差异。至于两组数据之间是否存在系统误差，则在进行F检验并确定它们的精密度没有显著性差异之后，再进行t 检验。查F分布临界值表得临界值F，如果F F表明两组数据没有显著差异；F F表明两组数据存在显著差异。若能得到F所对应的截尾概率（P值），则P值小于显著水平时差异显著。F分布函数描述见（图 103）,分布曲线见（图 112）.图 111 F分布基本概念图 112 F分布曲线图11-2蓝色部分为面
26、积为F分布累积概率1-；红色部分的概率则为，横轴为F值。2.F检验：双样本方差工具的使用例：对如下数据，利用EXCEL的F检验工具检验两组数据方差是否有显著差异。X50515442544748583661Y38475048515946494042（1）在EXCEL中输入数据。图 113数据资料（2）从“数据”选项卡选择“数据分析”，选择“F检验：双样本方差”，单击“确定”弹出对话框如下：图 114 F检验对话框（3）单击“确定”得到输出结果（图 115）图 115 F检验结果由图3可见，F统计量1.488,F临界值为3.1789，F0.05，没有落入否定域，不拒绝原假设。第12节单因素方差分析
27、重庆三峡学院 关文忠1.单因素方差分析基本理论（1）单因素方差分析的概念单因素方差分析，是指对单因素试验结果进行分析，检验因素对试验结果有无显著性影响的方法。单因素方差分析是两个样本平均数比较的引伸，它是用来检验多个平均数之间的差异，从而确定因素对试验结果有无显著性影响的一种统计方法。因素：影响研究对象的某一指标、变量。水平：因素变化的各种状态或因素变化所分的等级或组别。单因素试验：考虑的因素只有一个的试验叫单因素试验。例如，将抗生素注入人体会产生抗生素与血浆蛋白质结合的现象，以致减少了药效。下表列出了5种常用的抗生素注入到牛的体内时，抗生素与血浆蛋白质结合的百分比。现需要在显著性水平 = 0
28、.05下检验这些百分比的均值有无显著的差异。设各总体服从正态分布，且方差相同。表 121 试验数据青霉素四环素链霉素红霉素氯霉素29.627.35.821.629.224.332.66.217.432.828.530.811.018.325.032.034.88.319.024.2在这里，试验的指标是抗生素与血浆蛋白质结合的百分比，抗生素为因素，不同的5种抗生素就是这个因素的五个不同的水平。假定除抗生素这一因素外，其余的一切条件都相同。这就是单因素试验。试验的目的是要考察这些抗生素与血浆蛋白质结合的百分比的均值有无显著的差异。即考察抗生素这一因素对这些百分比有无显著影响。这就是一个典型的单因素
29、试验的方差分析问题。（2）单因素方差分析的基本思想（3）检验所需的统计量 （4）假设检验的拒绝域 表 122 方差分析表2.单因素方差分析工具使用如果只有两个样本，则可使用工作表函数 TTEST。如果有两个以上的样本，则没有使用方便的 TTEST 归纳，可改为调用“单因素方差分析”模型。（1）建立一张工作表，输入数据：图 121 输入数据（2）从“数据”选项卡选择“数据分析”，从“数据分析”列表框中选择“方差分析：单因素”，单击“确定”弹出单因素方差分析对话框。图 122 数据分析工具对话框图 123 单因素方差分析对话框图 124 方差分析输出结果第13节无重复双因素方差分析重庆三峡学院 关
30、文忠1.无重复双因素方差分析原理简介（1）无重复双因素方差分析的概念在许多实际问题中，往往要同时考虑两个因素对试验指标的影响. 例如，要同时考虑工人的技术和机器对产品质量是否有显著影响. 这里涉及到工人的技术和机器这样两个因素. 多因素方差分析与单因素方差分析的基本思想是一致的，不同之处就在于各因素不但对试验指标起作用，而且各因素不同水平的搭配也对试验指标起作用. 统计学上把多因素不同水平的搭配对试验指标的影响称为交互作用. 交互作用的效应只有在有重复的试验中才能分析出来.对于双因素试验的方差分析，我们分为无重复和可重复试验两种情况来讨论. 对无重复试验只需要检验两个因素对试验结果有无显著影响
31、；而对等重复试验还要考察两个因素的交互作用对试验结果有无显著影响.设因素A,B作用于试验指标。因素A有r个水平A1,A2,Ar,因素B有s个水平B1,B2,Bs. 对因素A，B的每一个水平的一对组合（Ai,Bj），(i=1,2, ,r,j=1,2, ,s)只进行一次实验，得到rs个试验结果Xij。列于下表中。 表 131 试验数据表因素B因素A B1B2BsA1X11X12X1sA2X21X22X2SArXr1Xr2Xrs（2）无重复双因素方差分析的假设前提（3）偏差平方和及其分解类似于单因素方差分析，需要将总偏差平方和进行分解. 记（4）检验方法表 132 方差无重复试验双因素方差分析表2.
32、无重复双因素方差分析工具使用例：4名工人（B1,B2,B3,B4）操作机器（A1,A2,A3）各一天，其日产量如表，问不同工人和同机器对日产量是否有显著影响（0.05）。工人机器B1B2B3B4A150474753A253545758A352424148Excel操作：（1）在一张Excel表格中输入如下数据图 131 输入实验数据（2）从“数据”选项卡选择“数据分析”，从“数据分析”列表框中选择“方差分析：无重复双因素”。图 132 数据分析对话框（3）单击“确定”弹出“方差分析：无重复双因素分析”对话框。图 133无秤星双因素方差分析对话框（4）设置完对话框后，单击“确定”，得方差分析表。
33、图 134 结果输出当P值大于显著水平时，就将显著，否则不显著。可见，若显著水平为0.05,行（A因素）效应显著，而列效应不显著。第14节可重复双因素方差分析重庆三峡学院 关文忠1.可重复双因素方差分析原理简介（1）可重复双因素方差分析的概念可重复双因素方差分析与无重复双因素方差分析的区别在于考虑交互作用。因素A与因素B每一对组合（Ai,Bj）（i=1,r,j=1,s）要进行t（t2）次实验（也称为等重复双因素试验）。实验结果为Xijk.例：某问题因素A有4个水平，因素B有3个水平，每一组合下做3次试验，试验结果如下：表 141 试验数据表因素A因素BA1A2A3A4B115151717171
34、7151716182020B2191916151515181716151617B3161821192222181818171717试进行等重复双因素方差分析（2）双因素方差分析的假设前提可重复双因素方差分析的假设前提与无重复双因素方差分析相同。（3）偏差平方和及其分解（4）检验方法可得如下方差分析表：表 142 有秤星试验双因素方差分析表2.Excel分析工具的使用承上例，Excel操作如下：（1）打开一张新的EXCEL表，输入如下数据。图 141 试验数据（2）从“数据”选项卡选择“数据分析”，选择“方差分析：可重复双因素分析”调出可重复双因素方差分析图 142 可重复双因素方差分析对话框图
35、中“输入区域”应包括因素名称等全部单元格区域；每一样本的行数为各因素每一水平搭配实验的次数“k”（3）单击“确定”按钮，得到方差分析表。图 143 结果输出这里的样本为B因素效应，列为A样本效应，交互为AB效应，内部为误差。我们关心的是P值（P-value），即截尾概率。当P值F crit时，效应显著，否则不显著。第15节相关系数重庆三峡学院 关文忠1.相关系数的概念著名统计学家卡尔皮尔逊设计了统计指标相关系数(Correlation coefficient)。相关系数是用以反映变量之间相关关系密切程度的统计指标。相关系数是按积差方法计算，同样以两变量与各自平均值的离差为基础，通过两个离差相乘
36、来反映两变量之间相关程度；着重研究线性的单相关系数。依据相关现象之间的不同特征，其统计指标的名称有所不同。如将反映两变量间线性相关关系的统计指标称为相关系数（相关系数的平方称为判定系数）；将反映两变量间曲线相关关系的统计指标称为非线性相关系数、非线性判定系数；将反映多元线性相关关系的统计指标称为复相关系数、复判定系数等。相关系数的计算公式为：复相关系数(multiple correlation coefficient)：反映一个因变量与一组自变量(两个或两个以上)之间相关程度的指标。它是包含所有变量在内的相关系数。它可利用单相关系数和偏相关系数求得。其计算公式为：当只有两个变量时，复相关系数就
37、等于单相关系数。Excel中的相关系数工具是单相关系数。2.相关系数工具的使用CORREL 和 PEARSON 工作表函数均可计算两个测量值变量之间的相关系数，条件是每种变量的测量值都是对 N 个对象进行观测所得到的。（丢失任何对象的任何观测值都会导致在分析中忽略该对象。）相关系数分析工具特别适合于当 N 个对象中的每个对象都有两个以上的测量值变量的情况。它提供一张输出表（相关矩阵），其中显示了应用于每个可能的测量值变量对的 CORREL（或 PEARSON）值。与协方差一样，相关系数是描述两个测量值变量之间的离散程度的指标。与协方差的不同之处在于，相关系数是成比例的，因此它的值与这两个测量值
38、变量的表示单位无关。（例如，如果两个测量值变量为重量和高度，当重量单位从磅换算成千克时，相关系数的值并不改变。）任何相关系数的值都必须介于 -1 和 +1 之间（包括 -1 和 +1）。可以使用相关系数分析工具来检验每对测量值变量，以便确定两个测量值变量是否趋向于同时变动，即，一个变量的较大值是否趋向于与另一个变量的较大值相关联（正相关）；或者一个变量的较小值是否趋向于与另一个变量的较大值相关联（负相关）；或者两个变量的值趋向于互不关联（相关系数近似于零）。【例】9个小麦品种(分别用A1,A2,.,A9表示)的6个性状资料见表，作相关系数计算。表 151 试验数据性状A1A2A3A4A5A6A
39、7A8A9X1(冬季分蘖）11.59.07.59.111.613.011.610.711.1X2（株高）95.397.7110.789.088.087.779.7119.387.7X3（每穗粒数）26.430.839.735.429.324.625.629.932.3X4（千粒重）39.246.839.135.33744.843.738.835.6X5（抽穗期）4/94/174/172/91/54/194/194/192/9X6（成熟期）6/26/66/36/26/76/76/56/56/3（1）建立一张工作表，输入数据：图 151 EXCEL数据输入（2）从“数据”选项卡选择“数据分析”，从
40、“数据分析”列表框中选择“相关系数”，单击“确定”弹出相关系数对话框。图 152 相关系数对话框数据区域可包括变量名称，但不包括样本编号。每个变量的样本按行排列的选择“逐行”按列排列的选择“逐列”。若包括变量名称，则选择“标志位于第一列”（3）单击“确定”得偏相关系数如下表。图 153 结果输出Excel分析工具中的“相关系数”仅计算出相关系数的值，并未进行相关性检验。相关系数检验可由相关系数临界值来判断。相关系数为可决系数的平方根，可决系数为回归平方和与总误差平方和之比，而F统计量为回归均方和与总均方和之比，由于可借助F临界值求得相关系数临界值。即：本例中n=9,在G9单元格输入=SQRT(
41、FINV(0.05,1,7)/(FINV(0.05,1,7)+7)，在B12输入=IF(ABS(B4)$G$9,相关性显著,不显著)并复制，得：图 154 由函数判断显著性第16节协方差重庆三峡学院 关文忠1.协方差的概念在概率论和统计学中，协方差用于衡量两个变量的总体误差。而方差是协方差的一种特殊情况，即当两个变量是相同的情况。两个实数随机变量X与Y之间的协方差定义为：COV(X，Y)=E(X-E(X)(Y-E(Y)其中，E是期望值。2.协方差工具的使用当您对一组个体进行观测而获得了 N 个不同的测量值变量时，“相关”和“协方差”工具可在相同设置下使用。“相关”和“协方差”工具都会提供一张输出表（矩阵），其中分别显示每对测量值变量之间的相关系数或协方差。不同之处在于协方差的取值在 -1 和 +1 之间，而协方差没有限定的取值范围。相关系数和协方差都是描述两个变量离散程度的指标。“协方差”工具为每对测量值变量计算工作表函数 COVAR 的值。（当只有两个测量值变量，即 N=2 时，可直接使用 COVAR，而不要使用“协方差”工具。）在“协方差”工具的输出表中的第 i 行、第 i 列的对角线上的输入值是第 i 个测量值变量与其自身的协方差；这正好是用工作表函数 VARP 计算得出的变量的总体方差。可以使用“协方差”工具来检验每对测量值变量，以便确定两个测量值变量是否趋向于
