1

数学建模培训——Excel 的应用技巧

主讲：曹清洁

2

电子表格的历史 表格处理软件（ spreadsheet ）

表格处理软件能对文字和数据的表格进行编辑、计算、存储、打印等，并具有数据汇总、分析、制图等功能，常用的表格处理软件有 Excel 和Lotus1-2-3 。一部 Spreadsheet 软件的演化史就是一部轰轰烈烈、一波几折、王朝更迭、情感交织的历史

3

VisiCalc

1979 年 Dan Bricklin 和 Bob Frankston 在 “苹果Ⅱ”计算机的一个叫 VisiCalc（可视化计算器）的程序； 1981 年他们发布了支持“命令行宏”的 VisiCalc 扩展版；1985 年该公司由于法律纠纷倒闭。

4

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 ）公司的产品。

5

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.analyse-it.com/ ）等

6

Excellent + Cell. “Cell” 是“单元格” , 是Excel 表格处理的最基本组成部分 . Microsoft 取了“ Ex-cel” 这个名字 , 一语双关 , 充分表达了 Excel 是一个优秀的表格处理文件，同时也为了超越其他的电子表格软件 .

7

CCED 是一款中文字表处理软件，由原北京乾为天电子技术研究所开发，主要开发者是朱崇君。问世于 1988 年，在 DOS时代曾是中国最流行的文字处理与电子表格软件之一。 1998 年以后推出了 Windows 版本。

上世纪九十年代中期， DOS 版的 CCED 一度辉煌，连续多年是中国最畅销的软件之一，是全国计算机等级考试以及国内不少计算机教育培训指定内容。九十年代后期， Windows 操作系统普及。 CCED虽然推出了 Windows 版本，但在功能和界面上与Microsoft Office 等成熟的“所见即所得”办公软件相比劣势显现。尤其在盗版 Microsoft Office流行、老对手WPS借中国政府采购计划恢复生机的情况下， CCED 市场迅速萎缩，已淡出主流软件市场。

8

Excel 基本知识Excel 在数学建模中的高级应用

技巧

9

Excel 基本知识

手动输入数据 自动输入数据 公式的使用 函数的引用 数据管理与分析

10

手动输入数据 文本输入默认方式为“左对齐” . 由数字组

成的字符串前加一个’号 . 数值输入默认“右对齐”方式 .输入分数时，

要先输入“ 0” 和空格，否则作为日期型数据处理。

11

自动输入数据 自动填充鼠标拖动填充柄向下、右拖数字

增量为 1 ，向上、左拖增量为 -1 . 自定义序列 ① 工具——选项——新序列——输入序列内容，每个序列间按回车键——添加

② 选中序列区域——工具——选项——导入——确定

12

13

公式的使用用公式计算 所有的公式运算均以“ =” 开头。错误的公式以 # 开头。（ 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 ）运算符优先级 （）——%——^——* 、 /——+ 、 - 、—— &——比较运算符。（若优

先级相同，则按从左到右的顺序计算）

14

函数的引用 手工输入：在编辑栏或单元格内输入 引用单元格地址 :

15

相对引用 利用单元格间的相对位置关系引用单元格内容。公式中的相对引用随单元格的移动而修改，但原来的位置不变。 例：打开文件“数据编辑”，在工作表“电视机的销售统计表” 的 G3 中输入“ =E3*F3 ，则可在 G4 、 G5 、G6 、 G7 、 G8 中填入相应公式来计算商品的销售金额。绝对引用 指引用单元格和被引用单元格位置关系是固定的，公式中的引用不随单元格地址变化而变化。 通常在地址前加“ $” 表示绝对引用。 例：打开文件“数据编辑”，计算其金额与利润值。 计算金额：在 G3 中输入： =E3*F3 ，并将之复制至 G8 。 计算利润：在 I3 中输入： =G3- （ G3*$G$1+H3+E3 ），然后复制到 I8混合引用 即采用行为相对地址、列为绝对地址或列为相对地址、行为绝对地址来表示地址。

16

Excel 的数据管理和分析

1      设计数据清单2      记录编辑3      排序数据4      筛选数据5      分类汇总数据

17

1 设计数据清单

数据清单的条件工作表中只含有一张数据表

数据表应在第 1 行设置列标题

数据表不应含有空行或空列

18

2    记录编辑

逐条显示添加记录删除记录查询记录

19

3    排序数据 排序依据：

主要关键字 第二关键字 第三关键字

排序选项： 自定义排序次序 区分大小写 排序方向 排序方法

20

4    筛选数据 筛选种类：自动筛选、高级筛选 自动筛选方式：

前 10 个

自定义

21

5     分类汇总数据 汇总步骤

按汇总依据字段排序指定汇总依据字段 设定汇总方式指定汇总字段

汇总显示控制

删除汇总删除汇总

22

数据透视表 功能

按多个字段分类汇总 步骤

指定数据源选定数据区域决定布局确定位置

23

24

Excel 在数学建模中的高级应用技巧

Excel 的数据处理功能用 Excel 绘制图表相关与回归分析

25

一、 Excel 的数据处理功能

1、 Excel 的函数

函 数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。 (1) 函数以公式的形式出现，请在函数名称前面键入等号（ =）。 如： =sum(A1,A4:A10,56)

26

（ 2）使用函数向导插入函数：按 fx即可调入粘贴函数对话框

27

例 1：已知某班级学生成绩，统计其中某科成绩为 80分（含） -90 分（不含）的人数。

例 2：计算 2e

例 3：计算 的值。

例 4 ：求矩阵 的逆矩阵

1 1 0 1

1 2 2 2

2 2 2 1

3 1 5 3

A

3ln2

注：鼠标先选中一块 4*4区，先按 F2键，再同时按下 Shift+Ctrl+Enter 三个键，则选定区域内出现逆矩阵的计算结果。

28

例 5：利用自定义函数进行计算（ 1）当 x=3,2,1,0,-1,-2,-3 时，计算分段函数

的值。（ 2）用迭代法求非线性方程 x-cosx=0 的数值解，迭代公式是 xk=cos(xk-1) ，取 x0=1,试用 Excel 计算，要求精度达到 10-12。

（ 3 ）利用公式 1 1 2 1 2 3 1 2 3 41 *

2 3 3 5 3 5 7 3 5 7 9

sin , 0

cos , 0x

x x xy

e x x

计算 的近似值，使误差小于 10-14 。

29

2 、 Excel 的数据分析功能

“数据分析”的统计分析工具包，包含方差分析、回归分析、协方差和相关系数、傅里叶分析等分析工具

首次使用时需要进行安装，方法如下：

（ 1 ）点击“工具” ----” 加载宏”；

（ 2 ）在弹出对话框中列出各种可以加载的项目，按照需要选择“分析工具库”、“规划求解”等等项目，点“确定”。

安装完后，“工具”菜单中多出了“数据分析”子菜单，点击它，弹出对话框，显示各种数据分析工具。

30

31

32

数据分析功能：1、描述统计 主要统计数据的平均值、中位数、标准差、方差等等统计量

例 6：某炼钢厂测了 120 种炉钢中的 Si含量，得到 120 个原始数据，见 Excel.

33

34

35

2 、直方图分析工具

直方图分析工具可完成数据的分组、频数分布与累积频数的计算、绘制直方图与累积折线图等一系列操作。以例 7 为操作范例，阐述直方图分析工具的统计整理功能，其操作过程如下。

直方图是一大批数据的频率分布图，由直方图可以观察和分析数据的概率分布。见数据分析实例

例 7 ：根据抽样调查，某月某市 50 户居民购买消费品支出资料。（单位：元）（单位：元）

36

37

首先，将样本数据排成一列，最好对数据进行排序，本例中已利用排序操作排好序，为 A1： A51 。输入分组标志，本例中为 B1： B10 ，分别是 899 、 999 、 1099 、 1199 、 1299 、 1399 、 1499 、 1599 、 1699 （如图所示）

然后 ，利用直方图分析工具进行分析，具体操作步骤如下。然后 ，利用直方图分析工具进行分析，具体操作步骤如下。

38

第一步：单击“工具”菜单，选择“数据分析”选项；打开“数据分析”对话框，从“分析工具”列表中选择“直方图”选项，（如图 1所示）

39

第二步：打开“直方图”对话框，确定输入区域、接收区域和输出区域，（如图所示）

40

（ 1 ）“输入区域”输入待分析数据区域的单元格引用，若输入区域有标志项，则选中“标志”复选框；否则，系统自动生成数据标志。“接收区域”输入接收区域的单元格引用，该框可为空，则系统自动利用输入区域中的最小值和最大值建立平均分布的区间间隔的分组。本例中输入区域为 $A$2:$A$51 ，接收区域为 $B$2:$B$10 。

（ 2 ）在“输出”选项中可选择输出去向，输出去向类似于“抽样”对话框的输出去向。本例中选择“输出区域”为 $C$1 。

（ 3 ）选择“柏拉图”可以在输出表中同时按降序排列频数数据；选择“累积百分率”可在输出表中增加一列累积百分比数值，并绘制一条百分比曲线；选择“图表输出”可生成一个嵌入式直方图。

41

第三步：单击“确定”按钮，在输出区域单元格可得到频数分布，（如图所示）

42

第四步：将条形图转换成标准直方图 （如图所示）

43

二、用 Excel 绘制图表 图表是数据表现的另一种形式。数据的图表化就是将单元格中的数据以各种统计图的形式显示。

1. 图表种类（ 1 ）独立的图表：单独占据一个工作表，打印时也将与数据表分开打印。（ 2 ）附属于工作表的嵌入式图表：它和数据源放置在同一张工作表中，同时打印。 这两种表所依据的数据都来自工作表上选中的区域，数据变化，则图表随之变化。

（以文件“图表”为例）

44

2 ．图表术语（ 1 ）数据系列：图表中决定图形 Y轴取值的

数值集合，对应工作表中的数据行。（ 2 ）分类：图表中决定数据系列的 X轴的标题值，对应工作表中的数据列。

（ 3 ）坐标轴：图表的一边。（ 4 ）图例：定义图表的图形的含义。（ 5 ）网格线：帮助确定数据点在 Y轴或 X轴刻度上的确切值。

45

3 ．创建图表 （ 1 ）利用图表向导分四个步骤创建图表：

选中区域——图表向导工具 （ 2 ）按 F11键快速创建图表：选中区域—— F11

46

4 ．编辑图表（ 1 ）选中图表元素：单击（ 2 ）图表移动及改变大小：拖动鼠标（ 3 ）改变图表图案、颜色、设置刻度等：双击图表（ 4 ）改变图表类型：右键单击（ 5 ）数据系列编辑① 添加数据系列：选中区域—拖曳到图标；选中图表—图表—添加数

据② 删除数据系列：选中系列— Delete ；选中系列—编辑—清除—系

列③ 系列次序的调整：选中系列—格式—数据系列—系列次序④ 修改系列颜色：选中系列双击（ 6 ）修改图表中的数据① 修改图表中的数据：双击要修改数据的单元格—修改数据 ② 修改图表中的图形：选中系列—有间断地单击结点—拖动鼠标（ 7 ）增加图表标题及数据标志①双击某一系列 ②选中图表—图表—图表选项—标题（数据标志）

47

绘图实例： 用 Excel绘制任意一元函数的图像

以 为例介绍：22sin ln(1 )y x x

48

1 、用 Excel 计算描述统计量

2 、用 Excel 进行相关分析

3 、用 Excel 进行回归分析

三、用 Excel 进行相关与回归分析

49

三 -1 、用 Excel 计算描述统计量

（ 1 ）用函数计算描述统计量

（ 2 ）描述统计菜单项的使用

50

常用的描述统计量有众数、中位数、算术

平均数、调和平均数、几何平均数、极差、

四分位差、标准差、方差、标准差系数等。

下面介绍如何用函数来计算描述统计量。

（ 1 ）用函数计算描述统计量

51

用函数运算有两种方法：一是手工输入函数名称及参数。这种输入形式比较简单、快捷。但需要非常熟悉函数名称及其参数的输入形式。所以，只有比较简单的函数才用这种方法输入；二是函数导入法。这是一种最为常用的办法，它适合于所有函数的使用，而且在导入过程中有向导提示，因而非常方便。函数的一般导入过程为：点菜单“插入”；找“函数”，此时出现一个“插入函数”对话框；在对话框的“选择类别”中确定函数的类别（如常用函数或统计）；在“选择函数”内确定欲选的函数名称，如 SUM 、 MODE 等；点“确定”后即可出现该函数运算的对话框向导，再按向导的提示往下运行即可。

52

众数

例：为了分析小麦的分蘖情况，进行了 10株小麦的调查，如下图所示：

53

①手工输入函数名称及参数

单击任一单元格，输入“ =MODE （ B2： B11 ）”，回车后即可得众数为 14 。

54

②函数导入法点菜单“插入”；找“函数”，此时出现一个“插入函数”对话框；在对话框的“选择类别”中确定函数的类别“统计”；在“选择函数”内确定欲选的函数名称“ MODE” ，

55

点“确定”后即可出现该函数运算的对话框向导，在 Number1 处输入 B2： B11或选择 Excel 中的 B2： B11区域。按“确定”，在 Excel 中即得到众数 14 。

56

中位数

单击任一空白单元格，

输入“ =MEDIAN （ B2:B11 ）”，

回车后得中位数为 14 。

57

算术平均数

单击任一空白单元格，

输入“ =AVERAGE （ B2:B11 ）”，

回车后得算术平均数为 14.1 。

58

几何平均数

单击任一空白单元格，

输入“ =GEOMEAN （ B2:B11 ）”，

回车后得几何平均数为 14.02 。几何平均数是求一组数值的平均数的方法中的一种。其计算公式为：

59

调和平均数

单击任一空白单元格，

输入“ =HARMEAN （ B2:B11 ）”，

回车后得调和平均数为 1.422 。

公式 : 调和平均数 =1/(1/A+1/B+1/C+1/D+…… ）

60

截尾平均数 将数据按由小到大顺序排列后，因数据两端值不

够稳定，按一定比例去掉头尾两端一定数量的观察值，然后再求平均，这样得到的平均数就是截尾平均数。如果按 2/10 ，即从 30 个数据中去掉最大的一个值和最小的一个值，再求平均数。

单击任一空白单元格，输入“ =TRIMMEAN （ B2:B11 ， 1/10 ）”，回车后得截尾平均数为 14.1

61

全距

单击任一空白单元格，

输入“ =MAX （ B2:B11 ） -MIN （ B2:B11 ）”，

回车后得全距为 5 。

62

标准差 ( 估计样本）

单击任一空白单元格，

输入“ =STDEV （ B2:B11 ）”，

回车后得标准差为 1.524 。

63

标准差系数

单击任一空白单元格，

输入“ =STDEV （ B2:B11 ） /AVERAGE （ B2:B1

1 ）”，

回车后得标准差系数为 0． 1080

64

偏度系数

单击任一空白单元格，

输入“ =SKEW （ B2:B11 ）”，

回车后得偏度系数为 -0.678 。

65

峰度系数

单击任一空白单元格，

输入“ =KURT （ B2:B11 ）”，

回车后得峰度系数为 0.6249 。

66

常用的统计量函数由于公式执行后显示的是计算结果．按 Ctrl+、键 (位于键盘左上角 )．可使公式在显示公式内容与显示公式结果之间切换．

67

（ 2 ）描述统计菜单项的使用

仍使用上面的例子

我们已经把数据输入到 B2:B11 单元格，

然后按以下步骤操作：

68

第一步：在工具菜单中选择数据分析选项，从其对话框中选择描述统计，按确定后打开描述统计对话框。

69

第二步：在输入区域中输入 $B$1:$B$11 ，在输出区域中选择 $F$1 ，其他复选框可根据需要选定，选择汇总统计，可给出一系列描述统计量；选择平均数置信度，会给出用样本平均数估计总体平均数的置信区间；第 K 大值和第 K小值会给出样本中第 K 个大值和第 K 个小值。

70

第三步：单击确定，可得输出结果。

71

上面的结果中，平均指样本均值；标准误差指样本平均数的标准差；中值即中位数；模式指众数；标准偏差指样本标准差，自由度为 n-1 ；峰值即峰度系数；偏斜度即偏度系数；区域实际上是极差，或全距；可以看出与我们前面用函数计算的结果完全相同。最大值为 16 ，最小值为 11 ，第三个最大值为 15 ，第三个最小值为 13 。

72

三 -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: 非常高度相关

73

1 、 CORREL 函数： CORREL(array1,array2)返回单元格区域 array1 和 array2 之间的相关系数。使用相关系数可以确定两种属性之间的关系。例如，可以检测某地的平均温度和空调使用情况之间的关系。Array1 第一组数值单元格区域。Array2 第二组数值单元格区域。如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略，但是包含零值的单元格将计算在内。

如果 array1 和 array2 的数据点的数目不同，函数 CORREL 返回错误值 #N/A。

74

例 1 、利用统计数据计算广告费与销售额之间的相关系数。

相关系数CORREL(B4:B15,C4:C15)=0.92251818

75

例 2. 我们收集了某厂家同一车型中旧车的车龄及其售价数据，求价格与车龄的相关系数

76

例 3 、总平均成绩、出勤率、选修学分与每周打工小时数的关系。

2 、使用数据分析工具求相关矩阵 利用 CORREL 函数只能返回两种属性之间的相关系数，如果使用 Excel提供的“数据分析”工具可以计算多组数据间的相关系数，组成相关系数表。

77

例 利用“数据分析”工具计算汽车扳金、省油与价格的满意度之间的相关系数。

汽车的扳金、省油与价格的满意度(5-很满意,1-很不满意)扳金 省油 价格

4 3 25 2 14 3 33 4 33 3 44 3 31 5 54 3 33 3 44 3 35 2 23 4 32 5 51 5 5

扳金 省油 价格扳金 1省油 -0. 94 1价格 -0. 91 0. 835 1

78

回归分析 :当一个结果与一个或多个参数之间存在联系时，可以进行回归分析，通常可由一个或多个自变量来预测一个变量的值。回归方程 : 表达参数与结果之间相互关系的数学方程式 (数学模型 )线性回归 : 如果变量与结果之间具有线性关系 , 我们可以用线性方程式来描述它们之间的关系 ,这种回归方法叫线性回归 .非线性回归 : 如果变量与结果之间不具有线性关系 , 我们必须用非线性方程式来描述它们之间的关系 (如指数关系 ,对数关系等等 ),这种回归方法叫非线性回归 .单回归 :当一个结果只与一个参数存在联系时，进行的回归分析称为单回归。复回归 :当一个结果与多个参数存在联系时，进行的回归分析称为复回归。判定系数 (R2): 用来确定回归方程式的可解释性，即吻合程度。范围在 0-1之间，越接近 1 ，解释性越强，即吻合程度越高。回归方法：1 、给图表增加趋势线；2 、使用 Excel提供的“数据分析工具”；3 、利用回归函数

三 -3. 回归分析

79

例 4(线性回归 )、 我们收集了某厂家同一车型中旧车的车龄及其售价数据，请使用给图表增加趋势线，求车龄对售价的回归方程 ,并计算车龄为 6.5 年的旧车售价是多少 .

车龄 价格 ( 万 )1 56.02 48.53 42.04 37.65 32.56 28.77 22.28 18.59 15.0

10 12.5

旧车车龄与售价关系图

y = - 4. 8091x + 57. 8R2 = 0. 9865

0. 0

10. 0

20. 0

30. 0

40. 0

50. 0

60. 0

0 2 4 6 8 10 12

车龄

售价

6.5 年的旧车车价 ==-4.8091*6.5+57.8=26.54

1 、给图表增加趋势线进行回归分析

80

81

有些数据间并不是简单的线性关系，如果用线性模式求其回归方程式，判定系数（ R2 ）很小，根本不具有任何解释力。因此要引入非线性回归，如多项式、指数、对数等回归方法。

例 5( 非线性回归 ) 、 我们收集了年龄与月收入关系的数据，请绘制该数据的散点图，并求年龄对月收入的回归方程式。年龄 每月所得

15 6000. 020 10000. 025 15000. 030 26000. 035 35000. 040 42000. 045 50500. 050 40500. 055 37650. 060 30500. 065 25000. 070 15800. 075 10200. 080 8000. 0

(年龄与每月所得的关系图 多项式回)归

y = - 36. 54x2 + 3463. 7x -42087

R2 = 0. 8840. 0

20000. 0

40000. 0

60000. 0

0 20 40 60 80 100

年龄

每月所得

82

例 6( 非线性回归 ) 、 我们收集了一个原始森林中树的直径与高度之间的关系数据 , 请绘制该数据的散点图，并求直径与高度的回归方程式 (指数回归 ) 。

直径 高度0. 9 171. 2 252. 9 323 35

3. 3 464 58

6. 5 699. 6 7512. 4 7216. 2 7827. 2 85

树的直径与高度关系图

y = 21. 512Ln(x) + 19. 478R2 = 0. 9257

0102030405060708090

100

0 5 10 15 20 25 30

直径

高度

83

第一步：单击“工具”菜单，选择“数据分析”选项，出现“数据分析”对话框，在分析工具中选择“回归”。

22 、使用、使用 ExcelExcel 提供的“数据分析工具”提供的“数据分析工具” 进行回归分析进行回归分析

84

第二步：单击“确定”按钮，弹出“回归”对话框，在“ Y值输入区域”输入 $B$1： $B$11 ；在“ X值输入区域”输入 $C$1:$C$11 ，在“输出选项”选择“ $E$1” ，如下图所示。

多元回归分析

多元回归分析

用用 ExcelExcel进行回归分析进行回归分析

85

第三步：单击确定按钮，得回归分析结果如下图所示。用用 ExcelExcel进行回归分析进行回归分析

86

Excel回归分析工具的输出结果包括 3 个部分： (1) 回归统计表 回归统计表包括以下几部分内容： ①Multiple R （复相关系数 R ）：是 R2 的平方根，又称为相关系数，用来衡量变量 x 和 y之间相关程度的大小。本例中 R 为 0.6313 ，表示二者之间的关系是正相关。

②R Square （复测定系数 R2 ）：用来说明自变量解释因变量变差的程度，以测定因变量 y 的拟合效果。

回归分析工具的输出解释回归分析工具的输出解释

87

③③Adjusted R SquareAdjusted R Square （调整复测定系数（调整复测定系数 RR22 ）：仅）：仅用于多元回归才有意义，它用于衡量加入独立变量用于多元回归才有意义，它用于衡量加入独立变量后模型的拟合程度。当有新的独立变量加入后，即后模型的拟合程度。当有新的独立变量加入后，即使这一变量同因变量之间不相关，未经修正的使这一变量同因变量之间不相关，未经修正的 RR22 也也要增大，修正的要增大，修正的 RR22 仅用于比较含有同一个因变量的仅用于比较含有同一个因变量的各种模型。各种模型。

④④ 标准误差：用来衡量拟合程度的大小，也用于计标准误差：用来衡量拟合程度的大小，也用于计算与回归相关的其他统计量，此值越小，说明拟合算与回归相关的其他统计量，此值越小，说明拟合程度越好。程度越好。

⑤⑤ 观测值：用于估计回归方程的数据的观测值个数。观测值：用于估计回归方程的数据的观测值个数。

回归分析工具的输出解释回归分析工具的输出解释

88

（ 2 ）方差分析表 方差分析表的主要作用是通过 F检验来判断回归模型的回归效果。表中“回归分析”行计算的是估计值同均值之差（ - ）的各项指标；“残差”行是用于计算每个样本观察值与估计值之差（ - ）的各项指标；“总计”行用于计算每个值同均值之差（ - ）的各项指标。第二列 df 是自由度，第三列 SS 是离差的平方和，第四列 MS 是均方差，它是离差平方和除以自由度，第五列是 F 统计量，第六列 Significance F 是在显著性水平下的 Fα的临界值

89

in

i cmi

ci

m ET

ET

Y

Y )(1

用用 ExcelExcel 进行回归分析进行回归分析

db xxy 21

)(ln()ln()ln( 21 xdxby

基本形式JENSEN模型（水分生产函

数）

两边同时取对数转化为线性形式

90

用用 ExcelExcel 进行回归分析进行回归分析

91

用用 ExcelExcel 进行回归分析进行回归分析

92

用用 ExcelExcel 进行回归分析进行回归分析

93

用用 ExcelExcel 进行回归分析进行回归分析

bxcey 基本形式

两边取对数

bxcy )ln()ln(

对因变量取对数

对截距反对数

94

用用 ExcelExcel 进行回归分析进行回归分析

95

用用 ExcelExcel 进行回归分析进行回归分析

96

用用 ExcelExcel 进行回归分析进行回归分析

)ln(xbay

基本形式

对自变量取对数

97

用用 ExcelExcel 进行回归分析进行回归分析

98

用用 ExcelExcel 进行回归分析进行回归分析产量与需水量之间的关系：抛物线

cbxaxY 2

把 x2,x分别看作变量按多元回归计算即可

99

用用 ExcelExcel 进行回归分析进行回归分析

100

用用 ExcelExcel 进行回归分析进行回归分析

101

加入趋势线可以进行简单的回归分析，但要获得更多的统计数据，可以使用数据分析工具，求简单相关系数、判定系数、用F 检定判定变量与自变量间是否有回归关系存在、用 t 检定判定各回归系数是否不为 0、计算回归系数的置信度、标准残差等。例 7、根据广告费与销售额统计数据使用分析工具进行回归分析。

月份 广告费(万)销售量(万)

1 245 30002 280 32003 175 18504 165 16505 150 15006 190 24007 240 28008 610 29609 190 240010 150 160011 140 150012 220 2350

回归分析信息说明

102

方差分析df SS MS F Si gni fi cance F

回归分析 1 1765442 1765442 6. 726 0. 026794856残差 10 2624983 262498. 3总计 11 4390425结 论:F 检定中的显著水准 Significance F = 0.026794856<a=0.05 说明:销售量与广告费存在回归关系.

方差分析 (F检定 ): 用回归分析检定 , 判定变量与自变量之间是否有显著的回归关系存在。如果显著水准 (Significance F)<a 值, 回归关系存在 ,否则不存在。(在这里判定系数 a=1- 置信度 ,在我们回归分析中置信度取 95%,

所以 a=1-95%=0.05)

103

t 检定Coeffi ci ents 标准误差 t Stat P-val ue Lower 95% Upper 95% 95. 0%下限 95. 0%上限

I ntercept 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

T检定 : 判断回归系数与常数项是否为 0

Intercept: 回归方程中的常数项信息广告费 ( 万 ): 自变量 X 的回归系数信息

结 论:

自变量广告费的显著水准 P-value = 0.026795<a=0.05

说明：广告费与销售量间存在直线关系，其系数为3.14338351，正相关。回归方程式为: Y=3.143384X+1545.832

t 检定结果中，常数项为1545.832，P-value = 0.00061826<a=0.05 说明:回归方程式的常数项不应为0，不可将其省略。

104

车龄 价格 ( 万 )1 56.02 48.53 42.04 37.65 32.56 28.77 22.28 18.59 15.0

10 12.5

例 8. 我们收集了某厂家同一车型中旧车的车龄及其售价数据，请使用数据分析工具，求车龄对售价的回归方程

SUMMARY OUTPUT

回归统计Mul ti pl e R 0. 993241R Square 0. 986527Adj usted R Square 0. 984843标准误差 1. 804792观测值 10

方差分析df SS MS F Si gni fi cance F

回归分析 1 1908. 007 1908. 007 585. 7682 9. 0595E-09残差 8 26. 05818 3. 257273总计 9 1934. 065

Coeffi ci ents 标准误差 t Stat P-val ue Lower 95%Upper 95% 95. 0%下限 95. 0%上限I ntercept 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

105

练习题 : 以下是银行客户的存款金额与贷款金额对照表 , 用数据分析工具求存款金额对贷款金额的回归方程 , 并预测当存款金额为 80千万时 ,贷款金额为多少 ?

存款金额( )千万

贷款金额( )千万

65 54102 8742 3458 4895 8173 6232 25124 107135 11745 37

回归统计Mul ti pl e R 0. 999956R Square 0. 999912Adj usted R Square 0. 999901标准误差 0. 314492观测值 10

方差分析df SS MS F Si gni fi cance F

回归分析 1 8970. 809 8970. 8 90701 1. 65436E-17残差 8 0. 791241 0. 0989总计 9 8971. 6

Coeffi ci ents 标准误差 t Stat P-val ue Lower 95% Upper 95% 95. 0%下限 95. 0%上限I ntercept -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

106

通过给图表加入趋势线只能进行简单的单一参数的回归分析，但在许多情况下需要使用多个自变量来预测一个变量的情况 ,这种回归分析叫复回归，复回归必须通过数据分析工具进行。

复回归

107

例 9、银行为了核发信用卡，收集了申请人的每月总收入、不动产、动产、每月房贷与抚养支出费用等数据，并以业务主管员的经验，主观地给予一个信用分数。为使评估信用分数能有一套公式，免得老是依赖主管评分，请使用复回归求其回归方程式。

每月总收( )入 万

不动产( )百万

动产( )百万

每月房( )贷 万

抚养费( )万

信用指数

6. 5 12. 0 3. 0 2. 0 2. 0 827. 2 8. 0 2. 0 0. 0 2. 0 863. 8 0. 0 1. 0 0. 0 1. 0 708. 5 15. 0 3. 5 2. 8 2. 0 904. 2 0. 0 0. 5 0. 0 1. 5 756. 3 6. 0 2. 0 1. 8 1. 5 803. 0 0. 0 0. 6 0. 0 0. 0 703. 2 3. 0 1. 0 0. 0 2. 0 65

图表标题

05000

0 100 200 300 400 500 600 700

y=a1x1+a2x2+a3x3+a4x4+a5x5+b

108

回归统计Mul ti pl e R 0. 990989406R Square 0. 982060003Adj usted R Square 0. 93721001标准误差 2. 179360967观测值 8

结 论:

R=0.999099,R2=0.98206,调整后的R=0.93721 说明:回归方程式可解释信用分数的差异程度相当高 .

方差分析df SS MS F Si gni fi cance F

回归分析 5 520 104 21. 9 0. 044248345残差 2 9. 499 4. 75总计 7 529. 5

结 论:F 检定中的显著水准 Significance F = 0.044248<a=0.05 说明:每月总收入、不动产、动产、每月房贷、抚养支出与信用分数整体间有明显回归关系存在。

Coeffi ci ents 标准误差 t Stat P-val ue Lower 95% Upper 95% 95. 0%下限 95. 0%上限I ntercept 57. 07610213 4. 9504323 11. 53 0. 00744 35. 7760961 78. 376108 35. 7760961 78. 3761082

( )每月总收入 万 5. 350912705 0. 995484 5. 375 0. 03291 1. 06768767 9. 6341377 1. 06768767 9. 63413774( )不动产 百万 0. 703920828 0. 9303825 0. 757 0. 52827 -3. 2991947 4. 7070363 -3. 2991947 4. 70703632

( )动产 百万 -4. 9618931 5. 4451066 -0. 91 0. 45835 -28. 390312 18. 466526 -28. 390312 18. 466526( )每月房贷 万 -0. 089892521 1. 7158095 -0. 05 0. 96298 -7. 4724301 7. 292645 -7. 4724301 7. 29264503

( )抚养费 万 -2. 499189095 1. 7049759 -1. 47 0. 28034 -9. 8351133 4. 8367351 -9. 8351133 4. 83673514

109

例 10. 我们收集了某厂家同一车型中旧车的车龄 ,行驶里程与售价数据，请使用数据分析工具，求车龄 ,行驶里程对售价的回归方程

车龄里程(万公里)

价格( )万

1 8. 5 61. 02 21 53. 53 35 46. 54 43 41. 55 50 37. 06 58 32. 07 65 27. 58 76 21. 09 80 18. 010 90 12. 0

SUMMARY OUTPUT

回归统计Mul ti pl e R 0. 9999R Square 0. 9999Adj usted R Square 0. 9998标准误差 0. 2055观测值 10

方差分析df SS MS F Si gni fi cance F

回归分析 2 2275. 7 1137. 85 26937. 35 2. 49919E-14残差 7 0. 2957 0. 04224总计 9 2276

Coeffi ci ents 标准误差 t Stat P-val ue Lower 95% Upper 95% 95. 0%下限 95. 0%上限I ntercept 65. 99587242 0. 186263 354. 315 3. 77E-16 65. 5554305 66. 436314 65. 555431 66. 4363143车龄 -1. 540712946 0. 207237 -7. 4345 0. 000145 -2. 0307505 -1. 050675 -2. 030751 -1. 0506754里程 -0. 427767355 0. 023844 -17. 94 4. 13E-07 -0. 4841495 -0. 371385 -0. 48415 -0. 3713852

y = -1.5407* 车龄 -0.4278*里程 + 65.9959

110

练习 影响出勤率的因素分析 ,1-5 代表影响程度，请使用复回归求其回归方程式。

111

非线性的复回归

年龄平方 年龄 每月所得225 15 6,000 400 20 10,000 625 25 15,000 900 30 26,000

1225 35 35,000 1600 40 42,000 2025 45 50,500 2500 50 40,500 3025 55 37,650 3600 60 30,500 4225 65 25,000 4900 70 15,800 5625 75 10,200 6400 80 8,000

例 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

y=a1x2+a2x+b

112

回归统计Multiple R 0.94023005R Square 0.88403254Adjusted R Square 0.86294755

标准误差 5383.55004

观测值 14

方差分析df 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

Coefficients 标准误差 t Stat P-value Lower 95% Upper 95%下限 95.0%上限 95.0%Intercept -42087.047 8250.414 -5.1012 0.000343 -60246.0938 -23928 -60246.09 -23928

年龄平方 -36.539835 3.990553 -9.15658 1.77E-06 -45.3229871 -27.75668 -45.32299 -27.75668

年龄 3463.74588 385.7649 8.978904 2.14E-06 2614.68262 4312.8091 2614.6826 4312.8091

y = -36.54x2 + 3463.7x - 42087

113

(1)直线回归函数 LINEST()使用最小平方法计算最适合于变量区域的回归直线公式 , 并传回该直线公式的数组。可以用于单回归和复回归 .语法 :LINEST(变量区域 ,自变量区域 ,常数项是否不为零 ,是否返回附加的统计值 )操作方法 :1.选定输出区域 2.输入公式 3.Ctrl+Shift+En

ter

例 12.以广告费与销售量的单一参数数据为例使用 LINEST 函数 ,求线性回归方程式。

3 、使用回归函数进行回归分析

系数(b) 常数标准误差(b) 标准误差判定系数(R2) 对y估计值的标准误差F统计值 F检定之自由度回归平方 残差平方

单回归 (y=bx+c) 输出结果9. 184629803 299. 8687911. 215127545 261. 6524490. 851039787 229. 16244657. 13202018 103000312. 402 525154. 264

y=9.1846x+299.8688

114

系数(bn) 系数(bn-1) … 系数(b1) 常数(c)

标准误差(bn) 标准误差(bn-1) … 标准误差(b1) 标准误差

判定系数(R2) 判定系数(R2) 判定系数(R2) 对y估计值的标准误差F统计值 F统计值 F统计值 F检定之自由度回归平方 回归平方 回归平方 残差平方

复回归 (y=bnx+bn-1x+…+b1x+c) 输出结果

例 13. 我们收集了某厂家同一车型中旧车的车龄 ,行驶里程与售价数据，请使用 LINEST函数，求车龄 ,行驶里程对售价的回归方程车龄 里程(万

公里)价格( )万

1 8. 5 61. 02 21 53. 53 35 46. 54 43 41. 55 50 37. 06 58 32. 07 65 27. 58 76 21. 09 80 18. 010 90 12. 0

-0. 42777 -1. 54071 65. 995870. 023844 0. 207237 0. 1862630. 99987 0. 205525 #N/ A26937. 35 7 #N/ A2275. 704 0. 295685 #N/ A

y = -1.5407* 车龄 -0.4278*里程 + 65.9959

115

(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 万

116

(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函数预测一组广告费的销售量。

117

(4) 指数回归函数• LOGEST：指数回归函数，计算最符合观测数据组的指数回归拟合曲线，并返回描述该曲线的数组。此曲线的方程为：y = b*mx or y = (b*(m1

x1)*(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

118