【实验目的】
生活中经常会见到两种事物直接存在一定的关系,当数据比较多的时候,我们凭肉眼并不能看出两者之间的关系。这时候就需要我们借助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$$万元