【干货！】如何制作EXCEL算货表，手把手教~~~

XGLPOLAR 2017-07-14

展开全文

本文作者：欧阳，来源：欧阳船说 ouyangtalk

大家好，今天来分享通过EXCEL Office 办公软件来制作一个算货表，又叫 Draft Survey Report。商船分好多种类，当然最大的份额就属货船，货船以运输货物赚取租金运费，装船最大货量当然是各个关联方比较关心的问题，船舶航行区域不同所取得载重线区域不同，这就例如汽车有上高速过大桥不能超载一样，船舶吃水(水下深度)越大，在水上的储备浮力也就越小。各船级社通过履行海事组织的规则，制订了最小的储备浮力，以保证船舶的储备浮力。载重线区域是多年通过观测该海域的风浪情况，从而设定安全的储备浮力，举例说，冬天的白令海海域风浪大就需要更大的储备浮力来保证安全。

本次计算主要基于57000载重吨的大灵便型船舶为基础做的 Draft Survey Report。制作表格主要解决几个内部函数问题，具体包括修正钢板厚度（正常加减函数Keel），大于或者小于9M吃水选择的修正常数不同（IF函数），根据得出的Quarter Mean 查找功能（VLOOKUP函数），内差各个必要的参数（内差函数），最后修正得出货量。

如何通过一个空白的EXCEL制作出这样一个算货表格呢？下面我们就详细介绍：

1. 新建EXCEL 确定大概需要合并单元格的列数大概有几个，A-H基本够用了（最后能够很好的显示在A4纸上，表格美观最后调节）,固定一个单元格输入所处水密度。

2. 根据实际观测的六面吃水，通过几次修正得出Quarter Mean的吃水

① 本船需要根据观测吃水减去钢板厚度0.018M，然后转换到型吃水。函数减法，浅黄色部分为实际观测的六面吃水，在F9单元格按键等于号，六面依次减去0.018M，得出绿色部分的型吃水。后面的Mean Draft为（F9+G9）/2，（F10+G10）/2，（F11+G11）/2的值。

② 第二次修正，通过Mean Draft 把吃水修正到首舯尾柱上(eg.DAmd=12.087, DMmd=11.812, DFmd=11.487)

公式根据装载手册，修正前舯尾吃水,当船艉DA md>=9.0M,取值+10.89 ，171.01

DA=DA md+10.89(DAmd -DFmd)/171.01

DM= DM md-0.84(DAmd -DFmd)/171.01

DF =DF md-3.10(DAmd -DFmd)/171.01

公式根据装载手册，修正前舯尾吃水,当船艉DA md<>

DA=DA md-1.2(DAmd -DFmd)/183.1

DM= DM md-0.84(DAmd -DFmd)/ 183.1

DF =DF md- 3.10(DAmd -DFmd)/ 183.1

以上公式可以看出，需要设定IF公式，IF公式就是说船尾的吃水大于等于9.0M就选择+10.89 ，171.01值，如果船尾的吃水小于9.0M就选择-1.2，183.1。(DAmd -DFmd)为一个固定的值（例如下面数值就是0.6），所以就直接设置在一个单元格。

船尾的吃水大于等于9.0M。C12公式=IF(B12>9,10.89,-1.2)

D12=IF(B12>9,171.01,183.1)

C12 ，D12的限定IF函数仅仅是为了选值是选取10.89，还是-1.2 ；还是171.01还是183.1.

例如IF公式输入C12和D12后，船尾的吃水输入成8.0M，C12和D12的数值就会自动变为-1.2，183.1。

因为根据装载手册船首和船舯修正公式是基本相似（取值是183.1还是171.01？）

(DAmd -DFmd)为一个固定的值（例如下面数值就是0.6），所以就直接设置在一个单元格。如下图：

③ 根据装载手册,和IF限定取值,修正到首舯尾柱的型吃水就只要各输入一个公式即可，根据尾部吃水是否大于小于9M就会根据IF自动选择系数代入公式。

红色部分Draft Foreward对应上面fx公式

红色部分Draft Midship对应上面fx公式

红色部分Draft After对应上面fx公式

④ 在求得Quarter Mean前需要求得首尾柱修正后的吃水差Trim,修正后船舯的平均吃水，和船舯的拱垂值大小 ?d.( ?d为正值即为舯垂，?d为负值时即为舯拱，同样有一个函数限定如果某个单元格的数值为正值，在另一个单元格就会变成Sagging，相反为负值这个单元格就会自动变成Hogging，以便提醒船舶的船舯拱垂状态)。

吃水差Trim=C13-C15 （在前面的几次吃水修正过程中，都是首舯尾都是单独修正的）

修正后船舯的平均吃水Tm=（修正后到首柱的船首吃水+修正后到尾柱的船尾吃水）/2，即=（C13+C15）/2.

上图中G14为11.8007是首尾相加的平均值，而C14值为11.80905是独立修正后的数值，两者差值即为拱垂值大小，?d=C14-G14单位为M，换算成厘米为舯垂0.84CM，在数值0.0084前面的单元格出现Sagging为自动显示，公式如下图：

E15 单元格函数fx=IF(G15<>

⑤ 求得Quarter Mean

Quarter Mean=G14+3/4（G15）直接在单元格F16输入函数，此公式为固定公式。

3．现在型吃水已经得出，按照常规手动查取Loading Manual 就可以得出排水量，然后再加上船舶的吃水差修正排水量，船舶的拱垂修正排水量，和密度修正排水量就可以了。密度修正排水量(因为在Loading Manual根据型吃水查得排水量是在1.025密度情况下，如果装载所在水域为1.025 ，此处的密度修正排水量就为0)。根据上图需要自动查取11.80696对应排水量，但是Loading Manual不会有这样的值，所以第一步是用函数VLOOKUP功能，第二步就是内差。

上图在Sheet2 可以改名为Hyd ，需要根据Loading Manual 手动输入Draft ,Displ. ,TPC , LCA ,MTC（意见一个人念一个人输入）下面介绍一下上图输入Draft ,Displ. ,TPC , LCA ,MTC是否正确呢？

例如，在输入LCA结束需要验证是否正确，在列表I新建一个相减的函数，在0.0280单元格输入=E2-E3，直接下拉I2单元格就会出现0.026，0.028，0.026，0.027，如果突然出现异常就能检查出哪个地方出现错误了，同理验证Displ. ,TPC ,MTC输入是否正确。

介绍VLOOKUP功能，为需要在表格数组（数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）第一列中查找的数值。举个例子如下图：

如果指定值为2.2M，就可以通过设置函数VLOOKUP查取2.2M后面对应的Displ. TPC . LCA MTC等数值。VLOOKUP仅仅是通过Excel第一列的限定值查取后面对应所需要的值而已，并没有计算的能力。内插还需要输入其它的公式。

上图只要公式输入正确，看一个六面吃水就会自动得出Quarter Mean，eg.上图得出的Quarter Mean数值为11.80696，11.80696数值在11.8和11.85之间的排水量，TPC，LCA，MTC，所以要通过VLOOKUP查找11.8和11.85对应的几个值。

上图11.8 和11.85 后面的各一排数值通过输入VLOOKUP函数就会被自动查取到。

根据上图黄色B18单元格VLOOKUP公式=VLOOKUP（F16，Hyd！A1：J1252，1）结构解释如下：

只要在B18单元格输入=VLOOKUP就会出现函数直接点击即可然后把鼠标点到F16（F16就是查找基准，就是11.80696对应的各个所需要的值，公式括号最后是1，就是对应的第一列查找的值）然后加逗号，然后把鼠标点到Hyd表中，！号是自动跳出的，A1：J1252是你在Hyd表搜索所固定的搜索范围。A1就是这个Hyd表的最左上角，J1252就是在Hyd的最下右的点，1的意思是在11.80696一整行固定到第一列，也就是11.80。有人会有疑问11.80696查取的值怎么会到11.8对应查取的值上了呢？

那么就根据Office Excel 里面介绍的VLOOKUP功能

查取数值仅查表内的数值，根据Quarter Mean的数值可以查取匹配数值（近似最小值为基数）。

通过 VLOOKUP查取到了，11.80，那么就以11.80为基数查取排水量，排水量单元格的公式如下图：

C18=VLOOKUP(B18,Hyd!A1:I242,2)

B19=B18+0.05（因在Loading Manual型吃水表吃水相差是0.05M，Quarter Mean11.80696又在11.8和11.85之间，故绿色部分B19=B18+0.05）

既B19数值会自动出现，那么C19单元格内的函数就会以B19为基数查取排水量， C19=VLOOKUP(B19,Hyd!A1:I242,2)

同理上面步骤TPC ，LCA，MTC 以11.80 ，11.85为基数输入VLOOKUP函数查值，对应的值就会被查取到。

上图是Hyd的表头对应能够查取的对应值。

TPC D18=VLOOKUP(B18,Hyd!A1:I242,4)，同理输入单元格D19 。

LCA E19=VLOOKUP(B19,Hyd!A1:I242,5)，同理输入单元格E18 。

MTC F18=VLOOKUP(B18,Hyd!A1:I242,7)，同理输入单元格F19 。

4．现在就需要回到第一张图的主旨了，以上所输入的函数，无非就是查取需要的数值，不需要人工取查取填入。后面就开始来计算具体船舶的排水量和如何内差修正了。我们还需要从上面的吃水来说。

（1）需要计算11.80696对应的排水量，上面的11.8和11.85对应的排水量已经自动会显示出来，仅仅需要输入内差公式就自动显示11.80696对应的排水量。

（2）三个排水量修正，即吃水差排水量修正，舯拱垂排水量修正，密度修正排水量（上面已经介绍，如果舷外密度为1.025，那么修正值就是0），通过计算三个修正得出的数值，再加上11.80696对应的排水量就得出了船舶现状态的排水量，而后的货量也就迎刃而解。那么现在我们就解决这四个问题的函数问题。

①第一个问题解决排水量修正，11.80696 内差对应的排水量，如下图：

11.80696 内差C26=C18+(C19-C18)*(F16-B18)/((B18+0.05)-B18)

②第二个问题解决吃水差排水量修正

根据Loading Manual 修正值为 100 X TPC X Trim（吃水差） X （LCA-Lbp/2）/Lbp

吃水差修正比较复杂，但是影响经常也是最大，从上面的公式需要解决11.80696对应的TPC值，11.80696对应的LCA值，Lbp（首尾柱长度）查取船舶资料为185M。既然TPC和LCA对应的11.8和11.85值已经知道，11.80696所对应的值只需内差就可以了。上图58.4就是内差后的TPC，90.22就是内差后的LCA。TPC和LCA内差公式如下图：

11.80696对应TPC 内差D23=D18+(D19-D18)*(F16-B18)/(B19-B18)

11.80696对应LCA 内差E23=E18-(E18-E19)/5*100*(F16-B18)

最后的吃水差排水量修正，如下图：

吃水差排水量修正值公式为 100 X TPC X Trim（吃水差） X （LCA-Lbp/2）/Lbp

单元格C27=100*D23*(G13)*(E23-185/2)/185，吃水差有可能为负值所以用括号，吃水差前面已经算出来了（单独修正后的型吃水，前后相减值）

③第三个问题解决船舶拱垂排水量修正（有的载重吨76000吨修正值为0）

拱垂排水量修正公式为50 X Trim（吃水差） X Trim（吃水差） X（MTC2-MTC1）/Lbp

此公式为总结公式，国外Survey经常用，这里的MTC2 和MTC1求取比较复杂，现说这个是什么吧，MTC2是在11.80696对应的MTC上加上一个50CM所得到的MTC值，MTC1 则为在11.80696对应的MTC上减去一个50CM所得到的MTC值。那么11.80696加上50CM是12.30696，如果想求12.30696对应的MTC就需要知道12.3和12.35各对应的MTC才能通过内差得到12.30696的MTC值。同理，11.80696减去50CM是11.30696，如果想求11.30696对应的MTC就需要知道11.3和11.35各对应的MTC才能通过内差得到11.30696的MTC值。

在输入内差公式的时候都会以12.30696和11.30696为基础，所以在两个单元格里面输入的函数分别加50CM，减50CM。

绿色部分直接输入以11.80696分别加0.5M，减0.5M。以12.30696和11.30696为基础算内差。

既然需要求取12.30696和11.30696，就需要12.3和12.35，11.3和11.35对应的值内差就可以得出了。如上图G20 =B18 + 0.5 ，其他三个同样可以输入，然后就通过VLOOKUP查取12.3和12.35，11.3和11.35对应的四个MTC值。

G21 单元格的函数就是通过12.3为基数查取得MTC值。同理求得其他三项。

通过上面查取的MTC数据内差公式在12.30696，G24=G21+(H21-G21)*(G18-G19)/(H19-G19)

MTC数据内差公式在11.30696，G25=G22+(H22-G22)*(H18-G20)/(H20-G20)。

根据拱垂排水量修正公式为50 X Trim（吃水差） X Trim（吃水差） X（MTC2-MTC1）/Lbp，（MTC2-MTC1）的值就可以算出了。

拱垂排水量修正公式为50 X Trim（吃水差） X Trim（吃水差） X（MTC2-MTC1）/Lbp，可以在C28内输入公式就可以了，50是个定值不变，吃水差为负值-0.6491，所以要加括号，Lbp为185M。

④第四个解决的问题就是修正密度排水量

公式为前面三个排水量相加（Quarter Mean查的排水量，吃水差修正排水量，拱垂修正排水量）X（Density-1.025）/1.025。如下图：

C29=(C26+C27+C28)*(H3-1.025)/1.025,(中间的数字隐藏了，为了查看方便，大家可以看左边一列数字排序不是连续的，就是因为隐藏了)

上面的四个修正输入函数后计算的结果都是自动显示的，现在计算最终的排水量，也就是这四个值相加就得出了最终的排水量。如下图: