Excel_软件在数理统计中的应用.doc

Excel 软件在统计分析中的运用第一节 概述一、Excel软件简介二、Excel 中的统计分析功能第二节 基本运算函数一、基本分布的计算二、数据的基本统计量的计算三、数据的排序与定位第三节 描述性统计方法一、散点图二、直方图三、箱线图第四节 假设检验与方差分析函数一、假设检验方法二、方差分析方法第五节 回归分析一、一元线性回归分析二、多元线性回归分析第十章 Excel 软件在统计分析中的运用当今时代称之为数字化信息时代，随着现代科学技术的飞速发展，我们已进入一个利用和开发信息资源的信息社会。在生产、商业活动、工程实验、科学研究等过程中，每天都会产生大量的数据，这些表面上看上去杂乱无章的数据，其实里面含有大量的有用信息，只有经过合理的分析和处理才能得到这些信息. 在许多问题中，我们面临的数据具有信息量大，范围广，变化快等特点，传统的人工处理手段无法适应社会和经济的高速发展对统计分析提出的要求，也难以提高数据分析和处理的速度和精度. 随着计算机硬件及软件技术的飞速发展，我们现在已经可以处理海量的数据，计算机技术在数理统计中的运用，主要是数据信息的存储、检索和统计资料的分析和检索.第一节 概述一、Excel软件简介功能强大的统计分析软件有SAS、SPSS等，这些软件功能强大，计算精度高，但是这些软件往往由于系统庞大、结构复杂，大多数非统计专业人员难以运用自如，而且其正版软件价格昂贵，是一般人难以承受的.Excel是办公室自动化中非常重要的一款软件，很多巨型国际企业都是依靠Excel进行数据管理。它不仅仅能够方便的处理表格和进行图形分析，其更强大的功能体现在对数据的自动处理和计算. Excel的数据处理功能在现有的文字处理软件中可以说是独占鳌头，几乎没有什么软件能够与它匹敌. 计算机上安装了微软(Microsoft)公司的办公软件Office后，随之就有了Excel，不需要另外投资，Excel的使用并不复杂，可通过联机帮助来学习其操作和功能.菜单栏工具栏格式栏垂直滚动条标题栏水平滚动条工作表名称编辑栏行号列号单元格C2状态栏图10-1 Excel 的用户界面窗口工作表区Excel的统计工作表函数用于对数据区域进行统计分析,Excel 中的工作表就像矩阵，Excel做计算往往是对工作表中某个区域进行，其统计分析函数中所用的数据区域用array 来表示，如A1:H1表示第1行的A列到H列共8个数，D2:D9表示D列的第2行到第15行共14数，B2:F15表示从B列到F列，从第2行到第15行共70个数据.二、Excel 中的统计分析功能第二节 基本运算函数一、基本分布的计算BINOMDIST(k, n, p, 0) 计算二项分布的分布律BINOMDIST(k, n, p, 1) 计算二项分布的累积分布HYPGEOMDIST( 0, k ，M, N )计算超几何分布的分布律；HYPGEOMDIST( 1, k ，M, N )计算超几何分布的累积分布；POISSON(k, , 0) 计算泊松分布的分布律POISSON(k, , 1) 计算泊松分布的分布的累积分布EXPONDIST(x, , 0) 计算指数分布密度函数在处的函数值；EXPONDIST(x, , 1) 计算指数分布函数在处的函数值；NORMDIST() 计算正态分布的密度函数在处的函数值；NORMDIST() 计算正态分布分布函数在处的函数值；NORMSDIST() 计算标准正态分布分布函数在处的函数值；NORMSINV() 计算标准正态分布分布函数的反函数在处的函数值； CHIDIST(x, n) 计算分布函数在处的函数值；CHIINV(p, n) 计算分布函数的反函数在处的函数值； TDIST(x, n, 1) 计算分布的右尾概率；TDIST(x, n, 2) 计算分布的双尾概率；TINV(p, n) 计算分布的满足的；FDIST(x, m, n)的右尾概率；FINV(p, m, n) 计算分布的反函数在p处的函数值；例1 求第八章第二节例2的建设检验问题的p值.解 这是一个单边t检验问题，检验统计量 ，统计量的观测值，检验的p值为打开一个Excel工作表，选定存放p值的单元格，单击菜单栏中的“插入”，在弹出的菜单中单击“函数”；在弹出的菜单中单击”TDIST”，然后单击确定；在弹出的对话框中输入x=1.162，df=5，Tails=1，单击“确定”，则在选定的单元格中显示出0.148841，这个值就是此检验问题的p值.二、数据的基本统计量的计算AVERAGE(A1:An) 计算数据的均值；TRIMMEAN(array, percent) 先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值;VAR (A1:An) 计算数据的方差；STDDEV (A1:An) 计算数据的标准方差；COVAR(A1:An, B1:Bn) 计算两组数据之间的样本协方差CORREL(A1:An, B1:Bn) 计算两组数据之间的相关系数；PERCENTILE (A1:An，f) 计算数据的下分位数;KURT (A1:An) 计算数据的峰度;SKEW(A1:An) 计算数据的偏度;CONFIDENCE(A1:An) 计算总体平均值的置信区间.三、数据的排序与定位RANK(x,array,order) x为需要找到排位的数值，order=0数据按降序排列，order=1数据按升序排列；LARGE(array, k)，SMALL(array, k) 计算数据的中第k个最大值与第k个最小值；MEDIAN(array) 计算数据的中位数；MODE(array) 计算数据中出现频率最多的数值;MAX(array)， MIN(array) 计算数据的最大值与最小值；PERCENTRANK(array,x,significance) 求特定数值x在一个数据集array中的百分比排位， Significance为可选项，表示返回的百分数值的有效位数。如果省略，函数 PERCENTRANK 保留 3 位小数。此函数输出数组，称为数组公式,输入完成后，应以 ctrl+shift+enter 进行确认.第三节 描述性统计方法一、散点图在相应于数据的坐标处记一个点，得到的一个由多个数据点构成的图称为散点图，Excel中可以画二维数据的散点图。二、直方图前面第六章第二节已经介绍了直方图的概念，在Excel中可以方便地作出数据的直方图，也可以用函数FREQUENCY计算一组数据按指定方式分组后每组的频数. FREQUENCY(array,bins_array) 计算样本数据array按bins_array指定的方式分组后每组的频数，以计算某公司的员工年龄分布情况为例说明。在工作表里列出了员工的年龄.这些年龄为 28、25、31、21、44、33、22 和 35，并分别输入到单元格 C4:C11。这一列年龄就是样本数据array。Bins_array 是另一列用来对年龄分组的区间值。在本问题中，bins_array 是设定在 C13:C16 单元格，分别含有值 25、30、35、和 40。以数组形式输入函数 FREQUENCY，就可以计算出年龄在 25岁以下、2630岁、3135岁、3640岁和40岁以上各区间中的频数。=FREQUENCY(C4:C11,C13:C16)等于 2;2;2;1;1.三、箱线图箱线图也称为盒图，用于反映一组或多组连续型定量数据分布的中心位置和散布范围。制作盒图首先要对数据作简单的加工，称之为“五数概括”，即数据最大值（M）、最小值(m)、四分之一下分位数(Q1)、中位数(Q2)、四分之一上分位数(Q3). 五数概括粗略地反映了数据的分布情况. 在Excel中这几个数可用函数QUARTILE来计算. 若有n个数据放在A1:An，五数m、M、Q1、Q2、Q3分别放在B1:B5中，在B1、B2、B3、B4、B5中依次键入如下五个表达式即可得到所需的五数.=QUARTILE(A1:An，0)，=QUARTILE(A1:An，1)=QUARTILE(A1:An，2)，=QUARTILE(A1:An，3)=QUARTILE(A1:An，4)有了这五个数就可以手工或借助于软件画出箱线图，箱线图的作法如下 （1）画一水平（或垂直）轴，在轴上标上m, Q1, Q2, Q3, M，在轴上方画一个上、下边平行与轴的矩形箱子，箱子的左右两侧分别位于Q1 ，Q3处的上方，在Q2处画一条垂直线段，线段位于箱子内部.（2）自箱子中部左侧引一条水平线至m，在同一水平高度自箱子右侧引一条水平线至M.mQ1Q2Q3M图10-2箱线图例1 为了估计一批18W的白炽灯泡的寿命，随机抽取了50个，其寿命（单位：小时）数据为91992370278511961037112613119369589181156920948107610921162117092095090597210351045855119611941340112293897012379561102115797883210091158115110097659581022133381112171085896985（1）画出直方图；（2）样本均值与方差、峰度与偏度；（3）画出箱线图.解 打开一个Excel工作表，将题目中的表格数据输入到单元格A1:A50（1）在Excel画出直方图的步骤如下：在B1:B5中依次输入800，900，1000，1100，1200；依次单击“工具”，“数据分析”，“直方图”和“确定”；在弹出的对话框中的“输入区域”键入“A1:A50”，“接受区域”键入“B1:B5”，选中“输出图形”，单击“确定”，即可得到如下图形.图10-3 灯泡寿命直方图（2）在B1中键入“=AVERAGE(A1:A50)”，按回车键，可得均值为1028.76；在B2中键入“=VAR(A1:A50)”，按回车键，可得方差为21784.64；在B3中键入“= KURT (A1:A50)”，按回车键，可得峰度为-0.38681；在B3中键入“= SKEW (A1:A50)”，按回车键，可得偏度为0.135585.（3）绘制箱线图需要借助于图表中的股价图中的“开盘-盘高-盘低-收盘图”，需将五数概括按特定的顺序排列. 在D2中键入“=QUARTILE(A1:A50,1)”，按回车键，可得四分之一下分位数926.25； 在D3中键入“=QUARTILE(A1:A50,4)”，按回车键，可得最大值为1340;在D4中键入“=QUARTILE(A1:A50,0)”，按回车键，可得最小值为702； 在D5中键入“=QUARTILE(A1:A50,2)”，按回车键，可得中位数为1009； 在D6中键入“=QUARTILE(A1:A50)”，按回车键，可得四分之一上分位数为1154.75；在C2:C6依次键入“P25, P100, P0, P50, P75”，在D1中键入“箱线图”，在Excel中绘制步骤如下单击菜单栏中的“插入”，在弹出的下拉菜单中单击“图标”；在弹出的对话框中左边的“图标类型”中选“股价图”，在右边的“子图类型”中选“开盘-盘高-盘低-收盘图”，单击“下一步”；在弹出的对话框中的“数据区域”键入“C1:D5”, “系列产生在”选定为“行”，单击“下一步”；在“图表选项”对话框的分类（X）轴下方填入“灯泡寿命箱线图”，在数值（Y）轴下方填入“灯泡寿命”，单击“完成”；在绘图区点击右键，选取“数据源系列添加”，在“名称”右侧用鼠标选取单元格C6，在“值”右侧用鼠标选取单元格区域D6，单击“确定”。在绘图区点击右键，选取“数据源系列添加”，在“名称”右侧用鼠标选取单元格C6，在“值”右侧用鼠标选取单元格区域D6，单击“确定”；在网格线上点击右键，在弹出的下拉菜单中单击“清除”，网格线消失，在绘图区单击右键“清除”背景色在横坐标上单击右键，选取“坐标轴格式图案”，右上部主要刻度线类型复选“无”，单击“确定”，在纵坐标上单击右键，选取“坐标轴格式刻度最小值”，值设为“650”，单击“确定”；在箱的底部位置P0 系列标志上单击右键，选取“数据系列格式图案”，在界面右侧“数据标记”的样式处选取“”，前景颜色处选黑色，“大小”改为6。同样的，将P25、 P59、 P75 、P100处的“数据标记”的样式依次改为“o, +, *” , 得到如下箱线图.图10-4灯泡寿命箱线图第四节 假设检验与方差分析方法一、假设检验在Excel中作假设检验可用函数的方法或数据分析工具中的方法. 检验用的函数名称最后四个英文字母为英文单词“TEST”，前面的字母为所用统计量的名称.常用的检验法的函数有：ZTEST 正态分布检验法，TTEST T分布检验法，FTEST F分布检验法，CHITEST 卡方分布检验法. Excel中对于假设检验问题给出的是p值.例1 10个失眠者, 服用甲、乙两种安眠药, 延长睡眠时间如下:甲1.90.81.10.10.14.45.51.64.63.4乙0.71.60.21.20.13.43.70.802.0讨论这两种药的疗效有无显著差异,取，试检验假设 解 用Excel求操作步骤如下：（1）打开一个Excel工作表，将题目中的表格数据输入到单元格A1:A11和B1:B11；（2）依次单击“工具”，“数据分析”，“t-检验：双样本异方差假设”和“确定”；（3）在弹出的对话框中输入变量1的范围A1:A11以及变量2的范围B1:B11，在假定均值差空格中输入0，单击“确定”后弹出如下一个新的工作表.表10.1 t-检验（双样本异方差假设）计算结果甲乙平均2.330.75方差4.0093.2005556观测值1010假设平均差0df18t Stat1.860813P(T=t) 单尾0.039593t 单尾临界1.734064P(T=t) 双尾0.079187t 双尾临界2.100922可以用两种方法来判定检验的结果（1）临界值法 这是双边t检验问题，将上表中t统计量的观测值“t Stat”与“t 双尾临界值”进行比较，现在t统计量的观测值1.860813小于t 双尾临界值2.100922，所以在显著性水平0.05下接受原假设.(2) p值法 0.05小于此双边t检验问题的p值0.079187，故接受原假设.二、方差分析Excel中可进行单因素方差分析，双因素无重复实验方差分析以及双因素有重复实验并考虑交互作用的方差分析.例1将20头猪仔随机地分成的四组，每组5头，每组给一种饲料，在一定长时间内每头猪增重（kg）如下表所示，问这四种饲料对猪仔的增重有无显著影响（）？组 别A1 A 2 A 3 A4 重量/kg60 73 95 8865 67 105 5361 68 99 9067 66 102 8464 71 103 87解 本题是单因素试验的方差分析。考虑的因素是饲料，水平数S=4，在各水平下的试验数nj5，总试验数n20，设喂这4中饲料使猪仔增重的均值分别为 需检验假设 不全相等，用Excel求解步骤如下：（1）打开Excel工作表，将数据输入到A1:D6;（2）依次单击“工具”，“数据分析”，“方差分析：单因素方差分析”和“确定”；（3）在弹出的对话框中输入变量的范围A1:D6，单击“标志行位于第一列”，设定 ，单击“确定”，显示结果有两张表，第一张表是四种饲料下猪增重值的均值、方差的汇总，第二张表是本题的方差分析表.表10.2 单因素方差分析计算结果差异源SSdfMSFP-valueF crit组间4095.631365.220.1282711.12E-053.238872组内1085.21667.825总计5180.819可以用两种方法来判定检验的结果（1）临界值法 F=20.128271，大于F的临界值F crit=3.238872所以在显著性水平0.05下拒绝原假设.认为这四种饲料对猪仔的增重有显著影响.(2) p值法 0.05远大于此检验问题的p值1.12E-05，故拒绝原假设.且知差异是非常显著的.例2为培养职业技术教育的师资，通过统计分析，认为招收在职生比招收应届生好，以往招生只确定一个录取分数线，对年龄和工龄并没有严格的限制，形成学生间在生活习惯和兴趣爱好等方面有较大的差异。对年龄，工龄两因素与学习成绩的关系进行重复有 交叉试验.取各因素的等级（水平）如下：A1:年龄不超过25岁;A2年龄超过25岁B1工龄不 到5年；B2工龄至少5年。成绩 BAB1B2A186 87 76 79 8582 93 82 88 91A277 82 84 90 7682 82 80 75 79试用有交互作用的双因素方差分析法，分析年龄和工龄对在职生的成绩的影响问题 。（）解 本题是考虑交互作用的双因素试验的方差分析。考虑的因素年龄和工龄，水平数p=q=2，各水平搭配下的试验数r5，总试验数n20. 设分别表示年龄的两个水平对成绩的效应，分别表示工龄的两个水平对成绩的效应，分别表示工龄的两个水平和年龄的两个水平的交互作用对成绩的效应，需检验下列假设，用Excel求解步骤如下（1）打开Excel工作表，将数据输入到A1:C11;（2）依次单击“工具”，“数据分析”，“方差分析：单因素方差分析”和“确定”；（3）在弹出的对话框中输入变量的范围A1:C11，在“每一行的样本行数”中键入“5”，设定 ，单击“确定”，即可显示本题的方差分析表.表10.3 双因素方差分析计算结果差异源SSdfMSFP-valueF crit样本88.2188.23.9418990.0645124.493998列7.217.20.3217880.5784084.493998交互57.8157.82.583240.1275534.493998内部3581622.375总计511.219结果分析 检验问题的p值0.064512，的p值0.578408，的p值0.127553，由于0.05均小于这些p值，故接受，认为年龄和工龄对学习成绩无影响；而年龄与工龄交互作用也不显著. 第五节 回归分析函数一、一元线性回归分析方法SLOPE(A1:An, B1:Bn) 计算因变量与自变量之间的回归系数-斜率；INTERCEPT(A1:An, B1:Bn) 计算因变量与自变量之间的回归系数-截距；FORECAST(x，A1:An, B1:Bn) 计算一元回归模型中，自变量取值x为时，因变量y的预测值.例1炼铝厂测得所产铸模用的铝的硬度X与抗张强度Y的数据如下表所示：铝的硬度X68537084607251837064抗张强度Y288293349343290354283324340286（1）画出散点图；（2）求Y对X的回归方程，并在显著水平0.05下检验回归方程的显著性；（3）试预报当铝的硬度X65时的抗张强度Y。解 打开Excel工作表，将X数据输入到A1:A10，将Y数据输入到B1:B10.（1）画散点图的步骤如下依次单击“插入”，“图标”，“XY散点图”和“下一步”；在弹出的对话框中的“数据区域”键入“A1:B10”,选定“系列产生在”