基于EXCEL函數的裝配BOM數據快速處理研究

張志睿 楊發祿 張吉民

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

關鍵詞? ?BOM、EXCEL、VBA宏、VLOOKUP、INDEX+MATCH、裝配工位BOM、裝配差異BOM

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

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

BOM的概念

BOM（Bill of Material）物料清單，是產品結構的技術性描述文件，它表明了產品組件、子件、零件、直到原材料之間的結構關系，以及每個組裝件所需要的各下屬部件的數量。也就是以數據格式來描述產品結構的文件，是計算機可以識別的產品結構數據文件，也是ERP（Enterprise Resource Planning）企業資源計劃系統的主導文件。BOM使系統識別產品結構，也是聯系與溝通企業各項業務的紐帶。

BOM的分類

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

1. DBOM

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

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

設計BOM一般在設計結束時匯總產生，如果存在大量借用關系的設計情況可以在設計階段開始就基本將設計BOM匯總出來，然后根據新產生的零部件安排設計任務。

對應電子視圖往往是產品結構樹的形式，樹上每個節點關聯各類屬性或圖形信息。主要在PDM（Product Data Management）產品數據管理軟件中作為產品管理和圖檔管理的基礎數據出現。

2. EBOM

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

EBOM主要是工程設計部門產生的數據，產品設計人員根據客戶訂單或者設計要求進行產品設計，生成包括產品名稱、產品結構、明細表、匯總表、產品使用說明書和裝箱清單等信息，這些信息大部分包括在EBOM中。EBOM 是工藝、制造等后續部門的其它應用系統所需產品數據的基礎。

3. PBOM

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

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

4. MBOM

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

基于EXCELVBA宏對BOM數據清洗

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

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

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

基于VLOOKUP函數導出裝配工位BOM

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

這里使用的是VLOOKUP函數精準查詢的功能，下面介紹一下VLOOKUP函數在此種用法下的語法結構：

VLOOKUP（查找值，查找區域，返回值在查找區域所處的列數，0）

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

第二參數：查找區域，即在哪個區域中進行查詢。要求查找區域中的最左列要包含第一參數的查找值，右側列中要包含需要返回的數據。

第三參數：返回值在查找區域所處的列數，即公式要返回的結果在第二參數的查找區域中的第幾列。

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

基于INDEX、MATCH函數導出裝配差異BOM

現在的裝配生產線往往是柔性的生產線，每天生產的品種有幾種甚至十幾種，如果生產的產品結構類似，大部分零件為通用件，只是有少部分零件型號的變化，這時候只需要制作裝配差異BOM，這樣在換產的時候對于通用的零件不需要過多關注，就只需要關注差異件，減少了工作量，便于快速換產，提高生產效率。

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

INDEX函數可以提取單列數據中指定行位置的數據，這種用法下的語法結構為：

INDEX（單列區域，第幾行）。

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

MATCH函數的常用語法結構如下：

MATCH（指定數據，單行或單列區域，0）。

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

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

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

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

結語

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