定量分析实验(bf)

《定量分析实验(bf)》由会员分享，可在线阅读，更多相关《定量分析实验(bf)（24页珍藏版）》请在装配图网上搜索。

1、定量分析实验指导书 实验报告要求一、 实验报告要求格式符合规范、文字书写工整、版面整 洁、言简意明；二、 实验报告内容包括：1、 实验目的要求2、 实验操作要点3、 实验数据记录4、 实验分析和使用说明目 录实验一 预测分析技术-时间序列移动平均预测法实验二 预测分析技术-时间序列指数平滑实验三 预测分析技术-时间序列趋势外推法实验四 预测分析技术-线性回归分析预测法实验五 预测分析技术-季节性和趋势性预测法实验六 投入产出分析方法实验一 预测分析技术-时间序列移动平均预测法实验目的：熟练掌握Excel中通过添加趋势线法和使用数据分析中的“移动平均”宏工具对所给数据作移动平均分析的操作步骤。了

2、解趋势外推分析和移动平均的区别与联系。实验要求：通过绘制时间序列图，观察原始数据的大致发展情况，使用添加趋势线或者数据分析工具中的移动平均工具即可完成分析。要求比较原始数据和移动平均模型预测值的区别，并作出实际评价。趋势外推分析只能概括历史数据的总体发展趋势，无法追踪序列的波动性或者周期性特征，而移动平均对此将作出一定的改善，比趋势外推方法更为精准和灵活。实验内容： 近年来，我国社会消费品零售总额持续上升。我国20022007年社会消费品零售总额的部分月度数据，根据以上所给的数据，对社会消费品零售总额五年来的月度数据作移动平均分析。具体实验操作指导在作20022007年社会消费品零售总额的移动

3、平均分析前，仍然需要画出时间序列图，对期有直观的认识，然后运用数据分析中的移动平均作分析。其具体操作步骤如下：01、 打开工作表“social consumption.xls”，获得全部数据；02、选择“插入图表”命令，随即弹出“图表向导-4步骤之1-图表类型”对话框，在“自定义类型”选项下选择“平滑直线图”，单击“下一步”按钮。03、随即弹出“图表向导-4步骤之2-图表源数据”对话框，在“数据区域”中输入单元格区域“C2:C72”。单击“系列”选项，在“名称”后的文本框中输入“社会消费品零售总额”，单击“下一步”按钮。04、随即弹出“图表向导-4步骤之3-图表选项”对话框，单击“标题”选项，

4、在“图表标题”下的文本框中输入“2002-2007年社会消费品零售总额”，在“分类（X）轴”下输入“时间”，在“数值（Y）轴”下输入“零售总额”，单击“图例”选项，取消选中“显示图例”选项，单击“完成”按钮，如图所示。从图中可以看出，我国的社会消费品零售总额在波动中稳步上升，接下来就分别使用添加趋势线法和移动平均宏工具作进一步分析。一、添加趋势线法通过绘制散点图，添加趋势线的方法在回归分析和趋势外推模型中都有相应介绍。本实验中移动平均分析的具体操作步骤如下：01、单击图中的散点，右击选择“添加趋势线”，即弹出“添加趋势线”对话框，选择“趋势预测/回归分析类型”下的“移动平均”，在“周期”下的微

5、调按钮中设置为“6”，即移动平均的步长选择6个月。如图所示。02、单击“选项”选项卡，单击“趋势线名称”下的“自定义”按钮，在其后的文本框中输入“6个月移动平均”，如图所示，然后单击“确定”按钮。最后的移动平均趋势图如图所示：从图中可以看出，6个月移动平均能够较好的反映出社会消费品零售总额的长期发展趋势。但是似乎该移动平均的步长稍长了一些，所以可以根据实际需要减小步长，使之也原始数据更吻合。二、“移动平均”宏工具简章移动平均的关键是选择合适的步长。对同一批时间序列数据，选择不同的步长其模型是预测精度不同。前面步长取长了，可以尝试3个月的移动步长，如果效果不够理想还可以继续调试，使得预测数据和实

6、际数据能够最大程度的吻合。其具体步骤如下：01、选择“工具”“数据分析”命令，随即弹出“数据分析”对话框，选择“分析工具”下的“移动平均”，如图所示。单击“确定”按钮。02、随即弹出“移动平均”对话框，单击“输入区域” 后的折叠按钮，选中观测值的单元格“C1:C72”，选中“标志位于第一行”复选框，在“间隔”后的文本框中输入“3”，即移动平均步长为3，单击“输出区域”后的折叠按钮，选中输出的单元格区域D2，选中“图表输出”和“标准误差”复选框，如图所示，然后单击“确定”按钮。 最终输出结果如图所示，图中输出了3个月移动平均预测值及其标准误差。以及3个月移动平均的预测值和实际值的趋势图，该图显示

7、3个月的步长要比6个月的步长拟合效果更好，更接近于实际时序数据。所以本实验的移动步长选择3个月要优于6个月，模型精度更高。【结论】随着我国人民生活水平的大幅提高，社会消费品零售总额也逐年提升。数据显示，2007年12的零售总额与2002年相比，增长了近2倍多，但5年来其中的发展轨迹如何，将来是否可能出现停滞增长的情况，这些民生问题是广大民众最关注的问题。本实验通过对2002至2007年的时序数据作移动平均分析，结果反映了我国消费品零售总额长期稳定增长的趋势。即使每年的特定月份会有些许波动，或者周期性的影响，但是基本面上仍然是上扬的趋势，相信随着人同生活水平的日益提高，将会在社会消费品上有更多的

8、支出。实验二 预测分析技术-时间序列指数平滑法实验目的：熟练掌握Excel中使用规划求解工具求出阻尼系数的操作步骤，然后利用数据分析中的“指数平滑”分析工具对所给的时间序列数据作进一步的长期预测分析。实验要求：通过绘制所给时间序列数据的趋势图，观察原始数据呈现的基本特点，然后选择合适的长期预测分析模型。本实验选用“指数平滑”宏工具作预测分析，要求对模型的预测结果能给出正确的评价。另外还需掌握移动平均模型与本实验的指数平滑对长期趋势预测的各自优势和局限。实验内容： 利用我国社会消费品零售总额数据，对其用指数平滑宏工具作长期趋势预测。具体实验操作指导在对时间序列数据作指数平滑之前，要求先求出最佳的

9、阻尼系数，然后再利用数据分析中的“指数平滑”宏工具作进一步的分析。一、确定最佳阻尼系数1-最佳阻尼系数的确定主要是由EXCEL中“规划求解”宏工具完成，其原则为时间序列的实际值和预测值之间的误差最小。在EXCEL中具体操作步骤如下：01、打开工作表“social consumption.xls”，获得全部数据；02、单击单元格B74，D74，输入“1-”和“”。首先给定一个阻尼系数0.7，单击单元格E74，在编辑栏中输入“1-C74”.03、求出社会消费品零售总额的均值，单击单元格C75，在编辑栏中输入“=AVERAGE(C2:C72)”，回车。04、采用指数平滑法求预测值。对应的第一期预测值

10、采用原值代替，单击单元格D2，在编辑栏中输入“=C2”。对应第二期的预测值，单击单元格D3，在编辑栏中输入“=C2*$E$74+D2*$c$74”，再次单击单元格D3，拖曳鼠标至单元格D72，运用自动填充单元格即可求出所有预测值。05、求出总方差。根据误差公式，单击E2单元格，在编辑栏中输入“=（C2-D2）2+(D2-$C$75)2）/71”，回车。再次单击E2单元格，拖曳鼠标至单元格E72，运用自动填充单元格即可求出所有方差。单击单元格E75在编辑栏中输入“=SUM(E2:E72)”，结果如图。 06、选择“工具规划求解”命令，随即弹出“规划求解参数”对话框。单击“设置目标单元格”后的折叠

11、按钮，选中总方差所在的单元格E75，在“等于”后的选项中选择“最小值”选项。单击可变单元格的折叠按钮，选择阻尼系数所在的单元格C74。单击“约束”选择组中的“添加”按钮，弹出“添加约束”对话框，如图所示。阻尼系数的取值范围在0和1之间，因此单击“单元引用位置”下的折叠按钮，选择C74单元格，在中间的下拉列表框中选择“=”，在“约束值”下的文本框中输入“0”，然后单击“添加”按钮。再次弹出空白的“添加约束”对话框，在其中输入“单元引用格的位置”C74，在中间的下拉列表框中选择“=”，在“约束值”下的文本框中输入“1”，完成后单击“确定”按钮，将返回“规划求解参数”对话框，如图所示，完成单击“求解

12、”按钮。 07、随即弹出“规划求解”对话框，显示找到一解满足所有约束条件，则可以单击“报告”下的“运算结果报告”或者直接选中“保存规划求解结果”单选按钮。然后单击“确定”按钮。 最终的求解最佳阻尼系数的运算结果如图所示，而使用“规划求解”宏工具的最终结果。二、“指数平滑”宏工具根据所得最佳阻尼系数为0.8009695，符合通常情况。然后利用EXCEL中自带的“指数平滑”宏工具作进一步分析，其具体操作步骤如下：01、选择“工具”“数据分析”命令，随即弹出“数据分析”对话框，从中选择分析工具下的“指数平滑”，如图所示。单击“确定”按钮。02、随即弹出“指数平滑”对话框，单击“输入区域”后折叠按钮，

13、选择C1C72单元格区域，在“阻尼系数”后的文本框中输入规划求解的结果0.80096，选中“标志”复选框。单击“输出区域”后的折叠按钮，选中F2单元格，选中“图表输出”和“标准误差”复选框，如图所示，然后单击“确定”按钮。最后生成的指数平滑图、指数平滑结果及预测值如图所示。【结论】指数平滑法和移动平滑法相似，最近的资料所占的比重较大，也主是赋予的权重较大。由于一次指数平滑法适用于较为平稳的序列，一般的取值不大于0.5。如果的大于0.5平滑值才能和实际接近，通常说明该序列有某种趋势。此时，不适宜用一次平滑预测。本实验值约为0.2，说明序列变化较为平缓，所以可以使用一次指数平滑。对比两种预测结果，

14、发现移动平均比指数平滑的效果稍好，因为拟合的趋势更接近实际观察值。因此我们在实际应用时，经常会同时使用不同的方法，从而互相验证分析结果，提高模型的效度和信度。实验三 预测分析技术-时间序列趋势外推法实验目的：熟练掌握Excel中如何对时间序列数据用ITERCEPT函数和SLOPE函数作线性趋势外推分析。通过观察绘制时间序列图形，选取合适的曲线模型对数据作非线性趋势外推，并根据趋势模型的预测结果评价模型的优劣。实验要求：趋势外推模型和回归模型有本质的不同，前者主要是根据历史数据考查规律，从而预测整体的发展趋势，后者的主要目的是拟合回归模型，研究因变量和自变量之间的因果关系。要求通过Excel绘制

15、原始数据的时间序列图，通过添加趋势线的操作，对时序数据作趋势外推分析，并对下一期作出预测。实验内容：国内生产总值GDP是典型的时间序列数据，本实验从中国统计年鉴2007中摘录当年的国内生产总值数据，研究其增长趋势。根据以上数据，要求先绘制出GDP的时间序列图，然后分别作线性趋势外推分析和非线性趋势外推分析。具体实验操作指导在作趋势外推分析之前，需要先对时序数据作图形化处理，从而观察原始数据的基本走向，然后根据图形选择非线性趋势外推分析还是线性趋势外推分析。一、时间序列的预处理和建立回归方程一样，拿到一批数据首先要看它呈现什么样的趋势，然后再决定下一步应该采用什么统计方法。通过图形来观察数据，以

16、避免盲目地套用统计方法。绘制时间序列的操作方法类似于散点图的绘制，具体步骤如下：01、打开工作表“GDP.xls”，获得全部数据。02、选择“插入”“图表”命令，随即弹出“图表向导-4步骤之1-图表类型”选项下选择“平滑直线图”，如图3-1所示。单击“下一步”按钮。图3-1 “平滑直线图”对话框 图3-2 1978-2006年我国GDP走势03、随即弹出“图表向导-4步骤之2-图表源数据”对话框，在“数据区域”中输入单元格区域“C2:C30”。单击“系列”选项，在“名称”后的文本框中输入“1978-2006年GDP”，单击“下一步”按钮。04、随即弹出“图表向导-4步骤之3-图表选项”对话框，

17、单击“标题”选项，在“图表标题”下的文本框中输入“1978-2006年GDP趋势”，在“分类（X）轴”下输入“时间”，在“数值（Y）轴”下输入“GDP”，单击“图例”选项，取消选中“显示图例”选项，单击“完成”按钮，如图3-2所示。图中为我国自19782006年国内生产总值的发展趋势，显示了GDP总量从缓慢增长到快速增长的过程。二、非线性趋势外推分析 根据上图所绘制的散点图，可以判断作趋势外推时，不能选择简单的线性，而应使用非线性模型，本实验尝试使用指数函数和多项式函数。在Excel中的具体操作步骤如下：01、在输出的图中，单击图中的散点，右击选择“添加趋势线”，随即弹出“添加趋势线”对话框，

18、在“趋势预测回归分析类型”下选择“指数”类型。单击“选项”选项，选中“显示公式”和“显示R平方值”复选框，完成后单击“确定”按钮。如图3-3所示。02、点击图中的原始数据的散点，右击选择“添加趋势线”，弹出“添加趋势线”对话框，在“趋势预测回归分析类型”下选择“多项式”类型，在阶数下选择“2”阶。03、单击“选项”选项卡，选中“显示公式”和“显示R平方值”，完成后单击“确定”按钮，如图3-4所示。图3-3指数模型趋势外推分析结果 图3-4 指数曲线、多项式曲线趋势外推图形三、预测根据上述得出的趋势外推模型，包括指数模型和多项式模型，可以作一期预测，也就是2007年我国的GDP总额作预测。对于指

19、数模型，用GROWTH函数预测下一期的GDP值。单击单元格E1、E2、E3，分别输入“预测”，“指数曲线”和“多项式曲线”。单击F2单元格，在其编辑栏中输入“=GROWTH（D$2:D$30，A$2:A$30，A31）”，按回车键即得到结果。单击F3单元格，在编辑栏中输入“=TREND（D$2:D$30，A$2:B$30，A31:B$31）”，按回车键得出预测值。结果如图3-5所示。图3-5 一期预测结果实验结论自改革开放30年以来，中国经济迅速崛起，各方面取得的成就举世瞩目。而经济增长的平均速度维持在10%左右，更被世界称之为奇迹。本实验展示了1978年至2006年29年的国内生产总值的发展

20、趋势，并通过指数曲线和多项式曲线作了趋势外推分析。从图中可以看出，指数模型和多项式模型的拟合优度，R Square非常高，分别为0.9889和0.9882，说明趋势外推模型精度都比较高。而预测后的值，指数曲线的预测结果为288212.4亿元，多项式曲线的预测结果为209327.9亿元。实际上，2007年中国的GDP总额为249530亿元，正好介于这两个值之间。因此，可以将两个预测值作为2007年国内生产总值的上限和下限，这是统计中经常使用的方法，即将两个模型的预测结果作为置信上限和置信下限。趋势外推分析主要用于长期预测分析。它主要基于建立长期的经验模型，再作趋势外推，得到相应的预测值。但是，由

21、于客观世界的变化多端，纷繁复杂，基于经验模型的趋势外推法从某种程度上说有些呆板。所以本实验的两个模型趋势外推的结果和现实有些差距，但还是可以反映出中国经济仍然保持着强劲地增长，在一定程度上消除了国内外一些学者对中国经济的担忧。实验四 预测分析技术-线性回归分析预测法实验目的：熟练掌握Excel中对数据作一元回归分析的具体操作，包括应用散点图添加趋势线的方法，以及数据分析中自带的回归分析工具。实验要求：使用Excel对数据作一元回归分析时，要结合两种方法各自的优点。散点图添加趋势线法中的散点图可以直观地显示自变量和因变量的关系，若两变量不存在明显的线性或曲线关系，此时就可直接放弃建立回归模型。若

22、散点图显示变量之间确有一定的关系，添加趋势线时仅能输出具体方程和拟合度。所以需要进一步使用回归分析工具对模型作相应的参数检验、模型评价，并结合实际解释结果。实验内容： 为了考查高一的学习成绩和初三的成绩是否有必然联系，随机抽取了某高中35名高一学生作为调查对象，形成数据Highschool。具体实验操作指导一、应用散点图和趋势线进行回归分析01、打开工作表“highschool.xls”，获得本次实验的所有数据。02、选择“插入”“图表”命令，出现“图表向导-4步骤之1-图表类型”对话框，在“标准类型”选项框中的“图表类型”列表框中选择“XY散点图”，在“子图表类型”中单击选中“散点图”，单击

23、“下一步”按钮。03、在出现的“图表向导-4步骤之2-图表源数据”对话框中，单击“数据区域”选项卡中“数据区域”后的折叠按钮，选择C2-C35单元格区域，选择“系列产生在”中的“列”单选按钮，如图所示。04、单击“系列”选项卡，在“名称”后的文本框中输入“高一成绩和初三成绩散点图”，并单击“添加”按钮，单击“X值”后的折叠按钮，选择B2-B35单元格区域，单击“Y值”后的折叠按钮，选择C2-C35单元格区域，如图所示，完成后单击“下一步”按钮。05、随即弹出“图表向导-4步骤之3-图表选项”对话框，在“数值（X）轴”下的文本框中输入“初三成绩”，在“数值（Y）轴”下的文本框中输入“高一成绩”在

24、“图表标题”下的文本框中输入“高一成绩和初三成绩散点图”如图所示。单击“网格线”选项卡，取消选中“数值（Y）轴”选项区域的“主要网格线”。单击“图例”选项卡，取消选中“显示图例”。单击“下一步”按钮。06、随即弹出“图表向导-4步骤之4-图表位置”对话框，只需按照默认形式，插入图表即可，单击“完成”按钮。生成的散点图如图。07、右击散点图中的蓝色散点，在快捷菜单中选择“添加趋势线”。随即弹出“添加趋势线”对话框，在“类型”选项卡中的“趋势预测/回归分析函数”下选择“线性”选项图标，如图所示。08、单击“选项”选项卡，选中“自动设置”按钮，选中“显示公式”和“显示R平方值”复选框，如图所示，单击

25、“确定”按钮。最终结果如图所示。从图中可以看出，该回归方程为：y = 0.6798x + 24.095对应的拟合系数为0.642，拟合效果并不是非常好。【说明1】拟合效果一般，只有近64%的数据符合这个方程，另外，该方法对于回归方程和回归系数无法做参数显著检验，如果这个方程的自变量不能通过参数显著检验和残差检验，那么该回归方程是无效的，所以，需要用回归分析工具来弥补这个方法的缺陷，但这个方法的散点图绘制又是下面第二种方法所不具备的。二、应用回归分析工具进行回归分析01、打开工作表“highschool.xls”，获得本次实验的所有数据。02、选择“工具”“数据分析”命令，随即弹出“数据分析”对

26、话框，从中选择分析工具下的“回归”，如图所示。单击“确定”按钮。03、随即弹出“回归”对话框，单击“输入”下的“Y输入区域”后折叠按钮，选择C1C35单元格区域，单击“输入”下的“X输入区域”后折叠按钮，选择B1B35，单击选中“标志”，“置信度”复选框。单击“输出选项”后的折叠按钮，选择A38单元格，在“残差”、“标准残差”、“残差图”和“线性拟合图”复选框，在“正态分布”选项下单击选中“正态概率图”，如图所示，然后单击“确定”按钮。04、最后输出的结果有如下几张图。（1）回归汇总输出【说明1】看输出的汇总结果，“summary output”中显示该回归方程的拟合系数为0.642，调整后的

27、拟合系统为0.631，用于拟合方程的观测值为34个。【说明2】在“方差分析部分”，回归方程通过显著性检验，其F检验统计的值为57.37594，F统计量在原假设成立时发生的概率为1.25E-08，远小于显著性水平0.05（由于置信度为95%），所以该回归方程显著。【说明3】自变量“初三”成绩也通过回归系数的显著性检验，其中t检验统计量的P值为1.25377E-08，远小于显著性水平0.05，因此拒绝原假设，说明学生初三的成绩对高一的成绩有显著影响。（2）对应回归分析结果中残差图的输出。【说明4】除了作回归方程和回归系数的显著性检验以外，还需对回归残差作检验。因为回归方程的残差必须满足均值为零、独

28、立，且服从正态分布，否则使用最小二乘估计对参数作估计将失效。从回归残差图可以看出，残差的分布基本是零散分布，没有呈现明显的趋势，说明残差独立分布，该方程参数估计有效（3）对应回归分析结果中的线性拟合图。三、一元线性预测根据以上建立的回归模型，经参数估计和残差分析模型有效，则可作一元线性预测。即若已知某同学初三的成绩，就可以根据该回归模型算出其高一的成绩。本实验特地留下了最后一个对数据作预测分析，建立回归模型时并未引进该组数据。这是回归与预测分析的常用手法，即将数据分成两部分，一部分用于回归建模，另一部分作预测，以检验该模型的精确度。在EXCEL中的具体操作步骤如下：01、打开工作表“highs

29、hool.xls”，获得全部数据。02、单击单元格C37，在编辑栏中输入“=FORECAST(C36,C2:C35,B2:B35)”，单击回车，即可得到预测值，和真实值72相差1分多。【结论】通过以上回归分析，说明一个学生的初三成绩对其高一成绩的确存在显著影响，但由于上述拟合的方程系数仅为0.64，说明除了初三成绩这个显著因素以外，可能还存在着其他各种因素，比如学生进入高中后自身的努力程度，老师的水平，学校的教育质量等，都可能影响其高一的学习成绩。实验五 预测分析技术-季节性和趋势性预测法实验六 投入产出分析方法实验目的：熟练掌握Excel对投入产出分析的具体操作，包括直接消耗系数矩阵、完全消

30、耗系数矩阵、间接消耗系数矩阵和进行相关的预测。实验要求：使用Excel对数据作一元回归分析时，要结合两种方法各自的优点。散点图添加趋势线法中的散点图可以直观地显示自变量和因变量的关系，若两变量不存在明显的线性或曲线关系，此时就可直接放弃建立回归模型。若散点图显示变量之间确有一定的关系，添加趋势线时仅能输出具体方程和拟合度。所以需要进一步使用回归分析工具对模型作相应的参数检验、模型评价，并结合实际解释结果。实验内容：若某经济系统只分成农业、工业和其他三个部门，其某报告期的投入产出如表6-1所示，现要求利用EXCEL回答下列问题表6-1 简化的价值型投入产出表1、计算直接消耗系数、完全消耗系数、间

31、接消耗系数矩阵；2、当农业、工业、和其他三个部门的最终使用分别为6000、11500、1500亿元时，需要完全消耗各部门多少亿元？具体实验操作指导01、在Excel表中输入如图6-1所示的投入产出表中第1象限的数据，及总投入和总产出数据，具体如图6-1区域A1:E5所示。02、计算直接消耗系数矩阵A。操作方法是：在单元格B7中输入公式“=B2/B$5”，然后复制单元格B7，粘贴于区域B7:D9，即得到直接消耗系数矩阵A，如图6-1区域B7:D9所示。03、构建一个33单位矩阵。较为简便的方法是，先将一个33区域全部设置为数值0，然后再将对角线的元素换成数值1，如图6-1区域B11:D13所示。

32、04、计算矩阵-A。操作方法：在单元格B15中输入公式=“B11-B7”，然后复制单元格B15，粘贴于区域B15:D17，即得到矩阵-A，如图6-1区域B15:D17所示。05、计算（-A）-1。操作方法：选定区域B19:D21，输入公式“=MINVERSE（B15:D17）”（注意输入完后不要按回车），然后同时按下【Ctrl】、【Shift】和【Enter】键，即得到逆矩阵（-A）-1，如图6-1区域B19:D21所示。【备注】求出的逆矩阵的第一列数据（图6-1区域B19:B21）表明：当农业的最终使用增加1亿元时，农业总产值将增加1.18257261亿元，工业总产值将增加0.4777842

33、亿元，其它部门总产值将增加0.1659751亿元。图6-1 投入产出分析MINVERSE函数-返回数组矩阵的逆矩阵。其语法为：MINVERSE（array）l Array可以是单元格区域，例如A1:C3；常数数组如1，2，3；4，5，6；7，8，9；或引用前两者的名称。l 如果在Array中单元格是空白单元格或包含文字，则函数MINVERSE返回错误值#VALUE!。l 如果Array的行列数目不相等，则函数MINVERSE也返回误值#VALUE!。本例中，区域B19:D21中公式“=MINVERSE（B15:D17）”表示对区域B15:D17数字取逆，并把逆矩阵列于区域B19:D21中。06

34、、计算完全消耗系数矩阵（-A）-1-。操作方法：在单元格B35中输入公式“=B19-B11”，然后复制单元格B35，粘贴于区域B35:D37，即得到完全消耗系数矩阵B，如图6-1区域B23:D25所示。【备注】图6-1中，完全消耗系数矩阵中第一列数据表明：农业部门增加1亿元最终使用时，需要完全消耗农产品0.18257261亿元，工业产品0.4777842亿元，其它部门产品0.1659751亿元。07、计算间接消耗系数矩阵B-A。操作方法：在单元格B27中输入公式“=B23-B7”，然后复制单元格B27，粘贴于区域B27:D29，即得到间接消耗系数B-A，如图6-1区域B27:D29所示。【备注

35、】图6-1中，间接消耗系数矩阵中的第二列数据表明工业部门增加1亿元最终使用时，需要间接消耗农产品0.1282158亿元，工业产品0.446473亿元，其它部门产品0.1074689亿元。08、预测。根据题意，三个部门的最终使用列矩阵F=（6000 115000 1500），则要求的需要安全消耗各部门的产值为BF。操作方法：在单元格中选取单元格B31:B33中输入最终使用列矩阵F，选取单元格B35:B37，并输入公式“=MMULT（B23:D25，B31:B33）”（注意输入完成后不要按回车），然后同时按下【Ctrl】、【Shift】和【Enter】键，即返回结果，如图6-1区域B35:B37所

36、示。【备注】图6-1中，表明当农业、工业和其他三个部门的最终使用分别为6000、11500、1500亿元时，需要完全消耗农产品4000亿元，工业产品13500亿元，其它部门产品3500亿元。MMULT函数-返回两数组的矩阵乘积。其语法为：MMULT（array1，array2）l Array1，Array2是要进行矩阵乘法运算的两个数组。l Array1的列数必须与Array2的行数相同，而且两个数组中都只能包含数值。l Array1和Array2可以是单元格区域、数组常数或引用。如果单元格是空白单元格或含有文字串，或是Array1的行数与Array2的列数不相等时，则函数MMULT返回错误值

37、#VALUE!。本例中，区域B35:D37的公式“=MMULT（B23:D25，B31:B33）”表示对区域B23:D25（完全消耗系数矩阵B）和区域B31:B33（最终使用列矩阵F）的矩阵求乘积，并把结果列于区域B35:B37中。实验七 层次分析方法实验目的：熟练掌握Excel对投入产出分析的具体操作，包括直接消耗系数矩阵、完全消耗系数矩阵、间接消耗系数矩阵和进行相关的预测。实验要求：使用Excel对数据作一元回归分析时，要结合两种方法各自的优点。散点图添加趋势线法中的散点图可以直观地显示自变量和因变量的关系，若两变量不存在明显的线性或曲线关系，此时就可直接放弃建立回归模型。若散点图显示变量

38、之间确有一定的关系，添加趋势线时仅能输出具体方程和拟合度。所以需要进一步使用回归分析工具对模型作相应的参数检验、模型评价，并结合实际解释结果。实验内容：设层次结构模型如图7-1所示。注意这个问题中不是每一个方案层因素都和每一个准则层因素都相关。图7-1 问题的层次结构模型具体实验操作指导01、在Excel表中输入如表7-1所示的准则B1、B2、B3对目标A的两两比较判断矩阵，具体如图7-2区域A3:D6所示。02、计算正规化判断矩阵，具体步骤如下：计算判断矩阵中B1、B2、B3各列的和。方法是（以B1列求和为例）按住鼠标左键选中区域B4：B6，然后单击工具栏上的求和按钮“”，结果显示在B7，如

39、图7-2区域B7:D7所示；计算正规化判断矩阵：在E4输入公式“=B4/$B$7”，并复制到单元格E5、E6中；在F4输入公式“=C4/$C$7”，并复制到单元格F5、F6中；在G4输入公式“=D4/$D$7”，并复制到单元格G5、G6中；结果如图7-2区域E4：G6所示； 03、计算特征向量W，具体步骤如下：计算正规化判断矩阵中各行的和：造中区域E4：G4，按工具栏上的求和按钮“”，得H4；用同样方法对在单元格E5：G5，E6：G6求和，分别得到H5、H6，如图7-2区域H4:H6所示；对区域H4:H6求和，结果显示在H7中，如图7-2区域H7所示；计算特征向量W：在单元格中选取单元格I4，

40、并输入公式“=H4/H7”；在单元格中选取单元格I5，并输入公式“=H5/H7”；在单元格中选取单元格I6，并输入公式“=H6/H7”，如图7-2区域I4:I6所示；04、计算最大特征根计算AW：在单元格中选取单元格A11:A13，并输入公式“=MMULT（B4:D6，I4:I6）”（注意输入完成后不要按回车），然后同时按下【Ctrl】、【Shift】和【Enter】键，即返回结果，如图7-2区域A11:A13所示计算AW/W：在单元格中选取单元格B11，并输入公式“=A11/(3*I4)”；并复制到单元格B12、B13中，如图7-2区域B11:B13所示；计算AW/W各元素和：按住鼠标左键选

41、中区域B11：B13，然后单击工具栏上的求和按钮“”，结果显示在B14中，即为最大特征根，如图7-2区域B14所示；05、一致性检验计算CI：在单元格中选取单元格C10，并输入公式“=（B14-3）/（3-1）”（此处3为判断矩阵的阶数），如图7-2区域C10所示；查表输入RI：在单元格中选取单元格D10，并输入“0.58”，如图7-2区域D10所示；计算CR：在单元格中选取单元格有E10，并输入公式“=C11/D11”。【分析】经计算CR0.1，满足一致性检验06、重复0105步骤，对C1-P, C2-P, C3-P进行一致性检验；07、层次总排序及计算结果计算CI：在单元格中选取单元格B8

42、，并输入公式“=（A8-3）/（3-1）”。结果如图7-2区域B8所示。查表得RI：在在单元格中选取单元格C8，并输入“0.58”。计算CR：在单元格中选取单元格D8，并输入公式“=B8/C8”。结果如图7-2区域D8所示，其结果0.1，计算结果具有满意的一致性。图7-2 层次分析数据图实验1：梁烨、柏芳：EXCEL统计分析与应用267分钟多媒体全程实录，机械工业出版社，2009.6 P178183实验2：梁烨、柏芳：EXCEL统计分析与应用267分钟多媒体全程实录，机械工业出版社，2009.6 P183189实验3：梁烨、柏芳：EXCEL统计分析与应用267分钟多媒体全程实录，机械工业出版社，2009.6 P173178实验4：梁烨、柏芳：EXCEL统计分析与应用267分钟多媒体全程实录，机械工业出版社，2009.6 P137145实验5：梁烨、柏芳：EXCEL统计分析与应用267分钟多媒体全程实录，机械工业出版社，2009.6 P189194实验6：国家发展和改革委员会培训中心：经济计量分析与EXCEL应用，北京中国市场出版社，2005.1 P343358实验7：蒋绍忠：数据、模型与决策：基于Excel的建模和商务应用，北京：北京大学出版社，2010 C934/186蒋绍忠：电子商务系统分析设计与实现，大连：东北财经大学出版社，2001 F713.36/24024