Excel在统计分析中的应用.pdf

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

1、ExcelExcelExcelExcel 在统计分析中的应用在统计分析中的应用编者编者吴灵英吴灵英丁斌鹰丁斌鹰目目录录EXCELEXCELEXCELEXCEL 在统计分析中的应用在统计分析中的应用.1 1 1 1前前言言.3 3 3 3目目录录.5 5 5 5第一节第一节中文中文 EXCELEXCELEXCELEXCEL 概述概述.1 1 1 1一、中文 EXCEL简介.1二、EXCEL的安装和启动.1第二节第二节 EXCELEXCELEXCELEXCEL 在描述统计中的应用在描述统计中的应用.3 3 3 3一、描述统计工具.3二直方图工具.4三、利用 EXCEL绘制散点图.5四、其它统计图的

2、绘制.6第三节第三节 EXCELEXCELEXCELEXCEL 在推断统计中的应用在推断统计中的应用.9 9 9 9一、双样本等方差假设检验（重复数相等的两样本平均数T检验）.9二、双样本异方差假设检验（重复数相等的两样本平均数T检验也可用此法）.10三、“T-检验：成对双样本均值分析”.10四、“Z-检验：双样本均值分析”.10五、列联表分析.11六、单因素方差分析.11七、交叉分组无重复双因素方差分析.12八、交叉分组有重复双因素方差分析.12九、线性回归分析.14十、多元线性方程.16十一、非线性方程.16十二、相关系数分析工具.16ExcelExcelExcelExcel在统计分析中的

3、应用在统计分析中的应用在统计分析中的应用在统计分析中的应用1第一节第一节中文中文 ExcelExcelExcelExcel 概述概述一、中文一、中文一、中文一、中文 ExcelExcelExcelExcel 简介简介简介简介Microsoft Excel 是美国微软公司开发的Windows 环境下的电子表格系统，它是目前应用最为广泛的办公室表格处理软件之一。自Excel 诞生以来Excel 历经了Excel5.0、Excel95、Excel97 和Excel2000等不同版本。Excel 具有强有力的数据库管理功能、丰富的宏命令和函数、能制作各种统计图表，并且Excel在“分析工具库”中，提供

4、了一组“数据分析”工具。在使用Excel 进行数据分析时，要经常使用到Excel 中一些函数。函数是Excel 预定义的内置公式。它可以接受被称为参数的特定数值，按函数的内置语法结构进行特定计算，最后返回一定的函数运算结果。例如，SUM 函数对单元格或单元格区域执行相加运算。Excel 还提供了一组数据分析工具，称为“分析工具库”，在建立复杂的统计分析时，使用现成的数据分析工具，可以节省很多时间。只需为每一个分析工具提供必要的数据和参数，该工具就会使用适宜的统计或数学函数，在输出表格中显示相应的结果。其中的一些工具在生成输出表格时还能同时产生图表。二、二、二、二、ExcelExcelExcel

5、Excel 的安装和启动的安装和启动的安装和启动的安装和启动以下以Excel2000 为例，说明Excel 的安装过程。如果没作特别说明，本章介绍其它内容都是针对Excel2000软件的。Excel2000 作为0ffice2000 系统中的一个应用程序，它的工作平台为window98，最好是window2000及以上的版本。对中文Excel 而言，最好安装在中文windows系统平台之上。Excel 在统计分析中的应用2图图-1-1ExcelExcel 的工作界面的工作界面（一）安装（一）安装Excel2000Excel20001在CD 一R0M 中放入0ffice 2000光盘。2单击Wi

6、ndows 的“开始”按钮并选择“运行”命令。在弹出的“运行”对话框，“打开”框中寻找光盘上的安装程序“SETUP.EXE”，找到后单击“确定”按钮即可。（如果光盘具有自动执行的功能，则可省略以上步骤，直接点取屏幕上的“开始安装”按扭。）3开始安装后系统将要求用户选择安装模式：“典型”、“自定义”、“快速”。用户可根据需要选择安装模式，一般可以选择“典型”安装方式，那么安装软件将装入最常用的Office 组件，如果用户想定制所要安装的组件，那么可以选择“自定义”安装方式，而“快速”安装方式是一种最节约磁盘空间的安装方式，它仅装入Office 运行中不能缺少的组件。在剩下安装过程中则用户按屏幕提

7、示选择。4单击“工具”菜单中的“数据分析”命令可以浏览已有的分析工具。如果在“工具”菜单上没有“数据分析”命令，应在“工具”菜单上运行“加载宏”命令，在“加载宏”对话框中复选上“分析工具库”，“确定”后，就会从CD 一R0M中补充安装“分析工具库”。（二）（二）ExcelExcel 的启动与退出的启动与退出启动Excel 的常用方法是：单击任务栏上的“开始”按扭，此时屏幕上出现一个弹出式菜单，将鼠标指向“程序”项后，屏幕出现另一个弹出菜单，单击“Microsoft Excel”,就可以启动Excel 系统，此时屏幕上出现如图图-1-1的Excel主工作画面。若安装Excel时，生成了“快捷工具

8、栏”，则双击其中的Excel 按扭也可立即启动Excel。退出Excel 常用的方法是，用鼠标单击标题栏的按扭或进入“文件”菜单栏，单击“退出”选项。ExcelExcelExcelExcel在统计分析中的应用在统计分析中的应用在统计分析中的应用在统计分析中的应用3第二节第二节 ExcelExcelExcelExcel 在描述统计中的应用在描述统计中的应用一、描述统计工具一、描述统计工具一、描述统计工具一、描述统计工具（一）简介（一）简介：此分析工具用于生成对输入区域中的单变量变量数据的量化特征分析量化特征分析，提供数据趋中性和易变性等有关信息。例1 分析126头基础母羊的体重资料的集中性（如平

9、均数）和离散性（即变异性）。（教教材材P19P19）（二）操作步骤：（二）操作步骤：1将本例的数据资料（即教材中表2-5中资料）输入到Excel单元格中，如图图-2 2（数据顺序有改动）。图图-2-2-2-22用鼠标点击工作表中待分析数据的任一单元格。3选择“工具”菜单的“数据分析”子菜单。4用鼠标双击数据分析工具中的“描述统计”选项。5出现“描述统计”对话框，对话框内各选项的含义如下：输入区域：在此输入待分析数据区域的单元格范围。本例为A1：I14。分组方式：如果需要指出输入区域中的数据是按行还是按列排列，则单击“行”或“列”。标志位于第一行/列：如果输入区域的第一行中包含标志项(变量名)，

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

11、的每一行或每一列生成一个两列的统计表。特别说明：如果相对特别说明：如果相对126126个数据进行整个数据进行整体体Excel 在统计分析中的应用4分析，则要把分析，则要把126126个数据输入一行或一列。个数据输入一行或一列。新工作表：单击此选项，可在当前工作簿中插入新工作表，并由新工作表的 A1 单元格开始存放计算结果。如果需要给新工作表命名，则在右侧编辑框中键入名称。新工作簿：单击此选项，可创建一新工作簿，并在新工作簿的新工作表中存放计算结果。汇总统计：指定输出表中生成下列统计结果，则选中此复选框。这些统计结果有：平均值、标准误差、中值、众数、标准偏差、方差、峰值、偏斜度、极差（全距）最小

12、值、最大值、总和、样本个数。6 6填写完“描述统计”对话框之后，按“确定”按扭即可。图图-3-3假设本例填写结果如图-3，就输出图-4结果（由于篇幅，只显示由第1-3行计算的结果）。图-4（三）结果说明（三）结果说明：描述统计工具可生成以下统计指标，按从上到下的顺序其中包括样本的平均值（），标准误差()，组中值(Medium)，众数(Mode),样xnS本标准差(S)，样本方差(S2),峰度值，偏度值，极差(Max-Min),最小值(Min),最大值(Max),样本总和，样本个数（n）和一定显著水平下总体均值的置信区间。二直方图工具二直方图工具二直方图工具二直方图工具（一）简介（一）简介：直方

13、图工具，用于制作相当于教材中的数据资料的次数分布表和次数分布图。例例例例2222 将126头基础母羊的体重资料（见图-2）整理成次数分布表和直方图Excel 在统计分析中的应用5（或柱形图、矩形图）。（教（教材材P19P19）（二）操作步骤：（二）操作步骤：1在本例数据表格的下方按升序将分组的边界值边界值按顺序输入，作为接收区域。如图-5。图图-5-5-5-52用鼠标点击表中待分析数据的任一单元格。3选择“工具”菜单的“数据分析”子菜单。4用鼠标双击数据分析工具中的“直方图”选项。5出现“直方图”对话框，对话框内主要选项的含义如下：输入区域、标志、输出区域的意义同前。柏拉图、累积百分比一般不选

14、，意义见“帮助”。接收区域（可选）接收区域（可选）：在此输入接收区域的单元格范围。只要填写，Excel 将统计在各个相邻边界值之间的数据出现的次数。如果省略此处的接收区域，Excel 将在数据组的最小值和最大值之间创建一组平滑分布平滑分布的接收区间。图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。6按需要填写完“直方图”对话框之后，按“确定”按扭即可。如果本例的对话框填写如图-6，就出现如图-7结果。图图-6-6-6-6图图-7-7-7-7三、利用三、利用三、利用三、利用 ExcelExcelExcelExcel 绘制散点图绘制散点图绘制散点图绘制散点图（一）简介（一）简介：

15、散点图是观察两个变量之间关系程度最为直观的工具之一，利用Excel 的“图表向导”，可以非常方便的创建并且改进一个散点图，也可以在一个图表中同时显示两个以上变量之间的散点图。为变量之间的建立模型作准为变量之间的建立模型作准Excel 在统计分析中的应用6备备。在两变量相关、回归分析之前，往往根据实际观测值在直角坐标纸上作散点图，确定两变量的关系的趋向是直线，还是曲线，以及曲线的类型，再进行回归分析，求出回归方程。例例例例3333 测定黑龙江雌性鲟鱼体长（cm）和体重（kg），作散点图。（教（教材材P194P194）（二）操作步骤：（二）操作步骤：1将数据输入单元格。见图8。2拖动鼠标选定数值区

16、域A2:B9，不包括数据上面的标志项。3选择“插入”菜单的“图表”子菜单，进入图表向导。4选择“图表类型”为“散点图”，然后单击“下一步”。5确定用于制作图表的数据区。Excel 将自动把你前面所选定选定的数据区的地址放入图表数据区的内。6.如果需要建立两个系列的散点图，如果一个是x-y 系列的散点图，一个是x-z系列的散点图。必须单击“系列系列”标签，确认系列1 的“X 值”方框与“数值方框”分别输入了x,y 数值的范围，在系列2 的“X 值”方框与“数值方框”分别输入了x,z 数值的范围。然后单击“下一步”。7.填写图表标题为“X-Y 与X-Z 散点图”，X 轴坐标名称为“X”与Y 轴坐标

17、名称“Y/Z”，单击“下一步”。8.选择图表输出的位置，然后单击“完成”按扭即生成图-8 的图表。图图-8-8（三）结果说明：（三）结果说明：如果同时作“X-Y 与X-Z 散点图”，Excel 中可同时生成两个序列的散点图，并分为两种颜色显示。四、其它统计图的绘制四、其它统计图的绘制四、其它统计图的绘制四、其它统计图的绘制（一）长条图（一）长条图1 1 1 1、单式长条图、单式长条图 例例44 某品种鸡杂种二代冠形分离情况。（P22P22）Excel 在统计分析中的应用7图图-9-9-9-9步骤：输入数据-选定数据区域-“插入”-“图表”-在图表向导选“条形图”-按提示选择-得图-9结果。2

18、2、复式长条图、复式长条图 例例55 作几种动物性食品的营养成分的长条图。（P23P23）同上例类似的操作，得图-10结果。图图-10-10-10-10（二）圆图（饼图）（二）圆图（饼图）用上例数据，作饼图。按以上类似操作，可得图-11结果。图图图图-11-11-11-11（三）折线图、面积图、雷达图、气泡图（三）折线图、面积图、雷达图、气泡图Excel“插入”-“图表”菜单里提供了许多其他统计图工具，操作与前类似Excel 在统计分析中的应用8第三节第三节 ExcelExcelExcelExcel 在推断统计中的应用在推断统计中的应用一、双样本等方差假设检验（重复数相等的两样本平均数一、双样

19、本等方差假设检验（重复数相等的两样本平均数一、双样本等方差假设检验（重复数相等的两样本平均数一、双样本等方差假设检验（重复数相等的两样本平均数 t t t t检验）检验）检验）检验）（一）简介（一）简介：双样本等均值检验是在一定置信水平之下,在两个总体方差相等总体方差相等的假设之下，检验两个总体均值的差值等于指定平均差指定平均差的假设是否成立的检验。例 6 某家禽研究所对粤黄鸡进行饲养对比试验，试验时间为 60 天，增重结果如表，问两种饲料对粤黄鸡的增重效果有无显著差异？（P80）（二）操作步骤（二）操作步骤：以上例子可按如下步骤进行假设检验。1将试验结果数据输入工作表中的相应单元格。见图-1

20、2。2.选择“工具”菜单的“数据分析”子菜单，双击“t-t-t-t-检验检验:双样本等方差假双样本等方差假设设”选项，则弹出图-13 所示对话框。3.分别填写变量1 的区域：$B$1:$B$9，变量2 的区域：$D$1:$D$9，由于我们进行的是等均值的检验，填写假设平均差为0，由于数据的首行包括标志项选择标志选项，所以选择“标志”选项，再填写显著水平为0.05,然后点击“确定”按扭。则可以得到图-14所示的结果。图图-12-12-12-12图图-13-13-13-13Excel 在统计分析中的应用9图图-14-14-14-14（三）结果分析（三）结果分析：如图-14中所示，表中分别给出了两组

21、的平均值、方差和样本个数。其中，合并方差是样本方差加权之后的平均值,Df 是假设检验的自由度它等于样本总个数减2，t 统计量是两个样本差值减去假设平均差之后再除于标准误差的结果，“P(T=t)单尾”是单尾检验的显著水平，“t 单尾临界”是单尾检验t 的临界值，“P(T=t)双尾”是双尾检验的显著水平，“t 双尾临界”是双尾检验t 的临界值。由下表的结果可以看出t 统计量均小于两个临界值，所以，在5%显著水平下，不能拒绝两个总体均值相等的假设。二、双样本异方差假设检验（重复数相等的两样本平均数二、双样本异方差假设检验（重复数相等的两样本平均数二、双样本异方差假设检验（重复数相等的两样本平均数二、

22、双样本异方差假设检验（重复数相等的两样本平均数 t t t t检验也可用此法）检验也可用此法）检验也可用此法）检验也可用此法）此分析工具与双样本等方差假设检验不同，该检验是在两个数据集的方差两个数据集的方差不不等等的前提假设之下进行两总体均值差额的检验，故也称作异方差 t-检验。当进当进行分析的样本个数不同时，也可使用此检验。行分析的样本个数不同时，也可使用此检验。操作步骤同前。操作步骤同前。三、三、三、三、“t-t-t-t-检验：成对双样本均值分析检验：成对双样本均值分析检验：成对双样本均值分析检验：成对双样本均值分析”此分析工具可以进行成对双样本学生氏t-检验，用来确定样本均值是否不等。此

23、此 t-t-检验并不假设两个总体的方差是相等的检验并不假设两个总体的方差是相等的。操作步骤同前。操作步骤同前。四、四、四、四、“z z z z-检验：双样本均值分析检验：双样本均值分析检验：双样本均值分析检验：双样本均值分析”此分析工具可以进行方差已知方差已知的双样本均值z-检验。此工具用于检验两个总体均值之间存在差异的假设。操作步骤同前。操作步骤同前。Excel 在统计分析中的应用10五、列联表分析五、列联表分析五、列联表分析五、列联表分析列联表分析经常用来判断同一个调查的对象的两个特性之间是否存在明显相关关系。由于资料数据为整数，手工计算较容易，计算机运算需输入较多函数，较复杂，这里不介绍

24、。六、单因素方差分析六、单因素方差分析六、单因素方差分析六、单因素方差分析（一）简介（一）简介：单因素方差分析可用于检验两个或两个以上的总体均值相等的假设是否成立。此方法是对双均值检验（如 t-检验）的扩充。检验假定总体是服从正态分布的，总体方差是相等的，并且随机样本是独立的。例例 77 某水产研究所为了比较四种不同配合饲料对鱼的饲喂效果，选取了条件基本相同的鱼 20 尾，随机分成四组，投喂不同饲料，经一个月试验以后，各组鱼的增重结果列于下表。（P97P97）（二）操作步骤（二）操作步骤1将数据输入单元格。见图-15。2.选择“工具”菜单的“数据分析”子菜单，双击“方差分析:单因素方差分析”选

25、项，弹出单因素方差分析对话框。3.按图-16 所示方式填写对话框。然后单击“确定”按扭。图图-15-15图图-16-16Excel 在统计分析中的应用11图图-17-17-17-17（三）结果分析（三）结果分析：通过将F值与一定置信水平下的F 临界值F crit 比较，可以判断均值相等的假设是否成立。P-value 列，是单尾概率值，表明如果四个总体均值相等的假设成立的话，得到如上样本结果的概率是0.002942。七、交叉分组无重复双因素方差分析七、交叉分组无重复双因素方差分析七、交叉分组无重复双因素方差分析七、交叉分组无重复双因素方差分析EXCEL 的“数据分析”中提供的有重复和无重复双因素

26、方差分析，但都是针有重复和无重复双因素方差分析，但都是针对固定模型，交叉分组。对固定模型，交叉分组。例例例例8888 为研究雌激素对子宫发育的影响，现有4窝不同品系未成年的大白鼠，每窝3只，随机分别注射不同剂量的雌激素，然后在相同条件下试验，并称得它们的子宫重量，见表，试作方差分析。（P118）操作与上例、下例相似。八、交叉分组有重复双因素方差分析八、交叉分组有重复双因素方差分析八、交叉分组有重复双因素方差分析八、交叉分组有重复双因素方差分析 例例例例 9999 为了研究饲料中钙磷含量对幼猪生长发育的影响，将钙(A)、磷(B)在饲料中的含量各分 4 个水平进行交叉分组试验。先用品种、性别、日龄

27、相同，初始体重基本一致的幼猪 48 头，随机分成 16 组，每组 3 头，用能量、蛋白质含量相同的饲料在不同钙磷用量搭配下各喂一组猪，经两月试验，幼猪增重结果(kg)列于表，试分析钙磷对幼猪生长发育的影响。P125P125操作步骤：操作步骤：1特别注意数据输入方式（见图-18）。Excel 在统计分析中的应用12图图-18-18-18-182选择“工具”菜单的“数据分析”子菜单，双击“可重复双因子方差分析”选项，出现对话框。注意填写每一样本的行数即为试验重复数。注意填写每一样本的行数即为试验重复数。填写对话框如图-19。图图-19-19-19-193得如图-20结果。Excel 在统计分析中的

28、应用13图图-20-20-20-20九、线性回归分析九、线性回归分析九、线性回归分析九、线性回归分析（一）简介：（一）简介：线性回归分析通过对一组观察值使用“最小二乘法”直线拟合，用来分析单个因变量是如何受一个或几个自变量影响的。例例10101010 在四川白鹅的生产性能研究中，得到如下一组关于雏鹅重（g）与70日龄重(g)的数据，试建立70日龄重(y)与雏鹅重(x)的直线回归方程。（P179P179）（二）操作步骤（二）操作步骤1输入试验结果数据。见图-21。图图-21-21-21-212选择“工具”菜单的“数据分析”子菜单，双击“回归”选项，弹出回归分析对话框。其中主要选项的含义如下：Y

29、值输入区域：在此输入对因变量数据区域，该区域必须由单列数据组成；X 值输入区域：在此输入对自变量数据区域，Excel 将对此区域中的自变量从左到右按升序排列，自变量的个数最多为最多为 1616；置信度：如果需要在汇总输出表中包含附加的置信度信息，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度，95%为默认值；Excel 在统计分析中的应用14常数为零：如果要强制回归线通过原点，则选中此复选框；输出选择同前。残差（即离回归）：如果需要以残差输出表的形式查看残差，则选中此复选框；标准残差：如果需要在残差输出表中包含标准残差，则选中此复选框；残差图：如果需要生成一张图表，绘制每个自变量及

30、其残差，则选中此复选框；线形拟合图：如果需要为预测值和观察值生成一个图表，则选中此复选框；正态概率图：如果需要绘制正态概率图，则选中此复选框。2 按如图-22的方式填写对话框：X 值输入区域为C1:C13,Y 值输入区域为B1:B13,并选择“标志”和“线性拟合图”两个复选框，输出区域填D1，然后单击“确定”按扭即可得图-23结果。图-22Excel 在统计分析中的应用15图图-23-23-23-23（三）结果分析（三）结果分析按照如上的操作步骤即可得到图-23表的计算结果。结果可以分为四个部分：第一部分是回归统计的结果包括多元相关系数、可决系数R2、调整之后的相关系数、回归标准差以及样本个数

31、。第二部分是方差分析的结果（即回归关系的F检验）：包括可解释的离差、残差、总离差和它们的自由度以及由此计算出的F 统计量和相应的显著水平。第三部分是回归方程的截距和斜率的估计值以及它们的估计标准误差、t 统计量大小（回归关系的t检验）、双边拖尾概率值、以及估计值的上下界。根据这部分的结果可知回归方程为Y=21.71217*X+582.185。第四部分是样本散点图，其中蓝色的点是样本的真实散点图，红色的点是根据回归方程进行样本历史模拟的散点。如果觉得散点图不够清晰可以用鼠标拖动图形的边界达到控制图形大小的目的。十、多元线性方程十、多元线性方程十、多元线性方程十、多元线性方程可用相同的方法可以进行

32、的参数估计，还可以在自变量中引入虚拟变量以增加方程的拟合程度。十一、非线性方程十一、非线性方程十一、非线性方程十一、非线性方程对于非线性的方程的参数真实散点图，红色的点是根据回归方程进行样本历史模拟的散点。如果估计，可以在进行样本数据的线性化处理之后，再按以上步骤进行参数估计。十二、相关系数分析工具十二、相关系数分析工具十二、相关系数分析工具十二、相关系数分析工具（一）简介（一）简介：此分析工具可用于判断两组数据之间的关系。可以使用“相关系数”分析工具来确定两个区域中数据的变化是否相关，即，一个集合的较大数据是否与另一个集合的较大数据相对应（正相关）；或者一个集合的较小数据是否与另一个集合的较

33、小数据相对应（负相关）；还是两个集合中的数据互不相关（相关Excel 在统计分析中的应用16系数为零）。（二）操作步骤：（二）操作步骤：采用图-21表中的数据，可按如下步骤计算变量x,y,z之间的相关系数。1 用鼠标点击表中待分析数据的任一单元格。2 选择“工具”菜单的“数据分析”子菜单。3 用鼠标双击数据分析工具中的“相关系数”选项。4填写完“相关系数”对话框，单击“确定”按扭即可得到各个变量的相关系数矩阵，结果如图-24所示。图图-24-24（三）结果说明（三）结果说明：以上下三角矩阵计算出变量两两之间的相关系数，如70日龄重与雏鹅重的的相关系数为：0.977404,所以可以判断两之间存在着较高的正线性相关关系。