计量经济学问题用Excel解决方法

1、计量经济学问题用Excel解决方法温永益【摘要】计量经济学中使用最广泛的计算工具是古典线性回归模型，采用最小二乘法来求解样本回归函数，根据样本回归函数进行预测。本文利用Microsoft Excel软件介绍最小二乘法的三种求解方法，以帮助计量经济学这一门课程的读者更好掌握其中的计算方法。【关键词】计量经济学Excel 最小二乘法计量经济学是依据经济理论，使用数学和统计推断等工具，用观测数据对经济和商务活动进行实证研究，测度和检验经济变量间的经验关系，从而给出经济理论的经验内容，在经济理论的抽象世界和人类活动的具体世界之间搭建桥梁。用于计量经济学的应用软件有很多如：SPSS、SAS、Eviews

2、等，对于计量经济学的初学者，学习该门课程的难度是很大的，因为内容涉及太多的数学知识，计算量很大。在没有掌握专用的计量经济学软件之前，能否用计算机的常用办公软件Excel电子表格来解决计量经济学的有关问题呢？本文试图通过一个例子来说明计量经济学的问题是可以通过电子表格来解决的。问题一：在美国对每周家庭收入（美元）与每周消费支出（美元）的抽样调查数据如下：每周收入（X）80100120140160180200220240260每周消费支出（Y）70659095110115120140155150（1） 你认为Y与X之间是怎样的关系？（2） 利用最小二乘法求样本回归函数，并预测当X280（美元）时，

3、Y的值。最小二乘法的原理是：在给定X和Y的一组观测值 (X1 , Y1), (X2 , Y2) , ., (Xn , Yn)的情况下, 求出 Yt = a + bXt + ut 中a和 b 的估计值和 , 使得拟合的直线为最佳。拟合的直线称为拟合的回归线。 （t=1,2,n），et代表观测点对于回归线的误差，称为拟合或预测的残差。 我们的目标是使拟合出来的直线在某种意义上是最佳的，直观地看，也就是要求估计直线尽可能地靠近各观测点，这意味着应使残差总体上尽可能地小。要做到这一点，就必须用某种方法将每个点相应的残差加在一起，使其达到最小。理想的测度是残差平方和，即 ：达到最小值。运用微积分知识可求

4、得与的计算公式为：其中：为样本均值，下面用Excel电子表格来解决问题一的两个小题：（1）判断Y与X之间的关系用散点图来描述。操作步骤：1输入问题一中表格的数据，也可以通过复制，然后粘贴到Excel的工作表中，如下图所示：2在“插入”菜单中选择“图表”，然后选择“XY散点图”，按操作向导即可完成上面散点图的绘制。从上面的散点图中可以看出：Y与X之间的关系近似于线性关系，因此可以用最小二乘法来求样本回归函数。（2）求样本回归函数的方法有如下三种：方法一：直接用公式进行计算：计算步骤如下：1.首先求出与，利用平均值函数average(x1,x2,xn)，170，111；2.计算 ，利用Excel内

5、置求和函数SUMPRODUCT(x1xn,y1yn）， =SUMPRODUCT(B2:K2-170,B3:K3-111)/SUMPRODUCT(B2:K2-170,B2:K2-170)=0.5090913.计算111*17024.45455因此得到样本回归函数为：24.454550.509091X当X280（美元）时，Y的估计值是：167（美元）方法二：利用Excel内置线性回归函数LINEST计算。函数LINEST（known_ys ,known_xs , const , stats）的参数说明如下：known_ys-关系表达式中已知的y值的集合；known_xs-关系表达式中已知的x值的集

6、合；const-逻辑值，用于指定是否将常量b强制设为0。如果const为TRUE（或1）或省略，b将按正常计算。如果const为FALSE（或0），b将被设为0，并同时调整m值使y=mx。stats-逻辑值，指定是否返回附加回归统计值。如果stats为TRUE（或1），则函数返回附加回归统计值。这时返回的数组为：如果stats为FALSE（或0）或省略，函数只返回系数预测模型的待估参数。附加回归统计值返回的顺序如下表所示：行号列号12n-1nn+11b234Fdf5上表中的参数说明如下：参数说明（i=1,2,n） 系数（i=1,2,n）的标准误差值常数项b的标准误差值（当const为FALSE

7、时，N/A判定系数即回归平方和（ESS）与总离差平方和（TSS）之比。范围在0到1之间，如果越接近1，表示回归直线与样本观测值拟合得越好。如果为0，则回归方程不能用来预测Y值Y估计值的标准误差FF统计值或F观察值。使用F统计值可以判断因变量和自变量之间是否偶尔发生过观察到的关系df自由度。用于在统计表上查找F临界值。所查得的值和LINEST函数返回的F统计值比较可用来判断模型的置信度。回归平方和（记为ESS）残差平方和（记为RSS），总离差平方和记为TSS，他们之间的关系是：TSSESSRSS使用LINEST函数计算步骤如下：步骤一：在工作表中选取5行2列单元格。步骤二：插入函数LINEST，

8、输入相关参数值：在known_ys中：输入B3：K3，在known_xs中输入：B2：K2，在const中输入TRUE（或1），在stats中输入TRUE（或1）。0.50909124.454550.0357436.4138170.9620626.493003202.867988552.727337.2727步骤三：同时按下键盘上的ctrl与shift两键，单击输入参数对话框中的“确定”按钮，只有这样才能以数组的方式输出结果。计算结果如右图所示：对照前面介绍的返回参数值顺序可知：0.509091，24.454550.035743，6.4138170.962062，6.493003，F202.8

9、679，df=8,ESS=8552.727,RSS=337.2727。可以验证：ESS/TSS=ESS/(ESS+RSS)= 0.962062。因此得到样本回归函数为：24.454550.509091X。方法三：利用规划求解方法。规划求解是Excel的一种非常有用的工具，不仅可以解决运筹学中的优化问题，还可以用来解决线性与非线性方程组。在安装Microsoft Excel软件时，如果采用“典型安装”，则规划求解工具没有被安装，只有在选择“完全/定制安装”时才可选择安装这个模块。在安装完成进入Excel后，在“工具”菜单中单击“加载宏”命令，在系统弹出的“加载宏”对话框中选定“规划求解”复选框，

10、单击“确定”按钮，系统就安装上了“规划求解”工具，就可以使用它了。问题一的（2）小题用规划求解的步骤如下：步骤一：设计计算表格结构如下图：步骤二：在目标单元格D5中输入残差平方和的计算公式：。具体方法是：插入乘积求和函数SUMPRODUCT，出现该函数参数的对话框，在Array1与Array2中输入：B3:K3A5B5*B2:K2。其中A5，B5分别代表与的取值。步骤三：单击菜单“工具”中的“规划求解”，在出现的对话框“规划求解参数”中，设置目标单元格为：$D$5，等于“最小值”，可以变单元格为：$A$5:$B$5，单击“求解”按钮，即可得到如下结果：上述三种计算方法，各有优缺点，可以根据不同的情况，灵活采用其中一种计算方法，计算结果是相同的。还可以进一步推广到多元线性回归模型中。愿所有计量经济学的读者都能应用Excel电