Excel在统计中的应用.

Presentation on theme: "Excel在统计中的应用."— Presentation transcript:

1 Excel在统计中的应用

2 第一节 中文Excel概述 第二节 Excel基本操作 第三节 Excel在描述统计中的应用 第四节 Excel在推断统计中的应用

3 第一节 中文Excel概述 一、中文Excel简介 二、Excel的安装和启动 三、Excel工作界面简介

4 第一节 中文Excel概述 一、 中文Excel简介
Microsoft Excel是美国微软公司开发的Windows环境下的电子表格系统，系Microsoft Office办公室自动化集成软件的重要组成部分，它是目前应用最为广泛的表格处理软件之一。自Excel诞生以来，主要历经了Excel3.0、Excel4.0、Excel5.0、Excel95、Excel97和Excel2000等不同版本。随着版本的不断提高，Excel强大的数据处理功能和操作的简易性逐渐走入了一个新的境界，整个系统的智能化程度也不断提高，它甚至可以在某些方面判断用户的下一步操作，使用户操作大为简化。这些特性，已使Excel成为现代办公软件重要的组成部分。

5 Excel具有强有力的数据库管理功能、丰富的宏命令和函数、强有力的决策支持工具，它具有以下主要特点：
强大的数据分析能力 操作简便 图表能力 数据库管理能力 宏语言功能 样式功能 对象连接和嵌入功能

6 网上相关资源 Excel之家， Excel的应用，提供大量的基础和高级教程。 办公学苑联盟， 等办公软件的使用技巧、VBA。 联数工作室主页， 国外统计资源总索引， Excel多元统计分析插件XLSTAT下载，

7 二、Excel的安装和启动 Excel97作为0ffice97系统中的一个应用程序，它的工作平台为window95或其以上的版本。
1．在CD－R0M中放入0ffice 97光盘。 2．0ffice 97光盘一般具有自动执行的功能，自动执行之后，直接点取安装屏幕上的“开始安装”按扭，即可开始Office的安装。（如果光盘不具有自动执行的功能，单击Windows的“开始”按钮并选择“运行”命令，在弹出的“运行”对话框中，利用“浏览”按扭寻找光盘上的安装程序“SETUP.EXE”，找到后单击“确定”按钮，运行该程序即可。） 3．开始安装后系统将要求用户选择安装模式：“典型”、“自定义”、“快速”。用户可根据需要选择安装模式，一般可以选择“典型”安装方式，那么安装软件将装入最常用的Office组件，如果用户想定制所要安装的组件，那么可以选择“自定义”安装方式，而“快速”安装方式是一种最节约磁盘空间的安装方式，它仅装入Office运行中不能缺少的组件。 然后，用户可按屏幕逐步提示，完成剩下安装过程。最后，重新开机，就可以开始使用Excel。 （二）Excel的启动与退出 启动Excel的常用方法是：单击任务栏上的“开始”按扭，此时屏幕上出现一个弹出式菜单，将鼠标指向“程序”项后，屏幕出现另一个弹出菜单，单击“Microsoft Excel”一程序项，就可以启动Excel系统，此时屏幕上出现如图附-1所示的Excel主工作画面。若安装Excel时，生成了“快捷工具栏”，则双击其中的Excel按扭也可立即启动Excel。 退出Excel常用的方法是：保存当前编辑的文件之后，用鼠标单击标题栏的╳按扭或进入“文件”菜单栏单击“退出”选项。

8 三、Excel工作界面简介

9 （一）“标题”’栏 “标题”栏告诉用户正在运行的程序名称和正在打开的文件的名称。如图附-1所示,标题栏显示“Microsoft Excel-Book1”表示此窗口运行的应用程序为Microsoft Excel，在Excel中打开的文件的文件名为Book1.xls。 （二）“菜单”栏 “菜单”栏按功能把Excel命令分成不同的菜单组，它们分别是“文件” 、“编辑” 、“视图” 、“插入” 、“格式” 、“工具” 、“表格” 、“帮助” 。当菜单项被选中时，引出一个下拉式菜单，可以从中选取相应的子菜单。 另外，在屏幕的不同地方单击鼠标右键时，“快捷菜单”将出现在鼠标指针处。选取“快捷菜单”中的命令同从菜单栏的菜单上选取相应命令的效果是一样的，但选取速度明显增快。 （三）“工具”栏 Excel可显示几种工具栏，这些工具栏可起到简化用户的操作的作用。“工具”栏中的按钮都是菜单中常用命令的副本，当鼠标指向某一按钮后，稍等片刻在按钮右下方会显示该按扭命令的含意。用户可以配置“工具”栏的内容，通过“视图”菜单中的“工具”栏子菜单来选择显示不同类型的“工具”栏。下面介绍出现在Excel开始屏幕中的两种“工具”栏。 1．“常用”工具栏 “常用”工具栏中为用户准备了Excel最常用命令的快捷按钮，如“新建文件”按扭，“打开文件”按扭，“保存文件”按钮，“撤消”按扭等。 2．“格式”工具栏 “格式”工具栏专门放那些和文本外观有关的命令，如字体、字号、颜色、对齐方式及其他选项。

10 （四）“编辑”栏 “编辑”栏用于显示和输入活动单元格的信息。在“编辑”栏中用户可以输入和编辑公式，“编辑”栏位于图附-1中第5行。 “编辑”栏由“名字”栏和“公式”栏组成。位于“编辑”栏左侧的“名字”栏中显示的是活动单元格的坐标，也可在“名字”栏中直接输入一个或一块单元格的地址进行单元格的快速选定；位于“编辑”栏右侧的“公式”栏可用于编辑活动单元格的内容，它包含三个按钮和一个编辑区。当向活动单元格输入数据时，公式栏中便出现三个按钮，三个按钮从左至右分别是：“╳”（取消）按钮、“√”（确认）按钮和“＝”（公式指南）按钮。 通常Excel在工作区中显示“编辑”栏。在“视图”菜单中的“编辑栏”命令是一个开关命令，它可以用于控制隐藏或显示“编辑”’栏。 （五）工作表 工作簿窗口包含了16张独立的工作表（sheet）。开始时，窗口中显示第一张工作表“Sheetl”，该表为当前工作表。当前工作表只有一张，用户可通过点击工作表下方的标签击活其他工作表为当前工作表。 工作表是一个由行和列组成的表格。行号和列号分别用字母和数字加以区别。行由上自下范围1～65536，列号则由左到右采用字母编号A～IV。因此每张表格的大小为256列╳65536行，若从Excel导入的数据超过以上范围，则会被Excel自动截去。每一个行、列坐标所指定的位置称之为单元格。在单元格中用户可以键人符号、数值、公式以及其他内容。

11 第二节 Excel基本操作 一、Excel操作方法概述 二、文件基本操作 三、数据的输入输出操作 四、数据的移动操作 五、数据的删除操作
六、与其它软件交换数据的方法

12 一、Excel操作方法概述 要完成任一项Excel操作一般都可以采用三种操作方法：鼠标操作、菜单操作和键盘命令操作。例如，想要将A1单元格的数据复制到A2单元格去，有如下几种操作方法： （一）鼠标操作法：先用鼠标选中A1单元格，然后缓慢移动鼠标到A1单元格的右下角，当鼠标的形状变为黑色实心“十”字形之后，拖动鼠标到A2单元格，然后放开鼠标，则A1的数据就复制到A2单元格了。 （二）菜单操作法：先用鼠标选中A1单元格，选择“编辑”菜单中的“复制”命令，然后用鼠标选中A2单元格，再选择“编辑”菜单中的“粘贴”命令，数据就复制到A2单元格了。 （三）键盘命令操作法：直接用鼠标选中A2单元格，从键盘输入“＝A1”命令，则复制即告完成。 以上是Excel中很典型的三种操作方法。在实际使用过程中，应根据实际情况，尽量选择三种方法中最简洁的操作方法，以提高操作速度。

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

14 三、数据的输入输出操作 （一）数据的手动输入
建立一个新的Excel文件之后，便可进行数据的输入操作。Excel中以单元格为单位进行数据的输入操作。一般用上下左右光标键，Tab键或鼠标选中某一单元格，然后输入数据。 Excel中的数据按类型不同通常可分为四类：数值型，字符型，日期型，和逻辑型。Excel根据输入数据的格式自动判断数据属于什么类型。如日期型的数据输入格式为“年/月/日”，“年-月-日”或“时：分：秒”；要输入逻辑型的数据，输入“true”(真)或 “false”(假)即可；若数据由数字与小数点构成，Excel自动将其识别为数字型，Excel允许在数值型数据前加入货币符号，Excel将其视为货币数值型，Excel也允许数值型数据用科学记数法表示，如2╳109在Excel中可表示为2E+9。除了以上三种格式以外的数据，Excel将其视为字符型处理。

15 （二）公式生成数据 　 Excel的数据中也可由公式直接生成。例如：在当前工作表中A1和B1单元格中已输入了数值数据，欲将A1与B1单元格的数据相加的结果放入C1单元格中，可按如下步骤操作：用鼠标选定C1单元格，然后输入公式 “=A1+B1”或输入“=SUM(a1:b1)”,回车之后即可完成操作。C1单元格此时存放实际上是一个数学公式“A1+B1”，因此C1单元格的数值将随着A1、B1单元格的数值的改变而变化。Excel提供了完整的算术运算符,如＋(加)－(减)*(乘)／(除)％(百分比)^(指数)和丰富的函数，如SUM(求和)、CORREL(求相关系数)、STDEV(求标准差)等，供用户对数据执行各种形式的计算操作，在Excel帮助文件中可以查到各类算术运算符和函数的完整使用说明。附表１整理出了Excel97中所有统计函数的名称及其函数功能介绍，以供查阅。

16 （三）复制生成数据 1．普通单元格指的是非公式的单元格。普通单元格的复制，一般可以按如下步骤进行： （1）拖动鼠标选定待复制的区域，选定之后该区域变为黑色。Excel可以进行整行、整列或整个表格的选定操作，例如，如果要选定表格的第一列，可直接用鼠标单击列标“A”，如果要选定表格的第一行，可直接用鼠标单击行标“1”，如果要选定整个表格，可直接点击全选按扭，如图附-2所示： 图 附-2 （2）选定完区域之后，用鼠标右击该区域，在弹出的菜单中选择“复制”命令，将区域内容复制到粘贴版之中。可以发现该区域已被虚线包围。 （3）用鼠标右击目标区域，在弹出的菜单中选择“粘贴”命令，则单元格区域的复制即告完成。

17 2．公式单元格的复制，一般可分为两种，一种是值复制，一种是公式复制。值复制指的是只复制公式的计算结果到目标区域，公式复制指的是仅复制公式本身到目标区域。下面对它们的操作步骤分别予以说明。
（1）值复制： ① 拖动鼠标选定待复制区域 。 ② 用鼠标右击选定区域，选择“复制”选项。 ③ 用鼠标右击目标区域，再单击“选择性粘贴”子菜单。出现复制选项，选定“数值”选项，然后单击“确定”按扭，则公式的值复制即告完成。

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

19 如果将该公式复制到F100的单元格,那么公式将变为“=D100+E100”，这就是相对引用的结果，公式的内容随着公式的位置变化而相对变化。如果在C1单元格输入的是“=$A$1+$B$1”那么此公式引用的便是绝对的单元格，不论将公式复制到何处，公式的内容都不会发生变化。当然，绝对引用和相对引用亦可在同一公式之中混合交叉使用，例如，如果在C1单元中输入的是公式“=A$1+B$1”,那么意味着，公式的内容不会随着公式的垂直移动而变动，而是随着公式的水平移动而变动，如果将该公式复制到F100单元格，那么公式将变为，“=D$1+E$1” 。可以作这样的归纳：公式中“$”符号后面的单元格坐标不会随着公式的移动而变动，而不带“$”符号后面的单元格坐标会随着公式的移动而变动。 在实际的使用中，如果能把单元格的相对引用与绝对引用灵活应用到Excel的公式之中，能为数据成批准确运算带来极大的方便。

20 四、数据的移动操作 数据的移动操作可按如下步骤进行： （一）拖动鼠标选定待移动区域 。
（二）用鼠标右击选定区域，选择弹出菜单中“剪切”命令。 （三）用鼠标右击目标区域，选择弹出菜单中“粘贴”命令，则单元格区域的移动即告完成。 与数据的复制操作不同，公式单元格的移动操作不存在值移动或公式移动的区别，也不存在绝对引用已相对引用的区别，移动操作将把公式单元格的公式内容原原本本移动到目标区域，不作任何改动。

21 五、数据的删除操作 数据的删除操作可按如下步骤进行： （一）拖动鼠标选定待删除区域 。
（二）用鼠标右击选定区域，选择“删除”，即可删除单元格区域的内容。 如果不小心删除了不该删除的区域，可以通过“编辑”菜单的“撤消”命令来恢复被删除的内容。“撤消”操作是Excel中较常用到的操作，如果不小心实施了错误的操作，那么可以通过“撤消”操作使工作表恢复原样。如果不小心撤消了不应该撤消的操作，那么可以用“编辑”菜单的“重复”命令，使撤消操作重新执行。

22 六、与其它软件交换数据的方法 在Excel中可以打开其它类型的数据文件，如FOXPRO系列的DBF数据库文件，文本文件，lotus1-2-3的数据文件等。具体操作方法如下： （一）在“文件”菜单中选择“打开”子菜单。 （二）在“打开文件”对话框中选择所要打开的文件的类型及其所在的目录。 （三）用鼠标双击该文件名，并按Excel提示步骤操作即可打开该文件。 Excel文件同样也可存为其它类型的数据文件，具体操作方法如下： （一）编辑好文件后，在“文件”菜单中选择“另存为”子菜单。 （二）在“另存为”对话框中选择所要打开文件的类型及其所在的目录。 （三）输入文件名之后，用鼠标单击“保存”按扭即可。 　 　需要注意的是，由于Excel工作簿文件由多张工作表构成，所以打开一个其它软件的数据文件之后的Excel工作簿仅由一个工作表构成，另一方面将Excel文件存为其它格式的数据文件之时，一般仅对当前工作表有效。

23 第三节 Excel在描述统计中的应用 前言 一、描述统计工具 二．直方图工具 三、利用Excel绘制散点图 四、数据透视表工具
五、排位与百分比工具

24 前 言 在使用Excel进行数据分析时，要经常使用到Excel中一些函数和数据分析工具。其中，函数是Excel预定义的内置公式。它可以接受被称为参数的特定数值，按函数的内置语法结构进行特定计算，最后返回一定的函数运算结果。例如，SUM 函数可对单元格或单元格区域执行相加运算。函数的语法以函数名称开始，后面分别是左圆括号、以逗号隔开的参数和右圆括号。参数可以是数字、文本、形如 TRUE 或 FALSE 的逻辑值、数组、形如 #N/A 的错误值，或单元格地址。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。 Excel还提供了一组数据分析工具，称为“分析工具库”，在建立复杂的统计分析时，使用现成的数据分析工具，可以节省很多时间。只需为每一个分析工具提供必要的数据和参数，该工具就会使用适宜的统计或数学函数，在输出表格中显示相应的结果。其中的一些工具在生成输出表格时还能同时产生图表。要浏览已有的分析工具，可以单击“工具”菜单中的“数据分析”命令。如果“数据分析”命令没有出现在“工具”菜单上，则必须运行“安装”程序来加载“分析工具库”，安装完毕之后，再通过“工具”菜单中的“加载宏”命令，在“加载宏”对话框中选择并启动它。

25 一、描述统计工具 （一）简介：此分析工具用于对输入区域中数据的单变量分析，并提供数据趋中性和易变性等有关信息。 （二）操作步骤：
1．用鼠标点击工作表中待分析数据的任一单元格。 2．选择“工具”菜单的“数据分析”子菜单，用鼠标双击数据分析工具中的“描述统计”选项 。 3．出现“描述统计”对话框，对话框内各选项的含义如下： 输入区域：在此输入待分析数据区域的单元格范围。一般情况下Excel会自动根据当前单元格确定待分析数据区域。 分组方式：如果需要指出输入区域中的数据是按行还是按列排列，则单击“行”或“列”,“描述统计”工具可以同时对多列或多行数据进行统计分析。 标志位于第一行/列：如果输入区域的第一行中包含标志项(变量名)，则选中“标志位于第一行”复选框；如果输入区域的第一列中包含标志项，则选中“标志位于第一列”复选框；如果输入区域没有标志项，则不需要选择该复选框，Excel 将在输出表中生成适宜的数据标志。

26 输出区域：在此框中可填写输出结果表左上角单元格地址,用于控制输出结果的存放位置。整个输出结果分为两列,左边一列包含统计标志项，右边一列包含统计值。根据所选择的“分组方式”选项的不同，Excel 将为输入表中的每一行或每一列生成一个两列的统计表。 新工作表：单击此选项，可在当前工作簿中插入新工作表，并由新工作表的 A1 单元格开始存放计算结果。如果需要给新工作表命名，则在右侧编辑框中键入名称。 新工作簿：单击此选项，可创建一新工作簿，并在新工作簿的新工作表中存放计算结果。 汇总统计：指定输出表中生成下列统计结果，则选中此复选框。这些统计结果有：平均值、标准误差、中值、众数、标准偏差、方差、峰值、偏斜度、极差（全距）最小值、最大值、总和、样本个数。 均值置信度：若需要输出由样本均值推断总体均值的置信区间，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度。例如，若置信度95%计算出的总体样本均值置信区间为10，则表示：在5%的显著水平下总体均值的置信区间为(-10,+10）。

27 第 K 个最大/小值：如果需要输出每个区域的数据的第 k 个最大或最小值，则选中此复选框。然后在右侧的编辑框中，输入 k 的数值。
5．填写完“描述统计”对话框之后，按“确定”按扭即可。 （三）结果说明：描述统计工具可生成以下统计指标，按从上到下的顺序其中包括样本的平均值（），标准误差()，组中值(Medium)，众数(Mode), 样本标准差(S)，样本方差(S2), 峰度值，偏度值，极差(Max-Min), 最小值(Min),最大值(Max),样本总和，样本个数（n）和一定显著水平下总体均值的置信区间。

28 二．直方图工具 （一）简介：直方图工具，用于计算数据的个别和累积频率，再根据有限集中某个数值元素的出现次数建立图表。例如，在一个有 50 名学生的班级里，可以通过直方图确定考试成绩的分布情况,它会给出考分出现在指定成绩区间的学生个数,而用户必须把存放分段区间的单元地址范围填写在在直方图工具对话框中的“接收区域”框中。 （二）操作步骤： 1．用鼠标点击表中待分析数据的任一单元格。 2．选择“工具”菜单的“数据分析”子菜单。用鼠标双击数据分析工具中的“直方图”选项 。 3．出现“直方图”对话框，对话框内主要选项的含义如下： 输入区域：在此输入待分析数据区域的单元格范围。 接收区域（可选）：在此输入接收区域的单元格范围，该区域应包含一组可选的用来计算频数的边界值。这些值应当按升序排列。只要存在的话，Excel 将统计在各个相邻边界直之间的数据出现的次数。如果省略此处的接收区域，Excel 将在数据组的最小值和最大值之间创建一组平滑分布的接收区间。

29 标志：如果输入区域的第一行或第一列中包含标志项，则选中此复选框；如果输入区域没有标志项，则清除此该复选框，Excel 将在输出表中生成适宜的数据标志。
累积百分比：选中此复选框，可以在输出结果中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略以上结果。 图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。 4． 按需要填写完“直方图”对话框之后，按“确定”按扭即可。 （三）结果说明：完整的结果包括三列数据和一个频率分布图，第一列是数值的区间范围，第二列是数值分布的频数，第三列是频数分布的累积百分比。

30 三、利用Excel绘制散点图 （一）简介：散点图是观察两个变量之间关系程度最为直观的工具之一，利用Excel的图表向导，可以非常方便的创建并且改进一个散点图，也可以在一个图表中同时显示两个以上变量之间的散点图。 （二）操作步骤：如图附-3所示数据，可按如下步骤建立变量x-y，x-z的散点图： 图 附-3

31 1．拖动鼠标选定数值区域A2:C12，不包括数据上面的标志项。
2．选择“插入”菜单的“图表”子菜单，进入图表向导。 3．选择“图表类型”为“散点图”，然后单击“下一步” 。 4．确定用于制作图表的数据区。Excel将自动把第1步所选定的数据区的地址放入图表数据区的内。 5．在此例之中，需要建立两个系列的散点图，一个是x-y系列的散点图，一个是x-z系列的散点图，因此，必须单击“系列”标签，确认系列1的“X值”方框与“数值方框”分别输入了x,y数值的范围，在系列2的“X值”方框与“数值方框”分别输入了x,z数值的范围。在此例中，这些都是Excel已经默认的范围，所以，通常情况下，直接单击“下一步”即可。 6. 填写图表标题为“X-Y与X-Z散点图” ，X轴坐标名称为“X”与Y轴坐标名称“Y/Z”，然后单击“下一步” 。 7. 选择图表输出的位置，然后单击“完成”按扭即生成图附-4的图表。

32 （三）结果说明：如图附-4所示，Excel中可同时生成两个序列的散点图，并分为两种颜色显示。通过散点图可观察出两个变量的关系，为变量之间建立数学模型作准备。
图 附-4

33 四、数据透视表工具 （一）简介：数据透视表是Excel中强有力的数据列表分析工具。它不仅可以用来作单变量数据的次数分布或总和分析，还可以用来作双变量数据的交叉频数分析、总和分析和其它统计量的分析。 （二）操作步骤：如图附-5所示，表中列出学生两门功课评定结果， 图 附-5

34 可按如下步骤建立交叉频数表： 1．选中图附-5中表格中有数据的任一单元格，然后选择“数据”菜单的“数据透视表”子菜单，进入数据透视表向导。 2．选择“Microsoft Excel数据清单或数据库”为数据源。单击“下一步” 。 3．选择待分析的数据的区域，一般情况下Excel会自动根据当前单元格确定待分析数据区域，因此你只要直接单击“下一步”按扭即可。 4．确定数据透视表的结构，在此例中，要建立的是一个交叉频数表，分别按语文和数学的成绩对学生的人数进行交叉频数分析，因此可按图附-６将三个按扭“学号”、“语文”、“数学”分别拖放到表格的指定部位，并且双击“求和项：学号” ，将其改为记数项，结果如图附-６所示，然后单击“下一步”按扭。

35 5．选择数据透视表的显示位置之后，单击“完成按扭” ，可出现如图附-7所示的数据透视表。
图 附-6 5．选择数据透视表的显示位置之后，单击“完成按扭” ，可出现如图附-7所示的数据透视表。

36 图 附-7 （三）结果说明：如图附-7的结果所示，数据透视表可以作为一个交叉频数分析工具。 完成数据透视表之后，可按需要修改数据表的显示格式。例如，如果想要把表格中的频数替换成为百分比数。可以用鼠标右击频数的任一单元格，选择“字段”子菜单，单击“选项”按扭，将“数据显示方式”替换成为“占总和的百分比” ，然后单击“确定”按扭即 可。按同样方式，可将数据透视表修改成为其它不同样式。

37 五、排位与百分比工具 （一）简介：此分析工具可以产生一个数据列表，在其中罗列给定数据集中各个数值的大小次序排位和相应的百分比排位。用来分析数据集中各数值间的相互位置关系。 （二）操作步骤： 1．用鼠标点击表中待分析数据的任一单元格。 2．选择“工具”菜单的“数据分析”子菜单，用鼠标双击数据分析工具中的“排位与百分比”选项 。 3．填写完“排位与百分比”对话框，单击“确定”按扭即可。 （三）结果说明：输出的结果可分为四列，第一列“点”是数值原来的存放位置，第二列是相应的数值，第三列是数值的排序号，第四列是数值的百分比排位，它的计算方法是：小于该数值的数值个数/（数值总个数-1）。

38 第四节 Excel在推断统计中的应用 一、二项分布工具 二、其它几种主要分布的函数 三、随机抽样工具 四、样本推断总体 五、假设检验
六、单因素方差分析 七、线性回归分析 八、相关系数分析 九、季节变动时间序列的分解分析

39 一、二项分布工具 （一）简介：在Excel中想要计算二项分布的概率值、累积概率，需要利用Excel的工作表函数BINOMDIST。函数 BINOMDIST 适用于固定次数的独立实验，实验的结果只包含成功或失败二种情况，且每次实验成功的概率固定不变。例如，已知次品概率的情况下，函数 BINOMDIST可以计算抽查10个样品中发现2个次品的概率。以下例子说明如何在Excel中计算二项分布的概率值，以及如何进一步建立二项分布图表。 （二）操作步骤：例子如下所示，一个推销员打了六个电话，推销成功的概率是0.3，那么可以按以下步骤建立推销成功次数的概率分布图表。

40 1、如图附-8所示，先在Excel之下建立好概率分布表格的框架。
图 附-8

41 2、如图附-9 所示,先在B7至F7单元格分别输入概率计算公式。
图 附-9

42 3、公式的拷贝。选取B7至F7单元格，然后移动鼠标至F7单元格的 右下角，使其成为黑色实心十字星状，一般称之为“填充柄”，拖动“填充柄”至F13单元格即可完成公式的拷贝操作。结果图附-10所示。
图 附-10

43 4．下面开始创建二项分布图表。选取B7至B13单元格，选取“插入”菜单的“图表”子菜单。
5．选择“柱状图” ，然后单击“下一步” 。 6 ．单击“系列”标签，单击“分类(X)轴标志”框，并用鼠标选取A7至A13单元格为图表X轴的轴标，然后单击“下一步” 。 7 ．分别键入图表名称“二项分布图” ，X轴名称“成功次数” ，Y轴名称“成功概率”，单击“完成”按扭即可生成二项分布图表。 （三）结果说明: 如图附-10所示，利用Excel的BINOMDIST的函数可以计算出二项分布的概率以及累积概率。BINOMDIST函数可以带四个参数，各参数的含义分别是：实验成功的次数，实验的总次数，每次实验中成功的概率，是否计算累积概率。第四个参数是一个逻辑值，如果为TRUE，函数 BINOMDIST 计算累积分布函数概率值，如果为FALSE，计算概率密度函数概率值。

44 二、其它几种主要分布的函数 （一）函数CRITBINOM：
1．说明：函数CRITBINOM可称为BINOMDIST的逆向函数，它计算出使累积二项式分布概率P(X<=x)大于等于临界概率值的最小值。 2．语法：CRITBINOM(trials,probability_s,alpha) Trials：贝努利实验次数。 Probability_s：一次试验中成功的概率。 Alpha： 临界概率。 3．举例: CRITBINOM(6,0.5,0.75) 等于 4，表明如果每次试验成功的概率为0.5,那么6次试验中成功的次数小于等于4的概率恰好超过或等于0.75 。

45 （二）函数HYPGEOMDIST： 1．说明：函数HYPGEOMDIST计算超几何分布。给定样本容量、总体容量和样本总体中成功的次数，函数 HYPGEOMDIST 计算出样本取得给定成功次数的概率。使用该函数可以解决有限总体的问题，其中每个观察值只有两种取值，或者为成功或者为失败，且给定样本区间的所有子集有相等的发生概率。 2. 语法：HYPGEOMDIST(sample_s,number_sample,population_s, number_population) Sample_s： 样本中成功的次数。 Number_sample：样本容量。 Population_s： 样本总体中成功的次数。 Number_population： 样本总体的容量。 3.举例：容器里有20块巧克力，8 块是焦糖的，其余 12 块是果仁的。如果从中随机选出 4 块，下面函数计算式计算出只有一块是焦糖巧克力的概率：HYPGEOMDIST(1,4,8,20)= 。

46 （三）函数POISSON: 1．说明：函数POISSON计算泊松分布。泊松分布通常用于预测一段时间内事件发生指定次数的概率，比如一分钟内通过收费站的轿车的数量为n的概率。 2．语法：POISSON(x,mean,cumulative) X： 事件数。 Mean： 期望值。 Cumulative： 为一逻辑值，确定计算出的概率分布形式。 如果 cumulative 为 TRUE，函数 POISSON 计算出累积分布函数概率值，即，随机事件发生的次数在 0 和 x 之间（包含 0 和 1）；如果为 FALSE，则计算概率密度函数，即，随机事件发生的次数恰好为 x。 3．举例：POISSON(2,5,FALSE)= 表明，若某一收费站每分种通过的轿车平均数量为5辆，那么某一分钟通只2辆的概率为 。

47 （四）正态分布函数NORMDIST： 1．说明：正态分布在模拟现实世界过程和描述随机样本平均值的不确定度时有广泛的用途。函数NORMDIST计算给定平均值和标准偏差的正态分布的累积函数概率值。 同样可以用类似“七”中的方法，利用NORMDIST函数建立正态分布密度函数图，这里不再赘述。 2. 语法：NORMDIST(x,mean,standard_dev,cumulative) X： 为需要计算其分布的数值。 Mean： 分布的算术平均值。 Standard_dev：分布的标准偏差。 Cumulative： 为一逻辑值，指明函数的形式。如果 cumulative 为 TRUE，函数 NORMDIST 计算累积分布函数；如果为 FALSE，计算概率密度函数。 3. 举例：公式NORMDIST(6,5,2,0)计算出平均值为5、标准差为2的正态函数当X=6时概率密度函数的数值，公式NORMDIST（60，50，4，1）计算出平均值为50、标准差为4的正态分布函数当X=60时累积分布函数的数值。

48 （五） 函数NORMSDIST： 1．说明：函数NORMSDIST计算标准正态分布的累积函数。 2．语法： NORMSDIST(z) Z 为需要计算其分布的数值。 3．举例：NORMSDIST(0)=0.5，表明若x服从标准正态分布，那么x<0的概率为50% （六）函数NORMSINV： 1．说明：函数NORMSINV计算标准正态分布累积函数的逆函数。 2．语法：NORMSINV(probability) Probability： 正态分布的概率值。 3．举例：NORMSINV(0.5)=0

49 （七）t分布函数TDIST: 1．说明：函数TDIST计算student的t 分布数值。T分布用于小样本数据集合的假设检验。使用此函数可以代替 t 分布的临界值表。 2．语法：TDIST(x,degrees_freedom,tails) X：为需要计算分布的数字。 Degrees_freedom：为表示自由度的整数。 Tails：指明计算的分布函数是单尾分布还是双尾分布。如果 tails = 1，函数 TDIST 计算单尾分布。如果 tails = 2，函数 TDIST 计算双尾分布。 3．举例：TDIST(1.96,60,2)=

50 三、随机抽样工具 （一）简介：Excel中的RAND()函数可以产生大于等于 0 小于 1 的均匀分布随机数，RAND()不带任何参数运行，每次计算时时都将产生一个新的随机数，如果将RAND() 函数从一个单元格复制或移动到另外一个单元格，RAND()函数也将重新计算一个新的数值。RAND()函数可以被用来作为不重复抽样调查的工具。 （二）操作步骤：如图附-11所示，10个象征性的样本数据，欲从中随机抽取5个数据可按如下步骤操作： 图 附-11

51 图 附-12 1．选择B2单元格，输入公式“=RAND()”并回车 。
2．拖动B2单元格右下角的填充柄至B11单元格，并在B1单元格输入列标志名称“random”。 3．选取单元格B2至B11，右击选中的区域选择“复制”，再次右击选中的区域，选择“选择性粘贴”，单击选项“数值”后，点击“确定”按扭，此时B2:B11单元格是10个稳定的随机数。 4．选取单元格A2至B11单元格，选择“数据”菜单项下的排序子菜单。 5．选取“RANDOM”为主要关键字 ，然后点击“确定”按扭。排序结果如图附-12所示，可以用A2至A6单元格的样本作为随机抽取的5个样本。 图 附-12

52 （三）结果说明： 1. 以上进行的是不重复随机抽样，可以用类似的方法，利用Excel的RANDBETWEEN(TOP,BOTTOM)函数实现总体的重复随机抽样。RANDBETWEEN(TOP,BOTTOM)函数可随机产生介于TOP与BOTTOM之间的随机整数，抽取此整数对应编号的样本可作为总体的重复随机抽样的结果。 2. RAND()函数产生的是0与1之间均匀的随机数，利用数据分析工具中的随机数发生器，可以生成用户指定类型分布的随机数。例如 0-1正态分布的随机数，指定γ参数的迫松分布的随机数等。 3. Excel易于产生各类型随机数，可以用类似的方法方便的进行进行随机数字模拟试验与随机游走模拟试验。

53 四、样本推断总体 （一）简介：利用Excel的几个函数，如求平均函数AVERAGE、标准差函数STDEV、T分布函数TINV等的组合使用可以构造出一个专门用于实现样本推断总体的Excel工作表。以下例子先计算样本的平均数和标准差，然后在一定置信水平上估计总体均值的区间范围。 （二）操作步骤： 1．构造工作表。如图附-13所示，首先在各个单元格输入以下的内容，其中左边是变量名，右边是相应的计算公式。 2.将A列的名称定义成为B列各个公式计算结果的变量名。选定A4:B6,A8:B8和A10:B15单元格(先用鼠标选择第一部分，再按住CTRL键选取另外两个部分)，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“最左列”选项,然后点击“确定”按扭即可。

54 图 附-13 3. 输入样本数据，和用户指定的置信水平0.95,如图附-13所示。
4. 为样本数据命名。选定D1:D11单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定”按扭，最后得到图附-14所示的计算结果。

55 （三）结果说明：以上例子说明如何交叉组合使用Excel 的公式和函数，以构造出一个能实现样本推断总体有关计算的Excel工作表。实际上，在用Excel进行数据统计处理之时，许多统计功能可以使用和上例类似的方法，通过组合使用Excel的各类统计函数和公式加以实现。 图 附-14

56 五、假设检验 （一）简介：假设检验是统计推断中的重要内容。以下例子利用Excel的正态分布函数NORMSDIST、判断函数IF等，构造一张能够实现在总体方差已知情况下进行总体均值假设检验的Excel工作表。 （二）操作步骤： 1．构造工作表。如图附-15所示，首先在各个单元格输入以下的内容，其中左边是变量名，右边是相应的计算公式。 图 附-15

57 2. 将A列的名称定义成为B列各个公式计算结果的变量名。选定A3:B4,　A6:B8，A10:A11,A13:A15和A17:B19单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“最左列”选项,然后点击“确定”按扭即可。 3. 输入样本数据，以及总体标准差、总体均值假设、置信水平数据。如图附-16所示。 图 附-16

58 4.为样本数据指定名称。选定C1:C11单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定” 按扭，最后得到如图附-16中所示的计算结果。
（三）结果说明：如图附-16所示，该例子的检验结果不论是单侧还是双侧均为拒绝Ho假设。所以，根据样本的计算结果，在5%的显著水平之下,拒绝总体均值为35的假设。同时由单侧显著水平的计算结果还可以看出：在总体均值是35的假设之下，样本均值小于等于31.4的概率仅为 <0.05,小概率事件居然发生，所以，同样得出在5%的显著水平下，拒绝总体均值为35的假设的结论。

59 六、单因素方差分析 （一）简介：单因素方差分析可用于检验两个或两个以上的总体均值相等的假设是否成立。此方法是对双均值检验（如 t-检验）的扩充。该检验假定总体是服从正太分布的，总体方差是相等的，并且随机样本是独立的。这种工具适用于完全随机化试验的结果分析。例子如图附-17表中所示，一产品制造商雇佣销售人员向销售商打电话。制造商想比较四种不同电话频率计划的效率，他从销售人员中随机选出32名，将他们随机分配到4种计划中，在一段时期内记录他们的销售情况已经在表中列出，试问其中是否有一种计划会带来较高的销售水平。 图 附-17

60 （二）操作步骤 1. 选择“工具”菜单的“数据分析”子菜单，双击“方差分析: 单因素方差分 析”选项，弹出单因素方差分析对话框。 2.按图附-18所示方式填写对话框。然后单击“确定”按扭即可。 图 附-18

61 （三）结果分析：按照如上的操作步骤即可得到图附-19的计算结果。其中表格的第二部分则是方差分析的结果。SS列分别给出了四个分组的组间方差、组内方差以及总方差，DF列分别给出了对应方差的自由度， MS列是平均值方差，由SS除于DF得到，它是总体方差的两个估计值。F列是F统计量的计算结果，如果四个总体均值相等的假设成立的化，它应该服从F分布，即近似为1，它是最终的计算结果，通过将它与一定置信水平下的F临界值F crit比较，可以判断均值相等的假设是否成立，在本例中， < ,所以不能拒绝四个总体均值相等的假设。P-value列，是单尾概率值，表明如果四个总体均值相等的假设成立的化，得到如上样本结果的概率是19.442% ,即得到以上样本并不是小概率事件，同样也得到不能拒绝四个总体均值相等的假设的结论。 按相似方法可进行无重复双因素方差分析，有重复双因素方差分析。 图 附-19

62 七、线性回归分析 （一）简介：线性回归分析通过使用“最小二乘法”对样本数据进行直线拟合，用于分析单个因变量是如何受一个或几个自变量影响的。例子如图附-20所示，表中是我国1987年至1997年的布匹人均产量和人均纱产量，试用线性回归分析的方法分析两组数据之间的关系。 图 附-20

63 （二）操作步骤 1．选择“工具”菜单的“数据分析”子菜单，双击“回归”选项，弹出回归分析对话框。对话框主要选项的含义如下：Y 值输入区域，在此输入因变量数据区域，该区域必须由单列数据组成；X 值输入区域，在此输入对自变量数据区域，自变量的个数最多为16；置信度，如果需要在汇总输出表中包含附加的置信度信息，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度，95%为默认值；常数为零，如果要强制回归线通过原点，则选中此复选框；输出区域，在此输入输出表左上角单元格的地址，用于控制计算结果的输出位置。汇总输出表至少需要有七列的宽度，包含的内容有 anova表、系数、y 估计值的标准误差、r2 值、观察值个数，以及系数的标准误差；新工作表，单击此选项，可在当前工作簿中插入新工作表，并由新工作表的 A1 单元格开始显示计算结果，如果需要给新工作表命名，则在右侧的编辑框中键入名称；新工作簿，单击此选项，可创建一新工作簿，并在新工作簿中的新工作表中显示计算结果；残差，如果需要以残差输出表的形式查看残差，则选中此复选框；标准残差，如果需要在残差输出表中包含标准残差，则选中此复选框；残差图，如果需要生成一张图表，绘制每个自变量及其残差，则选中此复选框；线形拟合图，如果需要为预测值和观察值生成一个图表，则选中此复选框；正态概率图，如果需要绘制正态概率图，则选中此复选框。

64 2．按如下方式填写对话框：X值输入区域为$B$1:$B$12, Y值输入区域为$C$1:$C$12, 并选择“标志”和“线性拟合图”两个复选框，然后单击“确定”按扭即可。
（三）结果分析 按照如上的操作步骤即可得到图附-21下表的计算结果。结果可以分为四个部分，第一部分是回归统计的结果包括多元相关系数、可决系数R2、调整之后的相关系数、回归标准差以及样本个数。第二部分是方差分析的结果包括可解释的离差、残差、总离差和它们的自由度以及由此计算出的F统计量和相应的显著水平。第三部分是回归方程的截距和斜率的估计值以及它们的估计标准误差、t统计量大小双边拖尾概率值、以及估计值的上下界。根据这部分的结果可知回归方程为Y= *X 。第四部分是样本散点图， 其中蓝色的点是样本

65 的真实散点图，红色的点是根据回归方程进行样本历史模拟的散点。如果觉得散点图不够清晰可以用鼠标拖动图形的边界达到控制图形大小的目的。用相同的方法可以进行多元线性方程的参数估计，还可以在自变量中引入虚拟变量以增加方程的拟合程度。对于非线性的方程的参数估计，可以在进行样本数据的线性化处理之后，再按以上步骤进行参数估计。 图 附-21

66 八、相关系数分析 （一）简介：使用“相关系数”分析工具来确定两个区域中数据的变化是否相关，即，一个集合的较大数据是否与另一个集合的较大数据相对应（正相关）；或者一个集合的较小数据是否与另一个集合的较小数据相对应（负相关）；还是两个集合中的数据互不相关（相关性为零）。 （二）操作步骤：采用图附-3表中的数据，可按如下步骤计算变量x,y,z之间的相关系数。 1．用鼠标点击表中待分析数据的任一单元格。 2．选择“工具”菜单的“数据分析”子菜单，用鼠标双击数据分析工具中的“相关系数”选项 。

67 3 ．填写完“相关系数”对话框，单击“确定”按扭即可得到各个变量的相关系数矩阵，结果如图附-22所示。
图 附-22 （三）结果说明：以上下三角矩阵计算出三个变量x,y,z两两之间的相关系数，如变量x,y之间的相关系数为：0.929,所以可以判断x,y之间存在着较高的正线性相关关系。

68 九、季节变动时间序列的分解分析 （一）简介：分解分析法是时间序列分析和预测过程中常用的统计方法。该方法假设时间序列是趋势变动(T)、循环变动(C)、随机变动(I)综合影响的结果，分解过程首先从原始序列中消除随机变动，然后在此基础上，分别识别出循环变动和趋势变动的变化模式。假设的合理性、方法的科学性和操作的简易性使分解分析法在经济预测中得到了较为广泛的应用。下面结合具体例子介绍在Excel中如何实现时间序列的分解分析。如图附-23所示，表中A1至B13单元格是1996至1998年各季度某海滨城市旅游人口数(千人),试预测1999年各季度旅游人口数。 （二）操作步骤： 1．计算一次移动平均，消除随机波动。在C3单元格填入公式“=AVERAGE(B2:B5)”，然后用“填充柄”将公式复制到C4:C11单元格。 2 ． 中心化移动平均数。在D4单元格输入公式“=AVERAGE(C3:C4)” ，再用“填充柄”将公式复制到D5:D11单元格。 3． 计算季节指数。在E4单元格输入公式“=B4/D4” ，然后用“填充柄”将公式复制到E5:E11单元格。

69 4. 计算平均季节指数。在F4单元格中输入公式“=AVERAGE(E4,E8)” ，然后用“填充柄”将公式复制到F5:F7单元格。
图 附-23

70 5. 计算调整后的季节指数。为了让季节指数的总平均为1，必须对季节指数加以调整。先在G4单元格中输入公式“=F4/AVERAGE($F$4:$F$7)” ，再用“填充柄”将公式复制到G5:G7单元格。此时，G4:G7就是最终计算出的四个标准化之后季节指数，的季节指数，它反映的是原始时间序列中的循环变动。然后，根据G4:G7单元格数值，将四个季节指数分别填充到G2:G13的其它对应季节的空白单元格内，供下一步计算使用。 6. 消除旅游人数序列中的季节变动。在H2单元格中输入公式“=B2/G2” ，然后将公式复制到H3:H13单元格。此时，H列就是消除季节变动之后的旅游人数时间序列。 图 附-24

71 7. 对消除季节变动的旅游人数进行回归分析。在I列填入时间序号1至15。选择“工具”菜单的“数据分析”子菜单，双击“回归”选 项，弹出回归分析对话框。按图附-24所示的方式填写对话框。然后单击“确定”按扭，即可得到剔除了季节波动的时间序列的线性趋势模型。线性模型估计结果如图附-25所示，其中B35单元格是线性趋势模型的截距，B36单元格是斜率。 图 附-25

72 8. 预测。在G14:G17单元格中分别填入刚才计算出的四个调整后的季节指数，在B14单元格中输入公式“=($B$35+I14*$B$36)*G14” ，其中“($B$35+I14*$B$36)”只是趋势变动的预测结果，乘以G14（季节指数）后，则反映的是趋势变动和季节循环变动叠加之后的预测结果。然后利将此公式复制到B15:B17单元格，B14:B17单元格中就是1999年各个季度旅游人数的预测值，如图附-26所示。 图 附-26 （三）结果分析：以上步骤完成了整个季节时间序列的分析和预测过程。使用了分解分析的方法，能将时间数列的各个影响因数都分解出来，由这种方法得到的预测模型和预测结果都比直接对时间序列使用回归分析要更为可靠合理。读者可以参考以上分析步骤，用类似的方法在Excel中进行月份时间序列、双循环变动时间序列等的分解分析和预测。