数学建模——excel

§10.4 EXCEL在数学建模中的应用

10.4.1 简介

Microsoft Excel是目前应用最为广泛的办公室表格处理软件之一。它在数学统计中也有广泛应用。Excel具有强有力的数据库管理功能、丰富的宏命令和函数、强有力的决策支持工具，具有分析能力强、操作简便、图表能力强等特点。

10.4.2 Excel 中的统计工具简介

1．统计函数

Excel提供78个统计函数。在主菜单中的“插入”中选择“函数”，单击后就可以得到一组常用的统计函数，如均值AVERAGE、方差VAR、中位数 MEDIAN、秩RANK、最大值MAX、最小值MIN、计数COUNT，离散和连续分布的分布函数、概率函数、分位点等，如图10.所示。在选定函数的同时，在命令的下方会出现一条说明，表明命令的意义及每个参数的含义。

图10.

例如正态分布分布函数 NORMDIST，返回给定均值和标准差的正态分布分布函数或正态分布概率密度函数。

语法：NORMDIST(x, mean, standard_dev , cumulative)

说明： x 为需要计算其分布的数值，Mean 为分布的均值，Standard_dev 为分布的标准差，Cumulative 为一逻辑值，指明函数的形式。如果 cumulative 为 TRUE，函数 NORMDIST 返回分布函数；如果为 FALSE，返回概率密度函数。

（1）如果 mean 或 stand_dev 为非数值型，函数 NORMDIST 返回错误值 #VALUE!。（2）如果 standard_dev < 0，函数 NORMDIST 返回错误值 #NUM!。

（3）如果 mean= 0 且 standard_dev = 1，函数 NORMDIST 返回标准正态分布，即函数NORMSDIST。

图10.

2．统计宏

Excel 为统计分析提供了一个功能很强的统计软件包。它是一个外挂的开发产品。在安装时可以有选择地将它加载到Excel 系统环境中去，在主菜单“工具”菜单的最下面一栏，如果已存在“数据分析”命令，则直接调用该软件包。如果初次使用Excel，在“工具”菜单中没有“数据分析”命令，则使用“工具”的“加载宏”命令，从中选取“分析工具库”选项将其装入，如图10.所示。

图10.

在“数据分析”列表框内，列出了19 种可供选择的统计分析方法，囊括了基础统计学的大部分方法。如方差分析、回归分析、假设检验等。

选中某个方法后，单击“确定”按钮，弹出一个参数设置对话框，如图10.，输入分析数据的参数，就能实现一组统计计算。大多数情况下，使用统计宏进行统计计算比使用工作表中的统计函数要方便得多。但如果要求将统计结果与原始数据链接起来，则必须使用工作表函数。此外，在宏表中还可以调用与每个统计宏相对应的宏函数执行统计计算。这对于基于Excel的应用开发是一个非常重要的功能。

图10.

10.4.3 描述性统计分析

1. 频数分析

频数分布反映总体分布形状的基础数据。计算频数分布的变量可以是字符变量或者是数值变量。

例2.2某大型建筑和出租公司在本地区有一种统一的公寓出租，为指导潜在消费者的消费行

为，他们收集了每月的房屋租赁租金收取的120 个样本，见表2—4。

采用分组的方法。将这些数据输入到工作表中的A1：A121，其中A1 是数据标志“租金”。在B2 单元中填入公式：=MAX(A2：A121)；在B4 单元中填人公式：=MIN(A2:A121)；B6 中是样本数120；B8 中填入分组计算公式：=ROUND(l+3.322*LOG10(B6),0)；B10 中填入组距计算公式：=(B2-B4)/B8；为便于计算组距，定位200 并根据分组数8 我们列出C1：C9 的分组。在G2 中填人组中值计算公式：＝ROUND((D2+E2)/2,0)，随后拉出G3～G9；在H2 填入计算频数的工作表函数：=FREQUENCY(A$2:A$12l,E2)，再拉出H3～H9 得到累积频数；在I2 填入公式：=H2，在I3填人公式：=H3-H2 并拉出I4～I9 的频数；在J2填入公式：=H2/B$6，随后拉出J3～J9 得到累积频率；同样在K2 中填人公式：=I2/B$6，拉出K3～K9 得到频率。这里的$为绝对引用符号。如果在复制公式时不希望Excel 调整引用，那么请使用绝对引用。

例如，如果公式将单元格A5 乘以单元格C1 (=A5*C1)，现在将公式复制到另一单元格中，则Excel 将调整公式中的两个引用。可以在不希望改变的引用前加上美元符号（$），这样就能对单元格C1 进行绝对引用。如果要对单元格C1 进行绝对引用，请在公式中加入美元符号：=A5*$C$1

1、工作表函数

Excel 提供了一个计算频数分布的工作表函数FREQENCY，它的语法格式为：

=FREQENCY(array，bins)

array表示频数分组变量的数值区域，bins 是统计分组的组上限。除了利用工作表计算和工作表函数计算外，Excel 还提供了一个“直方图”宏来处理统计分组、编制频率分布表和画

出直方图。

2、“直方图”宏

“直方图”宏是集统计分组、编制频率分布表和绘制直方图于一体的专门用于频率分析的统计工具(见图2—6)。它的使用如下：

第一步：定义输入区域。我们选择A1：A121。由于这个区域包含了变量名“租金”(在A1 单元)，故同时选中“标志”选

择框。

第二步：定义分组方式。可以采

取Excel某种最优算法的自动统

计分组，也可以用户自动分组。

本例“接收区域”为B1：B9(事

先输入)。

第三步：定义输出区域。为了便

于对输出结果的控制和操作，我

们选择建“新工作表”的方法，

并给该工作表取名为“直方图”。

本例我们选择“输出区域”从C l 开始。

第四步：选择输出内容。如果不作任何选择，“直方图”宏只建立一个包含两列的频率分布表。第一列为组界，它取每一组的上限，最后派生的一组取名为“其他”。第二列为频数(注意中文版的标题是“频率”)。此外，还有三个选择框用于产生不同的输出形式：

(1)“柏拉图”选择框用于规定频率和直方图的排列顺序。选择“柏拉图”选择框后，直方图将按照各组频率的大小从高到低排列，同时插入两列，建立一个按频率排序的表格。(2)“累积百分率”选择框使输出结果增加累积频率分布列。在频率分布表上增加“累积频率”列以百分数形式显示累积频率，同时在直方图上添加一条累积频率折线。

(3)“图表输出”选择框使

系统在频率分布表右边显

示频率分布直方图。

本例我们选择了“累积

百分率”／“图表输出”

选择框。各选项都正确设

定后，单击“确定”按钮，

结果输出区域在Cl 开始

处，我们经过整理得到如

图2—7 所示的结果。

（2）、描述数据

1、数字特征

常用的数字特征按其功能可分为三类：

(1)集中趋势。主要包括均值、中位数、众数、四分位数、最大(小)值等。

(2)离散趋势。主要包括方差、均方差、平均差、极差等。

(3)分布趋势。主要包括偏度系数、峰度系数等。

2、计算数字特征的工作表函数

Excel 提供了21 个计算上述数字特征的函数，具体应用比较简单，本题就不详细叙述了。

3、“描述统计”宏

使用这个宏认识分析数据的一些基础

信息是最恰当的，它是描述统计分析的核

心工具。

例：某公司营销人员的每月通讯费用特别

高，其中主要一项开支是移动电话费用，

为分析手机的使用情况，公司将上个月的

所有 17 部移动电话的报销费用记录下

来。随后可能制定一项手机使用和报销最

高金额的限制规定。

第一步：组织数据。如图 2—8 所示，在

一个新工作表中 A 列输人手机费用，

第一个数值的上方单元键入统计标志

“手机费用”。

第二步：使用“描述统计”宏。从“工

具”中选择“数据分析”，呈现如图 2

—8 中的对话框双击“描述统计”将显

示图 2—9 的输入输出的提示。对话框

参数“输入区域”：$A1：$A18 包括数

据集的标志“手机费用”；“分组方式”

为“逐列”，选择“标志位于第一行”；

“输出区域”：$C$1；“汇总统计”是

描述统计分析的主要原因，必须要选择

它；“平均数置信度”本例选择 90％；第 K 大值：是使用者想知道第 K 大的值是什么，

可选项，在本例中取 4；第K 小值：本例也

取 4。格式化输出，“描述统计”自动输出报

表。

完成了上述步骤后，我们可得到一个描述

统计的输出表。

10.4.4 Excel 求解线性回归模型

1 一元线性模型

例 2.5 为了研究弹簧悬挂不同重量（单位：

克力）x 时长度（单位：厘米）Y 的变化，通

过试验得到如下一组（6 对）数据：

把这些数据点

(,)(1, (6)

i i x y i =

01 y ββ=+

输入 Excel 表格中，得到如下散点图：

从图 2—13 中可以看出，自变量x 与

因变量Y 之间存在相互关系：这 6 个

点虽然不在同一条直线上，但大致在一条

直线的周围。记这条直线为01y x b b =+。

于是，可以把i x 与i y 之间的关系表示成：

01,1,...,6i i i y x i b b e =++=

这里i e 表示试验误差，它反映了自变量x 与因变量Y 之间的不确定性关系。回归分析的目的是要根据样本11(,),...,(,)n n x y x y 找到0b 与1b 适当的估计值 0 β与

1 β，从而用经验公式

01 y ββ=+来近似刻划自变量x 与因变量Y 之间的相互关系。这个经验公式称为经验回归公式，它代表的直线称为经验回归

直线。经验回归直线我们一般用最小

二乘法来找，可得如下的0b 与1b 计算公式： 0112()( )()i i i Y x x x Y Y x x βββ?=-???--?=-??

∑∑ 通过使用 Excel 中数据分析的“回

归”宏（见图 2—14），我们可以很

快计算出 0 β与

1 β，并得到该经验

回归直线的模拟图，见图 2—15 ： 在此图中，直线为经验回归直线，点为实际数据点。Coefficients 表示回归系数，也即 0 β与

1 β得值 0 β=6.283， 1 β=0.183，R Square=0.9999,这里的 R Square 取值在 0 到1 之间，表明自变量对于回归的拟合程度，越接近于 1，表明拟合得越好。我们也称 R Square 为回归的相关系数

在回归方程中，回归系数

1 β是一个重要的未知参数，对此需要检验 0111:0(:0)H H b b =

1 β的大小反映了自变量x 对因变量Y 影响的程度。如果经检验拒绝H ，那么可以认为自变量x 对因变量Y 有显著性影响，称为回归效果显著。如果经检验不能拒绝H ，即回归效果不显著，那么原因是多方面的。这可能是因为Y 与x 并不具有如公式所表达的那种线性关系，也可能影响Y 的变量不止一个，甚至还可能是因为Y 与x 之间不存在必须重视的相互关系。对回归系数作显著性检

因变量Y 有显著性影响，称为回归效果显著。如果经检验不能拒绝H ，即回归效果不显著，那么原因是多方面的。这可能是因为Y 与x 并不具有如公式所表达的那种线性关系，也可能影响Y 的自变量不止一个，甚至还可能是因为Y 与x 之间不存在必须重视的相互关系。对回归系数作显著性检验有本质上相同的三种方法，即 t-检验法、F-检验法、相关系数检验法，用 t-检验统计量、F-检验统计量及相关系数与各临界值的比较，来检验Y 与x 之间的线性关系。在显著性水平 0.01 下，三种检验的临界值分别为验有本质上相同的三种方法，即 t-检验法、F-检验法、相关系数检验法，用 t-检验统计量、F-检验统计量及相关系数与各临界值的比较，来检验Y 与x 之间的线性关系。在显著性水平 0.01 下，三种检验的临界值分别为:0.9950.99(4) 4.6041,(1,4)21.2,0.917t F c ===

而图 2—15中的 R Square 值为 0.999，远大于c 。t Stat 即为t 值，也远远大于0.995(4)t

，

易见，检验结论都是拒绝H ，即回归效果显著。

2 多元线性模型

假定要考察p 个自变量1,...,p x x 与因变量Y 之间的相互关系，设

011...,p p Y x x b b b e =++++

其中2(0,)N e s :。我们对这一组变量1(,...,;)p x x Y 做了n 次观测，得到本观测值1(,...,;),1,...,i ip i x x Y i n =

站在抽样前的立场看，这一组样本可以表示成011...,1,...,,i i p ip i Y x x i n b b b e =++++= 其中，1,...,n e e 是独立同分布的随机变量，且都服从2(0,)N s 。这个数学模型即为p 元线性模型。其参数亦是用最小二乘法求得。利用excel 中的回归宏，我们可以计算在自变量的个数p 小于等于16时的线性模型。所有的计算不走同上，只是在自变量输入域中，以实际的P 列数（也即p 各未知量）来代替上例中的单数列，即可马上求得p+1维空间的经验回归直线。