浅议EXCEL拟合回归模型的主要方法

【#文档大全网# 导语】以下是®文档大全网的小编为您整理的《浅议EXCEL拟合回归模型的主要方法》，欢迎阅读！



浅议EXCEL拟合回归模型的主要方法

【摘 要】经济的快速发展，促使人们采用各种数据分析工具对其未来发展形势做出判断和预测，回归模型的拟合就是其中一种常用的方法，而利用EXCEL可以使人们更便捷、更迅速地进行这种推算。本文主要探讨了现阶段人们利用EXCEL拟合回归模型的可能性，同时也分析了在EXCEL中拟合回归模型的三种方法及其优劣势。



【关键词】数据拟合；回归；EXCEL2003

随着我国市场经济的进一步完善，日常生活中我们会接触大量经济数据，如何让这些数据发挥更大的作用，使人们对未来经济形势的判断更加准确，就成了人们普遍关注的一个重要问题，而回归模型的拟合适度解决了其中部分问题。



一、回归分析的概念及主要内容

在我们生活中事物之间普遍存在着联系和制约的关系，如果将事物看作是变量的话，这些变量之间的关系一般表现为两大类：其一是函数关系，即变量之间存在确定性的依存关系，就是通常所说的“一一对应”；其二是相关关系，即变量间存在非确定性依存关系，当一个或是一组变量取某一值时，另一个或一组变量可能得到很多结论[1]。函数关系的存在使得人们可以容易地通过一个变量推导出另一变量的结果，从而进行相关预测，但事实上现实生活中绝大部分事物表现为相关关系，这就需要我们通过一组变量估算另一组变量的变化， 即进行回归分析。根据其存在的自变量多少，可以将回归分析分为一元回归和多元回归，也可以根据自变量与因变量之间的关系表现形式，将回归分析分为线性回归分析和非线性回归分析。



二、利用EXCEL拟合回归模型的可能性

首先EXCEL是微软公司推出的办公软件OFFICE系列组件之一，而OFFICE作为普通的办公软件已经成为计算机装机的主要程序之一，不需额外再购买软件，因此人们获取这种数据分析工具远远易于其他的专业分析软件；其次EXCEL的功能主要表现在强大的制表、数据处理和分析等功能上，它不仅可以处理一些简单的表格计算和图表制作，还可以通过一些更高级地数组、函数、宏等解决一些相对复杂的问题，特别是很多工作人员、学生等在培训或是课堂教学中早已将OFFICE列入必学办公软件，从而使得EXCEL有更广泛地应用普及性；此外，EXCEL的图表、工作表函数和内置的回归分析工具等都可以轻松依据提供的成组数据拟合出适合的回归模型，从而也使得人们利用EXCEL拟合回归模型成为可能。



三、EXCEL拟合回归模型的主要方法

利用EXCEL对成组出现的经济数据拟合回归模型有很多种方法，以




EXCEL2003为例，常用的较为简单的、也容易被大众接受的主要有以下三种：



1、图表法

利用图表进行回归分析是人们比较常用的一种方法，这种方法主要是把收集到的成组数据利用EXCEL图表制作选项“XY散点图”，通过在构成的散点图上构造“趋势线”，从而作出回归分析。具体步骤是：①先将成组的经济数据以数据库记录的方式录入EXCEL工作表中；②在“插入”菜单中选择“图表”选项，在“图表类型”中选择“XY散点图”，进入“下一步”；③在“数据源”对话框中选取之前输入的成组数据作为“数据区域”，并根据需要设置标题、坐标轴刻度、图例等；④单击“完成”按钮，得到与工作表配套的XY散点图；⑤用鼠标选取图中数据点，通常选择反光数据点较多的为好，单击鼠标右键，在出现的菜单中选择“添加趋势线”选项；⑥在“添加趋势线”对话框中设置回归模型的类型，同时还可以在“选项”页面中选取“显示公式”和“显示R平方值”选项，从而得到回归模型的公式和XY变量的关联程度；⑦最后单击“确定”，得到回归模拟线以及回归模型的公式。



图表法是通过在EXCEL图表中拟合回归趋势线进行预测，它可以较为直观地描述成组数据间的相关程度和变动关系，复杂程度较低，当人们仅需了解事物未来发展方向或是变动状况时，它是非常好地进行回归预测的工具。但是显然，它也有很多缺陷，例如不能得到更深层次地统计推断，不能得到更复杂的回归模型的验证结果，此外这种方法还有一个更大的缺点，就是它只能构建一元回归模型，如果需要判定两个以上自变量和因变量的回归关系，这种方法就不适用了。



2、工作表函数法

EXCEL2003中提供了很多工作表函数帮助人们处理各种各样的数据，根据回归模型以及参数的特点，也可以利用其中的一些函数帮助我们对成组数据进行回归模型的创建。例如INTERCEPT函数，它是根据已知的X和Y值计算回归直线在Y轴的截距；SLOPE函数，是求解回归直线的斜率；利用RSQ和STEYX函数可以轻松推算出X值在Y值变化中的影响程度以及Y值实际值与预测值之间的产生的标准误差，从而对拟合的回归模型做出一定程度的验证。



工作表函数法是将得到的数据通过EXCEL中内置的函数计算和处理，从而得到想要拟合的回归模型中的一些参数和验证指标，这种方法指向明确，当我们仅需要对回归模型中部分参数进行计算时，尤其是针对一元直线回归模型常用此方法，但是如果需要拟合的回归模型较为复杂，这种方法就不太合适。此外，这种方法需要使用者记住很多工作表函数的名称及其具体功能，对于其英语和计算机水平、记忆力等都有较高的要求，因此在拟合回归模型中普及性相对较差。



3、回归分析工具法

针对数据处理的多样性，EXCEL中还提供了多种数据分析工具，它们可以把需要处理的数据按照模型要求进行一系列的数据分析并得出相关结论，简化了人们对于数据分析的工作流程，从而节约时间和精力，回归分析工具就是其中的




一种。利用回归分析工具拟合回归模型需要以下几个步骤：①在“工具”菜单中点击“加载宏”选项，在弹出的对话框中钩选“分析工具库”和“分析工具库——VBA函数”，安装分析工具库；②分析工具库安装完成以后，重新进入“工具”菜单，这时增加了“数据分析”选项，点击“数据分析”，在弹出的对话框中选择“回归”，在打开的对话框中将X、Y值在工作表中的区域范围输入，设置“置信度”、“输出区域”、“残差”等选项；③最后在工作表中生成拟合的回归模型，并对回归模型进行方差分析和模型验证。



EXCEL2003中回归分析工具是通过对收集的经济数据应用最小平方法拟合回归直线模型，无论是一元回归还是多元回归都可以使用，最主要的是它不仅可以得到具体的回归模型参数，还可以针对拟合的回归模型做出一系列的验证和相关检验，这对于那些对统计不太熟悉的人来说方便至及，而对于熟悉统计的人来说也可以节约大量推算和验证的时间，因此在拟合回归模型中这种方法应用较多。但是回归分析工具法也有一些缺点，例如它拟合的是直线模型，如果遇到非线性关系的数据，首先需要利用图表法得到其最接近的发展趋势及相关模型，再将这种非线性模型转化为线性表达后，才能使用回归分析工具法进一步推算和验证。此外，数据分析工具库是在EXCEL典型安装之外的，所以必须单独安装，安装时需要提供EXCEL安装软件，相对有些麻烦。



随着我们每天接触的信息量的不断增加，拟合的回归模型也变得越来越复杂，如何找到适合自身要求的计算机软件解决现行遇到的难题就显得至关重要。虽然EXCEL2003拟合回归模型时有多种方法，而且这些方法都存在一定的优势和缺陷，但是只要其应用的背景合适，方法得当，必然能够为人们处理数据、决策时提供巨大的帮助。



参考文献：

[1] 于洪彦，刘金星. EXCEL统计分析与决策（第2版）[M]. 北京：高等教育出版社， 2009：227.



[2] 谈笑，刘忠伦，张婷婷. 应用EXCEL宏功能进行逐步回归分析与SPSS的比较[J]. 中国医疗前沿，2013（24）.



[3] 方小铁. 基于EXCEL图表向导进行数据拟合问题的解决方案[J]. 科技经济市场，2006（4）.



[4] 高海鸥. 试述EXCEL的函数与数据处理操作[J]. 电脑编程技巧与维护，2013（4）.

本文来源：https://www.wddqxz.cn/15c78fe009a1284ac850ad02de80d4d8d15a0166.html