excel线性回归分析函数（用excel做数据回归分析）

【实验目的】

  生活中经常会见到两种事物直接存在一定的关系，当数据比较多的时候，我们凭肉眼并不能看出两者之间的关系。这时候就需要我们借助Excel的线性回归分析来查看。

【实验原理】

  回归分析的结果有多种可以查看的结果，本实验主要通过Excel的回归计算将结果通过图和文字展示。

【实验环境】

   Office 2010

【实验步骤】

回归分析

“Excel线性回归分析”表，假定以某高校某班级2005至2018年每届毕业班的高等数学平均分统计数据资料为例，预测年份与高等数学平均分之间的关系。以年份为自变量，以高等数学平均分为因变量做回归分析，原始数据如图所示。

具体操作步骤如下：   

绘制散点图。在原始数据所在的工作表中，选择A1：B14单元格区域，转到”插入“选项卡，在”图表“选项组中单击”散点图“按钮，单击即可绘制出散点图。如图所示

散点图展示

添加趋势线。选择绘制出的散点图，在出现的”图表工具“标签下转到”布局“选项卡，在”分析“选项组中单击”趋势线“按钮，在弹出的如图所示的下拉列表中选择其他趋势线选项“。

随即在工作表右侧弹出如图所示的”设置趋势线格式”窗格。在设置趋势线窗格中的“趋势线选项”中选择“线性”；勾选“显示公式”和“显示R平方值”两个复选框。

设置完毕后即可得到所需的趋势线及其参数，回归结果如图所示

分析回归结果。如图可知，趋势线的公式为y=-0.8989+2064.4,反应了两个变量之间的强弱关系，说明时间每增加一年，该高校毕业班的高等数学平均分就减少0.989分，而拟合优度R²=0.1505说明了这个公式能够解释数据的15.05%，说明该公式的解释力度并不是很强。

数据分析

切换到sheet2表格，然后输入如下数据，点击“数据”选项卡下的“数据分析”选项。弹出对话框如图,选择“回归”。如图所示

X值输入区域中选择为$B$2:$B$11，Y值输入区域为$C$2:$C$11，输出区域选择为$B$15:$C$22，最后确定，如图所示

结果如图所示

回归统计部分给出了判定系数R²、调整后的系数R²、估计标准误差等；

  方差分析表部分给出的显著水平F值表明回归方程是显著的   最下面的一部分是a=395.567,b=0.895836。以及参数a、b的标准差、t检验的统计量、p-值下限95%和上线95%给出了参数a、b的置信区间。比如，我们有95% 的把握确信，a在21.04844和58.64964之间，b在0.650009和1.1416632之间。

  除表中输出结果之外，还可以根据需要给出残差图、线性拟合图等。

  所以，该实验中得到的回归方程为：$$y_c=395.567+0.895836x$$,回归估计标准误差为：S=126.6279

  当生产性固定资产x=1100万元时，工业总产值为：$$y_c=395.567+0.895836x100=1380.9866$$万元