基于Excel VBA執(zhí)行SQL語句對船用電纜冊進行設(shè)計優(yōu)化

常 亮，汪家政

（舟山長宏國際船舶修造有限公司，浙江舟山 316052）

0 引言

電纜冊是為某一船型的系列船在保持設(shè)備訂貨和船體結(jié)構(gòu)不變的情況下，根據(jù)全船電氣系統(tǒng)圖量身定制的全船電纜清單。該清單包含系統(tǒng)分類、電纜代號、電纜型號規(guī)格、電纜起始點和終止點位置、電纜長度等信息。

電纜冊按走向區(qū)域分為主干電纜冊（跨區(qū)域）[1]和分支電纜冊（不跨區(qū)域）。

目前，舟山長宏國際船舶修造有限公司（以下簡稱“公司”）散貨船和集裝箱船基本都是自主生產(chǎn)設(shè)計的。公司要求主干電纜中35 mm2以下的電纜要提前預(yù)裁好，以便車間施工拉放。因此，將電纜冊總表分成主干電纜冊和分支電纜冊。

1 設(shè)計背景

截止2018年5月，中國活躍的船廠數(shù)量僅剩110多家，一個民營船廠如何在全球船市低迷、眾多船廠倒閉的大背景下繼續(xù)生存下去，是所有造船人每天都在思考的問題。“打鐵還需自身硬”，必須提高我們的核心競爭力，盡量提高每個環(huán)節(jié)的工作效率、縮短造船周期，保質(zhì)保量地完成設(shè)計和生產(chǎn)任務(wù)，是每個船人應(yīng)該擔(dān)當(dāng)?shù)呢?zé)任和義務(wù)。

目前，國內(nèi)大中型國有船企和大型民營船廠中，大多采用 Tribon、Catia、Foran、東欣等主流造船3D放樣軟件進行生產(chǎn)設(shè)計建模和出圖。公司將利用內(nèi)部強大的技術(shù)、人力、資金等資源，對軟件進行二次開發(fā)，自動生成電纜冊。

但對于更多的中小型船廠、民營船企以及小型船舶設(shè)計院來講，一個軟件動輒幾百、上千萬，還要投入大量的人力和資金進行二次開發(fā)和后期維護，難免得不償失。有的軟件雖然有自動生成電纜冊的功能，但苦于公司沒有多余的人力去按照軟件的要求逐一錄入。同時，3D軟件的其他限制條件，也制約了3D放樣軟件自動生成電纜冊。若需從其他大公司購買，不但需要一筆很大的開支，而且大公司的數(shù)據(jù)管理都在其保密數(shù)據(jù)庫中，具有自主知識產(chǎn)權(quán)，無法直接搬移來用。

目前為止，大部分中小船企和小型設(shè)計公司的電纜冊還是以手動錄入、人工測量為主，經(jīng)錄入和測量的總電纜冊還要根據(jù)需要，分成適合造船部門使用的若干電纜分冊，這部分工作也相當(dāng)繁瑣。同時，人工在對包含少則四五千根、多則上萬根電纜的Excel總表進行數(shù)據(jù)處理時，極易出現(xiàn)錯誤、重復(fù)、漏線、錯行等各種問題；下發(fā)車間后，一經(jīng)發(fā)現(xiàn)，還要對其進行反復(fù)修改以保證后續(xù)船舶的生產(chǎn)任務(wù)能夠有序進行，所以這個問題長期困擾著電裝生產(chǎn)設(shè)計人員。如何實現(xiàn)排序、篩選、匯總、求和等數(shù)據(jù)的自動處理功能，讓人力和時間從大量的數(shù)據(jù)處理煩惱中解脫出來，迫在眉睫。本文主要對手動錄入、人工測量的總電纜冊進行自動排序、篩選、求和等功能進行了講解。

2 電纜冊制作流程

電纜冊的制作流程如下：

1）根據(jù)全船電氣系統(tǒng)圖（下發(fā)版），按系統(tǒng)進行電纜冊的錄入工作，并合并為一個總表。

2）完成電裝生產(chǎn)設(shè)計3D建模，并完成電裝圖、節(jié)點圖的出圖流程。

3）根據(jù)安裝圖和節(jié)點圖，結(jié)合電氣系統(tǒng)圖和布置圖，對每根電纜進行測量工作。

4）將測量好的總表按需分成預(yù)裁電纜的主干電纜冊和不需預(yù)裁的分支電纜冊。

在船舶的整個造船流程中，電纜冊的制作和出圖時間前后都遭受制約。前期受系統(tǒng)圖等詳細設(shè)計圖紙、電裝節(jié)點圖等生產(chǎn)設(shè)計圖紙的制約，后期又受上建區(qū)域小合攏、機艙貨艙首部合攏時間的制約。目前，中國的造船接單狀態(tài)還是以滿足船東交付時間為主。由交船向開工（按照工藝流程）倒推時間節(jié)點，這樣的流程留給船廠自主生產(chǎn)設(shè)計的時間就相當(dāng)緊迫。而按照合攏時間確定好電纜的到廠時間后，再倒推45天作為電纜廠家生產(chǎn)電纜和預(yù)裁主干的時間，剩下留給船廠設(shè)計部制作電纜冊的時間，基本都在2個月以內(nèi)。這么短的時間，要手動錄入并測量至少四五千根電纜，工作量相當(dāng)大。所以如何縮短電纜冊的制作時間，是小型船企和小型船舶設(shè)計公司亟待解決的問題。

3 構(gòu)建數(shù)據(jù)模型

圖1是典型散貨船的電纜冊劃分區(qū)域圖，機艙區(qū)域分為EF艙底、E2機艙下平臺、E1機艙上平臺等；上建區(qū)域分為AU主甲板、AA A甲板、AB B甲板、AC C甲板、AN駕駛甲板等，除此之外還有EC機艙棚區(qū)域、SG舵機艙區(qū)域、ON貨艙區(qū)域、ET管弄區(qū)域、BS艏部區(qū)域等。其他集裝箱船、油輪化學(xué)品船等均可按區(qū)域的劃分進行調(diào)整，并根據(jù)實船增減區(qū)域。本文僅以公司為例進行講解，其他船廠或設(shè)計公司可以根據(jù)自身需求進行修改，例如電纜冊的劃分原則、是否預(yù)裁、區(qū)域劃分等。船用電纜冊的制作屬于數(shù)據(jù)庫中的層次模型（見圖2）。

圖1 電裝及電纜冊制作區(qū)域劃分圖

圖2 電纜冊數(shù)據(jù)建模

4 電纜冊制作過程

由于電纜冊的制作是基于Excel VBA編程技術(shù)對數(shù)據(jù)進行統(tǒng)計分析，以Excel 2007為例，首先通過“office按鈕”→“Excel選項”→“信任中心”→“宏設(shè)置”來啟用宏，并將手工錄入的總電纜冊另存為“啟用宏的工作簿“*.xlsm文件”（“*”為電纜冊名字，文中的電纜冊名字為“64000電纜冊”）。在“office按鈕”→“常用”→勾選“在功能區(qū)顯示‘開發(fā)工具’選項卡”。

有些基本要求在錄入電纜冊時需要遵循一定原則，不能隨便寫，如果所建船舶工期太緊，很有可能兩三個人同時進行電纜冊的錄入工作，倘若不規(guī)定原則，后續(xù)電纜冊的數(shù)據(jù)處理將非常麻煩，電纜冊的錄入原則如下所示：

1）起始區(qū)域和終止區(qū)域分。上層建筑：AU、AA、AB、AC、……AN、AP（羅經(jīng)甲板）；機艙：EF、……E2、E1、MSB、ECC；機艙棚：EC；管弄：ET；貨艙：ON；艏部：BS；舵機艙：SG。

2）主干電纜冊起始區(qū)域和終止區(qū)域的遵循原則。由高到低，先寫高位后寫低位，所有涉及到主配電板、應(yīng)急配電板、駕控臺、集控臺、貨控臺等集中控制臺的電纜，全部將配電板和臺子寫在起始位置。

3）其他區(qū)域均為橫向分區(qū)，即一層甲板為一個區(qū)，只有機艙棚為縱向分區(qū)（跨層），所以在錄入機艙棚的設(shè)備起終位置時，要寫清在機艙棚的哪層甲板，并手動排好序。

4）主配電板、應(yīng)急配電板、駕控臺、集控臺在起始位置錄入時，統(tǒng)一寫成“主配第幾屏、應(yīng)配第幾屏、駕控臺第幾屏、集控臺第幾屏”，能夠盡量控制行高，以減少電纜冊頁數(shù)。

5）系統(tǒng)圖一列的錄入，在不引起歧義的情況下應(yīng)盡量簡化，盡量控制在5個字以內(nèi)，比如電力一次、照明、無線電等，這樣可以盡量縮短行高，使每頁顯示行數(shù)更量多。

6）按如下設(shè)置電纜代號列“開始—樣式—條件格式—突出顯示單元格規(guī)則—重復(fù)值—確定”，這樣設(shè)置后在錄入電纜冊時，系統(tǒng)會實時提示輸入的電纜代號是否有重復(fù)，倘若不同系統(tǒng)圖之間的虛實線電纜代號重復(fù)，待合并后客統(tǒng)一用此功能進行校核。

7）若要運行宏，需提前將Excel VBA的運行路徑修改為與電纜冊安放位置一致。

準(zhǔn)備工作做好后，就可以進行電纜冊的手動錄入工作了。

跨區(qū)域電纜就是電纜的兩端在不同區(qū)域，行走路徑為跨區(qū)域敷設(shè)，屬于主干電纜冊內(nèi)的電纜；非跨區(qū)域電纜就是電纜的兩端都在同一個區(qū)域，屬于分支電纜冊內(nèi)的電纜。

電纜冊中每根電纜均由序號、所屬系統(tǒng)、電纜代號、型號規(guī)格、長度、起始位置信息、終止位置信息、經(jīng)過節(jié)點等主要參數(shù)構(gòu)成。若要按區(qū)域劃分電纜冊，選取的數(shù)據(jù)應(yīng)為起始區(qū)域和終止區(qū)域。

待所有電纜全部錄完、長度測量好后，方可進行第一步編程操作。對于按照系統(tǒng)圖順序錄入的電纜冊而言，需重新按起始區(qū)域和終止區(qū)域進行排序。此操作很重要，所有主干電纜將全部按照排好順序的總電纜冊進行篩選，以便電纜廠家按層封裝電纜輥以及造船部車間的按層拉放。起始和終止的位置均可分別按升序（ASC）或降序（DESC）的規(guī)則進行排列，程序如下：

其中，第一部分和第三部分都是Excel VBA中的通用語句，針對不同的電纜冊和工作表，除了需要修改工作簿的完整路徑和 sheet表名外，其余均無需改動。第二部分是核心SQL排序語句。第四部分為調(diào)整列寬語句。第五部分為設(shè)置字體，第六部分為自動換行語句。

在總表所在工作簿的sheet2中點擊“開發(fā)工具”→“插入”→“按鈕”，將新建按鈕指定名為“test64000 sheet2”的宏→“確定”，點擊此按鈕，一個按起始位置和終止位置都升序的電纜冊總表就自動生成了。

接著進行第二步操作。對同樣型號規(guī)格的電纜進行匯總求和，以便后續(xù)電纜采購清單工作的進行，核心SQL語句如下。

由于人工錄入可能出現(xiàn)電纜型號規(guī)格前有空格的情況，Excel會將帶空格和不帶空格的電纜分開統(tǒng)計，因此，需要先去掉所有單元格前的空格再進行求和，其余第一部分和第三部分的程序和排序程序一樣。

進行第三步操作，按核心SQL查詢語句分別自動生成主干電纜冊工作簿和分支電纜冊工作簿，以機艙上平臺到機艙下平臺和艙底為例，核心語句如下。

其余第1、第2、第4、第5、第6部分語句跟排序語句相同。對主干和分支工作簿中每張表進行重復(fù)上述添加按鈕的操作，而后便可分別自動生成主干電纜冊和分支電纜冊。如圖3所示。

圖3 上建各層主干電纜冊截圖

對每張表進行添加邊框，修改頁面布局為 A3橫向，設(shè)定好打印區(qū)域后，主干電纜冊和分支電纜冊便可制作完成了。

5 結(jié)束語

在語句完成后、進行導(dǎo)出主干和分支電纜冊以及求和的過程中，可以發(fā)現(xiàn)很多問題，例如SQL查詢語句方括號內(nèi)sheet1$后面的范圍不能省略、編程語句需在英文半角狀態(tài)下輸入、路徑的設(shè)置不正確、程序復(fù)制過來后 sheet表的名稱忘記修改、語句換行缺少“_”符號、求和后型號規(guī)格重復(fù)等。倘若程序按 F8逐行運行時未發(fā)生問題、但運行結(jié)果有問題時，大部分的問題還是出在源數(shù)據(jù)上，需要進行多次試驗。由于其余語句都是固定模式，只有SQL查詢語句需要重新編寫，幾個固定的方面（比如路徑、sheet表名等）均需要重新修改，所以問題還是比較容易發(fā)現(xiàn)的。其余各程序還在陸續(xù)調(diào)試和使用中，部分程序也需要后續(xù)根據(jù)實際情況進行改進。