一文学会如何用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所示(具体操作步骤将在下一节详细说明)。