Excel函数在农业中的巧用

前言：本站为你精心整理了Excel函数在农业中的巧用范文，希望能为你的创作提供参考价值，我们的客服老师可以帮助你提供个性化的参考范文，欢迎咨询。

1材料与方法

主要选用的资料山东莒南报送的土壤常数和常用土壤湿度持水量表都是出自《气象电码技术问题汇编》[1](1988年1月1日00∶00开始执行),根据国家气象局颁发的《气象电码》和有关电码编报文件规定精神,结合山东省气象情报服务需要和省局以往下发有关编报规定文件,综合整理编写的。其中,莒南报送的土壤常数是根据莒南当地实测确定;常用土壤湿度持水量表则是根据《气象电码技术问题汇编》[1]中“不同类型土壤的土壤湿度与占田间最大持水量关系表”中的数据结合莒南实地情况查算出来。查用了农业气象测墒规范、常用土壤湿度持水量表及莒南气象局报送的土壤常数等资料,主要是excel函数综合运用,表格查算运用IF语句的多层套用的方法。

2程序设计方法的实现

用Excel提供的四则运算函数编写程序完成农业气象测墒中对土壤样本数据的基本计算,用逻辑函数编写程序实现对土壤湿度持水量的查表计算工作。

2.1程序设计简介农业气象中的土壤相对湿度计算分为2个部分:第1部分是固定地段的20个土壤样本计算;第2部分是全县普查的32个土壤样本计算。这2个部分主要用到了+、-、×、÷4种运算,对应的函数有SUM、PRODUCT。如加法运算SUM(B2,C2),那么相应的减法运算为SUM(B2,-C2),同样乘法运算为PRODUCT(E2,F2),除法运算为PRODUCT(E2,1/F2)。2个部分都用到了查表计算,其中,第2部分要查表格的数据较多,也是运用函数运算的难点,主要是涉及到算法问题。用Excel设计表格数据的查询是该研究主要说明的。

2.2程序设计思路及方法的实现程序设计分3个阶段:第1阶段,把固定地段和全县普查中单纯涉及到+、-、×、÷4种运算的部分设计好;第2阶段,设计土壤持水量的表格数据查询;第3阶段,代入具体数据测试计算结果的准确度。

2.2.1含水重、干土重、土壤含水量求和、平均等计算的实现。第1阶段的设计完成了含水重、干土重、土壤含水量求和、平均的计算。这个阶段算法上比较简单,主要遇到小数的四舍五入方面的问题。刚开始带入初始数据验证,计算结果总是有个别数据有零点几的误差,经过对计算过程的分析,发现产生误差主要原因在于把取1位有效小数跟四舍五入到小数点后1位的概念等同了,如1.24取1位有效小数显示是1.2,实际计算中仍保留没有显示的0.04,四舍五入到小数点后1位也是1.2,但是2个显示是1.2的数值在大小上是不相等的,很明显1.24>1.2。计算过程中,2个取1位有效小数的1.24相加[1.2(4)+1.2(4)=2.4(8)]显示是2.4,但是计算结果四舍五入的话就变成了2.5。由此可知,解决误差的方法就是每一步计算结果都要四舍五入。

2.2.2土壤湿度持水量查表计算程序设计方法。第2阶段即全县普查中的土壤湿度持水量查表计算的程序设计,是各种Excel函数的综合运用,其中逻辑函数的运用起到了非常重要的作用。设计思路是通过IF函数的多层套用来完成对表格数据的选择计算,如IF(F11>60,(IF(F11>90,“优秀”),“合格”),“不合格”),说明为F11≤60时显示“不合格”,当F11>90时显示“优秀”,否则(60<F11≤90的情况)为合格。设10或20cm土壤含水量平均值为a,a对应的土壤湿度持水量为b,对a向下取为最近的整数设为A,取整过程对应的函数为INT(a),A对应的土壤湿度持水量为B,B0为山岭、丘陵、平原、湖洼中的一类土壤湿度持水量数组的第1个数值(为15或17或22),A0为B0对应的土壤含水量平均,B、B1为表1中显示的2个邻近的土壤湿度持水量,且B1>B。土壤湿度持水量b计算方法是:b=B+(a-A)×(B1-B)(1)式中,B、(a-A)部分很容易用函数表达出来,但是(B1-B)部分是随B变化的数值,用函数比较难表达,是一个难点。下面主要讨论(B1-B)的取值问题。通过对表1中土壤湿度持水量大小变化的分析,发现对应土壤湿度持水量后面数值减前面数值的差值(B1-B)变化是有规律的。

如山岭、丘陵对应土壤湿度持水量后面数值减前面数值差值,即(B1-B)都是5,平原的前后数值差值(B1-B)依次为5、4、4、5、4、4、5、4、5、4、4、5、4、4、5、4、4、5、4,湖洼的前后数值差值(B1-B)依次为4、4、3、4、4、3、4、4、4、3、4、4、3、4、4、3、4、4、4、3、4。那么由10cm或20cm土壤含水量平均数值a,查表算得的土壤湿度持水量的算法如下。2.2.2.1山岭、丘陵。b=B+(a-A)×(B1-B)=A×5([a-A]×5=a×5(2)2.2.2.2平原。先分析其前后数值差的规律5、4、4、5、4、4、5、4、5、4、4、5、4、4、5、4、4、5、4,可以看出(B1-B)的差值是4或5,即是4+0或4+1。设单下划线的差值5、4、4为有规律数段,双下划线的5、4少1个数,为无规律数段。为了分析方便,先假定(B1-B)差值数列都是由5、4、4这样3个数的小段组成,如5、4、4、5、4、4、5、4、4…,由于(B1-B)差值数列以3个数值为1个循环出现差值5,那么出现4+0或4+1的情况就能用算式4+INT[(B-B0)/3]表示(INT为向下取整函数)。如果都是5、4、4的数段那么算法就容易多了,但是出现了5、4使得原差值数列循环变的无规律,为了解决这个问题,可以把原差值数列分为2个部分5、4、4、5、4、4、5、4和5、4、4、5、4、4、5、4、4、5、4,那么每个部分又都由有规律的数段组成了,用分情况讨论的方法分别编写算法式子就可以解决问题。由于5、4和5、4在原差值数列中的位置不一样,5、4出现在原差值数列中间,其后面一个数为5而不是4,所以归为无规律的数段,5、4在原差值数列最后,可以看作是5、4、4前面一部分,所以归为有规律数段。以平原的土壤湿度持水量查算为例:不规律的数段5、4中的第1个的差值5(即57-52=5)出现在A=13点处。下面以13为界线分情况讨论。(1)当a<13时,A取数值4,那么A=4对应的土壤湿度持水量B为17,则:X1=[17(4×(A-4)](INT[(A-4(2)/3],X2=[17(4×(A(1-4)](INT[(A(1-4(2)/3]。土壤湿度持水量的算法:b1=B((a-A)×(B1-B)=X1+(a-A)×(X2-X1)。(2)当a≥13时,A取数值13,那么A=13对应的土壤湿度持水量B为57,则:X3=[57+4×(A-13)](INT[(A-13)/3],X4=[57(4×(A(1-13)](INT[(A(1-13)/3]。土壤湿度持水量的算法:b2=B((a-A)×(B1-B)=X3((a-A)×(X4-X3)。用逻辑函数的IF语句来反映讨论的情况有IF(A<13,B1,B2)具体函数为:IF(a<13,(4+INT((A+1-4+2)/3)-INT((A-4+2)/3))×(a-A))+(17+4×(A-4))+INT((A-4+2)/3),(4+INT((A+1-13)/3)-INT((A-13)/3))×(a-A)+(57+4×(A-13))+INT((A-13)/3))。工作表中的函数应用举例见图2。2.2.2.3湖洼。先分析差值的规律4、4、3、4、4、3、4、4、4、3、4、4、3、4、4、3、4、4、4、3、4。数值的循环情况跟平原的相似,只是出现了2处不规律的数段,把上面的差值数列分为了3段4、4、3、4、4、3、4、和4、4、3、4、4、3、4、4、3、4、和4、4、3、4这样每段的差值又有规律了。这个就要求分3种情况讨论分别编写算法公式。分别是a<15、15≤a≤25、a≥25。

算法编写中用到IF函数的2层套用就可以了.2.2.3抄录计算结果程序设计。第3阶段为了方便抄录计算结果,可以给个别工作表单元格设定颜色,这样能防止看花眼抄错行。为验证计算的准确度,带入之前人工计算过的3套初始数据。计算机通过Excel函数计算出来的结果与人工算的结果完全一致,这说明用Excel工作表来计算农业气象测墒数值是可行的。下面是设计好的Excel工作表格,其中包含了一次具体的计算过程。(1)固定地段的Excel工作表格(2)全县普查的Excel工作表格(图5)。使用该Excel计算工作表的时候,只需要输入湿土重和干土重就能得到需要的含水量、干土重、土壤含水量、土壤持水量等数据结果。

3结语

Excel在农业气象测墒计算中的应用减少了工作时间,提高了工作效率,计算结果精确度为100%。对土壤湿度持水量表的查算涉及到数据的选择处理用到了IF语句的多层套用,这实际体现了分情况讨论的思想。用Excel中函数提供的IF语句来设计表格数据查算实现起来不困难,并且Ex-cel工作表格界面简单直观容易被县局工作人员学习接受。综上,Excel在农业气象测墒计算中的应用,特别是运用Ex-cel来设计表格数据查算有一定的推广价值。