利用EXCEL进行水稻二化螟孵化高峰期的预测预报

1. 试验内容
为做好水稻二化螟病虫害的预测预报工作，江苏武进植保站从1956年到1961年连续6年记录每年2月下旬至3月下旬旬平均气温累加值（x，旬.℃），以及二化螟越冬幼虫4月份化蛹高峰期（Y，以4月1日为0），得到12对数据（表5-1）。现已知1962年2月下旬至3月下旬的旬平均气温为40℃，请预测二化螟在什么时候达到孵化高峰。

 

2. 试验目的
（1）寻找水稻二化螟化蛹高峰期与旬平均气温之间的关系，计算出温度与孵化高峰期之间的一元直线回归方程。
（2）利用此方程对二化螟的发生情况进行预测预报。如1962年旬平均气温为40℃，请预测二化螟蛾盛发期时间。

3. 结果分析
3.1 建立Excel文件
打开Excel软件，创建新的数据文件，数据输入样式如下图所示。

 

3.2 作散点图，明确两个变量间的关系

 

散点图得到的结果：由图5-1可知，水稻二化螟化蛹孵化高峰期与气温存在着直线关系，是一种负相关关系，即气温升高，孵化时间变短。因此，下一步分析是求出二化螟化蛹孵化高峰期与气温之间的直线方程，寻找之间的数量关系。

 

3.3 计算直线回归方程

问题1： 什么是回归直线？
在x,y的直角坐标平面上可以作出无数条直线，而回归直线是指所有直线中最接近散点图中全部散点的直线。
设样本直线回归方程为：

其中，a是α的估计值，b是β的估计值。α、β分别是总体直线回归方程的回归截距和回归系数。

 

问题2：回归直线如何计算出来？如何利用Excel来绘制？

方法有3种。第一种方法有助于了解回归直线方程的原理，第二种方法用于只需要计算出方程的情况，第三种方法可在作散点图的基础上，在图上直接绘制出直线，并将方程公式显示在图上。建议学习者掌握第三种。

（1）方法一 直接利用公式计算
回归直线在平面坐标系中的位置取决于a、b的取值，为了使能最好地反应y和x两变量间的数量关系，根据最小二乘法，a、b应使回归估计值与观测值的偏差平方和最小，即：

本案例计算结果为：
a=96.4775, b= -2.2747
将a、b值代入公式5-1即得一元回归直线方程为： y= -2.2747 x + 96.4775。

 

（2）方法二 利用Excel中的统计函数计算
分别利用截距函数“INTERCEPT（因变量Y值所在单元格区域 , 自变量X值所在单元格区域）”计算截距a值；利用斜率函数“SLOPE（因变量Y值所在单元格区域 , 自变量X值所在单元格区域）”计算回归系数b值。
在Excel工作表的单元格中输入“=INTERCEPT（C3:C8,B3:B8）”……(不含双引号)
在Excel工作表的单元格中输入“=SLOPE(C3:C8,B3:B8）”……(不含双引号)
按回车键后，分别的得到 a=96.4775, b= -2.2747
则一元回归直线方程为： y= -2.2747 x + 96.4775

 

（3）方法三 利用Excel统计图直接显示公式、决定系数，绘制回归直线。

 

3.4 回归关系测验
按照以上计算和Excel操作，只要有两组数据，不管两个变量之间是否存在因果关系，或者这种关系是否紧密，都可以计算出一个一元直线方程，并绘制出一条直线。因此，需要对此方程进行显著性测验，即对两个变量之间是否存在显著的因果关系进行分析。
（1）测验方法
测验方法有3种：方差分析法、t测验法（对回归系数测验）、相关系数（r）测验法。

 

（2）回归方程假设测验的Excel操作步骤
用Excel中回归分析功能模块对回归关系进行分析，该模块提供了方差分析和t测验两种方法的数据分析结果。

具体操作如下：

 

 

4. 结论
根据以上统计分析，旬平均气温累加值（X,旬.℃）和二化螟蛾盛发期（Y）之间确实存在紧密的因果关系，可用一元回归方程y = -2.2747 x + 96.4775描述，即温度每提高1℃，孵化盛期平均提前2.2天。

5. 预测预报
可用已知的x值代入方程预测 y 值。1962年气温为40℃，即X=40，代入下列方程得：
y = -2.2747 x 40+ 96.4775=5.5（天）
因4月1日记为0天，水稻二化螟的孵化盛期约在4月5日到4月6日之间。