基于Excel VBA执行SQL语句对船用电缆册进行设计优化

常 亮，汪家政

（舟山长宏国际船舶修造有限公司，浙江舟山 316052）

0 引言

电缆册是为某一船型的系列船在保持设备订货和船体结构不变的情况下，根据全船电气系统图量身定制的全船电缆清单。该清单包含系统分类、电缆代号、电缆型号规格、电缆起始点和终止点位置、电缆长度等信息。

电缆册按走向区域分为主干电缆册（跨区域）[1]和分支电缆册（不跨区域）。

目前，舟山长宏国际船舶修造有限公司（以下简称“公司”）散货船和集装箱船基本都是自主生产设计的。公司要求主干电缆中35 mm2以下的电缆要提前预裁好，以便车间施工拉放。因此，将电缆册总表分成主干电缆册和分支电缆册。

1 设计背景

截止2018年5月，中国活跃的船厂数量仅剩110多家，一个民营船厂如何在全球船市低迷、众多船厂倒闭的大背景下继续生存下去，是所有造船人每天都在思考的问题。“打铁还需自身硬”，必须提高我们的核心竞争力，尽量提高每个环节的工作效率、缩短造船周期，保质保量地完成设计和生产任务，是每个船人应该担当的责任和义务。

目前，国内大中型国有船企和大型民营船厂中，大多采用 Tribon、Catia、Foran、东欣等主流造船3D放样软件进行生产设计建模和出图。公司将利用内部强大的技术、人力、资金等资源，对软件进行二次开发，自动生成电缆册。

但对于更多的中小型船厂、民营船企以及小型船舶设计院来讲，一个软件动辄几百、上千万，还要投入大量的人力和资金进行二次开发和后期维护，难免得不偿失。有的软件虽然有自动生成电缆册的功能，但苦于公司没有多余的人力去按照软件的要求逐一录入。同时，3D软件的其他限制条件，也制约了3D放样软件自动生成电缆册。若需从其他大公司购买，不但需要一笔很大的开支，而且大公司的数据管理都在其保密数据库中，具有自主知识产权，无法直接搬移来用。

目前为止，大部分中小船企和小型设计公司的电缆册还是以手动录入、人工测量为主，经录入和测量的总电缆册还要根据需要，分成适合造船部门使用的若干电缆分册，这部分工作也相当繁琐。同时，人工在对包含少则四五千根、多则上万根电缆的Excel总表进行数据处理时，极易出现错误、重复、漏线、错行等各种问题；下发车间后，一经发现，还要对其进行反复修改以保证后续船舶的生产任务能够有序进行，所以这个问题长期困扰着电装生产设计人员。如何实现排序、筛选、汇总、求和等数据的自动处理功能，让人力和时间从大量的数据处理烦恼中解脱出来，迫在眉睫。本文主要对手动录入、人工测量的总电缆册进行自动排序、筛选、求和等功能进行了讲解。

2 电缆册制作流程

电缆册的制作流程如下：

1）根据全船电气系统图（下发版），按系统进行电缆册的录入工作，并合并为一个总表。

2）完成电装生产设计3D建模，并完成电装图、节点图的出图流程。

3）根据安装图和节点图，结合电气系统图和布置图，对每根电缆进行测量工作。

4）将测量好的总表按需分成预裁电缆的主干电缆册和不需预裁的分支电缆册。

在船舶的整个造船流程中，电缆册的制作和出图时间前后都遭受制约。前期受系统图等详细设计图纸、电装节点图等生产设计图纸的制约，后期又受上建区域小合拢、机舱货舱首部合拢时间的制约。目前，中国的造船接单状态还是以满足船东交付时间为主。由交船向开工（按照工艺流程）倒推时间节点，这样的流程留给船厂自主生产设计的时间就相当紧迫。而按照合拢时间确定好电缆的到厂时间后，再倒推45天作为电缆厂家生产电缆和预裁主干的时间，剩下留给船厂设计部制作电缆册的时间，基本都在2个月以内。这么短的时间，要手动录入并测量至少四五千根电缆，工作量相当大。所以如何缩短电缆册的制作时间，是小型船企和小型船舶设计公司亟待解决的问题。

3 构建数据模型

图1是典型散货船的电缆册划分区域图，机舱区域分为EF舱底、E2机舱下平台、E1机舱上平台等；上建区域分为AU主甲板、AA A甲板、AB B甲板、AC C甲板、AN驾驶甲板等，除此之外还有EC机舱棚区域、SG舵机舱区域、ON货舱区域、ET管弄区域、BS艏部区域等。其他集装箱船、油轮化学品船等均可按区域的划分进行调整，并根据实船增减区域。本文仅以公司为例进行讲解，其他船厂或设计公司可以根据自身需求进行修改，例如电缆册的划分原则、是否预裁、区域划分等。船用电缆册的制作属于数据库中的层次模型（见图2）。

图1 电装及电缆册制作区域划分图

图2 电缆册数据建模

4 电缆册制作过程

由于电缆册的制作是基于Excel VBA编程技术对数据进行统计分析，以Excel 2007为例，首先通过“office按钮”→“Excel选项”→“信任中心”→“宏设置”来启用宏，并将手工录入的总电缆册另存为“启用宏的工作簿“*.xlsm文件”（“*”为电缆册名字，文中的电缆册名字为“64000电缆册”）。在“office按钮”→“常用”→勾选“在功能区显示‘开发工具’选项卡”。

有些基本要求在录入电缆册时需要遵循一定原则，不能随便写，如果所建船舶工期太紧，很有可能两三个人同时进行电缆册的录入工作，倘若不规定原则，后续电缆册的数据处理将非常麻烦，电缆册的录入原则如下所示：

1）起始区域和终止区域分。上层建筑：AU、AA、AB、AC、……AN、AP（罗经甲板）；机舱：EF、……E2、E1、MSB、ECC；机舱棚：EC；管弄：ET；货舱：ON；艏部：BS；舵机舱：SG。

2）主干电缆册起始区域和终止区域的遵循原则。由高到低，先写高位后写低位，所有涉及到主配电板、应急配电板、驾控台、集控台、货控台等集中控制台的电缆，全部将配电板和台子写在起始位置。

3）其他区域均为横向分区，即一层甲板为一个区，只有机舱棚为纵向分区（跨层），所以在录入机舱棚的设备起终位置时，要写清在机舱棚的哪层甲板，并手动排好序。

4）主配电板、应急配电板、驾控台、集控台在起始位置录入时，统一写成“主配第几屏、应配第几屏、驾控台第几屏、集控台第几屏”，能够尽量控制行高，以减少电缆册页数。

5）系统图一列的录入，在不引起歧义的情况下应尽量简化，尽量控制在5个字以内，比如电力一次、照明、无线电等，这样可以尽量缩短行高，使每页显示行数更量多。

6）按如下设置电缆代号列“开始—样式—条件格式—突出显示单元格规则—重复值—确定”，这样设置后在录入电缆册时，系统会实时提示输入的电缆代号是否有重复，倘若不同系统图之间的虚实线电缆代号重复，待合并后客统一用此功能进行校核。

7）若要运行宏，需提前将Excel VBA的运行路径修改为与电缆册安放位置一致。

准备工作做好后，就可以进行电缆册的手动录入工作了。

跨区域电缆就是电缆的两端在不同区域，行走路径为跨区域敷设，属于主干电缆册内的电缆；非跨区域电缆就是电缆的两端都在同一个区域，属于分支电缆册内的电缆。

电缆册中每根电缆均由序号、所属系统、电缆代号、型号规格、长度、起始位置信息、终止位置信息、经过节点等主要参数构成。若要按区域划分电缆册，选取的数据应为起始区域和终止区域。

待所有电缆全部录完、长度测量好后，方可进行第一步编程操作。对于按照系统图顺序录入的电缆册而言，需重新按起始区域和终止区域进行排序。此操作很重要，所有主干电缆将全部按照排好顺序的总电缆册进行筛选，以便电缆厂家按层封装电缆辊以及造船部车间的按层拉放。起始和终止的位置均可分别按升序（ASC）或降序（DESC）的规则进行排列，程序如下：

其中，第一部分和第三部分都是Excel VBA中的通用语句，针对不同的电缆册和工作表，除了需要修改工作簿的完整路径和 sheet表名外，其余均无需改动。第二部分是核心SQL排序语句。第四部分为调整列宽语句。第五部分为设置字体，第六部分为自动换行语句。

在总表所在工作簿的sheet2中点击“开发工具”→“插入”→“按钮”，将新建按钮指定名为“test64000 sheet2”的宏→“确定”，点击此按钮，一个按起始位置和终止位置都升序的电缆册总表就自动生成了。

接着进行第二步操作。对同样型号规格的电缆进行汇总求和，以便后续电缆采购清单工作的进行，核心SQL语句如下。

由于人工录入可能出现电缆型号规格前有空格的情况，Excel会将带空格和不带空格的电缆分开统计，因此，需要先去掉所有单元格前的空格再进行求和，其余第一部分和第三部分的程序和排序程序一样。

进行第三步操作，按核心SQL查询语句分别自动生成主干电缆册工作簿和分支电缆册工作簿，以机舱上平台到机舱下平台和舱底为例，核心语句如下。

其余第1、第2、第4、第5、第6部分语句跟排序语句相同。对主干和分支工作簿中每张表进行重复上述添加按钮的操作，而后便可分别自动生成主干电缆册和分支电缆册。如图3所示。

图3 上建各层主干电缆册截图

对每张表进行添加边框，修改页面布局为 A3横向，设定好打印区域后，主干电缆册和分支电缆册便可制作完成了。

5 结束语

在语句完成后、进行导出主干和分支电缆册以及求和的过程中，可以发现很多问题，例如SQL查询语句方括号内sheet1$后面的范围不能省略、编程语句需在英文半角状态下输入、路径的设置不正确、程序复制过来后 sheet表的名称忘记修改、语句换行缺少“_”符号、求和后型号规格重复等。倘若程序按 F8逐行运行时未发生问题、但运行结果有问题时，大部分的问题还是出在源数据上，需要进行多次试验。由于其余语句都是固定模式，只有SQL查询语句需要重新编写，几个固定的方面（比如路径、sheet表名等）均需要重新修改，所以问题还是比较容易发现的。其余各程序还在陆续调试和使用中，部分程序也需要后续根据实际情况进行改进。