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

徐 建，蒋 华，韩云卿

(扬州大洋造船有限公司，江苏 扬州 225107)



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

徐 建，蒋 华，韩云卿

(扬州大洋造船有限公司，江苏 扬州 225107)

为迅速汇总全船钢板用料，通过运用Excel程序制作一个表格，使其具有按照实际所需自动生成全船钢板用料清单的功能，并成为准软件化的表格。经实船应用证明，该表格可以快速准确地完成全船订货，从而减少错误浪费问题，为有效提高钢材利用率做好了坚实的基础。

船用钢板；用料清单；Excel表格；批次用料表

0 引言

全船钢板用料清单是船厂用于钢板订货的依据。目前，制作订货清单一般利用Excel软件来完成。其方法为：首先将各个批次的用料表集中放在一个总表里，其次进行排序，然后运用Excel的汇总功能，最后得出全船的用料清单。该方法操作繁琐，且不利于修改，一旦某个批次的用料表有修改，前面的步骤就要从头到尾重新操作一遍。船舶建造过程中，设备的修改、各专业之间的协调、建模时发生的错误以及船东船检意见等都会造成各批次材料规格、材质或数量的修改，而钢材的订货周期短，每条船的订货时间紧张，若要完成最终的全船订货清单，需要经过多次修改、汇总，既花费大量时间，又容易出现错误遗漏，因此迫切需要制作能够快速自动汇总全船钢板用料的表格，以提高设计的质量和设计的效率，更好满足生产实际需求。本文根据原始表格数据，通过Excel程序，并运用其中的函数公式，使其能够自动排序、索引、汇总，最终完成动态全船钢板用料清单。

1 基本思路

制作准软件化的Excel表格，主要从以下4方面进行探讨：

(1)如何在Excel中构思表格的制作，并且像专业应用软件那样可以反复使用。

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

(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列为自动生成。

表1 钢板原始用料清单

(2)中间计算区域：I～AP列，共34列，此处是运用各种函数公式做排序、汇总等运算的区域。此区域不能填写任何内容，自动生成。

(3)按批次名排序区域：AQ～AX列，共8列。按批次名顺序排列钢板用料清单。 此区域不能填写或修改任何内容，自动生成。

(4)按板规排序区域：AY～BE列，共7列，按板规顺序排列钢板用料清单。 此区域不能填写或修改任何内容，自动生成。

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

2.2 原始数据区域的制作

(1)在原始数据区域设置8列，表头标题分别为批次、板材规格、材质、数量、厚度、宽度、长度、重量(下文中的标题直接看表中的标题，不再在文中说明)，钢板清单原始数据区域见表2。第A、B、C、D列为手工输入部分，在这里手工输入各个批次的批次名、板材规格、材质及所需的数量。第E、F、G列则运用公式将板材规格分解为板厚、宽度和长度，第H列为总的板材重量。

(2)将表1的内容填入到表2相应的A、B、C、D 4列单元格中。在E2单元格填入公式“=IF(B2=0，""，VALUE(LEFT(B2，FIND("*"，B2，1)-1)))”将得到板材的厚度，F2单元格填入公式“=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(D2)*7.85/1000000000)”将得到该板规的总重量。E、F、G、H 4列的其他单元格可通过Excel的拖曳复制功能，将表格复制到30行，使各单元格得到相应的公式和数值。需要说明的是，本文中的公式均为该列中第2行单元格的公式，第3行向后的公式均使用Excel的拖曳功能复制完成。本文表格行数为10行，根据经验，实际操作时一般拖曳复制到5 000行可满足需要。

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

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

表2 钢板清单原始数据区域

2.3 中间计算区域的制作

从I列开始到AP列为中间计算区域。

I列为更正后板规列。由于原始板规数据里含有“*”号，容易造成运算时出错，通过公式“=IF(B2=0，""，E2&"X"&F2&"X"&G2)”即可得到所需要的板规表示形式。这里的板规已经不含有“*”，取代它的是字符“X”。

J列为材质代码列。常用的材质有A、B、D、E、AH32、AH36、DH32、DH36、EH32、EH36等，需要用一个公式来将它们进行排序。通过公式“=MATCH(C2，{"A";"B";"D";"E";"AH32";"AH36";"DH32";"DH36";"EH36";"";0}，0)”就可以知道本行的材质在顺序中对应的序号。例如材质A对应的就是1，材质AH32对应的就是5，AH36对应的就是6。

K列为排序依据，通常的做法是通过多重条件语句来进行比较。运用if语句，先将材质进行比较，如果不同就可以判断大小；如果相同，那么就继续比较板厚。不同则分出大小，相同就继续比较宽度。以此类推，直到将每个板规能够确定哪个在前哪个在后。此方法相当繁琐，可以变通一下，运用公式“=((N(G2)+N(F2)*10000+N(E2)*1000000000+N(J2)*100000000000)+ROW(B2)/100)*100”，即将几个需比较大小的参数代码放置到一个15位的长整数中，前2位放置材质代码，3、4、5位为板厚代码，6、7、8位为宽度代码，9、10、11为长度代码，12、13、14、15为行号代码。由于数字间比较大小是先比较高位再比较低位，那么自然材质代码小的数值就小，材质代码大的数值就大，相同就会继续比较下一位。这样就可以按照想要的顺序，给各种板规材质确定大小顺序，为下一步排序做好准备。

L列为相对位置列，公式为“=RANK(K2，$K$2:$K$30，1)”。通过此公式可将刚才的K列的数值进行排序，以确定该行的K值在该K列中的相对位置。例如单元格L2、L3、L4的值分别是1、4、6，说明K2、K3、K4的值在K列中的顺序分别为第1、第4、第6。

M列为序号列。为了拖曳复制公式的方便，将用公式“=ROW(B1)”表示(注:如果这里不用此公式,而是直接填序号1,当发生拖曳操作进行复制时,序号不会按行增加,始终是1)，结果见表3。

表3 按板规、材质排序的相对位置

N～U共8列，是通过Excel的INDEX函数的索引功能将A～H这8列重新进行排序，将最初的原始用料表按照所需要的材质→厚度→宽度→长度顺序重新进行了排列，结果见表4。

表4 重新排序后的结果

V～AC列是用于合并的列，即将同一批次中相同的板规和材质的进行合并，以确保同一批次中不存在板规、材质同时都相同的情况。假设在初始的某个批次的用料表中，有一种板规和材质同时出现了2次或2次以上，且没有在用料表中及时发现，那么在这里就会自动的合并，以确保后面的行列在同一批次中不会出现板规和材质同时相同的情况，以避免造成表格的错误运算。

AD列是运用公式“=MATCH(V2，{"301P";"301S";"302P";"302S";"303P";"302S";"";0}，0)”将批次顺序进行排序。

AE列公式为“=AD2*10000+ROW(A1)”。

AF列公式为“=RANK(AE2，$AE$2:$AE$30，1)”。

通过这3列的运算可得到不同批次、不同板规和材质的相对位置，从而为后面的以批次单位为顺序来排序做准备。

按批次排序的相对位置其结果见表5。

表5 按批次排序的相对位置

AG至AP这10列与V至AF这11列基本类似，只是去除了批次信息，使全船中只要板规材质相同的合并汇总到一起，而不考虑批次名是否相同，以此确定该行在后面以板规顺序来排序时的相对位置，见表6。

至此，中间运算区域的表格完成。

表6 按板规和材质排序的相对位置

2.4 按批次排序区域的制作

中间运算区域的表格制作完毕后，接着需要生成2种所要用的表格，一个是以批次单位为顺序的材料用料清单，将放置在按批次排序区域；一个是以材料规格为顺序的用料清单，将放置在按板规排序区域。

按批次排列区域运用INDEX函数的索引功能。

V列公式“=INDEX($V$2:$AC$30，MATCH($M2，$AF$2:$AF$30，0)，1)”，W列公式“=INDEX($V$2:$AC$30，MATCH($M2，$AF$2:$AF$30，0)，2)”，……，AC列公式“=INDEX($V$2:$AC$30，MATCH($M2，$AF$2:$AF$30，0)，8)”。

通过逐个变换INDEX函数的column参数，将V至AC列的数据按相对位置中的值进行升序排列，得出的结果就是按批次排列的钢板用料清单，生成的清单见表7。

2.5 按材质和板规排序区域的制作

同理，继续运用INDEX函数的索引功能，将AG～AN列的数据按材质和板规顺序进行升序排列，得出的结果就是按照板规排列的钢板用料清单，其生成的清单见表8。

以上就是制作动态全船用料清单的全过程。为了防止误操作，导致表格的结构和公式遭到破坏，可以将表格进行保护。由于该表格中A、B、C、D 4列为人工输入区域，通过单元格设置将此区域设置为不锁定，也就是将锁定项前的选择框里的选钩去掉，而将后面所有的自动运算的列都设置为锁定，然后将部分不需要显示的列隐藏，再通过选单选择审阅→保护工作表，这样就可以清晰直观地显示结果，并有效地避免数据错乱，到此就完成了设计需要的“准软件化”的动态表格。

3 使用方法

由于制作动态全船钢板用料清单整个过程除了原始数据需手工填入，其他部分均为自动生成，因此操作人员只需将单个批次的用料表手工填入或复制到本表格的原始数据区域中，即可自动生成钢板用料清单。另外，还可以对已完成的订货清单表格进行修改。例如因设备修改造成船体结构发生变化从而使某个批次的板规、数量需要修改的，这时只要在原始数据区前4列中进行增、减、修改等操作，修改后的全船钢板用料清单就能自动完成更新，整个流程简单、快捷、方便。

表7 钢板用料清单(按批次排序)

表8 钢板用料清单(按材质和板规排列)

4 结语

制作完成此动态全船用料清单表格后，依次录入各批次用料清单到此Excel表格，由表格自动生成全船的订货清单，包括按批次排列的订货清单和按板规、材质排序的订货清单，再交由采购部门进行订货。当然，还可以运用Excel程序对该表格进行优化，例如通过设置软件自带的条件格式功能将输入的数据进行识别、检验，找出输入错误，再比如增加合并板规区域来自动合并相近板规等功能。通过几个项目的实际使用，充分展示了该表格方便快捷、功能强大，极大地提高了工作效率和质量。

[1] 傅靖.Excel 2007中文版VBA开发技术大全[M].北京：电子工业出版社，2008.

2015-12-04

徐建(1972—)，男，工程师，从事船舶与海洋工程研究。

U673.2

A