用EXCEL 的內(nèi)置函數(shù)實現(xiàn)船舶水尺計量中的內(nèi)插計算

福建省輪船有限公司 林 熹

對于大型散裝船舶而言，水尺計量是比較重要且繁瑣的工作, 筆者所在船舶是一艘載重噸為73829的大型散裝貨輪，有7 個貨艙，壓載水艙共計23 個,從觀察船舶六面吃水開始，到測量每個壓載艙的水位，到最后的查表內(nèi)插計算得出貨量,往往需要2 小時，若商檢對某些問題有爭議，則往往需要更多的時間去完成這項工作，非常麻煩。筆者在工作之余，利用Excel 軟件的內(nèi)置函數(shù)功能制作水尺計量專用表格，并設(shè)為模板，大大簡化了日常的水尺計量工作，只需輸入船舶六面水尺、各壓載艙的測深數(shù)據(jù)、船舶留存燃油和淡水等數(shù)據(jù)，可以立即得到船舶常數(shù)、本航次載貨量等核心數(shù)據(jù)，大大簡化了查艙容表、內(nèi)插計算等工作，提高了計算精度和工作效率。

本文的讀者要求對EXCEL 內(nèi)置函數(shù)有一定的了解和實際使用經(jīng)驗，若是初學(xué)者，請自學(xué)相關(guān)的EXCEL 書籍入門。大多數(shù)的使用者用EXCEL 完成各種初始表格的制作后，會發(fā)現(xiàn)要使得EXCEL 完成內(nèi)插計算比較困難，而制作船舶水尺計量的關(guān)鍵也就在于如何實現(xiàn)有關(guān)數(shù)據(jù)的查找并進行內(nèi)插計算，本文著重說明如何應(yīng)用EXCEL 內(nèi)置函數(shù)完成求取艙水重量的內(nèi)插計算，并以較為詳細的步驟完成，實際編制計算表時，可以使用公式的組合，節(jié)省有關(guān)步驟。

為節(jié)省篇幅，筆者僅以我輪2＃壓載艙為例，講解如何通過輸入船舶吃水差、艙水密度、壓載艙測量水位等3 個數(shù)據(jù)，經(jīng)過EXCEL 內(nèi)置函數(shù)的內(nèi)插計算而直接得出2＃壓載艙的艙水重量。

現(xiàn)以2＃壓載艙測深為5.25 米，船舶吃水差為－1.0 米，艙水密度為1.025 為例，介紹如何通過內(nèi)插計算得出艙水重量（結(jié)果為1258.6 噸）。

首先，建立一個工作表，將2＃壓載艙的相關(guān)數(shù)據(jù)輸入，如圖1 所示。

圖1

圖1 中，B 列為壓載艙測深查表數(shù)據(jù)，C 列為艙容數(shù)據(jù)，E4 至K4 為船舶吃水差修正查表值，E4 至K20 為吃水差修正查表區(qū)域。B22 輸入艙水測深數(shù)據(jù)（如5.25），B23 輸入船舶吃水差數(shù)據(jù)（如－1.0），H22 輸入艙水密度數(shù)據(jù)（如1.025），單位略去。內(nèi)插計算時，以壓載艙測量水位和船舶吃水差為查表值查取內(nèi)插計算數(shù)值進行內(nèi)插計算，得出吃水差修正數(shù)值；吃水差修正數(shù)值與壓載艙測深水位相加后得出實際水位（即零吃水差狀態(tài)下的壓載艙水位）；以實際壓載艙水位查取艙容表，經(jīng)內(nèi)插計算得出艙水容積；艙水容積乘以艙水密度即為艙水重量。

其次，在B25 至B48 單元格間建立共計18 步計算步驟，得出艙水重量，如圖2 所示。

圖2

分析輸入數(shù)據(jù)及艙容表，發(fā)現(xiàn)測深5.25 介于數(shù)據(jù)5.00以及5.50 之間，吃水差－1.0 介于數(shù)據(jù)－1.5 和－0.5 之間,要完成壓載艙水位為5.25 米，船舶吃水差為－1.0 的吃水差修正，我們必須進行內(nèi)插計算。

按照內(nèi)插計算要求，我們應(yīng)查找單元格H15、H16、I15、I16 四個修正值進行內(nèi)插計算，查找值為B15、B16 及H4、I4, 即以艙水測深5.00 和5.50 及吃水差－1.5 和－0.5 為查找值查找吃水差修正值，其值為0.95、1.39、0.82、1.23,在進行內(nèi)插計算，首先得出吃水差為－1.0 時測深為5.00 和5.50 的修正值,在進行內(nèi)插計算得出艙水測深為5.25、吃水差為－1.0時的實際修正值，對測深進行吃水差修正后即得到實際艙水測深6.348;其次以實際艙水測深6.348查6.00處的艙容及6.50處的艙容,再經(jīng)內(nèi)插計算得出6.348 測深的實際艙容1227.9;最后以實際艙水容積乘以艙水密度,即得該壓載艙壓載水實際重量1258.6。具體步驟如下:

步驟一：在B25 單元格輸入“=HLOOKUP(B23,E4:K20, (MATCH(B22,B5:B20,1))+1,TRUE)”，（注：引號不輸入，下同），即以小于測深和吃水差的表列數(shù)值查取吃水差修正內(nèi)插第一項得出數(shù)值0.95;

步驟二：在B26 單元格輸入“=HLOOKUP(B23,E4:K20, (MATCH(B22,B5:B20,1))+2,TRUE)”,即以大于測深和小于吃水差的表列數(shù)值查取內(nèi)插第二項得出數(shù)值1.39;

步驟三：在B27 單元格輸入“=INDEX(E4:K20,(MATCH (B22,B5:B20,1))+1, (MATCH(B23,E4:K4,1))+1)”，即以小于測深和大于吃水差的表列數(shù)值查取內(nèi)插第三項得出數(shù)值0.82;

步驟四：在B28 單元格輸入“=INDEX(E4:K20,(MATCH (B22,B5:B20,1))+2, (MATCH(B23,E4:K4,1))+1)”，即以大于測深和吃水差的表列數(shù)值查取吃水差修正內(nèi)插第四項得出數(shù)值1.23;

步驟五：在單元格B30 輸入“=HLOOKUP(B23,E4:K4,1, TRUE)”，即查取吃水差修正的查表值1(小于本船吃水差值,吃水差=-1.5)；

步驟六：在單元格B31 輸入“=INDEX(E4:K4,1,(MATCH (B23,E4:K4,1))+1)”，即查取吃水差修正的查表值2(大于本船吃水差值,吃水差=-0.5)；

步驟七：在單元格B32 輸入“=VLOOKUP(B22,B5:B20,1, TRUE)”，即查取測深的內(nèi)插查表值3(小于實際測深數(shù)值,測深=5.0)；

步驟八：在單元格B33 輸入“=INDEX(B5:B20,(MATCH (B22,B5:B20,1))+1,1)”，即查取測深的內(nèi)插查表值4(大于實際測深數(shù)值，測深=5.5)；

到本步驟為止，我們將內(nèi)插計算需要的各個數(shù)值均查找出來，并分別列于B25 至B28，B30 至B33；

步驟九：在單元格B35 輸入“=ROUND(B25+(((B25-B27)* (B23-B30))/(B30-B31)),3)”,即求取測深=5.0 處,吃水差為-1.0的吃水差修正值1 得出數(shù)據(jù)0.885;

步驟十: 在單元格B36輸入“=ROUND(B26+(((B26-B28) *(B23-B30))/(B30-B31)),3)”,即求取測深=5.5 處,吃水差為-1.0的吃水差修正值2 得出數(shù)據(jù)1.310;

步驟十一: 在單元格B37 輸入“=ROUND(B35+(B36- B35)*(B22-B32)/(B33-B32),3)”,即求取實際測深=5.25 處,吃水差為-1.0 的實際吃水差修正值,得出實際吃水差修正值1.098;

步驟十二: 在單元格B39 輸入“=B22+B37”,即實際測深=測深+實際吃水差修正值,得出吃水差修正后的實際測深值6.348;

步驟十三: 在單元格B41 輸入“=VLOOKUP(B39,B5: C20,2,TRUE)”,即以實際測深查取艙容值1(小于實際測深艙容值),得出測深為6.00 處的艙容數(shù)據(jù)1223.00;

步驟十四: 在單元格 B42 輸入“=INDEX(B5:C20, (MATCH(B39,B5:B20,1)+1),2)”,即以實際測深查取艙容值2(大于實際測深艙容值),得出測深為6.50 處的艙容數(shù)據(jù)1230.00;

步驟十五: 在單元格B43 輸入“=VLOOKUP(B39,B5: B20,1,TRUE)”,即以實際測深查取內(nèi)插的查表值1(小于實際測深)得出數(shù)據(jù)6.00;

步驟十六: 在單元格 B44 輸入“=INDEX(B5:B20, (MATCH(B39,B5:B20,1)+1),1)”,即以實際測深查取內(nèi)插的查表值2(大于實際測深)得出數(shù)據(jù)6.50;

步驟十七: 在單元格B46輸入“=ROUND(B41+(B42-B41) *(B39-B43)/(B44-B43),1)”,即以實際測深經(jīng)內(nèi)插后得出實際艙水容積,得出艙水容積1227.9;

步驟十八: 在單元格B48 輸入“=ROUND(B46*H22,1)”,實際艙容乘以艙水密度即為艙水重量,得出最終艙水重量數(shù)據(jù)1258.6。

經(jīng)過以上的18 個步驟,我們可以得到2#壓載艙的實際艙水重量數(shù)據(jù),以上步驟及公式和注釋可參看圖3。

圖3

經(jīng)過以上實例，我們知道內(nèi)插計算的關(guān)鍵函數(shù)不外乎以下這么幾個：值查找和引用函數(shù)VLOOKUP、HLOOKUP、INDEX、MATCH,數(shù)學(xué)與三角函數(shù)ROUND、INT 等等，以上有關(guān)EXCEL 內(nèi)置函數(shù)的應(yīng)用和各個參數(shù)的設(shè)置請讀者參閱有關(guān)EXCEL 進階讀物和書籍，筆者在此不再詳細敘述。

總之，EXCEL 的計算功能是非常強大的，筆者用EXCEL制作了大量的實用表格，如船舶穩(wěn)性、吃水差、船中強度校核表(小型船舶用)；水尺計量計算表;船舶快速配載圖表；太陽真出沒和視出沒方位求羅經(jīng)差計算表等等，都非常實用，且節(jié)約了不少計算時間，而很多計算表各功能的實現(xiàn)大多經(jīng)過查數(shù)據(jù)表內(nèi)插計算完成，所以掌握有關(guān)數(shù)據(jù)的查找和引用并進行內(nèi)插計算是關(guān)鍵，本文意在拋磚引玉，使得更多的讀者利用EXCEL 制作出功能更為強大的船舶實用計算表格，簡化我們的日常工作。