线性回归模型的三种Ｅｘｃｅｌ构建方法分析及其比较.docVIP

线性回归模型的三种Ｅｘｃｅｌ构建方法分析及其比较 　　摘要：本文介绍了在Excel环境下的三种线性回归模型的构建方法，分析了三种方法的优缺点，讨论了各自的适用范围，不同用户可以根据自身特点和需要来选择使用适合自己的方法。 　　关键词：Excel；线性回归；模型 　　中图分类号：TP311文献标识码：A文章编号：1009-3044(2008)28-0242-02 　　Analyses of Three Kinds of Construction Methods for Linear Regression Model Based on Excel 　　ZHANG Jin 　　(Computer and Information Engineering College of Henan University, Kaifeng 475001, China) 　　Abstract: This paper offered there kinds of construction methods for linear regression model based on Excel and analyzed their application fields. Different users can chose appropriate method that adopt their character and needs. 　　Key words: excel; linear regression; model 　　1 引言 　　在自然界中，某一现象往往受多种因素的影响，如何在对现有数据进行分析的基础上，找到因变量与自变量之间的关系，找出其内在规律是研究者关注的主要问题之一。线性回归模型是实现上述目的的主要手段之一，如何根据已有数据构建线性回归模型则是首先需要解决的问题。而一般情况下用于进行线性回归分析的数据量和计算量都非常大，单纯依靠手工方式分析计算是不太现实的，需要借助各种计算分析软件来实现。Office办公软件是国内安装使用最为广泛的软件，作为其套件之一，Excel界面友好，功能强大，且易学易用，是非专业人士进行数据分析的首选工具。但能熟练将Excel用于线性回归分析的人士并不多。本文对Excel环境下线性回归模型的构建加以分析，具体描述了三种模型构建方法并进行了比较。 　　2 Excel的三种线性模型构建方法 　　借助Excel特有的单元格之间的链接、函数及数据分析功能等，可以有效的解决诸如此类的相关问题。 　　2.1 手工模式的翻版 　　例：某一生产企业某年12个月的广告费、销售单价及销售量如表1所示。 　　此企业各月的销售量与销售单价及广告费之间存在一定的联系，通过对三者之间关系的分析，可以有效地对未来企业的销量进行预测，而进行预测的前提是首先构建三者之间的数据模型，从数据的初步分析可以发现三者之间存在明显的线性关系，即： 　　y(销售量)=a1*x1(销售单价)+a2*x2(广告费)+b 　　按照手工方式进行计算，需首先计算出相关的中间变量，然后带入公式，得到相应的系数a1、a2及b的值。具体步骤如下： 　　1) 在A1：M4单元格依次录入某企业12个月的销售量、销售单价及广告费等原始数据； 　　2) 计算出中间结果∑x2、（∑x）2、∑y2、（∑y）2等； 　　3) 由此按照公式得到L11、L12、L22等； 　　4) 代入公式，计算出系数a1、a2及b的值（见图1）。 　　2.2 利用Excel的函数功能 　　Excel中包含有大量实用的函数，可以借助INDEX、LINEST等函数对上述问题进行计算分析。同样对于上例，步骤如下： 　　1) 如第一种方法的步骤一所示，首先录入相关数据； 　　2) 利用INDEX、LINEST函数，进行计算（具体如图2所示）。 　　a1参数的计算公式为：B6=INDEX(LINEST(C2:N13,C4:C5,TRUE,TRUE),1,1) 　　a2参数的计算公式为：B7=INDEX(LINEST(C2:N13,C4:C5,TRUE,TRUE),1,2) 　　b参数的计算公式为：B8=INDEX(LINEST(C2:N13,C4:C5,TRUE,TRUE),1,3) 　　2.3 利用Excel的数据分析功能 　　Excel“工具”栏下的“数据分析”功能，对于构建此类线性回归模型非常方便。如果Excel工具菜单中未加载此项功能，可以用过单击“工具”下的“加载宏”，在出现的对话框中，选择“分析工具库”，单击“确定”，完成此项功能的加载。如果已经有此项功能，可以按如下步骤完成相关计算： 　　1) 依次输入例题中的相关数据（如图2所示），尤其需要提