36附录四Excel在统计分析与数量方法中的应用.docx

1、36附录四 Excel在统计分析与数量方法中的应用附录四 Excel在统计分析与数量方法中的应用1 Excel简介Microsoft Excel是美国微软公司开发的Windows环境下的电子表格系统，它是目前应用最为广泛的办公室表格处理软件之一。Excel具有强有力的数据库管理功能、丰富的宏命令和函数、强有力的决策支持工具，它具有以下主要特点。（1）分析能力Excel除了可以做一些一般的计算工作外，还有400多个函数，用来做统计、财务、数学、字符串等操作以及各种工程上的分析与计算。Excel还专门提供了一组现成的数据分析工具，称为“分析工具库”， 这些分析工具为建立复杂的统计或计量分析工作带来

2、极大的方便。（2）操作简便（3）图表能力在Excel中，系统大约有100多种不同格式的图表可供选用，用户只要做几个简单的按键动作，就可以制作精美的图表。通过图表指南一步步的引导，可使用不同的选项，得到所需的结果，满意的话就继续，不满意则后退一步，重新修改选项，直到最后出现完美的图表。（4）数据库管理能力管理数据库可用专门的数据库管理软件，如FoxPro，Access，Clipper，Sybase等。在Excel中提供了类似的数据库管理功能，保存在工作表内的数据都是按照相应的行和列存储的，这种数据结构再加上Excel提供的有关处理数据库的命令和函数，使得Excel具备了能组织和管理大量数据的能力

3、。（5）宏语言功能利用Excel中的宏语言功能，用户可以将经常要执行的操作的全过程记录下来，并将此过程用一简单的组合按键或工具按扭保存起来。这样，在下一次操作中，只需按下所定义的宏功能的相应按键或工具按钮即可，而不必重复整个过程。例如，可以定义一个打开最后编辑文件且可以自动执行的宏，以后当用户打开Excel后，将自动打开上一次编辑的工作簿。在Excel中，高级用户可使用Visual Basic语言，进行宏命令的开发。利用宏命令，用户可以将Excel的下拉菜单和对话框更改或将图形按钮的说明更换，使它们更适合于用户的工作习惯和特殊要求。（6）样式功能在Excel中，用户可以利用各种文字格式化的工具

4、和制图工具，制作出美观的报表。所谓样式，就是将一些格式化的组合用一个名称来表示，以后要使用这些格式化的组合时，只要使用此名称即可，因此可大幅度地节省报表格式化的时间。（7）对象连接和嵌入功能利用对象连接和嵌入功能，用户可将其它软件（例如画笔）制作的图形插入到Excel 的工作表中。当需要更改图案时，只要在图案上双击鼠标键，制作该图案的程序就会自动打开，图案将出现在该图形编辑软件内，修改、编辑后的图形也会在Excel内显示出来。也可以将一个声音文件或动画文件嵌入到Excel工作表中，使工作表变成一幅声形并貌的报表。（8）连接和合并功能通常，每个工作在一张工作表上执行即可，早期的工作表软件都只能在

5、一张工作表上执行。但有时需要同时用到多张工作表，例如，公司内每个分公司每月都会有会计报表，要将各分公司的资料汇总起来，就需要用到连接和合并功能。Excel很容易将工作表连接起来，并进行汇总工作。Excel内一个工作簿可以存放许多工作表、图形等，每个工作簿文件最多可以由255张工作表组成。2 Excel基本操作2.1 Excel操作方法概述要完成任一项Excel操作一般都可以找到三种操作方法：鼠标操作、菜单操作和键盘命令操作。例如，想要将A1单元格的数据复制到A2单元格去，有如下几种操作方法：（1）鼠标操作法先用鼠标选中A1单元格，然后缓慢移动鼠标到A1单元格的右下角，当鼠标的形状变为黑色实心“

6、十”字形之后(以后称之为“添充柄”)，拖动鼠标到A2单元格，然后放开鼠标，则A1的数据就复制到A2单元格了。（2）菜单操作法先用鼠标选中A1单元格，选择“编辑”菜单中的“复制”命令，然后用鼠标选中A2单元格，再选择“编辑”菜单中的“粘贴”命令，数据就复制到A2单元格了。（3）键盘命令操作法直接用鼠标选中A2单元格，从键盘输入“A1”命令，则复制即告完成。以上是Excel中很典型的三种操作方法。在实际使用过程中，应根据实际情况，尽量选择三种方法中最简洁的操作方法，以提高操作速度。2.2 数据的输入输出操作（1）数据的手动输入建立一个新的Excel文件之后，便可进行数据的输入操作。Excel中以单

7、元格为单位进行数据的输入操作。一般用上下左右光标键，Tab键或用鼠标选种某一单元格，然后输入数据。Excel中的数据按类型不同通常可分为四类：数值型，字符型，日期型，和逻辑型。Excel根据输入数据的格式自动判断数据属于什么类型。如日期型的数据输入格式为“月/日/年”，“月-日-年”或“时：分：秒”。要输入逻辑型的数据，输入“true”（真）或“false”（假）即可。（2）公式生成数据Excel的数据也可由公式直接生成。例如：在当前工作表中A1和B1单元格中已输入了数值数据，欲将A1与B1单元格的数据相加的结果放入C1单元格中，可按如下步骤操作：用鼠标选定C1单元格，然后输入公式“=A1+B

8、1”或输入“=SUM(a1:b1)”,回车之后即可完成操作。C1单元格此时存放实际上是一个数学公式“A1+B1”，因此C1单元格的数值将随着A1、B1单元格的数值的改变而变化。Excel提供了完整的算术运算符,如（加）、（减）、*（乘）、（除）、（百分比）、（指数）和丰富的函数，如SUM（求和）、CORREL（求相关系数）、STDEV（求标准差）等，供用户对数据执行各种形式的计算操作，在Excel帮助文件中可以查到各类算术运算符和函数的完整使用说明。（3）复制生成数据Excel中的数据也可由复制生成。实际上，在生成的数据具有相同的规律性的时候，大部分的数据可以由复制生成。可以在不同单元格之间复

9、制数据，也可以在不同工作表或不同工作簿之间复制数据，可以一次复制一个数据，也可同时复制一批数据，为数据输入带来了极大的方便。普通单元格的复制结果与公式单元格的复制结果相差较大，下面分别予以说明。1普通单元格指的是非公式的单元格。普通单元格的复制，一般可以按如下步骤进行： ）拖动鼠标选定待复制的区域，选定之后该区域变为黑色。Excel可以进行整行、整列或整个表格的选定操作，例如，如果要选定表格的第一列，可直接用鼠标单击列标“A”， 如果要选定表格的第一行，可直接用鼠标单击行标“1”，如果要选定整个表格，可直接点击全选按扭。）选定区域之后，用鼠标右击该区域，选择“复制”，将区域内容复制到粘贴板之中

10、。可以发现该区域已被虚线包围。）用鼠标右击目标区域，选择“粘贴”，则单元格区域的复制即告完成。2公式单元格的复制，一般可分为两种，一种是值复制，一种是公式复制。值复制指的是只复制公式的计算结果到目标区域，公式复制指的是仅复制公式本身到目标区域。下面对它们的操作步骤分别予以说明。）值复制 拖动鼠标选定待复制区域。 用鼠标右击选定区域，选择“复制”选项。 用鼠标右击目标区域，再单击“选择性粘贴”子菜单。出现复制选项，选定“数值”选项，然后用鼠标单击“确定”按扭，则公式的值复制即告完成。）公式复制公式复制是Excel数据成批计算的重要操作方法，要熟练公式复制的操作首先要区分好两个概念：单元格的相对引

11、用与绝对引用。Excel中的公式中一般都会引用到别的单元格的数值，如果你希望当公式复制到别的区域之时，公式引用单元格不会随之相对变动，那么你必须在公式中使用单元格的绝对引用。如果你希望当公式复制到别的区域之时，公式引用单元格也会随之相对变动，那么你必须在公式中使用单元格的相对引用。在公式中如果直接输入单元格的地址，那么默认的是相对引用单元格，如果在单元格的地址之前加入“$”符号那么意味着绝对引用单元格。例如，在当前工作表中A1和B1单元格中已输入了数值数据，用鼠标选定C1单元格，然后输入公式“=A1+B1”,此公式引用的便是两个相对的单元格A1、B1, 也就是说,如果将该公式复制到C2的单元格

12、，公式所引用的单元格的地址将随着发生变化，公式将变为“=A2+B2”，如果将该公式复制到F100的单元格,那么公式将变为“=D100+E100”，这就是相对引用的结果，公式的内容随着公式的位置变化而相对变化。如果在C1单元格输入的是“=$A$1+$B$1”那么此公式引用的便是绝对的单元格，不论将公式复制到何处，公式的内容都不会发生变化。当然，绝对引用和相对引用亦可在同一公式之中混合交叉使用，例如，如果在C1单元中输入的是公式“=A$1+B$1”,那么意味着，公式的内容不会随着公式的垂直移动而变动，而是随着公式的水平移动而变动，如果将该公式复制到F100 单元格，那么公式将变为，“=D$1+E$

13、1”。可以作这样的归纳：公式中“$”符号后面的单元格坐标不会随着公式的移动而变动，而不带“$”符号后面的单元格坐标会随着公式的移动而变动。在实际的使用中，如果能把单元格的相对引用与绝对引用灵活应用到Excel的公式之中，能为数据成批准确运算带来极大的方便。2.3 与其它软件交换数据的方法在Excel中可以打开其它类型的数据文件，如FOXPRO系列的DBF数据库文件，文本文件，lotus1-2-3的数据文件等。具体操作方法如下：（1）在“文件”菜单中选择“打开”子菜单。（2）在“打开文件”对话框中选择所要打开的文件的类型及其所在的目录。（3）用鼠标双击该文件名，并按Excel提示步骤操作即可打开

14、该文件。Excel文件同样也可存为其它类型的数据文件，具体操作方法如下：（1）编辑好文件后，在“文件”菜单中选择“另存为”子菜单。（2）在“另存为”对话框中选择所要打开文件的类型及其所在的目录。（3）输入文件名之后，用鼠标单击“保存”按扭即可。3 Excel在描述统计中的应用在使用Excel进行数据分析时，要经常使用到Excel中一些函数和数据分析工具。其中，函数是Excel预定义的内置公式。它可以接受被称为参数的特定数值，按函数的内置语法结构进行特定计算，最后返回一定的函数运算结果。例如，SUM函数对单元格或单元格区域执行相加运算，PMT函数在给定的利率、贷款期限和本金数额基础上计算偿还额。

15、函数的语法以函数名称开始，后面是左圆括号、以逗号隔开的参数和右圆括号。参数可以是数字、文本、形如TRUE或FALSE的逻辑值、数组、形如#N/A的错误值，或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。Excel还提供了一组数据分析工具，称为“分析工具库”，在建立复杂的统计分析时，使用现成的数据分析工具，可以节省很多时间。只需为每一个分析工具提供必要的数据和参数，该工具就会使用适宜的统计或数学函数，在输出表格中显示相应的结果。其中的一些工具在生成输出表格时还能同时产生图表。如果要浏览已有的分析工具，可以单击“工具”菜单中的“数据分析”命令。如果“数据分析”命令没有

16、出现在“工具”菜单上，则必须通过“工具”菜单中的“加载宏”命令，在“加载宏”对话框中选择并启动它。3.1 描述统计工具（1）简介此分析工具用于生成对输入区域中数据的单变量分析，提供数据趋中性和易变性等有关信息。（2）操作步骤1用鼠标点击工作表中待分析数据的任一单元格。2选择“工具”菜单的“数据分析”子菜单。3用鼠标双击数据分析工具中的“描述统计”选项。4出现“描述统计”对话框，对话框内各选项的含义如下：输入区域：在此输入待分析数据区域的单元格范围。一般情况下Excel会自动根据当前单元格确定待分析数据区域。分组方式：如果需要指出输入区域中的数据是按行还是按列排列，则单击“行”或“列”。标志位于

17、第一行/列：如果输入区域的第一行中包含标志项（变量名），则选中“标志位于第一行”复选框；如果输入区域的第一列中包含标志项，则选中“标志位于第一列”复选框；如果输入区域没有标志项，则不选任何复选框，Excel将在输出表中生成适宜的数据标志。均值置信度：若需要输出由样本均值推断总体均值的置信区间，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度。第 K个最大/小值：如果需要在输出表的某一行中包含每个区域的数据的第K个最大/小值，则选中此复选框。然后在右侧的编辑框中，输入K的数值。输出区域：在此框中可填写输出结果表左上角单元格地址,用于控制输出结果的存放位置。整个输出结果分为两列,左边一列

18、包含统计标志项，右边一列包含统计值。根据所选择的“分组方式”选项的不同，Excel 将为输入表中的每一行或每一列生成一个两列的统计表。新工作表：单击此选项，可在当前工作簿中插入新工作表，并由新工作表的A1单元格开始存放计算结果。如果需要给新工作表命名，则在右侧编辑框中键入名称。新工作簿：单击此选项，可创建一新工作簿，并在新工作簿的新工作表中存放计算结果。汇总统计：指定输出表中生成下列统计结果，则选中此复选框。这些统计结果有：平均值、标准误差、中值、众数、标准偏差、方差、峰值、偏度、极差（全距）最小值、最大值、总和、样本个数。5填写完“描述统计”对话框之后，按“确定”按扭即可。3.2 直方图工具

19、（1）简介直方图工具，用于在给定工作表中数据单元格区域和接收区间的情况下，计算数据的个别和累积频率，可以统计有限集中某个数值元素的出现次数。例如，在一个有 50 名学生的班级里，可以通过直方图确定考试成绩的分布情况,它会给出考分出现在指定成绩区间的学生个数,而用户必须把存放分段区间的单元地址范围填写在在直方图工具对话框中的“接收区域”框中。（2）操作步骤1用鼠标点击表中待分析数据的任一单元格。2选择“工具”菜单的“数据分析”子菜单。3用鼠标双击数据分析工具中的“直方图”选项。4出现“直方图”对话框，对话框内主要选项的含义如下：输入区域：在此输入待分析数据区域的单元格范围。 接收区域（可选）：在

20、此输入接收区域的单元格范围，该区域应包含一组可选的用来计算频数的边界值。这些值应当按升序排列。只要存在的话，Excel将统计在各个相邻边界值之间的数据出现的次数。如果省略此处的接收区域，Excel将在数据组的最小值和最大值之间创建一组平滑分布的接收区间。标志：如果输入区域的第一行或第一列中包含标志项，则选中此复选框；如果输入区域没有标志项，则清除该复选框，Excel将在输出表中生成适宜的数据标志。输出区域：在此输入结果输出表的左上角单元格的地址。如果输出表将覆盖已有的数据，Excel会自动确定输出区域的大小并显示信息。柏拉图：选中此复选框，可以在输出表中同时显示按降序排列频率数据。如果此复选框

21、被清除，Excel将只按升序来排列数据。累积百分比：选中此复选框，可以在输出结果中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略以上结果。图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。5按需要填写完“直方图”对话框之后，按“确定”按扭即可。（3）结果说明：完整的结果通常包括三列和一个频率分布图，第一列是数值的区间范围，第二列是数值分布的频数，第三列是频数分布的累积百分比。3.3 利用Excel绘制散点图（1）简介散点图是观察两个变量之间关系程度最为直观的工具之一，利用Excel的图表向导，可以非常方便的创建并且改进一个散点图，也可以在

22、一个图表中同时显示两个以上变量之间的散点图。（2）操作步骤数据如图1所示，可按如下步骤建立变量x-y，x-z的散点图。 图1 数据表1拖动鼠标选定数值区域A2:C12 ，不包括数据上面的标志项。2选择“插入”菜单的“图表”子菜单，进入图表向导。3选择“图表类型”为“散点图”，然后单击“下一步”。4确定用于制作图表的数据区。Excel将自动把你前面所选定的数据区的地址放入图表数据区内。5. 在此例之中，需要建立两个系列的散点图，一个是x-y系列的散点图，一个是x-z 系列的散点图，因此，必须单击“系列”标签，确认系列1的“X 值”方框与“数值方框”分别输入了x,y 数值的范围，在系列2的“X 值

23、”方框与“数值方框”分别输入了x,z 数值的范围。在此例中，这些都是Excel已经默认的范围，所以，可忽略第5 步，直接单击“下一步”即可。6. 填写图表标题为“X-Y与X-Z散点图”，X 轴坐标名称为“X”与Y 轴坐标名称“Y/Z”，单击“下一步”。7. 选择图表输出的位置，然后单击“完成”按扭即生成图2的图表。 图2 散点图示意图（3）结果说明如图2所示，Excel中可同时生成两个序列的散点图，并分为两种颜色显示。通过散点图可观察出两个变量的关系，为变量之间的建立模型作准备。3.4 数据透视表工具（1）简介数据透视表是Excel中强有力的数据列表分析工具。它不仅可以用来作单变量数据的次数分

24、布或总和分析，还可以用来作双变量数据的交叉频数分析、总和分析和其它统计量的分析。（2）操作步骤如图3所示，表中列出学生两门功课评定结果，可按如下步骤建立交叉频数表。1选中图3中表格中有数据的任一单元格，然后选择“数据”菜单的“数据透视表”子菜单，进入数据透视表向导。2选择“Microsoft Excel数据清单或数据库”为数据源。单击“下一步”。 图3 数据表3选择待分析的数据的区域，一般情况下Excel会自动根据当前单元格确定待分析数据区域，因此你只要直接单击“下一步”按扭即可。4确定数据透视表的结构，在此例中，要建立的是一个交叉频数表，分别按语文和数学的成绩对学生的人数进行交叉频数分析，因

25、此可按图4将三个项目“学号”、“语文”、“数学”分别拖放到表格的指定部位，并且双击“求和项：学号”，将其改为记数项，然后单击“下一步”按扭。 图4 透视表示意图5选择数据透视表的显示位置之后，单击“完成按扭”，可出现如图5所示的数据透视表。图5 数据透视表（3）结果说明如图5的结果所示，数据透视表可以作为一个交叉频数分析工具。完成数据透视表之后，可按需要修改数据表的显示格式。例如，如果想要把表格中的频数替换成为百分比数。可以用鼠标右击频数的任一单元格，选择“字段”子菜单，单击“选项”按扭，将“数据显示方式”替换成为“占总和的百分比”，然后单击“确定”按扭即可。按同样方式，可将数据透视表修改成为

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

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

28、的框架。图6 二项分布框架表2先在B7至F7单元格分别输入概率计算公式: “=BINOMDIST(A7,6,0.3,0)”， “=BINOMDIST(A7,6,0.3,1)” “=C7-B7”， “=1-C7”， “=1-D7”这里BINOMDIST的语法为BINOMDIST(number_s,trials,probability_s,cumulative)其中number_s为试验成功的次数；trials为独立试验的次数；probability_s为每次试验中成功的概率；cumulative为一逻辑值，用于确定函数的形式。如果cumulative为 TRUE，函数BINOMDIST返回累积分

29、布函数，即至多number_s次成功的概率；如果为FALSE，返回概率密度函数，即number_s次成功的概率。3公式的拷贝。选取B7至F7单元格，拖动“填充柄”至F13单元格即可完成公式的拷贝操作。结果图7所示。图7 二项分布数据4下面开始创建二项分布图表。选取B7至B13单元格，选取“插入”菜单的“图表”子菜单。5选择“柱状图”，然后单击“下一步”。6单击“系列”标签，单击“分类(X)轴标志”框，并用鼠标选取A7至A13单元格为图表X轴的轴标，然后单击“下一步”。7分别键入图表名称“二项分布图”，X轴名称“成功次数”，Y轴名称“成功概率”，单击“完成”按扭即可生成二项分布图表。4.2 其它

30、分布的函数（1）函数CRITBINOM1说明：函数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。（2）函数HYPGEOMDIST： 1说明：函数HYPGEOMDIST返回超几何

31、分布。给定样本容量、总体容量和样本总体中成功的次数，函数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块，下面函数计算式计算出只有一块是焦糖巧克力的概率：YPGEOMDIST(1,4,8,20)=0.363261（3）函数NEGBINOMDIST： 1说明：函数NEGBINOMDIST返回负二项式分布。当每次试验成功概率固定时，函数 NEGBINOMDIST返回在到达指定次数成功之前，出现n次失败的概率。此函数与二项式分布相似，只是它的成功次数固定，试验总数为变量。与二项分布类似的是，试验次数被假设为自变量。2语法: NEGBINOMDIST(number_f,number_s,p