如斯专业的BOM用Excel就这么建

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，你会事半功倍！