excel在统计学中应用.ppt

《excel在统计学中应用.ppt》由会员分享，可在线阅读，更多相关《excel在统计学中应用.ppt（75页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、市场经济呼唤统计学统计学学是研究客是研究客观事物数量特征和数量关系的方法事物数量特征和数量关系的方法论学科，能学科，能够告告诉人人们如何通如何通过打开几扇窗口去探索一个未知的世界，教会打开几扇窗口去探索一个未知的世界，教会人人们怎怎样用一种新的方式来思考用一种新的方式来思考问题，是一，是一门很很实用的学科。用的学科。大至国家的宏大至国家的宏观决策，小至企事决策，小至企事业单位的微位的微观管理，都离不开管理，都离不开统计的的应用。用。现代市代市场经济对统计信息的需求急信息的需求急剧增加，增加，对统计理理论与方法提出了更高的要求。与方法提出了更高的要求。社会社会发展展问题、经济可持可持续发展展问题

2、、国、国际竞争力争力问题、金融、金融风险管理管理问题、保、保险精算精算问题、人口与社会保障、人口与社会保障问题、环境保境保护问题、科学研究探索、科学研究探索问题等等，等等，这些都迫切地等待着我些都迫切地等待着我们去深入地去深入地研究。研究。统计分析分析软件是数据分析的主要工具件是数据分析的主要工具完整的数据分析完整的数据分析过程包括：程包括：数据的收集数据的收集数据的整理数据的整理数据的分析数据的分析结果的解果的解释和推和推论统计学学为数据分析数据分析过程提供一套完整的程提供一套完整的科学科学的方法的方法论。统计软件件为数据分析提供了数据分析提供了实现手段。手段。SAS(Ver9.1)-真正的

3、巨无霸。被誉为国际上的标准统计软件和最权威的组合式优秀统计软件。常用统计软件简介SPSS(Ver13)-统计软件中的贵族最易上手的统计软件最易上手的统计软件NCSS NCSS 国产统计软件的佼佼者国产统计软件的佼佼者DPSDPSExcel 它严格说来并不是统计软件，但作为数据表格软件，必然有一定统计计算功能。功能全面，系功能全面，系统地集成了多种成熟的地集成了多种成熟的统计分析方分析方法；法；有完善的数据定有完善的数据定义、操作和管理功能；、操作和管理功能；方便地生成各种方便地生成各种统计图形和形和统计表格；表格；使用方式使用方式简单，有完，有完备的的联机帮助功能；机帮助功能；软件开放性好，能

4、方便地和其他件开放性好，能方便地和其他软件件进行数据交行数据交换。一、Excel基本介绍二、数据管理功能三、统计图的绘制四、统计分析功能Microsoft Excel是当前功能强大、技术先进和使用方便灵活的电子表格系统。Excel的历史渊源1987年10月，美国Microsoft公司推出视窗版Excel（超越），被公认达到软件技术的最佳专业水平；1993年，微软又把Word和Excel集成在Office办公套装软件内，使其能相互共享数据。Microsoft Office办公室自动化集成软件的重要组成部分，它是目前应用最为广泛的表格处理软件之一。自Excel诞生以来，主要历经了Excel3.0、

5、Excel4.0、Excel5.0、Excel95、Excel97和Excel2000,Excel2003等不同版本。随着版本的不断提高，Excel强大的数据处理功能和操作的简易性逐渐走入了一个新的境界，整个系统的智能化程度也不断提高，它甚至可以在某些方面判断用户的下一步操作，使用户操作大为简化。这些特性，已使Excel成为现代办公软件重要的组成部分。Excel的应用领域在财务、金融、统计、办公自动化等领域广泛应用Excel的应用特点操作简单易学易用用户界面友好 表格处理：可制作各类表格，并具有强大的处理功能图形处理：系统有100种以上的图表供选用，还可自行绘制数据运算：有400多个函数,可完

6、成复杂的表内和表间计算，并可进行适当的统计分析数据库管理：将有组织的数据清单当作数据库处理标题栏菜单栏工具栏编辑栏列标单元格工作区行号滚动按钮表格签滚动条任务窗格下拉按钮Office 助手工作簿在Excel中创建的文件叫工作簿，由1个或多个工作表组成，最多可以包括255个工作表。是Excel管理数据的文件单位，相当于人们日常生活中的“文件夹”，它们以独立文件的形式存储在磁盘上。工作表工作表就是人们平常所说的电子表格，由一些横向和纵向的网格组成，横向称为行，纵向称为列，在网格中可以填写不同的数据。一个工作表最多可有65536行，256列。行号工作表由65536行组成，每行用一个数字进行编号列标每

7、列用字母来标识，称为列标，列标用字母 A 到 IV 表示，共 256 列Excel的基本概念的基本概念单元格Excel的工作表实际上是一个二维表格，单元格就是这个表格中的一个“格子”。单元格是输入数据、处理数据及显示数据的基本单位。单元格中的内容可以是数字、文本或计算公式等，最多可包含32000个字符。数据列表数据排序数据筛选数据的分类与汇总按单变量排序根据某一列的数据按单一关键字对行数据进行排序。单击要排序的变量列中的任一单元格，再单击常用工具栏的【升序】或【降序】按钮即可。按多变量排序根据某一列的数据排序时，会遇到有相同数据的情况，此时可采用按多个列排序的方法。单击要排序的数据列表中的任一

8、单元格，再单击【数据】【排序】【排序】对话框。有时需要从工作表的数据行中找出满足一定条件的几行或几列数据，这就要用到Excel的数据筛选功能。数据筛选将工作表中所有不满足条件的数据行暂时隐藏起来，只显示那些满足条件的数据行（数据并没有丢失）。Excel提供了两种不同的筛选方式：自动筛选高级筛选n n分分分分类类类类汇汇汇汇总总总总是是是是对对对对数数数数据据据据列列列列表表表表按按按按某某某某一一一一字字字字段段段段值值值值进进进进行行行行分分分分类类类类，将将将将同同同同类类类类别别别别数数数数据据据据放放放放在在在在一一一一起起起起，并并并并分分分分别别别别为为为为各各各各类类类类数数数数

9、据据据据进进进进行行行行统统统统计计计计汇汇汇汇总总总总（求求求求总总总总和和和和，平平平平均均均均值值值值、最最最最大大大大值值值值、最最最最小小小小值值值值、统计个数等）统计个数等）统计个数等）统计个数等）n n操作过程：操作过程：操作过程：操作过程：n n先对数据列表依据分类字段排序先对数据列表依据分类字段排序先对数据列表依据分类字段排序先对数据列表依据分类字段排序n n单单单单击击击击【数数数数据据据据】菜菜菜菜单单单单【分分分分类类类类汇汇汇汇总总总总】【分分分分类类类类汇汇汇汇总总总总】对话框。对话框。对话框。对话框。条图圆图线图散点图描述性统计推断统计分析 在使用Excel进行数

10、据分析时，要经常使用到Excel中一些函数和数据分析工具。其中，函数是Excel预定义的内置公式。它可以接受被称为参数的特定数值，按函数的内置语法结构进行特定计算，最后返回一定的函数运算结果。例如，SUM 函数可对单元格或单元格区域执行相加运算。函数的语法以函数名称开始，后面分别是左圆括号、以逗号隔开的参数和右圆括号。参数可以是数字、文本、形如 TRUE 或 FALSE 的逻辑值、数组、形如#N/A 的错误值，或单元格地址。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。Excel还提供了一提供了一组数据分析工具，称数据分析工具，称为“分析工具分析工具库”，在建立复在建立复杂的

11、的统计分析分析时，使用，使用现成的数据分析工具，可以成的数据分析工具，可以节省很多省很多时间。只需。只需为每一个分析工具提供必要的数据和参数，每一个分析工具提供必要的数据和参数，该工具就会使用适宜的工具就会使用适宜的统计或数学函数，在或数学函数，在输出表格中出表格中显示相示相应的的结果。果。其中的一些工具在生成输出表格时还能同时产生图表。要浏览已有的分析工具，可以单击“工具”菜单中的“数据分析”命令。如果“数据分析”命令没有出现在“工具”菜单上，则必须运行“安装”程序来加载“分析工具库”，安装完毕之后，再通过“工具”菜单中的“加载宏”命令，在“加载宏”对话框中选择并启动它。在安装Microso

12、ft office办公软件时必须选择“完全安装”，分析工具库才会被安装到电脑上。操作过程：单击【工具】菜单【加载宏】【加载宏】对话框。“工具工具”菜单菜单描述性统计中常用的统计量包括：均数、中位数、几何均数等平均数指标极差、方差、标准差、四分位数间距、变异系数等变异指标最小值、最大值Excel提供了两种不同的计算方式：利用函数进行描述统计利用分析数据库进行描述统计操作过程：单击【工具】菜单【数据分析】【数据分析】对话框【描述统计】【描述统计】对话框。（参看视频）计算算术均数的函数计算几何均数的函数计算中位数的函数=AVERAGE(B5:B14)=GEOMEAN(B5:B14)=MEDIAN(B

13、5:B14)=MAX(B5:B14)-MIN(B5:B14)=STDEV(B5:B14)=QUARTILE(B5:B14,3)-QUARTILE(B5:B14,1)=B19/B15*100二项分布工具其他几种主要分布函数随机抽样工具样本推断总体假设检验单因素方差分析线性回归分析 （一）简介：在Excel中想要计算二项分布的概率值、累积概率，需要利用Excel的工作表函数BINOMDIST。函数 BINOMDIST 适用于固定次数的独立实验，实验的结果只包含成功或失败二种情况，且每次实验成功的概率固定不变。例如，已知次品概率的情况下，函数 BINOMDIST可以计算抽查10个样品中发现2个次品的

14、概率。以下例子说明如何在Excel中计算二项分布的概率值，以及如何进一步建立二项分布图表。（二）操作步骤：例子如下所示，一个推销员打了六个电话，推销成功的概率是0.3，那么可以按以下步骤建立推销成功次数的概率分布图表。1、如图附-1所示，先在Excel之下建立好概率分布表格的框架。图 附-12、如图附-2 所示,先在B7至F7单元格分别输入概率计算公式。图 附-23、公式的拷贝。选取B7至F7单元格，然后移动鼠标至F7单元格的 右下角，使其成为黑色实心十字星状，一般称之为“填充柄”，拖动“填充柄”至F13单元格即可完成公式的拷贝操作。结果图附-3所示。图 附-3 4下面开始创建二项分布图表。选

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

16、RUE，函数 BINOMDIST 计算累积分布函数概率值，如果为FALSE，计算概率密度函数概率值。（一）函数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

17、.75。（二）函数HYPGEOMDIST：1说明：函数HYPGEOMDIST计算超几何分布。给定样本容量、总体容量和样本总体中成功的次数，函数 HYPGEOMDIST 计算出样本取得给定成功次数的概率。使用该函数可以解决有限总体的问题，其中每个观察值只有两种取值，或者为成功或者为失败，且给定样本区间的所有子集有相等的发生概率。2.语法：HYPGEOMDIST(sample_s,number_sample,population_s,number_population)Sample_s：样本中成功的次数。Number_sample：样本容量。Population_s：样本总体中成功的次数。Numb

18、er_population：样本总体的容量。3.举例：容器里有20块巧克力，8 块是焦糖的，其余 12 块是果仁的。如果从中随机选出 4 块，下面函数计算式计算出只有一块是焦糖巧克力的概率：HYPGEOMDIST(1,4,8,20)=0.363261。（一）简介：Excel中的RAND()函数可以产生大于等于 0 小于 1 的均匀分布随机数，RAND()不带任何参数运行，每次计算时时都将产生一个新的随机数，如果将RAND()函数从一个单元格复制或移动到另外一个单元格，RAND()函数也将重新计算一个新的数值。RAND()函数可以被用来作为不重复抽样调查的工具。（二）操作步骤：如图附-4所示，1

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

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

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

22、”选项,然后点击“确定”按扭即可。3.输入样本数据，和用户指定的置信水平0.95,如图附-13所示。4.为样本数据命名。选定D1:D11单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项,然后点击“确定”按扭，最后得到图附-14所示的计算结果。图 附-6（三）结果说明：以上例子说明如何交叉组合使用Excel 的公式和函数，以构造出一个能实现样本推断总体有关计算的Excel工作表。实际上，在用Excel进行数据统计处理之时，许多统计功能可以使用和上例类似的方法，通过组合使用Excel的各类统计函数和公式加以实现。图 附-7 （一）简介：假设检验是统计推断中的重要内容。

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

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

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

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

27、。例子如图附-17表中所示，一产品制造商雇佣销售人员向销售商打电话。制造商想比较四种不同电话频率计划的效率，他从销售人员中随机选出32名，将他们随机分配到4种计划中，在一段时期内记录他们的销售情况已经在表中列出，试问其中是否有一种计划会带来较高的销售水平。图 附-10（二）操作步骤 1.选择“工具”菜单的“数据分析”子菜单，双击“方差分析:单因素方差分 析”选项，弹出单因素方差分析对话框。2.按图附-11所示方式填写对话框。然后单击“确定”按扭即可。图 附-11 （三）结果分析：按照如上的操作步骤即可得到图附-12的计算结果。其中表格的第二部分则是方差分析的结果。SS列分别给出了四个分组的组间

28、方差、组内方差以及总方差，DF列分别给出了对应方差的自由度，MS列是平均值方差，由SS除于DF得到，它是总体方差的两个估计值。F列是F统计量的计算结果，如果四个总体均值相等的假设成立的化，它应该服从F分布，即近似为1，它是最终的计算结果，通过将它与一定置信水平下的F临界值F crit比较，可以判断均值相等的假设是否成立，在本例中，1.677612.94668,所以不能拒绝四个总体均值相等的假设。P-value列，是单尾概率值，表明如果四个总体均值相等的假设成立的化，得到如上样本结果的概率是19.442%,即得到以上样本并不是小概率事件，同样也得到不能拒绝四个总体均值相等的假设的结论。按相似方法

29、可进行无重复双因素方差分析，有重复双因素方差分析。图 附-12 （一）简介：线性回归分析通过使用“最小二乘法”对样本数据进行直线拟合，用于分析单个因变量是如何受一个或几个自变量影响的。例子如图附-13所示，表中是我国1987年至1997年的布匹人均产量和人均纱产量，试用线性回归分析的方法分析两组数据之间的关系。图 附-13（二）操作步骤 1选择“工具”菜单的“数据分析”子菜单，双击“回归”选项，弹出回归分析对话框。对话框主要选项的含义如下：Y 值输入区域，在此输入因变量数据区域，该区域必须由单列数据组成；X 值输入区域，在此输入对自变量数据区域，自变量的个数最多为16；置信度，如果需要在汇总输

30、出表中包含附加的置信度信息，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度，95%为默认值；常数为零，如果要强制回归线通过原点，则选中此复选框；输出区域，在此输入输出表左上角单元格的地址，用于控制计算结果的输出位置。汇总输出表至少需要有七列的宽度，包含的内容有 anova表、系数、y 估计值的标准误差、r2 值、观察值个数，以及系数的标准误差；新工作表，单击此选项，可在当前工作簿中插入新工作表，并由新工作表的 A1 单元格开始显示计算结果，如果需要给新工作表命名，则在右侧的编辑框中键入名称；新工作簿，单击此选项，可创建一新工作簿，并在新工作簿中的新工作表中显示计算结果；残差，如果需

31、要以残差输出表的形式查看残差，则选中此复选框；标准残差，如果需要在残差输出表中包含标准残差，则选中此复选框；残差图，如果需要生成一张图表，绘制每个自变量及其残差，则选中此复选框；线形拟合图，如果需要为预测值和观察值生成一个图表，则选中此复选框；正态概率图，如果需要绘制正态概率图，则选中此复选框。2按如下方式填写对话框：X值输入区域为$B$1:$B$12,Y值输入区域为$C$1:$C$12,并选择“标志”和“线性拟合图”两个复选框，然后单击“确定”按扭即可。（三）结果分析 按照如上的操作步骤即可得到图附-14下表的计算结果。结果可以分为四个部分，第一部分是回归统计的结果包括多元相关系数、可决系数

32、R2、调整之后的相关系数、回归标准差以及样本个数。第二部分是方差分析的结果包括可解释的离差、残差、总离差和它们的自由度以及由此计算出的F统计量和相应的显著水平。第三部分是回归方程的截距和斜率的估计值以及它们的估计标准误差、t统计量大小双边拖尾概率值、以及估计值的上下界。根据这部分的结果可知回归方程为Y=8.46433*X-18.288。第四部分是样本散点图，其中蓝色的点是样本的真实散点图，红色的点是根据回归方程进行样本历史模拟的散点。如果觉得散点图不够清晰可以用鼠标拖动图形的边界达到控制图形大小的目的。用相同的方法可以进行多元线性方程的参数估计，还可以在自变量中引入虚拟变量以增加方程的拟合程度。对于非线性的方程的参数估计，可以在进行样本数据的线性化处理之后，再按以上步骤进行参数估计。图 附-143 填写完“相关系数”对话框，单击“确定”按扭即可得到各个变量的相关系数矩阵，结果如图附-15所示。（三）结果说明：以上下三角矩阵计算出三个变量x,y,z两两之间的相关系数，如变量x,y之间的相关系数为：0.929,所以可以判断x,y之间存在着较高的正线性相关关系。图 附-15 何祥珲 邓延玲 余佩 黄愉涵 王钰婷