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

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

————————————————————————————————作者：————————————————————————————————日期：

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

一、相关函数

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

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

1、线性回归函数LINEST。

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

该函数的功能为：运算结果返回一线性回归方程的参数，即当已知一组混合成本为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。附加回归统计值如下：

统计值说明

se1,se2,...,sen 系数 m1,m2,...,mn 的标准误差值。

seb 常量 b 的标准误差值（当 const 为 FALSE时，seb = #N/A）

r2 判定系数。Y 的估计值与实际值之比，范围在 0 到 1 之间。如果为 1，则样本有很好的相关性，Y 的估计值与实际值之间没有差别。如果判定系数为 0，则回归公式不能用来预测 Y 值。有关计算 r2 的方法的详细信息，请参阅本主题后面的“说明”。

sey Y 估计值的标准误差。

F F 统计或 F 观察值。使用 F 统计可以判断因变量和自变量之间是否偶尔发生过可观察到的关系。

df 自由度。用于在统计表上查找 F 临界值。所查得的值和 LINEST 函数返回的 F 统计值的比值可用来判断模型的置信度。有关如何计算 df，请参阅在此主题中后面的“说明”。示例 4 说明了 F 和 df 的使用。

ssreg 回归平方和。

ssresid 残差平方和。

二、示例

计算柯布-道格拉斯生产函数（Cobb-Douglas ）：

lnQ = lnA + a lnL + b lnK

式中Q为产出，L和K分别表示劳动和资本投入量，A表示平均生产技术水平，a和b分别是Q相对于L和K的弹性。使用下表的统计数据，线性回归参数A, a, b。

A B C D

1 年份产出投入万人投入亿元

2 1984 733.69 1531.2 351.3

3 1985 985.1 1701.

4 376.4

4 1986 1330.8 1800.6 459.3

5 1987 1603.61 1852.5 501.6

6 1988 1959.42 1899.4 565.3

7 1989 2169.48 1773.4 675.28

8 1990 1947.58 1716.7 717.5

9 1991 2284.78 1783.3 792.3

10 1992 3298.7 1961.2 792.63

11 1993 5498.35 2156.7 865.52

12 1994 7684.36 2448.8 906.48

13 1995 9505 2511.9 1152.34

14 1996 11579.15 2992.3 1610.86

15 1997 12462.57 2804.6 1773.38

16 1998 13740.69 2778.9 1875.88

17 1999 15151.46 2765.7 2066.19

18 2000 16780.96 2740.9 2255.09

19 2001 20009.8 2872.8 2690.76

20 回归值：

21 0.85776 2.437547 -16.212

22 0.17032 0.4830369 2.66302

23 0.97973 0.1648699 #N/A

24 362.488 15 #N/A

25 19.7064 0.4077314 #N/A

1、定义名称：将A2:A19定义为Y，将B2:D19定义为X

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返回的数组的行列号。