《最新Excel应用实例之八——预测分析.doc》由会员分享，可在线阅读，更多相关《最新Excel应用实例之八——预测分析.doc（83页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Four short words sum up what has lifted most successful individuals above the crowd: a little bit more.-author-dateExcel应用实例之八预测分析第八章 预测分析5.18 预测分析本节提要本节首先讨论了两种时间序列预测法：移动平均法和指数平滑法。然后介绍了回归分析法，其中包括线性回归法和可以转化为线性处理的非线性回归。 预测是指从已知事件测定未知事件。具体地讲，预测就是以准确的调查统计资料和统计数据为依据，从研究现象的历史、现状和规律性出发，运用科学的方法，对研究现象的未来发展前景
2、的测定。预测理论作为通用的方法论，既可以应用于研究自然现象，又可应用于研究社会现象。将预测理论、方法和个别领域现象发展的实际相结合，就产生了预测的各个分支。如社会预测、人口预测、经济预测、政治预测、科技预测、军事预测、气象预测等等。本章主要以经济预测为例来讨论预测技术中最基本、最常用的预测方法及其在Excel 2000中的具体实现。 经济预测的内容十分丰富，常见如某种商品或产品的社会需求预测、市场占有率预测、市场供求预测、库存预测以及企业利润预测、投资效益预测、价格变动预测等等。由于经济系统的复杂性、随机性、动态性、开放性、模糊性以及经济信息的不完善性，使得没有哪种单纯的预测方法能满足一切预测
3、决策工作的需要，所以现在已发展了许多预测方法，不同的预测方法适用于不同的情况。在实际应用中应具体问题具体分析，针对具体问题选择最有效的预测方法来进行预测分析。本节只讨论应用最为广泛的两种时间序列预测法和回归分析预测法。 8.1 时间序列预测法 时间序列是指把历史统计资料按时间顺序排列起来得到的一组数据序列。例如，按月份排列的某种商品的销售量；工农业总产值按年度顺序排列起来的数据序列等等都是时间序列。时间序列一般用 表示，t为时间。 时间序列预测法是将预测目标的历史数据按时间的顺序排列成为时间序列，然后分析它随时间的变化趋势，外推预测目标的未来值。也就是说，时间序列预测法将影响预测目标的一切因素
4、都由“时间”综合起来描述。因此，时间序列预测法主要用于分析影响事物的主要因素比较困难或相关变量资料难以得到的情况。 8.1.1 移动平均法移动平均法是一种简单平滑预测技术，它的基本思想是：根据时间序列资料、逐项推移，依次计算包含一定项数的序时平均值，以反映长期趋势的方法。因此，当时间序列的数值由于受周期变动和随机波动的影响，起伏较大，不易显示出事件的发展趋势时，使用移动平均法可以消除这些因素的影响，显示出事件的发展方向与趋势（即趋势线），然后依趋势线分析预测序列的长期趋势。 1. 移动平均法的基本理论简单移动平均法设有一时间序列 ，则按数据点的顺序逐点推移求出N个数的平均数，即可得到一次移动平
5、均数： 式中 为第t周期的一次移动平均数； 为第t周期的观测值；N为移动平均的项数，即求每一移动平均数使用的观察值的个数。 这个公式表明当t向前移动一个时期，就增加一个新近数据，去掉一个远期数据，得到一个新的平均数。由于它不断地“吐故纳新”，逐期向前移动，所以称为移动平均法。 由于移动平均可以平滑数据，消除周期变动和不规则变动的影响，使得长期趋势显示出来，因而可以用于预测。其预测公式为： 即以第t周期的一次移动平均数作为第t+1周期的预测值。 趋势移动平均法当时间序列没有明显的趋势变动时，使用一次移动平均就能够准确地反映实际情况，直接用第t周期的一次移动平均数就可预测第t+1周期之值。但当时间
6、序列出现线性变动趋势时，用一次移动平均数来预测就会出现滞后偏差。因此，需要进行修正，修正的方法是在一次移动平均的基础上再做二次移动平均，利用移动平均滞后偏差的规律找出曲线的发展方向和发展趋势，然后才建立直线趋势的预测模型。故称为趋势移动平均法。 设一次移动平均数为 ，则二次移动平均数 的计算公式为： 再设时间序列 从某时期开始具有直线趋势，且认为未来时期亦按此直线趋势变化，则可设此直线趋势预测模型为： 式中t为当前时期数；T为由当前时期数t到预测期的时期数，即t以后模型外推的时间； 为第t+T期的预测值； 为截距； 为斜率。 ， 又称为平滑系数。 根据移动平均值可得截距 和斜率 的计算公式为：
7、 在实际应用移动平均法时，移动平均项数N的选择十分关键，它取决于预测目标和实际数据的变化规律。 2. 应用举例已知某商场19781998年的年销售额如下表所示，试预测1999年该商场的年销售额。 年份销售额年份销售额19783219897619794119907319804819917919815319928419825119938619835819948719845719959219856419969519866919971011987671998107198869下面使用移动平均工具进行预测，具体操作步骤如下： 选择工具菜单中的数据分析命令，此时弹出数据分析对话框。 在分析工具列表框中，选
8、择移动平均工具。这时将弹出移动平均对话框，如图5.181所示。 图5.181 在输入框中指定输入参数。在输入区域框中指定统计数据所在区域B1:B22；因指定的输入区域包含标志行，所以选中标志位于第一行复选框；在间隔框内键入移动平均的项数5（根据数据的变化规律，本例选取移动平均项数N=5）。 在输出选项框内指定输出选项。可以选择输出到当前工作表的某个单元格区域、新工作表或是新工作簿。本例选定输出区域，并键入输出区域左上角单元格地址C2；选中图表输出复选框。若需要输出实际值与一次移动平均值之差，还可以选中标准误差复选框。 单击确定按钮。这时，Excel给出一次移动平均的计算结果及实际值与一次移动平
9、均值的曲线图，如图5.182所示。 图5.182从图5.182可以看出，该商场的年销售额具有明显的线性增长趋势。因此要进行预测，还必须先作二次移动平均，再建立直线趋势的预测模型。而利用Excel 2000提供的移动平均工具只能作一次移动平均，所以在一次移动平均的基础上再进行移动平均即可。 二次移动平均的方法同上，求出的二次移动平均值及实际值与二次移动平均值的拟合曲线，如图5.183所示。 图5.1834再利用前面所讲的截距 和斜率 计算公式可得： 于是可得t=21时的直线趋势预测模型为： 预测1999年该商场的年销售额为： 8.1.2 指数平滑法移动平均法的预测值实质上是以前观测值的加权和，且
10、对不同时期的数据给予相同的加权。这往往不符合实际情况。指数平滑法则对移动平均法进行了改进和发展，其应用较为广泛。 1. 指数平滑法的基本理论根据平滑次数不同，指数平滑法分为：一次指数平滑法、二次指数平滑法和三次指数平滑法等。但它们的基本思想都是：预测值是以前观测值的加权和，且对不同的数据给予不同的权，新数据给较大的权，旧数据给较小的权。 一次指数平滑法 设时间序列为 ，则一次指数平滑公式为： 式中 为第 t周期的一次指数平滑值； 为加权系数，0 1。 为了弄清指数平滑的实质，将上述公式依次展开，可得： 由于0 1，当 时， 0，于是上述公式变为： 由此可见 实际上是 的加权平均。加权系数分别为
11、 ， ，是按几何级数衰减的，愈近的数据，权数愈大，愈远的数据，权数愈小，且权数之和等于1，即 。因为加权系数符合指数规律，且又具有平滑数据的功能，所以称为指数平滑。 用上述平滑值进行预测，就是一次指数平滑法。其预测模型为： 即以第t周期的一次指数平滑值作为第t+1期的预测值。 二次指数平滑法当时间序列没有明显的趋势变动时，使用第t周期一次指数平滑就能直接预测第t+1期之值。但当时间序列的变动出现直线趋势时，用一次指数平滑法来预测仍存在着明显的滞后偏差。因此，也需要进行修正。修正的方法也是在一次指数平滑的基础上再作二次指数平滑，利用滞后偏差的规律找出曲线的发展方向和发展趋势，然后建立直线趋势预测
12、模型。故称为二次指数平滑法。 设一次指数平滑为 ，则二次指数平滑 的计算公式为： 若时间序列 从某时期开始具有直线趋势，且认为未来时期亦按此直线趋势变化，则与趋势移动平均类似，可用如下的直线趋势模型来预测。 式中t为当前时期数；T为由当前时期数t到预测期的时期数； 为第t+T期的预测值； 为截距， 为斜率，其计算公式为： 三次指数平滑法 若时间序列的变动呈现出二次曲线趋势，则需要用三次指数平滑法。三次指数平滑是在二次指数平滑的基础上再进行一次平滑，其计算公式为： 三次指数平滑法的预测模型为： 其中： 加权系数的选择在指数平滑法中，预测成功的关键是 的选择。 的大小规定了在新预测值中新数据和原预
13、测值所占的比例。 值愈大，新数据所占的比重就愈大，原预测值所占比重就愈小，反之亦然。 若把一次指数平滑法的预测公式改写为： 则从上式可以看出，新预测值是根据预测误差对原预测值进行修正得到的。 的大小表明了修正的幅度。 值愈大，修正的幅度愈大， 值愈小，修正的幅度愈小。因此， 值既代表了预测模型对时间序列数据变化的反应速度，又体现了预测模型修匀误差的能力。 在实际应用中， 值是根据时间序列的变化特性来选取的。若时间序列的波动不大，比较平稳，则 应取小一些，如0.10.3；若时间序列具有迅速且明显的变动倾向，则 应取大一些，如0.60.9。实质上， 是一个经验数据，通过多个 值进行试算比较而定，哪
14、个 值引起的预测误差小，就采用哪个。 2. 应用举例已知某厂19781998年的钢产量如下表所示，试预测1999年该厂的钢产量。 年份钢产量年份钢产量1978676198920311979825199022341980774199125661981716199228201982940199330061983115919943093198413841995327719851524199635141986166819973770198716881998410719881958下面利用指数平滑工具进行预测，具体步骤如下： 选择工具菜单中的数据分析命令，此时弹出数据分析对话框。 在分析工具列表框中，选
15、择指数平滑工具。这时将出现指数平滑对话框，如图5.184所示。 图5.184在输入框中指定输入参数。在输入区域指定数据所在的单元格区域B1:B22；因指定的输入区域包含标志行，所以选中标志复选框；在阻尼系数指定加权系数0.3。 在输出选项框中指定输出选项。本例选择输出区域，并指定输出到当前工作表以C2为左上角的单元格区域；选中图表输出复选框。单击确定按钮。这时，Excel给出一次指数平滑值，如图5.185所示。 图5.185 从图5.185可以看出，钢产量具有明显的线性增长趋势。因此需使用二次指数平滑法，即在一次指数平滑的基础上再进行指数平滑。所得结果如图5.186所示。 图5.186 利用前
16、面的截距 和斜率 计算公式可得： 于是，可得钢产量的直线趋势预测模型为： 预测1999年的钢产量为： 8.2 回归分析预测法 在实际经济问题中，某一经济行为常受多因素的影响和制约。例如，商品的销售量与商品的价格、商品的质量以及消费者的收入水平等因素有关；又如果树的产量受施肥量、降雨量、气温等因素的影响。因此，要研究该经济行为就应从事物变化的因果关系出发，寻找它与其他因素之间的的内在联系，这就是因果关系分析法。在因果关系分析法中最常用的方法之一就是回归分析法。 回归分析预测法就是从各种经济现象之间的相互关系出发，通过对与预测对象有联系的现象变动趋势的分析，推算预测对象未来状态数量表现的一种预测方
17、法。 根据回归分析中所考虑因素的多少，可将回归分析分为一元回归分析和多元回归分析。例如，对于耐用消费品销售量与居民收入的相关关系的分析问题就属于一元回归分析；而对于粮食产量与施肥量、降雨量、气温的相关关系的分析问题就属于多元回归分析。一元回归分析实质上多元回归分析的一种特例。 根据回归模型是否是线性的，又可将回归分析分为线性回归分析和非线性回归分析。例如，若耐用消费品销售量与居民收入具有线性关系，则属于线性回归分析问题；若某商店的商品流通费用率与销售额具有曲线关系，则属于非线性回归分析问题。 本节先讨论多元线性回归法，然后再讨论非线性回归问题。 8.2.1 线性回归预测法1. 多元线性回归模型
18、设所研究的对象 受多个因素 的影响，假定各个影响因素与 的关系是线性的，则可建立多元线性回归模型： 式中 代表影响因素，通常是可以控制或预先给定的，故称为解释变量或自变量； 代表各种随机因素对 的影响的总和，称为随机误差项，根据中心极限定理，可以认为它服从正态分布，即 ； 就是所研究的对象，即预测目标，称为被解释变量或因变量。 根据n组观测值 则有 写成矩阵形式 其中 利用最小二乘法可得回归系数向量 的估计值 为 故回归分析模型为 2. 多元线性回归模型的检验 求出的回归模型是否合理，是否符合变量之间的客观规律性，引入所影响因素是否有效，变量之间是否存在线性相关关系，模型能否付诸应用，这要通过
19、检验决定。一般讲，回归模型必须通过三方面的检验：经济意义检验、统计检验和计量经济学检验。只有当所有检验都通过时，所做的回归模型才成立。才能利用连贯性、相关性和类推性原则，根据过去和现在的规律预测未来。当然，还应从经济角度上分析模型的预测值是否合理？是否符合经济规律？是否可行等等？ 经济意义检验 首先需要检验模型是否符合经济意义，检验求得的参数估计值的符号与大小是否合理，是否与根据人们的经验和经济理论所拟定的期望值相符合。如果不符，则要查找原因和采取必要的修正措施，重新建立模型。 统计检验统计检验是运用数理统计的方法，对方程进行检验、对模型参数估计值的可靠性进行检验。这主要包括拟合优度检验、方程
20、显著性检验、变量显著性检验，即常用的 检验、F检验和 检验。 拟合优度检验（ 检验）：检验所有解释变量与被解释变量间的相关程度。拟合优度检验就是检验回归方程对样本观测值的拟合程度。常用的方法是 检验，又称为复相关系数检验法，它是通过对总变差（总离差） 的分解得到。 其中 总变差平方和 是各个观察值与样本均值之差的平方和，反映了全部数据之间的差异；残差平方和 是总变差平方和中未被回归方程解释的部分，由解释变量 中未包含的一切因素对被解释变量 的影响而造成的；回归平方和 是总变差平方和中由回归方程解释的部分。 显然，对于一个好的回归模型，它应该较好地拟合样本观测值， 中 越小越好。于是可以用： 对
21、回归方程的拟合优度进行检验。如果所有样本观测值都位于回归方程上，即： 此时回归方程完全拟合了样本观测值， 等于1。当然，完全拟合的情况是不可能的， 不可能等于1，但毫无疑问， 越接近1，回归方程的拟合优度越高。 通常， 称为复可决系数，取值范围为0 1，其平方根 称为复相关系数（当解释变量的个数为1时， 称为相关系数）。这里， 说明了在被解释变量 的总变差中，由一组解释变量 的变动所引起的百分比； 则描述了一组解释变量 与被解释变量 之间的线性相关程度。例如 =0.93，可以说 值的变化有93%因 的变化而产生。因此，若 的值愈接近1，则表示 与 的关系愈密切。 由于 是一个随解释变量个数的增
22、加而递增的增函数，所以为使拟合优度检验指标不仅能反映已被解释的变差与总变差的关系，而且能反映回归模型中所包含的解释变量个数的影响，需要调整 ，记为 。 式中 为残差平方和 的自由度， 为总变差平方和 的自由度。根据 与 的定义式可推导出两者的关系式为： 从上式可以看出：当 时， ，这说明 中包含了解释变量个数的影响，随着解释变量个数的增加， 总是小于 ；尽管 总是非负的，但 却可能为负，若遇到 为负数的情况， 取值为零。 在实际应用中， 到底多大时回归模型才算通过了拟合优度检验呢？这并无绝对标准，要根据具体情况而定，因为拟合优度并不是检验回归模型的唯一标准，有时为了使模型有较合理的经济解释，可
23、能会在某种程度上牺牲拟合优度。这就是说模型的合理的经济解释是第一位的。 方程显著性检验（ 检验）：判断被解释变量与所有解释变量之间是否线性关系。 对于多元线性回归方程，方程显著性检验就是对总体的线性关系是否显著成立作出推断，即检验被解释变量 与所有解释变量 之间的线性关系是否显著，这就要检验假设： ：至少有一个 不为零 应用数理统计理论可以证明： 与 相互独立，且当 为真时， 与 分别服从自由度为 的 分布，故有： 即 统计量服从以（ ）为自由度的 分布。 首先根据样本观测值及回归值计算出统计量 ，于是在给定的显著性水平 下，若 ，则拒绝 ，判定被解释变量 与所有解释变量 之间的回归效果显著，
24、即确实存在线性关系；反之，则不显著。 比较 统计量与 检验值可以看出，在 一定时， 大，即拟合优度高， 统计量的值也大，容易通过方程显著性检验，拟合优度检验与方程显著性检验是一致的，只是前者比较模糊，后者更加定量化。一般来说，当拟合优度 0.80时，相应的 统计量的值将达到两位数，只要样本容量 适当，通过方程显著性检验是没有问题的。 变量显著性检验（ 检验）：挑选重要因素，剔除可有可无的因素。 前面讲的 检验和 检验都是将所有的解释变量作为一个整体来检验它们与被解释变量 的相关程度以及回归效果，但对于多元回归模型，方程的显著性并不意味每个解释变量对被解释变量 的影响都是重要的。如果某个解释变量
25、并不重要，则应该从方程中把它剔除，重新建立更为简单的方程。所以必须对每个解释变量进行显著性检验。这等价于对每个解释变量检验假设 其中 。 应用数理统计理论可以证明：当 为真时，统计量 服从自由度为 的 分布，即： 在给定的显著性水平 下，若 ，则拒绝 ，说明解释变量 对被解释变量 有显著影响，即 是影响 的主要因素；反之，接受 ，说明解释变量 对被解释变量 无显著影响，则应删除该因素。 需要说明的是，当影响 的主要因素只有一个变量 时，问题变成了一元回归分析，此时 检验和 检验的作用是一样的，因此可以不用再做 检验了。 计量经济学检验 在回归分析法中，假设随机误差项在不同的样本点之间是不相关的
26、，即 与 （ ）相互独立。但在实际问题中，经常出现与此相违背的情况， 与 （ ）之间存在相关性，称为序列相关。若存在序列相关，则此时的回归模型无效，必须重新建立回归模型。 在序列相关中，最常见的是 与 相关，称为一阶自相关。最常用的检验方法是DW检验法，定义DW统计量为： 其中： ，是 的估计值。 分析可知：当 与 正自相关时，DW0；当 与 负自相关时，DW4；当 与 不存在自相关或相关程度很小时，DW2；可见，DW的取值范围为04之间。 在实际应用中，对于给定的显著性水平 以及解释变量个数 、样本个数 ，从DW检验表中查得相应的临界值 和 ，然后利用下表判别检验结论。 DW值 检验结果 4
27、 DW4 否定假设，存在负自相关 0DW 否定假设，存在正自相关 DW4 接受假设，不存在自相关 DW 检验无结论（不能确定） 4 DW4 检验无结论（不能确定） 从表中可以看出，DW检验存在无结论区域，即当计算的DW统计量落到无结论区域时，决策者就不能作出回归模型是否存在自相关现象的结论。但通常，当DW统计量的值在2左右时，则无须查表检验即可判定回归模型不存在自相关，此时回归模型有效。 需要说明的是，回归分析法既适合于利用历史数据即时间序列数据来研究经济变量之间的定量关系，建立预测模型，进行预测分析；又适合于利用同一时期横断面数据来研究经济变量之间的内在联系，分析经济结构，比较经济发展状态及
28、发展趋势。当所研究的数据是横断面数据时，由于数据是同一时期的（例如由今年全国各地不同产区的粮食产量所组成的数据序列），不存在相关性，因此无须做DW检验。 3. 应用举例 例如，在改革中，某企业重视科技人才，提供了足够的科研经费，获得了良好的经济效益。下表是该企业19871998年的经济效益、科研人员、科研经费的统计数据。假定1999年该企业科研人员61名、科研经费40万元，试预测1999年该企业的经济效益。 年份经济效益（万元）科研人员（名）科研经费（万元）年份经济效益（万元）科研人员（名）科研经费（万元）1987406198.519936323813.71988484249.71994685
29、4714.419895042610.419957504916.219905202811.319967945018.519915603112.219978665120.319925913312.819989895325.0相关分析 回归分析是研究某一随机变量（因变量或被解释变量）与其他一个或几个普通变量（自变量或解释变量）之间的数量变动关系的，由回归分析求出的关系式是回归模型。而要研究及测度两个及两个以上变量之间关系，除上述的回归分析法外，常用的方法还有相关分析。相关分析是研究两个或两个以上随机变量之间的相互依存关系的紧密程度的。直线相关时用相关系数表示，曲线相关时用相关指数表示，多元相关时用复
30、相关系数表示，通常相关系数或相关指数或复相关系数的取值范围为01，该值越接近1说明两随机变量之间的相关程度越强，若该值等于0，则两随机变量相互独立。 这两种分析方法的区别是，相关分析研究的都是随机变量，并且不分自变量与因变量；回归分析研究的变量要定出自变量与因变量，并且自变量是确定的普通变量，因变量是随机变量。但在实际工作中，这两种分析是研究现象之间相互依存关系的不可分割的两个方面。一般先进行相关分析，根据相关系数或相关指数的大小对变量进行筛选，剔除不相关或是相关性小的变量，然后再进行回归分析，建立回归模型，进行预测。 本例有两个自变量：科研人员与科研经费。下面先分析它们与因变量经济效益的相关
31、性。具体操作步骤如下： 选择工具菜单中的数据分析命令，弹出数据分析对话框。 在分析工具列表框中，选相关系数工具。 这时将出现相关系数对话框，如图5.187所示。 图5.187 在输入框中指定输入参数。在输入区域指定数据所在的单元格区域B1:D13；因输入数据是以列方式排列的，所以在分组方式中选择逐列；因指定的输入区域包含标志行，所以选中标志位于第一行复选框。 在输出选项框中指定输出选项。本例选择输出区域，并指定输出到当前工作表以F3为左上角的单元格区域。 单击确定按钮。 所得到的相关分析结果如图5.188所示。 图5.188 从图5.188中F3:I6区域所给出的相关系数可以看出，科研人员与科
32、研经费和经济效益都有较强的相关性。因此，需要利用回归分析工具进一步建立关系式。 建立回归模型 通常，在相关分析的基础上必须拟合回归模型，以便进行推算、预测。建立回归模型的具体操作步骤如下： 选择工具菜单中的数据分析命令，弹出数据分析对话框。 在分析工具列表框中，选回归工具。 这时，将弹出回归对话框，如图5.189所示。图5.189指定输入参数。在输入Y区域、输入X区域指定相应数据所在的单元格区域，本例分别指定为B1:B13和C1:D13，并选定标志复选框，在置信水平框内键入95%。对于一些特殊的回归模型，可以根据需要指定常数为0（即 ）。 指定输出选项。这里选择输出到新工作表组，并指定工作表名
33、称为“回归模型”，选定残差（即随机误差项）和正态分布中的所有输出选项，以观察相应的结果。 单击确定按钮。最后得到回归分析的计算结果。图5.1810是有关回归分析的统计量、方差分析表和回归系数及其 检验、预测区间等数据；图5.1811给出了预测值、残差值以及所计算的DW统计值；图5.1812给出了自变量 （科研人员）与 （科研经费）的残差分析图；图5.1813给出了自变量 与 的最佳适配回归线图；图5.1814给出了样本百分比排位及正态概率图。 分析图5.1810、5.1811中的计算结果，可得下述检验结论（本例样本个数n=12，解释变量个数k=2）。 检验：在回归统计区域A3:B8中，给出的
34、为0.997644，调整后的 为0.997121，均很接近1，说明 与 的关系很密切。 检验：在方差分析区域A10:F14中，给出的 检验值为1905.786远远大于 =4.26，说明 与 之间的回归效果非常显著。 检验：在回归模型区域A16:I19中，给出了回归系数 的估计值及其标准误差、 检验值和回归系数估计区间的上下限等。由于各回归系数的 检验值分别为 =12.7637， =7.5472， =18.7804，都大于 =2.2622，故拒绝原假设 =0， =0和 =0。即可以断言：科研人员与科研经费对该企业的经济效益有显著影响。 图5.1810图5.1811图5.1812图5.1813图5
35、.1814 DW检验：在Excel给出的回归分析结果中没有给出DW检验值。因此，需要自行按公式计算DW统计值。方法是：先利用公式分别求出 之值，再单击工具栏上的求和按钮计算 ，最后将两个求和值相除便可求的DW值。如图5.1811中的G40单元格所示。对于给定的显著性水平为 =0.05，解释变量个数 =3、样本个数 =12，查DW检验表。因DW检验表中样本容量 最小为15，故取临界值 =0.82， =1.75，于是有DW统计值在 =0.82DW=2.621524 =3.25之间。所以该回归模型不存在自相关，通过计量经济检验。 综合上述计算结果和检验结果，可得如下的回归模型： 这是一个较为优良的回
36、归模型。现在利用该回归模型，就可以根据预测期的科研人员与科研经费，对该企业未来的经济效益进行预测。 现假定1999年该企业科研人员61名、科研经费40万元，即预测期的解释变量矩阵为 ，则1999年该企业的经济效益的预测值为： 因标准误差S=9.3039281，样本个数n=12，解释变量个数k=2，则在显著性水平 =0.05下，1999年该企业的经济效益的预测区间为： 即：当1999年该企业的科研人员61名、科研经费40万元时，在=0.05显著性水平下，该企业1999年的经济效益预测区间在1391.7191433.773万元之间。 矩阵运算 在上面的计算过程中，该企业1999年的经济效益预测值是
37、由预测期解释变量矩阵 与回归系数矩阵 的估计值 相乘得到的。在此是利用Excel提供的矩阵相乘函数MMULT方便求得的。具体的操作步骤如下： 选定A1:C1单元格区域键入预测期解释变量矩阵 ，选定E1:E3单元格区域输入回归系数矩阵 的估计值 。 在G1单元格输入公式：“=MMULT(A1:C1,E1:E3)”，按Ctrl+Shift+Enter复合键结束函数的输入。 这样就可得到经济效益的预测值，如图5.1815所示。 图5.1815 再如，要计算以A5:C7单元格区域中数据为矩阵的逆矩阵，并将计算结果放置在E5:G7单元格区域，则具体操作步骤为： 选定E5:G7单元格区域。 输入公式：“=
38、MINVERSE(E5:G7)”，按Ctrl+Shift+Enter复合键确认，这样就可得到所需的结果，见图5.1815中的E5:G7区域。 又如，要计算两个矩阵之和，则具体操作步骤如下： 选定E9:G11单元格区域。 输入公式：“=A5:C7+A9:C11”，按Ctrl+Shift+Enter复合键确认，这样就可得到所需的结果，见图5.1815中的E9:G11区域。 在上面的计算过程中，所用到的两个函数MMULT和MINVERSE分别是矩阵相乘和求逆矩阵函数。类似的，Excel提供的矩阵运算函数还有：求矩阵的行列式值MDETERM、求矩阵的转置TRANSPOSE等。 在实际应用中常常涉及到矩
39、阵的加、减、乘、求逆、求转置等运算，如在回归分析、求解线性方程组、求解线性规划以及统计分析等中，此时使用Excel的矩阵函数就可以方便地完成。但值得注意的是： 按Ctrl+Shift+Enter复合键结束输入 一般计算公式输入完毕后，都是按回车键或是光标键结束，而矩阵运算公式输入完毕后，必须按Ctrl+Shift+Enter复合键。这时相应的公式两端会自动加上花括号“”，相应的计算也将按矩阵运算规则进行。这是与一般数据计算的根本区别。 计算结果区域是一个整体 通常，矩阵运算的结果也是矩阵。当按Ctrl+Shift+Enter复合键结束输入时，计算结果将会填充到选定的整个结果区域，这时的整个结果
40、区域是一个整体，不能单独编辑其中的某个单元格。 4. 使用函数求解线性回归模型 在实际预测中，Excel 2000除可以利用回归分析工具外，还可以采用函数来进行预测分析。Excel提供的函数包括： LINEST：线性估计最小二乘法 TREND：趋势分析 FORECAST：线性趋势预测 INTERCEPT：线性回归截距分析 SLOPE：线性回归斜率分析 LOGEST：曲线估计回归分析 GROWTH：成长分析 STEYX：预测值的标准误差 下面只介绍线性估计最小二乘法LINEST函数的使用方法。 假设多元线性回归模型为： 式中 为自变量； 为因变量； 为回归系数。利用LINEST函数可以求出上述回
41、归模型，LINEST函数语法格式如下： LINEST（因变量Y，自变量X，常数项C，统计量S） 其中 因变量Y代表因变量 所在的数据区域； 自变量X代表自变量 所在的数据区域； 常数项C是一个逻辑常量，若其值为假FALSE，则强制上述多元线性回归模型中的常数项为0，即 ；若其值为真TRUE（默认值），则常数项 不为0，即根据给定数据进行具体计算； 统计量S是一个逻辑量，若其值为假FALSE（默认值），则函数只返回各个回归系数，函数返回的数据格式为 ；若其值为真TRUE，则函数返回各个回归系数与统计检验信息，函数返回的数据格式如下表所示。 计算结果 数据含义 回归系数 回归系数的标准误差 复可决
42、系数 、因变量标准误差 统计量、自由度 回归平方和 、残差平方和 对上面的预测某企业经济效益的例子，现在用LINEST函数来建立回归模型。具体操作过程如下： 在工作表中输入原始数据。 为输出数据选定足够的区域。若在返回回归系数的同时，也返回有关统计检验的信息，则本例可以选定区域。 向F2单元格输入公式：“=LINEST(B2:B13,C2:D13,TRUE,TRUE)”。 按Ctrl+Shift+Enter复合键结束输入。 这时，函数将计算结果的返回数据存放区域F2:H6中，如图5.1816所示。 图5.1816 对照上表可知，回归系数分别为 =116.8099， =4.1818， =26.0
43、212，回归系数的标准误差分别为 =9.1517， =0.5541， =1.3856，复可决系数 =0.9976、因变量标准误差 =9.3039， 统计量=1905.7886、自由度 =9，残差平方和 =779.0677，回归平方和 =329941.8490。 将这些计算结果与由回归工具所得的结果（图5.1810）比较可以看出，使用LINEST函数与利用回归工具进行回归分析可得到一致的结论。 若只需返回各个回归系数，则本例可以指定区域F8:H8；且向F8单元格输入的公式为：“=LINEST(B2:B13,C2:D13)”，按Ctrl+Shift+Enter复合键结束输入。其计算结果见图5.1816中的区域F8:H8。8.2.2 可线性化的非线性回归