基于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数据高效且不易出错，大大提高了工作效率。