用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 多重线性回归假设有开发商正在考虑购买商业区里的一组小型办公楼。

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

本示例假设在自变量（ y 是办公楼的价值。

y = 27.64*x1 + 12,530*x2 + 2,553*x3+ 234.24*x4 + 52,318现在，开发商用下面公式可得到办公楼的评估价值，其中面积为 2,500 平方英尺、3 个办公室、2 个入口，已使用 25 年：y = 27.64*2500 + 12530*3 + 2553*2 - 234.24*25 + 52318 = $158,261 示例 4 使用 F 和 R2 统计在上例中，判定系数（或 r2）为 0.99675（函数 LINEST 的输出单元格 A16 中的值），表明在自变量与销售价格之间存在很大的相关性。

可以通过 F 统计来确定具有如此高的 r2 值的结果偶然发生的可能性。

假设事实上在变量间不存在相关性，但选用 11 个办公楼作为小样本进行统计分析却导致很强的相关性。

“Alpha”表示得出这样的相关性结论错误的概率。

如果 F 观测统计值大于 F 临界值，表明变量间存在相关性。

F 临界值可以从许多的统计类书籍的 F 统计临界值表中查到。

如果一项单尾实验的 Alpha 值为 0.05，自由度（在大多数表中缩写成 v1 和 v2）v1 = k = 4，v2 = N - (k + 1) = 11 - (4 + 1) = 6，其中 k 是回归分析中的变量数，n 是数据点的个数。

F 临界值为 4.53。

在单元格 A17 中的 F 观测值为 459.753674，远大于 F 临界值 4.53。

因此回归方程可用来对该地区的办公楼进行评估。

示例 5 计算 T 统计另一个假设检验可以检验示例中的每个斜率系数是否可以用来估算示例 3 中的办公楼的评估价值。

例如，如果要检验年数系数的统计显著水平，用 13.268（单元格 A15 里的年数系数的估算标准误差）去除 -234.24（年数斜率系数）。

下面是 T 观察值：t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7 如果查阅统计手册里的表格，将会发现：单尾、自由度为 6、Alpha = 0.05 的 t 临界值为 1.94。

既然 t 的绝对值为 17.7 大于 1.94，则年数对于估算办公楼的评估价值来说是一个显著变量。

用同值。

二、示例计算柯布-道格拉斯生产函数（Cobb-Douglas ）：lnQ = lnA + a lnL + b lnK式中Q为产出，L和K分别表示劳动和资本投入量，A表示平均生产技术水平，a和b分别是Q相对于L和K1、定义名称：将2、在A21中输入公式：=LINEST(LN(Y),LN(X),TRUE,TRUE)3、将返回值以数组形式显示：选中A21:C25，按F2，再按Ctrl + Shift + Enter回归计算得 lnY = -16.212 + 2.4375 lnL + 0.8578 lnK三、返回值的其它操作如果只想要返回值中的指定项，则可以index函数。

１、索引取值函数INDEX.语法格式：INDEX（单元格区域或数组常量，行序号，列序号）；功能：使用索引从单元格区域或数组中选取值。

可用该函数在LINEST函数返回系数序列数组表中根据所需数据所处的行列位置定位选取。

２、四舍五入函数ROUND.语法格式：ROUND（数字，小数位数）；功能：将数字四舍五入到指定的小数位数。

由于LINEST函数的返回值为6位小数，用此函数指定保留的小数位数。

在需要的位置输入：=ROUND（INDEX（LINEST（YX，TRUE，TRUE），1，4），2）。

index的后两个参数是指向linest返回的数组的行列号。