多元统计分析在EXCEL中的应用

书书书内部资料（５３）江西省统计局编印二○○九年六月二十四日多元统计分析在ＥＸＣＥＬ中的实现多元统计分析作为研究多维随机变量之间相互依赖关系以及内在规律的一门科学，它在经济研究的各个领域都有着广泛的运用，并取得了许多卓有成效的应用成果。多元统计分析已经越来越成为多类学科进行科学研究、数据分析、数据处理必不可少的重要方法。多元统计分析软件为我们提供了便利，如：ＴＳＰ、ＳＰＳＳ、ＳＡＳ和Ｒ软件，本文介绍在ＥＸＣＥＬ中如何实现多元统计分析。一、基本原理多元统计分析在ＥＸＣＥＬ中的实现原理基于多元统计分析ＢＡＳＩＣ程序，以ＥＸＣＥＬ工作簿进行数据维护和管理，利用ＥＸＣＥＬ的ＶＢＡ程序开发多元统计分析宏，包括：多元回归分析宏、主成分分析宏、·１·因子分析宏、聚类分析宏、判别分析宏、对应分析宏、典型相关分析宏，在将结果以ＥＸＣＥＬ数表形式进行分析宏打包，最终形成应用方便的“多元统计分析程序”，只要进行安装，在ＥＸＣＥＬ的菜单上就出现了“多元统计分析程序”，应用方便直观。二、应用描述（一）简单回归分析在ＥＸＣＥＬ中的实现１、问题及背景通过经济分析可知：国内生产总值（ＧＤＰ）与固定资产投资有密切关系，研究发现两变量之间存在线性关系。根据江西１９９０－２００７年的ＧＤＰ与全社会固定资产投资数据，研究它们的数量规律性，探讨江西固定资产投资与ＧＤＰ的数量关系。２、分析过程第一步：录入原始数据，数据格式如下图１。图１　原始数据·２·第二步：启动多元统计分析系统，选择“回归分析”。单击确定，并选择其中的“多元线性回归”。图２　选择项对话框图３　选择项对话框第三步：填写系统需要信息。样品数为年份数１８，自变量个数为１，待预报样品为０，如图４。·３·　图４　回归分析对话框第四部：单击确定，得到运行结果，如图５。图５　一元回归分析结果·４·３、输出结果和分析结论回归方程为：ｙ＝４５９５８３６＋０６４２８ｘ１其中，复相关系数为Ｒ＝０９８０４，说明回归方程拟合优度较高。而回归系数ｔ＝１９９２５７，查ｔ分布表ｔ００２５（１６）＝２１１９９，小于ｔ值，因此回归系数显著。查Ｆ分布表，Ｆ００５（１，１６）＝４４９，由回归结果知，Ｆ＝３１３３７６５＞４４９，因此回归方程也显著。我们可以根据回归方程进行预测。若已知２００９年江西全社会固定资产投资可以预测２００９年江西ＧＤＰ数据。程序中如果输入待预报样品信息，可以直接获得待预报样品的预测结果。（二）多元线性回归模型在ＥＸＣＥＬ中的实现１、问题及背景在我国，居民消费是在国内生产总值经过初次分配和再分配后形成的，因此考虑人均国内生产总值作为影响人均消费的一个因素；另外，在进行收入分配时，必须考虑到消费者已经实现了的消费，保持消费的连续性，因此，分析当年的消费必须考虑上年已经实现的消费。为探寻我国的人均消费模型，我们搜集整理了我国１９９０－２００７年人均消费及人均ＧＤＰ数据。２、分析过程第一步：录入数据，数据格式如图６。·５·　图６　原始数据第二步，启动多元统计宏，选择回归分析，单击确定，并选择其中的“多元线性回归分析”。第三步：根据系统要求信息填写，样品数为１８，自变量数为２，带预报样品数１个。图７　多元回归对话框·６·第四步：单击确定，得到多元线性回归运算结果如图８。图８　多元回归结果３、输出结果和分析结论具体输出结果有原始数据表，均值表，正规方程系数矩阵，逆矩阵，回归系数，回归方程，方差分析表，复相关系数，自变量的ｔ检验值，各个自变量的偏回归平方和，偏回归系数，观测值，回归值，残差值，以及预测结果。由输出结果，得出以下结论：ｙ＝２２７６９０３＋０１５５６ｘ１＋０６３１９ｘ２　　　ｔ值　（６３２４３）（８５０５４）·７·从实际意义来看，各个回归系数均大于零，没有明显错误。由回归方程可知，如果其他变量保持不变，则人均ＧＤＰ每增加１元，人均居民消费将增加０１６元；同理，如果其他变量保持不变，前期居民消费每增加１元，人均居民消费将增加０６３元。回归系数显著性检验：当显著性水平分别为００１，００５时，ｔ分布临界值分别为ｔ０００５（１８－２－１）＝２９４６７，ｔ００２５（１８－２－１）＝２１３１５。由上述方程各自变量的ｔ检验值可知，变量ｘ１、ｘ２均显著。回归方程的显著性检验：查Ｆ分布表可知Ｆ００５（２，１５）＝３６８，由多元回归结果，Ｆ统计量的值为３２９２１９，显然大于３５５，因此回归方程显著。复相关系数Ｒ为０９９８９，从统计意义来看其方程的拟合度很高，总体显著性好。预测：若已知２００９年的前期（２００８年）人均居民消费和２００９年的人均ＧＤＰ值，则可以预测２００９年的人均居民消费。ｙ２００８＝２２７６９０３＋０１５５６ｘ１＋０６３１９ｘ２此步也可在程序中得以实现，即待预报样品数为１，输入相应的２００８年的前期（２００７年）人均居民消费和２００８年的人均ＧＤＰ值，即可得到运算结果。（三）岭回归分析在ＥＸＣＥＬ中的实现１、问题及背景城镇居民的住房需求受多种因素影响，有宏观上的地区经济总体水平，固定资产投资状况的影响，也有微观上居民收入、储蓄的影响。从搜集数据方便的角度出发，我们以北京市为例，研究北京市城镇居民人均居住面积的影响因素，选择如下指标进行分析：城市人均住房面积（ｙ，平方米），城镇居民人均可支配收入（ｘ１，元），人均城镇储蓄存款余额（ｘ２，元），人均ＧＤＰ（ｘ３，元），房地产开发投·８·资额（ｘ４，亿元），人均固定资产投资（ｘ５，元）。由于各影响因素间存在多重共线性问题，如果仍使用最小二乘方法，参数估计值的方差会出现偏差，因此，我们使用岭回归来修正。２、分析过程第一步：录入原始数据，数据格式如下图９。图９　原始数据第二步：启动多元统计分析宏，选择“回归分析”，单击确定，选定其中的“岭回归分析”。·９·　图１０　选择对话框第三步：同前所述，根据系统要求输入样品数位１８，自变量为５，带预报样品１个。图１１　岭回归对话框第四步：单击确定，得到岭回归分析结果。·０１·　图１２　岭回归分析结果３、输出结果和分析结论（１）分别以ｋ＝ｋｔ４，ｋ＝ｋｔ２，ｋ＝３ｋｔ４，ｋｔ，建立岭回归估计方程。·１１·（２）根据各岭回归的剩余值计算误差平方和可知，当ｋ＝ｋｔ４时，误差平方和最小，因此选择ｋ＝ｋｔ４时的预报结果，相应的岭回归方程为：ｙ＝０００１７９７９ｘ１－００００３４ｘ２＋０００１１３２６ｘ３＋００２８６１６ｘ４－０００００９５８ｘ５由上述方程，我们可以进行因素分析，其中，对居民人均居住面积影响较大的因素主要有：人均可支配收入（ｘ１）、人均ＧＤＰ（ｘ３）和房地产开发投资（ｘ４），与人均储蓄存款（ｘ２）和人均固定资产投资（ｘ５）影响不大。（四）主成分分析在ＥＸＣＥＬ中的实现１、问题及背景随着生产力水平的不断提高，我国居民生活水平不断提高，生活质量也在不断改善。但是受个地区生产力发展水平不平衡的影响，我国各地区居民生活质量也表现为不平衡，为了分析各地区居民生活质量的状况并进行综合评价，我们选取如下指标体系对２００７年全国３１个省市、自治区的居民生活质量状况进行评价分析，即职工人均工资（ｘ１，元），人均住宅建筑面积（ｘ２，平方米），城市用水普及率（ｘ３，％），城市燃气普及率（ｘ４，％），人均城市道路面积（ｘ５，平方米），人均公共绿地面积（ｘ６，平方米），批发零售贸易额（ｘ７，亿元），旅游外汇收入（ｘ８，百万美元）。２、分析过程第一步：录入数据，数据格式如图１３。·２１·　图１３　原始数据第二步：启动多元统计分析程序，选择“主成分分析”。第三步：确定并输入系统要求信息，样品数位ｎ＝３１，ｐ＝８。特征值贡献率一般选择为８５％，如下图１４。·３１·　　３、输出结果图１４　标准化数据·４１·表１相关系数矩阵１０４５３６８４６２０３２１３３１４０３００６１６０４－０１５４４３３４－０１２４７３７０１２４７１７１８０５１１６９０６０４５３６８４６１０３６３３４８５０４４４７２１４８６４Ｅ－０２９１９Ｅ－０２０４２９２０９５８０４５７２６１５０３２１３３１４０３６３３４８４８１０８１９４０８５５０１７２６５７３２５９０Ｅ－０２０２０７５３１８４０１２７１５３０３００６１６０４４４７２１４０８１９４０８５１０１０７６６７０５０２８５６１３７０４２１３４３７４０３４７９９５９－０１５４４３３８６４Ｅ－０２０１７２６５７３０１０７６６７０５１０６７６２６２０２２５１８９９１－０１８７５８－０１２４７３７９１９Ｅ－０２５９０Ｅ－０２０２８５６１３７４０６７６２６１９９１０５４０４３６４２０１９７１６０３０１２４７１７２０４２９２０９５８０２０７５３１８０４２１３４３７４０２２５１８９９１０５４０４３６４１０７３７９２２７０５１１６９０６０４５７２６１５２０１２７１５３０３４７９９５９４－０１８７５８０５０１９７１６０３０７３７９２２６５１表２特征向量（列向量）０２９３０４２８－０４２０９６７５１６４Ｅ－０２－０５５４０９９４－０５６８７３２２００３５９３５５－０２９２７０６３０１３８５３８８０３９７８３８５－０１６３６１９３１７６Ｅ－０２－０４４６６６３５０７４５２８６０７０２１５３５２８３９２Ｅ－０３－０１１５２８４０３５９７０５９－７６２Ｅ－０２－０６１３９５０１８１１７６７３－６７７Ｅ－０２－０３６０３４３－８５３Ｅ－０２－０５５９７６８０４４５１１６７－２６０Ｅ－０２－０３９９８９４０３５７６９０５３－４３８Ｅ－０２０３８６８３２２０１８６７２１２７０５７１７６５０１３４２１１８０６０７３６７９２－０１７４１６－０４７２８３０５－５７５Ｅ－０２－０４１７３４６０３３２７６７９０２６６４０６１０２５３２６８４０５８７８０３４６０１４７８７８１－３０５Ｅ－０２－０２５２４０８０５６５８６７８－０１５５０１７５－０３９９４９８０４２９４４０８０１８３６７４８７０３９９２９９９０２９０３２５０２０１３０３４７５７－０４００４５４－０５５３４５７２０２３３３３７８０４０２１８９１－０２０３４２６８０５００９３０１５４６３３９６－０１７４９０１３－０１３４５３１０６５６５７１５２－０２０５５３３表３累计贡献率表ＮＯ特征值Ｈ（　）百分率ＬＨ累计率１３１７３４８６８９０３９６６８５９０３９６６８５８６２１８４２８９５２２０２３０３６１９０６２７０４７７６３１３１０７５３８７０１６３８４４２０７９０８９２４０７１９７８４３４００８９９７３０８８０８６５０４５０５２４６４８５７００６５５８１１０９４６４４６１１６０２５６６５００７００３２０８１３０９７８５２７３７７００９３３６４８３００１１６７０６０９９０１９７９７８００７８４１６２０００９８０２１　　由特征值得累计百分率确定方差贡献的百分数８５％，由此选择主成分Ｍ＝４。·５１·表４主成分