统计学实验指导书（Excel）

1、统计学实验指导书【试验目的】通过实验教学，使学生验证并加深理解和巩固课堂教学内容，掌握常用统计分析方法在Excel、SPSS的应用，理解和掌握统计分析方法的应用原理、基本条件、实现步骤、结果的内涵等问题。通过实验，使学生能够结合具体任务和条件对社会经济问题进行初步的调查研究，结合自己的专业，在定性分析基础上做好定量分析，提高学生的科研能力和解决实际问题的能力，以适应社会主义市场经济中各类问题的实证研究、科学决策和经济管理的需要。【试验内容】一、Excel中的统计分析功能包括：1、数据的整理与显示，包括数据的排序与筛选、数据透视表与分类汇总、制作频数分布表和绘制各种统计图。2、计算描述统计量，选

2、择适合的描述统计量反映统计数据的集中和离中趋势。3、利用Excel进行推断统计。4、利用Excel进行相关回归分析。试验一：数据的整理与显示（一）数据的筛选和排序1、数据的筛选在对统计数据进行整理时，首先需要进行审核，以保证数据的质量。对审核中发现的错误应尽可能予以纠正。如果对发现的错误无法纠正，或者有些数据不符合调查的要求而又无法弥补时，就要对数据进行筛选。数据筛选有两方面内容：一是将某些不符合要求的数据或有明显错误的数据予以剔除；二是将符合某种特定条件的数据筛选出来，不符合特定条件的数据予以剔除。数据筛选可借助于计算机自动完成。下面通过一个实例说明用Excel进行数据筛选的过程。【例】下表

3、是8名学生4门课程的考试成绩数据（单位：分）。试找出统计学成绩等于75分的学生，英语成绩最高的前三名学生，4门课程都高于70分的学生。 8名学生的考试成绩数据首先，选择“工具”菜单，并选择“筛选”命令。如果要筛选出满足给定条件的数据，可使用自动筛选命令。如图所示。 自动筛选(1)这时会在第一行出现下拉箭头，用鼠标点击箭头会出现下面的结果，如图 自动筛选(2)要筛选出统计学成绩为75分的学生，选择75，得到结果：自动筛选(3)要筛选出英语成绩最高的前三名学生，可选择“前10个”，并在对话框中输入数据3，得到的结果如图所示。 自动筛选(4)如果要筛选出4门课程成绩都大于70分的学生，由于设定的条件

4、比较多，需要使用“高级筛选”命令。使用“高级筛选”时，必须建立条件区域。这时需要在数据清单上面至少留出3行作为条件区域，然后把数据清单中含有筛选值的数据列复制粘贴到条件区域的一个空行，再在条件标志下面的一行中键入要匹配的条件。如图所示： 自动筛选(5)然后，在高级筛选对话框中修改“数据区域”或“条件区域”。结果如图所示。 高级筛选(1)单击“确定”后出现如图的结果. 高级筛选(2)2、数据的排序数据排序就是按一定顺序将数据排列，其目的是为了便于研究者通过浏览数据发现一些明显的特征或趋势，找到解决问题的线索。排序还有助于对数据检查、纠错，为重新分组或归类提供依据。在某些场合，排序本身就是分析的目

5、的之一。名义级数据，如果是字母型数据，排序有升序与降序之分，升序更常见些，因为升序跟字母的自然排列相同；如果是汉字型数据，排列方式很多，比如按汉字的首位拼音字母排列，这与字母型数据的排序完全一样，也可按笔画顺序，其中也有笔画多少的升序与降序之分。交替运用不同方式排序，在汉字型数据的检查、纠错中十分有用。区间数据和比数据的排序只有两种，即递增和递减。排序后的数据亦称为顺序统计量（Order statistics）。无论是定性数据还是定量数据，其排序均可借助计算机完成。【例】有一张8名学生的学习成绩所构成的数据清单（单位：分），如下表所示。试按总成绩的递增顺序按列排序。表3.1.2 学习成绩统计表

6、所谓按列排序就是根据一列或几列中的数据清单进行排序。排序时，Excel将按指定字段的值和指定的“升序”、“降序”排序次序重新设定行。操作步骤如下：（1）单击数据区域内的任何一个单元格。（2）选取“数据”菜单中的“排序”命令，出现“排序”对话框，如图所示。 排序(1)（3）在对话框中，单机“主要关键字”右边的下拉列表按钮，在字段下拉列表中选取主关键字段，如“总成绩”，如图所示。排序(2)（4）制定“升序”还是“降序”，单击主要关键字右边的“递增”单选钮。（5）还可以用同样的方法选择“次要关键字”、“第三关键字”以及升降序。（6）排除字段名行。因为字段名行不参加排序，所以数据清单中如果含有字段名行

7、，应单击“有标题行”单选钮将其排除，否则单击“没有标题行”单选钮。（7）单击“确定”按钮执行排序。各记录的行序被重新组织，记录1为总成绩最低的学生。如图所示。 按“总成绩”排序的成绩单当对数据清单按列进行排序且只有一个排序关键字时，可以直接使用标准工具栏中的“升序”或“降序”工具按钮来完成排序，如图所示。操作步骤：（1）单击排序字段中的任意一个单元格。（2）单击工具栏中的“升序”或“降序”工具按钮。“升序”或“降序”工具按钮通常情况下，Excel是按列排序的，但也可以按行排序。所谓按行排序就是根据一行或几行中的数据清单进行排序。排序时，Excel将按指定行的值和指定的“升序”或“降序”排序次序

8、重新设定列。操作步骤如下：（1）单击数据区域内的任何一个单元格。（2）选取“数据”菜单中的“排序”命令，出现“排序”对话框。（3）单击对话框中的“选项”按钮，出现“排序选项”对话框，如图所示。 按行排序(4)在“排序选项”对话框中的“方向”框中，选取“按行排序”单选钮。(5)单击“确定”按钮。以下步骤和按列排序的步骤相同。（二）利用直方图制作频数分布表在给定工作表中数据单元格区域和接收区间的情况下，计算数据的个别和累积频率，用于统计有限集中某个数值元素的出现次数。例如，在一个有 20 名学生的班级里，可以确定以字母打分（如 A、B-等）所得分数的分布情况。直方图表会给出字母得分的边界，以及在最

9、低边界与当前边界之间某一得分出现的次数。出现频率最多的某个得分即为数据组中的众数。“直方图”对话框接收区域（可选）在此输入接收区域的单元格引用，该区域应包含一组可选的用来定义接收区间的边界值。这些值应当按升序排列。只要存在的话，Microsoft Excel 将统计在当前边界点和相邻的高值边界点之间的数据点个数。如果某个数值等于或小于某个边界值，则该值将被归到以该边界值为上限的区间中。所有小于第一个边界值的数值将一同计数，同样所有大于最后一个边界值的数值也将一同记数。如果省略此处的接收区域，Microsoft Excel 将在数据组的最小值和最大值之间创建一组平滑分布的接收区间。柏拉图选中此复

10、选框，可以在输出表中同时按降序排列频率数据。如果此复选框被清除，Microsoft Excel 将只按升序来排列数据，即省略输出表中最右边的三列数据。累积百分率选中此复选框，可以在输出表中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略累积百分比。图表输出选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。实例应用：学生历次考试成绩统计，按照一定区间生成频数分布表，使用“直方图”分析工具来完成。（数据文件名：直方图.xls）操作步骤：1、 打开数据“直方图.xls”，如图2、 选择“数据分析”对话框中“直方图”，跳出“直方图”对话框。3、 在“输入区域

11、”对应编辑框输入学生成绩数据的引用。（$B$2:$B$15）4、 在“接受区域”对应编辑框输入数据划分单元格的引用。（$A$18:$A$22）5、 看是否在输入栏里是否选择了标志单元格，考虑选定“标志”。在输出选项中选择“新工作表组”，在其对应编辑框中输入输出工作表名称，如；“图表输出”。选择“帕拉图”、“累积百分率”、“图表输出”选项。6、 单击确定。结果输出如下在图中显示的统计结果中，可以看见输出的内容分为两部分，一部分是数据表示形式，一部分是直方图形式。在数据表部分，显示每个区间中的人数及累计百分率数值。通过该统计结果，我们可以知道，在第一次考试中有6人在10085分之间，5人在8575

12、之间，1人在7565之间，1人在65以下。应当注意，上图实际上是一个条形图，而不是直方图，若要把它变成直方图，可按如下操作：用鼠标左键单击任一直条，然后右键单击，在弹出的快捷菜单中选取数据系列格式，弹出数据系列格式对话框，如图1-8所示：图1-8数据系列格式对话框在对话框中选择选项标签，把间距宽度改为0，按确定后即可得到直方图，如图1-9所示：图1-9调整后的直方图（三）数据透视表假设我们已经建立了一张统计表，若要建立一个交叉式的复合分组统计表，可使用数据透视表功能。“数据透视表”对话框打开数据透视表和数据透视图向导后，选择待分析数据的数据源及所需创建的报表类型，然后单击下一步选择数据区域，再

13、单击下一步选择数据透视表的保存位置，最后单击完成。实例应用： 如果我们已经建立了一张某单位部分人事统计表，利用数据透视表按性别统计各职称的基本工资总额（数据文件名：数据透视表.xls）操作步骤：1、 打开数据“数据透视表.xls”，如图2、单击“数据”菜单选择“数据透视表和数据透视图”命令进入数据透视表和数据透视图向导，创建的报表类型选择数据透视表。3、单击下一步，键入或选定建立数据透视表的数据源区域，4、单击下一步，在数据透视表显示位置中选择新建工作表5、单击完成，则在新建工作表生成数据透视表7、 将“职称”拖至行字段处；将“性别”拖至列字段处；将“基本工资”拖至数据项处。即得到数据透视表-

14、按性别统计各职称的基本工资总额为内容的交叉式的复合分组统计表。（四）统计图统计图是统计资料另一种常用的表达方式。它是利用几何图形（点、线、面、形）或其它图形把所研究对象的特征、内部结构等相互关联的数量关系绘制成简明的图形。它由坐标系、图形、图例组成。统计图表示的数量关系形象、直观、明白，可以使人们一目了然地认识客观事物的状态、形成、发展趋势或在某地区上的分布状况等，故它在统计工作中使用得也非常广泛。图形的制作均可由计算机来完成。统计图有两种类型：一是单式图，一个图只用来显示一种现象的数量特征；二是复合图和叠加图，用一个图同时显示几种数量的分布或变化情况。下面介绍几种常用的统计图。1、条形图（B

15、ar）。条形图可用于显示离散型变量的次数分布。最主要是显示顺序数据和分类数据的频数分布。条形图是用宽度相同的条形的高度或长短来表示数据的多少的图形。条形图可以横置或纵置，纵置时也称为柱形图。此外，条形图有单式、复式等形式。在表示分类数据的分布时，用条形图的高度或长度来表示各类别数据的频数或频率。绘制时，各类别可以放在纵轴，称为条形图；也可以放在横轴，称为柱形图。 条形图（Bar）用于显示离散型变量的次数分布，用条形的高度来表示变量值的大小，如图所示。 类别数据条形图离散型变量次数分布条形图2、 直方图（ Histogram ）和折线图。用于显示连续型变量的次数分布。直方图是用矩形的宽度和高度（

16、即面积）来表示频数分布的图形。在平面直角坐标中，用横轴表示数据分组，纵轴表示频数或频率，这样，各组与相应的频数就形成了一个矩形，即直方图。在直方图中，实际上是用矩形的面积来表示各组的频数分布。在直方图基础上添加趋势线，形成折线图。例如根据资料绘制的直方图和折线图。某生产车间50名工人日加工零件频数分布直方图某生产车间50名工人日加工零件频数分布折线图直方图与条形图不同。首先，条形图是用条形的长度（横置时）表示各类别频数的多少，其宽度（表示类别）则是固定的；直方图是用面积表示各组频数的多少，矩形的高度表示每一组的频数或频率，宽度则表示各组的组距，因此，其高度与宽度均有意义。其次，由于分组数据具有

17、连续性，直方图的各矩形通常是连续排列，而条形图则是分开排列。最后，条形图主要用于展示分类数据，而直方图主要用于展示数值型数据。3、圆形图（饼图 Pie ）。用于显示定类变量的次数分布。它是用圆形及圆内扇形的面积来表示数值大小的图形。饼图主要用于表示总体中各组成部分所占的比例，对于研究结构性问题十分有用。在绘制饼图时，总体中各部分所占的百分比用圆内的各个扇形面积表示,这些扇形的中心角度，是按各部分比占3600的相同比例确定的。如图（a）、(b)、(c)所示。图（a） 饼图图(b) 饼图图 (c) 饼图4、环形图。环形图与饼形图类似，但又有区别。环形图中间有一个“空洞”，总体或样本中的每一部分数据

18、用环中的一段表示。饼图只能显示一个总体和样本各部分所占的比例，而环形图则可以同时绘制多个总体或样本的数据系列，每一个总体或样本的数据系列为一个环。因此环形图可显示多个总体或样本各部分所占的相应比例，从而有利于我们进行比较研究。例如根据资料绘制成的环形图，如图所示。环形图5、线图（Line）。线图是在平面坐标上用折线表现数量变化特征和规律的图形。主要用于显示连续型变量的次数分布和现象的动态变化。例如，根据资料绘制成的乙城市家庭对住房状况的评价线图，如图 (a)、(b)所示。乙城市向上累积频数分布图乙城市向上累计频数分布图6、散点图（Scatter）。主要用来观察变量间的相关关系，也可显示数量随时

19、间的变化情况。如图所示。散点图试验二：计算描述统计量此分析工具用于生成对输入区域中数据的单变值分析，提供有关数据趋中性和易变性的信息。（一）描述统计1、“描述统计”对话框输入区域在此输入待分析数据区域的单元格引用。该引用必须由两个或两个以上按列或行组织的相邻数据区域组成。分组方式如果需要指出输入区域中的数据是按行还是按列排列，请单击“逐行”或“逐列”。标志位于第一行/列如果输入区域的第一行中包含标志项，请选中“标志位于第一行”复选框；如果输入区域的第一列中包含标志项，请选中“标志位于第一列”复选框；如果输入区域没有标志项，则该复选框不会被选中，Microsoft Excel 将在输出表中生成适

20、宜的数据标志。平均数置信度如果需要在输出表的某一行中包含均值的置信度，请选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度。例如，数值 95% 可用来计算在显著性水平为 5% 时的均值置信度。第 K 大值如果需要在输出表的某一行中包含每个区域的数据的第 k 个最大值，请选中择此复选框，然后在右侧的编辑框中，输入 k 的数值。如果输入 1，则这一行将包含数据集中的最大数值。第 K 小值如果需要在输出表的某一行中包含每个区域的数据的第 k 个最小值，请选中此复选框，然后在右侧的编辑框中，输入 k 的数值。如果输入 1，则这一行将包含数据集中的最小数值。输出区域在此输入对输出表左上角单元格的引

21、用。此工具将为每个数据集产生两列信息。左边一列包含统计标志项，右边一列包含统计值。根据所选择的“分组方式”选项的不同，Microsoft Excel 将为输入表中的每一行或每一列生成一个两列的统计表。新工作表组单击此选项，可在当前工作簿中插入新工作表，并由新工作表的 A1 单元格开始粘贴计算结果。如果需要给新工作表命名，请在右侧编辑框中键入名称。新工作簿单击此选项，可创建一新工作簿，并在新工作簿的新工作表中粘贴计算结果。汇总统计如果需要 Microsoft Excel 在输出表中生成下列统计结果，请选中此复选框。这些统计结果有：平均值、标准误差（相对于平均值）、中值、众数、标准偏差、方差、峰值

22、、偏斜度、极差（全距）、最小值、最大值、总和、总个数、Largest (#)、Smallest (#) 和置信度。2、实例应用：某老师对几名学生的五次考试成绩进行分析，以便对这几个学生在学习方面的问题加以解决。使用“描述统计”分析工具对其数据进行分析。（数据文件名：描述统计.xls）操作步骤：1、 打开数据“描述统计.xls”。如下：2、选择“数据分析”对话框中“描述统计”，跳出“描述统计”对话框。2、 在“输入区域”编辑框中键入三列数据所在的单元格区域引用（$A$4:$F$8）。3、 单击“逐行”选项。4、 选中“标志位于第一行”选项。5、 在“输出选项”下单击“新工作表组”选项，并在对应编

23、辑框中输入新工作表的名称。如：“描述统计结果”。6、 选中“汇总统计”。7、 选中“平均数置信度”，并在其相应的编辑框中输入“95”。8、 选中“第K大值”和“第K小值”，并在其相应编辑框中输入“1”。9、 单击“确定”。10、 数据输出在新创建的工作表“协方差分析结果”中的“A1:J18”区域。如下：在数据输出的工作表中，可以看出每个学生的的成绩的各种分析结果。其中第3行至第18行分别为：平均值、标准误差、中值、标准误差、样本方差、峰值、偏度、最大值、最小值、和、计数、第1大值、第1小值、95%概率保证程度的置信度。根据“标准偏差”值可以看出王华的成绩离散程度最大，即是说其成绩极不稳定，刘明

24、的成绩是最稳定的。若取中值进行分析则是王华的成绩最好。平均值为刘明最佳。总体上而言，王华的成绩应该是最好的，只是最后一次的成绩太差，造成了较大的影响。（二）描述统计函数的介绍得到数据的基本描述统计量除了用Excel数据分析中的描述统计外，还可以直接通过函数功能得到各个统计量的值，步骤如下：1、打开数据文件。2、在“插入”菜单中选择函数，从类别中选择“统计”，如下图：3、选择需要计算的函数，单击确定，例如选择AVERAGE确定，出现如下窗口：4、指定数据区域计算描述统计量，单击确定，即在指定的空白单元格返回该函数值。5、计算其他函数方法完全相同。EXCEL中常用描述统计量函数对照表函数名称（英）

25、函数名称（中）公式或符号AVEDEV平均差-=xxnDA1.AVERAGE算术平均数nxx=GEOMEAN几何平均数nxG=HARMEAN调和平均数=xnH1MAX最大值MEDIAN中位数eMMIN最小值MODE众数oMSTDEV样本标准差（标准偏差）1)(2-=nxxSSTDEVP总体标准差nxx-=2)(sVAR样本方差2sVARP总体方差2s试验三 利用Excel进行推断统计（一）用Excel进行随机抽样使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，具体可参见教材有关抽样的章节，编号后，将编号输入工作表。资料假定有80个总体单位，每

26、个总体单位给一个编号，共有从1到80个编号，输入工作表后如下图所示：步骤输入各总体单位的编号后，可按以下步骤操作：第1步：单击工具菜单，选择“数据分析”选项，打开“数据分析”对话框，从中选择“抽样”，如图所示。第2步：单击“抽样”选项，弹出“抽样”对话框，如下图：第3步：在输入区域框中输入总体单位编号所在的单元格区域，在本例是$A$1:$H$10，系统将从A列开始抽取样本，然后按顺序抽取B列至H列。如果输入区域的第一行或第一列为标志项（横行标题或纵列标题），可单击标志复选框。第4步：在抽样方法项下，有周期和随机两种抽样模式：“周期”模式即所谓的等距抽样，采用这种抽样方法，需将总体单位数除以要抽

27、取的样本单位数，求得取样的周期间隔。如我们要在80个总体单位中抽取10个，则在“间隔”框中输入8。“随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。采用纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可；若采用分类抽样，必须先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合；整群抽样也要先将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的类的所有单位全部进行调查。可以看出，此例的编号输入方法，只适用于等距抽样和纯随机抽样。第5步：指定输出区域，在这里我们输入$A$12，单击确定后，即可得到抽样结果。结果8个随机

28、抽取的样本编号就显示在区域“A12：A19”单元格中。（二）用Excel求置信区间 用Excel的“统计函数”工具进行抽样推断中的区间估计测算。下面结合实例来说明具体的操作步骤。资料某商店随机抽查10名营业员，统计他们的日营业额(千元)如附图51中的“A2：A11”。假定该商店各营业员的日营业额是服从正态分布，试以95%的置信水平估计该商店营业员的日营业额的置信区间。为构造区间估计的工作表，在工作表中输入下列内容：A列输入样本数据，B列输入变量名称，C列输入计算公式，其实C列中当计算公式输入后显现的是计算结果，为了说明计算过程，在D列中展示C列的计算公式。步骤第1步：把样本数据输入到A2：A1

29、1单元格第2步：在C2中输入公式“=COUNT（A2：A11）”，得到计算结果“10”。“COUNT”是计数函数，得出样本容量(n=10)。第3步：在C3中输入“=AVERAGE（A2：A11）”，在C4中输入“=STDEV（A2：A11）”，在C5中输入“=C4/SQRT（C2）”，在C6中输入0.95，在C7中输入“=C2-1”，在C8中输入“=TINV（1-C6，C7）”，在C9中输入“=C8*C5”，在C10中输入“=C3-C9”，在C11中输入“=C3+C9”。在输入每一个公式回车后，便可得到如下表的结果。样本数据计算指标计算公式计算结果42样本数据个数C2=COUNT(A2:A11

30、)1045样本均值C3=AVERAGE(A2:A11)38.443样本标准差C4=STDEV(A2：A11)4.40抽样平均误差C5=C4/SQRT(C2) 1.38置信水平C6=0.950.9536自由度C7=C2-1935t值C8=TINV(1-C6，C7)2.32误差范围C9=C8*C53.34置信下限C10=C3-C935.39置信上限C11=C3+C941.结果从上面的结果我们可以知道，该商店营业员的日营业额的置信下限为35. (千元)，置信上限为41. (千元)。计算结果可以得出，我们有95%的把握认为该商店营业员的日营业额平均在35.(千元)到41.(千元)之间。 在上表中，对于

31、不同的样本数据，依上表的格式，只要输入新的样本数据，再对C列公式略加修改，置信区间就会自动给出。（三）用Excel进行假设检验假设检验包括一个正态总体的参数检验和两个正态总体的参数检验。对于一个正态总体参数的检验,可利用函数工具和自己输入公式的方法计算统计量,并进行检验。本例主要介绍如何使用Excel进行两个正态分布的均值方差的检验。资料为了评价两个学校的教学质量,分别在两个学校抽取样本。在A学校抽取30名学生,在B学校抽取40名学生,对两个学校的学生同时进行一次英语标准化考试，成绩如下所示。假设学校A考试成绩的方差为64，学校B考试成绩的方差为100。检验两个学校的教学质量是否有显著差异。（

32、a=0.05）学校A学校B7097 85 87 64 738690 82 83 92 7472 94 76 89 73 8891 79 84 76 87 8885 78 83 84 91 7476 91 57 62 89 82 93 6480 78 99 59 79 82 70 8583 87 78 84 84 70 79 7291 93 75 85 65 74 79 6484 66 66 85 78 83 75 74假定我们将上表中学校A的数据输入到工作表中的A2：A31,学校B的数据输入到工作表的B2：B41。步骤第1步：选择“工具”下拉菜单。再选择“数据分析”选项。第2步：在分析工具中选

33、择“Z检验:双样本平均差检验”，如图第3步：当出现对话框后，在“变量1的区域”方框内键入A2：A31；在“变量2的区域”方框内键入B2：B41；在“假设平均差”方框内键入0；在“变量1的方差”方框内键入64；在“变量2的方差”方框内键入100；在“”方框内键入0.05；在“输出选项”中选择输出区域(在此选择“新工作表”)。如图所示。第4步：所有选项设置好，选择确定。结果输出结果如下。变量 1变量 2平均数82.578已知协方差64100观测值个数3040假设平均差0z2.P(Z=z) 单尾0.z 单尾临界1.P(Z=z) 双尾0.z 双尾临界1.由于2ZZa，所以拒绝，即两个学校的教学质量有显

34、著差异。试验四 用Excel进行相关与回归分析（一）相关分析相关分析可用于判断两组数据之间的关系。我们可以使用“相关分析”来确定两个区域中数据的变化是否相关。用Excel进行相关分析有两种方法，一是利用相关系数函数计算，如“CORREL函数”和“PERSON函数”；另一种是利用“数据分析”功能相关分析宏计算。这里主要介绍后者。资料有10个同类企业生产性固定资产年均价值和工业增加值资料如下表：企业编号生产性固定资产价值（万元）工业增加值（万元）13185242910101932006384409815541591365029287314605812101516910221219101225162

35、4合计65259801要求根据资料计算相关系数，并说明两变量相关的方向和程度。步骤将数据输入工作表后，按如下步骤： 第1步：选择“工具”下拉菜单，再选择“数据分析”选项。 第2步：在分析工具中选择“相关系数”。 第3步：当出现对话框时，在“输入区域”方框内键入A2：B11，在“输出选项”中选择输出区域(在此我们选择“新工作表”)。最后：“确定”，得出下图。结果根据上述步骤计算的相关系数矩阵如附图71所示。表中得出了两个变量之间的相关系数，如“生产性固定资产价值（万元）”与“工业增加值（万元）”的相关系数为0.，属于高度正相关。（二）回归分析 利用Excel可以很容易地进行回归分析，包括一元线性

36、回归和多元线性回归。资料根据上表的资料，编制直线回归方程，计算估计标准误，并估计生产性固定资产（自变量）为1100万元时，工业增加值（因变量）的可能值。步骤我们仍结合上面的例子说明其操作步骤： 第1步：选择“工具”下拉菜单。 第2步：选择“数据分析”选项。 第3步：在分析工具中选择“回归”。 第4步：当出现对话框时，在“输入Y的区域”方框内键入B2：B11，在“输入X的区域” 方框内键入A2：A11，在“输出选项”中选择输出区域(这里我们选择“新工作表”)。 最后：“确定”。结果得到下图所示的结果。为了让大家看清楚，我们把有关的指标稍作解释。附图72中回归统计部分给出了判定系数、调整后的、估计

37、标准误差等；方差分析表部分给出的显著水平F值表明回归方程是显著的；最下面的一部分是567.395=a，.0=b。以及参数的标准差、t检验的统计量、p-值、下限95%和上限95%给出了参数置信区间。比如，我们有95%的把握确信，在210.4844和580.64964之间，在0.和1.之间。除表中输出的结果外，我们还可以根据需要给出残差图、线性拟合图等。所以，该例题中得到的回归方程为：xyc.0567.395+=，回归估计标准误为：=yxS126.6279。当生产性固定资产万元时，工业总产值为：（万元）。 实验五 用Excel进行时间数列分析（一） 用Excel进行季节变动分析 为介绍Excel在

38、季节变动分析中的应用，我们以实例操作，采用趋势剔除法计算季节指数。资料某小型企业销售收入如表单位：万元年份春夏秋冬200779486810720089766851342009113911001482010136105125174步骤把数据输入到工作表中的B2：B17。用Excel构造一张季节变动分析表，计算的步骤如下： 第1步：计算4项移动平均数。在C3单元格输入公式“=AVERAGE(B2：B5)”，然后将公式复制到C4：C15单元格。结果如下图的C列。 第2步：计算计算移动平均趋势值(中心化移动平均数)。也就是对C列的结果再进行一次二项移动平均。在D4单元格输入公式“AVERAGE(C3：C4)”，然后将公式复制到D5：D15单元格。结果如下图中的D列。 第3步：将实际值除以相应的趋势值。在E4单元格输入公式“=B4/D4”，然后将公式复制到E5：E15单元格。结果如下图中的E列。 第4步：计算同季平均。在F2单元格输入公式“=(E6+E10+E14)/3，在