Excel函數(shù)用于解決水文流量計算中尾數(shù)取舍問題的簡單方法

李 偉，李 弘，彭學(xué)成

（四川省成都水文水資源勘測局，四川 成都 611130）

0 引言

流量計算是水文站水文測驗資料收集中非常重要的一項基本工作。傳統(tǒng)方法進行流量計算需用計算器一步一步計算平均水深、部分斷面面積、平均流速、部分流量，然后匯總統(tǒng)計出實測流量。中間還需要對各步計算結(jié)果進行有效位數(shù)和尾數(shù)人工取舍。故1份實測流量的整個計算過程是非常復(fù)雜和耗時的。在電腦普及化程度非常高的今天，各個水文站均配置了電腦，故筆者考慮采用Excel軟件對實測流量進行計算。Excel作為一個常用的計算機辦公軟件，其計算的功能非常強大，可以為我們進行實測流量計算提供簡便的方法。但在應(yīng)用時我們發(fā)現(xiàn)了一個問題，就是Excel軟件的尾數(shù)取舍是常規(guī)的四舍五入，而并非水文規(guī)范要求的尾數(shù)取舍方式。為了方便實測流量計算，筆者對Excel軟件進行水文流量計算中的尾數(shù)取舍問題進行研究和試驗，找到一種簡單便捷的方法。

1 流量計算的尾數(shù)取舍方式

規(guī)范文件《數(shù)值修約規(guī)則與極限數(shù)值的表示和判定》（GB/T8170-2008）[1]和《水文資料整編規(guī)范》（SL247-2012）對流量計算的尾數(shù)取舍方式進行了規(guī)范。尾數(shù)取舍方式[2]如下：“取用精度位數(shù)后一位數(shù)字，采用‘四舍六入’方法取舍，即取用精度位數(shù)后一位數(shù)字小于五者則舍，大于五者則入，等于五時若其后有非零尾數(shù)仍入，無非零尾數(shù)則視取用的末位數(shù)字的奇偶取舍，為奇則入，為偶則舍?！?/p>

2 使用Excel函數(shù)實現(xiàn)流量計算的尾數(shù)取舍

2.1 采用 Excel函數(shù)

ROUND函數(shù)和MOD函數(shù)，關(guān)于這2個函數(shù)的介紹和用法可以參看本文參考文獻[3]或者Excel軟件的幫助文檔。

2.2 實現(xiàn)流量計算的尾數(shù)取舍

由前節(jié)所述水文流量“四舍六入”取舍方法可知，這種取舍方法與Excel軟件中ROUND函數(shù)采用的常規(guī)的“四舍五入”取舍方法是有差異的。差異如表1所示。

由上圖分析得知2種取舍方法只有在一種情況下才存在取舍差異。即取用精度位數(shù)后一位數(shù)字等于5，且其后無非零尾數(shù)，且取用的末位數(shù)字為偶數(shù)時，水文流量“四舍六入”法為“舍”Excel“四舍五入”法為“入”。由此分析，保留兩位小數(shù)的水文流量“四舍六入”的取舍方法可采用以下函數(shù)式實現(xiàn)。

式中，n為保留小數(shù)位數(shù)，與ROUND函數(shù)參數(shù)定義相同，比如1為保留1位小數(shù)，0位為保留個位數(shù)，-1為保留到十位數(shù)等。

本文以保留2位小數(shù)的取舍示例如圖2。

圖2 2種取舍方法保留2位小數(shù)的取舍示例

函數(shù)式解釋：

第一部分為ROUND（A2，n）。即將A2單元格數(shù)據(jù)按保留n位小數(shù)進行常規(guī)Excel“四舍五入”法取舍。如圖2例中各行原始數(shù)據(jù)常規(guī)Excel“四舍五入”為第三列結(jié)果。

第二部分為（MOD（A2*10^n，2）=0.5）/10^n MOD（A2*10^n，2）表示先求A2單元格數(shù)據(jù)乘以10的n次方再除以2的余數(shù)。如圖2例中各行原始數(shù)據(jù)求余數(shù)結(jié)果分別為1.41、0.62、1.50001、0.5、1.5。（MOD（A2*10^n，2）=0.5）表示判斷上一步求余數(shù)結(jié)果是不是等于0.5，如果等于0.5返回1，否則返回0。如圖2例中各行原始數(shù)據(jù)僅1.145返回值為1，其余為 0。（MOD（A2*10^n，2）=0.5）/10^n表示將上一步返回結(jié)果除以10的n次方。如圖2例中各行原始數(shù)據(jù)僅1.145返回值為0.01，其余為0。所以第二部分表示僅當取用精度位數(shù)后一位數(shù)字等于5，且其后無非零尾數(shù)，且取用的末位數(shù)字為偶數(shù)時，第二部分返回結(jié)果為0.01，其余為0。

表1 2種取舍方法的差異分析

最后將第一部分結(jié)果減去第二部分結(jié)果得到最終結(jié)果。如圖2例中各行原始數(shù)據(jù)僅1.145返回值為第一部分結(jié)果減去0.01，其余為第一部分結(jié)果。表示僅當取用精度位數(shù)后一位數(shù)字等于5，且其后無非零尾數(shù)，且取用的末位數(shù)字為偶數(shù)時，最終結(jié)果會將第一部分結(jié)果減去0.01，即將第一部分結(jié)果中五入的0.01舍去，其余情況采用第一部分結(jié)果。從而得到了規(guī)范要求的水文流量“四舍六入”取舍方法。

3 使用Excel函數(shù)實現(xiàn)流量計算中各分步計算

3.1 采用 Excel函數(shù)

IF函數(shù)，關(guān)于這2個函數(shù)的介紹和用法可以參看本文參考文獻[3]或者Excel軟件的幫助文檔。

3.2 水深計算

規(guī)范文件《水文資料整編規(guī)范》（SL247-2012）對水深的取用精度方式進行了規(guī)范[2]。“不小于100 m，記至1 m；小于100 m，不小于5 m，記至0.1 m；小于5 m，記至0.01 m”。所以對水深計算結(jié)果采用以下函數(shù)式實現(xiàn)精度取舍。

3.3 斷面面積計算

規(guī)范文件《水文資料整編規(guī)范》（SL247-2012）對斷面面積的取用精度方式進行了規(guī)范。“取三位有效數(shù)字，小數(shù)不過二位”?？梢员硎緸椤皵嗝婷娣e數(shù)值不小于100，記至1；小于100，不小于10，記至0.1；小于10，記至0.01”（由于筆者所在水文站斷面面積一般均小于1000 m2，故不考慮斷面面積不小于1000 m2的情況）。所以對斷面面積計算結(jié)果采用以下函數(shù)式實現(xiàn)精度取舍。

3.4 流速計算

規(guī)范文件《水文資料整編規(guī)范》（SL247-2012）對流速的取用精度方式進行了規(guī)范?！安恍∮? m/s，取三位有效數(shù)字；小于1 m/s，取二位有效數(shù)字，小數(shù)不過三位”?？梢员硎緸椤傲魉贁?shù)值不小于10，記至0.1；小于10，不小于0.1，記至0.01；小于 0.1，記至0.001”（由于筆者所在水文站流速一般均小于100 m/s，故不考慮流速不小于100 m/s的情況）。所以對流速計算結(jié)果采用以下函數(shù)式實現(xiàn)精度取舍。

3.5 流量計算

規(guī)范文件《水文資料整編規(guī)范》（SL247-2012）對流量的取用精度方式進行了規(guī)范?！叭∪挥行?shù)字，小數(shù)不過三位”?？梢员硎緸椤傲髁繑?shù)值不小于10000，記至100；小于10000，不小于1000，記至10；小于1000，不小于100，記至1；小于100，不小于1，記至0.1；小于10，不小于1，記至0.01；小于1，記至0.001”（由于筆者所在水文站流速一般均小于100000 m3/s，故不考慮流速不小于100000 m3/s的情況）。所以對流量計算結(jié)果采用以下函數(shù)式實現(xiàn)精度取舍。

流量計算中各分步計算示例如圖3。

流量計算中各分步計算函數(shù)式（2）（3）（4）（5）看似很長和復(fù)雜，其實均為IF函數(shù)的嵌套使用，中間全部是插入的函數(shù)式（1），所以各個函數(shù)式都很簡單易用。將各個函數(shù)式用于實測流量計算表中，通過對同類計算函數(shù)式的復(fù)制粘貼就可以對流量中各行列數(shù)據(jù)進行精度取舍，最終計算出符合規(guī)范要求的實測流量值。

圖3 流量計算中各分步計算示例

4 使用Excel實現(xiàn)流量計算可能出現(xiàn)問題的探討

由于Excel中浮點運算可能會給出不準確的結(jié)果[4]，本文所采用的計算函數(shù)式進行精度取舍后偶爾還是會出現(xiàn)偏差。原因是Excel中的數(shù)值均是以二進制存儲，而浮點數(shù)（即小數(shù)）以二進制存儲時，大多數(shù)為無限循環(huán)數(shù)，而且Excel中只能存儲15位精度之內(nèi)的浮點數(shù)，所以部分小數(shù)計算結(jié)果就存在尾數(shù)取舍誤差。比如計算結(jié)果是0.5，可能在有些情況下就存儲為0.500000000000028，而1.5有些情況下卻存儲為1.49999999999999。在這種情況出現(xiàn)時本文函數(shù)式（1）的計算結(jié)果就會出現(xiàn)偏差。這種情況出現(xiàn)時，函數(shù)式（1）中的MOD（A2*10^n，2）就會出現(xiàn)本該等于0.5的卻由于Excel中浮點運算誤差而不等于0.5，故筆者考慮將MOD（A2*10^n，2）改為 ROUND（MOD（A2*10^n，2），10），這樣對MOD（A2*10^n，2）的結(jié)果進行小數(shù)10位的取舍，消除像0.500000000000028這種Excel中浮點運算誤差。但這樣做也會導(dǎo)致本來計算結(jié)果真的為1.49999999999999的數(shù)據(jù)出現(xiàn)精度取舍偏差。但筆者以為后面這種偏差出現(xiàn)的幾率比Excel中浮點運算誤差引起偏差的幾率更低（關(guān)于哪種情況的幾率更低筆者還未能進行證明，希望各位讀者朋友能夠幫忙證明，或者找到更好的方法），故筆者采用這種改良過的函數(shù)式（1）。即如下函數(shù)式（6），用以替代函數(shù)式（2）（3）（4）（5）中包含的函數(shù)式（1）。

=ROUND（A2，n）-（ROUND（MOD（A2*10^n，2），10）=0.5）/10^n （6）

5 結(jié)論

本文僅僅選用ROUND函數(shù)和MOD函數(shù)2個Excel函數(shù)就實現(xiàn)了符合規(guī)范要求的水文流量“四舍六入”取舍方法，方法簡單易學(xué)。加上IF函數(shù)僅僅3個Excel函數(shù)就解決了符合規(guī)范要求的水文實測流量計算中水深、斷面面積、流速、流量各個分步計算的取用精度取舍問題。從而大大地簡化了實測流量的計算過程，節(jié)約了實測流量計算時間。方法簡單易學(xué)，便于推廣，從而能提高水文流量測驗的工作效率。本文的取用精度取舍方法已經(jīng)在邛崍水文站的實測流量計算中進行大量運用，計算結(jié)果準確可靠，得到了同事們的認可。

當然，水平有限，本文旨在拋磚引玉，希望廣大水文技術(shù)人員能從中得到啟發(fā)，編寫出更多方便實用的水文電算化程序，從而提高各種水文技術(shù)工作的工作效率。