统计学实验实习指导书1.doc

统计学实验实习指导书统计学课题组安徽科技学院管理学院2007-9-18Excel 工作界面简介附图1 Excel 工作界面按附图1 从上到下的顺序，Excel 工作界面包含如下几项内容：“标题”栏、“菜单”栏、“工具”栏、“编辑”栏、工作表、工作表标签、滚动条、和“状态”栏。下面分别介绍它们的作用。（一）“标题”栏“标题” 栏告诉用户正在运行的程序名称和正在打开的文件的名称。如图附-1 所示,标题栏显示“Microsoft Excel-Book1”表示此窗口的应用程序为Microsoft Excel ， 在Excel 中打开的当前文件的文件名为Book1.xls。（二）“菜单”栏“菜单”栏按功能把Excel 命令分成不同的菜单组，它们分别是“文件” 、“编辑” 、“视图” 、“插入” 、“格式” 、“工具” 、“表格” 、 “帮助”。当菜单项被选中时，引出一个下拉式菜单，可以从中选取相应的子菜单。另外，在屏幕的不同地方单击鼠标右键时，“快捷菜单”将出现在鼠标指针处。选取“快捷菜单”中的命令同从菜单栏的菜单上选取相应命令的效果是一样的，但选取速度明显增快。（三）“工具”栏Excel 可显示几种工具栏，这些工具可控制简化用户的操作。“工具”栏中的按钮都是菜单中常用命令的副本，当鼠标指向某一按钮后，稍等片刻在按钮右下方会显示该按扭命令的含意。用户可以配置“工具”栏的内容，通过“视图”菜单中的“工具”栏子菜单来选择显示不同类型的“工具”或全部显示出来。下面介绍出现在Excel 开始屏幕中的两种“工具”栏。1“常用”工具栏“常用”工具栏中为用户准备了访问Excel 最常用命令的快捷按钮，如“新建文件”按扭，“打开文件”按扭，“保存文件”按钮等。2“格式”工具栏“格式”工具栏专门放那些和文本外观有关的命令，如字体、字号、对齐方式及其他选项。（四）“编辑”栏“编辑”栏给用户提供活动单元格的信息。在“编辑”栏中用户可以输入和编辑公式，“编辑”栏位于图1 中第5 行。“编辑”栏由“名字”栏和“公式”栏组成。位于“编辑”栏左侧的“名字”栏中显示的是活动单元格的坐标，也可在“名字”栏中直接输入一个或一块单元格的地址进行单元格的快速选定；位于“编辑”栏右侧的“公式”栏可用于编辑活动单元格的内容，它包含三个按钮和一个编辑区。当向活动单元格输入数据时，公式栏中便出现三个按钮，三个按钮从左至右分别是：“”（取消）按钮、“”（确认）按钮和“”（公式指南）按钮。通常Excel 在工作区中显示“编辑”栏。在“视图”菜单中的“编辑栏”命令是一个开关命令，它可以用于隐藏或显示“编辑”栏。（五）工作表工作簿窗口包含了16 张独立的工作表（sheet）。开始时，窗口中显示第一张工作表“Sheetl”，该表为当前工作表。当前工作表只有一张，用户可通过点击工作表下方的标签击活其他工作表为当前工作表。工作表是一个由行和列组成的表格。行号和列号分别用字母和数字区别。行由上自下范围165536，列号则由左到右采用字母编号AIV。因此，每张表为256 列65536 行，若从Excel 导入的数据超过以上范围，则会被Excel 自动截去。每一个行、列坐标所指定的位置称之为单元格。在单元格中用户可以键人符号、数值、公式以及其他内容。（六）工作表标签工作表标签通常用“Sheet1” ,“Sheet2”等名称来表示，用户也可以通过用鼠标右击标签名，选择弹出菜单中“重命名”命令来修改标签名。Excel 一般同时显示工作表队列中的前3 个标签。利用标签队列左边的一组标签滚动按钮可显示队列中的后续工作表的标签。工作簿窗口中的工作表称之为当前工作表，当前工作表的标签为白色，其他为灰色。（七）“滚动”栏当工作表很大时，如何在窗口中查看表中的全部内容呢？可以使用工作簿窗口右边及下边的滚动栏，使窗口在整张表上移动查看，也可以通过修改常用“工具”栏中“显示比例框”的参数来扩大整个工作表的显示范围。（八）“状态”栏“状态”栏位于Excel 窗口底部，它的左端是信息区，右端是键盘状态区。在信息区中，显示的是Excel 的当前工作状态。例如，当工作表准备接受命令或数据时，信息区显示“就绪” ；当在“编辑”栏中键人新的内容时，信息区显示“输入”；当选取菜单命令或工具按钮时，信息区显示此命令或工具按钮用途的简要提示。在键盘状态区中，显示的是若干按键的开关状态。例如，当按CapsLock键时，状态 栏中便显示“CAPS”。与“编辑”栏相同，在“视窗”菜单中的“状态”命令是一个开关命令，它可以用于隐藏或显示“状态”栏。Excel 基本操作一、Excel 操作方法概述要完成任一项Excel 操作一般都可以找到三种操作方法：鼠标操作、菜单操作和键盘命令操作。例如，想要将A1 单元格的数据复制到A2 单元格去，有如下几种操作方法：（一）鼠标操作法：先用鼠标选中A1 单元格，然后缓慢移动鼠标到A1单元格的右下角，当鼠标的形状变为黑色实心“十”字形之后(以后称之为“添充柄”)，拖动鼠标到A2 单元格，然后放开鼠标，则A1 的数据就复制到A2 单元格了。（二）菜单操作法：先用鼠标选中A1 单元格，选择“编辑”菜单中的“复制”命令，然后用鼠标选中A2 单元格，再选择“编辑”菜单中的“粘贴”命令，数据就复制到A2 单元格了。（三）键盘命令操作法：直接用鼠标选中A2 单元格，从键盘输入“A1”命令，则复制即告完成。以上是Excel 中很典型的三种操作方法。在实际使用过程中，应根据实际情况，尽量选择三种方法中最简洁的操作方法，以提高操作速度。二、文件基本操作（一）新建文件：进入“文件”菜单栏，选择“新建”即可创建一个新的Excel 文件。（二）打开文件：进入“文件”菜单栏，选择“打开”子菜单，可在Excel 中打开一个已经存在的数据文件。它可以是Excel 的数据文件，也可是Excel 兼容的其它软件的数据文件。可在不同窗口中同时打开多个数据文件,通过“窗口”菜单下方的不同选项，进行不同窗口的切换。（三）保存文件：进入“文件”菜单栏，选择“保存”命令，可保存当前数据文件。如果选择“另存为”，可将当前工作簿存为一个新的文件。保存文件的格式可以是Excel 的数据文件，也可是Excel 兼容的其它软件的数据文件。（四）文件打印：进入“文件”菜单栏，选择“打印”，可打印当前的工作簿文件。打印之前，可以选择“文件”菜单栏的“页面设置”和“打印预览”选项，进行打印前的页面设置操作和打印效果的预先浏览。三、数据的输入输出操作（一）数据的手动输入建立一个新的Excel 文件之后，便可进行数据的输入操作。Excel 中以单元格为单位进行数据的输入操作。一般用上下左右光标键，Tab 键或用鼠标选种某一单元格，然后输入数据。Excel 中的数据按类型不同通常可分为四类：数值型，字符型，日期型，和逻辑型。Excel 根据输入数据的格式自动判断数据属于什么类型。如日期型的数据输入格式为“月/日/年”，“月-日-年”或“时：分：秒”。要输入逻辑型的数据，输入“true”(真)或 “false”(假)即可。若数据由数字与小数点构成，Excel 自动将其识别为数字型，Excel 允许在数值型数据前加入货币符号，Excel 将其视为货币数值型，Excel 也允许数值型数据用科学记数法表示，如2109 在Excel 中可表示为2E+9。除了以上三种格式以外的输入数据，Excel 将其视为字符型处理。（二）公式生成数据Excel 的数据中也可由公式直接生成。例如：在当前工作表中A1 和B1单元格中已输入了数值数据，欲将A1 与B1 单元格的数据相加的结果放入C1 单元格中，可按如下步骤操作：用鼠标选定C1 单元格，然后输入公式“=A1+B1”或输入“=SUM(a1:b1)”,回车之后即可完成操作。C1 单元格此时存放实际上是一个数学公式“A1+B1”，因此C1 单元格的数值将随着A1、B1 单元格的数值的改变而变化。Excel 提供了完整的算术运算符,如(加)(减)*(乘)(除)(百分比)(指数)和丰富的函数，如SUM(求和)、CORREL(求相关系数)、STDEV(求标准差)等，供用户对数据执行各种形式的计算操作，在Excel 帮助文件中可以查到各类算术运算符和函数的完整使用说明。（三）复制生成数据Excel 中的数据也可由复制生成。实际上，在生成的数据具有相同的规律性的时候，大部分的数据可以由复制生成。可以在不同单元格之间复制数据，也可以在不同工作表或不同工作簿之间复制数据，可以一次复制一个数据，也可同时复制一批数据，为数据输入带来了极大的方便。普通单元格的复制结果与公式单元格的复制结果相差较大，下面分别予以说明。1 普通单元格指的是非公式的单元格。普通单元格的复制，一般可以按如下步骤进行：（1） 拖动鼠标选定待复制的区域，选定之后该区域变为黑色。Excel可以进行整行、整列或整个表格的选定操作，例如，如果要选定表格的第一列，可直接用鼠标单击列标“A”， 如果要选定表格的第一行，可直接用鼠标单击行标“1”，如果要选定整个表格，可直接点击全选按扭，如附图-2 所示：附图 -2（2） 选定完区域之后，用鼠标右击该区域，选择“复制”，将区域内容复制到粘贴版之中。可以发现该区域已被虚线包围。（3） 用鼠标右击目标区域，选择“粘贴”，则单元格区域的复制即告完成。2公式单元格的复制，一般可分为两种，一种是值复制，一种是公式复制。值复制指的是只复制公式的计算结果到目标区域，公式复制指的是仅复制公式本身到目标区域。下面对它们的操作步骤分别予以说明。（1）值复制： 拖动鼠标选定待复制区域 。 用鼠标右击选定区域，选择“复制”选项。 用鼠标右击目标区域，再单击“选择性粘贴”子菜单。出现复制选项，选定“数值”选项，然后用鼠标单击“确定”按扭，则公式的值复制即告完成。（2）公式复制：公式复制是Excel 数据成批计算的重要操作方法，要熟练公式复制的操作首先要区分好两个概念：单元格的相对引用与绝对引用。Excel 中的公式中一般都会引用到别的单元格的数值，如果你希望当公式复制到别的区域之时，公式引用单元格不会随之相对变动，那么你必须在公式中使用单元格的绝对引用。如果你希望当公式复制到别的区域之时，公式引用单元格也会随之相对变动，那么你必须在公式中使用单元格的相对引用。在公式中如果直接输入单元格的地址，那么默认的是相对引用单元格，如果在单元格的地址之前加入“$”符号那么意味着绝对引用单元格。例如，在当前工作表中A1和B1 单元格中已输入了数值数据，用鼠标选定C1 单元格，然后输入公式“=A1+B1”,此公式引用的便是两个相对的单元格A1、B1,也就是说如果将该公式复制到C2 的单元格，公式所引用的单元格的地址将随着发生变化，公式将变为“=A2+B2”，如果将该公式复制到F100 的单元格,那么公式将变为“=D100+E100”，这就是相对引用的结果，公式的内容随着公式的位置变化而相对变化。如果在C1 单元格输入的是“=$A$1+$B$1”那么此公式引用的便是绝对的单元格，不论将公式复制到何处，公式的内容都不会发生变化。当然，绝对引用和相对引用亦可在同一公式之中混合交叉使用，例如，如果在C1 单元中输入的是公式“=A$1+B$1”,那么意味着，公式的内容不会随着公式的垂直移动而变动，而是随着公式的水平移动而变动，如果将该公式复制到F100 单元格，那么公式将变为，“=D$1+E$1”。可以作这样的归纳：公式中“$”符号后面的单元格坐标不会随着公式的移动而变动，而不带“$”符号后面的单元格坐标会随着公式的移动而变动。在实际的使用中，如果能把单元格的相对引用与绝对引用灵活应用到Excel 的公式之中，能为数据成批准确运算带来极大的方便。四、数据的移动操作数据的移动操作可按如下步骤进行：（一）拖动鼠标选定待移动区域 。（二）用鼠标右击选定区域，选择“剪切”选项。（三）用鼠标右击目标区域，选择“粘贴”，则单元格区域的移动即告完成。与数据的复制操作不同，公式单元格的移动操作不存在值移动或公式移动的区别，也不存在绝对引用已相对引用的区别，移动操作将把公式单元格的公式内容原原本本移动到目标区域，不作任何改动。五、数据的删除操作数据的删除操作可按如下步骤进行：（一）拖动鼠标选定待删除区域 。（二）用鼠标右击选定区域，选择“删除”，即可删除单元格区域的内容。如果不小心删除了不该删除的区域，可以通过“编辑”菜单的“撤消”命令来恢复被删除的内容。“撤消”操作是Excel 中较常用到的操作，如果不小心实施了错误的操作，那么可以通过“撤消”操作使工作表恢复原样。六、与其它软件交换数据的方法在Excel 中可以打开其它类型的数据文件，如FOXPRO 系列的DBF 数据库文件，文本文件，lotus1-2-3 的数据文件等。具体操作方法如下：（一）在“文件”菜单中选择“打开”子菜单。（二）在“打开文件”对话框中选择所要打开的文件的类型及其所在的目录。（三）用鼠标双击该文件名，并按Excel 提示步骤操作即可打开该文件。Excel 文件同样也可存为其它类型的数据文件，具体操作方法如下：（一）编辑好文件后，在“文件”菜单中选择“另存为”子菜单。（二）在“另存为”对话框中选择所要打开文件的类型及其所在的目录。（三）输入文件名之后，用鼠标单击“保存”按扭即可。实验一 用Excel绘制统计图一、用Excel绘制直方图用Excel进行统计分组有两种方法，一是利用FREQUENCY函数；二是利用数据分析中的“直方图”工具。我们介绍一下后者的操作方法。【例2】把50名工人的月产量资料输入工作表，如图1-5所示：图1-5工人月产量资料然后按以下步骤操作。第一步：在工具菜单中单击数据分析选项，从其对话框的分析工具列表中选择直方图，打开直方图对话框。如图1-6所示：图13-6直方图对话框第二步：在输入区域输入$A$1:$J$5，在接收区域输入$A$9:$A$15。接收区域指的是分组标志所在的区域，假定我们把分组标志输入到A9:A15单元格，注意这里只能输入每一组的上限值，即130，160，190，220，250，280，310。第三步：选择输出选项，可选择输入区域、新工作表组或新工作薄。我们在这里选择输入区域，可以直接选择一个区域，也可以直接输入一个单元格（代表输出区域的左上角），这里我们推荐只输入一个单元格（本例为A7），因为我们往往事先并不知道具体的输出区域有多大。第四步：选择图表输出，可以得到直方图；选择累计百分率，系统将在直方图上添加累积频率折线；选择柏拉图，可得到按降序排列的直方图。第五步：按确定按钮，可得输出结果如图1-7：图1-7频数分布和直方图应当注意，上图实际上是一个条形图，而不是直方图，若要把它变成直方图，可按如下操作：用鼠标左键单击任一直条，然后右键单击，在弹出的快捷菜单中选取数据系列格式，弹出数据系列格式对话框，如图1-8所示：图1-8数据系列格式对话框在对话框中选择选项标签，把间距宽度改为0，按确定后即可得到直方图，如图1-9所示：图1-9调整后的直方图二、用Excel作统计图Excel提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图等，各种图的作法大同小异。【例3】首先把数据输入到工作表中，如图1-10所示：图1-10河北省主要市2001年人口情况按以下步骤可作出饼图：第一步：选中某一单元格，单击插入菜单，选择图表选项，弹出图表向导对话框。如图1-11：图1-11图表向导对话框第二步：在图表类型中选择饼图，然后在子图表类型中选择一种类型，这里我们选用系统默认的方式。然后单击下一步按钮，打开源数据对话框。如图1-12所示：图1-12源数据对话框第三步：在源数据对话框中填入数据所在区域，单击完成按钮，即可得如1-13所示的饼图：图1-13饼图实验案例：统计分组，作统计图和计算描述统计量某灯泡厂抽取100只灯泡寿命如下：80091499182790990489199699994695086410499279498529488679888499589341000878978816100191810408541098900869949890103892787810509249059548901006926900999886907956900963838961948950900937864919863981916878891870986913850911886950926967921978821924951850要求：用MIN和MAX函数找出最小值和最大值，以50为组距，确定每组范围；用“数据分析”中“直方图”作直方图；用“数据分析”中“描述统计”计算100只灯泡的平均数，样本方差、中位数、众数和全距。操作步骤：将上表的数据复制到EXCEL中；选“插入-函数-统计-MAX”在单元格中出现最大值1098，同理找出最小值800；选一个单元格，输入每一组上限，组距50；第一组850，第二组900在“工具”中选“数据分析”-“直方图”（第一次要“加载宏”-“分析工具库”）在“输入区域”填入数据范围，在“接收区域”填入分组的范围，选择“输出区域”和“图表输出”，得到次数分布和直方图；对直方图进行编辑：在直方图上按右键，选“数据系列格式-选项”，将“分类间隔”设置为0，将编辑好的直方图和次数分布表复制到实验报告中；在“数据分析”中选“描述统计”，选择“输入区域”、“输出区域”和“汇总统计”，将结果复制到实验报告中。实验二用EXCEL计算描述统计量实验目的：用EXCEL计算描述统计量实验步骤：EXCEL中用于计算描述统计量的方法有两种，函数方法和描述统计工具的方法。一、 用函数计算描述统计量常用的描述统计量有众数、中位数、算术平均数、调和平均数、几何平均数、极差、四分位差、标准差、方差、标准差系数等。一般来说，在Excel中求这些统计量，未分组资料可用函数计算，已分组资料可用公式计算。这里我们仅介绍如何用函数计算。(一)众数【例4】：为了解某经济学院新毕业大学生的工资情况，随机抽取30人，月工资如下：156013401600141015901410161015701710155014901690138016801470153015601250156013501560151015501460155015701980161015101440用函数方法求众数，应先将30个人的工资数据输入A1：A30单元格，然后单击任一空单元格，输入“=MODE（A1：A30）”，回车后即可得众数为1560(二)中位数仍采用上面的例子，单击任一空单元格，输入“=MEDIAN（A1：A30）”，回车后得中位数为1550。(三)算术平均数单击任一单元格，输入“=AVERAGE（A1：A30）”，回车后得算术平均数为1535。(四)标准差单击任一单元格，输入“=STDEV（A1：A30）”，回车后得标准差为135.0287。二、 描述统计工具量的使用仍使用上面的例子，我们已经把数据输入到A1：A30单元格，然后按以下步骤操作：第一步：在工具菜单中选择数据分析选项，从其对话框中选择描述统计，按确定后打开描述统计对话框，如图2-1所示：图2-1描述统计对话框第二步：在输入区域中输入$A$1:$A$30，在输出区域中选择$C$1，其他复选框可根据需要选定，选择汇总统计，可给出一系列描述统计量；选择平均数置信度，会给出用样本平均数估计总体平均数的置信区间；第K大值和第K小值会给出样本中第K个大值和第K个小值。第三步：单击确定，可得输出结果，如图2-2所示：图2-2描述统计输出结果上面的结果中，平均指样本均值；标准误差指样本平均数的标准差；中值即中位数；模式指众数；标准偏差指样本标准差，自由度为n-1；峰值即峰度系数；偏斜度即偏度系数；区域实际上是极差，或全距。实验三 用EXCEL进行参数估计和假设检验实验目的：用EXCEL进行参数估计和假设检验实验步骤：在EXCEL中，进行参数估计只能使用公式和函数的方法，而假设检验除以上两种方法外，还可以使用假设检验工具。一、用EXCEL进行区间估计【例14】：某饭店在7星期内抽查49位顾客的消费额（元）如下： 15 24 38 26 30 42 18 30 25 26 34 44 20 35 24 26 34 48 18 28 46 19 30 36 42 24 32 45 36 21 47 26 28 31 42 45 36 24 28 27 32 36 47 53 22 24 32 46 26求在概率90%的保证下，顾客平均消费额的估计区间。如图7-1：图7-1 参数估计数据及结果计算方法如下：第一步：把数据输入到A2：A50单元格。第二步：在C2中输入公式“=COUNT（A2：A50）”，C3中输入“=AVERAGE（A2：A50）”，在C4中输入“STDEV（A2：A50）”，在C5中输入“=C4/SQRT（C2）”，在C6中输入0.90，在C7中输入“=C2-1”，在C8中输入“=TINV（1-C6，C7）”，在C9中输入“=C8*C5”，在C10中输入“=C3-C9”，在C11中输入“=C3+C9”。在输入每一个公式回车后，便可得到上面的结果，从上面的结果我们可以知道，顾客平均消费额的置信下限为29.73536，置信上限为34.26464。关于总体方差的估计、总体比例的估计等可按类似方法进行。二、用EXCEL进行假设检验用EXCEL函数和公式进行假设检验与参数估计类似，大家可以自已做一下，我们这里介绍一下假设检验工具的使用。在EXCEL中，假设检验工具主要有四个，如图7-2所示：图7-2 数据分析对话框平均值的成对二样本分析实际上指的是在总体方差已知的条件下两个样本均值之差的检验，准确的说应该是Z检验，双样本等方差检验是总体方差未知，但假定其相等的条件下进行的t检验，双样本异方差检验指的是总体方差未知，但假定其不等的条件下进行的t检验，双样本平均差检验指的是配对样本的t检验。我们介绍一下Z检验。【例15】：某厂铸造车间为提高缸体的耐磨性而试制了一种镍合金铸件以取代一种铜合金铸件，现从两种铸件中各抽一个样本进行硬度测试（表示耐磨性的一种考核指标）其结果如下：合镍铸件（X） 72.0 69.5 74.0 70.5 71.8 72合铜铸件（Y） 69.8 70.0 72.0 68.5 73.0 70.0根据以往经验知硬度，且，试在水平上比较镍合金铸件硬度有无显著提高。计算步骤如下：第一步：输入数据到工作表。第二步：单击工具菜单，选择数据分析选项，弹出对话框后，在其中选择双样本平均差分析，弹出对话框如图7-3所示：图7-3 双样本平均差分析对话框第三步：按上图所示输入后，按确定按钮，得输出结果如图7-4：图7-4双样本平均差分析结果在上面的结果中，我们可以根据P值进行判断，也可以根据统计量和临界值比较进行判断。如本例采用的是单尾检验，其单尾P值为0.17，大于给定的显著性水平0.05，所以应该接受原假设，即镍合金铸件硬度没有明显提高；若用临界值判断，得出的结论是一样的，如本例Z值为0.938194，小于临界值1.644853，由于是右尾检验，所以也是接受原假设。实验四 用EXCEL进行回归与预测分析（一）相关分析实验目的：用EXCEL进行相关与回归分析实验步骤：我们用下面的例子进行相关和回归分析：【例12】：10个学生身高和体重的情况如下：学生身高（公分）体重（公斤）1234567891017116717715416917516315217216053566449556652475850要求对身高和体重作相关和回归分析。首先把有关数据输入EXCEL的单元格中，如图5-1图5-1 EXCEL数据集用EXCEL进行相关分析有两种方法，一是利用相关系数函数，另一种是利用相关分析宏。1利用函数计算相关系数在EXCEL中，提供了两个计算两个变量之间相关系数的方法，CORREL函数和PERSON函数，这两个函数是等价的，这里我们介绍用CORREL函数计算相关系数：第一步：单击任一个空白单元格，单击插入菜单，选择函数选项，打开粘贴函数对话框，在函数分类中选择统计，在函数名中选择CORREL，单击确定后，出现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图5-4相关分析输出结果在上面的输出结果中，身高和体重的自相关系数均为1，身高和体重的相关系数为0.896，和用函数计算的结果完全相同。（二）用EXCEL进行回归分析EXCEL进行回归分析同样分函数和回归分析宏两种形式，其提供了9个函数用于建立回归模型和预测。这9个函数分别是：INTERCEPT 返回线性回归模型的截距SLOPE 返回线性回归模型的斜率RSQ 返回线性回归模型的判定系数FORECAST 返回一元线性回归模型的预测值STEYX 计算估计的标准误TREND 计算线性回归线的趋势值GROWTH 返回指数曲线的趋势值LINEST 返回线性回归模型的参数LOGEST 返回指数曲线模型的参数用函数进行回归分析比较麻烦，我们这里介绍使用回归分析宏进行回归分析。第一步：单击工具菜单，选择数据分析选项，出现数据分析对话框，在分析工具中选择回归，如图5-5图5-5数据分析对话框 第二步：单击确定按钮，弹出回归对话框，在Y值输入区域输入$B$2：$B$11，在X值输入区域输入$C$2：$C$11，在输出选项选择新工作表组，如图5-6所示：图5-6回归对话框 第四步：单击确定按钮，得回归分析结果如图5-7所示图5-7 EXCEL回归分析结果在上面的输出结果中，第一部分为汇总统计，MultipleR指复相关系数，R Square指判定系数，Adjusted指调整的判定系数，标准误差指估计的标准误，观测值指样本容量；第二部分为方差分析，df指自由度，SS指平方和，MS指均方，F指F统计量，Significance of F指p值；第三部分包括：Intercept指截距，Coefficient指系数，t stat指t统计量。三、实验案例：多元线性回归操作步骤输入数据；年份粮食产量y有机肥x1牲畜头数x2199424461519952544171996264616199726461519982544151999274616200028451820013048202002315019在“数据分析”中选“回归”输入“Y”、“X”、“输出区域”；得到输出结果SUMMARY OUTPUT回归统计Multiple R0.941103051R Square0.885674952Adjusted R Square0.847566603标准误差0.924835513观测值9方差分析dfSSMSFSignificance F回归分析239.7569619.8784823.240970.001494253残差65.1319240.855321总计844.88889Coefficients标准误差t StatP-valueLower 95%Intercept-11.26028928.289961-1.35830.223217-31.54509257X Variable 10.5448275860.2172332.5080350.0460250.013277964X Variable 20.7764182420.2224263.4906750.0129730.23216046写出直线回归方程，R平方值，并判断方程统计检验是否通过；2003年X1=52，X2=21进行预测，将方程和预测结果保存到实验报告中。实验案例 非线性回归用P358习题9数据作适当的回归方程，计算相关系数，并进行预测。操作步骤输入数据；“插入”-“图表”-“X-Y散点图”，编辑后将图表复制到实验报告中；在“工具”-“数据分析”中选“相关系数”；点击图表，在“图表”中选“添加趋势线”，选择适当类型，在“选项”中选择“显示公式”和“显示R平方值”，反复试验，直到找到一下最好的回归方程；计算估计标准误差；当产量为8万件，置信度为95.45%时，对单位成本进行区间估计。将回归方程和计算结果保存到实验报告中。（三）用EXCEL进行预测实验目的：用EXCEL进行预测实验步骤：【例13】：某煤矿某年1-11月份采煤量如下表：月份产量月份产量1234569.039.069.128.738.949.307891011129.159.369.459.309.241、用移动平均法进行预测具体步骤：第一步：将原始数据录入到单元格区域A2：A12，如图6-1所示：图6-1 EXCEL数据集第二步：选择菜单条上的“工具”“数据分析”命令，弹出如图6-2所示的对话框：图6-2数据分析对话框第三步：在“分析工具”框中选择“移动平均”，单击“确定”按钮，弹出移动平均对话框，相应作如下输入，即可得到如图6-3所示的对话框：（1） 在“输出区域”内输入：$A$2：$A$12，即原始数据所在的单元格区域。（2） 在“间隔”内输入：3，表示使用三步移动平均法。（3） 在“输出区域”内输入：B2，即将输出区域的左上角单元格定义为B2。（4） 选择“图表输出”复选框和“标准误差”复选框。6-3移动平均对话框第四步：单击“确定”按钮，便可得到移动平均结果，如图6-4所示：分析：在图中，B4：B12对应的数据即为三步移动平均的预测值；单元格区域C6：C12即为标准误差。6-4移动平均分析结果2、用指数平滑法进行预测：第一步：将原始数据输入到单元格B2：B12；第二步：选择菜单条上的“工具”“数据分析”命令，弹出如图6-5所示的对话框：图6-5数据分析对话框第三步：在“分析工具”中选择“指数平滑”，单击“确定”按钮，弹出一个对话框，作相应输入，即可得到如图6-6所示的对话框；6-6指数平滑对话框第四步：单击“确定”按钮，即可得到指数平滑结果，如图6-7所示：图6-7指数平滑结果3、趋势预测法进行预测第一步：把相关数据输入到EXCEL中，其中月份输入A1A11单元格，月产量输入B1B11单元格，如图6-8所示：图6-8 EXCEL数据集第二步：在工作表中选择一个空的单元格。在这里我们选择D2单元格。第三步：选择插入下拉菜单。第四步：选择函数选项。第五步：当函数对话框出现时：在函数类别框中选择统计，在函数名字中选择FORECAST(预测)，如图6-9所示：图6-9粘贴函数对话框第六步：单击确定按钮，出现预测对话框，在x中输入12，在know-ys中输入B1：B11，在know-xs中输入A1：A11，如图6-10所示：图6-10 FORCAST对话框第七步：单击确定按钮，预测结果出现在D2单元格中，如图6-11所示：图6-11趋势预测法预测结果实验五 综合业务实验实验名称：我国“黄金周”对拉动内需的经济效果分析实验目的：训练学生利用统计学相关知识解决实际问题的能力 1996年我国经济在经历了建国以来较为严重的通货膨胀后实现了软着陆，但是随后又出现的较为严重的内需不足让刚刚从通货膨胀的伤害中复苏的中国经济又蒙上了一层阴影，1999年，时任总理的朱镕基同志第一次提出扩大内需以拉动中国经济的论点，具有特定历史使命的“黄金周”政策正式出台，但是几年的时间过去了，在一片叫好声中，黄金周也开始暴露出其很多问题，如环境问题，基础设施压力过大等，让一部分人开始对黄金周政策的经济效果提出了质疑，请利用1990-2003年国内生产总值、社会商品零售额及城镇居民可支配收入的数据资料进行分析，对黄金周政策的经济效果进行评价。年份社会商品零售总额（亿元）城镇居民可支配收入（元）国内生产总值（亿元）19908300.11510.218547.919919415.61700.621617.8199210993.72026.626638.1199312462.12577.434634.4199416264.73496.246759.4199520620428358478.1199624774.14838.967884.6199727298.95160.374462.6199829152.55425.178345.2199931134.75854.0282067.46200034152.6628089468.1200137595.26859.697314.8200242027.17702.8105172.342003458428472.2117251.9表1：1990-2003年我国社会商品零售额、居民可支配收入及GDP数据要求：运用统计学及经济学的相关知识进行分析，根据分析结果对黄金周政策的经济效果进行客观的评价。实验及分析过程：1、1999年开始实行黄金周，因此可以考虑这一年是否是一个拐点，也就是说从1999年开始社会商品零售总额及GDP是否会出现一个高增长。做散点图：根据散点图我们可以看出，1999年社会商品零售总额及国内生产总值增幅不明显，而1999年至2003年度增幅也保持一个较平稳的状态，因此没有证据可以认为1999年是一个拐点，那么我们还可以从时间序列的发展速度来看。2、根据时间序列计算环比增长速度及平均发展速度如小表：年份社会商品零售总额城镇居民可支配收入国内生产总值社会商品零售总额环比发展速度GDP环比发展速度19908300.11510.218547.919919415.61700.621617.81.1343961.165512199210993.72026.626638.11.1676051.23223199312462.12577.434634.41.1335671.300183199416264.73496.246759.41.3051331.350085199520620428358478.11.2677761.250617199624774.14838.967884.61.201461.160855199727298.95160.374462.61.1019131.0969199829152.55425.178345.21.06791.052142199931134.75854.0282067.461.0679941.047511200034152.6628089468.11.096931.090178200137595.26859.697314.81.1008011.087704200242027.17702.8105172.341.1178851.0807442003458428472.2117251.91.0907721.114855200453950.19421.6平均发展速度1.1404881.152397根据上表，这14年度社会商品零售总额的平均增长速度约为14%，GDP的平均增长速度约为15%，而1999年相对于1998年度增幅分别仅为6.8%和4.75%，远远低于平均增长速度，并且1999年至2003年度平均增长速度也均低于平均增长速度，仍然无明显证据说明黄金周对经济发展有何作用，当然这里没有考虑到物价的因素，因此我们可以再从另一个角度来分析。3、购买能力与实际消费的关系。一般而言，消费能力与其购买力是密