试验三(1) EXCEL-统计分析中的应用.ppt

《试验三(1) EXCEL-统计分析中的应用.ppt》由会员分享，可在线阅读，更多相关《试验三(1) EXCEL-统计分析中的应用.ppt（39页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、EXCEL的基本统计功能的基本统计功能及在区间估计与假设检验中的应用及在区间估计与假设检验中的应用田间试验与生物统计学实验三1.1.目的要求：目的要求：（1）熟悉EXCEL软件的基本操作命令，掌握利用EXCEL的统计功能实际频数分布和描述性统计的操作全过程。（2）熟悉假设检验的步骤和过程，重点掌握参数估计、总体均值假设检验、总体比例假设检验、单因子方差和双因子方差分析。2.教学内容：（1）Microsoft EXCEL是一个设计精良、功能齐全的办公软件。它除了具有我们常用的办公功能外，还是一个十分强大而且非常易于操作的数据统计工具。EXCEL2003/2007提供的基本统计功能主要包括描述性统

2、计、频数统计、等级和百分数等方法。这些统计方法主要利用EXCEL统计函数或数据分析中的描述统计过程来实现。（2）Excel 还专门提供了一组现成的数据分析工具，称为“分析工具库”，这些分析工具为建立复杂的统计或计量分析工作带来极大的方便。（3）教材习题3.2、3.3、3.4、3.5的软件实现；（4）教材习题5.10的软件实现；（5）教材例题6.13的软件实现。3.3.实验步骤实验步骤（1 1）进入）进入EXCELEXCEL界面，了解菜单及其功能；界面，了解菜单及其功能；（2 2）数据录入，了解行、列及单元格的概念；）数据录入，了解行、列及单元格的概念；（3 3）进入制图菜单，了解图表类型、图要

3、素、数据）进入制图菜单，了解图表类型、图要素、数据引用等；引用等；（4 4）打开函数菜单，了解常用统计函数，重点演示）打开函数菜单，了解常用统计函数，重点演示平均数、方差、标准差函数的应用；平均数、方差、标准差函数的应用；（5 5）了解在单元格中编辑公式、计算的过程；）了解在单元格中编辑公式、计算的过程；（6 6）启动）启动EXCELEXCEL的统计分析工具并进行的统计分析工具并进行t t测验和方测验和方差分析。差分析。统计数据处理分析方法的统计数据处理分析方法的应用软件应用软件统计应用软件附 属 型独 立 型EXCELLOTUSSAS(Statistical Analysis System)

4、SPSS（Statistical Program for Social Sciences)第一章第一章 EXCELEXCEL的统计数据处理分析功能概述的统计数据处理分析功能概述EXCEL2000中文版是美国Microsoft公司开发的电子表格软件n n具有强大的电子表格处理功能n n附带有内容丰富的统计数据处理功能（统计函数和统计数据处理分析宏）一、一、EXCELEXCEL的统计函数的统计函数n nEXCELEXCEL提供了大约提供了大约8080个统计函数。个统计函数。n n在在EXCELEXCEL运行过程中调用统计函数主要采用两种方法：运行过程中调用统计函数主要采用两种方法：1 1、在工作薄

5、的单元格中直接输入统计函数的函数名称在工作薄的单元格中直接输入统计函数的函数名称（必须在统计函数名称前加（必须在统计函数名称前加“=”符号），立即就会弹符号），立即就会弹出该函数的初始值输入对话框；或在工作薄的单元格中出该函数的初始值输入对话框；或在工作薄的单元格中输入输入“=”=”符号后，查找工作薄左上方的符号后，查找工作薄左上方的“名称名称”显示显示格内出现的函数选择表，选择某个函数名称，同样会得格内出现的函数选择表，选择某个函数名称，同样会得到该函数的初始值输入对话框。到该函数的初始值输入对话框。2 2、利用粘贴函数按钮利用粘贴函数按钮“f fx x “调用，单击调用，单击“f fx x

6、 “，即会弹，即会弹出一个出一个EXCELEXCEL函数选择表，选择其中的函数选择表，选择其中的“统计统计”选项，选项，再单击，又会弹出一个再单击，又会弹出一个EXCELEXCEL统计函数选择表，选定需统计函数选择表，选定需调用的统计函数名称，同样会弹出该函数的初始值输入调用的统计函数名称，同样会弹出该函数的初始值输入对话框。对话框。Excel中的统计函数一AVEDEVAVEDEVAVERAGEAVERAGEAVERAGEAAVERAGEABETADISTBETADISTBETAINVBETAINVBINOMDISTBINOMDISTCHIDISTCHIDISTCHIINVCHIINVCHIT

7、ESTCHITESTCONFIDENCECONFIDENCECORRELCORRELCOUNTCOUNTCOUNTACOUNTACOVARCOVARCRITBINOMCRITBINOMDEVSQDEVSQEXPONDISTEXPONDISTFDISTFDISTFINVFINVFISHERFISHERFISHERINVFISHERINVFORECASTFORECASTFREQUENCYFREQUENCYFTESTFTESTGAMMADISTGAMMADISTGAMMAINVGAMMAINVGAMMALNGAMMALNGEOMEANGEOMEANGROWTHGROWTHHARMEANHARMEAN

8、HYPGEOMDISHYPGEOMDIST TINTERCEPTINTERCEPTKURTKURTLARGELARGELINESTLINESTLOGESTLOGESTLOGINVLOGINVLOGNORMDISTLOGNORMDISTMAXMAXMAXAMAXAExcel中的统计函数二MEDIANMEDIANMINMINMINAMINAMODEMODENEGBINOMDISTNEGBINOMDISTNORMDISTNORMDISTNORMINNORMINV VNORMSDISTNORMSDISTNORMSINVNORMSINVPEARSONPEARSONPERCENTILEPERCENTILE

9、PERCENPERCENTRANKTRANKPERMUTPERMUTPOISSONPOISSONPROBPROBQUARTILEQUARTILERANKRANKRSQRSQSKEWSKEWSLOPESLOPESMALLSMALLSTANDARSTANDARDIZEDIZESTDEVSTDEVSTDEVASTDEVASTDEVPSTDEVPSTDEVPASTDEVPASTEYXSTEYXTDISTTDISTTINVTINVTRENDTRENDTRIMMEANTRIMMEANTTESTTTESTVARVARVARAVARAVARPVARPVARPAVARPAWEIBULLWEIBULLZTESTZ

10、TESTMAXMAXMAXAMAXA二、加载二、加载EXCELEXCEL数据分析宏程序数据分析宏程序1、打开EXCEL（2000版本）后，如果在“工具”项的下拉菜单中没有“数据分析”命令出现，则说明EXCEL尚未加载分析工具宏程序。2、点击“工具”菜单中的“加载宏”，在弹出的“加载宏”对话框中勾选“分析工具库”，然后点击确定。EXCEL的“工具”菜单里即会出现“数据分析”的命令选项。ExcelExcel中的统计宏中的统计宏 方差分析方差分析 (anovaanova)相关系数分析工具相关系数分析工具 协方差分析工具协方差分析工具“描述统计描述统计”分析工具分析工具“指数平滑指数平滑”分析工具分析

11、工具 “傅立叶分析傅立叶分析”分析工分析工具具 “F-F-检验：双样本方差检验：双样本方差分析分析”分析工具分析工具“排位与百分比排位排位与百分比排位”分析工具分析工具“移动平均移动平均”分析工具分析工具 t-t-检验分析检验分析 “随机数发生器随机数发生器”分析工分析工具具 “直方图直方图”分析工具分析工具“回归分析回归分析”分析工具分析工具 “抽样分析抽样分析”分析工具分析工具 “z-z-检验：双样本平均差检验检验：双样本平均差检验”分析工具分析工具 第二章第二章 描述统计的描述统计的EXCELEXCEL应用应用n n统计数据的分组和整理：直方图n n统计数据分布特征值的计算：样本均值、样

12、本方差、样本标准差、中位数、众数、峰度、偏度等一、直一、直 方方 图图例1 将某班50名学生概率统计考试成绩顺序排列如下：50 51 54 58 59 61 61 62 63 64 65 66 67 68 69 69 70 71 72 72 72 73 74 74 74 75 75 75 75 76 77 78 79 80 81 82 83 84 84 85 86 86 87 88 90 91 91 95 97 99 根据这些数据进行统计分组和制作统计图表。步步 骤骤1 1、进入进入EXCELEXCEL并打开新工作薄，在并打开新工作薄，在A A单元列中逐个输入单元列中逐个输入原始数据，然后在原

13、始数据，然后在B B单元列中输入各组的分组上限。一单元列中输入各组的分组上限。一般般上限上限均取均取“10”10”的倍数减的倍数减 1 1，而分组的，而分组的下限下限将默认为将默认为“10”10”的倍数的倍数。2 2、输入例输入例1 1中中5050个原始数据。在个原始数据。在B1B1单元中输入标志名称，单元中输入标志名称，在在B2B2单元中输入单元中输入“59”59”作为第一组的分组上限，在作为第一组的分组上限，在B3B3单单元中输入元中输入“69”69”作为第二组的分组上限，选取作为第二组的分组上限，选取B2B2：B3B3，向下拖动所选区域右下角的填充柄可以填充到需要的分向下拖动所选区域右下

14、角的填充柄可以填充到需要的分组单元。组单元。3 3、从工具菜单中下拉选择从工具菜单中下拉选择“数据分析数据分析/直方图直方图”，点击确，点击确定按钮，打开直方图分析选项框。定按钮，打开直方图分析选项框。4 4、在直方图选项框的在直方图选项框的“输入区域输入区域”中输入含有原始数据中输入含有原始数据单元的范围，本例为单元的范围，本例为$A$1:$A$51$A$1:$A$51，更为简便的是直接，更为简便的是直接用光标圈选用光标圈选A A列的数据。此外，如果引用范围包括标列的数据。此外，如果引用范围包括标志值的名称，须勾选志值的名称，须勾选“标志标志”选项。选项。5 5、在在“接收区域接收区域”中输

15、入包含有分组标志值单元范围的中输入包含有分组标志值单元范围的引用，本例为引用，本例为$B$1:$B$6$B$1:$B$6，通常这些分组区间的界限，通常这些分组区间的界限（上限）必须按升序排列。（上限）必须按升序排列。6 6、在在“输出区域输出区域”中输入想让输出表显示其范围的左上中输入想让输出表显示其范围的左上角单元引用（必须是空的单元），注意防止表格与图角单元引用（必须是空的单元），注意防止表格与图表以及原始数据的覆盖和重叠。本例取表以及原始数据的覆盖和重叠。本例取$D$1$D$1。7 7、如果勾选如果勾选“图表输出图表输出”复选框，除了在复选框，除了在EXCELEXCEL工作工作表上得到一

16、个频率分布表外，还可得到另一与之相对表上得到一个频率分布表外，还可得到另一与之相对应的直方图。应的直方图。8 8、在在EXCELEXCEL数据分析数据分析/直方图分析工具的对话框中输入直方图分析工具的对话框中输入完毕后，单击确定。输出表的完毕后，单击确定。输出表的$D$D列为分组的上限，列为分组的上限，$E$E列为各组的频数。列为各组的频数。9 9、对输出的图表可以进行各种各样的修改并进行格式化，对输出的图表可以进行各种各样的修改并进行格式化，修改后的直方图可以与原始数据放在一起，也可以输修改后的直方图可以与原始数据放在一起，也可以输出到新的工作表中。出到新的工作表中。操作演示见操作演示见EX

17、CELEXCEL文件文件“直方图、描述统计直方图、描述统计”二、统计数据分布特征值的计算二、统计数据分布特征值的计算 统计数据分布特征值包括：n n分布集中趋势指标样本均值、中位数、众数n n分布离散趋势指标样本方差、样本标准差、标准误差、峰度n n分布非对称性趋势指标偏度1 1、进入进入EXCELEXCEL并打开新工作薄，在并打开新工作薄，在A A单元列中逐个输入单元列中逐个输入需测定分析的变量数列数据，必须将这些数据的有关需测定分析的变量数列数据，必须将这些数据的有关数据值输在工作表的同一列中，并且在第一个数值上数据值输在工作表的同一列中，并且在第一个数值上方的单元格中键入有关的标志名称，

18、以便在输出图表方的单元格中键入有关的标志名称，以便在输出图表的分析结果中能够明显地定义数据的名称。的分析结果中能够明显地定义数据的名称。2 2、从从“工具工具”菜单中选择菜单中选择“数据分析数据分析”命令，弹出统计命令，弹出统计分析对话框，双击分析对话框，双击“描述统计描述统计”，显示对话框。，显示对话框。3 3、在在“输入区域输入区域”里输入含有测定数据单元的范围，本里输入含有测定数据单元的范围，本例为例为$A$1:$A$51$A$1:$A$51，更为简便的是直接用光标圈选，更为简便的是直接用光标圈选A A列的列的数据。这里还包括变量数列的标志名称。数据。这里还包括变量数列的标志名称。步步

19、骤骤4 4、勾选勾选“标志位于第一行标志位于第一行”选项。选择该框是说明输选项。选择该框是说明输入区域和接收区域的单元格范围引用中，第一行为标入区域和接收区域的单元格范围引用中，第一行为标志名称，而非数据，并且在输出的测定结果中能够显志名称，而非数据，并且在输出的测定结果中能够显示数据被定义的名称或意义。示数据被定义的名称或意义。5 5、在输出选项中勾选在输出选项中勾选“汇总统计汇总统计”，即可得到分布特，即可得到分布特征值的测定结果。如果还想知道分析数据中排序为第征值的测定结果。如果还想知道分析数据中排序为第K K个最大值的变量值，可选个最大值的变量值，可选“第第K K大值大值”的选项框，的

20、选项框，并在其序号框中输入想要知道的序号值，如想知道第并在其序号框中输入想要知道的序号值，如想知道第2 2个最大值，即在序号框中输入个最大值，即在序号框中输入2 2，一般的默认值为，一般的默认值为1 1，即最大值。同理可在，即最大值。同理可在“第第K K小值小值”的选项框中作选的选项框中作选择。择。6、在输出选项框中必须确定所要显示的输出结果所处的工作表区域，有两种选择，即新设工作表和原始工作表。如输出结果将位于新设工作表中，可以在选项框中输入新设工作表的名称。7、完成上述步骤后，单击确定。操作演示见EXCEL文件“直方图、描述统计”50名学生概率统计成绩平均74.76标准误差1.649875

21、691中位数74.5众数75标准差11.66638289方差136.1044898峰度-0.394813481偏度-0.034508798区域49最小值50最大值99求和3738观测数50最大(1)99最小(1)50ss2最大值最大值最小值最小值样本容量样本容量n第三章第三章 概率计算概率计算n n二项分布概率的计算和概率曲线图的编制n n正态分布概率的计算和概率曲线图的编制n n动态概率分布曲线的编制一、二项分布概率的计算和概率曲线一、二项分布概率的计算和概率曲线图的编制图的编制1 1、点概率点概率P(X=k)P(X=k)计算计算：直接输入：直接输入“=BINOMDIST(kBINOMDIS

22、T(k,n,p,n,p,0)”0)”或在或在BINOMDISTBINOMDIST函数公式菜单中分别输入参数，其函数公式菜单中分别输入参数，其中中“0”0”也可换成也可换成“false”.false”.2 2、累计概率累计概率P(XkP(Xk)计算计算：直接输入：直接输入“=BINOMDIST(kBINOMDIST(k,n,p,n,p,1)”1)”或在或在BINOMDISTBINOMDIST函数公式菜单中分别输入参数，其函数公式菜单中分别输入参数，其中中“1”1”也可换成也可换成“true”.true”.3 3、二项曲线图编制二项曲线图编制：选择二项分布的全部点概率值，点：选择二项分布的全部点概

23、率值，点击击“工具工具”栏的栏的“图表向导图表向导”，在弹出的选项框标准类，在弹出的选项框标准类型中选择型中选择“折线图折线图”，在子图表类型的图案中选择不带，在子图表类型的图案中选择不带点子的折线图案，按确定后，按照对话提示继续下去，点子的折线图案，按确定后，按照对话提示继续下去，最后点击完成最后点击完成.二、正态分布概率的计算和概率二、正态分布概率的计算和概率曲线图的编制曲线图的编制1、概率密度值f(x)计算：直接输入“=NORMDIST(x,mean,standard_dev,0)”或在NORMDIST函数公式菜单中分别输入参数.2、分布函数值F(x)计算：直接输入“=NORMDIST(

24、x,mean,standard_dev,1)”或在NORMDIST函数公式菜单中分别输入参数。标准正态分布函数值计算也可直接输入“=NORMSDIST(z)”.3 3、正态曲线图编制正态曲线图编制：1 1）输入）输入x x值时，通常采用值时，通常采用比平均值低比平均值低3 3倍标准差倍标准差的数量的数量作为初始量，其每个变量之间的增量可以设计为作为初始量，其每个变量之间的增量可以设计为标准标准差的差的5%5%。如作均值为。如作均值为0 0、标准差为、标准差为1 1的正态分布曲线图的正态分布曲线图表，应从表，应从X1(=0-3*1=-3)X1(=0-3*1=-3)开始开始,增量为增量为0.05(

25、=0.05*1).0.05(=0.05*1).2 2）选定正态分布的全部概率密度值，注意不要包括第）选定正态分布的全部概率密度值，注意不要包括第一行的标志名称。点击一行的标志名称。点击“工具工具”栏的栏的“图表向导图表向导”，在弹出的选项框标准类型中选择在弹出的选项框标准类型中选择“折线图折线图”，在子图，在子图表类型的图案中选择不带点子的折线图案，按确定后，表类型的图案中选择不带点子的折线图案，按确定后，按照对话提示继续下去，最后点击完成按照对话提示继续下去，最后点击完成.三、动态概率分布曲线的编制三、动态概率分布曲线的编制1、动态二项概率分布曲线的编制 设置数值调控按钮：设置数值调控按钮：

26、1 1）在在“视图视图”选项的下拉菜单中下拉到选项的下拉菜单中下拉到“工具栏工具栏”中中选择选择“窗体窗体”项并单击，出现窗体按钮的选择菜单，项并单击，出现窗体按钮的选择菜单，点击点击“滚动条滚动条”，并把它拉到，并把它拉到B4B4和和B7B7单元格变成数值单元格变成数值调控按钮。调控按钮。2 2）把鼠标放置在数值调控按钮图标上变成手形并右击，把鼠标放置在数值调控按钮图标上变成手形并右击，在弹出的对话框中选择在弹出的对话框中选择“设置控件格式设置控件格式”。3 3）B4B4的的“设置控件格式设置控件格式”栏中栏中“单元格链接单元格链接”中输中输入入“A4”A4”，B7B7的的“设置控件格式设置

27、控件格式”栏中栏中“单元格链接单元格链接”中输入中输入“B7”B7”，再在，再在A7A7单元格中输入单元格中输入“=B7/100”=B7/100”。2 2、动态二项概率分布曲线的编制动态二项概率分布曲线的编制 设置数值调控按钮：设置数值调控按钮：1 1）在在“视图视图”选项的下拉菜单中下拉到选项的下拉菜单中下拉到“工具栏工具栏”中中选择选择“窗体窗体”项并单击，出现窗体按钮的选择菜单，项并单击，出现窗体按钮的选择菜单，点击点击“滚动条滚动条”，并把它拉到，并把它拉到D2D2和和F2F2单元格变成数值单元格变成数值调控按钮。调控按钮。2 2）把鼠标放置在数值调控按钮图标上变成手形并右击，把鼠标放

28、置在数值调控按钮图标上变成手形并右击，在弹出的对话框中选择在弹出的对话框中选择“设置控件格式设置控件格式”。3 3）D2D2的的“设置控件格式设置控件格式”栏中栏中“单元格链接单元格链接”中输中输入入“D2”D2”，F2F2的的“设置控件格式设置控件格式”栏中栏中“单元格链接单元格链接”中输入中输入“F2”F2”，再在，再在E2E2、G2G2单元格中分别输入单元格中分别输入“=D2”=D2”、“=F2/10”=F2/10”。统计分析工具的安装与调用统计分析工具的安装与调用统计分析工具的安装与调用统计分析工具的安装与调用1.1.安装安装安装安装 在安装在安装在安装在安装excelexcel时在设

29、置中，选择加载宏选项；时在设置中，选择加载宏选项；时在设置中，选择加载宏选项；时在设置中，选择加载宏选项；在已安装好的系统中增添分析工具功能，选择菜单在已安装好的系统中增添分析工具功能，选择菜单在已安装好的系统中增添分析工具功能，选择菜单在已安装好的系统中增添分析工具功能，选择菜单栏中栏中栏中栏中:工具工具工具工具加载宏：选中分析工具。加载宏：选中分析工具。加载宏：选中分析工具。加载宏：选中分析工具。2.2.调用调用调用调用 工具菜单工具菜单工具菜单工具菜单-分析工具分析工具分析工具分析工具3.3.分析方法分析方法分析方法分析方法 方差分析、两样本资料的均数检验、方差分析、两样本资料的均数检验

30、、方差分析、两样本资料的均数检验、方差分析、两样本资料的均数检验、回归与相关、回归与相关、回归与相关、回归与相关、F F检验、描述性统计等。检验、描述性统计等。检验、描述性统计等。检验、描述性统计等。第四章第四章 统计分析工具统计分析工具配对配对t检验检验两样本方差齐性检验两样本方差齐性检验等方差成组等方差成组t检验检验单因素方差分析单因素方差分析n n选择统计分析区域选择统计分析区域n n确定分组方式确定分组方式n n确定检验水准确定检验水准n n确定结果输出区域确定结果输出区域相关与回归相关与回归两种分析方法比较两种分析方法比较n n函数分析方法函数分析方法函数分析方法函数分析方法：可以进

31、行各种方法的计算，组合灵可以进行各种方法的计算，组合灵活，当原始数据改变时，结果也自动随之发生变化、活，当原始数据改变时，结果也自动随之发生变化、公式可以粘贴。缺点是较为复杂，每个函数只能产生公式可以粘贴。缺点是较为复杂，每个函数只能产生一个结果，有时需要多个函数组合使用才能完成一项一个结果，有时需要多个函数组合使用才能完成一项分析工作，对使用者的统计基础知识求较高。分析工作，对使用者的统计基础知识求较高。n n数据分析工具数据分析工具数据分析工具数据分析工具：操作十分简便直观，对操作者方法操作十分简便直观，对操作者方法本身的要求较少，结果全面，而且表格化，一次操作本身的要求较少，结果全面，而且表格化，一次操作即可完成。缺点是即可完成。缺点是ExcelExcel中所带的统计分析工具较少，中所带的统计分析工具较少，不能满足需要，分析结果不随原始数据的变化而自动不能满足需要，分析结果不随原始数据的变化而自动改变，若原始数据发生变化，必须重新进行操作，才改变，若原始数据发生变化，必须重新进行操作，才能得到新的结果、不能进行粘贴操作。能得到新的结果、不能进行粘贴操作。