利用EXCEL进行相关关系分析下

(二) 利用 EXCEL 建立直线回归方程 如果两个变量表现为近似的直线关系, 那就需
要配合一条直线, 代表两个变量之间的平均相互关
系即一般的数量关系。 也就是说利用一条直线将这
种不严格、不规则的数量关系规则化, 或者说将相关
关系使用函数关系的形式表现出来, 以便作为推算
和预测的依据。这条直线在数学上叫做回归直线, 简
要研究如何利用 EXCEL 的自动运算功能去求解 a,
b。 以某型机床使用年限和维修费的对应关系为
例:
为介绍使用 EXCEL 计算 a, b 值, 进而建立直
线回归方程, 我们首先建立求解直线回归方程计算
表:
表 2- 1 求解直线回归方程计算表
A
B
C
D
E
1 机床使用年限 X 维修费 Y x2
y2
XY
称回归直线方程。 其数学表达式为: YC= a+ bX。 上述公式中的符号从数学含义讲, X 是自变量
为已知数, YC 为因变量是推算的估计值, 又叫理论 值, a 是直线在纵轴上的截距, 是回归直线方程的常 数, b 是直线的斜率, a, b 习惯上都叫回归系数, 是两
个未知数。
建立此方程的关键是求 a, b 两个未知数, 依据 最小二乘法的要求提出了求解 a, b 两个未知数的标
10
6
760 36 577600 4560
11
6
900 36 810000 5400
12
8
840 64 705600 6720
13
9
1080 81 1166400 9720
14
60
8520 352 6428800 46560
用 EXCEL 求解的方法: 11 进入 EXCEL。 21 光标移至 A 1, 拖动鼠标将 14×5 表格变黑, 即定义了一个 14 行 5 列的表格。这时屏幕出现如表 2- 1 的表格。 31 单击[ 边框线 ], 选择[ 田 ]。 41 单击 [ 工具 ]—[ 选项 ]—将 [ 窗口选项 ] 中网 格线去掉, 这样, 屏幕上出现了一个 14 行 5 列的二
(1) : 可分别求出 a, b 的值:
a= 329125
b= 76115 得出: 反映某型机床使用年限与维修费用关系 的回归直线方程为:
YC= 329125+ 76115X
a = 329125 (元) 是直线在 Y 轴上的截距, 是机床维 修费的起点值。 可以理解为每年维修机床的最基本
费用。b= 76115 (元) 是说明机床使用每增加 1 年, 相 应平均增加的维修费用。 YC 就是根据这个方程有 规则的数量变化而推算出来的维修费的估计值。如:
41 将光标移至一个空的单元格。
51 单击按钮 [ FX ]—单击 [ 统计 ]—单击 [CO R 2 R EL ]—单击[ 下一步 ]—在A rray1 (数组 1) 中输入: B 1: B 12, 在 A rray2 (数组 2) 中输入: A 1: A 12—单击 [ 完成 ]
此时在刚定义的单元格中得到 r 值: 018913。 可以看到, 和前面的计算结果完全一样。
准联立方程:
2 Y = na + b2X
2X Y = a2X + b2X 2
根据以上方程求得:
a=
2X 22Y n2X 2 -
2X 2X Y (2X ) 2
·46·
b=
n2X Y n2X 2 -
2X 2Y (2X ) 2
这个方程式是运用微积分学不必去讨论, 下面主
☆知识讲座
维表。
行号用 1—14 表示, 列号用 A —E 表示。 51 在 A 1 输 入“X ”, B 1 输 入:“Y ”, C1 输 入: “x2”, D 1 输入:“y2”, E 1 输入:“XY”。 61 分别将表 1 中 X 数据输入至 A 2—A 13。 71 分别将表 1 中 Y 数据输入至 B 2—B 13。 81 将光标移至 C2, 输入[= ], 此时光标进入公 式栏, 接着输: A 23 A 2, 回车, 此时 C2 栏得到 A 2× A 2 的结果 4。 91 将光标移至 C2 左下角, 出现[ + ], 此时按住 光标左键不放, 一直拖至 C13, 此时 A 列各数的平 方出现在 C 列相应的单元格位置。 101 将光标移至 D 2, 输入 [ = ], 此时光标进入 公式栏, 接着输: B 23 B 2, 回车, 此时 D 2 栏出现 B 2 ×B 2 的结果 160000。 111 将光标移至 D 2 左下角, 出现 [ + ], 此时按 住光标左键不放, 一直拖至D 13, 此时B 列各数的平 方出现在 D 列相应的单元格位置。 121 将光标移至 E2, 输入 [ = ], 此时光标进入 公式栏, 接着输: A 23 B 2, 回车, 此时 D 2 栏出现 A 2 ×B 2 的结果 800。 131 将光标移至 E2 左下角, 出现 [ + ], 此时按 住光标左键不放, 一直拖至 E13, 此时各项结果出现 在 E 列相应的单元格位置。 141 将光标移至 A 14, 单击 [ 2 ] (自动求和功 能) , 再回车, 求和结果 60 出现在A 14。 151 将 光 标 移 至 B 14, 单 击 [ 2 ] ( 自 动 求 和 功 能) , 再回车, 求和结果 8520 出现在B 14。 161 将 光 标 移 至 C14, 单 击 [ 2 ] (自 动 求 和 功 能) , 再回车, 求和结果 352 出现在 C14。 171 将 光 标 移 至 D 14, 单 击 [ 2 ] (自 动 求 和 功 能) , 再回车, 求和结果 6428800 出现在D 14。 181 将 光 标 移 至 E14, 单 击 [ 2 ] (自 动 求 和 功 能) , 再回车, 求和结果 46560 出现在 E14。 此时 2X, 2 Y, 2 x2, 2XY 已分别求出, 代入公式
2
2
400
4 160000 800
3
2
540
4 291600 1080
4
3
520
9 270400 1560
5
4
640 46 409600 2560
6
4
740 46 547600 2960
7
5
600 25 360000 3000
8
5
800 25 640000 4000
9
6
700 36 490000 4200
机床使用 2 年, 维修费估计值为: YC= 329125+ 76115×2= 481155 (元) 机床使用 4 年, 维修费估计值为: YC= 329125+ 76115×4= 633185 (元) 以上, 我们介绍了用 EXCEL 计算直线回归方 程截距和斜率的方法, 从而达到建立直线回归方程 的目的。 通过实际操作, 一方面, 我们熟悉了统计学 中建立直线回归方程的方法, 另一方面, 也体会到了 利用 EXCEL 来计算回归方程参数给我们带来的便 利。 其实, EXCEL 的设计者还为我们准备了更为方 便快捷的函数 IN T ERCEPT (其中文含义是: 截距) 和 SLO PE (其中文含义为: 斜率)。 使用这两个函数 后, 只需给出 X 和 Y 的值, 无需求出 2X, 2 Y, 2 x2, 2XY 的值, 即可直接得出 a 和 b 的值。 这样只通过 两步操作即可建立直线回归方程 YC= a+ bX。 我们仍然利用上面的数据, 假设没有进行过任 何 EXCEL 操作, 开始计算 a 值, 具体操作如下: 11 进入 EXCEL。 21 建立 12×2 表格 (为简化操作过程过程, 我 们不必建立表的栏目行)。 31 在 A 1 至 A 12 输入 X 值, 在 B 1 至 B 12 输入 Y 值。 41 将光标移至一个空的单元格。 51 单 击 按 钮 [ FX ]—单 击 [ 统 计 ]—单 击 [ IN 2 T ERCEPT ]—单击[ 下一步 ]—在 Know n y’s 中输 入: B 1: B 12, 在 Know n x’s 中输入: A 1: A 12—单击 [ 完成 ] 此时在刚定义的单元格中得到 a 值: 329125。 可以看到, 和前面的计算结果完全一样。 用同样的方法, 我们可以得到 b 值。 在上面计算相关系数及建立直线回归方程中, 我们都采取了分两步讲解的方法, 即先介绍分步操 作, 这样可以学习到统计学中有关知识的具体求解 方法, 掌握具体运算步骤, 又能学习到 EXCEL 的具 体操作, 然后再介绍用现成函数一步求解的方法, 从 而为今后进行快捷运算打下基础。
☆知识讲座
利用 EXCEL 进行相关关系分析 (下)
●胡大新
我们仍然利用上面例子中的数据, 假设我们还
没有建立相关系数计算表:
11 进入 EXCEL。 21 建立 12×2 表格。
31 在 A 1 至 A 12 输入机床使用年限值 (X 值) , 在B 1 至 B 12 输入年维修费值 (Y 值) (为简化操作 过程, 我们不必建立表的栏目行)。
(责任编辑 马士龙)
·47·