《Excel在统计中的应用与数据统计分析.doc》由会员分享，可在线阅读，更多相关《Excel在统计中的应用与数据统计分析.doc（73页珍藏版）》请在金锄头文库上搜索。
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 在描述统计中的应用实验目的及要求 要求学生掌握运用Excel进行描述统计分析、绘制各种图表和运用数
3、据透视表工具的技术。 实验内容及步骤 （一）描述统计分析 例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 1984 652.1 1998 5425.1 19
4、85 739.1 1999 5854 31986 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所示。 图 1-1 对话框内各选项的含义如下： 输入区域：在此输入待分
5、析数据区域的单元格范围。 分组方式：如果需要指出输入区域中的数据是按行还是按列排列，则单击“行”或“列”。 标志位于第一行/列：如果输入区域的第一行中包含标志项(变量名)，则选中“标志位于第一行”复选框；如果输入区域的第一列中包含标志项，则选中“标志位于第一列”。 复选框：如果输入区域没有标志项，则不选任何复选框，Excel 将在输出表中生成适宜的数据标志。 均值置信度：若需要输出由样本均值推断总体均值的置信区间，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度。例如，置信度 95%可计算出的总体样本均值置信区间为10，则表示：在5%的显著水平下总体均值的置信区间为( X -10,
6、X +10）。 第 K 个最大/小值：如果需要在输出表的某一行中包含每个区域 的数据的第 k 个最大/小值，则选中此复选框。然后在右侧的编辑框中，输入 k 的数值。 输出区域：在此框中可填写输出结果表左上角单元格地址,用于控 制输出结果的存放位置。 新工作表：单击此选项，可在当前工作簿中插入新工作表，并由新工作表的 A1 单元格开始存放计算结果。如果需要给新工作表命名，则在右侧编辑框中键入名称。 新工作簿：单击此选项，可创建一新工作簿，并在新工作簿的新工作表中存放计算结果。 汇总统计：指定输出表中生成下列统计结果，则选中此复选框。 STEP5：填写完“描述统计”对话框之后，按“确定”按扭即可。
7、结果如图1-2所示。 图1-2 描述统计结果 结果说明：描述统计工具可生成以下统计指标，按从上到下的顺序其中包括样本的平均值、标准误差、组中值、众数、 样本标准差、样本方差、 峰度值、偏 度值、极差、最小值、最大值、样本总和、样本个数和一定显著水平下总体均值的置信区间。 （二）绘图操作 例1-2：仍以例1-1的数据为例，绘制城镇居民可支配收入时间序列的散点图。 STEP1：拖动鼠标选定数值区域A2:C12，不包括数据上面的标志项。 STEP2：选择“插入”菜单的“图表”子菜单，进入图表向导，图1-3所示。 STEP3：选择“图表类型”为“散点图”，然后单击“下一步”。 图1-3 图表向导图 S
8、TEP4：确定用于制作图表的数据区。Excel 将自动把你前面所选定的数据区的地址放入图表数据区的内，单击下一步按钮，出现图1-4所示对话框。 图1-4 作图过程图 STEP5：填写图表标题、X 轴坐标名称、Y 轴坐标名称，此处不用填写，单击“下一步”。 STEP6： 选择图表输出的位置，然后单击“完成”按扭即生成 （三）数据透视表工具 数据透视表是Excel 中强有力的数据列表分析工具。它不仅可以用来作单变量数据的次数分布或总和分析，还可以用来作双变量数据的交叉频数分析、总和分析和其它统计量的分析。 例1-3：表1-2列出了学生两门功课评定结果，建立学生两门功课评定结果的交叉频数表。 表1-
9、2 学生两门功课评定结果 学号 语文 数学 学号 语文 数学 1001 优 差 1006 中 良 1002 良 中 1007 中 优 1003 中 中 1008 差 良 1004 差 中 1009 良 中 1005 差 差 操作步骤： STEP1： 输入数据，选中有数据的任一单元格，然后选择“数据”菜单的“数据透视表”子菜单，进入数据透视表向导。 STEP2： 选择“Microsoft Excel 数据清单或数据库”为数据源。图1-6所示，单击“下一步”。 图1-6 STEP3： 选择待分析的数据的区域，一般情况下Excel 会自动根据当前单元格确定待分析数据区域，因此你只要直接单击“下一步”
10、按扭即可。 STEP4： 确定数据透视表的结构，在此例中，要建立的是一个交叉频数表，分别按语文和数学的成绩对学生的人数进行交叉频数分析，因此可将三个按扭“学号”、“语文”、“数学”分别拖放到表格的指定部位，并且双击“求和项：学号”，将其改为记数项，结果如图1-7所示。 图1-7 布局对话框 STEP5：选择数据透视表的显示位置之后，单击“完成按扭”，可出现如图1-8所示的数据透视表。 图1-8 结果说明：如图1-8的结果所示，数据透视表可以作为一个交叉频数分析工具。 完成数据透视表之后，可按需要修改数据表的显示格式。例如，如果想要把表格中的频数替换成为百分比数。可以用鼠标右击频数的任一单元格，
11、选择“字段”子菜单，单击“选项”按扭，将“数据显示方式”替换成为“占总和的百分比”，然后单击“确定”按扭即可。按同样方式，可将数据透视表修改成为其它不同样式。 实验二 Excel中的二项分布工具 实验目的及要求 掌握利用Excel的BINOMDIST 的函数计算二项分布的概率以及累积概率。 实验内容及步骤 例1-4：一个推销员打了六个电话，推销成功的概率是0.3，建立推销成功次数的概率分布图表。可以按以下步骤建立推销成功次数的概率分布图表。 STEP1： 如图1-9所示，先在Excel之下建立好概率分布表格的框架。 图 1-9 STEP2：如图1-10所示,先在B7 至F7 单元格分别输入概率
12、计算公式。 图1-10 STEP3：公式的拷贝。选取B7 至F7 单元格，拖动“填充柄”至F13 单元格 即可完成公式的拷贝操作。结果图1-11所示。 图1-11 STEP4：下面开始创建二项分布图表。选取B7 至B13 单元格，选取“插入”菜单的“图表”子菜单。 STEP5：选择“柱状图”，然后单击“下一步”。 STEP6：单击“系列”标签，单击“分类(X)轴标志”框，并用鼠标选取A7至A13 单元格为图表X 轴的轴标，然后单击“下一步”。 STEP7：分别键入图表名称“二项分布图”，X 轴名称“成功次数”，Y 轴名称“成功概率”，单击“完成”按扭即可生成二项分布图表。 结果说明: 如图1-
13、11 所示，利用Excel 的BINOMDIST 的函数可以计算出二项分布的概率以及累积概率。BINOMDIST 函数可以带四个参数，各参数的含义分别是：实验成功的次数，实验的总次数，每次实验中成功的概率，是否计算累积概率。四个参数是一个逻辑值，如果为TRUE，函数 BINOMDIST 返回累积分布函数，如果为FALSE，返回概率密度函数。 另外，EXCEL还提供了其它分布的函数，如函数CRITBINOM；函数HYPGEOMDIST； 函数NEGBINOMDIST：函数POISSON: 正态分布函数NORMDIST：函数NORMSDIST：函数NORMSINV：t 分布函数TDIST: 有兴趣
14、的同学可以自己研究。 实验三 随机抽样工具 实验目的及要求 掌握利用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，右击选中的区域选择“复制”，再次右击选中的区域，选择“选择性粘贴”，单击选项“数值” 后，点击“确定”按扭。 STEP4：选取单元格A2 至B11 单元格，选择“数据”菜单项下的排序子菜单。 STEP5：选取“RANDOM”为主要关键字 ，然后点击“确定”按扭。排序结果如图1-13 所示，A2 至A6 单元格的样本即为随机抽取的5 个样本。 图1-13 实验四 由样本推断总体 实验目的及要求 掌握利用Excel的几个函数，如求平均函数AVERAGE、标准差函数STDEV、T 分布函数TINV 等的组合使用构造一个专
