登录
首页精彩阅读一文学会如何用Excel做回归分析
一文学会如何用Excel做回归分析
2016-06-10
收藏

一文学会如何用Excel做回归分析

一、什么是回归分析法

回归分析”是解析“注目变量”和“因于变量”并明确两者关系的统计方法。此时,我们把因子变量称为“说明变量”,把注目变量称为“目标变量址(被说明变量)”。清楚了回归分析的目的后,下面我们以回归分析预测法的步骤来说明什么是回归分析法:

1.根据预测目标,确定自变量和因变量

明确预测的具体目标,也就确定了因变量。如预测具体目标是下一年度的销售量,那么销售量Y就是因变量。通过市场调查和查阅资料,寻找与预测目标的相关影响因素,即自变量,并从中选出主要的影响因素。

2.建立回归预测模型

依据自变量和因变量的历史统计资料进行计算,在此基础上建立回归分析方程,即回归分析预测模型。

3.进行相关分析

什么是回归分析法回归分析是对具有因果关系的影响因素(自变量)和预测对象(因变量)所进行的数理统计分析处理。只有当变量与因变量确实存在某种关系时,建立的回归方程才有意义。因此,作为自变量的因素与作为因变量的预测对象是否有关,相关程度如何,以及判断这种相关程度的把握性多大,就成为进行回归分析必须要解决的问题。进行相关分析,一般要求出相关关系,以相关系数的大小来判断自变量和因变量的相关的程度。

4.检验回归预测模型,计算预测误差

回归预测模型是否可用于实际预测,取决于对回归预测模型的检验和对预测误差的计算。回归方程只有通过各种检验,且预测误差较小,才能将回归方程作为预测模型进行预测。

5.计算并确定预测值

利用回归预测模型计算预测值,并对预测值进行综合分析,确定最后的预测值。

二、回归分析的目的

回归分析的目的大致可分为两种:

第一,“预测”。预测目标变量,求解目标变量y和说明变量(x1,x2,…)的方程。

y=a0+b1x1+b2x2+…+bkxk+误差(方程A)

把方程A叫做(多元)回归方程或者(多元)回归模型。a0是y截距,b1,b2,…,bk是回归系数。当k=l时,只有1个说明变量,叫做一元回归方程。根据最小平方法求解最小误差平方和,非求出y截距和回归系数。若求解回归方程.分別代入x1,x2,…xk的数值,预测y的值。

第二,“因子分析”。因子分析是根据回归分析结果,得出各个自变量对目标变量产生的影响,因此,需要求出各个自变量的影响程度。

希望初学者在阅读接下来的文章之前,首先学习一元回归分析、相关分析、多元回归分析、数量化理论I等知识。

根据最小平方法,使用Excel求解y=a+bx中的a和b。那么什么是最小平方法?

分别从散点图的各个数据标记点,做一条平行于y轴的平行线,相交于图中直线(如下图)

平行线的长度在统计学中叫做“误差”或者‘残差”。误差(残差)是指分析结果的运算值和实际值之间的差。

接这,求平行线长度曲平方值。可以把平方值看做边长等于平行线长度的正方形面积(如下图)

最后,求解所有正方形面积之和。确定使面积之和最小的a(截距)和b(回归系数)的值(如下图)。

使用Excel求解回归方程;“工具”→“数据分析”→“回归”,具体操作步骤将在后面的文章中具体会说明。

三、回归分析案例

接着上节的回归分析的目的,我们来根据一个二手车的实例来继续说回归分析。

二手车价格的决定因素有:空调类型有无附加品(TV导航导航SR、天窗、空气囊LD TV AW)  行驶距离,颜色车检剩余有效月数、评分拍卖会地点等。

在这14个因子(说明变量)中,最影响价格(目标变量)的是什么?最不影响价格的是什么?通过定量求出影响度,然后根据多个因子(说明变量)预测二手车价格(目标变量)。

可以用方程2表示。二手车价格“这个目标变量数据,既(“空调类型(AC WC)”  “有无TV导航”、    、“行驶距离”、  。车检剩余有效月数”, “评分”)。

混合模型

混台模型是指因子巾既包含定性数据也包含定量数据的模型。在混台模型中.把“空调”、 “TV导航”等定性因子叫做项目,把数据群(空调的“AC”,“WAC”,TV导航的“有”、“无”)叫做类别。

接下来,根据表l进行回归分析。

这节我们主要告诉大家回归分析前,我们需要先根据自己的思维来了解分析,把这些需要注意的先分析出来,这样对我们接下来的回归分析有很大的帮助。

四、Excel回归分析需要注意的事项

经过上节,我们了解了回归分析前,我们要先通过思维分析出来需要注意的事项,那么今天接着上一节的课来了解下Excel回归分析需要注意的事项。包含的定性数据,不能直接使用Excel分析,需要将其转换成虚拟变量(也叫O,1数据)。例如,  “空调(AC、WAC)”的数据,“AC”用“1”,“WAC"用“O”表示。同样地,“导航(有导航、无导航)”的数据,  “有导航”用“1”,  “无导航”用“O”表示。表1是根据这种方法转换的(0,1)数据表。

直接使用Excel的对表1进行回归分析时,运算结果不理想。理由如下;

以“导航”为例,各行

“有导航”+“无导航”=1

此式成立。把公式变形,

“有导航”=1-“无导航”

所以“有导航”是“0”或是“1”,由“无导航。自动决定。

线性代数中发生秩(矩阵秩)亏时,不能正确求出必要的逆矩阵。因此也不能求出回归系数。

由于上述原因,进行回归分析时,需要从各个项目中删除—列因子(表2)。

根据表2的数据进行回归分析,操作步骤如下:

1、“工具”一“数据分析”

2、在弹出的“数据分析”对话框中选择“回归”,单击“确定”(图1)。

3、点击“回归”对话框的“Y值输入区域”,选择“二手车价格”的列数据,包括项目名称;接着点击“X值输入区域”,选择从“AC”到“中国、四国、九州”的区域,包括项目名称;选中。标志”,单击“确定”。(图2)

系统弹出错误信息,不能进行回归分析(图3)。这是因为Excel回归自由度的最大上限是16(P62小知识)。这里的回归自由度是22,因此不能进行回归分析。

统计学中经常出现“自由度”,即有效信息的数量。

前面已经提到,在Excel的回归分析中,回归自由度的最大上限是16。回归自由度在(多重)回归分析、数量化理论|、混合模型中具有不同意义。表3是对回归自由度的不同意义的总结。

五、分两次进行回归分析

我们在前面提到过,当回归自由度在17以上时,Excel无法进行回归分析,那么就需要分两次进行回归分析。第一次,把“空调”、“TV导航”、“导航”、“SR”、“天窗”、“空气囊”、“LD”、“TV”、“AW”作为说明变量(表1),第二次,把“颜色”、“拍卖会地点”、“行驶距离”、“车检剩余有效月数”、“评分”作为说明变量(表2),目标变量都是“二手车价格”。

对表1、表2进行回归分析。回归分析的结果分别如表3、表4所示(具体操作步骤将在下一节详细说明)。

数据分析咨询请扫描二维码

客服在线
立即咨询