统计学Excel应用.pdf

1、 数学与统计 学院 本科教学实验讲义 （实验）课程名称 统计学 贵州财经学院教务处制表 1 统计学实验教学讲义 Excel 工作界面简介 附图1 Excel 工作界面 按附图1 从上到下的顺序，Excel 工作界面包含如下几项内容：“标题”栏、“菜单”栏、“工具”栏、“编辑”栏、工作表、工作表标签、滚动条、 和“状态”栏。下面分别介绍它们的作用。 （一）“标题”栏 “标题” 栏告诉用户正在运行的程序名称和正在打开的文件的名称。 如图附-1 所示,标题栏显示“Microsoft Excel-Book1”表示此窗口的应用程序为Microsoft Excel ， 在Excel 中打开的当前文件的文件

2、名为Book1.xls。 （二）“菜单”栏 “菜单”栏按功能把Excel 命令分成不同的菜单组，它们分别是“文件” 、“编辑” 、“视图” 、“插入” 、“格式” 、“工具” 、“表格” 、 “帮助”。当菜单项被选中时，引出一个下拉式菜单，可以从中选取相应的子菜单。另外，2 在屏幕的不同地方单击鼠标右键时，“ 快捷菜单”将出现在鼠标指针处。选取“快捷菜单”中的命令同从菜单栏的菜单上选取相应命令的效果是一样的，但选取速度明显增快。 （三）“工具”栏 Excel 可显示几种工具栏，这些工具可控制简化用户的操作。“工具”栏中的按钮都是菜单中常用命令的副本，当鼠标指向某一按钮后，稍等片刻在按钮右下方会

3、显示该按扭命令的含意。用户可以配置“工具”栏的内容，通过“视图”菜单中的“工具”栏子菜单来选择显示不同类型的“工具”或全部显示出来。下面介绍出现在Excel 开始屏幕中的两种“工具”栏。 1“常用”工具栏 “常用”工具栏中为用户准备了访问Excel 最常用命令的快捷按钮，如“新建文件”按扭，“打开文件”按扭，“保存文件”按钮等。 2“格式”工具栏 “格式”工具栏专门放那些和文本外观有关的命令，如字体、字号、对齐方式及其他选项。 （四）“编辑”栏 “编辑”栏给用户提供活动单元格的信息。在“编辑”栏中用户可以输入和编辑公式，“编辑”栏位于图1 中第5 行。 “编辑”栏由“名字”栏和“公式”栏组成。

4、位于“编辑”栏左侧的“名字”栏中显示的是活动单元格的坐标，也可在“名字”栏中直接输入一个或一块单元格的地址进行单元格的快速选定；位于“编辑”栏右侧的“公式”栏可用于编辑活动单元格的内容，它包含三个按钮和一个编辑区。当向活动单元格输入数据时，公式栏中便出现三个按钮，三个按钮从左至右分别是：“”（取消）按钮、“”（确认）按钮和“”（公式指南）按钮。 通常Excel 在工作区中显示“编辑”栏。在“视图”菜单中的“编辑栏”命令是一个开关命令，它可以用于隐藏或显示“编辑”栏。 （五）工作表 工作簿窗口包含了16 张独立的工作表（sheet）。开始时，窗口中显示第一张工作表“Sheetl”，该表为当前工作

5、表。当前工作表只有一张，用户可通过点击工作表下方的标签击活其他工作表为当前工作表。 工作表是一个由行和列组成的表格。行号和列号分别用字母和数字区别。行由上自下范围165536，列号则由左到右采用字母编号AIV。因此，每张表为256 列65536 行，若从Excel 导入的数据超过以上范围，则会被Excel 自动截去。每一个行、列坐标所指定的位置称之为单元格。在单元格中用户可以键人符号、数值、公式以及其他内容。 （六）工作表标签 工作表标签通常用“Sheet1” ,“Sheet2”等名称来表示，用户也可以通过用鼠标右击标签名，选择弹出菜单中“重命名”命令来修改标签名。 Excel 一般同时显示工

6、作表队列中的前3 个标签。利用标签队列左边的一组 标签滚动按钮可显示队列中的后续工作表的标签。工作簿窗口中的工作表称之为当前工作表，当前工作表的标签为白色，其他为灰色。 （七）“滚动”栏 当工作表很大时，如何在窗口中查看表中的全部内容呢？可以使用工作簿窗口右边及下边的滚动栏，使窗口在整张表上移动查看，也可以通过修改常用“工3 具”栏中“显示比例框”的参数来扩大整个工作表的显示范围。 （八）“状态”栏 “状态”栏位于Excel 窗口底部，它的左端是信息区，右端是键盘状态区。在信息区中，显示的是Excel 的当前工作状态。例如，当工作表准备接受命令或数据时，信息区显示“就绪” ；当在“编辑”栏中键

7、人新的内容时，信息区显示“输入”；当选取菜单命令或工具按钮时，信息区显示此命令或工具按钮用途的简要提示。 在键盘状态区中，显示的是若干按键的开关状态。例如，当按Caps Lock键时，状态 栏中便显示“CAPS”。与“编辑”栏相同，在“视窗”菜单中的“状态”命令是一个开关命令，它可以用于隐藏或显示“状态”栏。 Excl 基本操作 一、Excel 操作方法概述 要完成任一项Excel 操作一般都可以找到三种操作方法：鼠标操作、菜单操作和键盘命令操作。例如，想要将A1 单元格的数据复制到A2 单元格去，有如下几种操作方法： （一）鼠标操作法：先用鼠标选中A1 单元格，然后缓慢移动鼠标到A1单元格的

8、右下角，当鼠标的形状变为黑色实心“十”字形之后(以后称之为“添充柄”)，拖动鼠标到A2 单元格，然后放开鼠标，则A1 的数据就复制到A2 单元格了。 （二）菜单操作法：先用鼠标选中A1 单元格，选择“编辑”菜单中的“复制”命令，然后用鼠标选中A2 单元格，再选择“编辑”菜单中的“粘贴”命令，数据就复制到A2 单元格了。 （三）键盘命令操作法：直接用鼠标选中A2 单元格，从键盘输入“A1”命令，则复制即告完成。 以上是Excel 中很典型的三种操作方法。在实际使用过程中，应根据实际情况，尽量选择三种方法中最简洁的操作方法，以提高操作速度。 二、文件基本操作 （一）新建文件：进入“文件”菜单栏，选

9、择“新建”即可创建一个新的Excel 文件。 （二）打开文件：进入“文件”菜单栏，选择“打开”子菜单，可在Excel 中打开一个已经存在的数据文件。它可以是Excel 的数据文件，也可是Excel 兼容的其它软件的数据文件。可在不同窗口中同时打开多个数据文件,通过“窗口”菜单下方的不同选项，进行不同窗口的切换。 （三）保存文件：进入“文件”菜单栏，选择“保存”命令，可保存当前数据文件。如果选择“另存为”，可将当前工作簿存为一个新的文件。保存文件的格式可以是Excel 的数据文件，也可是Excel 兼容的其它软件的数据文件。 （四）文件打印：进入“文件”菜单栏，选择“打印”，可打印当前的工作簿文

10、件。打印之前，可以选择“文件”菜单栏的“页面设置”和“打印预览”选项，进行打印前的页面设置操作和打印效果的预先浏览。 4 三、数据的输入输出操作 （一）数据的手动输入 建立一个新的Excel 文件之后，便可进行数据的输入操作。Excel 中以单元格为单位进行数据的输入操作。一般用上下左右光标键，Tab 键或用鼠标选种某一单元格，然后输入数据。Excel 中的数据按类型不同通常可分为四类：数值型，字符型，日期型，和逻辑型。Excel 根据输入数据的格式自动判断数据属于什么类型。如日期型的数据输入格式为“月/日/年”，“月-日-年”或“时：分：秒”。要输入逻辑型的数据，输入“true”(真)或 “

11、false”(假)即可。若数据由数字与小数点构成，Excel 自动将其识别为数字型，Excel 允许在数值型数据前加入 货币符号，Excel 将其视为货币数值型，Excel 也允许数值型数据用科学记数法表示，如2109 在Excel 中可表示为2E+9。除了以上三种格式以外的 输入数据，Excel 将其视为字符型处理。 （二）公式生成数据 Excel 的数据中也可由公式直接生成。例如：在当前工作表中A1 和B1单元格中已输入了数值数据，欲将A1 与B1 单元格的数据相加的结果放入C1 单元格中，可按如下步骤操作：用鼠标选定C1 单元格，然后输入公式“=A1+B1”或输入“=SUM(a1:b1)

12、”,回车之后即可完成操作。C1 单元格此时存放实际上是一个数学公式“A1+B1”，因此C1 单元格的数值将随着A1、B1 单元格的数值的改变而变化。Excel 提供了完整的算术运算符,如(加)(减)*(乘)(除)(百分比)(指数)和丰富的函数，如SUM(求和)、CORREL(求相关系数)、STDEV(求标准差)等，供用户对数据执行各种形式的计算操作，在Excel 帮助文件中可以查到各类算术运算符和函数的完整使用说明。 （三）复制生成数据 Excel 中的数据也可由复制生成。实际上，在生成的数据具有相同的规律性的时候，大部分的数据可以由复制生成。可以在不同单元格之间复制数据，也可以在不同工作表或

13、不同工作簿之间复制数据，可以一次复制一个数据，也可同时复制一批数据，为数据输入带来了极大的方便。普通单元格的复制结果与公式单元格的复制结果相差较大，下面分别予以说明。 1 普通单元格指的是非公式的单元格。普通单元格的复制，一般可以按如下步骤进行： （1） 拖动鼠标选定待复制的区域，选定之后该区域变为黑色。Excel可以进行整行、整列或整个表格的选定操作，例如，如果要选定表格的第一列，可直接用鼠标单击列标“A”， 如果要选定表格的第一行，可直接用鼠标单击行标“1”，如果要选定整个表格，可直接点击全选按扭，如附图-2 所示： 5 附图 -2 （2） 选定完区域之后，用鼠标右击该区域，选择“复制”，

14、将区域内容复制到粘贴版之中。可以发现该区域已被虚线包围。 （3） 用鼠标右击目标区域，选择“粘贴”，则单元格区域的复制即告完成。 2公式单元格的复制，一般可分为两种，一种是值复制，一种是公式复制。值复制指的是只复制公式的计算结果到目标区域，公式复制指的是仅复制公式本身到目标区域。下面对它们的操作步骤分别予以说明。 （1）值复制： 拖动鼠标选定待复制区域 。 用鼠标右击选定区域，选择“复制”选项。 用鼠标右击目标区域，再单击“选择性粘贴”子菜单。出现复制选项，选定“数值”选项，然后用鼠标单击“确定”按扭，则公式的值复制即告完成。 （2）公式复制： 公式复制是Excel 数据成批计算的重要操作方法

15、，要熟练公式复制的操作首先要区分好两个概念：单元格的相对引用与绝对引用。 Excel 中的公式中一般都会引用到别的单元格的数值，如果你希望当公式复制到别的区域之时，公式引用单元格不会随之相对变动，那么你必须在公式中使用单元格的绝对引用。如果你希望当公式复制到别的区域之时，公式引用单元格也会随之相对变动，那么你必须在公式中使用单元格的相对引用。 在公式中如果直接输入单元格的地址，那么默认的是相对引用单元格，如果在单元格的地址之前加入“$”符号那么意味着绝对引用单元格。例如，在当前工作表中A1和B1 单元格中已输入了数值数据，用鼠标选定C1 单元格，然后输入公式“=A1+B1”,此公式引用的便是两

16、个相对的单元格A1、B1,也就是说如果将该公式复制到C2 的单元格，公式所引用的单元格的地址将随着发生变化，公式将变为“=A2+B2”，如果将该公式复制到F100 的单元格,那么公式将变为“=D100+E100”，这就是相对引用的结果，公式的内容随着公式的位置变化而相对变化。如果在C1 单元格输入的是“=$A$1+$B$1”那么此公式引用的便是绝对的单元格，不论将公式复制到何处，公式的内容都不会发生变化。当然，绝对引用和相对引用亦可在同一公式之中混合交叉使用，例如，如果在C1 单元中输入的是公式“=A$1+B$1”,那么意味着，公式的内容不会随着公式的垂直移动而变动，而是随着公式的水平移动而变

17、动，如果将该公式复制到F100 单元格，那么公式将变为，“=D$1+E$1”。可以作这样的归纳：公式中“$”符号后面的单元格坐标不会随着公式的移动而变动，而不带“$”符号后面的单元格坐标会随着公式的移动而变动。 在实际的使用中，如果能把单元格的相对引用与绝对引用灵活应用到Excel 的公式之中，能为数据成批准确运算带来极大的方便。 四、数据的移动操作 数据的移动操作可按如下步骤进行： （一）拖动鼠标选定待移动区域 。 （二）用鼠标右击选定区域，选择“剪切”选项。 （三）用鼠标右击目标区域，选择“粘贴”，则单元格区域的移动即告完 6 成。 与数据的复制操作不同，公式单元格的移动操作不存在值移动或

18、公式移动的区别，也不存在绝对引用已相对引用的区别，移动操作将把公式单元格的公式内容原原本本移动到目标区域，不作任何改动。 五、数据的删除操作 数据的删除操作可按如下步骤进行： （一）拖动鼠标选定待删除区域 。 （二）用鼠标右击选定区域，选择“删除”，即可删除单元格区域的内容。 如果不小心删除了不该删除的区域，可以通过“编辑”菜单的“撤消”命令来恢复被删除的内容。“撤消”操作是Excel 中较常用到的操作，如果不小心实施了错误的操作，那么可以通过“撤消”操作使工作表恢复原样。 六、与其它软件交换数据的方法 在Excel 中可以打开其它类型的数据文件，如FOXPRO 系列的DBF 数据 库文件，文

19、本文件，lotus1-2-3 的数据文件等。具体操作方法如下： （一）在“文件”菜单中选择“打开”子菜单。 （二）在“打开文件”对话框中选择所要打开的文件的类型及其所在的 目录。 （三）用鼠标双击该文件名，并按Excel 提示步骤操作即可打开该文件。 Excel 文件同样也可存为其它类型的数据文件，具体操作方法如下： （一）编辑好文件后，在“文件”菜单中选择“另存为”子菜单。 （二）在“另存为”对话框中选择所要打开文件的类型及其所在的目录。 （三）输入文件名之后，用鼠标单击“保存”按扭即可。 7 实验一 用Excel搜集与整理数据 实验目的：掌握用EXCEL进行数据的搜集整理和显示 实验步骤：

20、 一、用Excel搜集数据 搜集数据的方法有多种，可以采用统计报表、典型调查、重点调查或抽样调查，以后我国的统计调查将以抽样为主，所以我们在这里介绍一下如何用 Excel进行抽样。 使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，编号后，将编号输入工作表。 【例1】假定有100个总体单位，每个总体单位给一个编号，共有从1到100个编号，输入工作表后如图1-1所示： 图：1-1总体各单位编号表 输入各总体单位的编号后，可按以下步骤操作： 第一步：单击工具菜单，选择数据分析选项（若无数据分析选项，可在工具菜单下选择加载宏，在弹出的对话框中选择分

21、析工具库，便可出现数据分析选项），打开数据分析对话框，从中选择抽样。如图1-2所示： 8 图1-2数据分析对话框 第二步：单击抽样选项，弹出抽样对话框。如图1-3 图1-3 抽样对话框 第三步：在输入区域框中输入总体单位编号所在的单元格区域，在本例是$A$1:$J$10，系统将从A列开始抽取样本，然后按顺序抽取B列至J列。如果输入区域的第一行或第一列为标志项（横行标题或纵列标题），可单击标志复选框。 第四步：在抽样方法项下，有周期和随机两种抽样模式： “周期”模式即所谓的等距抽样，采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，求得取样的周期间隔。如我们要在 100个总体单位中抽取12

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

23、l进行统计分组有两种方法，一是利用FREQUENCY函数；二是利用数据分析中的“直方图”工具。我们介绍一下后者的操作方法。 【例2】把50名工人的月产量资料输入工作表，如图1-5所示： 图1-5工人月产量资料 10然后按以下步骤操作。 第一步：在工具菜单中单击数据分析选项，从其对话框的分析工具列表中选择直方图，打开直方图对话框。如图1-6所示： 图13-6直方图对话框 第二步：在输入区域输入$A$1:$J$5，在接收区域输入$A$9:$A$15。接收区域指的是分组标志所在的区域，假定我们把分组标志输入到A9:A15单元格，注意这里只能输入每一组的上限值，即130，160，190，220，250

24、，280，310。 第三步：选择输出选项，可选择输入区域、新工作表组或新工作薄。我们在这里选择输入区域，可以直接选择一个区域，也可以直接输入一个单元格（代表输出区域的左上角），这里我们推荐只输入一个单元格（本例为 A7），因为我们往往事先并不知道具体的输出区域有多大。 第四步：选择图表输出，可以得到直方图；选择累计百分率，系统将在直方图上添加累积频率折线；选择柏拉图，可得到按降序排列的直方图。 第五步：按确定按钮，可得输出结果如图1-7： 图1-7频数分布和直方图 11 应当注意，上图实际上是一个条形图，而不是直方图，若要把它变成直方图，可按如下操作： 用鼠标左键单击任一直条，然后右键单击，在

25、弹出的快捷菜单中选取数据系列格式，弹出数据系列格式对话框，如图1-8所示： 图1-8数据系列格式对话框 在对话框中选择选项标签，把间距宽度改为 0，按确定后即可得到直方图，如图1-9所示： 图1-9调整后的直方图 三、用Excel作统计图 Excel提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图等，各种12图的作法大同小异。 【例3】首先把数据输入到工作表中，如图1-10所示： 图1-10河北省主要市2001年人口情况 按以下步骤可作出饼图： 第一步：选中某一单元格，单击插入菜单，选择图表选项，弹出图表向导对话框。如

26、图1-11： 图 1-11图表向导对话框 第二步：在图表类型中选择饼图，然后在子图表类型中选择一种类型，这里13我们选用系统默认的方式。然后单击下一步按钮，打开源数据对话框。如图1-12所示： 图1-12源数据对话框 第三步：在源数据对话框中填入数据所在区域，单击完成按钮，即可得如1-13所示的饼图： 石家庄市唐山市秦皇岛市保定市邯郸市邢台市沧州市张家口市图1-13饼图 14实验二用EXCEL计算描述统计量 实验目的：用EXCEL计算描述统计量 实验步骤： EXCEL 中用于计算描述统计量的方法有两种，函数方法和描述统计工具的方法。 一、 用函数计算描述统计量 常用的描述统计量有众数、中位数、

27、算术平均数、调和平均数、几何平均数、极差、四分位差、标准差、方差、标准差系数等。一般来说，在Excel中求这些统计量，未分组资料可用函数计算，已分组资料可用公式计算。这里我们仅介绍如何用函数计算。 (一)众数 【例 4】：为了解某经济学院新毕业大学生的工资情况，随机抽取 30 人，月工资如下： 1560 1340 1600 1410 1590 1410 1610 1570 1710 1550 1490 1690 1380 1680 1470 1530 1560 1250 1560 1350 1560 1510 1550 1460 1550 1570 1980 1610 1510 1440 用函

28、数方法求众数，应先将 30个人的工资数据输入A1：A30单元格，然后单击任一空单元格，输入“=MODE（A1：A30）”， 回车后即可得众数为1560 (二)中位数 仍采用上面的例子，单击任一空单元格，输入“ =MEDIAN（A1：A30）”，回车后得中位数为1550。 (三)算术平均数 单击任一单元格，输入“=AVERAGE（A1：A30）”，回车后得算术平均数为1535。 (四)标准差 单击任一单元格，输入“=STDEV（A1：A30）”， 回车后得标准差为135.0287。 二、 描述统计工具量的使用 仍使用上面的例子，我们已经把数据输入到A1：A30单元格，然后按以下步骤操作： 第一步

29、：在工具菜单中选择数据分析选项，从其对话框中选择描述统计，按确定后打开描述统计对话框，如图2-1所示： 15图2-1描述统计对话框 第二步：在输入区域中输入$A$1:$A$30，在输出区域中选择$C$1，其他复选框可根据需要选定，选择汇总统计，可给出一系列描述统计量；选择平均数置信度，会给出用样本平均数估计总体平均数的置信区间；第 K 大值和第K小值会给出样本中第K个大值和第K个小值。 第三步：单击确定，可得输出结果，如图2-2所示： 图2-2描述统计输出结果 上面的结果中，平均指样本均值；标准误差指样本平均数的标准差；中值即中位数；模式指众数；标准偏差指样本标准差，自由度为n-1；峰值即峰度

30、系数；偏斜度即偏度系数；区域实际上是极差，或全距。 16实验三 用EXCEL进行时间序列分析 实验目的：用EXCEL进行时间序列分析 实验步骤： 一、测定增长量和平均增长量 【例 5】：根据 1995-2001 年河北省国内生产总值，计算逐期增长量、累计增长量和平均增长量。如图3-1 图3-1用EXCEL计算增长量和平均增长量资料及结果 计算步骤如下： 第一步：在A列输入年份，在B列输入国内生产总值。 第二步：计算逐期增长量：在C3中输入公式：=B3-B2，并用鼠标拖曳将公式复制到C3：C8区域。 第三步：计算累计增长量：在 D3中输入公式：=B3-$B$2，并用鼠标拖曳公式复制到D3：D8区

31、域。 第四步：计算平均增长量（水平法）：在C10中输入公式：=（B8-B2）/5，按回车键，即可得到平均增长量。 二、测定发展速度和平均发展速度 【例6】：以1995-2001年河北省国内生产总值为例，说明如何计算定基发展速度、环比发展速度和平均发展速度。如图3-2 17图3-2用EXCEL计算发展速度和平均发展速度资料及结果 第一步：在A列输入年份，在B列输入国内生产总值。 第二步：计算定基发展速度：在 C3中输入公式：=B3/$B$2，并用鼠标拖曳将公式复制到C3：C8区域。 第三步：计算环比发展速度：在D3中输入公式：=B3/B2，并用鼠标拖曳将公式复制到D3：D8区域。 第四步：计算平

32、均发展速度（水平法）：选中C10单元格，单击插入菜单，选择函数选项，出现插入函数对话框后，选择 GEOMEAN（返回几何平均值）函数，在数值区域中输入D3：D8即可。 三、计算长期趋势 【例7】：我们用某企业某年12个的总产值资料来说明如何用移动平均法计算长期趋势。如图3-3 第一步：在A列输入月份，在B列输入总产值。 第二步：计算三项移动平均：在 C3 中输入“=（B2+B3+B4）/3”，并用鼠标拖曳将公式复制到 C3：C12区域。 第三步：计算四项移动平均：在D4中输入“=SUM（B2：B5）/4”，并用鼠标拖曳将公式复制到 D4：D12区域。 第四步：计算二项移正平均数：在 E4 中输

33、入“=（D4+D5）/2”，并用公式拖曳将公式复制到 E4：E11区域。 18图3-3用EXCEL计算长期趋势资料及结果 四、计算季节变动 【例 8】：利用某种商品五年分季度的销售额资料，说明如何用移动平均趋势剔除法测定季节变动。如图3-4 图3-4用EXCEL计算季节变动资料 19第一步：按图上的格式在A列输入年份，在B列输入季别，在C列输入销售收入。 第二步：计算四项移动平均：在D3中输入“=SUM（C2：C4）/4”，并用鼠标拖曳将公式复制到 D3：D19区域。 第三步：计算趋势值（即二项移动平均）T：在 E4中输入“=（D3+D4）/2”，并用鼠标拖曳将公式复制到 E4：E19区域。

34、第四步：剔除长期趋势，即计算Y/T：在 F4中输入“=C4/E4”，并用鼠标拖曳将公式复制到 F4：F19区域。 第五步：重新排列F4：F19区域中的数字，使同季的数字位于一列，共排成四列。 第六步：计算各年同季平均数：在 B29 单元格中输入公式：=average(B25:B28)；在 C29 中输入公式=average(C25:C28)；在 D29 中输入公式=average(D24:27)；在E29中输入公式=average(E24:E27)。 第七步：计算调整系数：在B31中输入公式：=4/sum(B29:E29) 第八步：计算季节比率：在B30中输入公式：=B29*$B$31，并用鼠

35、标拖曳将公式复制到单元格区域 B30：E30，就可以得到季节比率的值，具体结果见图3-5： 图3-5用EXCEL计算季节变动结果 20实验四 用EXCEL进行指数分析 实验目的：用EXCEL进行指数分析 实验步骤： 指数分析法是研究社会经济现象数量变动情况的一种统计分析法。指数有总指数与平均指数之分，在这一节我们介绍如何用Excel进行指数分析与因素分析。 一、用Excel计算总指数 【例 9】：图中是某企业甲、乙、丙三种产品的生产情况，以基期价格 p 作为同度量因素，计算生产量指数。如图4-1 图4-1用EXCEL计算总指数资料及结果 计算步骤： 第一步：计算各个p0q0：在G2中输入“=C

36、2*D2”，并用鼠标拖曳将公式复制到 G2：G4区域。 第二步：计算各个 p0*q1：有 H2 中输入“=C2*F2”，并用鼠标拖曳将公式复制到 H2：H4区域。 第三步：计算p0q0和p0q1：选定G2：G4区域，单击工具栏上的“”按钮，在 G5出现该列的求和值。选定 H2：H4区域，单击工具栏上的“”按钮，在 H5出现该列的求和值。 第四步：计算生产量综合指数Iq=p0q1/p0q0：在C6中输入“=H5/G5”便可得到生产量综合指数 21注意：在输入公式的时候，不要忘记等号，否则就不会出现数值。 二、用Excel计算平均指数 现以生产量平均指数为例，说明加权算术平均法的计算方法。 【例

37、10】：图中的 A1：A4 区域内是某企业生产情况的统计资料，我们要以基期总成本为同度量因素，计算生产量平均指数。如图4-2 图4-2用EXCEL计算平均指数资料及结果 计算步骤： 第一步：计算个体指数 k=q1/q0：在 F2中输入“=D2/C2”。并用鼠标拖曳将公式复制到 F2：F4区域。 第二步：计算k*p0q0并求和。在G2中输入“=F2*E2”并用鼠标拖曳将公式复制到G2：G4 区域。选定 G2：G4 区域，单击工具栏上的：“”按钮，在G5列出现该列的求和值。 第三步：计算生产量平均指数：在C7中输入“=G5/E5”即得到所求的值。 三、用Excel进行因素分析 【例11】：我们还用

38、上面的例子，有关资料如图4-3 22图4-3用EXCEL进行因素分析资料及结果 进行因素分析的计算步骤如下： 第一步：计算各个p0*q0和p0q0：在G2中输入“C2*D2”，并用鼠标拖曳将公式复制到 G2：G4区域。选定G2：G4区域，单击工具栏上的“”按钮，在G5出现该列的求和值。 第二步：计算各个p0*q1和p0*q1：在H2中输入“=C2*F2”，并用鼠标拖曳将公式复制到 H2：H4区域。选定H2：H6区域，单击工具栏上的“”按钮，在H5出现该列的求和值。 第三步：计算各个p1*q1和p1*q1：在I2中输入“=E2*F2”，并用鼠标拖曳将公式复制到 I2：I4区域。选定I2：I4区域

39、，单击工具栏上的“”按钮，在I5出现该列的求和值。 第四步：计算总成本指数：在C6中输入“=I5/G5”，即求得总成本指数。 第五步：计算产量指数：在C7中输入“=H5/G5”，即得产量指数 第六步：计算单位成本指数：在C8中输入“=I5/H5”即求得单位成本指数 23实验五 用EXCEL进行相关与回归分析 实验目的：用EXCEL进行相关与回归分析 实验步骤： 我们用下面的例子进行相关和回归分析： 【例12】：10个学生身高和体重的情况如下： 学生 身高（公分） 体重（公斤） 1 2 3 4 5 6 7 8 9 10 171 167 177 154 169 175 163 152 172 16

40、0 53 56 64 49 55 66 52 47 58 50 要求对身高和体重作相关和回归分析。 一、用EXCEL进行相关分析 首先把有关数据输入EXCEL的单元格中，如图5-1 图5-1 EXCEL数据集 用EXCEL进行相关分析有两种方法，一是利用相关系数函数，另一种是利用相关分析宏。 1利用函数计算相关系数 24在EXCEL中，提供了两个计算两个变量之间相关系数的方法，CORREL函数和PERSON函数，这两个函数是等价的，这里我们介绍用CORREL函数计算相关系数： 第一步：单击任一个空白单元格，单击插入菜单，选择函数选项，打开粘贴函数对话框，在函数分类中选择统计，在函数名中选择CO

41、RREL，单击确定后，出现CORREL对话框。 第二步：在 array1 中输入 B2：B11，在 array2 中输入 C2：C11，即可在对话框下方显示出计算结果为0.896。如图5-2所示： 图5-2CORREL对话框及输入结果 2用相关系数宏计算相关系数 第一步：单击工具菜单，选择数据分析选项，在数据分析选项中选择相关系数，弹出相关系数对话框，如图5-3所示： 图5-3相关系数对话框 第二步：在输入区域输入$B$1:$C$1，分组方式选择逐列，选择标志位于第一行，在输出区域中输入$E$1，单击确定，得输出结果如图5-4 25图5-4相关分析输出结果 在上面的输出结果中，身高和体重的自相

42、关系数均为1，身高和体重的相关系数为0.896，和用函数计算的结果完全相同。 二、用EXCEL进行回归分析 EXCEL进行回归分析同样分函数和回归分析宏两种形式，其提供了9个函数用于建立回归模型和预测。这9个函数分别是： INTERCEPT 返回线性回归模型的截距 SLOPE 返回线性回归模型的斜率 RSQ 返回线性回归模型的判定系数 FORECAST 返回一元线性回归模型的预测值 STEYX 计算估计的标准误 TREND 计算线性回归线的趋势值 GROWTH 返回指数曲线的趋势值 LINEST 返回线性回归模型的参数 LOGEST 返回指数曲线模型的参数 用函数进行回归分析比较麻烦，我们这里

43、介绍使用回归分析宏进行回归分析。 第一步：单击工具菜单，选择数据分析选项，出现数据分析对话框，在分析工具中选择回归，如图5-5 26图5-5数据分析对话框 第二步：单击确定按钮，弹出回归对话框，在 Y 值输入区域输入$B$2：$B$11，在X值输入区域输入$C$2：$C$11，在输出选项选择新工作表组，如图5-6所示： 图5-6回归对话框 第四步：单击确定按钮，得回归分析结果如图5-7所示 图5-7 EXCEL回归分析结果 27在上面的输出结果中，第一部分为汇总统计，MultipleR指复相关系数，R Square指判定系数，Adjusted指调整的判定系数，标准误差指估计的标准误，观测值指样

44、本容量；第二部分为方差分析，df指自由度，SS指平方和，MS指均方，F指F统计量，Significance of F指p值；第三部分包括：Intercept指截距，Coefficient指系数，t stat指t统计量。 28实验六 用统计软件进行预测 实验目的：用EXCEL进行预测 实验步骤： 【例13】：某煤矿某年1-11月份采煤量如下表： 月份 产量 月份 产量 1 2 3 4 5 6 9.03 9.06 9.12 8.73 8.94 9.30 7 8 9 10 11 12 9.15 9.36 9.45 9.30 9.24 一、用移动平均法进行预测 具体步骤： 第一步：将原始数据录入到单元

45、格区域A2：A12，如图6-1所示： 图6-1 EXCEL数据集 第二步：选择菜单条上的“工具”“数据分析”命令，弹出如图6-2所示的对话框： 图6-2数据分析对话框 29第三步：在“分析工具”框中选择“移动平均”，单击“确定”按钮，弹出移动平均对话框，相应作如下输入，即可得到如图6-3所示的对话框： （1） 在“输出区域”内输入：$A$2：$A$12，即原始数据所在的单元格区域。 （2） 在“间隔”内输入：3，表示使用三步移动平均法。 （3） 在“输出区域”内输入：B2，即将输出区域的左上角单元格定义为B2。 （4） 选择“图表输出”复选框和“标准误差”复选框。 6-3移动平均对话框 第四步：单击“确定”按钮，便可得到移动平均结果，如图6-4所示： 分析：在图中，B4：B12 对应的数据即为三步移动平均的预测值；单元格区域C6：C12即为标准误差。 6-4移动平均分析结果 二、用指数平滑法进行预测： 第一步：将原始数据输入到单元格B2：B12； 第二步：选择菜单条上的“工具”“数据分析”命令，弹出如图6-5所示的对话框：