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进行描述统计分析、绘制各种图表和运用数据透视表工具的技术。 实验内容及步骤 （一）描述统计分析 例 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 3 1984 652.1 1998 5425.1 1985 739.1 1999 5854 1986 899.6 2000 6280 1987 1002.2 2001 6859.6 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所示。 4 图 1-1 对话框内各选项的含义如下： 输入区域：在此输入待分析数据区域的单元格范围。 分组方式：如果需要指出输入区域中的数据是按行还是按列排列，则单击“行”或“列”。 标志位于第一行 /列：如果输入区域的第一行中包含标志项 (变量名 )，则选中“标志位于第一行”复选框；如果输入区域的第一列中包含标志项，则选中“标志位于第一列”。 复选框：如果输入区域没有标志项，则不选任何复选框， Excel 将在输出表中生成适宜的数据标志。 均值置信度：若 需要输出由样本均值推断总体均值的置信区间，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度。例如，置 5 信度 95%可计算出的总体样本均值置信区间为 10，则表示：在 5%的显著水平下总体均值的置信区间为 ( X -10, X +10）。 第 K 个最大 /小值：如果需要在输出表的某一行中包含每个区域 的数据的第 k 个最大 /小值，则选中此复选框。然后在右侧的编辑框中，输入 k 的数值。 输出区域：在此框中可填写输出结果表左上角单元格地址 ,用于控 制输出结果的存放位置。 新工作表：单击此选项，可在 当前工作簿中插入新工作表，并由新工作表的 A1 单元格开始存放计算结果。如果需要给新工作表命名，则在右侧编辑框中键入名称。 新工作簿：单击此选项，可创建一新工作簿，并在新工作簿的新工作表中存放计算结果。 汇总统计：指定输出表中生成下列统计结果，则选中此复选框。 STEP5：填写完“描述统计”对话框之后，按“确定”按扭即可。结果如图 1-2所示。 6 图 1-2 描述统计结果 结果说明：描述统计工具可生成以下统计指标，按从上到下的顺序其中包括样本的平均值、标准误差、组中值、众数、 样本标准差、样本方差、 峰度值、偏 度值、极差、最小值、最大值、样本总和、样本个数和一定显著水平下总体均值的置信区间。 （二）绘图操作 例 1-2：仍以例 1-1的数据为例，绘制城镇居民可支配收入时间序列的散点图。 STEP1：拖动鼠标选定数值区域 A2:C12，不包括数据上面的标志项。 7 STEP2：选择“插入”菜单的“图表”子菜单，进入图表向导，图 1-3所示。 STEP3：选择“图表类型”为“散点图”，然后单击“下一步”。 图 1-3 图表向导图 STEP4：确定用于制作图表的数据区。 Excel 将自动把你前面所选 定的数据区的地址放入图表数据区的内，单击下一步按钮，出现图 1-4所示对话框。 8 图 1-4 作图过程图 STEP5：填写图表标题、 X 轴坐标名称、 Y 轴坐标名称，此处不用填写，单击“下一步”。 STEP6： 选择图表输出的位置，然后单击“完成”按扭即生成 （三）数据透视表工具 数据透视表是 Excel 中强有力的数据列表分析工具。它不仅可以用来作单变量数据的次数分布或总和分析，还可以用来作双变量数据的交叉频数分析、总和分析和其它统计量的分析。 例 1-3：表 1-2列出了学生两门功课评定结果，建立学生 两门功课评定结果的交叉频数表。 表 1-2 学生两门功课评定结果 9 学号 语文 数学 学号 语文 数学 1001 优 差 1006 中 良 1002 良 中 1007 中 优 1003 中 中 1008 差 良 1004 差 中 1009 良 中 1005 差 差 操作步骤： STEP1： 输入数据，选中有数据的任一单元格，然后选择“数据”菜单的“数据透视表”子菜单，进入数据透视表向导。 STEP2： 选择“ Microsoft Excel 数据清单或数据库”为数据源。图 1-6所示，单击“下一步”。图 1-6 10 STEP3： 选择待分析的数据的区域，一般情况下 Excel 会自动根据当前单元格确定待分析数据区域，因此你只要直接单击“下一步”按扭即可。 STEP4： 确定数据透视表的结构，在此例中，要建立的是一个交叉频数表，分别按语文和数学的成绩对学生的人数进行交叉频数分析，因此可将三个按扭“学号”、“语文”、“数学”分别拖放到表格的指定部位，并且双击“求和项：学号”，将其改为记数项，结果如图 1-7所示。 图 1-7 布局对话框 STEP5：选择数据透视表的显示位置之后，单击“完成按扭”，可出现如图 1-8所示的数据透视表。 11 图 1-8 结果说明：如图 1-8的结果所示，数据透视表可以作为一个交叉频数分析工具。 完成数据透视表之后，可按需要修改数据表的显示格式。例如，如果想要把表格中的频数替换成为百分比数。可以用鼠标右击频数的任一单元格，选择“字段”子菜单，单击“选项”按扭，将“数据显示方式”替换成为“占总和的百分比”，然后单击“确定”按扭即可。按同样方式，可将数据透视表修改成为其它不同样式。 12 实验二 Excel中的二项分布工具 实验目的及要求 掌握利用 Excel的 BINOMDIST 的函数计算二项分布的概率以及累积概率。 实验内容及步骤 例 1-4：一个推销员打了六个电话，推销成功的概率是 0.3，建立推销成功次数的概率分布图表。可以按以下步骤建立推销成功次数的概率分布图表。 STEP1： 如图 1-9所示，先在 Excel之下建立好概率分布表格的框架。 图 1-9 STEP2：如图 1-10所示 ,先在 B7 至 F7 单元格分别输入概率计算公式。 13 图 1-10 STEP3：公式的拷贝。选取 B7 至 F7 单元格，拖动“填充柄”至 F13 单元格 即可完成公式的拷贝操作。结果图 1-11所示。 图 1-11 STEP4：下面开始创建二项分布图表。选取 B7 至 B13 单元格，选取“插入”菜单的“图表”子菜单。 STEP5：选择“柱状图”，然后单击“下一步”。 STEP6：单击“系列”标签，单击“分类 (X)轴标志”框，并用鼠标选取A7至 A13 单元格为图表 X 轴的轴标，然后单击“下一步”。 14 STEP7：分别键入图表名称“二项分布图”， X 轴名称“成功次数”， Y 轴名称“成功 概率”，单击“完成”按扭即可生成二项分布图表。 结果说明 : 如图 1-11 所示，利用 Excel 的 BINOMDIST 的函数可以计算出二项分布的概率以及累积概率。 BINOMDIST 函数可以带四个参数，各参数的含义分别是：实验成功的次数，实验的总次数，每次实验中成功的概率，是否计算累积概率。四个参数是一个逻辑值，如果为 TRUE，函数 BINOMDIST 返回累积分布函数，如果为 FALSE，返回概率密度函数。 另外， EXCEL还提供了其它分布的函数，如函数 CRITBINOM；函数HYPGEOMDIST； 函数 NEGBINOMDIST：函数 POISSON: 正态分布函数 NORMDIST：函数NORMSDIST：函数 NORMSINV： t 分布函数 TDIST: 有兴趣的同学可以自己研究。 15 实验三 随机抽样工具 实验目的及要求 掌握利用 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，右击选中的区域选择“复制”，再次右 16 击选中的区域，选择“选择性粘贴”，单击选项“数值” 后，点击“确定”按扭。 STEP4：选取单元格 A2 至 B11 单元格，选择“数据”菜单项下的排序子菜单。 STEP5：选取“ RANDOM”为主要关键字 ，然后点击“确定”按扭。排序结果如图 1-13 所示， A2 至 A6 单元格的样本即为随机抽取的 5 个样本。 图 1-13 17 实验四 由样本推断总体 实验目的及要求 掌握利用 Excel的几个函数，如求平均函数 AVERAGE、标准差函数 STDEV、T 分布函数 TINV 等的组合使用构造一个专门用于实现样本推断总体的Excel工作表。 实验内容及步骤 下面的例子先计算样本的平均数和 标准差，然后在一定置信水平上估计总体均值的区间范围。操作步骤： STEP1：构造工作表。如图 1-14所示，首先在各个单元格输入以下的内容，其中左边是变量名，右边是相应的计算公式。 STEP2：为表格右边的公式计算结果定义左边的变量名。选定A4:B6,A8:B8和 A10:B15 单元格 (先选择第一部分，再按住 CTRL 键选取另外两个部分 )，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“最左列”选项 ,然后点击“确定”按扭即可。 图 1-14 18 STEP3：输入样本数据，和用户指定的置信水平 0.95,如图附 -13 所示。 STEP4：为样本数据命名。选定 D1:D11 单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项 ,然后点击“确定”按扭，得到图 1-15所示的计算结果。 图 1-15 结果说明：以上例子说明如何交叉组合使用 Excel 的公式和函数，以构造出一个能实现样本推断总体有关计算的 Excel 工作表。实际上，在用Excel 进行数据统计处理之时，许多统计功能可以使用和上例类似的方法，通过组合使用 Excel 的各类统计函数和公式加以实现的。 19 实验五 假设检验 实验目的及要求 掌握利用 Excel 的正态分布函数 NORMSDIST、判断函数 IF 等，构造一张能够实现在总体方差已知情况下进行总体均值假设检验的 Excel工作表。 实验内容及步骤 例 1-6：利用 Excel 的正态分布函数 NORMSDIST、判断函数 IF 等，构造一张能够实现在总体方差已知情况下进行总体均值假设检验的 Excel 工作表。 操作步骤： STEP1：构造工作表。如图 1-16 所示，首先在各个单元格输入以下的内容，其中左边是变量名，右边是相应的计算公式。 STEP2：为表格右边的公式计算结果定义左边的变量名。选定A3:B4,A6:B8， A10:A11,A13:A15 和 A17:B19 单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“最左列”选项 ,然后点击“确定”按扭即可。 20 图 1-16 STEP3：输入样本数据，以及总体标准差、总体均值假设、置信水平数据。如 图 1-17所示。 STEP4：为样本数据命名。选定 C1:C11 单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项 ,然后点击“确定”按扭，得到如图 1-17中所 示的计算结果。 图 1-17 21 结果说明：如图 1-17所示，该例子的检验结果不论是单侧还是双侧均为拒绝 Ho 假设。所以，根据样本的计算结果，在 5%的显著水平之下 ,拒绝总体均值为 35 的假设。同时由单侧显著水平的计算结果还可以看出，在总体均值是 35 的假设之下，样本均值小于等于 31.4 的概率仅为0.020303562。 22 实验六 双样本等均值假设检验 实验目的及要求 掌握利用 Excel 数据分析中提供双样本等均值假设检验工具进行假设检验的方法，并能够解释实验结果。 实验内容及步骤 例 1-7：双样本等均值检验是在一定置信水平之下 ,在两个总体方差相等的假设之下，检验两个总体均值的差值等于指定平均差的假设是否成立的检验。假设某工厂为了比较两种装配方法的效率，分别组织了两组员工，每组 9 人，一组采用新的装配方法，另外一组采用旧的装配方法。18个员工的设备装配时间图 1-18 中表格所示。根据以下数据，是否有理由认为新的装配方法更节约时间？ 图 1-18 操作步骤： STEP1：选择“工具”菜单的“数据分析”子菜单，双击“ t-检验 : 双样本等方差假设”选项，则弹出图 1-19 所示对话框。 23 图 1-19 STEP2： 分别填写变量 1 的区域： $B$1:$B$10，变量 2 的区域：$D$1:$D$10，由于我们进行的是等均值的检验，填写假设平均差为 0，由于数据的首行包括标志项选择标志选项，所以选择“标志”选项，再填写显著水平为 0.05,然后点击“确定”按扭。则可以得到图 1-20所示的结果。 24 图 1-20 结果分析：如图 1-20中所示，表中分别给出了两组装配时间的平均值、方差和样本个数。其中，合并方差是样本方差加权之后的平均值 ,Df 是假设检验的自由度它等 于样本总个数减 2， t 统计量是两个样本差值减去假设平均差之后再除于标准误差的结果，“ P(T1 之后，都在区间 (-0.438,0.438)之间，因此可以认为自相关函数在 K1 之后截尾，因此我们选用 AR(1)模型进行数据拟合。复制 C2:C20 的数据，将之以值复制的形式复制到 D3:D21 的单元格，并在D1 中填入标志项“ Z(-1)”。选择“工具”菜单的“数据分析”子菜单，双击“回归”选项，弹出回归分析对话框。按图附 -40 所示的方式填写对话框。然后单击“确定”按扭，即可得到 AR(1)模型的估计结果。 STEP5：按以上操作步骤，可得到图 1-41 所示 AR(1)模型。 42 图 1-41 结果分析：零均值化模型的估计结果是 Z=1.06284*Z(-1),还原成上证指数，最终的时间序列模型是：上证指数估计值 -上证指数的平均值=1.06284(上一天上证指数 -上证指数平均值 )。 Excel 与数据统计分析 统计计算与统计分析强调与计算机密切结合， Excel与数据统计分析旨在提高学生计算机的综合运用能力，用统计方法分析问题、解决问题而编写的。根据教材内容，也可以选择使用 SPSS、 QSTAT、 Evievs、 SAS、 MINITAB 等统计软件。 第三章 统计整理 3.1 计量数据的频数表与直方图 例 3.1 (3-1) 一、指定接受区域直方图 在应用此工具前，用户应先决定分布区间。否则， Excel将用一个大约等于数据集中某数值的平方根作区间，在数据集的最大值与最小值之间用等宽间隔。如果用户自己定义区间，可用 2、 5或 10的倍数，这样易于分析。 对于工资数据，最小值是 100，最大值是 298。一个紧凑的直方图可从区间 100开始，区间宽度用 10，最后一区间为 300结束，需要 21个区间。这里所用的方法在两 43 端加了一个空区间，在低端是区间 “ 100或 小于 100” ，高端是区间 “ 大于 300” 。 参考图 3.3，利用下面这些步骤可得到频率分布和直方图： 1为了方便，将原始数据拷贝到新工作表“指定频数直方图”中。 2在 B1单元中输入“组距”作为一标记，在 B2单元中输入 100， B3单元中输入110，选取 B2:B3，向下拖动所选区域右下角的 +到 B22单元。 3按下列步骤使用“直方图”分析工具： （ 1）选择 工具 菜单之 数据分析 选项 , 在 分析工具 框中“直方图”。如图 4所示。 图 3.1 数据分析工具之直方图对话框 1) 输入 输入区域： A1:A51 接受区域： B1:B22 (这些区间断点或界限必须按升序排列 ) 选择标志 2) 输出选项 输出区域 : C1 选定图表输出 (2)单击 确定 ， Excel将计算出结果显示在 输出区域 中。 44 图 3.2 数据分析工具之直方图对话框 Excel将把频率分布和直方图放在工作表中，如图 3.3所示，输出表的 C和 D列中包括开始指定的界限。这些界限实际上是每一区间的上限，也就是说，界限实际上是边界。 图 3.3 频数分布与直方图 为了使图表更像传统的直方图和更易于理解，可双击图表并对 它做如下修改： 1 图例 ：因为只有一个系列的数据显示在图表中，所以不需要图例。单击图例 (位于图表右侧的 “频率 ”)并按 Delete键。 2图表区：绘图区是以 X和 Y轴为边界的矩形区域。通过在柱形上面单击可选取绘图区，单击鼠标右键并选择绘图区格式，将边框改为 无 并将 区域 改为 无 ，单击 确定 。 45 3条宽：在传统的直方图中，柱形是彼此相连接而不是分开的。选择某个柱形，单击鼠标右键，选择 数据系列格式 ，并单击 选择 标签，将 间距宽度 从 150改为 0，单击 确定 。 4 X轴标志：选取 x轴，单击鼠标右键，选择 坐标轴格式 ，单击 对齐 标签 ，将 方向从自动改为水平文本，在这种设置下，即使图表已重置尺寸， x轴标记也会变为水平的，单击 确定 。最后的直方图 4.6 图 3.4 修改后的直方图 二、不指定接受区域直方图 在进行探索性分析时，为了方便，通常不指定接受区域作直方图，步骤如下： （ 1）选择 工具 菜单之 数据分析 选项 , 在 分析工具 框中“直方图”。如图 4所示。 1) 输入 输入区域： A1:A51 接受区域： (该处为空 ) 选择标志 2) 输出选项 输出区域 : B1 选定图表输出 直方图01234567100 120 140 160 180 200 220 240 260 280 300组距频率 46 (2)单击 确定 ，得结果。 (3)按 前面方法对直方图进行进一步修饰即得图 3.5 图 3.5 修改后的直方图 3.2 计数数据的透视表与条图 例 3.2（ 3-3）数据见图 步骤如下： （ 1）选择 数据 菜单之 数据透视表和图表报告 选项 , 如图 4所示。 47 (2)选择数据源区域 （ 3）选定数据透视表位置，完成 （ 4）将“性别”作为行字段拖至 G 列，并将“性别”作为数据拖至数据项处，得下表结果 48 同理可得“文化程度”的透视表 此时如点击图形按钮，立即得到如下的透视图 49 （ 5）将“性别”作为行字段拖至行字段处，并将“文化 程度”作为列字段拖至列字段处，将“性别”或“文化程度”作为列字段拖至数据字段处得下表结果 第四章 总量指标和相对指标 例 4.1 (4-13) 计算步骤： （ 1）计算各厂计划完成 % E3=D3/C3*100, （ 2） 2000 年实际产量为 1999 年的 % F3=D3/B3*100, 第五章 平均指标 5.1 简单平均数 例 5.1某组有学生 10人统计课考试成绩为 65， 82， 76， 80， 82， 86， 84， 88， 95， 50 98分，试求其平均指标。 平均数的计算步骤如下： （ 1） 将数据输入到 A列，根据 Excel提供的公式计算各种平均数 （ 2）用 Ctrl+ 可切换到下面的结果： 5.2 加权平均数 例 5.2（ 5-1）原始数据见下图 A-D列，其中 A、 B列放日产量的下限和上限 平均数的计算步骤如下： （ 1）计算日产量的组中值 E3=(A3+B3)/2, 51 （ 2）计算每个组段的总产量 F3=C3*E3, G3=D3*E3, （ 3）计算每月的总产量 F8=SUM(F3:F7), G8=Sum(G3:G7), （ 4）计算平均数公式如下： 均数 F9=F8/C8 G9=G8/D8 众数 F10=A4+(C4-C3)/(C4-C3+C4-C5)*10 G10=A6+(D6-D5)/(D6-D5+D6-D7)*10 中位数 F11=A4+(C8/2-A4)/C4*10 G11=A5+(D8/2-C5)/D5*10 第六章 变异度指标 6.1 简单变异度指标 例 6.1（ 6-1） 变异度指标的计算步骤如下： （ 1）将甲乙两组数据输入到 A, B列，根据 Excel提供的公式计算各种变异度指标 （ 2）用 Ctrl+ 可切换到下面的公式： 6.2 加权变异度指标 例 6.2（ 6-2）甲品种的原始数据见下图 B-C列，乙品种的原始数据见下图 G-H列 52 下面以甲品种的数据计算为例： （ 1）计算单产值 D4=C4/B4, （ 2）计算单产均值 D9=C9/B9 （ 3）计算次数 X离差平方 E4=B4*(D4-$D$9)2 , 并求和 E9=SUM(E4:E8) （ 4）计算标准差： D11=SQRT(E9/B9)=68.91 （ 5）计算变异系数： D12=D11/D9*100=6.9% 同理可得乙品种的标准差为 162.71, 变异系数为 16.30% 第七章 抽样调查 例 7.1 (7-5) 期望 求 E(X)的公式 B4=SUM(B1:F1)*(B2:F2), 由于此处用到数组乘积求和，所以要得到结果，需用 Ctrl+Shift+Enter组合键。 例 7.2 (7-6) 二项分布 计算公式： P(5=9)=1-P(x=5)=1-P(x=4) Excel 计算结果： Excel 计算公式： 54 （ Ctrl+ 互换） 例 7.4 (7-9) 超几何分布 例 7.5 (7-10) 正态分布 其中 F2=1-D2, D4=D3-D2 第八章 假设检验 8.1 大样本 使用正态分布的假设检验 例 8.1 商店经理想为商店的持信用卡的顾客建一新的付款系统，经过详细的经济分析，她判定如果新系统每月平均利润低于 70元的话就不能有效地使用资金。于是随机抽取了 200个 月的利润，其平均月利润为 66元。如果 0.05，有无充分的证据说明新系统不是一项节省资金的系统 ?假设总体的标准偏差为 30元。 图 8.1 正态假设检验的标记和公式 -已知均值标准差计算公式 55 上图所示的工作表可用于正态分布平均值的左尾、右尾和双尾假设检验。检验结果包括基于 判决法和 P值报告法。输入样本大小、样本平均值和标准偏差作为值、公式或引用，指定假设的平均值 (Mean)和显著水平 作为值。 下面各步骤描述了如何建立该工作表： （ 1）打开一新工作表并输入 B列所示标记。 （ 2） 要在 C列的公式使用 B列中的名称，选取单元 B4:C12，从 插入 菜单中选择名称 指定 ，在指定名称对话框中复选名称创建于 最左列 ，单击 确定 。 （ 3）输入 C列所示的公式 (按图所示键入公式或通过单击适当的已命名的单元插入函数来建立公式 )。 （ 4） 要得图 8.1的 A列所示的外观， 按 Ctrl+。 因为经理想知道平均月利润是否小于 70元，所以备择假设为 Hd： Mean 70，零假设为 Ho： Mean 70或简单地为 Ho： Mean 70。由于数据已经总结过了，可直接在工作表单元中输入样本大小 n、样本平均值、总体的标准偏差 、假设总体平均值和显著水平。 56 图 8.2 正态假设检验 结论 ：得到 Z小于 -1.886的概率是 0.0297。如果零假设为真 (每月平均利润为 70元 )，得到样本平均值为 66元或小于它的概率约为 3，即有充分的证据说明新系统是一项节省资金的系统。 8.2 小样本 使 t分布的假设检验 家保险公司用代理的方式支付其客户，赔偿假定每年的平均代理赔偿费用为 32000元，如果平均支付费用与计划不同，就需要对计划进行修改。对一个有 36个代理的样本，上一年的平均支付费用为 27500元，标准偏差为 8400元，如果整个公司的平 均支付变化与该样本的情况不同，那么可用管理计划来修改赔偿计划。根据这一结果的 P值，这一样本能充分说明平均值变化了吗？ 本例已知均值标准差，下面各步骤描述了如何建立计算工作表： 1打开一新工作表，输入 A列所示标记。 2要在 C列的公式使用 B列中的名称，选取单元 B4:C13，从 插入 菜单中选择 名称 指定 ，在指定名称对话框中复选名称创建于 最左列 ，单击 确定 。 图 8.3 已知均值标准差计算公式 -t假设检验的标记和公式 57 因为经理想知道平均支付是否发生了变化 (不用指出变化的方向 )，所以备择假设为 H1： Mean 32000，零假设为 Ho： Mean 32000。由于数据已经总结过了，可以直接向工作表的单元中输入样本大小 n、样本平均值、总体的标准偏差和假设总体的平均值。尽管例中未指明显著水平可输入为 0.05。 结论：由于 P=0.00280.05，所以，有足够的理由拒绝在显著水平为 5 (双尾检验 )时的零假设，可得出如下结论：平均支付值不等于 32000，明确说明平均值改变了。 58 图 8.4 t假设检验 例 8.3（ 已知原始数据）一家制造商生产钢棒，为了提高质量，如果某新的生产工艺生产出的钢棒的断裂强度大于现有平均 断裂强度标准的话，公司将采用该工艺。当肪钢棒的平均断裂强度标准是 500公斤。对新工艺生产的钢捧进行抽样， 12件棒材的断裂强度如下： 502， 496， 510， 508， 506， 498， 512， 497， 515， 503， 510和 506，假设断裂强度的分布比较近似于正态分布，将样本数据画图，所画图形能表明平均断裂强度有所提高吗 ? 图 8.5显示了假设检验所需的数据。 因为经理想检查是否有提高，备择假设为H1： Mean 500，所以用右尾检验比较合适。零假设为 Ho： Mean 500，或简单地 Ho：Mean=500。如例 8.2所述， D2： D13单元已命名为 Data，单元 B5:B7包含了公式COUNT(Data), AVERAGE(Data)和 STDEV(Data)。尽管例 5.5未指定一显著水平 , 在 B10单元中入了显著水平为 0.05。包含了左尾检验结果的 15到 18行被隐藏。 59 图 8.5 小样本 t假设检验公式 结论： P=0.01310.05，说明有充分证据来拒绝零假设。可得出如下结论：新工艺在统计上可带来平均断裂强度的显著提高。 图 8.6 小样本 t假设检验结果 第九章 相关与回归 简单线性相关分析 例 9.1 （ 9-1） 60 1 Excel进行相关分析： （ 1）输数据 : 将数据输入 A1:C9单元格。 （ 2）绘制散点图 : 图 9.1 简单相关系数及散点图 3. 计算相关系数 (1) 选择 工具 菜单之 数据分析 选项 , 在 分析工具 框中“相关系数”。 相关系数对话框将显示为图 9.2所示，它带输入输出的提示。 图 9.2 相关系数对话框 1) 输入 输入区域： B1:C9 分组方式：逐列 选择标志位于第一行 2) 输出选项 61 输出区域 : A13 (2)单击 确定 ， Excel将计算出结果显示在 输出区域 中 。 4. 相关系数假设检验 (1)在单元格 F14中输入公式 =B15/SQRT(1-B152)/(8-2) 计算得相关系数的 t值为 49.46 (2)在单元格 F15中输入公式 =TDIST(ABS(F14),B-2,2) 计算得 p=0.0001 (3) 结论 : 由于 r=-0.9689, 且 p0.05, 所以 , 在 0.05水平上拒绝原假设 , 认为产品产量与单位成本 间有负的线性相关关系 9.2 简单 回归分析 上面的简单相关分析只是说明两变量之间的线性关系密切的程度，如果要建立它们之间线性依存的关系式，就需用回归分析。 可按下列步骤使用“回归”分析工具： 1. 输数据 : 将数据输入 A1:C9单元格。 2. 回归分析 : (1) 选择 工具 菜单之 数据分析 选项 , 在 分析工具 框中“回归”。回归对话框将显示为图 9.3所示， 62 图 9.3 回归分析对话框 1) 输入 Y值输入区域： C1:C9 X值输入区域： B1:B9 标志 : 选择 常数为零 : 只有当用户想强制使回归线通过原点 (0,0)时才选此框 置信度 : Excel自动包括了回归系数的 95置信区间。要使用其他置信区间 , 选择该框并在 Confidence Levet框中输入 置信水平 2) 输出选项 输出区域 : D1 3) 残差 残差 (R)：选择此框可得到预测值和残差 (Residual)。 残差图 (D): 选择此框可得到残差和每一 x值的图表。 标准残差 (T)：选择此框可得到标准化的残差 ,每一 残差 被估计标准误差除 )。这一输出可使曲线较容易分层。 线性拟合图 (I)：选择此框可得到一含有 y输入数据和拟合的 y值的散点图。 4) 正态概率图 : 绘制 因变量的 正态概率图 63 (2)单击 确定 ， Excel将计算出结果显示在 输出区域 中。 图 9.4 回归分析结果 3. 回归解释 拟合回归线的截距和斜率放在图 9.4的总结输出中标记有 “Coeffients的左下部。截距系数 77.30769是线性回归方程中的常数项 ， x系数 -0.80769是斜率。回归方程是 ： y 77.30769-0.80769 * x 图 9.5 残差及拟合线 在图 9.5所示的残 差 输出中，预测 y，有时又称拟合值，是用这个回归方程计算的单位成本的估计 值 。残差是实际值和拟合值之间的差值。 回答 “拟合关系怎么样 ”问题的最通用的四个方法是标准误差， R2， t统计值和方差分析。标准误差 0.83205显示在图 9.4的单元 E7中。作为残数的标准偏差，它衡量单位成本在回归线周围的分散情况，标准误差通常称为估计标准误差。 64 R2（ R Square），如图 9.4的单元 E5所示，衡量用回归线解释的因变量变化的比例。这一比例必击是 0和 1之间的一个数据，经常以百分数表示。这里，约有的 94的单位成本的变化是在线性方程中用产品产量做为预测因子来解释的。单元 E6显示的 Adjusted R square在用附加解释变量把此模型和其他模型比较时很有用。 第十章 时间序列分析指标 例 10.1 (10-2) 1995-2000 的销售额见下图 B 列，则其速度分析指标计算如下： Excel 计算公式如下 : (Ctrl+ 切换 ) 例 10.2 (10-3) 65 计算步骤： （ 1）各季平均每月总产值计算公式 说明 单元格 公式 一季 B16 = AVERAGE(B2:B4) 二季 B17 = AVERAGE(B5:B7) 三季 B18 = AVERAGE(B8:B10) 四季 B19 = AVERAGE(B11:B13) 全年 B20 = AVERAGE(B2:B13) （ 2）全年平均职工人数： C16 = (C2/2+C3+C4+C5+C6+C7+C8+C9+C10+C11+C12+C13+C14/2)/13 （ 3）月平均劳动生产率： C17 =B20/C16*10000 年平均劳动生产率： C18 =SUM(B2:B13)/C16*10000 （ 4）全年 职工构成指标： C19 = (D2/2+D3+D4+D5+D6+D7+D8+D9+D10+D11+D12+D13+D14/2)/ (C2/2+C3+C4+C5+C6+C7+C8+C9+C10+C11+C12+C13+C14/2)*100 例 10.3 (10-5) 66 计算步骤： （ 1）计算每年的增长速度 A2=1+A