巧用Excel表格制作动态全船钢板用料清单

第34卷第1期2017年2月江苏船舶JIANGSU SHIPVol.34 No.1Feb.2017巧用Excel表格制作动态全船钢板用料清单徐建，蒋华，韩云卿(扬州大洋造船有限公司，江苏扬州225107)摘要:为迅速汇总全船钢板用料，通过运用Excel程序制作一个表格，使其具有按照实际所需自动生成全船钢 板用料清单的功能，并成为准软件化的表格。

经实船应用证明，该表格可以快速准确地完成全船订货，从而减少错误浪费问题，为有效提高钢材利用率做好了坚实的基础。

关键词:船用钢板;用料清单;Excel表格;批次用料表中图分类号:U673.2 文献标志码:A〇引言全船钢板用料清单是船厂用于钢板订货的依据。

目前，制作订货清单一般利用Excel软件来完成。

其方法为:首先将各个批次的用料表集中放在一个总表里，其次进行排序，然后运用Excel的汇总功能，最后得出全船的用料清单。

该方法操作繁琐，且不利于修改，一旦某个批次的用料表有修改，前面的步骤就要从头到尾重新操作一遍。

船舶建造过程中，设备的修改、各专业之间的协调、建模时发生的错误以及船东船检意见等都会造成各批次材料规格、材质或数量的修改，而钢材的订货周期短，每条船的订货时间紧张，若要完成最终的全船订货清单，需要经过多次修改、汇总，既花费大量时间，又容易出现错误遗漏，因此迫切需要制作能够快速自动汇总全船钢板用料的表格，以提高设计的质量和设计的效率，更好满足生产实际需求。

本文根据原始表格数据，通过Excel程序，并运用其中的函数公式，使其能够自动排序、索引、汇总，最终完成动态全船钢板用料清单。

1基本思路制作准软件化的Excel表格，主要从以下4方面进行探讨：(1) 如何在Excel中构思表格的制作，并且像专 业应用软件那样可以反复使用。

(2) 能否在输入原始数据后，实现自动计算，快 速得到计算结果。

收稿日期:2015-12-04作者简介:徐建（1972—），男，工程师,从事船舶与海洋工程研究。

(3) 能否在输入原始数据的地方进行修改，自动重新处理数据，快速得到新的计算结果。

(4) 能否直观地检查可能发生的错误，避免据错乱，并对“准软件化”的表格进行有效的保护，让表格操作人员不能随意更改表格中的函数公式和 构架。

针对以上问题，研究运用Microsoft Office Excel 2007或以上版本表格功能，将全船各批次钢板用料 表合并，通过Excel的各种函数计算，将各种板规按 照材质、板厚、板材宽度、板材长度自动进行排序，并 将相同的规格和材质的钢板数量进行合并，最后列 出全船所有板规的汇总结果，实现动态制作全船钢 板用料清单的功能。

2制作过程某6万吨级散货船大约有130个分段，每个分 段作为独立批次套料，每个批次套料完成后都会生 成1个批次用料表，每个批次用料表约有二三十种 板规，此表就是最原始的数据依据。

本文假设某船 共有3个批次，每个批次各有3种板规，将3个批次 用料表放到一起，就作为钢板原始用料清单，然后根 据此表来制作该船的全船钢板用料清单。

钢料原始 用料清单见表1。

本文中的板材规格、厚度、宽度、长度单位为mm,重量单位为kg。

2.1根据功能需求划分表格的4大区域根据表1，新创建一个Excel表格，并将此表格 划分为4个大的区域，分别如下：(1)原始数据区域:A~H列，共8列。

表格的 前4列为各批次用料表填写区域，此处填写各批次 的钢板用料表，为手工录入或复制填入;后4列为自动生成。

32表1钢板原始用料清单江苏船舶第34卷批次板材规范材质数量301C 11.5 *2 200 * 10 000A 1301C 12 * 2 200 * 8 000A 2301C 11.5 *2 000 * 11 000AH322302C 11.5 *2 200 * 10 000A 1302C 11.5 *2 200 * 10 000A 2302C 11.5 *2 000 * 10 000AH362303C 12 * 2 000 * 8 000A 2303C 11.5 *2 000 * 11 000AH321303C 11.5 *2 000 * 10 000AH362(2) 中间计算区域:I ~ A P 列，共34列，此处是 运用各种函数公式做排序、汇总等运算的区域。

此 区域不能填写任何内容，自动生成。

(3) 按批次名排序区域:A Q 〜A X 列，共8列。

按批次名顺序排列钢板用料清单。

此区域不能填写或修改任何内容，自动生成。

(4) 按板规排序区域:A Y 〜B E 列，共7列，按板 规顺序排列钢板用料清单。

此区域不能填写或修改 任何内容，自动生成。

下面就对各个区域逐个制作。

2.2原始数据区域的制作(1)在原始数据区域设置8列，表头标题分别 为批次、板材规格、材质、数量、厚度、宽度、长度、重 量（下文中的标题直接看表中的标题，不再在文中表2钢板清说明），钢板清单原始数据区域见表2。

第A 、B 、C 、 D 列为手工输入部分，在这里手工输入各个批次的 批次名、板材规格、材质及所需的数量。

第E 、F 、G 列则运用公式将板材规格分解为板厚、宽度和长度， 第H 列为总的板材重量。

(2)将表1的内容填入到表2相应的A 、B 、C、D 4列单元格中。

在E 2单元格填入公式“ =IF( B2 = 0,"", VALUE ( LEFT ( B2, FIND ( " *",B 2,1)- 1))) ”将得到板材的厚度，F 2单元格填入公式“= IF( B2 = 0,"" , VALUE ( MID ( B2, FIND ( " * " , B2, 1) +1,(FIND(" * " ,B2,FIND(" * " ,B2,1) +1)- FIND(" *"，B2，1) -1))))”将得到板材的宽度数值，G2单元格填入公式“ =IF(B2 =0，""，VALUE (RIGHT( B2,LEN (B2) - FIND (" * " , B2, FIND (" * "，B2,1) + 1)))) ”将得到板材的长度数值，H2 单元格填入公式“ =IF( B2 = 0，""，E2 * F2 * G2 * N(D 2) * 7. 85/1000000000)”将得到该板规的总重量。

E 、F 、G、H 4列的其他单元格可通过Excel 的拖 曳复制功能，将表格复制到30行，使各单元格得到 相应的公式和数值。

需要说明的是，本文中的公式 均为该列中第2行单元格的公式，第3行向后的公 式均使用Excel 的拖曳功能复制完成。

本文表格行 数为1〇行，根据经验，实际操作时一般拖曳复制到 5 000彳了可柄足需要。

至此，原始数据区域制作完成。

钢板清单原始数据区域结果见表2。

ABCDEFGH 序号批次板材规范材质数量厚度宽度长度重量25.347 651301C 11.5 *2 200 * 10 000A 111.5 2 20010 000 1.986 052301C 12*2 200 * 8 000A 212 2 2008 000 3.014 43301C 11.5 *2 000 * 11 000AH32211.5 2 00011 000 3.972 14302C 11.5 *2 200 * 10 000A 111.5 2 20010 000 1.986 055302C 11.5 *2 200 * 10 000A 211.5 2 20010 000 3.972 16302C 11.5 *2 000 * 10 000AH36111.5 2 00010 000 1.805 57303 C 12 * 2 000 * 8 000A 212 2 0008 000 3.014 48303 C 11.5 *2 000 * 11 000AH32111.5 2 00011 000 1.986 059303 C 11.5 *2 000 * 10 000AH36211.5 2 00010 0003.6112.3中间计算区域的制作从I 列开始到A P 列为中间计算区域。

I 列为更正后板规列。

由于原始板规数据里含 有“ ”号，容易造成运算时出错，通过公式“ =IF (B 2 =0，""，£2&"义"&打&"义"&02)”即可得到所需要的板规表示形式。

这里的板规已经不含有“ 取代它的是字符“X ”。

J 列为材质代码列。

常用的材质有A 、B 、D 、E 、 AH 32、AH 36、DH 32、DH 36、EH 32、EH 36 等，需要用 一个公式来将它们进行排序。

通过公式“ =m atch第1期徐建，等:巧用Excel表格制作动态全船钢板用料清单33(C2, j "A" ；"B" ；"D" ；"E" ；"AH32" ；"AH36"；" DH32" ;"DH36" ;"EH36" 丨，0)”就可以知道本行的材质在顺序中对应的序号。

例如材质A对 应的就是1，材质AH32对应的就是5，AH36对应的 就是6。

K列为排序依据，通常的做法是通过多重条件 语句来进行比较。

运用i f语句，先将材质进行比较，如果不同就可以判断大小;如果相同，那么就继 续比较板厚。

不同则分出大小，相同就继续比较宽 度。

以此类推，直到将每个板规能够确定哪个在前 哪个在后。

此方法相当繁琐，可以变通一下，运用公 式“=((N(G2 )+ N(F2 )* 10000 + N(E2 )* 1000000000 + N (J2 )* 100000000000 )+ ROW (B2)/100) * 100”，即将几个需比较大小的参数代 码放置到一个15位的长整数中，前2位放置材质代 码，3、4、5位为板厚代码，6、7、8位为宽度代码，9、表3按板规、木1〇、11为长度代码，12、13、14、15为行号代码。

由于 数字间比较大小是先比较高位再比较低位，那么自 然材质代码小的数值就小，材质代码大的数值就大，相同就会继续比较下一位。

这样就可以按照想要的 顺序，给各种板规材质确定大小顺序，为下一'步排序 做好准备。

L列为相对位置列，公式为“ =RANK(K2，$ K $ 2: $ K$ 30，1)”。