在Excel中快速计算一元立木材积及金额
在Excel 中快速计算一元立木材积及金额
洪湖东荆河管理分局 刘大庆 邵艳琼
一元立木材积表的应用在某些地方还较普遍。分局在卖树时,要算树木的材积,由于数据多,许多人苦于找不到公式,而只能借助于《一元立木材积表》手工查询,还要计算求和。即使是抽排,要计算的数据也不少。这样既费时间,又让人觉得枯燥。如果在计算中以整片树的平均粗度来计算材积,则误差在-12%左右。因为从材积表中,可以得出规律:直径越大,其材积的增量也越大,并不是成正比例的增加,而是呈上升的抛物线趋势增加。笔者在实践中利用Excel 巧编公式,实现了一元立木的材积和金额计算电算化,其速度之快是可想而知的。下面为大家介绍一下,以供参考。
在计算林木的材积时,先要量出其直径,有直接用特制的钢卷尺量直径的,这样可以直接读出直径;也有的由于需要的精度不是太高,或买不到特制的钢卷尺,而采用裁缝师傅常用的皮尺量出周长,再除以3.14得到直径。由于后一种情况编公式时复杂些,这里以周长16除以3.14为例来计算杉原条的材积及金额(此公式可直接用于前一种情况)。
第一步:如图示,在第一页页面外按《杉原条材积表》(2003年湖北省平原湖区适用)输入相关的数值;第二步:在第一页中打出相应的表头,如图《杉原条材积计算示例》;第三步:在b3单元格中输入周长;第四步:编公式。1、算直径。在直径一列c3单元格中输入公式“=b3/pi()”,并设置单元格格式,保留一位小数,其中pi()的值是圆周率3.1415926 ;2、算材积。此过程是利用公式根据林木的直径在打出的《杉原条材积表》中查询材积。在材积一列d3单元格中(英文状态)输入公式“=index($g$3:$p$48,trunc(round(c3,1))-4,right(trunc(round(c3,1)*
10),1)+1)”。式中函数的意义在Excel 函数表中(或常用工具栏中
的fx 中)可找到详细用法。式中index 表示从单元格区域中返回一个值或对其中数值的引用,其中有三个参数,分别是index(区域, 行, 列) ,$g$3:$p$48表示绝对引用g3单元格至p48单元格组成的矩形区域中的数值,即材积表中的材积数据;trunc(round(c3,1))-4的结果得到直径代表的行。其中函数round(numble,num_digits)表示按指定的位数(num_digits)对数值(numble)进行四舍五入,如round(5.09,1)的结果是5.1,函数trunc(numble)表示把数值(numble)截为整数, -4表示将直径的整数位减4后与矩形区域中的行对应,如直径为5厘米,则对应于5-4=1行,即为材积表中的第一行;right(text,num_chars)表示提取字符串(text)最右边的若干个(num_chars)字符,如right(5.1,1)的结果是1,+1同样表示将直径的小数位代表的列与区域中的列对应。另外,在公式right(trunc(round(c3,1)*10),1)+1)中*10的作用在于对小数位是0的数,如19.0,电脑内存中只能记忆为9,,因而提取到的数是9,不是0。通过编公式后,就可以很快查询出材积,再通过粘贴公式即实现了材积的快速计算。
下面谈谈金额的计算。我们在卖树订单价时,一般按树的大小分类订价。如φ在5cm —10cm 时,300元/方,φ在10.1cm —15cm 时,330元/方,φ在15.1cm —20cm 时,380元/方,φ在20.1cm —25cm 时,400元/方,φ在25.1cm 以上时,450元/方。在编公式时,要根据直径的大小来确定单价的值。可以在单价一列(以c3单元格中直径为例) 中用if 语句输入公式 “=if(c3
再用材积乘单价可得到金额。