Excel - 软件在数理统计中的应用

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) 计算指数分布密度函数f(x)在x处的函数值； EXPONDIST(x, λ, 1) 计算指数分布函数F(x)在x处的函数值；

NORMDIST(x,?,?,0) 计算正态分布N(?,?2)的密度函数在x处的函数值； NORMDIST(x,?,?,1) 计算正态分布N(?,?2)分布函数在x处的函数值；

NORMSDIST(x) 计算标准正态分布N(0,1)分布函数?(x)在x处的函数值；

NORMSINV(p) 计算标准正态分布N(0,1)分布函数?(x)的反函数在p处的函数值；

CHIDIST(x, n) 计算分布函数在x处的函数值；

CHIINV(p, n) 计算分布函数的反函数在p处的函数值；

TDIST(x, n, 1) 计算t(n)分布的右尾概率P(T?x)； TDIST(x, n, 2) 计算t(n)分布的双尾概率P(|T|?x)； TINV(p, n) 计算分布t(n)的满足P(|T|?x)?p的x； FDIST(x, m, n)的右尾概率P(F?x)；

FINV(p, m, n) 计算分布F(m,n)的反函数在p处的函数值； 例1 求第八章第二节例2的建设检验问题的p值. 解 这是一个单边t检验问题，检验统计量 T?t0?70?71.510/5X??0s/nt(5)，统计量的观测值

??1.162，检验的p值为p0?P(T?t0)?P(T??t0)

打开一个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岁以下、26~30岁、31~35岁、36~40岁和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.

m Q1 Q2 Q3 图10-2箱线图

M

例1 为了估计一批18W的白炽灯泡的寿命，随机抽取了50个，其寿命（单位：小时）数据为

919 923 702 785 1196 1037 1126 1311 936 958 918 1156 920 948 1076 1092 1162 1170 920 950 905 972 1035 1045 855 1196 1194 1340 1122 938 970 1237 956 1102 1157 978 832 1009 1158 1151 1009 765 958 1022 1333 811 1217 1085 896 985 （1）画出直方图；

（2）样本均值与方差、峰度与偏度； （3）画出箱线图.

解 打开一个Excel工作表，将题目中的表格数据输入到单元格A1:A50 （1）在Excel画出直方图的步骤如下：

①在B1:B5中依次输入800，900，1000，1100，1200；②依次单击“工具”，“数据分析”，“直方图”和“确定”；③在弹出的对话框中的“输入区域”键入“A1:A50”，“接受区域”键入“B1:B5”，选中“输出图形”，单击“确定”，即可得到如下图形.

直方图20151050800900100011001200其他接收

图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, +,△, *” , 得到如下箱线图.

14501350125011501050950850750650P25P100P0P50P75

图10-4灯泡寿命箱线图

第四节 假设检验与方差分析方法 一、假设检验

在Excel中作假设检验可用函数的方法或数据分析工具中的方法. 检验用的函数名称最后四个英文字母为英文单词“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 3.7 1.6 0.8 4.6 0 3.4 2.0 0.7 －1.6 －0.2 －1.2 －0.1 3.4 讨论这两种药的疗效有无显著差异,取??0.05，试检验假设 H0:?1??2,H1:?1??2

解 用Excel求操作步骤如下：

（1）打开一个Excel工作表，将题目中的表格数据输入到单元格A1:A11和B1:B11； （2）依次单击“工具”，“数据分析”，“t-检验：双样本异方差假设”和“确定”； （3）在弹出的对话框中输入变量1的范围A1:A11以及变量2的范围B1:B11，在假定均值差空格中输入0，单击“确定”后弹出如下一个新的工作表.

表10.1 t-检验（双样本异方差假设）计算结果

平均 方差 观测值 假设平均差 df t Stat

P(T<=t) 单尾 t 单尾临界 P(T<=t) 双尾 t 双尾临界

甲 2.33 4.009 10 0 18

1.860813 0.039593 1.734064 0.079187 2.100922

乙 0.75

3.2005556 10

可以用两种方法来判定检验的结果

（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）如下表所示，问这四种饲料对猪仔的增重有无显著影响（??0.05）？

组 别 重量/kg A1 A 2 A 3 A4 60 73 95 88 65 67 105 53 61 68 99 90 67 66 102 84 64 71 103 87 解 本题是单因素试验的方差分析。考虑的因素是饲料，水平数S=4，在各水平下的试验数nj＝5?1?j?4?，总试验数n＝20，设喂这4中饲料使猪仔增重的均值分别为

?1,?2,?3,?4. 需检验假设 H0:?1??2??3??4,H0:?1,?2,?3,?4.不全相等，用Excel求

解步骤如下：

（1）打开Excel工作表，将数据输入到A1:D6;

（2）依次单击“工具”，“数据分析”，“方差分析：单因素方差分析”和“确定”； （3）在弹出的对话框中输入变量的范围A1:D6，单击“标志行位于第一列”，设定

，显示结果有两张表，第一张表是四种饲料下猪增重值的均值、方??0.05，单击“确定”

差的汇总，第二张表是本题的方差分析表.

表10.2 单因素方差分析计算结果 差异源 组间 组内 总计

可以用两种方法来判定检验的结果

（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年。 成绩 B A A1 A2 B1 86 87 76 79 85 77 82 84 90 76 B2 82 93 82 88 91 82 82 80 75 79 SS 4095.6 1085.2 5180.8

df 3 16

MS 1365.2 67.825

F 20.128271

P-value 1.12E-05

F crit 3.238872

19

试用有交互作用的双因素方差分析法，分析年龄和工龄对在职生的成绩的影响问题 。

（??0.05）

解 本题是考虑交互作用的双因素试验的方差分析。考虑的因素年龄和工龄，水平数p=q=2，各水平搭配下的试验数r＝5?1?j?4?，总试验数n＝20. 设?1,?2分别表示年龄的两个水平对成绩的效应，?1,?2分别表示工龄的两个水平对成绩的效应，?ij,i,j?1,2分别表示工龄的两个水平和年龄的两个水平的交互作用对成绩的效应，需检验下列假设

?H01:?1??2?0?H02:?1??2?0??H03:?ij?0,i,j?1,2 ，，???H:??0H:???H:????ij?1112?1212?13用Excel求解步骤如下

（1）打开Excel工作表，将数据输入到A1:C11;

（2）依次单击“工具”，“数据分析”，“方差分析：单因素方差分析”和“确定”； （3）在弹出的对话框中输入变量的范围A1:C11，在“每一行的样本行数”中键入“5”，设定 ??0.05，单击“确定”，即可显示本题的方差分析表.

表10.3 双因素方差分析计算结果

差异源 样本 列 交互 内部 总计 SS 88.2 7.2 57.8 358 511.2 df MS 1 88.2 1 7.2 1 57.8 16 22.375 19 F 3.941899 0.321788 2.58324 P-value 0.064512 0.578408 0.127553 F crit 4.493998 4.493998 4.493998

结果分析 检验问题H01的p值0.064512，H02的p值0.578408，H03的p值0.127553，由于0.05均小于这些p值，故接受H01，H02，H03，认为年龄和工龄对学习成绩无影响； 而年龄与工龄交互作用也不显著.

第五节 回归分析函数

一、一元线性回归分析方法

SLOPE(A1:An, B1:Bn) 计算因变量与自变量之间的回归系数-斜率； INTERCEPT(A1:An, B1:Bn) 计算因变量与自变量之间的回归系数-截距；

FORECAST(x，A1:An, 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）试预报当铝的硬度X＝65时的抗张强度Y。

解 打开Excel工作表，将X数据输入到A1:A10，将Y数据输入到B1:B10. （1）画散点图的步骤如下

①依次单击“插入”，“图标”，“XY散点图”和“下一步”；②在弹出的对话框中的“数据区域”键入“A1:B10”,选定“系列产生在”的“列”，单击“下一步”；③在弹出的对话框“图标选项”中的“图标标题”键入硬度-强度散点图，在“X轴”键入“硬度x”，在“Y轴”键入“强度y”，单击“完成”，显示出如下散点图。

硬度-张强度散点图400张强 y30020010000204060硬度 x80100

系列1图10-4 硬度X与抗张强度Y散点图

（2）求Y对X的回归方程的步骤如下

依次单击“工具”，“数据分析”，“回归”和“确定”；②在弹出的对话框中的“X值区域”键入“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.9988

x 1.866849 0.679362 2.747946 0.025139 0.300238 3.43346

从表中可得到如下结果：

①表中第一栏Coefficients下的Intercept：188.9877和 x：1.866849分别是回归方程中常数项和x的系数，由此得到回归方程为

Y=188.9877 + 1.866849X

②表中P-value栏下的x：0.025139给出了x的回归系数b的双边检验H0:b?0,H0:b?0的p值，由于0.05大于0.025139，故拒绝原假设，认为回归效果是显著的.

③表中下限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，所以，当铝的硬度X＝65时，抗张强度Y=310.3329.

本文来源：https://www.bwwdw.com/article/thc5.html