统计学原理实习讲义
Excel在统计中的应用
《Excel在统计中的应用》实习讲义
《Excel在统计中的应用》
一、 实验说明
（一）中文Excel 简介
Microsoft Excel 是美国微软公司开发的Windows 环境下的电子表格系 统，它是目前应用最为广泛的办公室表格处理软件之一。自Excel 诞生以来 Excel 历经了Excel5.0、Excel95、Excel97 和Excel2000 等不同版本。随着版本的不断提高，Excel 软件的强大的数据处理功能和操作的简易性逐渐走入了一个新的境界，整个系统的智能化程度也不断提高，它甚至可以在某些方面判断用户的下一步操作，使用户操作大为简化。Excel 具有强有力的数据库管理功能、丰富的宏命令和函数、强有力的决策支持工具、图表绘制功能、宏语言功能、样式功能、对象连接和嵌入功能、连接和合并功能，并且操作简捷，这些特性，已使Excel成为现代办公软件重要的组成部分。
由于大家对Excel的常用办公功能都比较熟悉，本实验重点介绍Excel在统计分析中的应用。
（二）实验目的与要求
本实验重点介绍Excel在统计分析中的应用，包括Excel在描述统计中的应用以及Excel在推断统计中的应用，要求学生熟练掌握运用Excel进行统计分析的方法，并能够对分析结果进行解释。
2
《Excel在统计中的应用》实习讲义
二、实验
实验一 Excel 在描述统计中的应用
实验目的及要求
要求学生掌握运用Excel进行描述统计分析、绘制各种图表和运用数据透视表工具的技术。 实验内容及步骤 （一）描述统计分析
例1-1：表1-1是1978-2005年我国城镇居民可支配收入数据，试求城镇居民可支配收入时间序列的基本统计量。
表1-1 1978-2005年我国城镇居民可支配收入（元）
年份 城镇居民可 年份 城镇居民可 支配收入 支配收入 1978 344 1992 2026.6 1979 405 1993 2577.4 1980 477.6 1994 3496.2 1981 500.4 1995 4283 1982 535.3 1996 4838.9 1983 564.6 1997 5160.3 1984 652.1 1998 5425.1 1985 739.1 1999 5854
1986 899.6 2000 6280 1987 1002.2 2001 6859.6 3
《Excel在统计中的应用》实习讲义
1988 1181.4 2002 7702.8 1989 1379 2003 8472.2 1990 1510.2 2004 9421.6 1991 1700.6 2005 10493
STEP1：用鼠标点击工作表中待分析数据的任一单元格。 STEP2：选择“工具”菜单的“数据分析”子菜单。 STEP3：用鼠标双击数据分析工具中的“描述统计”选项 。 STEP4：出现“描述统计”对话框，如图1-1所示。
图 1-1
对话框内各选项的含义如下：
输入区域：在此输入待分析数据区域的单元格范围。
4
《Excel在统计中的应用》实习讲义
分组方式：如果需要指出输入区域中的数据是按行还是按列排列，则单击“行”或“列”。
标志位于第一行/列：如果输入区域的第一行中包含标志项(变量名)，则选中“标志位于第一行”复选框；如果输入区域的第一列中包含标志项，则选中“标志位于第一列”。
复选框：如果输入区域没有标志项，则不选任何复选框，Excel 将在输出表中生成适宜的数据标志。
均值置信度：若需要输出由样本均值推断总体均值的置信区间，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度。例如，置信度 95%可计算出的总体样本均值置信区间为10，则表示：在5%的显著水平下总体均值的置信区间为( X -10, X +10）。
第 K 个最大/小值：如果需要在输出表的某一行中包含每个区域 的数据的第 k 个最大/小值，则选中此复选框。然后在右侧的编辑框中，输入 k 的数值。
输出区域：在此框中可填写输出结果表左上角单元格地址,用于控 制输出结果的存放位置。
新工作表：单击此选项，可在当前工作簿中插入新工作表，并由新工作表的 A1 单元格开始存放计算结果。如果需要给新工作表命名，则在右侧编辑框中键入名称。
新工作簿：单击此选项，可创建一新工作簿，并在新工作簿的新工作表中存放计算结果。
汇总统计：指定输出表中生成下列统计结果，则选中此复选框。 STEP5：填写完“描述统计”对话框之后，按“确定”按扭即可。结果如图1-2所示。
5
《Excel在统计中的应用》实习讲义
图1-2 描述统计结果
结果说明：描述统计工具可生成以下统计指标，按从上到下的顺序其中包括样本的平均值、标准误差、组中值、众数、 样本标准差、样本方差、 峰度值、偏
度值、极差、最小值、最大值、样本总和、样本个数和一定显著水平下总体均值的置信区间。 （二）绘图操作
例1-2：仍以例1-1的数据为例，绘制城镇居民可支配收入时间序列的散点图。
STEP1：拖动鼠标选定数值区域A2:C12，不包括数据上面的标志项。
6
《Excel在统计中的应用》实习讲义
STEP2：选择“插入”菜单的“图表”子菜单，进入图表向导，图1-3所示。
STEP3：选择“图表类型”为“散点图”，然后单击“下一步”。
图1-3 图表向导图
STEP4：确定用于制作图表的数据区。Excel 将自动把你前面所选定的数据区的地址放入图表数据区的内，单击下一步按钮，出现图1-4所示对话框。
7
《Excel在统计中的应用》实习讲义
图1-4 作图过程图
STEP5：填写图表标题、X 轴坐标名称、Y 轴坐标名称，此处不用填写，单击“下一步”。
STEP6： 选择图表输出的位置，然后单击“完成”按扭即生成 （三）数据透视表工具
数据透视表是Excel 中强有力的数据列表分析工具。它不仅可以用来作单变量数据的次数分布或总和分析，还可以用来作双变量数据的交叉频数分析、总和分析和其它统计量的分析。
例1-3：表1-2列出了学生两门功课评定结果，建立学生两门功课评定结果的交叉频数表。 表1-2 学生两门功课评定结果
8
《Excel在统计中的应用》实习讲义
学号 语文 数学 学号 语文 数学 1001 优 差 1006 中 良 1002 良 中 1007 中 优 1003 中 中 1008 差 良 1004 差 中 1009 良 中 1005
操作步骤：
STEP1： 输入数据，选中有数据的任一单元格，然后选择“数据”菜单的“数据透视表”子菜单，进入数据透视表向导。
STEP2： 选择“Microsoft Excel 数据清单或数据库”为数据源。图1-6所示，单击“下一步”。
差 差 图1-6
9
《Excel在统计中的应用》实习讲义
STEP3： 选择待分析的数据的区域，一般情况下Excel 会自动根据当前单元格确定待分析数据区域，因此你只要直接单击“下一步”按扭即可。 STEP4： 确定数据透视表的结构，在此例中，要建立的是一个交叉频数表，分别按语文和数学的成绩对学生的人数进行交叉频数分析，因此可将三个按扭“学号”、“语文”、“数学”分别拖放到表格的指定部位，并且双击“求和项：学号”，将其改为记数项，结果如图1-7所示。
图1-7 布局对话框
STEP5：选择数据透视表的显示位置之后，单击“完成按扭”，可出现如图1-8所示的数据透视表。
10
《Excel在统计中的应用》实习讲义
图1-8
结果说明：如图1-8的结果所示，数据透视表可以作为一个交叉频数分析工具。 完成数据透视表之后，可按需要修改数据表的显示格式。例如，如果想要把表格中的频数替换成为百分比数。可以用鼠标右击频数的任一单元格，选择“字段”子菜单，单击“选项”按扭，将“数据显示方式”替换成为“占总和的百分比”，然后单击“确定”按扭即可。按同样方式，可将数据透视表修改成为其它不同样式。
11
《Excel在统计中的应用》实习讲义
实验二 Excel中的二项分布工具
实验目的及要求
掌握利用Excel的BINOMDIST 的函数计算二项分布的概率以及累积概率。
实验内容及步骤
例1-4：一个推销员打了六个电话，推销成功的概率是0.3，建立推销成功次数的概率分布图表。可以按以下步骤建立推销成功次数的概率分布图表。
STEP1： 如图1-9所示，先在Excel之下建立好概率分布表格的框架。
图 1-9
STEP2：如图1-10所示,先在B7 至F7 单元格分别输入概率计算公式。
12
《Excel在统计中的应用》实习讲义
图1-10
STEP3：公式的拷贝。选取B7 至F7 单元格，拖动“填充柄”至F13 单元格
即可完成公式的拷贝操作。结果图1-11所示。
图1-11
STEP4：下面开始创建二项分布图表。选取B7 至B13 单元格，选取“插入”菜单的“图表”子菜单。
STEP5：选择“柱状图”，然后单击“下一步”。
STEP6：单击“系列”标签，单击“分类(X)轴标志”框，并用鼠标选取A7至A13 单元格为图表X 轴的轴标，然后单击“下一步”。
13
《Excel在统计中的应用》实习讲义
STEP7：分别键入图表名称“二项分布图”，X 轴名称“成功次数”，Y 轴名称“成功概率”，单击“完成”按扭即可生成二项分布图表。
结果说明: 如图1-11 所示，利用Excel 的BINOMDIST 的函数可以计算出二项分布的概率以及累积概率。BINOMDIST 函数可以带四个参数，各参数的含义分别是：实验成功的次数，实验的总次数，每次实验中成功的概率，是否计算累积概率。
四个参数是一个逻辑值，如果为TRUE，函数 BINOMDIST 返回累积分布函数，如果为FALSE，返回概率密度函数。
另外，EXCEL还提供了其它分布的函数，如函数CRITBINOM；函数HYPGEOMDIST；
函数NEGBINOMDIST：函数POISSON: 正态分布函数NORMDIST：函数NORMSDIST：函数NORMSINV：t 分布函数TDIST: 有兴趣的同学可以自己研究。
14
《Excel在统计中的应用》实习讲义
实验三 随机抽样工具
实验目的及要求
掌握利用Excel的BINOMDIST 的函数计算二项分布的概率以及累积概率。
实验内容及步骤
Excel 中的Rand()函数可以返回大于等于 0 小于 1 的均匀分布随机数，Rand()不带任何参数运行，每次计算时时都将返回一个新的数值。RAND()函数可以被用来作为不重复抽样调查的工具。
例1-5：如图1-12所示有10 个象征性的样本数据，欲从中随机抽取5 个数据可按如下步骤操作：
图1-12
STEP1：选择B2 单元格，输入公式“=RAND()”并回车 。
STEP2：拖动B2 单元格右下角的填充柄至B11 单元格，并在B1 单元格输入标题“RANDOM”。
STEP3：选取单元格B2 至B11，右击选中的区域选择“复制”，再次右
15
《Excel在统计中的应用》实习讲义
击选中的区域，选择“选择性粘贴”，单击选项“数值” 后，点击“确定”按扭。
STEP4：选取单元格A2 至B11 单元格，选择“数据”菜单项下的排序子菜单。
STEP5：选取“RANDOM”为主要关键字 ，然后点击“确定”按扭。排序结果如图1-13 所示，A2 至A6 单元格的样本即为随机抽取的5 个样本。
图1-13
16
《Excel在统计中的应用》实习讲义
实验四 由样本推断总体
实验目的及要求
掌握利用Excel的几个函数，如求平均函数AVERAGE、标准差函数STDEV、T 分布函数TINV 等的组合使用构造一个专门用于实现样本推断总体的Excel工作表。 实验内容及步骤
下面的例子先计算样本的平均数和标准差，然后在一定置信水平上估计总体均值的区间范围。操作步骤：
STEP1：构造工作表。如图1-14所示，首先在各个单元格输入以下的内容，其中左边是变量名，右边是相应的计算公式。
STEP2：为表格右边的公式计算结果定义左边的变量名。选定A4:B6,A8:B8和A10:B15 单元格(先选择第一部分，再按住CTRL 键选取另外两个部分)，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“最左列”选项,然后点击“确定”按扭即可。
图1-14
17
《Excel在统计中的应用》实习讲义
STEP3：输入样本数据，和用户指定的置信水平0.95,如图附-13 所示。
STEP4：为样本数据命名。选定D1:D11 单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定”按扭，得到图1-15所示的计算结果。
图1-15
结果说明：以上例子说明如何交叉组合使用Excel 的公式和函数，以构造出一个能实现样本推断总体有关计算的Excel 工作表。实际上，在用Excel 进行数据统计处理之时，许多统计功能可以使用和上例类似的方法，通过组合使用Excel 的各类统计函数和公式加以实现的。
18
《Excel在统计中的应用》实习讲义
实验五 假设检验
实验目的及要求
掌握利用Excel 的正态分布函数NORMSDIST、判断函数IF 等，构造一张能够实现在总体方差已知情况下进行总体均值假设检验的Excel工作表。
实验内容及步骤
例1-6：利用Excel 的正态分布函数NORMSDIST、判断函数IF 等，构造一张能够实现在总体方差已知情况下进行总体均值假设检验的Excel 工作表。 操作步骤：
STEP1：构造工作表。如图1-16 所示，首先在各个单元格输入以下的内容，其中左边是变量名，右边是相应的计算公式。
STEP2：为表格右边的公式计算结果定义左边的变量名。选定A3:B4,A6:B8，A10:A11,A13:A15 和A17:B19 单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“最左列”选项,然后点击“确定”按扭即可。
19
《Excel在统计中的应用》实习讲义
图1-16
STEP3：输入样本数据，以及总体标准差、总体均值假设、置信水平数据。如 图1-17所示。
STEP4：为样本数据命名。选定C1:C11 单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定”按扭，得到如图1-17中所示的计算结果。
图1-17
20
《Excel在统计中的应用》实习讲义
结果说明：如图1-17所示，该例子的检验结果不论是单侧还是双侧均为拒绝Ho 假设。所以，根据样本的计算结果，在5%的显著水平之下,拒绝总体均值为35 的假设。同时由单侧显著水平的计算结果还可以看出，在总体均值是35 的假设之下，样本均值小于等于31.4 的概率仅为0.020303562。
21
《Excel在统计中的应用》实习讲义
实验六 双样本等均值假设检验
实验目的及要求
掌握利用Excel 数据分析中提供双样本等均值假设检验工具进行假设检验的方法，并能够解释实验结果。 实验内容及步骤
例1-7：双样本等均值检验是在一定置信水平之下,在两个总体方差相等的假设之下，检验两个总体均值的差值等于指定平均差的假设是否成立的检验。假设某工厂为了比较两种装配方法的效率，分别组织了两组员工，每组9 人，一组采用新的装配方法，另外一组采用旧的装配方法。18个员工的设备装配时间图1-18 中表格所示。根据以下数据，是否有理由认为新的装配方法更节约时间？
图1-18
操作步骤：
STEP1：选择“工具”菜单的“数据分析”子菜单，双击“t-检验: 双样本等方差假设”选项，则弹出图1-19 所示对话框。
22
《Excel在统计中的应用》实习讲义
图1-19
STEP2： 分别填写变量1 的区域：$B$1:$B$10，变量2 的区域：$D$1:$D$10，由于我们进行的是等均值的检验，填写假设平均差为0，由于数据的首行包括标志项选择标志选项，所以选择“标志”选项，再填写显著水平α为0.05,然后点击“确定”按扭。则可以得到图1-20所示的结果。
23
《Excel在统计中的应用》实习讲义
图1-20
结果分析：如图1-20中所示，表中分别给出了两组装配时间的平均值、方差和样本个数。其中，合并方差是样本方差加权之后的平均值,Df 是假设检验的自由度它等于样本总个数减2，t 统计量是两个样本差值减去假设平均差之后再除于标准误差的结果，“P(T<=t)单尾”是单尾检验的显著水平，“t 单尾临界”是单尾检验t 的临界值，“P(T<=t)双尾”是双尾检验的显著水平，“t 双尾临界”是双尾检验t 的临界值。由下表的结果可以看出t 统计量均小于两个临界值，所以，在5%显著水平下，不能拒绝两个总体均值相等的假设，即两种装配方法所耗时间没有显著的不同。
Excel 中还提供了以下类似的假设检验的数据分析工具，它们的名称和作用如下：
“t-检验：双样本异方差假设” “t-检验：成对双样本均值分析” “z-检验：双样本均值分析”
24
《Excel在统计中的应用》实习讲义
实验七 正态性的卡方检验
实验目的及要求
掌握利用Excel进行总体卡方分布检验、总体泊松分布、总体超几何分布等其它分布的检验的方法步骤，并能够解释实验结果。 实验内容及步骤
卡方检验检验可以用来判断所观测的样本是否来自某一特定分布的总体，这种检验亦称为一致性检验。
例1-8：已知某样本的相关统计量和分组频数分布如图1-21所示，试用卡方检验判断该样本是否来自一正态总体。
图1-21
操作步骤：
STEP1：创建变量名。选定A3:C4 单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定”按扭即可。
STEP2： 计算预期正态概率值。如图1-22表中所示，在D6 单元格输入标志项，在D7:D11 单元格输入公式，分别计算各组的预期正态概率值，
25
《Excel在统计中的应用》实习讲义
在D12 计算累积概率值。
图1-22
STEP3： 计算预期频数值。如图1-23 所示，在E6 单元格输入标志项，在E7:E11
单元格输入公式，分别计算各组的预期频数，在E12 计算累积频数值。
图1-23
STEP4： 计算X2 统计量。如图1-24 所示，在F6 单元格输入标志项，在F7:F11分别输入计算公式，分别计算X2 值，在E12 计算X2 平方和,这项就是最后计算出的X2 统计量。在E13 单元格输入标志项“卡方统计量”，为以后的引用作准备。先选中F12、F13 两个单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“尾行”选项,然后点击“确定”按扭即可。
26
《Excel在统计中的应用》实习讲义
图1-24
STEP5： 如图1-25所示，分别在A14 到B20 单元格输入自由度、X2 概率值、置信水平、临界值、X2 检验结果几项的标志值及计算公式。其中的自由度=区间分段数-正态分布参数个数-1=5-2-1=2。
图1-25
STEP6：结果如图1-26所示。
27
《Excel在统计中的应用》实习讲义
图 1-26
结果分析：如图1-26所示，按照以上操作步骤可以得到表中的计算结果。按同样的方法可以作总体泊松分布、总体超几何分布等其它分布的检验。此类统计应用也是由Excel 各类公式和函数综合使用而实现的,为了以后使用方便，和上面的一些例子一样，一般需要将整个表格的计算框架和标志项罗列好，再保存成文件，以后只要对数据项稍作修改即可很快得到计算结果。如果对Excel 宏语言较为熟悉，还可以将它编成一个宏语言程序，加入 Excel 的工具栏，这样以后使用起来更为方便。
28
《Excel在统计中的应用》实习讲义
实验八 列联表分析
实验目的及要求
列联表分析经常用来判断同一个调查的对象的两个特性之间是否存在明显相关关系。该实验要求学生掌握利用Excel实现列联表分析的方法步骤，并能够解释实验结果。 实验内容及步骤
例1-9：如图1-27所示，表中是某装修公司的调查报告，试用列联表分析方法分析在顾客的所在地区和所选房子的地板类型之间是否存在明显的相关关系。
图1-27
操作步骤：
STEP1：建立期望频数表。如图1-28所示，先建立期望频数表的框架，然后在B10 单元格输入公式“=B$7*$F4/$F$7”，再利用“填充柄”将公式复制到表格的其它单元格，最后利用Excel 的求和函数sum 计算行和与列和。
图1-28
29
《Excel在统计中的应用》实习讲义
STEP2：计算X2 概率值。在A15 单元格输入标志项“卡方概率值”，先点击B15单元格，从“插入”菜单中“函数”子菜单，选择“统计函数”中的“CHITEST”函数，单击“确定按扭，然后在弹出的对话框中分别添入实际频数范围“B4:E6”和预期频数范围“B10:E12”。最后单击“确定”按扭即可得到计算结果1.3E-07,如图1-29 所示。
图1-29
STEP3： 建立X2 统计表。如图1-30 所示，先建立表格的框架，然后在B20单元格输入公式“=(B4-B10)^2/B10”，再利用填充柄将公式复制到表格的其它单元格。最后计算X2 卡方统计量，分别在A24 与B24 单元输入标志项与计算公式。
图1-30
30
《Excel在统计中的应用》实习讲义
STEP4： 进行假设检验。如图1-31所示，分别输入置信水平、临界值和假设检验的结果其中CHIINV 函数的自由度=(第一类属性的分类数-1)*( 第二类属性的分类数-1)=(3-1)*(4-1)=6。
图1-31
结果分析：以上的操作步骤完成整个列联表的分析。其中，B15单元格的卡方概率值与B24 单元格的卡方统计量是表格的两个重要计算结果。其中卡方概率值等于1.3E-07 表明：如果总体的两类属性，即所在地区和所选地板类型，是不相关的，那么得到以上观察的样本的概率是0.00000013。这个概率几乎接近于0，所以可以认为总体的这两个属性是显著相关的。
31
《Excel在统计中的应用》实习讲义
实验九 单因素方差分析
实验目的及要求
掌握利用Excel进行单因素方差分析、无重复双因素方差分析、有重复双因素方差分析的步骤和方法，并能够解释实验结果。 实验内容及步骤
单因素方差分析可用于检验两个或两个以上的总体均值相等的假设是否成立。此方法是对双均值检验（如 t-检验）的扩充。检验假定总体是服从正太分布的，总体方差是相等的，并且随机样本是独立的。这种工具适用于完全随机化试验的结果分析。例1-10：如图1-32中所示，一产品制造商雇佣销售人员向销售商打电话。制造商想比较四种不同电话频率计划的效率，他从销售人员中随机选出32 名，将他们随机分配到4种计划中，在一段时期内记录他们的销售情况已经在表中列出，试问其中是否有一种计划会带来较高的销售水平。
图1-32
操作步骤：
STEP1：选择“工具”菜单的“数据分析”子菜单，双击“方差分析: 单因
32
《Excel在统计中的应用》实习讲义
素方差分析”选项，弹出单因素方差分析对话框。 STEP2：按图1-33所示方式填写对话框。
图1-33
STEP3：单击“确定”按扭，得到图1-34的计算结果。
图1-34
33
《Excel在统计中的应用》实习讲义
结果分析：
图1-34中表格的第二部分则是方差分析的结果。SS列分别给出了四个分组的组间方差、组内方差以及总方差，DF 列分别给出了对应方差的自由度，MS列是平均值方差，由SS 除于DF 得到，它是总体方差的两个估计值。F列是F 统计量的计算结果，如果四个总体均值相等的假设成立的化，它应该服从F 分布，即近似为1，它是最终的计算结果，通过将它与一定置信水平下的F 临界值F crit 比较，可以判断均值相等的假设是否成立，在本例中，1.67761小于第二步0.94668 ,所以不能拒绝四个总体均值相等的假设。P-value 列，是单尾概率值，表明如果四个总体均值相等的假设成立的化，得到如上样本结果的概率是19.442% ,即得到以上样本并不是小概率事件，同样也得到不能拒绝四个总体均值相等的假设的结论。按相似方法可进行无重复双因素方差分析，有重复双因素方差分析。
34
《Excel在统计中的应用》实习讲义
实验十 线性回归分析
实验目的及要求
掌握利用Excel计算相关系数、进行线性回归分析的步骤和方法，并能够解释实验结果。 实验内容及步骤
（一）线性回归分析
线性回归分析通过对一组观察值使用“最小二乘法”直线拟合，用来分析单个因变量是如何受一个或几个自变量影响的。
例1-11：如图1-35所示，我国1987 至1997年布匹的人均产量和人均纱产量，试用线性回归分析的方法分析两组数据之间的关系，并计算二者之间的相关系数。
图1-35 操作步骤：
STEP1：选择“工具”菜单的“数据分析”子菜单，双击“回归”选项，弹出回归分析对话框。
35
《Excel在统计中的应用》实习讲义
STEP2：按如下方式填写对话框：X 值输入区域为$B$1:$B$12, Y 值输入区域为$C$1:$c$12, 并选择“标志”和“线性拟合图”两个复选框 STEP3：单击“确定”按扭即可，结果如图1-36所示。
图1-36
结果分析：结果可以分为四个部分，第一部分是回归统计的结果包括多元相关系数、可决系数R2、调整之后的相关系数、回归标准差以及样本个数。第二部分是方差分析的结果包括可解释的离差、残差、总离差和它们的自由度以及由此计算出的F 统计量和相应的显著水平。第三部分是回归方程的截距和斜率的估计值以及它们的估计标准误差、t 统计量大小双边拖尾概率值、以及估计值的上下界。根据这部分的结果可知回归方程为Y=8.46433*X-18.288。第四部分是样本散点图，其中蓝色的点是样本的真实散点图，红色的点是根据回归方程进行样本历史模拟的散点。如果觉得散点图不够清晰可以用鼠标拖动图形的边界达到控制图形大小的目的。用相同的方法可以进行多元线性方程的参数估计，还可以
36
《Excel在统计中的应用》实习讲义
在自变量中引入虚拟变量以增加方程的拟合程度。对于非线性的方程的参数估计，可以在进行样本数据的线性化处理之后，再按以上步骤进行参数估计。 （二）相关系数计算 实验内容及步骤
STEP1：用鼠标点击表中待分析数据的任一单元格。 STEP2：选择“工具”菜单的“数据分析”子菜单。 STEP2：用鼠标双击数据分析工具中的“相关系数”选项 。 STEP4：填写完“相关系数”对话框，单击“确定”按扭即可得到各个变量的相关系数矩阵，结果如图1-37 所示。
图1-37
结果说明：图1-37下三角矩阵计算出三个变量x,y,z 两两之间的相关系数，如变量x,y 之间的相关系数为：0.929167,所以可以判断x,y 之间存在着较高的正线性相关关系。
37
《Excel在统计中的应用》实习讲义
实验十二 自回归模型的识别与估计
实验目的及要求
掌握利用Excel对时间序列的自回归模型进行识别与估计的步骤和方法，并能够解释实验结果。 实验内容及步骤
例1-12：如图1-38所示是自1999年4月1日起的20个交易日内的上证指数的时间序列，试用自回归模型加以拟合。
图 1-38
操作步骤：
STEP1：数据的零均值化处理。如图1-39中所示，在C1 中输入序列名“Z”，在C2 中输入公式“=上证指数-AVERAGE(上证指数)”，然后在C2 单元格中，拖动Excel“填充柄”将公式复制到C3 至C22 单元格，即可生成
38
《Excel在统计中的应用》实习讲义
上证指数的零均值化序列。
STEP2：计算自相关函数。在E1 和F1 单元格分别输入标志项Lag 和ac ,在E2 到E9 单元格中分别输入置后期数1 至8。在F2 单元格输入计算自相关函数的公式“ =SUMPRODUCT(OFFSET(C$2,0,0,20-E2),OFFSET(C3, 0,0,20-E2))/VAR($C$2:$C$21)/19”，然后利用“填充柄”将F2 单元格公式复制到F3:F9 单元格，结果如图1-40所示。
图 1-39
STEP3：计算偏自相关函数。计算偏自相关函数的步骤较为复杂，必须利用Excel 的逆矩阵等函数求解Yule-Walker 方程组，由于我们选择了置后期数为8，为了求解偏自相关函数，我们必须求解8 个Yule-Walker方程组。首先，利用自相关函数的计算结果，填写H2:O9 范围内的对称矩阵如图1-40中H2:O9 单元格所示。其次，利用Excel 数组公式分别求解8 个方程组的结果，结果分别放在φ1i 至φ8i 的八列之中，第一个方程组的结果放在H12中，第二个方程组的结果放在I12:I13 中, 第三个方程组的结果放在J12:J14 中 ,以此类推。所输入的8个数组公式分别为：
“MMULT(MINVERSE(OFFSET(H2,0,0,1,1)),OFFSET(F2,0,0,1))”， “MMULT(MINVERSE(OFFSET(H2,0,0,2,2)),OFFSET(F2,0,0,2))”， “MMULT(MINVERSE(OFFSET(H2,0,0,3,3)),OFFSET(F2,0,0,3))”，
39
《Excel在统计中的应用》实习讲义
“MMULT(MINVERSE(OFFSET(H2,0,0,4,4)),OFFSET(F2,0,0,4))”， “MMULT(MINVERSE(OFFSET(H2,0,0,5,5)),OFFSET(F2,0,0,5))”， “MMULT(MINVERSE(OFFSET(H2,0,0,6,6)),OFFSET(F2,0,0,6))”， “MMULT(MINVERSE(OFFSET(H2,0,0,7,7)),OFFSET(F2,0,0,7))”， “MMULT(MINVERSE(OFFSET(H2,0,0,8,8)),OFFSET(F2,0,0,8))”。 （说明1.在Excel 中输入数组公式时，先用鼠标选定所有需放置结果的单元格地址范围然后输入数组公式，例如“=MMULT(MINVERSE(OFFSET(H2,0,0,2,2)), OFFSET(F2,0,0,2))”，然后同时按下“CTRL+SHIFT+回车”三个按键，完成数组公式的输入，公式会自动加上一对大括号，它由Excel 自动添入。
STEP2：以上数组公式中包含的各个函数的含义及其用法请参看附表1。）最后，将每一个方程组的最后一个解，用值复制的方式复制到pac这一列，即可得到8 个偏自相关系数。如图附-39，表中H12:O19 单元格的８列分别给出了８个数组公式计算的结果，F12:F19 单元格的内容即是所要求解的8 个偏自相关系数。
40
《Excel在统计中的应用》实习讲义
图 1-40
STEP4：模型的识别与估计。自相关函数序列呈明显拖尾性，偏自相关函数序列在k>1 之后，都在区间(-0.438,0.438)之间，因此可以认为自相关函数在K>1 之后截尾，因此我们选用AR(1)模型进行数据拟合。复制C2:C20 的数据，将之以值复制的形式复制到D3:D21 的单元格，并在D1 中填入标志项“Z(-1)”。选择“工具”菜单的“数据分析”子菜单，双击“回归”选项，弹出回归分析对话框。按图附-40 所示的方式填写对话框。然后单击“确定”按扭，即可得到AR(1)模型的估计结果。 STEP5：按以上操作步骤，可得到图1-41 所示AR(1)模型。
41
《Excel在统计中的应用》实习讲义
图 1-41
结果分析：零均值化模型的估计结果是Z=1.06284*Z(-1),还原成上证指数，最终的时间序列模型是：上证指数估计值-上证指数的平均值=1.06284(上一天上证指数-上证指数平均值)。
42
因篇幅问题不能全部显示，请点此查看更多更全内容