Transcript Excel在数学建模中的应用2010

数学建模培训——
Excel的应用技巧
主讲：曹清洁
1
电子表格的历史

表格处理软件（spreadsheet）
 表格处理软件能对文字和数据的表格进行编辑、
计算、存储、打印等，并具有数据汇总、分析、
制图等功能，常用的表格处理软件有Excel和
Lotus1-2-3。一部Spreadsheet软件的演化史
就是一部轰轰烈烈、一波几折、王朝更迭、情
感交织的历史
2
VisiCalc

1979年 Dan Bricklin 和Bob Frankston 在
“苹果Ⅱ”计算机的一个叫VisiCalc（可视化
计算器）的程序；1981年他们发布了支持
“命令行宏”的VisiCalc扩展版；1985年该
公司由于法律纠纷倒闭。
3
Lotus1-2-3

1983年 Mitch Kapor设计了Lotus1-2-3，第一年销售额达
到5300万美元，其核心思想是围绕VisiCalc展开的；1985
年推出Lotus1-2-3第2版，提供8192行、256列，可以方便
简单的录制宏；它给用户一张足够大的电子数据表，并以
此为基础，将数据表、数据库管理和图形处理功能集于一
身，该软件在20世纪80年代到90年代初几乎垄断了这个
市场，成为了会计标准；但是该公司在20世纪90年代初犯
了战略性错误，重点转向开发CP/M操作系统上的Lotus版
本，没有及时开发出能很好的支持windows操作系统的版
本，市场份额锐减。目前是IBM旗下莲花软件（LOTUS）
公司的产品。
4



20世纪90年代初，微软推出了Excel；1990年Excel3.0还不如Lotus1-2-3，但
是每1到2年就改进一次；1992年Excel4.0提供了XML宏语言；1993年
Excel5.0引入一个工作薄放置多个工作表的概念，并提供VBA宏语言；20世
纪中期Excel95和Excel97进一步拓展了“数据透视表”、“自动筛选”和
“自动分类汇总”等功能，取代了市场上Lotus1-2-3的位置。
Excel把数据表、图表和数据库等功能有机地组合在一起，为用户提供了一个
集成操作环境。它继承了windows的优秀风格，具备窗口、菜单、对话框和
图标，并且增加了工具栏和快显菜单。Excel采用了新的工作簿形式，即三维
电子表格，给数据操作提供了更广阔的空间。它使用先进的智能感知技术，
简化了操作，而且可以对用户的要求智能地作出反应。Excel被推出后，迅速
在世界范围内受到欢迎和广泛应用。
目前Excel已经成为了一个集数据库软件、数学运算软件、财务软件、运筹学
（管理科学）软件和统计学软件于一体的综合性软件。而且大量加载宏软件
拓展了其各方面的功能，加载到Excel上的比较著名的统计软件有XLSTAT
（http://www.xlstat.com/en/home/）、Analyse-it（http://www.analyseit.com/）等
5

Excellent + Cell. “Cell” 是“单元格”, 是
Excel表格处理的最基本组成部分.
Microsoft 取了“Ex-cel” 这个名字, 一语双
关, 充分表达了Excel是一个优秀的表格处
理文件，同时也为了超越其他的电子表格
软件.
6


CCED是一款中文字表处理软件，由原北京乾为天电子技
术研究所开发，主要开发者是朱崇君。问世于1988年，在
DOS时代曾是中国最流行的文字处理与电子表格软件之一。
1998年以后推出了Windows版本。
上世纪九十年代中期，DOS版的CCED一度辉煌，连续多
年是中国最畅销的软件之一，是全国计算机等级考试以及
国内不少计算机教育培训指定内容。九十年代后期，
Windows操作系统普及。CCED虽然推出了Windows版本，
但在功能和界面上与Microsoft Office等成熟的“所见即所
得”办公软件相比劣势显现。尤其在盗版Microsoft Office
流行、老对手WPS借中国政府采购计划恢复生机的情况
下，CCED市场迅速萎缩，已淡出主流软件市场。
7
 Excel基本知识
 Excel在数学建模中的高级应用
技巧
8
Excel基本知识
手动输入数据
 自动输入数据
 公式的使用
 函数的引用
 数据管理与分析

9
手动输入数据
文本输入默认方式为“左对齐”.由数字组
成的字符串前加一个’号 .
 数值输入默认“右对齐”方式.输入分数时，
要先输入“0”和空格，否则作为日期型数据
处理。

10
自动输入数据
自动填充鼠标拖动填充柄向下、右拖数字
增量为1，向上、左拖增量为-1 .
 自定义序列
① 工具——选项——新序列——输入序列
内容，每个序列间按回车键——添加
② 选中序列区域——工具——选项——导
入——确定

11
12
公式的使用
用公式计算 所有的公式运算均以“=”开头。错误的公式以#开头。
（1）数学运算符： +、-、*、/、^、%（计算准则：先乘方，再乘、除，
后加、减）
（2）文本连接符：&——将两段用双引号（英文状态下）括起的文本
连接为一段连续的文本。
（3）比较运算符：>、<、=、>=、<=、<>（用于比较两个数据的大小，
比较结果是一个逻辑值，即TRUE（真）或FALSE（假））。
（4）引用运算符：冒号、空格、逗号
①冒号：用于定义一个单元格区域，以便在公式中使用。
例：=Sum(A3：A7) ——A3至A7的内容之和
②空格：交集运算符，表示只处理几个单元格区域之间互相重叠部分。
例：=Sum（A3：A5 A4：A7）——A4+A5的结果
③逗号：并集运算符，用于连接两个或多个单元格区域。
例：=Sum(A3:A5,A4:A7)——A3、A4、A5及A4、A5、A6、A7之和
(单元格有可能重复)。
（5）运算符优先级
（）——%——^——*、/——+、-、——&——比较运算符。（若优
先级相同，则按从左到右的顺序计算）
13
函数的引用
手工输入：在编辑栏或单元格内输入
 引用单元格地址:

14
相对引用
利用单元格间的相对位置关系引用单元格内容。公式中
的相对引用随单元格的移动而修改，但原来的位置不变。
例：打开文件“数据编辑”，在工作表“电视机的销售
统计表” 的G3中输入“=E3*F3，则可在G4、G5、G6、
G7、G8中填入相应公式来计算商品的销售金额。
绝对引用
指引用单元格和被引用单元格位置关系是固定的，公
式中的引用不随单元格地址变化而变化。
通常在地址前加“$”表示绝对引用。
例：打开文件“数据编辑”，计算其金额与利润值。
计算金额：在G3中输入：=E3*F3，并将之复制至G8。
计算利润：在I3中输入：=G3-（G3*$G$1+H3+E3），
然后复制到I8
混合引用
即采用行为相对地址、列为绝对地址或列为相对地址、
15
Excel的数据管理和分析
1
2
3
4
5
设计数据清单
记录编辑
排序数据
筛选数据
分类汇总数据
16
1 设计数据清单
数据清单的条件
 工作表中只含有一张数据表
 数据表应在第1行设置列标题
 数据表不应含有空行或空列
17
2 记录编辑
逐条显示
 添加记录
 删除记录
 查询记录

18
3 排序数据

排序依据：




主要关键字
第二关键字
第三关键字
排序选项：




自定义排序次序
区分大小写
排序方向
排序方法
19
4 筛选数据


筛选种类：自动筛选、高级筛选
自动筛选方式：
 前10个
 自定义
20
分类汇总数据
5

汇总步骤
 按汇总依据字段排序
 指定汇总依据字段
 设定汇总方式
 指定汇总字段

汇总显示控制
删除汇总
21
数据透视表

功能
 按多个字段分类汇总

步骤
指定数据源
选定数据区域
决定布局
确定位置
22
23
Excel在数学建模中的高级应用技巧
 Excel的数据处理功能
 用Excel绘制图表
 相关与回归分析
24
一、Excel的数据处理功能
1、Excel的函数
函 数的结构以函数名称开始，后面是左圆括
号、以逗号分隔的参数和右圆括号。
(1)函数以公式的形式出现，请在函数名称前
面键入等号（=）。
如：
=sum(A1,A4:A10,56)
25
（2）使用函数向导插入函数：按fx即可调入粘贴函数
对话框
26
例1：已知某班级学生成绩，统计其中某科成
绩为80分（含）-90分（不含）的人数。
2
e
例2：计算
例3：计算 2  ln 3 的值。
1 1
1 2
例4：求矩阵 A  2 2

 3 1
0
2
2
5
1
2

1

3
的逆矩阵
注：鼠标先选中一块4*4区，先按F2键，再同时按下Shift+Ctrl+Enter三个
键，则选定区域内出现逆矩阵的计算结果。
27
例5：利用自定义函数进行计算
（1）当x=3,2,1,0,-1,-2,-3时，计算分段函
数
 x sin x, x  0 
y x

e cos x, x  0 的值。
（2）用迭代法求非线性方程x-cosx=0的数值
解，迭代公式是xk=cos(xk-1)，取x0=1,试用
Excel计算，要求精度达到10-12。
（3）利用公式   1  1  1  2  1  2  3  1  2  3 * 4  
2
3 3 5 3 5 7 3 5 7 9
计算  的近似值，使误差小于10-14。
28
2、Excel的数据分析功能
“数据分析”的统计分析工具包，包含方差分析、回
归分析、协方差和相关系数、傅里叶分析等分析工具
首次使用时需要进行安装，方法如下：
（1）点击“工具”----”加载宏”；
（2）在弹出对话框中列出各种可以加载的项目，按
照需要选择“分析工具库”、“规划求解”等等项目，
点“确定”。
安装完后，“工具”菜单中多出了“数据分析”子菜
单，点击它，弹出对话框，显示各种数据分析工具。
29
30
31
数据分析功能：
1、描述统计
主要统计数据的平均值、中位数、标准差、方差等等
统计量
例6：某炼钢厂测了120种炉钢中的Si含量，得到
120个原始数据，见Excel.
32
33
34
2、直方图分析工具
直方图是一大批数据的频率分布图，由直方图
可以观察和分析数据的概率分布。见数据分析实例
直方图分析工具可完成数据的分组、频数分
布与累积频数的计算、绘制直方图与累积折线图
等一系列操作。以例7为操作范例，阐述直方图
分析工具的统计整理功能，其操作过程如下。
例7 ：根据抽样调查，某月某市50户居民购买
消费品支出资料。（单位：元）
35
36
首先，将样本数据排成一列，最好对数据进行排序，本例
中已利用排序操作排好序，为A1：A51。输入分组标志，
本例中为B1：B10，分别是899、999、1099、1199、
1299、1399、1499、1599、1699（如图所示）
37
然后 ，利用直方图分析工具进行分析，具体操作步骤如下。
第一步：单击“工具”菜单，选择“数据分析”
选项；打开“数据分析”对话框，从“分析工
具”列表中选择“直方图”选项，（如图1所示）
38
第二步：打开“直方图”对话框，确定输
入区域、接收区域和输出区域，（如图所示）
39



（1）“输入区域”输入待分析数据区域的单元
格引用，若输入区域有标志项，则选中“标志”
复选框；否则，系统自动生成数据标志。“接收
区域”输入接收区域的单元格引用，该框可为空，
则系统自动利用输入区域中的最小值和最大值建
立平均分布的区间间隔的分组。本例中输入区域
为$A$2:$A$51，接收区域为$B$2:$B$10。
（2）在“输出”选项中可选择输出去向，输出
去向类似于“抽样”对话框的输出去向。本例中
选择“输出区域”为$C$1。
（3）选择“柏拉图”可以在输出表中同时按降
序排列频数数据；选择“累积百分率”可在输出
表中增加一列累积百分比数值，并绘制一条百分
比曲线；选择“图表输出”可生成一个嵌入式直
40
方图。
第三步：单击“确定”按钮，在输出区域
单元格可得到频数分布，（如图所示）
41
第四步：将条形图转换成标准直方图
（如图所示）
42
二、用Excel绘制图表
（以文件“图表”为例）
图表是数据表现的另一种形式。数据的图表化就是将单
元格中的数据以各种统计图的形式显示。
1.图表种类
（1）独立的图表：单独占据一个工作表，打印时也将与数据
表分开打印。
（2）附属于工作表的嵌入式图表：它和数据源放置在同一张
工作表中，同时打印。
这两种表所依据的数据都来自工作表上选中的区域，数据
变化，则图表随之变化。
43
2．图表术语
（1）数据系列：图表中决定图形Y轴取值的数
值集合，对应工作表中的数据行。
（2）分类：图表中决定数据系列的X轴的标题
值，对应工作表中的数据列。
（3）坐标轴：图表的一边。
（4）图例：定义图表的图形的含义。
（5）网格线：帮助确定数据点在Y轴或X轴刻
度上的确切值。
44
3．创建图表
 （1）利用图表向导分四个步骤创建图表：选
中区域——图表向导工具
 （2）按F11键快速创建图表：选中区域——
F11

45
4．编辑图表
（1）选中图表元素：单击
（2）图表移动及改变大小：拖动鼠标
（3）改变图表图案、颜色、设置刻度等：双击图表
（4）改变图表类型：右键单击
（5）数据系列编辑
①添加数据系列：选中区域—拖曳到图标；选中图表—图表—添加数
据
②删除数据系列：选中系列—Delete；选中系列—编辑—清除—系列
③系列次序的调整：选中系列—格式—数据系列—系列次序
④修改系列颜色：选中系列双击
（6）修改图表中的数据
①修改图表中的数据：双击要修改数据的单元格—修改数据
②修改图表中的图形：选中系列—有间断地单击结点—拖动鼠标
（7）增加图表标题及数据标志
①双击某一系列 ②选中图表—图表—图表选项—标题（数据标志）
46
绘图实例：
用Excel绘制任意一元函数的图像
以 y  2sin x  ln(1  x2 ) 为例介绍：
47
三、用Excel进行相关与回归分析
1、用Excel计算描述统计量
2、用Excel进行相关分析
3、用Excel进行回归分析
48
三-1、用Excel计算描述统计量
（1）用函数计算描述统计量
（2）描述统计菜单项的使用
49
（1）用函数计算描述统计量

常用的描述统计量有众数、中位数、
算术平均数、调和平均数、几何平
均数、极差、四分位差、标准差、
方差、标准差系数等。下面介绍如
何用函数来计算描述统计量。
50

用函数运算有两种方法：一是手工输入函数名称及
参数。这种输入形式比较简单、快捷。但需要非常
熟悉函数名称及其参数的输入形式。所以，只有比
较简单的函数才用这种方法输入；二是函数导入法。
这是一种最为常用的办法，它适合于所有函数的使
用，而且在导入过程中有向导提示，因而非常方便。
函数的一般导入过程为：点菜单“插入”；找“函
数”，此时出现一个“插入函数”对话框；在对话
框的“选择类别”中确定函数的类别（如常用函数
或统计）；在“选择函数”内确定欲选的函数名称，
如SUM、MODE等；点“确定”后即可出现该函数
运算的对话框向导，再按向导的提示往下运行即可。
51
众数
例：为了分析小麦的分蘖情况，进行了10株
小麦的调查，如下图所示：
52
①手工输入函数名称及参数
单击任一单元格，输入“=MODE（B2：B11）”，
回车后即可得众数为14。
53
②函数导入法
点菜单“插入”；找“函数”，此时出现一个
“插入函数”对话框；在对话框的“选择类别”
中确定函数的类别“统计”；在“选择函数”
内确定欲选的函数名称“MODE”，
54
点“确定”后即可出现该函数运算的对话框向导，
在Number1处输入B2：B11或选择Excel中的B2：
B11区域。按“确定”，在Excel中即得到众数14。
55
中位数
单击任一空白单元格，
输入“=MEDIAN（B2:B11）”，
回车后得中位数为14。
56
算术平均数
单击任一空白单元格，
输入“=AVERAGE（B2:B11）”，
回车后得算术平均数为14.1。
57
几何平均数
单击任一空白单元格，
输入“=GEOMEAN（B2:B11）”，
回车后得几何平均数为14.02。
几何平均数是求一组数值的平均数的方法中的一种。
其计算公式为：
58
调和平均数
单击任一空白单元格，
输入“=HARMEAN（B2:B11）”，
回车后得调和平均数为1.422。
公式: 调和平均数=1/(1/A+1/B+1/C+1/D+……）
59
截尾平均数


将数据按由小到大顺序排列后，因数据两
端值不够稳定，按一定比例去掉头尾两端
一定数量的观察值，然后再求平均，这样
得到的平均数就是截尾平均数。如果按
2/10，即从30个数据中去掉最大的一个值
和最小的一个值，再求平均数。
单击任一空白单元格，输入
“=TRIMMEAN（B2:B11，1/10）”，回
车后得截尾平均数为14.1
60
全距

单击任一空白单元格，

输入“=MAX（B2:B11）-MIN（B2:B11）”，

回车后得全距为5。
61
标准差(估计样本）
单击任一空白单元格，
输入“=STDEV（B2:B11）”，
回车后得标准差为1.524。
62
标准差系数
单击任一空白单元格，
输入“=STDEV（B2:B11）/AVERAGE
（B2:B11）”，
回车后得标准差系数为0．1080
63
偏度系数
单击任一空白单元格，
输入“=SKEW（B2:B11）”，
回车后得偏度系数为-0.678。
64
峰度系数
单击任一空白单元格，
输入“=KURT（B2:B11）”，
回车后得峰度系数为0.6249。
65
常用的统计量函数
由于公式执行后显示的是计算结果．按Ctrl+、键(位于键
盘左上角)．可使公式在显示公式内容与显示公式结果之
间切换．
66
（2）描述统计菜单项的使用
仍使用上面的例子
我们已经把数据输入到B2:B11单元格，
然后按以下步骤操作：
67
第一步：在工具菜单中选择数据分析选项，从其对话
框中选择描述统计，按确定后打开描述统计对话框。
68
第二步：在输入区域中输入$B$1:$B$11，
在输出区域中选择$F$1，其他复选框可
根据需要选定，选择汇总统计，可给出
一系列描述统计量；选择平均数置信度，
会给出用样本平均数估计总体平均数的
置信区间；第K大值和第K小值会给出样
本中第K个大值和第K个小值。
69
第三步：单击确定，可得输出结果。
70
上面的结果中，平均指样本均值；标准误差
指样本平均数的标准差；中值即中位数；模
式指众数；标准偏差指样本标准差，自由度
为n-1；峰值即峰度系数；偏斜度即偏度系
数；区域实际上是极差，或全距；可以看出
与我们前面用函数计算的结果完全相同。最
大值为16，最小值为11，第三个最大值为
15，第三个最小值为13。
71
三-2.相关性分析
相关性分析：
判断两组数据集（可以使用不同的度量单位）
之间的关系。
相关系数（R）：
相关性计算的返回值。用来确定两个区域中数
据的变化是否相关，以及相关的程度。是两组数
据集的协方差除以它们标准偏差的乘积。
1、R>0：一个集合的较大数据与另一个集合的较大数据相对
应（正相关）
2、R<0：一个集合的较大数据与另一个集合的较小数据相对
应（负相关）
3、R=0：两个集合中的数据互不相关。
|R|<0.4
：低度相关；
0.4<=|R|<0.6:中度相关；
0.6<=|R|<0.8:高度相关；
|R|>=0.8:非常高度相关
72
1、CORREL函数： CORREL(array1,array2)
返回单元格区域 array1 和 array2 之间的相关系数
。使用相关系数可以确定两种属性之间的关系。例如
，可以检测某地的平均温度和空调使用情况之间的关
系。
Array1 第一组数值单元格区域。
Array2 第二组数值单元格区域。
如果数组或引用包含文本、逻辑值或空白单元格，这
些数值将被忽略，但是包含零值的单元格将计算在内。
如果 array1 和 array2 的数据点的数目不同，函数
CORREL 返回错误值 #N/A。
73
例 1、利用统计数据计算广告费与销售额之间的相
关系数。
相关系数
CORREL(B4:B15,C4:C15)=0.92251818
74
例2.我们收集了某厂家同一车型中旧车的车龄及其售
价数据，求价格与车龄的相关系数
75
2、使用数据分析工具求相关矩阵
利用CORREL函数只能返回两种属性之间的相关系数，如果使
用Excel提供的“数据分析”工具可以计算多组数据间的相
关系数，组成相关系数表。
例 3、总平均成绩、出勤率、选修学分与每周打工小时数的
关系。
76
例 利用“数据分析”工具计算汽车扳金、省油与价
格的满意度之间的相关系数。
汽车的扳金、省油与价格的满意度(5-很满意,1-很不满意)
扳金
4
5
4
3
3
4
1
4
3
4
5
3
2
1
省油
3
2
3
4
3
3
5
3
3
3
2
4
5
5
价格
2
1
3
3
4
3
5
3
4
3
2
3
5
5
扳金
省油
价格
扳金 省油 价格
1
-0.94
1
-0.91 0.835
1
77
三-3.回归分析
回归分析:当一个结果与一个或多个参数之间存在联系时，可以进行回归分
析，通常可由一个或多个自变量来预测一个变量的值。
回归方程:表达参数与结果之间相互关系的数学方程式(数学模型)
线性回归:如果变量与结果之间具有线性关系,我们可以用线性方程式来描
述它们之间的关系,这种回归方法叫线性回归.
非线性回归:如果变量与结果之间不具有线性关系,我们必须用非线性方程
式来描述它们之间的关系(如指数关系,对数关系等等),这种回归方法叫非
线性回归.
单回归:当一个结果只与一个参数存在联系时，进行的回归分析称为单回归
。
复回归:当一个结果与多个参数存在联系时，进行的回归分析称为复回归。
判定系数(R2):用来确定回归方程式的可解释性，即吻合程度。范围在0-1
之间，越接近1，解释性越强，即吻合程度越高。
回归方法：
1、给图表增加趋势线；
2、使用Excel提供的“数据分析工具”；
78
3、利用回归函数
1、给图表增加趋势线进行回归分析
例4(线性回归)、 我们收集了某厂家同一车型中旧车的车龄及其售价数
据，请使用给图表增加趋势线，求车龄对售价的回归方程,并计算车龄为
6.5年的旧车售价是多少.
价格(万)
56.0
48.5
42.0
37.6
32.5
28.7
22.2
18.5
15.0
12.5
旧车车龄与售价关系图
售价
车龄
1
2
3
4
5
6
7
8
9
10
60.0
50.0
40.0
30.0
20.0
10.0
0.0
y = -4.8091x + 57.8
R2 = 0.9865
0
2
4
6
车龄
6.5年的旧车车价==-4.8091*6.5+57.8=26.54
8
10
12
79
80
例5(非线性回归)、 我们收集了年龄与月收入关系的数据，请
绘制该数据的散点图，并求年龄对月收入的回归方程式。
每月所得
6000.0
10000.0
15000.0
26000.0
35000.0
42000.0
50500.0
40500.0
37650.0
30500.0
25000.0
15800.0
10200.0
8000.0
年龄与每月所得的关系图(多项式回
归)
60000.0
每月所得
年龄
15
20
25
30
35
40
45
50
55
60
65
70
75
80
40000.0
y = -36.54x 2 + 3463.7x 42087
R 2 = 0.884
20000.0
0.0
0
20
40
60
年龄
80
100
有些数据间并不是简单的线性关系，如果用线性模式求其回归方程式，判
定系数（R2）很小，根本不具有任何解释力。因此要引入非线性回归，如
多项式、指数、对数等回归方法。
81
例6(非线性回归)、 我们收集了一个原始森林中树的直径
与高度之间的关系数据,请绘制该数据的散点图，并求直
径与高度的回归方程式(指数回归)。
高度
17
25
32
35
46
58
69
75
72
78
85
树的直径与高度关系图
高度
直径
0.9
1.2
2.9
3
3.3
4
6.5
9.6
12.4
16.2
27.2
100
90
80
70
60
50
40
30
20
10
0
y = 21.512Ln(x) + 19.478
R 2 = 0.9257
0
5
10
15
直径
20
25
30
82
2、使用Excel提供的“数据分析工具” 进行回归分析
第一步：单击“工具”菜单，选择“数据分析”选
项，出现“数据分析”对话框，在分析工具中选择
“回归”。
83
用Excel进行回归分析
第二步：单击“确定”按钮，弹出“回归”对话框，在“Y
值输入区域”输入$B$1：$B$11；在“X值输入区域”输入
$C$1:$C$11，在“输出选项”选择“$E$1”，如下图所示。
多
元
回
归
分
析
84
用Excel进行回归分析
第三步：单击确定按钮，得回归分析结果如下图所示。
85
回归分析工具的输出解释
Excel回归分析工具的输出结果包括3个部分：
 (1)回归统计表
 回归统计表包括以下几部分内容：
 ①Multiple R（复相关系数R）：是R2的平方根，
又称为相关系数，用来衡量变量x和y之间相关程
度的大小。本例中R为0.6313，表示二者之间的
关系是正相关。
 ②R Square（复测定系数R2）：用来说明自变量
解释因变量变差的程度，以测定因变量y的拟合
效果。
86
回归分析工具的输出解释
③Adjusted R Square（调整复测定系数R2）：仅用
于多元回归才有意义，它用于衡量加入独立变量后
模型的拟合程度。当有新的独立变量加入后，即使
这一变量同因变量之间不相关，未经修正的R2 也要
增大，修正的R2 仅用于比较含有同一个因变量的各
种模型。
④标准误差：用来衡量拟合程度的大小，也用于计
算与回归相关的其他统计量，此值越小，说明拟合
程度越好。
⑤观测值：用于估计回归方程的数据的观测值个数。
87


（2）方差分析表
方差分析表的主要作用是通过F检验来判断回归
模型的回归效果。表中“回归分析”行计算的
是估计值同均值之差（-）的各项指标；“残差”
行是用于计算每个样本观察值与估计值之差（-）
的各项指标；“总计”行用于计算每个值同均
值之差（-）的各项指标。第二列df是自由度，
第三列SS是离差的平方和，第四列MS是均方差，
它是离差平方和除以自由度，第五列是F统计量，
第六列Significance F是在显著性水平下的Fα
的临界值
88
用Excel进行回归分析
JENSEN模型（水
分生产函数）
基本形式
y  x x
b
1
d
2
n
ETci i
Y
 (
)
Ym
ET
i 1
cmi
两边同时取对数转化为线性形式
ln(y)  b ln(x1 )  d (ln(x2 )
89
用Excel进行回归分析
90
用Excel进行回归分析
91
用Excel进行回归分析
92
用Excel进行回归分析
基本形式
y  ce
bx
两边取对数
对截距反对
数
ln( y)  ln(c)  bx
对因变量取
对数
93
用Excel进行回归分析
94
用Excel进行回归分析
95
用Excel进行回归分析
基本形式
y  a  b ln(x)
对自变量取
对数
96
用Excel进行回归分析
97
用Excel进行回归分析
产量与需水量之间的关系：抛物线
Y  ax  bx  c
2
把x2,x分别看作变量按多元回归计算即可
98
用Excel进行回归分析
99
用Excel进行回归分析
100
加入趋势线可以进行简单的回归分析，但要获得更多的统计数
据，可以使用数据分析工具，求简单相关系数、判定系数、用
F检定判定变量与自变量间是否有回归关系存在、用t检定判定
各回归系数是否不为0、计算回归系数的置信度、标准残差等
。
例 7、根据广告费与销售额统计数据使用分析工具进行回归分析。
月份 广告费(万) 销售量(万)
1
245
3000
2
280
3200
3
175
1850
4
165
1650
5
150
1500
6
190
2400
7
240
2800
8
610
2960
9
190
2400
10
150
1600
11
140
1500
12
220
2350
回归分析信息说明
101
方差分析(F检定):用回归分析检定,判定变量与自变量之间是否
有显著的回归关系存在。如果显著水准(Significance F)<a值,回
归关系存在,否则不存在。
(在这里判定系数a=1-置信度,在我们回归分析中置信度取95%,
所以a=1-95%=0.05)
方差分析
df
1
10
11
SS
MS
F
Significance F
1765442 1765442 6.726
0.026794856
2624983 262498.3
4390425
回归分析
残差
总计
结
论:
F 检定中的显著水准 Significance F = 0.026794856<a=0.05
说明:销售量与广告费存在回归关系.
102
T检定:判断回归系数与常数项是否为0
t 检定
Coefficients 标准误差 t Stat P-value Lower 95% Upper 95% 下限 95.0% 上限 95.0%
Intercept 1545.831536 315.1378 4.905255 0.0006 843.6605796 2248.002 843.66058 2248.0025
广告费(万) 3.14338351 1.212087 2.593364 0.0268 0.442684371 5.844083 0.44268437 5.8440827
Intercept:回归方程中的常数项信息
广告费(万):自变量X的回归系数信息
结
论:
t 检定结果中，常数项为1545.832，P-value = 0.00061826<a=0.05 说明:回归方程式的常数项不应为
0，不可将其省略。
自变量广告费的显著水准 P-value = 0.026795<a=0.05
说明：广告费与销售量间存在直线关系，其系数为3.14338351，正相关。
回归方程式为:
Y=3.143384X+1545.832
103
例8.我们收集了某厂家同一车型中旧车的车龄及其售价数
据，请使用数据分析工具，求车龄对售价的回归方程
车龄
1
2
3
4
5
6
7
8
9
10
价格(万)
56.0
48.5
42.0
37.6
32.5
28.7
22.2
18.5
15.0
12.5
SUMMARY OUTPUT
回归统计
Multiple R
R Square
Adjusted R Square
标准误差
观测值
0.993241
0.986527
0.984843
1.804792
10
方差分析
df
回归分析
残差
总计
SS
MS
F
Significance F
1 1908.007 1908.007 585.7682
9.0595E-09
8 26.05818 3.257273
9 1934.065
Coefficients 标准误差 t Stat P-value Lower 95% Upper 95%下限 95.0%上限 95.0%
Intercept
57.8 1.232907 46.88106 4.74E-11 54.9569086 60.64309 54.95691 60.64309
车龄
-4.809090909 0.198701 -24.2026 9.06E-09 -5.2672966 -4.35089 -5.2673 -4.35089
y = -4.8091x + 57.8
104
练习题:以下是银行客户的存款金额与贷款金额对照表,用数据分析
工具求存款金额对贷款金额的回归方程,并预测当存款金额为80千
万时,贷款金额为多少?
存款金额 贷款金额
(千万)
(千万)
65
54
102
87
42
34
58
48
95
81
73
62
32
25
124
107
135
117
45
37
回归统计
Multiple R
R Square
Adjusted R Square
标准误差
观测值
0.999956
0.999912
0.999901
0.314492
10
方差分析
df
回归分析
残差
总计
SS
MS
F Significance F
1 8970.809 8970.8 90701
1.65436E-17
8 0.791241 0.0989
9 8971.6
Coefficients 标准误差 t Stat P-value Lower 95% Upper 95% 下限 95.0% 上限 95.0%
Intercept
-3.44460939 0.248681 -13.85 7E-07 -4.018069372 -2.87115 -4.018069 -2.8711494
存款金额(千万) 0.890332158 0.002956 301.17 2E-17 0.883514957 0.897149 0.883515 0.89714936
y = 0.8903x –3.4461
105
复回归
通过给图表加入趋势线只能进行简单的单一参数的回归分析，
但在许多情况下需要使用多个自变量来预测一个变量的情况,
这种回归分析叫复回归，复回归必须通过数据分析工具进行。
106
例9、银行为了核发信用卡，收集了申请人的每月总收入、不
动产、动产、每月房贷与抚养支出费用等数据，并以业务主
管员的经验，主观地给予一个信用分数。为使评估信用分数
能有一套公式，免得老是依赖主管评分，请使用复回归求其
回归方程式。
每月总收 不动产
入(万) (百万)
6.5
12.0
7.2
8.0
3.8
0.0
8.5
15.0
4.2
0.0
6.3
6.0
3.0
0.0
3.2
3.0
动产
每月房
(百万) 贷(万)
3.0
2.0
2.0
0.0
1.0
0.0
3.5
2.8
0.5
0.0
2.0
1.8
0.6
0.0
1.0
0.0
抚养费
(万)
2.0
2.0
1.0
2.0
1.5
1.5
0.0
2.0
信用
指数
82
86
70
90
75
80
70
65
y=a1x1+a2x2+a3x3+a4x4+a5x5+b
107
回归统计
Multiple R
R Square
Adjusted R Square
标准误差
观测值
0.990989406
0.982060003
0.93721001
2.179360967
8
方差分析
df
回归分析
残差
总计
SS
MS
F
Significance F
5
520 104 21.9
0.044248345
2 9.499 4.75
7 529.5
Coefficients
Intercept
57.07610213
每月总收入(万) 5.350912705
不动产(百万) 0.703920828
动产(百万)
-4.9618931
每月房贷(万) -0.089892521
抚养费(万)
-2.499189095
结 论:
2
R=0.999099,R =0.98206,调整后的R=0.93721
说明:回归方程式可解释信用分数的差异程度相当高.
结
论:
F 检定中的显著水准 Significance F = 0.044248<a=0.05
说明:每月总收入、不动产、动产、每月房贷、抚养
支出与信用分数整体间有明显回归关系存在。
标准误差 t Stat P-value
4.9504323 11.53 0.00744
0.995484 5.375 0.03291
0.9303825 0.757 0.52827
5.4451066 -0.91 0.45835
1.7158095 -0.05 0.96298
1.7049759 -1.47 0.28034
Lower 95% Upper 95%
35.7760961 78.376108
1.06768767 9.6341377
-3.2991947 4.7070363
-28.390312 18.466526
-7.4724301 7.292645
-9.8351133 4.8367351
下限 95.0%
35.7760961
1.06768767
-3.2991947
-28.390312
-7.4724301
-9.8351133
上限 95.0%
78.3761082
9.63413774
4.70703632
18.466526
7.29264503
4.83673514
结
论:
t 检定结果中，常数项为57.0761,其显著水准 P-value = 0.007439<a=0.05
说明:回归方程式的常数项不应为0，不可将其省略。
其它五个自变量中，只有每月总收入的显著水准 P-value = 0.032912<a=0.05
说明：每月总收入与信用分数间存在直线关系，其系数为5.350913，正相关。
其余的不动产、动产、每月房贷、抚养支出等四个参数的显著水准（P-value）均大于a=0.05
说明：这些参数与信用分数间不存在显著的直线关系，所以可将这些参数的系数
从回归方程式中排除掉，最后的回归方程式为：Y=5.350913X+57.0761。
108
即：信用分数=5.350913*每月总收入+57.0761。
例10.我们收集了某厂家同一车型中旧车的车龄,行驶里程与售价数据，
请使用数据分析工具，求车龄,行驶里程对售价的回归方程
里程(万
车龄
公里)
1
2
3
4
5
6
7
8
9
10
8.5
21
35
43
50
58
65
76
80
90
价格
(万)
61.0
53.5
46.5
41.5
37.0
32.0
27.5
21.0
18.0
12.0
SUMMARY
OUTPUT
Multiple
R Square
Adjusted
标准误差
观测值
回归统计
R
R
Square
0.9999
0.9999
0.9998
0.2055
10
方差分析
df
回归分析
残差
总计
Coefficients 标准误差
Intercept 65.99587242 0.186263
车龄
-1.540712946 0.207237
里程
-0.427767355 0.023844
t Stat
354.315
-7.4345
-17.94
SS
MS
F
Significance F
2 2275.7 1137.85 26937.35
2.49919E-14
7 0.2957 0.04224
9 2276
P-value
3.77E-16
0.000145
4.13E-07
Lower 95%
65.5554305
-2.0307505
-0.4841495
Upper 95% 下限 95.0%
66.436314 65.555431
-1.050675 -2.030751
-0.371385 -0.48415
y = -1.5407*车龄-0.4278*里程 + 65.9959
上限 95.0%
66.4363143
-1.0506754
-0.3713852
109
练习 影响出勤率的因素分析,1-5代表影响程度，请使
用复回归求其回归方程式。
110
非线性的复回归
例11.我们收集了年龄与月收入关系的数据，使用数据分
析工具求年龄对月收入的回归方程式。
年龄 每月所得
15
6,000
20
10,000
25
15,000
30
26,000
35
35,000
40
42,000
45
50,500
50
40,500
55
37,650
60
30,500
65
25,000
70
15,800
75
10,200
80
8,000
年龄平方
225
400
625
900
1225
1600
2025
2500
3025
3600
4225
4900
5625
6400
年龄 每月所得
15
6,000
20
10,000
25
15,000
30
26,000
35
35,000
40
42,000
45
50,500
50
40,500
55
37,650
60
30,500
65
25,000
70
15,800
75
10,200
80
8,000
y=a1x2+a2x+b
111
回归统计
Multiple R
R Square
Adjusted R Square
标准误差
观测值
0.94023005
0.88403254
0.86294755
5383.55004
14
方差分析
df
回归分析
残差
总计
Intercept
年龄平方
年龄
Coefficients
-42087.047
-36.539835
3463.74588
SS
MS
F
Significance F
2 2.43E+09 1.22E+09 41.9271 7.1425E-06
11 3.19E+08 28982611
13 2.75E+09
P-value
标准误差 t Stat
8250.414 -5.1012 0.000343
3.990553 -9.15658 1.77E-06
385.7649 8.978904 2.14E-06
Lower 95% Upper 95% 下限 95.0%上限 95.0%
-60246.0938
-23928 -60246.09
-23928
-45.3229871 -27.75668 -45.32299 -27.75668
2614.68262 4312.8091 2614.6826 4312.8091
y = -36.54x2 + 3463.7x - 42087
112
3、使用回归函数进行回归分析
(1)直线回归函数LINEST()
使用最小平方法计算最适合于变量区域的回归直线公式,并传
回该直线公式的数组。可以用于单回归和复回归.
语法:LINEST(变量区域,自变量区域,常数项是否不为零,是否
返回附加的统计值)
操作方法:1.选定输出区域
2.输入公式
3.Ctrl+Shift+Enter
单回归(y=bx+c)输出结果
系数(b)
常数
标准误差(b)
标准误差
2
判定系数(R )
F统计值
回归平方
对y估计值的标准误差
F检定之自由度
残差平方
9.184629803
1.215127545
0.851039787
57.13202018
3000312.402
299.868791
261.652449
229.162446
10
525154.264
例12.以广告费与销售量的单一参数数据为例使用LINEST函数,
求线性回归方程式。
y=9.1846x+299.8688
113
复回归(y=bnx+bn-1x+…+b1x+c)输出结果
… 系数(b )
标准误差(b ) 标准误差(b ) … 标准误差(b )
系数(bn)
系数(bn-1)
1
n
n-1
1
2
2
2
常数(c)
标准误差
判定系数(R ) 判定系数(R )
判定系数(R ) 对y估计值的标准误差
F统计值 F统计值
F统计值
F检定之自由度
回归平方 回归平方
回归平方 残差平方
例13.我们收集了某厂家同一车型中旧车的车龄,行驶里程与售
价数据，请使用LINEST函数，求车龄,行驶里程对售价的回归方
程车龄 里程(万 价格
1
2
3
4
5
6
7
8
9
10
公里)
(万)
8.5
21
35
43
50
58
65
76
80
90
61.0
53.5
46.5
41.5
37.0
32.0
27.5
21.0
18.0
12.0
-0.42777
0.023844
0.99987
26937.35
2275.704
-1.54071 65.99587
0.207237 0.186263
0.205525
#N/A
7
#N/A
0.295685
#N/A
y = -1.5407*车龄-0.4278*里程 + 65.9959
114
(2) 线性预测函数(FORECAST)
根据给定的数据计算或预测未来值。以数组或数据区域的形式给定 x 值和
y 值后，返回基于 x 的线性回归预测值。使用此函数可以对未来销售额、
库存需求或消费趋势进行预测。
FORECAST(x,known_y,known_x)
X 为需要进行预测的数据点。
Known_y 为因变量数组或数据区域。
Known_x 为自变量数组或数据区域。
例 14.以广告费与销售量的单一参数数据为例使用
Forecast函数预测当广告费用为500万元时销售量为多少。
FORECAST(500,$C$2:$C$13,$B$2:$B$13)=4892万
115
(3) 线性趋势函数（TREND）
返回一条线性回归拟合线的一组纵坐标值（y 值）。即找到适合给定的数
组 known_y 和 known_x 的直线（用最小二乘法），并返回指定数组
new_x's 值在直线上对应的 y 值。
语法：TREND(known_y,known_x,new_x,const)
操作方法:1.选定输出区域
2.输入公式
3.Ctrl+Shift+Enter
例15 、以广告费与销售量的单一参数数据为例使用TREND函数预测一组广
告费的销售量。
116
(4) 指数回归函数
• LOGEST：指数回归函数，计算最符合观测数据组的指数回归
拟合曲线，并返回描述该曲线的数组。此曲线的方程为：
y = b*mx or y = (b*(m1x1)*(m2x2)*…) （如果有多个 x 值）
LOGEST(known_y's,known_x's,const,stats)
• GROWTH:指数趋势函数，根据给定的数据预测指数增长值。
根据已知的 x 值和 y 值，函数 GROWTH 返回一组新的 x 值
对应的 y 值。可以使用 GROWTH 工作表函数来拟合满足给定
x 值和 y 值的指数曲线。
语法：GROWTH(known_y's,known_x's,new_x's,const)
选定输出区域—输入公式—Ctrl+Shift+Enter
117
118