1、第十章 Excel 软件在统计分析中的运用第一节 概述一、Excel 软件简介二、Excel 中的统计分析功能第二节 基本运算函数一、基本分布的计算二、数据的基本统计量的计算三、数据的排序与定位第三节 描述性统计方法一、散点图二、直方图三、箱线图第四节 假设检验与方差分析函数一、假设检验方法二、方差分析方法第五节 一元线性回归分析第十章 Excel 软件在统计分析中的运用当今时代称之为数字化信息时代，随着现代科学技术的飞速发展，我们已进入一个利用和开发信息资源的信息社会。在生产、商业活动、工程实验、科学研究等过程中，每天都会产生大量的数据，这些表面上看上去杂乱无章的数据，其实里面含有大量的有用
2、信息，只有经过合理的分析和处理才能得到这些信息. 在许多问题中，我们面临的数据具有信息量大，范围广，变化快等特点，传统的人工处理手段无法适应社会和经济的高速发展对统计分析提出的要求，也难以提高数据分析和处理的速度和精度. 随着计算机硬件及软件技术的飞速发展，我们现在已经可以处理海量的数据，计算机技术在数理统计中的运用，主要是数据信息的存储、检索和统计资料的分析和检索.第一节 概述一、Excel 软件简介功能强大的统计分析软件有 SAS、SPSS 等，这些软件功能强大，计算精度高，但是这些软件往往由于系统庞大、结构复杂，大多数非统计专业人员难以运用自如，而且其正版软件价格昂贵，是一般人难以承受的
3、.Excel 是办公室自动化中非常重要的一款软件，很多巨型国际企业都是依靠 Excel 进行数据管理。它不仅仅能够方便的处理表格和进行图形分析，其更强大的功能体现在对数据的自动处理和计算. Excel 的数据处理功能在现有的文字处理软件中可以说是独占鳌头，几乎没有什么软件能够与它匹敌. 计算机上安装了微软(Microsoft) 公司的办公软件 Office后，随之就有了 Excel，不需要另外投资，Excel 的使用并不复杂，可通过联机帮助来学习其操作和功能.Excel 的统计工作表函数用于对数据区域进行统计分析,Excel 中的工作表就像矩阵，Excel 做计算往往是对工作表中某个区域进行，
4、其统计分析函数中所用的数据区域用 array 来表示，如 A1:H1 表示第 1 行的 A 列到 H 列共 8 个数，D2:D15 表示 D 列的第 2 行到第15 行共 14 数，B2:F15 表示从 B 列到 F 列，从第 2 行到第 15 行共 70 个数据.二、Excel 中的统计分析功能菜单栏工具栏格式栏垂直滚动条标题栏水平滚动条工作表名称编辑栏行号 列号单元格 C2状态栏图 10-1 Excel 的用户界面窗口工作表区第二节 基本运算函数一、基本分布的计算BINOMDIST(k, n, p, 0) 计算二项分布的分布律BINOMDIST(k, n, p, 1) 计算二项分布的累积分
5、布HYPGEOMDIST( 0, k ，M, N )计算超几何分布的分布律；HYPGEOMDIST( 1, k ，M, N )计算超几何分布的累积分布；POISSON(k, , 0) 计算泊松分布的分布律POISSON(k, , 1) 计算泊松分布的分布的累积分布EXPONDIST(x, , 0) 计算指数分布密度函数 在 处的函数值；()fxEXPONDIST(x, , 1) 计算指数分布函数 在 处的函数值；FNORMDIST( ) 计算正态分布 的密度函数在 处的函数值；,0x2(,)NxNORMDIST( ) 计算正态分布 分布函数在 处的函数值；,12,NORMSDIST( ) 计算
6、标准正态分布 分布函数 在 处的函数值；x(0,1)()xNORMSINV( ) 计算标准正态分布 分布函数 的反函数在 处的函数值； pNpCHIDIST(x, n) 计算分布函数在 处的函数值；xCHIINV(p, n) 计算分布函数的反函数在 处的函数值； pTDIST(x, n, 1) 计算 分布的右尾概率 ；()tn()PTxTDIST(x, n, 2) 计算 分布的双尾概率 ；t |TINV(p, n) 计算分布 的满足 的 ；()tn(|)TxpFDIST(x, m, n)的右尾概率 ；PFFINV(p, m, n) 计算分布 的反函数在 p 处的函数值；(,)mn例 1 求第八
7、章第二节例 2 的建设检验问题的 p 值.解 这是一个单边 t 检验问题，检验统计量 ，统计量的观测值0(5)/XTtsn:，检验的 p 值为07.5.61/t000()()Ptt打开一个 Excel 工作表，选定存放 p 值的单元格，单击菜单栏中的“插入”，在弹出的菜单中单击“函数”；在弹出的菜单中单击”TDIST”，然后单击确定；在弹出的对话框中输入 x=1.162，df=5，Tails=1，单击“确定”，则在选定的单元格中显示出 0.148841，这个值就是此检验问题的 p 值.二、数据的基本统计量的计算AVERAGE(A1:An) 计算数据的均值；TRIMMEAN(array, per
8、cent) 先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值;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 为需要找到排位的数值，or
9、der=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 位小数。此函
10、数输出数组，称为数组公式 ,输入完成后，应以 ctrl+shift+enter 进行确认.第三节 描述性统计方法一、散点图在相应于数据的坐标处记一个点，得到的一个由多个数据点构成的图称为散点图，Excel 中可以画二维数据的散点图。二、直方图前面第六章第二节已经介绍了直方图的概念，在 Excel 中可以方便地作出数据的直方图，也可以用函数 FREQUENCY 计算一组数据按指定方式分组后每组的频数 . FREQUENCY(array,bins_array) 计算样本数据 array 按 bins_array 指定的方式分组后每组的频数，以计算某公司的员工年龄分布情况为例说明。在工作表里列出了员
11、工的年龄.这些年龄为 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.三、箱线图箱线图也称为盒图，用于反映一组或多组连续型定量数据分布的中心
12、位置和散布范围。制作盒图首先要对数据作简单的加工，称之为“五数概括” ，即数据最大值（ 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:A
13、n，3)=QUARTILE(A1:An，4)有了这五个数就可以手工或借助于软件画出箱线图，箱线图的作法如下 （1）画一水平（或垂直）轴，在轴上标上 m, Q1, Q2, Q3, M，在轴上方画一个上、下边平行与轴的矩形箱子，箱子的左右两侧分别位于 Q1 ，Q3 处的上方，在 Q2 处画一条垂直线段，线段位于箱子内部.（2）自箱子中部左侧引一条水平线至 m，在同一水平高度自箱子右侧引一条水平线至 M.图 10-2 箱线图例 1 为了估计一批 18W 的白炽灯泡的寿命，随机抽取了 50 个，其寿命（单位：小时）数据为919 923 702 785 1196 1037 1126 1311 936 9
14、58918 1156 920 948 1076 1092 1162 1170 920 950905 972 1035 1045 855 1196 1194 1340 1122 938970 1237 956 1102 1157 978 832 1009 1158 11511009 765 958 1022 1333 811 1217 1085 896 985（1）画出直方图；（2）样本均值与方差、峰度与偏度；（3）画出箱线图.解 打开一个 Excel 工作表，将题目中的表格数据输入到单元格 A1:A50（1）在 Excel 画出直方图的步骤如下：在 B1:B5 中依次输入 800， 900，1
15、000，1100，1200；依次单击“工具”，“数据分析”，“直方图”和“确定”；在弹出的对话框中的“输入区域”键入“A1:A50”，“接受区域”键入“B1:B5 ”，选中 “输出图形” ，单击“ 确定” ，即可得到如下图形.直 方 图05101520800 900 1000 1100 1200 其 他接 收频率 频 率图 10-3 灯泡寿命直方图（2）在 B1 中键入“=AVERAGE(A1:A50)” ，按回车键，可得均值为 1028.76；在 B2 中键入“=VAR(A1:A50)” ，按回车键，可得方差为 21784.64；m Q1 Q2 Q3 M在 B3 中键入“= KURT (A1
16、: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
17、:A50,2)” ，按回车键，可得中位数为 1009； 在 D6 中键入“=QUARTILE(A1:A50)” ，按回车键，可得四分之一上分位数为 1154.75；在 C2:C6 依次键入 “P25, P100, P0, P50, P75”，在 D1 中键入 “箱线图” ，在 Excel 中绘制步骤如下单击菜单栏中的“插入” ，在弹出的下拉菜单中单击“图标” ；在弹出的对话框中左边的“图标类型”中选“股价图” ，在右边的“子图类型”中选“开盘-盘高- 盘低-收盘图” ，单击“下一步” ；在弹出的对话框中的“数据区域”键入“C1:D5”, “系列产生在”选定为“行” ，单击“下一步” ；在“图表
18、选项”对话框的分类（X）轴下方填入“灯泡寿命箱线图” ，在数值（Y）轴下方填入 “灯泡寿命” ，单击“完成” ；在绘图区点击右键，选取“数据源系列添加” ，在“名称”右侧用鼠标选取单元格 C6，在“值”右侧用鼠标选取单元格区域 D6，单击 “确定” 。在绘图区点击右键，选取“数据源系列添加” ，在“名称”右侧用鼠标选取单元格 C6，在“值”右侧用鼠标选取单元格区域 D6，单击“确定” ；在网格线上点击右键，在弹出的下拉菜单中单击“清除” ，网格线消失，在绘图区单击右键“清除”背景色在横坐标上单击右键，选取“坐标轴格式图案” ，右上部主要刻度线类型复选“无” ，单击“确定” ，在纵坐标上单击右键
19、，选取“坐标轴格式刻度最小值” ，值设为“650” ，单击“确定” ；在箱的底部位置 P0 系列标志上单击右键，选取“数据系列格式图案” ，在界面右侧“数据标记”的样式处选取“” ，前景颜色处选黑色， “大小”改为 6。同样的，将 P25、 P59、 P75 、P100 处的“数据标记”的样式依次改为“o, +, *” , 得到如下箱线图.65075085095010501150125013501450P25P100P0P50P75图 10-4 灯泡寿命箱线图第四节 假设检验与方差分析方法一、假设检验在 Excel 中作假设检验可用函数的方法或数据分析工具中的方法. 检验用的函数名称最后四个英
20、文字母为英文单词“TEST”，前面的字母为所用统计量的名称 .常用的检验法的函数有：ZTEST 正态分布检验法， TTEST T 分布检验法，FTEST F 分布检验法，CHITEST 卡方分布检验法. Excel 中对于假设检验问题给出的是 p 值.例 1 10 个失眠者, 服用甲、乙两种安眠药, 延长睡眠时间如下:甲 1.9 0.8 1.1 0.1 0.1 4.4 5.5 1.6 4.6 3.4乙 0.7 1.6 0.2 1.2 0.1 3.4 3.7 0.8 0 2.0讨论这两种药的疗效有无显著差异,取 ，试检验假设 0.521210:,:H解 用 Excel 求操作步骤如下：（1）打开
21、一个 Excel 工作表，将题目中的表格数据输入到单元格 A1:A11 和 B1:B11；（2）依次单击“工具”，“数据分析”，“t-检验：双样本异方差假设”和“确定”；（3）在弹出的对话框中输入变量 1 的范围 A1:A11 以及变量 2 的范围 B1:B11，在假定均值差空格中输入 0，单击“确定”后弹出如下一个新的工作表 .表 10.1 t-检验（双样本异方差假设）计算结果甲 乙平均 2.33 0.75方差 4.009 3.2005556观测值 10 10假设平均差 0df 18t Stat 1.860813P(T=t) 单尾 0.039593t 单尾临界 1.734064P(T=t)
22、双尾 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 头，每组给
23、一种饲料，在一定长时间内每头猪增重（kg）如下表所示，问这四种饲料对猪仔的增重有无显著影响（ ）？0.5组 别 A1 A 2 A 3 A4 60 73 95 88重量/kg65 67 105 5361 68 99 9067 66 102 8464 71 103 87解 本题是单因素试验的方差分析。考虑的因素是饲料，水平数 S=4，在各水平下的试验数 nj5 ，总试验数 n20，设喂这 4 中饲料使猪仔增重的均值分别为41j需检验假设 不全相等，用 Excel 求.,3210123401234:,:,.HH解步骤如下：（1）打开 Excel 工作表，将数据输入到 A1:D6;（2）依次单击“工具
24、”，“数据分析”，“方差分析：单因素方差分析”和“确定”；（3）在弹出的对话框中输入变量的范围 A1:D6，单击“标志行位于第一列”，设定 ，单击“确定” ，显示结果有两张表，第一张表是四种饲料下猪增重值的均值、方0.5差的汇总，第二张表是本题的方差分析表.表 10.2 单因素方差分析计算结果差异源 SS df MS F P-value F crit组间 4095.6 3 1365.2 20.128271 1.12E-05 3.238872组内 1085.2 16 67.825总计 5180.8 19 可以用两种方法来判定检验的结果（1）临界值法 F=20.128271，大于 F 的临界值 F
25、 crit=3.238872 所以在显著性水平0.05 下拒绝原假设.认为这四种饲料对猪仔的增重有显著影响 .(2) p 值法 0.05 远大于此检验问题的 p 值 1.12E-05，故拒绝原假设.且知差异是非常显著的.例 2 为培养职业技术教育的师资，通过统计分析，认为招收在职生比招收应届生好，以往招生只确定一个录取分数线，对年龄和工龄并没有严格的限制，形成学生间在生活习惯和兴趣爱好等方面有较大的差异。对年龄，工龄两因素与学习成绩的关系进行重复有 交叉试验.取各因素的等级（水平）如下：A1:年龄不超过 25 岁;A 2 年龄超过 25 岁B1 工龄不 到 5 年；B 2 工龄至少 5 年。成
26、绩 BA B1 B2A1 86 87 76 79 85 82 93 82 88 91A2 77 82 84 90 76 82 82 80 75 79试用有交互作用的双因素方差分析法，分析年龄和工龄对在职生的成绩的影响问题 。 （）0.5解 本题是考虑交互作用的双因素试验的方差分析。考虑的因素年龄和工龄，水平数p=q=2，各水平搭配下的试验数 r5 ，总试验数 n20. 设 分别表示年龄41j 12,的两个水平对成绩的效应， 分别表示工龄的两个水平对成绩的效应， 分别12, ,ij表示工龄的两个水平和年龄的两个水平的交互作用对成绩的效应，需检验下列假设， ，012:0H0212:0H031:,1
27、,2ijHj用 Excel 求解步骤如下（1）打开 Excel 工作表，将数据输入到 A1:C11;（2）依次单击“工具”，“数据分析”，“方差分析：单因素方差分析”和“确定”；（3）在弹出的对话框中输入变量的范围 A1:C11，在“每一行的样本行数”中键入“5”，设定 ，单击“确定” ，即可显示本题的方差分析表 .0.5表 10.3 双因素方差分析计算结果差异源 SS df MS F P-value F crit样本 88.2 1 88.2 3.941899 0.064512 4.493998列 7.2 1 7.2 0.321788 0.578408 4.493998交互 57.8 1 57
28、.8 2.58324 0.127553 4.493998内部 358 16 22.375总计 511.2 19 结果分析 检验问题 的 p 值 0.064512， 的 p 值 0.578408， 的 p 值01H02H03H0.127553，由于 0.05 均小于这些 p 值，故接受 ， ， ，认为年龄和工龄对学103习成绩无影响；而年龄与工龄交互作用也不显著. 第五节 一元线性回归分析函数SLOPE(A1:An, B1:Bn) 计算因变量与自变量之间的回归系数-斜率；INTERCEPT(A1:An, B1:Bn) 计算因变量与自变量之间的回归系数-截距；FORECAST(x， A1:An,
29、B1:Bn) 计算一元回归模型中，自变量取值 x 为时，因变量 y 的预测值.例 1 炼铝厂测得所产铸模用的铝的硬度 X 与抗张强度 Y 的数据如下表所示：铝的硬度 X 68 53 70 84 60 72 51 83 70 64抗张强度 Y 288 293 349 343 290 354 283 324 340 286（1）画出散点图；（2）求 Y 对 X 的回归方程，并在显著水平 0.05 下检验回归方程的显著性；（3）试预报当铝的硬度 X65 时的抗张强度 Y。解 打开 Excel 工作表，将 X 数据输入到 A1:A10，将 Y 数据输入到 B1:B10.（1）画散点图的步骤如下依次单击
30、“插入”，“图标”，“XY 散点图”和“下一步”；在弹出的对话框中的“数据区域”键入“A1:B10”,选定“系列产生在”的“列 ”，单击“下一步”；在弹出的对话框“图标选项”中的“图标标题”键入硬度-强度散点图，在 “X 轴”键入“硬度x”，在“Y 轴”键入“强度 y”，单击“完成” ，显示出如下散点图。硬 度 -张 强 度 散 点 图01002003004000 20 40 60 80 100硬 度 x张强y 系 列 1图 10-4 硬度 X 与抗张强度 Y 散点图（2）求 Y 对 X 的回归方程的步骤如下依次单击“工具”，“数据分析”，“回归”和“确定”；在弹出的对话框中的“X 值区域”键
31、入“A1:A10”, “Y 值区域”键入“B1:B10”,单击“确定”，设定“置信水平”为 95%，“输出选项”选定“新工作表组”，单击“确定”，即得到计算表格.输出的表格共三张，最后一张表的信息最重要，如下表所示表 10.4 一元线性回归分析表Coefficients 标准误差 t Stat P-value 下限 95.0% 上限 95.0%Intercept 188.9877 46.40542 4.072535 0.003571 81.9766 295.9988x 1.866849 0.679362 2.747946 0.025139 0.300238 3.43346从表中可得到如下结果：
32、表中第一栏 Coefficients 下的 Intercept：188.9877 和 x：1.866849 分别是回归方程中常数项和 x 的系数，由此得到回归方程为 Y=188.9877 + 1.866849X表中 P-value 栏下的 x：0.025139 给出了 x 的回归系数 b 的双边检验的 p 值，由于 0.05 大于 0.025139，故拒绝原假设，认为回归效果是00:,:Hb显著的.表中下限 95%一栏下的 x： 0.300238 和上限 95%一栏下 x：3.43346 分别为回归系数 b的 95%置信区间的左端点值和右端点值，即 b 的 95%置信区间为（ 0.300238，3.43346） ，同样可得 a 的 95%置信区间为（ 81.9766，295.9988）（3）利用 FORECAST 计算预测值，将计算结果放在单元格 A12 中.在单元格 A12 中键入“=FORECAST(65,A4:A13,B4:B13)” ，按回车键，可以看到单元格A12 中显示的值为 310.3329，所以，当铝的硬度 X65 时，抗张强度 Y=310.3329.
