Question

如何用EXCEL做回归分析?

X=727 751 780 811 832 862 926 979 1015 1062
Y=30.4 31.7 29.7 29.9 31.3 38.5 40.0 44.8 67.47 71.9
在EXCEL中插入图标，XY散点图后右键点击添加趋势曲线，选择线型的趋势曲线得到公式是对的，但R2比较小，拟核程度不高。但选择多项式2阶趋势曲线得到的公式R2较大，拟核程度高，但带X进去算相差较大这是为什么？
我是想问在上面的例题中为什么采用二阶的趋势函数后，二阶的趋势函数不对，随便带个X值都与Y相差很大，麻烦高手用EXCEL做一下，谢谢！

Answer 1

在日常数据分析工作当中，回归分析是应用十分广泛的一种数据分析方法，按照涉及自变量的多少，可分为一元回归分析和多元回归分析；按照自变量和因变量之间的关系类型，可分为线性回归分析和非线性回归分析。

回归分析的实施步骤：

1）根据预测目标，确定自变量和因变量

2）建立回归预测模型

3）进行相关分析

4）检验回归预测模型，计算预测误差

5）计算并确定预测值

我们接下来讲解在Excel2007中如何进行回归分析？

一、案例场景

为了研究某产品中两种成分A与B之间的关系，现在想建立不同成分A情况下对应成分B的拟合曲线以供后期进行预测分析。测定了下列一组数据：

二、操作步骤

1、先绘制散点图：具体步骤是选中数据，插入—>图表—>散点图

2、在散点图的数据点上右键—>添加趋势线

3、在弹出的选项框的选项中选择公式和相关系数等，这样就以得到拟合的直线

在图中我们可以看到，拟合的回归方程是 y = 0.223x + 9.121 ，R² = 0.982

附：R2相关系数取值及其意义

我们进一步使用Excel中数据分析的回归分析提供更多的分析变量来描述这一个线性模型

4、选中数据—>数据—>数据分析—>回归

注：本操作需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，可以参考该专题文章的第一篇《用Excel进行数据分析：数据分析工具在哪里？》。

Answer 2

用EXCEL做回归分析主要有图表法和函数法：

1、图表法：
选择参与一元线性回归两列数据（自变量x应在应变量y的左侧），插入图表，选择散点图。
选择图表中的数据系列，右击，添加趋势线，点击“选项”选项卡，勾选“显示公式”、显示R平方值。
注意显示出的R2值为R的平方，需要用SQRT()函数，计算出R值。

2、函数法
若X值序列在A1:A100单元格，Y值序列在B1:B100单元格，
则线性公式的截距b
=INTERCEPT(B1:B100,A1:A100)
斜率k
=SLOPE(B1:B100,A1:A100)
相关系数R
=CORREL(A1:A100,B1:B100)
或
=CORREL(B1:B100,A1:A100)

上述两种方法都可以做回归分析，同时结合图表和函数会取得更满意的效果。

Answer 3

拟合程度高，主要是前几期的数据离散性小（30.4 31.7 29.7 29.9 31.3 38.5），使用二阶做趋势线，前几期的数据拟合的好，残差减少许多，但是用线性的话，前几期就拟合不好，残差比较大。

我感觉EXCEL给出的公式不正确，用给出的公式回推Y值，都相差很大。

Answer 4

以Excel2010为例。
1、“开发工具”选项卡 中单击“加载项”组中的“加载项”按钮，打开“加载宏”对话框。如下图。勾选 “分析工具库”。
2、“数据”选项卡中“分析”组中的“数据分析”按钮，打开“数据分析”对话框。如下图。单击“回归”选项。

Answer 5

excel只能做线性回归，如果非线性回归，先转化为线性
用linest函数
比如回归y=ax^2+ba+c
先将x^2放在a列，x放b列，y放c列，假设有12组数
选中3个单元格，输入=linest（c1：c12，a1:b12，），是数组操作，所以得按F2，再按CTRL+SHIFT+ENTER就ok了
不懂的话，再看下该函数的帮助文件