Excel中的有关预测函数及其应用

Excel中的有关预测函数及其应用

Excel提供了关于估计线性模型和指数模型参数的几个预测函数。线性模型和指数模型的数学表达式如下：

线性模型：

y = mx + b或y = m1x1 + m2x2+ …+ b

指数模型：

或

式中，y为因变量；x是自变量；m、m1、...、m n-1、m n、b分别为预测模型的待估计参数。

Excel提供的预测函数主要有LINEST函数、LOGEST函数、TREND函数、GROWTH函数、FORECAST 函数、SLOPE函数和INTERCEPT函数，它们所使用的参数都基本相同，现列于表4-1中，以供参考。

参数含义

known_y's 因变量y的观测值集合

known_x's 自变量x的观测值集合。它可以是一个变量（即一元模型）或多个变量（即

多元模型）的集合。

如果只用到一个变量，只要 known-y's 和 known-x's 维数相同，它们可以是任何形状的选定区域。如果用到不只一个变量，known_y's 必须是向量（也就是说，必须是一行或一列的区域）。如果省略 known_x's，则假设该数组是 {1,2,3...}，其大小与 known_y's 相同

const 逻辑值，指明是否强制使常数b为0（线性模型）或为1（指数模型）。如果const 为 TRUE或省略，b将被正常计算。如果const为FALSE，b将被设为0（线性模型）或设为1（指数模型）

stats 逻辑值，指明是否返回附加回归统计值。如果 stats 为 TRUE，则函数返回附加回归统计值，这时返回的数组为 {m n，m n-1，...，m1，b；se n，se n-1，...，se1，se b，r2，se y；F，df；ss reg，ss resid}。如果 stats为FALSE或省略，函数只返回系数预测模型的待估计参数m、m n、m n-1、...、m1和b。

附加回归统计值返回的顺序见表4-2。

表4-2中的各参数说明见表4-3。

如果要得到附加回归统计值数组中的值，需用INDEX函数将其取出

1 2 3 4 5 6

1 m n m n-1…m2m1 b

2 se n se n-1…se2se1se b

4.3.1 LINEST函数

LINEST函数的功能是使用最小二乘法计算对已知数据进行最佳线性拟合的直线方程，并返回描述此线性模型的数组。因为此函数返回数值为数组，故必须以数组公式的形式输入。

函数公式为

= LINEST（known_y's，known_x's，const，stats）

下面举例说明LINEST函数的应用。

1．一元线性回归分析

LINEST函数可用于一元线性回归分析，也可以用于多元线性回归分析，以及时间数列的自回归分析。

当只有一个自变量 x （即一元线性回归分析）时，可直接利用下面的公式得到斜率和 y 轴的截距值以及相关系数：

斜率：INDEX（LINEST（known_y's，known_x's）,1,1）；或INDEX（LINEST

（known_y's,known_x's）,1）

截距：INDEX（LINEST（known_y's，known_x's）,1,2）；或INDEX（LINEST

（known_y's,known_x's）,2）

相关系数：INDEX（LINEST（known_y's,known_x's,true,true）,3,1）

【例4-1】某企业1～9月份的总成本与人工小时及机器工时的数据如图4-1所示。假设总成本与人工小时之间存在着线性关系，则在单元格B13中插入公式

“=INDEX(LINEST(B2:B10,D2:D10),2)”，在单元格B14插入公式

“=INDEX(LINEST(B2:B10,D2:D10),1)”，在单元格B15插入公式

“=INDEX(LINEST(B2:B10,D2:D10,TRUE,TRUE),3,1)”，即得总成本与人工小时的一元线性回归分析方程为：Y=562.72756+4.41444X

1

，相关系数为R2=0.99801，如图4-1所示。

图4-1 一元线性回归分析

2．多元线性回归分析

仍以例4-1的数据为例，首先选取单元格区域A17:D21，再以数组公式方式输入公式

“=LINEST(B2:B10,C2:D10,TRUE,TRUE)”，即得该二元线性回归的有关参数如图4-2所示，从而得到：

图4-2 二元线性回归分析

回归方程：Y = 471.4366+3.6165X

1+3.4323X

2

相关系数：R2 =0.9990

标准差：Se

y

=11.7792。

4.3.2 LOGEST函数

LOGEST函数的功能是在回归分析中，计算最符合观测数据组的指数回归拟合曲线，并返回描述该指数模型的数组。由于这个函数返回一个数组，必须以数组公式输入。

LOGEST函数的公式为

= LOGEST(known_y's,known_x's,const,stats)

【例4-2】某企业12个月某产品的生产量（X）与生产成本（Y）的有关资料如图4-3所示，假设它们之间有如下关系：。选取单元格区域B15:C18，输入公式

“=LOGEST(C2:C13,B2:B13,TRUE,TRUE)”（数组公式输入），即得回归参数，如图4-3所示，参数m=0.8887，参数b=1891.7729，生产成本与生产量的回归曲线为：Y=1791.7729×0.8887X，相关系数R2=0.95885。

图4-3 指数回归

回归方程的系数及相关系数也可以利用下面的公式直接计算

参数m：INDEX(LOGEST(C2:C13,B2:B13),1)=0.8887

参数b：INDEX(LOGEST(C2:C13,B2:B13),1,2)=1791.7729

相关系数R2：=INDEX(LOGEST(C2:C13,B2:B13,TRUE,TRUE),3,1)= 0.95885

4.3.3 TREND函数

TREND函数的功能是返回一条线性回归拟合线的一组纵坐标值（y 值），即找到适合给定的数组 known_y's 和 known_x's 的直线（用最小二乘法），并返回指定数组 new_x's 值在直线上对应的 y 值。

TREND函数的公式为

= TREND(known_y's,known_x's,new_x's,const)