基于EXCEL函數(shù)的裝配BOM數(shù)據(jù)快速處理研究

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

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

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

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

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

BOM的概念

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

BOM的分類

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

1. DBOM

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

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

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

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

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

3. PBOM

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

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

4. MBOM

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

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

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

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

如圖1所示：從ERP上下載的原始BOM數(shù)據(jù)包含A-AU共計(jì)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ù)，點(diǎn)擊開始>查找和替換，打開對話框，在查找內(nèi)容中輸入空格+“-”，替換為中文本框中保持為空，單擊“全部替換”按鈕將空格+“-”全部替換為空，這樣就刪除了零件號中的無用數(shù)據(jù)，篩選E列，選中并刪除無用的原材料、毛坯等零件數(shù)據(jù)，這樣就完成了對BOM原始數(shù)據(jù)的清洗。利用VBA宏錄制以上這些操作，當(dāng)處理新的BOM時(shí)，就可以利用宏來一鍵處理BOM數(shù)據(jù)。

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

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

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

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

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

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

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

第四參數(shù)：精準(zhǔn)查詢用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)類似，大部分零件為通用件，只是有少部分零件型號的變化，這時(shí)候只需要制作裝配差異BOM，這樣在換產(chǎn)的時(shí)候?qū)τ谕ㄓ玫牧慵恍枰^多關(guān)注，就只需要關(guān)注差異件，減少了工作量，便于快速換產(chǎn)，提高生產(chǎn)效率。

裝配差異BOM左側(cè)為工位信息，按照工位做出各裝配工位的差異件明細(xì)，如圖3所示。由于BOM中除了標(biāo)準(zhǔn)件之外，零件的名稱都是唯一的，我們利用INDEX+MATCH函數(shù)，通過對零件名稱匹配、引用，可以將BOM中的數(shù)據(jù)快速導(dǎo)出至裝配差異BOM，使用函數(shù)導(dǎo)出準(zhǔn)確度高，不容易出錯(cuò)。下面介紹一下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，代表精準(zhǔn)查詢，運(yùn)算原理為在單行或單列單元格區(qū)域中查詢指定數(shù)據(jù)，找到指定數(shù)據(jù)后返回其在行區(qū)域或列區(qū)域中的相對位置，返回結(jié)果是數(shù)字。

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

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

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

結(jié)語

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