用EXCEL进行生产函数的多元线性回归分析

用EXCEL进行生产函数的多元线性回归分析

一、相关函数

EXCEL电子制表系统中函数的语法分为函数名和参数两部分，参数用圆括号括起来，之间以逗号隔开。参数可以为单元格区域、数组、函数、常数（逻辑型、数值型等）。

进行回归分析时，主要采用线性回归函数LINEST，辅以使用索引取值INDEX与四舍五入ROUND 函数。

1、线性回归函数LINEST。

使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。因为此函数返回数值数组，所以必须以数组公式的形式输入。

Excel中数组公式非常有用，它可建立产生多值或对一组值而不是单个值进行操作的公式。要输入数组公式，首先必须选择用来存放结果的单元格区域，在编辑栏输入公式，然后按ctrl＋Shift＋Enter 组合键锁定数组公式，Excel将在公式两边自动加上括号“{}”。不要自己键入花括号，否则，Excel认为输入的是一个正文标签。要编辑或清除数组公式．需选择数组区域并且激活编辑栏，公式两边的括号将消失，然后编辑或清除公式，最后按Ctrl＋shift＋Enter键。

【实现目标】在Excel中巧编辑数组公式。【操作方法】其具体操作如下：（1）选择用来存放结果的单元格区域，在编辑栏输入公式，然后按【Ctrl＋Shift＋Enter】键锁定数组公式，Excel将在公式两边自动加上括号“{}”。（2）编辑或清除数组公式时，先选择数组区域并且激活编辑栏，然后编辑或清除公式，最后按【Ctrl＋Shift＋Enter】键即可。

该函数的功能为：运算结果返回一线性回归方程的参数，即当已知一组混合成本为Y因变量序列值、N组Xi有关自变量因素的数量序列值时，函数返回回归方程的系数bi（i=1，2…n单位变动成本）和常数a（固定成本或费用）。

多元回归方程模型则为：y=b1x1＋b2X2……＋bnXn＋a

语法

LINEST(known_y's,known_x's,const,stats)

Known_y's 是关系表达式 y = mx + b 中已知的 y 值集合。

如果数组 known_y's 在单独一列中，则 known_x's 的每一列被视为一个独立的变量。

如果数组 known-y's 在单独一行中，则 known-x's 的每一行被视为一个独立的变量。

Known_x's 是关系表达式 y = mx + b 中已知的可选 x 值集合。

数组 known_x's 可以包含一组或多组变量。如果只用到一个变量，只要 known_y's 和 known_x's 维数相同，它们可以是任何形状的区域。如果用到多个变量，则 known_y's 必须为向量（即必须为一行或一列）。

如果省略 known_x's，则假设该数组为 {1,2,3,...}，其大小与 known_y's 相同。

Const 为一逻辑值，用于指定是否将常量 b 强制设为 0。

如果 const 为 TRUE 或省略，b 将按正常计算。

如果 const 为 FALSE，b 将被设为 0，并同时调整 m 值使 y = mx。

Stats 为一逻辑值，指定是否返回附加回归统计值。

如果 stats 为 TRUE，则 LINEST 函数返回附加回归统计值，这时返回的数组为：

{mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}。

如果 stats 为 FALSE 或省略，LINEST 函数只返回系数 m 和常量 b。

说明

可以使用斜率和 y 轴截距描述任何直线：

斜率 (m)：通常记为 m，如果需要计算斜率，则选取直线上的两点，(x1,y1) 和 (x2,y2)；斜率等于 (y2 - y1)/(x2 - x1)。

Y 轴截距 (b)：通常记为 b，直线的 y 轴的截距为直线通过 y 轴时与 y 轴交点的数值。

当只有一个自变量 x 时，可直接利用下面公式得到斜率和 y 轴截距值：

斜率：INDEX(LINEST(known_y's,known_x's),1)

Y 轴截距：INDEX(LINEST(known_y's,known_x's),2)

数据的离散程度决定了函数 LINEST 计算的精确度。数据越接近直线形，LINEST 模型就越精确。函数 LINEST 使用最小二乘法来判定最适合数据的模型。

直线和曲线函数 LINEST 和 LOGEST 可用来计算与给定数据拟合程度最高的直线或指数曲线。但需要判断两者中哪一个更适合数据。可以用函数 TREND(known_y's,known_x's) 来计算直线，或用函数 GROWTH(known_y's, known_x's) 来计算指数曲线。如果函数不带参数 new_x's，可在实际数据点上根据直线或曲线来预测 y 的数组值，然后可以将预测值与实际值进行比较。还可以用图表方式来直观地比较二者。

回归分析时，Microsoft Excel 计算每一点的 y 的估计值和实际值的平方差。这些平方差之和称为残差平方和。然后 Microsoft Excel 计算 y 的实际值和平均值的平方差之和。称为总平方和（回归平方和 + 残差平方和）。残差平方和与总平方和的比值越小，判定系数 r2 的值就越大，r2 是表示回归分析方程的结果反映变量间关系的程度的标志。

对于返回结果为数组的公式，必须以数组公式的形式输入。

当在参数中输入 known_x's 这样的数组常数时，可以用逗号分隔同一行中的数值，用分号分隔数值行。根据国别设置，分隔符有可能不同。

注意，如果 y 的回归分析预测值超出了用来计算方程的 y 值的范围，它们可能是无效的。

示例 1 斜率和 Y 轴截距

LINEST({1,9,5,7},{0,4,2,3})等于 {2,1}，斜率 = 2 且 y 轴截距 = 1

示例 2 简单线性回归

假设有一小商号，本财政年度的前六个月的销售额是 $3,100，$4,500，$4,400，$5,400，$7,500 和 $8,100 。假设这些值已分别输入到 B2:B7 单元格，可以用下面的简单线性回归模型来预测第九个月的销售额。

SUM(LINEST(B2:B7)*{9,1})等于SUM({1000,2000}*{9,1})等于 $11,000

通常，SUM({m,b}*{x,1}) 等于 mx + b，即给定 x 值的 y 的估计值。也可以使用函数 TREND。示例 3 多重线性回归

假设有开发商正在考虑购买商业区里的一组小型办公楼。

开发商可以根据下列变量，采用多重线性回归的方法来估算给定地区内的办公楼的价值。