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

徐 建，蔣 華，韓云卿

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

?

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

徐 建，蔣 華，韓云卿

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

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

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

0 引言

全船鋼板用料清單是船廠用于鋼板訂貨的依據(jù)。目前，制作訂貨清單一般利用Excel軟件來完成。其方法為：首先將各個批次的用料表集中放在一個總表里，其次進(jìn)行排序，然后運用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 基本思路

制作準(zhǔn)軟件化的Excel表格，主要從以下4方面進(jìn)行探討：

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

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

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

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

針對以上問題，研究運用Microsoft Office Excel 2007或以上版本表格功能，將全船各批次鋼板用料表合并，通過Excel的各種函數(shù)計算，將各種板規(guī)按照材質(zhì)、板厚、板材寬度、板材長度自動進(jìn)行排序，并將相同的規(guī)格和材質(zhì)的鋼板數(shù)量進(jìn)行合并，最后列出全船所有板規(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ū)域，此處填寫各批次的鋼板用料表，為手工錄入或復(fù)制填入;后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列，表頭標(biāo)題分別為批次、板材規(guī)格、材質(zhì)、數(shù)量、厚度、寬度、長度、重量(下文中的標(biāo)題直接看表中的標(biāo)題，不再在文中說明)，鋼板清單原始數(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的拖曳復(fù)制功能，將表格復(fù)制到30行，使各單元格得到相應(yīng)的公式和數(shù)值。需要說明的是，本文中的公式均為該列中第2行單元格的公式，第3行向后的公式均使用Excel的拖曳功能復(fù)制完成。本文表格行數(shù)為10行，根據(jù)經(jīng)驗，實際操作時一般拖曳復(fù)制到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等，需要用一個公式來將它們進(jìn)行排序。通過公式“=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ù)，通常的做法是通過多重條件語句來進(jìn)行比較。運用if語句，先將材質(zhì)進(jìn)行比較，如果不同就可以判斷大??；如果相同，那么就繼續(xù)比較板厚。不同則分出大小，相同就繼續(xù)比較寬度。以此類推，直到將每個板規(guī)能夠確定哪個在前哪個在后。此方法相當(dāng)繁瑣，可以變通一下，運用公式“=((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ì)確定大小順序，為下一步排序做好準(zhǔn)備。

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

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

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

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

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

V～AC列是用于合并的列，即將同一批次中相同的板規(guī)和材質(zhì)的進(jìn)行合并，以確保同一批次中不存在板規(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)”將批次順序進(jìn)行排序。

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

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

通過這3列的運算可得到不同批次、不同板規(guī)和材質(zhì)的相對位置，從而為后面的以批次單位為順序來排序做準(zhǔn)備。

按批次排序的相對位置其結(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ù)按相對位置中的值進(jìn)行升序排列，得出的結(jié)果就是按批次排列的鋼板用料清單，生成的清單見表7。

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

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

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

3 使用方法

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

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

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

4 結(jié)語

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

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

2015-12-04

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

U673.2

A