CN107633353B - 基于excel表vba的物料bom表合规检查方法 - Google Patents

Abstract

本发明公开了一种基于EXCEL表VBA的物料BOM表合规检查方法，包括将BOM表导入EXCEL表中并进行整理，将表头调整为工序，序号，代号，ERP编码，物品描述，数量，备注；修正整理后的BOM表，划定表头的类属区；对工序类属区和序号类属区进行填充；对代号类属区和ERP编码类属区的内容进行查对，对物品描述类属区内容与公司制定的模件设计检查表规则进行查对；将上述内容以VBA代码进行实现，封装到EXCEL表中，并使用XML制作EXCEL快捷菜单并关联到这些VBA代码。本发明有效的避免上述错误的发生，并减轻了硬件设计人员的负担，最终可以提高生产制造的效率。

Description

基于EXCEL表VBA的物料BOM表合规检查方法

技术领域

本发明涉及一种基于EXCEL表VBA的物料BOM表合规检查方法，属于物料清单管理技术领域。

背景技术

对于硬件开发来说，设计新模件是一个必不可少的环节，而模件BOM又是联通设计和生产的纽带，模件BOM(物料清单)的正确与否直接影响着生产制造的速度和效率，甚至关系到此批模件试制的成败。

录入BOM是精细而繁琐的工作，传统录入BOM的方法是：从原理图或PCB中导出BOM表，这个BOM表的格式与生产时用的BOM表有较大的差异，不能直接用于生产，需要重新整理核对，根据不同的焊装模式进行删减或补充，不同的设计软件导出的BOM表之间的差异也是比较大的。录入BOM很容易出现错误，常见的错误有：1)器件代号和数量不一致；2)遗漏器件(尤其是数目较多的阻容类器件)；3)多余器件(不需要装焊的器件，体现在BOM中)；4)器件与实际PCB封装不符；5)选用了一些不推荐使用的器件；6)缺少器件代号；7)器件代号错误或重复；8)工序录入不正确；9)缺少必要的备注信息。究其原因是在录入BOM的过程当中人为干预的因素太多。要想解决这个问题，首先就要排除人在整个过程当中的干扰。

另外，录入后的手动修改原理图或PCB中导出的BOM表耗时较长，且缺乏工具校验，容易出现的问题也比较多，例如：

1、由于不同的焊装模式而修改BOM造成的位号实际数量与BOM中填写的数量不一致，这可能是修改位号时漏删或者多删造成的，也可能是数量没有修改。

2、容易遗漏器件，导出的BOM中不会存在诸如短接块、连接器插头、SFP光口或铜塑线等物料信息的，这些往往需要人为添加。如果物料是线缆的话，数量有可能会填写错误，例如，数量填写为2，但线缆物料的单位往往是“米/卷”，那此时就变成此模件使用了2卷线，这是不可能的。

3、存在一些由于复制/粘贴带来的诸如位号重复，ERP编码重复等问题。

4、由于现有的ERP编码系统中存在相同或相似物料不同码的问题(可能由于厂家不同或重复录入导致)，这样在导出的BOM表中存在使用了不同ERP编码的相同或相似的物料。例如由于ERP编码不同，在导出的BOM表中有3个地方都使用了1KOhm，0603封装的电阻。带来的麻烦是需要采购3种物料，焊接的时候需要上3次料。实际上，这3种物料是可以互相替代的，只需要将他们合并为一种物料来处理的。手动合并可能会遗漏或合并过程出现错误等问题。

5、导出的物料不包含序号(默认序号间隔为10，起始序号为200)及工序，需要手动添加，有的物料工序，不能直接从物品描述中直接判别出来。

6、对于BOM表中序号重复的问题排查比较困难。

7、对于BOM表中ERP编码重复的问题排查比较困难。

8、手动修改BOM表的时候，容易遗漏印制板的ERP编码。

9、检查每种物料的合规性比较高繁琐，例如排查那些已经禁止使用的物料。

10、有些物料是需要填写备注的，例如需要指明插装电阻是否需要抬高，抬多高；使用的线缆是否需要裁剪成小段等。

发明内容

本发明所要解决的技术问题是克服现有技术的缺陷，提供一种基于EXCEL表VBA的物料BOM表合规检查方法，基于EXCEL的VBA来对BOM的录入进行检查替代人为检查，可以有效的减轻硬件设计人员的负担，最终提高生产制造的效率。

为解决上述技术问题，本发明提供一种基于EXCEL表VBA的物料BOM表合规检查方法，包括以下步骤：

1)将Cadence/Altium Designer导出的BOM表导入EXCEL表中并进行整理，将表头调整为工序，序号，代号，ERP编码，物品描述，数量和备注共七项；

2)修正整理后的BOM表，划定表头的类属区，包括工序类属区，序号类属区，代号类属区，ERP编码类属区，物品描述类属区，数量类属区和备注类属区；

3)对工序类属区和序号类属区进行填充；

4)对代号类属区和ERP编码类属区的内容进行查对，对物品描述类属区内容与公司制定的模件设计检查表规则进行查对；

5)将所述步骤1)至4)以VBA代码进行实现，封装到EXCEL表中，并使用XML制作EXCEL快捷菜单并关联到这些VBA代码；

6)查对完成后，将警告信息填写在备注类属区。

前述的步骤1)中，整理的步骤如下：

1-1)把Cadence/Altium Designer导出CSV格式的BOM表导入EXCEL表中，获取EXCEL表格工作区，然后获取工作区行数；

1-2)在表头中搜索“Quantity”、“Description”、“Part Number”、“PartReference”这四个关键字所在的列，并删除其余的列；

1-3)把表头中的“Part Reference”修改为“代号”，并把此列移动到第1列，把表头中的“Part Number”修改为“ERP编码”，并把此列移动到第2列，把表头中的“Description”修改为“物品描述”，并把此列移动到第3列，把表头中的“Quantity”修改为“数量”，并把此列移动到第4列；

1-4)在第4列后插入一列，表头命名为“备注”，在第1列前插入两列，表头分别命名为“工序”和“序号”。

前述的步骤2)中，修正过程中，首先需要把整个EXCEL表格的数据转化为UTF-8的格式。

前述的步骤2)中，划定表头的类属区的方法为：扫描表头，将每一项所在的列，从第2行开始到工作区的最末行就设置为该项的类属区。

前述的步骤2)中，划定类属区后，需要去除“代号类属区”内多余的空格及首尾多余的逗号，然后把“物品描述类属区”内的英文字符全部转换为大写，把全角字符转化为半角字符。

前述的步骤3)中，对工序类属区进行填充的方法为：以“ERP编码类属区”中当前行的ERP编码在公司网络器件库中搜索到相应的器件项目，然后获取器件封装，判断封装类型，如果器件封装为贴装，则对应的“工序类属区”中填写10，如果器件封装为插装，则对应的“工序类属区”中填写20，如果器件为金工件，则对应的“工序类属区”中填写30。

前述的步骤3)中，对序号类属区进行填充的方法为：从210开始填写的，相邻行间隔为10。

前述的步骤4)中，对代号类属区进行查对的方法为：首先检查“代号类属区”中当前行内的代号是否重复，如果存在重复，则使用红色标记重复的代号；然后检查“代号类属区”中不同行间的代号是否重复，如果存在重复，则使用蓝色标记重复的代号；最后，获取“代号类属区”中每一项包含的代号数量，然后与相应的“数量类属区”中的数值进行比对，如果不相等则在“数量类属区”中的数值后面标明代号的实际个数，以“/”分割。

前述的步骤4)中，对ERP编码类属区进行查对的方法为：检查“ERP编码类属区”中是否存在空白项目，需要检查“ERP编码类属区”中是否存在相同的项目。

前述的步骤4)中，对物品描述类属区内容与公司制定的模件设计检查表规则进行查对的方法为：

4-1)对“物品描述类属区”的数据进行关键字提取，提取的关键字组成数组：【器件类型，焊接类型，封装类型，物料的值，物料的精度，物料的电压等级，物料的电流等级，物料的功率】，数组中，如果不包含某项参数，则其值为空；

4-2)对于电阻或电容器件的比对，如果关键字数组中器件类型，焊接类型，物料的值这3个关键字相同，则比对的物料为能够合并的相似物料，则用同种颜色进行标记；

4-3)对照公司制定的模件设计检查表来检查所使用的物料是否符合公司制定的设计规范；

4-4)对于非电阻，非电容器件的比对，提取的关键字里面至少包含“器件类型”这一项，然后，检查物料是否符合模件设计检查表里面的规定。

本发明所达到的有益效果：

本发明采用基于EXCEL的VBA设计针对于BOM的录入检查工具来替代人为检查，有效的避免上述错误的发生，并减轻了硬件设计人员的负担，最终可以提高生产制造的效率。

本发明最终生成的目标BOM表格式与企业ERP系统中的BOM表格式相同，便于录入ERP系统。

附图说明

图1为导入的BOM表整理过程流程图；

图2为修正BOM表过程流程图；

图3为各个类属区域示意图；

图4为填补BOM表过程流程图；

图5为核对代号数量示意图；

图6为物料描述类属区的关键字数组。

具体实施方式

下面对本发明作进一步描述。以下实施例仅用于更加清楚地说明本发明的技术方案，而不能以此来限制本发明的保护范围。

Cadence/Altium Designer等原理图(PCB)绘制软件导出的BOM表格式都是可以设置为固定格式的，这些固定的格式都包含特定不变的表头字段，除去表头其余行都为物料信息，Cadence/Altium Designer导出的BOM表如表1所示。

表1Cadence/Altium Designer导出的原器件BOM表头部片段

对BOM表进行合规检查主要包括整理、修正、填补、查对这四个过程。整理过程主要是对导入的BOM表格进行格式化，获取BOM表的一些基本信息，包括表格的行列数，所需要字段的位置等。修正过程主要是修正一些可确定的错误，并把表格内容中的全角字符转换为半角字符，格式化表格内容为UTF-8格式，所有字符转化为大写等。填补过程主要是补充每项物料的序号，补充每项的工序。查对过程包括对所需每一列中每行内容间的查对；对所需每一列中每行的内容与公司制定的模件设计检查表规则进行查对。最后给出查对结果及建议修改的具体内容，以便指导硬件设计人员快速的定位所在的问题行，并进行修正。整个合规检查过程完全自动化，不需要人为干预，这样可以有效的避免遗漏和错误的产生，极大的提高了工作效率。各过程的具体操作方法如下：

一、整理

如图1所示，整理过程，首先需要把Cadence/Altium Designer导出CSV格式的BOM表导入EXCEL表中，然后获取表格中的有效数据区域(表格工作区)，然后获取工作区行数，因为表头仅占1行，所以工作区行数减1就为实际有效的物料行数。物料行数会在后面用于确认各个类属工作区。接下来查找表头，搜索“Quantity”、“Description”、“Part Number”、“Part Reference”这四个关键字所在的列，并删除其余的列。把表头中的“PartReference”修改为“代号”，并把此列移动到第1列，把表头中的“Part Number”修改为“ERP编码”，并把此列移动到第2列，把表头中的“Description”修改为“物品描述”，并把此列移动到第3列，把表头中的“Quantity”修改为“数量”，并把此列移动到第4列。接下来在第4列后插入一列，表头命名为“备注”，在第1列前插入两列，表头分别命名为“工序”和“序号”，至此导入的BOM表整理完毕，整理后的BOM表符合ERP系统的BOM格式。表2为整理后的表头。

表2整理后的原器件BOM表头部片段

二、修正

如图2所示的修正过程，为了便于后续对数据进行查找和对比，首先，把整个表格的数据转化为UTF-8的格式，然后扫描表头，找到“工序”所在的列，那么此列从第2行开始到工作区的最末行(工作区内容不为空的最后一行)就设置为“工序类属区”，如图3所示。据此分别设定“序号类属区”，“代号类属区”，“ERP编码类属区”，“物品描述类属区”，“数量类属区”和“备注类属区”。至此，后面就可以很方便的对相应的类属区进行操作。在处理各个类属区之前还要先去除“代号类属区”内多余的空格及首尾多余的逗号等干扰符号，然后把“物品描述类属区”内的英文字符全部转换为大写，把全角字符转化为半角字符，以便后续的查找和查对操作。

三、填补

如图4所示的填补过程，是对“序号类属区”和“工序类属区”进行填充，“序号类属区”是按顺序填充的，由于序号200为PCB板项目专属序号，Cadence/Altium Designer软件导出的BOM中又不会包含PCB板项目，所以序号是从210开始填写的，按照要求初次填写序号间隔为10，那么“序号类属区”会填入210，220，230……这样的数据，直到最后。填写“工序类属区”需要以“ERP编码类属区”中当前行的ERP编码在公司网络器件库中搜索到相应的器件项目，然后获取器件封装，判断封装类型，如果器件封装为贴装，则对应的“工序类属区”中填写10，如果器件封装为插装，则对应的“工序类属区”中填写20，如果器件为金工件，则对应的“工序类属区”中填写30。

四、查对

PCB板上的器件代号一般是不会重复的，毕竟一个器件不会焊接在两个位置上。这样就需要检查“代号类属区”内的代号是否存在重复，查重包括两个步骤，第一个步骤是检查“代号类属区”中当前行内的代号是否重复，如果存在重复，则使用红色标记重复的代号；第二个步骤是检查“代号类属区”中不同行间的代号是否重复，如果存在重复，则使用蓝色标记重复的代号。在整个BOM核查完毕后，设计人员可以根据这些标记来决定是否要去除重复的代号。

接下来获取“代号类属区”中每一项包含的代号数量，然后与相应的“数量类属区”中的数值进行比对，如果不相等则在“数量类属区”中的数值后面标明代号的实际个数，以“/”分割。具体参见图5。

Cadence/Altium Designer中如果使用新的元器件，一般是不存在ERP编码的，所以需要检查“ERP编码类属区”中是否存在空白项目，如果存在空白项目则需要告知设计者填入该元件的ERP编码。然后还需要检查“ERP编码类属区”中是否存在相同的项目，以防止填写时重复录入相同的ERP编码。

在对物品描述进行检查之前，需要对“物品描述类属区”的数据进行关键字提取。关键字数组格式如图6所示，首先根据“ERP编码类属区”中对应的ERP编码的前4个字符来筛选出电阻、电容等，再根据物品种类(电阻/电容)提取物品描述中的安装方式、封装信息、器件各个属性值(阻值/容值，电压值，精度值)。例如对物料“陶瓷电容:SMT-0603-10nF/10％/50V/X7R(RoHS)”进行关键字提取的结果是["DE09","SMT","0603","10000pF","0.1","50V","",""]，为了方便书写，这里用方括号表示数组。数组中的第1个元素“DE09”为该物料的ERP编码前4个字符，用来标识器件类型为陶瓷电容；数组中的第2个元素“SMT”表示焊接类型为表贴封装；数组中的第3个元素“0603”为该物料的封装；数组中的第4个元素“10000pF”为该物料的值，这个值为最小单位值，电阻最小单位单位为mΩ，电容最小单位单位为pF，这样对比会比较方便；数组中的第5个元素“0.1”为该物料的精度；数组中的第6个元素“50V”为该物料的电压等级；数组中的第7个元素“”为该物料的电流等级，此处为空值，因为此物料不包含此参数；数组中的第8个元素“”为该物料的功率，此处为空值，因为此物料不包含此参数，此处仅用于电阻。

“物品描述类属区”数据的关键字提取完成后，就可以对物料进行比对分析了。通过器件类型，焊接类型，封装类型，物料的值这4个值来进行相似物料的比对分析。

对于电阻(电容)器件的判据为：器件类型，焊接类型，物料的值这3个关键字相同的话就认为比对的物料为可以合并的相似物料。可以合并的相似物料会用同种颜色进行标记。例如“DD0500000124贴片电阻:4.7K+/-5％0805封装(RoHS)”和“DD0500000138贴片电阻:4.7K+/-1％0603封装(RoHS)”两种物料提取关键字后分别为["DD05","贴片","0805","4700000","0.5","","",""]和["DD05","贴片","0603","4700000","0.1","","",""]，经过对比这两种物料就是可以合并的两种物料，毕竟如果仅仅是用于信号上拉，这两种物料是完全可以合并的。当然，如果有什么特殊的用途，也可以不进行合并。而电容器件“DE0900000733陶瓷电容:SMT-0603-100nF/10％/50V/X7R(RoHS)”和“DE0900001241陶瓷电容:SMT-0805-0.1μF/5％/25V/X5R(RoHS)”两种物料提取关键字后分别为["DE09","SMT","0603","100000","0.1","50V","",""]和["DE09","SMT","0805","100000","0.05","25V","",""]，经过对比这两种物料就是可以合并的两种物料，毕竟如果仅仅是用于滤波，这两种物料也是可以考虑合并的。当然，如果有什么特殊的用途，也可以不进行合并。是否真的需要合并，还需要设计者在检查结束后确认。

在拥有各个物料的关键字后，可以进一步对照公司制定的模件设计检查表来检查所使用的物料是否符合公司制定的设计规范。例如模件设计检查表中规定禁止使用表贴金属钽电容，以及慎用1206封装的电容等信息，就可以通过对比来检查BOM中有使用这样的非推荐物料，并给出警告，以便检查结束后，设计人员进行处理。

对于除去电阻(电容)器件的其他物料来说，提取的关键字里面至少会包含“器件类型”这一项，对于这些物料来讲，检查物料是否符合模件设计检查表里面的规定已经足够了。例如模件设计检查表中规定使用线缆物料时要检查物料的数量，因为线缆的单位一般为“米/卷”，而Cadence/Altium Designer导出的BOM中没有这样的单位，都是以“个”为单位的，所以这里就涉及到单位的换算，设计师往往在此处很容易忘记换算，对此处检查以便提醒设计师。另外模件设计检查表中规定如果使用插装电阻，需要在BOM表的备注栏里面标明电阻焊接时是否需要做抬高处理，这项检查也可以通过“器件类型”的判别来进行查对。所有检查后的警告信息都会写在备注后面的一列中。

整个查对过程意在说明查对的方式是通过提取物料关键字来组成2维关键字数组，然后根据这个2维关键字数组来进行物料的各种比对的。此处就不再一一列举每一项检查的具体内容。

最后将上述的各个功能部分以VBA代码进行实现后，封装到EXCEL表中，并使用XML制作EXCEL快捷菜单并关联到这些VBA代码，以方便设计人员使用。

以上所述仅是本发明的优选实施方式，应当指出，对于本技术领域的普通技术人员来说，在不脱离本发明技术原理的前提下，还可以做出若干改进和变形，这些改进和变形也应视为本发明的保护范围。

Claims (7)

1.基于EXCEL表VBA的物料BOM表合规检查方法，其特征在于，包括以下步骤：

1)将Cadence/Altium Designer导出的BOM表导入EXCEL表中并进行整理，将表头调整为工序，序号，代号，ERP编码，物品描述，数量和备注共七项；

2)修正整理后的BOM表，划定表头的类属区，包括工序类属区，序号类属区，代号类属区，ERP编码类属区，物品描述类属区，数量类属区和备注类属区；

3)对工序类属区和序号类属区进行填充；

4)对代号类属区和ERP编码类属区的内容进行查对，对物品描述类属区内容与公司制定的模件设计检查表规则进行查对；

对代号类属区进行查对的方法为：首先检查“代号类属区”中当前行内的代号是否重复，如果存在重复，则使用红色标记重复的代号；然后检查“代号类属区”中不同行间的代号是否重复，如果存在重复，则使用蓝色标记重复的代号；最后，获取“代号类属区”中每一项包含的代号数量，然后与相应的“数量类属区”中的数值进行比对，如果不相等则在“数量类属区”中的数值后面标明代号的实际个数，以“/”分割；

对ERP编码类属区进行查对的方法为：检查“ERP编码类属区”中是否存在空白项目，需要检查“ERP编码类属区”中是否存在相同的项目；

对物品描述类属区内容与公司制定的模件设计检查表规则进行查对的方法为：

4-1)对“物品描述类属区”的数据进行关键字提取，提取的关键字组成数组：【器件类型，焊接类型，封装类型，物料的值，物料的精度，物料的电压等级，物料的电流等级，物料的功率】，数组中，如果不包含某项参数，则其值为空；

4-2)对于电阻或电容器件的比对，如果关键字数组中器件类型，焊接类型，物料的值这3个关键字相同，则比对的物料为能够合并的相似物料，则用同种颜色进行标记；

4-3)对照公司制定的模件设计检查表来检查所使用的物料是否符合公司制定的设计规范；

4-4)对于非电阻，非电容器件的比对，提取的关键字里面至少包含“器件类型”这一项，然后，检查物料是否符合模件设计检查表里面的规定；5)将所述步骤1)至4)以VBA代码进行实现，封装到EXCEL表中，并使用XML制作EXCEL快捷菜单并关联到这些VBA代码；

6)查对完成后，将警告信息填写在备注类属区。

2.根据权利要求1所述的基于EXCEL表VBA的物料BOM表合规检查方法，其特征在于，所述步骤1)中，整理的步骤如下：

1-1)把Cadence/Altium Designer导出CSV格式的BOM表导入EXCEL表中，获取EXCEL表格工作区，然后获取工作区行数；

1-2)在表头中搜索“Quantity”、“Description”、“Part Number”、“Part Reference”这四个关键字所在的列，并删除其余的列；

1-3)把表头中的“Part Reference”修改为“代号”，并把此列移动到第1列，把表头中的“Part Number”修改为“ERP编码”，并把此列移动到第2列，把表头中的“Description”修改为“物品描述”，并把此列移动到第3列，把表头中的“Quantity”修改为“数量”，并把此列移动到第4列；

1-4)在第4列后插入一列，表头命名为“备注”，在第1列前插入两列，表头分别命名为“工序”和“序号”。

3.根据权利要求1所述的基于EXCEL表VBA的物料BOM表合规检查方法，其特征在于，所述步骤2)中，修正过程中，首先需要把整个EXCEL表格的数据转化为UTF-8的格式。

4.根据权利要求1所述的基于EXCEL表VBA的物料BOM表合规检查方法，其特征在于，所述步骤2)中，划定表头的类属区的方法为：扫描表头，将每一项所在的列，从第2行开始到工作区的最末行就设置为该项的类属区。

5.根据权利要求4所述的基于EXCEL表VBA的物料BOM表合规检查方法，其特征在于，所述步骤2)中，划定类属区后，需要去除“代号类属区”内多余的空格及首尾多余的逗号，然后把“物品描述类属区”内的英文字符全部转换为大写，把全角字符转化为半角字符。

6.根据权利要求1所述的基于EXCEL表VBA的物料BOM表合规检查方法，其特征在于，所述步骤3)中，对工序类属区进行填充的方法为：以“ERP编码类属区”中当前行的ERP编码在公司网络器件库中搜索到相应的器件项目，然后获取器件封装，判断封装类型，如果器件封装为贴装，则对应的“工序类属区”中填写10，如果器件封装为插装，则对应的“工序类属区”中填写20，如果器件为金工件，则对应的“工序类属区”中填写30。

7.根据权利要求1所述的基于EXCEL表VBA的物料BOM表合规检查方法，其特征在于，所述步骤3)中，对序号类属区进行填充的方法为：从210开始填写的，相邻行间隔为10。

Images