Excel在统计中的应用与数据统计分析.pdf

《Excel在统计中的应用与数据统计分析.pdf》由会员分享，可在线阅读，更多相关《Excel在统计中的应用与数据统计分析.pdf（49页珍藏版）》请在三一文库上搜索。

1、Excel在统计中的应用 Excel与数据统计分析 一、实验说明 (一中文 Excel 简介 Microsoft Excel 是美国微软公司开发的Windows 环境下的电子表格系统 ,它是 目前应用最为广泛的办公室表格处理软件之一。自Excel 诞生以来 Excel 历经了 Excel5.0、Excel95、Excel97 和 Excel2000 等不同版本。随着版本的不断提高,Excel 软件的强大的数据处理功能和操作的简易性逐渐走入了一个新的境界,整个系统的 智能化程度也不断提高 ,它甚至可以在某些方面判断用户的下一步操作,使用户操作 大为简化。 Excel 具有强有力的数据库管理功能、

2、丰富的宏命令和函数、强有力的 决策支持工具、图表绘制功能、宏语言功能、样式功能、对象连接和嵌入功能、连 接和合并功能 ,并且操作简捷 ,这些特性 ,已使 Excel 成为现代办公软件重要的组成部 分。 由于大家对 Excel 的常用办公功能都比较熟悉,本实验重点介绍Excel在统计分 析中的应用。 (二实验目的与要求 本实验重点介绍 Excel 在统计分析中的应用 ,包括 Excel在描述统计中的应用以 及 Excel 在推断统计中的应用 ,要求学生熟练掌握运用Excel 进行统计分析的方法 ,并能够对分析结果进行解释。 二、实验 实验一 Excel 在描述统计中的应用 实验目的及要求 要求学

3、生掌握运用Excel 进行描述统计分析、绘制各种图表和运用数据透视表 工具的技术。 实验内容及步骤 (一描述统计分析 例 1-1:表 1-1是 1978-2005年我国城镇居民可支配收入数据,试求城镇居民可支 配收入时间序列的基本统计量。 表 1-1 1978-2005年我国城镇居民可支配收入(元 STEP1:用鼠标点击工作表中待分析数据的任一单元格。STEP2:选择“ 工具” 菜 单的“ 数据分析 ” 子菜单。 STEP3:用鼠标双击数据分析工具中的“ 描述统计 ” 选项。 STEP4:出现“ 描述统计 ” 对话框 ,如图 1-1所示。 图 1-1 对话框内各选项的含义如下: 输入区域 :在

4、此输入待分析数据区域的单元格范围。 分组方式 :如果需要指出输入区域中的数据是按行还是按列排列,则单击 “ 行” 或 “ 列” 。 标志位于第一行 /列:如果输入区域的第一行中包含标志项(变量名,则选中 “ 标志 位于第一行 ” 复选框 ;如果输入区域的第一列中包含标志项,则选中 “ 标志位于第一 列” 。 复选框 :如果输入区域没有标志项,则不选任何复选框 ,Excel 将在输出表中生成 适宜的数据标志。 均值置信度 :若需要输出由样本均值推断总体均值的置信区间,则选中此复选框 , 然后在右侧的编辑框中 ,输入所要使用的置信度。例如,置 信度 95%可计算出的总体样本均值置信区间为10,则表

5、示 :在 5%的显著水平下 总体均值的置信区间为 ( X -10, X +10。 第 K 个最大 /小值:如果需要在输出表的某一行中包含每个区域 的数据的第 k 个最大 /小值,则选中此复选框。然后在右侧的编辑框中,输入 k 的 数值。 输出区域 :在此框中可填写输出结果表左上角单元格地址,用于控 制输出结果的存放位置。 新工作表 :单击此选项 ,可在当前工作簿中插入新工作表,并由新工作表的 A1 单 元格开始存放计算结果。如果需要给新工作表命名,则在右侧编辑框中键入名称。 新工作簿 :单击此选项 ,可创建一新工作簿 ,并在新工作簿的新工作表中存放计算 结果。 汇总统计 :指定输出表中生成下列

6、统计结果,则选中此复选框。 STEP5:填写完 “ 描述统计 ” 对话框之后 ,按“ 确定” 按扭即可。结果如图1-2所示。 图 1-2 描述统计结果 结果说明 :描述统计工具可生成以下统计指标,按从上到下的顺序其中包括样本 的平均值、标准误差、组中值、众数、样本标准差、样本方差、峰度值、偏 度值、极差、最小值、最大值、样本总和、样本个数和一定显著水平下总体均 值的置信区间。 (二绘图操作 例 1-2:仍以例 1-1的数据为例 ,绘制城镇居民可支配收入时间序列的散点图。 STEP1:拖动鼠标选定数值区域A2:C12,不包括数据上面的标志项。 STEP2:选择“ 插入” 菜单的 “ 图表” 子菜

7、单 ,进入图表向导 ,图 1-3 所示。 STEP3:选择“ 图表类型 ” 为“ 散点图 ”,然后单击 “ 下一步 ” 。 图 1-3 图表向导图 STEP4:确定用于制作图表的数据区。Excel 将自动把你前面所选定的数据区的 地址放入图表数据区的内,单击下一步按钮 ,出现图 1-4 所示对话框。 图 1-4 作图过程图 STEP5:填写图表标题、 X 轴坐标名称、 Y 轴坐标名称 ,此处不用填写 ,单击“ 下一 步” 。 STEP6:选择图表输出的位置 ,然后单击 “ 完成” 按扭即生成 (三数据透视表工具 数据透视表是 Excel 中强有力的数据列表分析工具。它不仅可以用来作单变量 数据

8、的次数分布或总和分析,还可以用来作双变量数据的交叉频数分析、总和分析 和其它统计量的分析。 例 1-3:表 1-2列出了学生两门功课评定结果,建立学生两门功课评定结果的交叉 频数表。 表 1-2 学生两门功课评定结果 操作步骤 : STEP1:输入数据 ,选中有数据的任一单元格 ,然后选择 “ 数据” 菜单的 “ 数据透视 表” 子菜单 ,进入数据透视表向导。 STEP2:选择“Microsoft Excel 数据清单或数据库 ” 为数据源。图 1-6 所示,单击 “ 下一步 ” 。 图 1-6 STEP3:选择待分析的数据的区域 ,一般情况下 Excel 会自动根据当前单元格确 定待分析数据

9、区域 ,因此你只要直接单击 “ 下一步 ” 按扭即可。 STEP4:确定数据透视 表的结构 ,在此例中 ,要建立的是一个交叉频数表,分别按语文和数学的成绩对学生的 人数进行交叉频数分析 ,因此可将三个按扭 “ 学号” 、“ 语文” 、“ 数学” 分别拖放到表格 的指定部位 ,并且双击 “ 求和项 :学号”,将其改为记数项 ,结果如图 1-7所示。 图 1-7 布局对话框 STEP5:选择数据透视表的显示位置之后,单击“ 完成按扭 ”,可出现如图 1-8 所示 的数据透视表。 图 1-8 结果说明 :如图 1-8 的结果所示 ,数据透视表可以作为一个交叉频数分析工具。 完成数据透视表之后 ,可按

10、需要修改数据表的显示格式。例如,如果想要把表格中的 频数替换成为百分比数。可以用鼠标右击频数的任一单元格,选择“ 字段” 子菜单 ,单 击“ 选项” 按扭,将“ 数据显示方式 ” 替换成为 “ 占总和的百分比 ”,然后单击 “ 确定” 按扭 即可。按同样方式 ,可将数据透视表修改成为其它不同样式。 实验二 Excel中的二项分布工具 实验目的及要求 掌握利用 Excel 的 BINOMDIST 的函数计算二项分布的概率以及累积概率。 实验内容及步骤 例 1-4:一个推销员打了六个电话 ,推销成功的概率是0.3,建立推销成功次数的概 率分布图表。可以按以下步骤建立推销成功次数的概率分布图表。 S

11、TEP1:如图 1-9 所示,先在 Excel之下建立好概率分布表格的框架。 图 1-9 STEP2:如图 1-10 所示,先在 B7 至 F7 单元格分别输入概率计算公式。 图 1-10 STEP3:公式的拷贝。选取B7 至 F7 单元格 ,拖动“ 填充柄 ” 至 F13 单元格 即可完成公式的拷贝操作。结果图1-11 所示。 图 1-11 STEP4:下面开始创建二项分布图表。选取B7 至 B13 单元格 ,选取“ 插入” 菜单 的“ 图表” 子菜单。 STEP5:选择“ 柱状图 ”,然后单击 “ 下一步 ” 。 STEP6:单击“ 系列” 标签,单击“ 分类(X 轴标志 ” 框,并用鼠标

12、选取 A7 至 A13 单元 格为图表 X 轴的轴标 ,然后单击 “ 下一步” 。 STEP7:分别键入图表名称 “ 二项分布图 ”,X 轴名称 “ 成功次数 ”,Y 轴名称 “ 成功概 率”,单击“ 完成” 按扭即可生成二项分布图表。 结果说明 : 如图 1-11 所示,利用 Excel 的 BINOMDIST 的函数可以计算出二项分 布的概率以及累积概率。BINOMDIST 函数可以带四个参数 ,各参数的含义分别是 : 实验成功的次数 ,实验的总次数 ,每次实验中成功的概率 ,是否计算累积概率。 四个参数是一个逻辑值 ,如果为 TRUE,函数 BINOMDIST 返回累积分布函数 ,如 果

13、为 FALSE,返回概率密度函数。 另外,EXCEL 还提供了其它分布的函数,如函数 CRITBINOM; 函数 HYPGEOMDIST; 函数 NEGBINOMDIST: 函数 POISSON: 正态分布函数 NORMDIST: 函数 NORMSDIST:函数 NORMSINV:t 分布函数 TDIST: 有兴趣的同学可以自己研究。 实验三随机抽样工具 实验目的及要求 掌握利用 Excel 的 BINOMDIST 的函数计算二项分布的概率以及累积概率。 实验内容及步骤 Excel 中的 Rand(函数可以返回大于等于 0 小于 1 的均匀分布随机数 ,Rand(不带 任何参数运行 ,每次计算

14、时时都将返回一个新的数值。RAND( 函数可以被用来作为 不重复抽样调查的工具。 例 1-5:如图 1-12所示有 10 个象征性的样本数据 ,欲从中随机抽取 5 个数据可按 如下步骤操作 : 图 1-12 STEP1:选择 B2 单元格 ,输入公式 “=RAND( ” 并回车。 STEP2:拖动 B2 单元格右下角的填充柄至B11 单元格 ,并在 B1 单元格输入标题 “RANDOM”。 STEP3:选取单元格 B2 至 B11,右击选中的区域选择 “ 复制”,再次右 击选中的区域 ,选择“ 选择性粘贴 ”,单击选项 “ 数值” 后,点击“ 确定” 按扭。 STEP4:选取单元格 A2 至

15、B11 单元格 ,选择“ 数据” 菜单项下的排序子菜单。 STEP5:选取“RANDOM”为主要关键字 ,然后点击 “ 确定” 按扭。排序结果如图1- 13 所示,A2 至 A6 单元格的样本即为随机抽取的5 个样本。 图 1-13 实验四由样本推断总体 实验目的及要求 掌握利用 Excel 的几个函数 ,如求平均函数 AVERAGE 、标准差函数 STDEV、 T 分布函数 TINV 等的组合使用构造一个专门用于实现样本推断总体的Excel工作 表。 实验内容及步骤 下面的例子先计算样本的平均数和标准差,然后在一定置信水平上估计总体均 值的区间范围。操作步骤: STEP1:构造工作表。如图1

16、-14所示,首先在各个单元格输入以下的内容,其中左 边是变量名 ,右边是相应的计算公式。 STEP2:为表格右边的公式计算结果定义左边的变量名。选定A4:B6,A8:B8 和 A10:B15 单元格 (先选择第一部分 ,再按住 CTRL 键选取另外两个部分 ,选择“ 插入” 菜 单的“ 名称” 子菜单的 “ 指定” 选项,用鼠标点击 “ 最左列 ” 选项,然后点击 “ 确定” 按扭即 可。 图 1-14 STEP3:输入样本数据 ,和用户指定的置信水平0.95,如图附 -13 所示。 STEP4:为样本数据命名。选定D1:D11 单元格 ,选择“ 插入” 菜单的 “ 名称” 子菜单 的“ 指定

17、” 选项,用鼠标点击 “ 首行” 选项,然后点击 “ 确定” 按扭,得到图 1-15所示的计算 结果。 图 1-15 结果说明 :以上例子说明如何交叉组合使用Excel 的公式和函数 ,以构造出一个 能实现样本推断总体有关计算的Excel 工作表。实际上 ,在用 Excel 进行数据统计处 理之时 ,许多统计功能可以使用和上例类似的方法,通过组合使用 Excel 的各类统计 函数和公式加以实现的。 实验五假设检验 实验目的及要求 掌握利用 Excel 的正态分布函数 NORMSDIST 、判断函数 IF 等,构造一张能够 实现在总体方差已知情况下进行总体均值假设检验的Excel 工作表。 实验

18、内容及步骤 例 1-6:利用 Excel 的正态分布函数 NORMSDIST 、判断函数 IF 等,构造一张能 够实现在总体方差已知情况下进行总体均值假设检验的Excel 工作表。 操作步骤 : STEP1:构造工作表。如图1-16 所示 ,首先在各个单元格输入以下的内容,其中左 边是变量名 ,右边是相应的计算公式。 STEP2:为表格右边的公式计算结果定义左边的变量名。选定 A3:B4,A6:B8,A10:A11,A13:A15 和 A17:B19 单元格 ,选择“ 插入” 菜单的 “ 名称” 子菜单 的“ 指定” 选项,用鼠标点击 “ 最左列 ” 选项,然后点击 “ 确定” 按扭即可。 图

19、 1-16 STEP3:输入样本数据 ,以及总体标准差、总体均值假设、置信水平数据。如图 1-17所示。 STEP4:为样本数据命名。选定C1:C11 单元格 ,选择“ 插入” 菜单的 “ 名称” 子菜单 的“ 指定” 选项,用鼠标点击 “ 首行” 选项,然后点击 “ 确定” 按扭,得到如图 1-17中所示的 计算结果。 图 1-17 结果说明 :如图 1-17 所示,该例子的检验结果不论是单侧还是双侧均为拒绝Ho 假设。所以 ,根据样本的计算结果 ,在 5%的显著水平之下 ,拒绝总体均值为 35 的假 设。同时由单侧显著水平的计算结果还可以看出,在总体均值是 35 的假设之下 ,样本 均值小

20、于等于 31.4 的概率仅为 0.020303562 。 实验六双样本等均值假设检验 实验目的及要求 掌握利用 Excel 数据分析中提供双样本等均值假设检验工具进行假设检验的方 法,并能够解释实验结果。 实验内容及步骤 例 1-7:双样本等均值检验是在一定置信水平之下,在两个总体方差相等的假设 之下,检验两个总体均值的差值等于指定平均差的假设是否成立的检验。假设某工 厂为了比较两种装配方法的效率,分别组织了两组员工 ,每组 9 人,一组采用新的装配 方法,另外一组采用旧的装配方法。18 个员工的设备装配时间图1-18 中表格所示。 根据以下数据 ,是否有理由认为新的装配方法更节约时间? 图

21、1-18 操作步骤 : STEP1:选择“ 工具” 菜单的 “ 数据分析 ” 子菜单 ,双击“t-检验: 双样本等方差假设 ” 选项,则弹出图 1-19 所示对话框。 图 1-19 STEP2:分别填写变量 1 的区域 :$B$1:$B$10,变量 2 的区域 : $D$1:$D$10,由于我 们进行的是等均值的检验,填写假设平均差为0,由于数据的首行包括标志项选择标 志选项 ,所以选择 “ 标志” 选项,再填写显著水平 为 0.05,然后点击 “ 确定” 按扭。则可 以得到图 1-20 所示的结果。 图 1-20 结果分析 :如图 1-20 中所示 ,表中分别给出了两组装配时间的平均值、方差

22、和样 本个数。其中 ,合并方差是样本方差加权之后的平均值,Df 是假设检验的自由度它等 于样本总个数减 2,t 统计量是两个样本差值减去假设平均差之后再除于标准误差的 结果, “P(T1 之后,都在区间 (-0.438,0.438之间,因此可以认为自相关函数在K1 之后截尾 , 因此我们选用 AR(1 模型进行数据拟合。复制C2:C20 的数据 ,将之以值复制的形式 复制到 D3:D21 的单元格 ,并在 D1 中填入标志项 “Z( -1” 。选择 “ 工具” 菜单的 “ 数据分 析” 子菜单 ,双击“ 回归” 选项,弹出回归分析对话框。按图附-40 所示的方式填写对话 框。然后单击 “ 确定

23、” 按扭 ,即可得到 AR(1 模型的估计结果。 STEP5:按以上操作步骤 , 可得到图 1-41 所示 AR(1 模型。 图 1-41 结果分析 :零均值化模型的估计结果是Z=1.06284*Z(-1,还原成上证指数 ,最终的 时间序列模型是 :上证指数估计值 -上证指数的平均值 =1.06284(上一天上证指数 -上 证指数平均值。 Excel与数据统计分析 统计计算与统计分析强调与计算机密切结合,Excel 与数据统计分析旨在提 高学生计算机的综合运用能力,用统计方法分析问题、解决问题而编写的。根据教 材内容 ,也可以选择使用 SPSS 、QSTAT、Evievs、SAS、MINITA

24、B 等统计软件。 第三章统计整理 3.1 计量数据的频数表与直方图 例 3.1 (3-1 一、指定接受区域直方图 在应用此工具前 ,用户应先决定分布区间。否则,Excel 将用一个大约等于数据集 中某数值的平方根作区间,在数据集的最大值与最小值之间用等宽间隔。如果用户 自己定义区间 ,可用 2、5或 10的倍数,这样易于分析。 对于工资数据 ,最小值是 100,最大值是 298。一个紧凑的直方图可从区间100开 始,区间宽度用 10,最后一区间为 300结束,需要 21个区间。这里所用的方法在两 端加了一个空区间 ,在低端是区间 “100 或小于 100”,高端是区间 “ 大于 300” 。

25、参考图 3.3,利用下面这些步骤可得到频率分布和直方图: 1.为了方便 ,将原始数据拷贝到新工作表“ 指定频数直方图 ” 中。 2.在 B1单元中输入 “ 组距” 作为一标记 ,在 B2 单元中输入 100,B3单元中输入 110,选取 B2:B3,向下拖动所选区域右下角的+到 B22 单元。 3.按下列步骤使用 “ 直方图 ” 分析工具 : (1, 在分析工具框中 “ 直方图 ” 。如图 4 所示。 图 3.1 数据分析工具之直方图对话框 1 输入 输入区域 :A1:A51 接受区域 :B1:B22 (这些区间断点或界限必须按升序排列 选择标志 2 输出选项 输出区域 : C1 选定图表输出

26、 (2Excel将计算出结果显示在输出区域中。 图 3.2 数据分析工具之直方图对话框 Excel将把频率分布和直方图放在工作表中,如图 3.3所示,输出表的 C 和 D 列中 包括开始指定的界限。这些界限实际上是每一区间的上限,也就是说 ,界限实际上是 边界。 图 3.3 频数分布与直方图 为了使图表更像传统的直方图和更易于理解,可双击图表并对它做如下修改: 1.图例:因为只有一个系列的数据显示在图表中,所以不需要图例。单击图例(位 于图表右侧的 “ 频率” 并按 Delete键。 2.图表区 :绘图区是以 X 和 Y 轴为边界的矩形区域。通过在柱形上面单击可选 取绘图区 ,单击鼠标右键并选

27、择绘图区格式,将边框改为无并将区域改为无,单击确 定。 3.条宽:在传统的直方图中 ,柱形是彼此相连接而不是分开的。选择某个柱形,单 击鼠标右键 ,选择数据系列格式 ,并单击选择标签 ,将间距宽度从 150%改为 0 %, 4.X 轴标志 :选取 x 轴,单击鼠标右键 ,选择坐标轴格式 ,单击对齐标签 ,将方向 从自动改为水平文本 ,在这种设置下 ,即使图表已重置尺寸 ,x 轴标记也会变为水 平 4.6 图 3.4 修改后的直方图 二、不指定接受区域直方图 在进行探索性分析时 ,为了方便 ,通常不指定接受区域作直方图,步骤如下 : (1 , 在分析工具框中 “ 直方图 ” 。如图 4 所示。

28、1 输入 输入区域 :A1:A51 接受区域 :(该处为空 选择标志 2 输出选项 输出区域 : B1 选定图表输出 (2 (3按前面方法对直方图进行进一步修饰即得图3.5 图 3.5 修改后的直方图 3.2 计数数据的透视表与条图 例 3.2(3-3数据见图 步骤如下 : (1, 如图 4 所示。 (2.选择数据源区域 (3选定数据透视表位置 ,完成 (4将“ 性别” 作为行字段拖至G列,并将“ 性别” 作为数据拖至数据项处 ,得下表结 果 同理可得 “ 文化程度 ” 的透视表 此时如点击图形按钮 ,立即得到如下的透视图 (5将“ 性别” 作为行字段拖至行字段处,并将“ 文化程度 ” 作为列

29、字段拖至列字 段处,将“ 性别” 或“ 文化程度 ” 作为列字段拖至数据字段处得下表结果 第四章总量指标和相对指标 例 4.1 (4-13 计算步骤 : (1计算各厂计划完成 % E3=D3/C3*100, (22000年实际产量为 1999年的% F3=D3/B3*100, 第五章平均指标 5.1 简单平均数 例 5.1.某组有学生 10人统计课考试成绩为65,82,76,80,82,86,84,88,95, 98分,试求其平均指标。 平均数的计算步骤如下 : (1将数据输入到 A 列,根据 Excel 提供的公式计算各种平均数 (2用 Ctrl+ 可切换到下面的结果 : 5.2 加权平均数 例 5.2(5-1原始数据见下图 A-D 列,其中 A、B 列放日产量的下限和上限 平均数的计算步骤如下 : (1计算日产量的组中值E3=(A3+B3/2,