巧用Excel表格制作動態(tài)全船鋼板用料清單

徐 建，蔣 華，韓云卿

(揚州大洋造船有限公司，江蘇 揚州 225107)

?

巧用Excel表格制作動態(tài)全船鋼板用料清單

徐 建，蔣 華，韓云卿

(揚州大洋造船有限公司，江蘇 揚州 225107)

為迅速匯總?cè)摪逵昧希ㄟ^運用Excel程序制作一個表格，使其具有按照實際所需自動生成全船鋼板用料清單的功能，并成為準軟件化的表格。經(jīng)實船應(yīng)用證明，該表格可以快速準確地完成全船訂貨，從而減少錯誤浪費問題，為有效提高鋼材利用率做好了堅實的基礎(chǔ)。

船用鋼板；用料清單；Excel表格；批次用料表

0 引言

全船鋼板用料清單是船廠用于鋼板訂貨的依據(jù)。目前，制作訂貨清單一般利用Excel軟件來完成。其方法為：首先將各個批次的用料表集中放在一個總表里，其次進行排序，然后運用Excel的匯總功能，最后得出全船的用料清單。該方法操作繁瑣，且不利于修改，一旦某個批次的用料表有修改，前面的步驟就要從頭到尾重新操作一遍。船舶建造過程中，設(shè)備的修改、各專業(yè)之間的協(xié)調(diào)、建模時發(fā)生的錯誤以及船東船檢意見等都會造成各批次材料規(guī)格、材質(zhì)或數(shù)量的修改，而鋼材的訂貨周期短，每條船的訂貨時間緊張，若要完成最終的全船訂貨清單，需要經(jīng)過多次修改、匯總，既花費大量時間，又容易出現(xiàn)錯誤遺漏，因此迫切需要制作能夠快速自動匯總?cè)摪逵昧系谋砀瘢蕴岣咴O(shè)計的質(zhì)量和設(shè)計的效率，更好滿足生產(chǎn)實際需求。本文根據(jù)原始表格數(shù)據(jù)，通過Excel程序，并運用其中的函數(shù)公式，使其能夠自動排序、索引、匯總，最終完成動態(tài)全船鋼板用料清單。

1 基本思路

制作準軟件化的Excel表格，主要從以下4方面進行探討：

(1)如何在Excel中構(gòu)思表格的制作，并且像專業(yè)應(yīng)用軟件那樣可以反復使用。

(2)能否在輸入原始數(shù)據(jù)后，實現(xiàn)自動計算，快速得到計算結(jié)果。

(3)能否在輸入原始數(shù)據(jù)的地方進行修改，并自動重新處理數(shù)據(jù)，快速得到新的計算結(jié)果。

(4)能否直觀地檢查可能發(fā)生的錯誤，避免數(shù)據(jù)錯亂，并對“準軟件化”的表格進行有效的保護，讓表格操作人員不能隨意更改表格中的函數(shù)公式和構(gòu)架。

針對以上問題，研究運用Microsoft Office Excel 2007或以上版本表格功能，將全船各批次鋼板用料表合并，通過Excel的各種函數(shù)計算，將各種板規(guī)按照材質(zhì)、板厚、板材寬度、板材長度自動進行排序，并將相同的規(guī)格和材質(zhì)的鋼板數(shù)量進行合并，最后列出全船所有板規(guī)的匯總結(jié)果，實現(xiàn)動態(tài)制作全船鋼板用料清單的功能。

2 制作過程

某6萬噸級散貨船大約有130個分段，每個分段作為獨立批次套料，每個批次套料完成后都會生成1個批次用料表，每個批次用料表約有二三十種板規(guī)，此表就是最原始的數(shù)據(jù)依據(jù)。本文假設(shè)某船共有3個批次，每個批次各有3種板規(guī)，將3個批次用料表放到一起，就作為鋼板原始用料清單，然后根據(jù)此表來制作該船的全船鋼板用料清單。鋼料原始用料清單見表1。本文中的板材規(guī)格、厚度、寬度、長度單位為mm，重量單位為kg。

2.1 根據(jù)功能需求劃分表格的4大區(qū)域

根據(jù)表1，新創(chuàng)建一個Excel表格，并將此表格劃分為4個大的區(qū)域，分別如下：

(1)原始數(shù)據(jù)區(qū)域：A～H列，共8列。表格的前4列為各批次用料表填寫區(qū)域，此處填寫各批次的鋼板用料表，為手工錄入或復制填入;后4列為自動生成。

表1 鋼板原始用料清單

(2)中間計算區(qū)域：I～AP列，共34列，此處是運用各種函數(shù)公式做排序、匯總等運算的區(qū)域。此區(qū)域不能填寫任何內(nèi)容，自動生成。

(3)按批次名排序區(qū)域：AQ～AX列，共8列。按批次名順序排列鋼板用料清單。 此區(qū)域不能填寫或修改任何內(nèi)容，自動生成。

(4)按板規(guī)排序區(qū)域：AY～BE列，共7列，按板規(guī)順序排列鋼板用料清單。 此區(qū)域不能填寫或修改任何內(nèi)容，自動生成。

下面就對各個區(qū)域逐個制作。

2.2 原始數(shù)據(jù)區(qū)域的制作

(1)在原始數(shù)據(jù)區(qū)域設(shè)置8列，表頭標題分別為批次、板材規(guī)格、材質(zhì)、數(shù)量、厚度、寬度、長度、重量(下文中的標題直接看表中的標題，不再在文中說明)，鋼板清單原始數(shù)據(jù)區(qū)域見表2。第A、B、C、D列為手工輸入部分，在這里手工輸入各個批次的批次名、板材規(guī)格、材質(zhì)及所需的數(shù)量。第E、F、G列則運用公式將板材規(guī)格分解為板厚、寬度和長度，第H列為總的板材重量。

(2)將表1的內(nèi)容填入到表2相應(yīng)的A、B、C、D 4列單元格中。在E2單元格填入公式“=IF(B2=0，""，VALUE(LEFT(B2，F(xiàn)IND("*"，B2，1)-1)))”將得到板材的厚度，F(xiàn)2單元格填入公式“=IF(B2=0，""，VALUE(MID(B2，F(xiàn)IND("*"，B2，1)+1，(FIND("*"，B2，F(xiàn)IND("*"，B2，1)+1)-FIND("*"，B2，1)-1))))”將得到板材的寬度數(shù)值，G2單元格填入公式“=IF(B2=0，""，VALUE(RIGHT(B2，LEN(B2)-FIND("*"，B2，F(xiàn)IND("*"，B2，1)+1))))”將得到板材的長度數(shù)值，H2單元格填入公式“=IF(B2=0，""，E2*F2*G2*N(D2)*7.85/1000000000)”將得到該板規(guī)的總重量。E、F、G、H 4列的其他單元格可通過Excel的拖曳復制功能，將表格復制到30行，使各單元格得到相應(yīng)的公式和數(shù)值。需要說明的是，本文中的公式均為該列中第2行單元格的公式，第3行向后的公式均使用Excel的拖曳功能復制完成。本文表格行數(shù)為10行，根據(jù)經(jīng)驗，實際操作時一般拖曳復制到5 000行可滿足需要。

至此，原始數(shù)據(jù)區(qū)域制作完成。

鋼板清單原始數(shù)據(jù)區(qū)域結(jié)果見表2。

表2 鋼板清單原始數(shù)據(jù)區(qū)域

2.3 中間計算區(qū)域的制作

從I列開始到AP列為中間計算區(qū)域。

I列為更正后板規(guī)列。由于原始板規(guī)數(shù)據(jù)里含有“*”號，容易造成運算時出錯，通過公式“=IF(B2=0，""，E2&"X"&F2&"X"&G2)”即可得到所需要的板規(guī)表示形式。這里的板規(guī)已經(jīng)不含有“*”，取代它的是字符“X”。

J列為材質(zhì)代碼列。常用的材質(zhì)有A、B、D、E、AH32、AH36、DH32、DH36、EH32、EH36等，需要用一個公式來將它們進行排序。通過公式“=MATCH(C2，{"A";"B";"D";"E";"AH32";"AH36";"DH32";"DH36";"EH36";"";0}，0)”就可以知道本行的材質(zhì)在順序中對應(yīng)的序號。例如材質(zhì)A對應(yīng)的就是1，材質(zhì)AH32對應(yīng)的就是5，AH36對應(yīng)的就是6。

K列為排序依據(jù)，通常的做法是通過多重條件語句來進行比較。運用if語句，先將材質(zhì)進行比較，如果不同就可以判斷大小；如果相同，那么就繼續(xù)比較板厚。不同則分出大小，相同就繼續(xù)比較寬度。以此類推，直到將每個板規(guī)能夠確定哪個在前哪個在后。此方法相當繁瑣，可以變通一下，運用公式“=((N(G2)+N(F2)*10000+N(E2)*1000000000+N(J2)*100000000000)+ROW(B2)/100)*100”，即將幾個需比較大小的參數(shù)代碼放置到一個15位的長整數(shù)中，前2位放置材質(zhì)代碼，3、4、5位為板厚代碼，6、7、8位為寬度代碼，9、10、11為長度代碼，12、13、14、15為行號代碼。由于數(shù)字間比較大小是先比較高位再比較低位，那么自然材質(zhì)代碼小的數(shù)值就小，材質(zhì)代碼大的數(shù)值就大，相同就會繼續(xù)比較下一位。這樣就可以按照想要的順序，給各種板規(guī)材質(zhì)確定大小順序，為下一步排序做好準備。

L列為相對位置列，公式為“=RANK(K2，$K$2:$K$30，1)”。通過此公式可將剛才的K列的數(shù)值進行排序，以確定該行的K值在該K列中的相對位置。例如單元格L2、L3、L4的值分別是1、4、6，說明K2、K3、K4的值在K列中的順序分別為第1、第4、第6。

M列為序號列。為了拖曳復制公式的方便，將用公式“=ROW(B1)”表示(注:如果這里不用此公式,而是直接填序號1,當發(fā)生拖曳操作進行復制時,序號不會按行增加,始終是1)，結(jié)果見表3。

表3 按板規(guī)、材質(zhì)排序的相對位置

N～U共8列，是通過Excel的INDEX函數(shù)的索引功能將A～H這8列重新進行排序，將最初的原始用料表按照所需要的材質(zhì)→厚度→寬度→長度順序重新進行了排列，結(jié)果見表4。

表4 重新排序后的結(jié)果

V～AC列是用于合并的列，即將同一批次中相同的板規(guī)和材質(zhì)的進行合并，以確保同一批次中不存在板規(guī)、材質(zhì)同時都相同的情況。假設(shè)在初始的某個批次的用料表中，有一種板規(guī)和材質(zhì)同時出現(xiàn)了2次或2次以上，且沒有在用料表中及時發(fā)現(xiàn)，那么在這里就會自動的合并，以確保后面的行列在同一批次中不會出現(xiàn)板規(guī)和材質(zhì)同時相同的情況，以避免造成表格的錯誤運算。

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列的運算可得到不同批次、不同板規(guī)和材質(zhì)的相對位置，從而為后面的以批次單位為順序來排序做準備。

按批次排序的相對位置其結(jié)果見表5。

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

AG至AP這10列與V至AF這11列基本類似，只是去除了批次信息，使全船中只要板規(guī)材質(zhì)相同的合并匯總到一起，而不考慮批次名是否相同，以此確定該行在后面以板規(guī)順序來排序時的相對位置，見表6。

至此，中間運算區(qū)域的表格完成。

表6 按板規(guī)和材質(zhì)排序的相對位置

2.4 按批次排序區(qū)域的制作

中間運算區(qū)域的表格制作完畢后，接著需要生成2種所要用的表格，一個是以批次單位為順序的材料用料清單，將放置在按批次排序區(qū)域；一個是以材料規(guī)格為順序的用料清單，將放置在按板規(guī)排序區(qū)域。

按批次排列區(qū)域運用INDEX函數(shù)的索引功能。

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函數(shù)的column參數(shù)，將V至AC列的數(shù)據(jù)按相對位置中的值進行升序排列，得出的結(jié)果就是按批次排列的鋼板用料清單，生成的清單見表7。

2.5 按材質(zhì)和板規(guī)排序區(qū)域的制作

同理，繼續(xù)運用INDEX函數(shù)的索引功能，將AG～AN列的數(shù)據(jù)按材質(zhì)和板規(guī)順序進行升序排列，得出的結(jié)果就是按照板規(guī)排列的鋼板用料清單，其生成的清單見表8。

以上就是制作動態(tài)全船用料清單的全過程。為了防止誤操作，導致表格的結(jié)構(gòu)和公式遭到破壞，可以將表格進行保護。由于該表格中A、B、C、D 4列為人工輸入?yún)^(qū)域，通過單元格設(shè)置將此區(qū)域設(shè)置為不鎖定，也就是將鎖定項前的選擇框里的選鉤去掉，而將后面所有的自動運算的列都設(shè)置為鎖定，然后將部分不需要顯示的列隱藏，再通過選單選擇審閱→保護工作表，這樣就可以清晰直觀地顯示結(jié)果，并有效地避免數(shù)據(jù)錯亂，到此就完成了設(shè)計需要的“準軟件化”的動態(tài)表格。

3 使用方法

由于制作動態(tài)全船鋼板用料清單整個過程除了原始數(shù)據(jù)需手工填入，其他部分均為自動生成，因此操作人員只需將單個批次的用料表手工填入或復制到本表格的原始數(shù)據(jù)區(qū)域中，即可自動生成鋼板用料清單。另外，還可以對已完成的訂貨清單表格進行修改。例如因設(shè)備修改造成船體結(jié)構(gòu)發(fā)生變化從而使某個批次的板規(guī)、數(shù)量需要修改的，這時只要在原始數(shù)據(jù)區(qū)前4列中進行增、減、修改等操作，修改后的全船鋼板用料清單就能自動完成更新，整個流程簡單、快捷、方便。

表7 鋼板用料清單(按批次排序)

表8 鋼板用料清單(按材質(zhì)和板規(guī)排列)

4 結(jié)語

制作完成此動態(tài)全船用料清單表格后，依次錄入各批次用料清單到此Excel表格，由表格自動生成全船的訂貨清單，包括按批次排列的訂貨清單和按板規(guī)、材質(zhì)排序的訂貨清單，再交由采購部門進行訂貨。當然，還可以運用Excel程序?qū)υ摫砀襁M行優(yōu)化，例如通過設(shè)置軟件自帶的條件格式功能將輸入的數(shù)據(jù)進行識別、檢驗，找出輸入錯誤，再比如增加合并板規(guī)區(qū)域來自動合并相近板規(guī)等功能。通過幾個項目的實際使用，充分展示了該表格方便快捷、功能強大，極大地提高了工作效率和質(zhì)量。

[1] 傅靖.Excel 2007中文版VBA開發(fā)技術(shù)大全[M].北京：電子工業(yè)出版社，2008.

2015-12-04

徐建(1972—)，男，工程師，從事船舶與海洋工程研究。

U673.2

A