【多比克文学,经典语录,心灵鸡汤,励志语录,正能量句子,www.dopic.net】

如此专业的BOM,用Excel就这么建!

专题: 哲思
作者:数字工厂 时间:2021-03-11 04:30:13  阅读:1460   网上投稿

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,你会事半功倍!

    相关美文阅读: