【实验目的】

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

【实验原理】

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

【实验环境】

   Office 2010

【实验步骤】

回归分析

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

excel线性回归分析函数(用excel做数据回归分析)(1)

具体操作步骤如下:   

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

excel线性回归分析函数(用excel做数据回归分析)(2)

散点图展示

excel线性回归分析函数(用excel做数据回归分析)(3)

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

excel线性回归分析函数(用excel做数据回归分析)(4)

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

excel线性回归分析函数(用excel做数据回归分析)(5)

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

excel线性回归分析函数(用excel做数据回归分析)(6)

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

数据分析

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

excel线性回归分析函数(用excel做数据回归分析)(7)

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

excel线性回归分析函数(用excel做数据回归分析)(8)

结果如图所示

excel线性回归分析函数(用excel做数据回归分析)(9)

回归统计部分给出了判定系数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$$万元