張志睿 楊發(fā)祿 張吉民

摘要? 介紹了BOM的概念、作用及分類，運用EXCEL的VBA宏功能對ERP上下載的BOM原始數(shù)據(jù)進行清洗，刪除無用數(shù)據(jù)，保留有用數(shù)據(jù)。根據(jù)BOM中零件被使用的工序及放置的裝配工位建立數(shù)據(jù)庫，當(dāng)遇到新的BOM時，運用VLOOKUP函數(shù)可以在數(shù)據(jù)庫中快速查找引用零件所在的工位，生成裝配工位BOM，運用INDEX+MATCH函數(shù)可以快速查找引用出不同工位的差異零件，導(dǎo)出裝配差異BOM，裝配工位BOM與裝配差異BOM被用于指導(dǎo)現(xiàn)場裝配生產(chǎn)與快速換產(chǎn)，借助EXCEL的VBA宏功能及函數(shù)對裝配BOM數(shù)據(jù)進行處理，不僅準確度高，而且大幅提升了工作效率。

關(guān)鍵詞? ?BOM、EXCEL、VBA宏、VLOOKUP、INDEX+MATCH、裝配工位BOM、裝配差異BOM

在實際裝配生產(chǎn)過程中，裝配工藝工程師往往需要對ERP系統(tǒng)上下載的BOM進行一系列數(shù)據(jù)處理，數(shù)據(jù)處理工作包括刪除無用數(shù)據(jù)、保留有用數(shù)據(jù)、確定每種零件使用于哪道工序以及所在的裝配工位，通過對BOM進行數(shù)據(jù)處理，生成符合裝配現(xiàn)場作業(yè)所需的裝配工位BOM以及裝配差異BOM。

由于OFFICE辦公軟件EXCEL擁有強大且豐富的數(shù)據(jù)處理功能，借助EXCEL的VBA宏功能及函數(shù)可以準確高效的實現(xiàn)對BOM數(shù)據(jù)的精細化快速處理，大幅提高工作效率，進而可以更加準確高效地指導(dǎo)現(xiàn)場裝配生產(chǎn)，實現(xiàn)快速換產(chǎn)。

BOM的概念

BOM（Bill of Material）物料清單，是產(chǎn)品結(jié)構(gòu)的技術(shù)性描述文件，它表明了產(chǎn)品組件、子件、零件、直到原材料之間的結(jié)構(gòu)關(guān)系，以及每個組裝件所需要的各下屬部件的數(shù)量。也就是以數(shù)據(jù)格式來描述產(chǎn)品結(jié)構(gòu)的文件，是計算機可以識別的產(chǎn)品結(jié)構(gòu)數(shù)據(jù)文件，也是ERP（Enterprise Resource Planning）企業(yè)資源計劃系統(tǒng)的主導(dǎo)文件。BOM使系統(tǒng)識別產(chǎn)品結(jié)構(gòu)，也是聯(lián)系與溝通企業(yè)各項業(yè)務(wù)的紐帶。

BOM的分類

實際上BOM是一個廣泛的概念，根據(jù)不同的用途，BOM有許多種類；設(shè)計圖紙上的BOM，計劃BOM，計算最終產(chǎn)品裝配的制造BOM，計算成本的成本BOM，保養(yǎng)維修BOM等。根據(jù)在不同階段應(yīng)用側(cè)重點不同，我們常常見到不同的BOM提法，常見的有四種，分別是DBOM、EBOM、PBOM、MBOM。

1. DBOM

DBOM（Design BOM）：設(shè)計BOM，這個可以理解為原始的產(chǎn)品設(shè)計圖紙以及零件是如何組裝成部件或者成品的。設(shè)計部門的DBOM是產(chǎn)品的總體信息，對應(yīng)常見文本格式表現(xiàn)形式包括產(chǎn)品明細表、圖樣目錄、材料定額明細表等等。

設(shè)計BOM信息來源一般是設(shè)計部門提供的成套設(shè)計圖紙中標題欄和明細欄信息。有時候也涉及工藝部門編制的工藝卡片上的部分信息。

設(shè)計BOM一般在設(shè)計結(jié)束時匯總產(chǎn)生，如果存在大量借用關(guān)系的設(shè)計情況可以在設(shè)計階段開始就基本將設(shè)計BOM匯總出來，然后根據(jù)新產(chǎn)生的零部件安排設(shè)計任務(wù)。

對應(yīng)電子視圖往往是產(chǎn)品結(jié)構(gòu)樹的形式，樹上每個節(jié)點關(guān)聯(lián)各類屬性或圖形信息。主要在PDM（Product Data Management）產(chǎn)品數(shù)據(jù)管理軟件中作為產(chǎn)品管理和圖檔管理的基礎(chǔ)數(shù)據(jù)出現(xiàn)。

2. EBOM

EBOM（Engineering BOM）：工程BOM，這個是在DBOM的基礎(chǔ)上，增加了工藝要求以及質(zhì)量要求等。產(chǎn)品工程設(shè)計管理中使用的數(shù)據(jù)結(jié)構(gòu)，它通常精確地描述了產(chǎn)品的設(shè)計指標和零件與零件之間的設(shè)計關(guān)系。對應(yīng)文件形式主要有產(chǎn)品明細表、圖樣目錄、材料定額明細表及產(chǎn)品各種分類明細表等。EBOM通常僅限于圖紙零件明細表出現(xiàn)的物料，說明圖紙的層次和從屬關(guān)系，做好技術(shù)文檔管理，雖然也有指導(dǎo)采購和估算報價的功能，但主要是為了管理圖紙。

EBOM主要是工程設(shè)計部門產(chǎn)生的數(shù)據(jù)，產(chǎn)品設(shè)計人員根據(jù)客戶訂單或者設(shè)計要求進行產(chǎn)品設(shè)計，生成包括產(chǎn)品名稱、產(chǎn)品結(jié)構(gòu)、明細表、匯總表、產(chǎn)品使用說明書和裝箱清單等信息，這些信息大部分包括在EBOM中。EBOM 是工藝、制造等后續(xù)部門的其它應(yīng)用系統(tǒng)所需產(chǎn)品數(shù)據(jù)的基礎(chǔ)。

3. PBOM

PBOM（Plan BOM）：計劃BOM，是工藝工程師根據(jù)工廠的加工水平和能力，對EBOM再設(shè)計出來的。它用于工藝設(shè)計和生產(chǎn)制造管理，使用它可以明確地了解零件與零件之間的制造關(guān)系，跟蹤零件是如何制造出來的，在哪里制造、由誰制造、用什么制造等信息。同時，PBOM也是 MRPⅡ/ERP生產(chǎn)管理的關(guān)鍵管理數(shù)據(jù)結(jié)構(gòu)之一。

PBOM是工藝設(shè)計部門以EBOM中的數(shù)據(jù)為依據(jù)，制定工藝計劃、工序信息、生成計劃BOM的數(shù)據(jù)。計劃BOM是由普通物料清單組成的，只用于產(chǎn)品的預(yù)測，尤其用于預(yù)測不同的產(chǎn)品組合而成的產(chǎn)品系列，有時是為了市場銷售的需要，有時是為了簡化預(yù)測計劃從而簡化了主生產(chǎn)計劃。另外，當(dāng)存在通用件時，可以把各個通用件定義為普通型BOM，然后由各組件組裝成某個產(chǎn)品，這樣一來各組件可以先按預(yù)測計劃進行生產(chǎn)，下達的PBOM 產(chǎn)品可以很快進行組裝，滿足市場要求。

4. MBOM

MBOM（Manufacturing BOM）：制造BOM，這個是在DBOM的基礎(chǔ)上，表明了零件是怎么生產(chǎn)出來的，或者是從哪里購買的，是制造部門根據(jù)己經(jīng)生成的PBOM，對工藝裝配步驟進行詳細設(shè)計后得到的，主要描述了產(chǎn)品的裝配順序、工時定額、材料定額以及相關(guān)的設(shè)備、刀具、夾具和模具等工裝信息，反映了零件、裝配件和最終產(chǎn)品的制造方法和裝配順序，反映了物料在生產(chǎn)車間之間的合理流動和消失過程。PBOM和MBOM也是提供給計劃部門（ERP）的關(guān)鍵管理數(shù)據(jù)之一。

基于EXCELVBA宏對BOM數(shù)據(jù)清洗

從ERP上下載的MBOM里面有許多無用的數(shù)據(jù)，零件號里面還有許多無用的空格和“-”，這樣的BOM無法直觀的用于指導(dǎo)現(xiàn)場生產(chǎn)。因此具體到某一條產(chǎn)線，還需要對其進行進一步處理。首先要對BOM數(shù)據(jù)進行清洗，去除臟數(shù)據(jù)，保留有用的數(shù)據(jù)。由于這類工作往往是重復(fù)的，且BOM數(shù)據(jù)的格式固定，因此非常適合采用Excel 宏來對數(shù)據(jù)進行處理。

Excel軟件的一個強大優(yōu)勢在于它提供的宏語言Visual Basicfor Application（VBA），VBA可以廣泛地應(yīng)用于Microsoft公司開發(fā)的各種軟件中。Excel把一步步的操作稱為宏。宏的應(yīng)用主要在于“記錄宏”，“記錄宏”僅記錄操作結(jié)果，而不記錄操作過程。宏錄制存儲之后就可以“執(zhí)行宏”，此時，Excel自動將所錄制的操作過程全部操作。

如圖1所示：從ERP上下載的原始BOM數(shù)據(jù)包含A-AU共計47列數(shù)據(jù)，多數(shù)數(shù)據(jù)與現(xiàn)場生產(chǎn)無關(guān)，我們只需要保留B、C、E、M、R五列數(shù)據(jù)，刪除其它列數(shù)據(jù)，B列中的物料信息中含有多余的空格和“-”，選中B列數(shù)據(jù)，點擊開始>查找和替換，打開對話框，在查找內(nèi)容中輸入空格+“-”，替換為中文本框中保持為空，單擊“全部替換”按鈕將空格+“-”全部替換為空，這樣就刪除了零件號中的無用數(shù)據(jù)，篩選E列，選中并刪除無用的原材料、毛坯等零件數(shù)據(jù)，這樣就完成了對BOM原始數(shù)據(jù)的清洗。利用VBA宏錄制以上這些操作，當(dāng)處理新的BOM時，就可以利用宏來一鍵處理BOM數(shù)據(jù)。

基于VLOOKUP函數(shù)導(dǎo)出裝配工位BOM

裝配工位BOM就是在經(jīng)過宏處理完的BOM數(shù)據(jù)基礎(chǔ)上，確定BOM中的每種零件在哪個工位使用，具體到每一條產(chǎn)線，需要關(guān)注的是每種零件用于哪道工序及哪個工位，當(dāng)遇到一個新的BOM，如何快速確定BOM中的零件用于哪個工位？處理思路是這樣的，對于一條生產(chǎn)線來說，除了標準件之外，某一種零件用于哪個裝配工位是固定的，這樣就可以針對這個生產(chǎn)線建立一個所有零件匯總的數(shù)據(jù)庫，這個數(shù)據(jù)庫包含零件所使用的裝配工位信息，如圖2所示，當(dāng)遇到新的BOM時，利用VLOOKUP函數(shù)，匹配零件號，從數(shù)據(jù)庫中就可以快速提取出工位信息。

這里使用的是VLOOKUP函數(shù)精準查詢的功能，下面介紹一下VLOOKUP函數(shù)在此種用法下的語法結(jié)構(gòu)：

VLOOKUP（查找值，查找區(qū)域，返回值在查找區(qū)域所處的列數(shù)，0）

第一參數(shù)：查找值，即按什么條件查找。此案例要求按員工編號查詢，所以第一參數(shù)為“N2”。

第二參數(shù)：查找區(qū)域，即在哪個區(qū)域中進行查詢。要求查找區(qū)域中的最左列要包含第一參數(shù)的查找值，右側(cè)列中要包含需要返回的數(shù)據(jù)。

第三參數(shù)：返回值在查找區(qū)域所處的列數(shù)，即公式要返回的結(jié)果在第二參數(shù)的查找區(qū)域中的第幾列。

第四參數(shù)：精準查詢用0或邏輯值FALSE，模糊查詢用非0或邏輯值TRUE，如果第四參數(shù)省略不寫也是模糊查詢。

基于INDEX、MATCH函數(shù)導(dǎo)出裝配差異BOM

現(xiàn)在的裝配生產(chǎn)線往往是柔性的生產(chǎn)線，每天生產(chǎn)的品種有幾種甚至十幾種，如果生產(chǎn)的產(chǎn)品結(jié)構(gòu)類似，大部分零件為通用件，只是有少部分零件型號的變化，這時候只需要制作裝配差異BOM，這樣在換產(chǎn)的時候?qū)τ谕ㄓ玫牧慵恍枰^多關(guān)注，就只需要關(guān)注差異件，減少了工作量，便于快速換產(chǎn)，提高生產(chǎn)效率。

裝配差異BOM左側(cè)為工位信息，按照工位做出各裝配工位的差異件明細，如圖3所示。由于BOM中除了標準件之外，零件的名稱都是唯一的，我們利用INDEX+MATCH函數(shù)，通過對零件名稱匹配、引用，可以將BOM中的數(shù)據(jù)快速導(dǎo)出至裝配差異BOM，使用函數(shù)導(dǎo)出準確度高，不容易出錯。下面介紹一下INDEX、MATCH函數(shù)在此種用法下的語法結(jié)構(gòu)：

INDEX函數(shù)可以提取單列數(shù)據(jù)中指定行位置的數(shù)據(jù)，這種用法下的語法結(jié)構(gòu)為：

INDEX（單列區(qū)域，第幾行）。

MATCH函數(shù)可以查詢指定數(shù)據(jù)在一列數(shù)據(jù)中的相對位置。

MATCH函數(shù)的常用語法結(jié)構(gòu)如下：

MATCH（指定數(shù)據(jù)，單行或單列區(qū)域，0）。

MATCH函數(shù)的第三參數(shù)經(jīng)常用0，代表精準查詢，運算原理為在單行或單列單元格區(qū)域中查詢指定數(shù)據(jù)，找到指定數(shù)據(jù)后返回其在行區(qū)域或列區(qū)域中的相對位置，返回結(jié)果是數(shù)字。

INDEX+MATCH函數(shù)組合是Excel中非常經(jīng)典的一對數(shù)據(jù)查詢組合，可以用于從各種區(qū)域中按條件提取目標數(shù)據(jù)。

將處理完的BOM作為數(shù)據(jù)源，和裝配差異BOM放到EXCEL的同一個工作簿中的兩個工作表中，制作裝配差異BOM時的函數(shù)使用格式為：INDEX（數(shù)據(jù)源BOM！$A：$C，MATCH（“零件名稱”，BOM！$C：$C，0），2）。

如圖3所示，當(dāng)建立好一個裝配差異BOM模板，遇到新的BOM時，只需要將BOM數(shù)據(jù)粘貼到數(shù)據(jù)源BOM工作表中，就能自動生成新的裝配差異BOM。

結(jié)語

利用EXCEL的VBA宏功能以及VLOOKUP、INDEX等一些函數(shù)可以高效的對BOM數(shù)據(jù)進行處理，生成適用于裝配生產(chǎn)線的BOM數(shù)據(jù)表，文中的裝配工位BOM及裝配差異BOM就是BOM數(shù)據(jù)表的另一種形式，利用EXCEL的VBA宏功能及函數(shù)處理BOM數(shù)據(jù)高效且不易出錯，大大提高了工作效率。