1. 什么是BOM？

用过ERP的小伙伴们都知道，BOM是ERP系统最重要的基础资料，是整个ERP系统有效运行的基石。BOM不仅是一种技术文件，还是一种管理文件，是企业各部门沟通的纽带和协作的基础。BOM这么重要，那么它到底是什么呢？

BOM-Bill of Material，即物料清单，是以数据格式来描述产品结构的文件，是计算机识别物料结构的基础。为了提高在ERP系统中构建BOM的效率，减少和预防错误，我们建议先用万能的Excel把BOM构建好，然后再输入ERP系统。那么具体怎么操作呢？我们一起来看看吧！

图 1 - BOM结构示意图

2. 准备料品档案

BOM的创建建立在准确规范的料品信息的基础上，故在构建BOM之前需要准备好料品档案。

对X成品及其用到的所有料品，根据既定的编码规则进行了编码（即料号）。料号是料品的唯一识别码，一个料号只能代表一种料品，一种料品也只能用一个料号表示，即料号与料品之间是一对一的关系。料品档案如图2所示。

图 2 - 料品档案

3. 构建BOM表

构建BOM表的第一步是要明确BOM结构及相应料品。X成品的BOM结构如图3所示。

图 3 - X成品的BOM结构图

前面说过，ERP系统中的BOM是用数据格式描述产品结构，故我们需要把上图转换成表格的形式。在表格中如何分辨BOM结构呢？这点小问题肯定难不倒机智的我！我们可以采用多级列表符号！

因料号是料品的唯一识别码，故每个料品可以直接用其料号来表示，初步的BOM结构表如图4所示。

图 4 - X成品的BOM结构表

4. 完善BOM表信息

但上面的BOM表不太直观，所以接下来就需要我们进一步完善BOM表信息，以增强BOM表的可读性。

通过VLOOKUP函数和MATCH函数的组合应用，可以根据“料号”从料品档案中返回对应的料品信息。例如C2单元格中根据“料号”返回“品名”的公式为：

=IFERROR(VLOOKUP($B2,料品档案!$A:$G,MATCH(C$1,料品档案!$1:$1,0),0),"")

VLOOKUP函数用于从料品档案中返回“料号”所对应的“品名”。MATCH函数用于定位“品名”列在料品档案中的位置。IFERROR函数是为了防止料品档案中无对应料号时返回错误值#N/A，即如果找不到对应料号，则返回空值。如图5所示。

图 5 - 根据“料号”返回“品名”的公式

接着，选中C2:H20区域，按快捷键“CTRL+R”向右填充，再按“CTRL+D”向下填充，则得到了从料品档案中返回的所有料品信息。对H列“损耗”设置百分比样式。如图6所示。

图 6 - 根据料号返回所有料品信息

BOM还要明确每个料品的用量和母件底数。如图7所示。

图 7 - 完善BOM信息

5. BOM结构分层

通过上述操作，我们已经基本完善了BOM表中的信息，现在需要对BOM表进行分层处理。在BOM中，成品是0层，成品的下一阶子件是1层，成品下一阶子件的下一阶子件是2层，依次类推。在BOM表的B列处，插入一列名为“层”。根据BOM结构可以确定料品所在的层数，在B2单元格输入公式如下：

=IF(ROW()-ROW(B$1)=1,0,LEN($A2)-LEN(SUBSTITUTE($A2,".",))+1)

成品位于第二行，属于0层。使用ROW函数确定成品的位置。除了0层外，上述公式根据结构中的点数量确定层数。公式LEN($A2)-LEN(SUBSTITUTE($A2,".",))先用SUBSTITUTE函数将结构中的点替换成空值，然后计算结构中的点数量，层数比点数量大1。如图8所示。

图 8 - BOM结构分层公式

选中B2:B20区域，按CTRL+D向下填充，则得到图9的BOM结构分层结果。

图 9 - BOM结构分层

6. BOM结构的分级显示

尽管设置了结构和分层，但是感觉BOM结构还不是很直观。下面我们通过分级显示，更直观地展示BOM结构。步骤如下：

1. 因为BOM是从上往下逐层分解的，故先点击“数据”菜单“分级显示”选项卡右下角的箭头，设置分级显示的方向。如图10。

图 10 - 设置分级显示1

2. 在弹出的“设置”窗口中，取消“明细数据的下方”前的勾并确定。如图11。

图 11 - 设置分级显示2

3. 选择3-20行，点击“分级显示”选项卡中的“组合”，或者直接按快捷键“Alt+Shift+→”。

4. 按同样方法分别对4-15行、18-20行、5-6行、8-12行、14-15行、11-12行进行组合操作。设置好的结构分级如图12所示。

图 12 - BOM结构的分级显示

5. 我们可以点击左上角的层级数来显示或隐藏相应的层级。如点击数字2，则只显示前2层，即0、1层。点击数字5，则显示所有层。如图13所示。

图 13 - 前2层的显示效果

6. 另外，还可以点击左边的“-”、“+”符号分别折叠、展开相应的层级（先点击“-”符号折叠之后才会显示“+”符号）。

7. 对层设置条件格式

设置了分级显示，是否还可以进一步优化BOM的效果呢？答案是肯定的。为了增强可视化效果，还可以对各层设置条件格式。步骤如下：

1. 选中A2:K20区域，点击“开始”菜单“样式”选项卡中的“条件格式”，选择“新建规则”。如图14所示。

图 14 - 新建规则

2. 在弹出的“新建格式规则”对话框中，选择“使用公式确定要设置格式的单元格”，设置公式如图15所示。

图 15 - 设置条件格式公式

3. 点击“格式”，弹出“设置单元格格式”对话框，在“填充”选项卡下选择自己喜欢的颜色并确定。如图16所示。

图 16 - 设置单元格格式

4. 这样，第1层结构的条件格式就设置好了。效果如图17所示。

图 17 - 第 1层的条件格式

5. 按照同样的方法分别对第2、3、4层设置条件格式（如图18）。

图 18 - 各层条件格式设置

6. 确定之后，效果如图19所示。

图 19 - 条件格式效果

7. 结合分级显示，如点击左上角的3，显示前三层（即0、1、2层）如图20所示。是不是更直观了呢？

图 20 - 前3层的条件格式效果

至此，一个可视化效果超级棒的BOM构建完毕。有没有觉得眼前一亮呢？

8. 总结

总结一下在BOM的构建过程中用到的函数：

1. VLOOKUP函数

2. MATCH函数

3. IFERROR函数

4. IF函数

5. ROW函数

6. LEN函数

7. SUBSTITUTE函数

此外，我们还用到了分级显示和基于公式的条件格式。虽然整个过程较为复杂，但是只要多练习，这些Excel技能也是不难掌握的。用好Excel，你会事半功倍！