巧用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