SAP实施中Excel函数公式在BOM批量导入中的应用

[摘 要] ERP系统实施过程中，企业数据如何迅速转换成实施顾问需要的形式，是一个目前尚无太多研究和讨论的领域，但对该领域的忽略造成了数据转换工作一直以低水平低效率的状态在各个项目中进行，给数据批量导入乃至系统上线带来很多隐患。本文以sap系统实施中，BOM数据的转换为例，采用逐步展示图表的方式，详细阐述了如何利用excel函数公式，设计出高效、快捷、准确转换的自动化模板，迅速将多层bom自动转换成顾问需要的单层BOM形式，从而满足了批量导入在格式、时间及准确度方面的要求。

[关键词] SAP；Excel函数；BOM；批量导入

doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2014 . 04. 022

[中图分类号] TP391 [文献标识码] A [文章编号] 1673 - 0194（2014）04- 0038- 03

BOM（Bill of Material）也叫物料清单，是企业生产运营中最重要的资料之一，它说明产品是如何“做出来”的，包涵了企业产品从原材料到最终产成品的各种状态下所有物料的信息，包括物料名称、数量、材质以及物料上下级关系。BOM是运行ERP（Enterprise Resource Planning）内部集成系统的主导文件，企业各个业务部门都要依据统一的BOM进行工作。

1 BOM的内涵

在缺乏ERP系统支持的情况下，企业通常采用办公软件作为BOM的载体，例如Microsoft的Excel或Word。前者由于其简单易用，普及大众化，并具备基本的统计功能，可以进行初步的物料统计，从而得到了众多企业的青睐。为企业在“前ERP时代”的生产运营起到了不可或缺的重要作用。

图1是一份简化的BOM范例，真正的BOM包含更多内容。尤其是制造业，产品结构层次复杂的大型设备制造商将不得不面对动辄上千行的BOM清单。MTO（Make to Order）类型的制造商，更是比MTS（Make to Stock）类型的制造商情况复杂，因其所有产品均为客户量身定制，产品之间各不相同，每个产品都拥有一个BOM，数据量之大可以想象。

因此，当企业进行ERP 系统的实施时，如此复杂繁多的BOM数据能否及时、准确地导入，甚至会成为系统能否按时上线的一个重要影响因素。

2 实例

以SAP为例，实施顾问在项目初期采取批量导入的方法，用标准格式Excel模板来收集BOM数据，一次性将所有数据上传到系统。但该标准格式Excel模板严格遵循SAP系统能够识别的逻辑设计格式，因此模板采取单层BOM的表达方式描述零件之间的逻辑层次关系，见表1①

这种单层BOM表达方式与企业现有的多层次BOM完全不一致，不再是一层层展开直到最下面的原材料，而是每行只描述两个零件之间的关系，也就是单层表达方式。企业员工多年来已经习惯了多层次的表达方式，对于这种不能直观看出层次结构的单层表达方法十分困惑。

更令人沮丧的是，即使员工弄清楚了该模板数据间的逻辑关系，如何将现有的多层次BOM转换为顾问所要求的单层次BOM，也就是说如何从图1转换到表1，绝对是件颇费脑筋的事。是否应该手工逐行输入？如何保证效率和准确性？对于每个产品就要上千行的企业，是否也要手工逐行输入？这无疑是一场噩梦，终将影响到项目整体进程。

企业员工遇到该问题时，首先会想到咨询外部实施顾问，希望能获得更专业的支持和帮助。然而遗憾的是，SAP实施顾问对此却无能为力。因为甲乙双方在这一点上的工作范围划分是：顾问提供BOM模板，企业按模板提供BOM数据，顾问负责将符合模板形式的数据上传到系统。至于企业方面如何完成从原始BOM结构到顾问要求的BOM结构，则不是外部实施顾问的工作范畴。所以即使实施顾问在其他企业的实施项目中也发生了类似的问题，却因为该问题不在其工作范围内，从而没有给这个问题更多关注，或者说，注意到了该问题的存在却没有对此进行深入的研究，更不用说经验的积累和传递。而且目前市面及网络上各种有关SAP实施方法论的书籍、期刊也都没有对类似问题的研究讨论。

3 解决上线瓶颈问题

本文作者曾在某SAP实施项目中，创造性地利用Excel函数公式解决了这个问题，利用嵌套函数公式，设计出高效、快捷、准确转换的自动化模板，迅速将多层BOM自动转换成顾问需要的单层BOM形式，从而满足了批量导入在格式、时间及准确度方面的要求。极大地缩短了BOM数据批量导入时间，解决了系统上线的瓶颈问题。

以下为详细说明。

3.1 确保原始BOM表物料号完整

首先检查原始BOM表是否已经赋予了最新的SAP物料号。在这之前，物料编码工作应已经按照唯一性，即一物一号的原则编制完成，并已建立物料库。如果原始BOM表尚未更新对应的SAP物料号，则用Vlookup函数将BOM中各个零件所对应的SAP物料号找出来②。C4单元格内的公式是：

=VLOOKUP（B4，■H■2：■I■21，2，0）

3.2 在顾问提供模板中选择基准物料号

观察图1与表1的异同，发现图1即企业原始BOM只有一列物料号，而表1即顾问提供的模板要求有两列物料号，一列是母件，另一列是其组件。由于母件可能包含不止一个组件，所以不能以母件物料号作为基准进行查找返回唯一值，但根据组件物料号却可以返回唯一母件物料号。因此，我们把表1中的“BOM组件”列作为基准物料号，把图1中的“物料编号”列数据，直接链接到表1的“BOM组件”列。如图3。

3.3 根据组件物料号来查找它的上层母件物料号

我们必须找到一个与母件唯一对应的信息，才能顺藤摸瓜查找到母件物料号。此时我们发现“序号”在原始BOM表中是不重复的数值，序号与母件物料号有什么样的联系呢？对比图1和表1我们发现一个规律：在图1中“序号”列里，只要去掉倒数最后一个小数点及其后面的数字，就是该行零件的母件序号。如1.1.2对应零件N，去掉.2就得到零件N的母件零件L的序号1.1。因此只要得到该唯一序号，就可以根据序号返回对应的母件物料号。因此，插入辅助列，用嵌套函数求得母件序号，并根据该序号用Vlookup函数返回对应母件物料号，见图4。

P5单元格内的公式为=LEFT（A5，FIND（"a"，SUBSTITUTE（A5，"."，"a"，LEN（A5）-LEN（SUBSTITUTE（A5，"."，""））））-1），为方便读者理解，下面对该嵌套公式进行了分步解释。

3.4 设计函数公式求BOM行号

对比图1和表1，发现图1中的“BOM行号”即图1中“序号”列里最后一个小数点后面的数字乘10。可以考虑用上一步所用的方法，只不过上一步采取的是“切掉”后面部分，而这一次我们恰好需要“切掉”的那一部分。于是在上一步公式基础上，稍稍变形，引入LEN函数，结合FIND函数，算一算上次被“切掉”那部分的位数，然后再利用RIGHT函数，从右往左保留这么多位数。最后，在Q5单元格中输入完整的嵌套公式，因为内在逻辑与第3步的分步解释类似，此处不再赘述：

=RIGHT（A5，LEN（A5）-FIND（"a"，SUBSTITUTE（A5，"."，"a"，LEN（A5）-LEN（SUBSTITUTE（A5，"."，""）））））*10

3.5 完成“组件数量”及“单位”的函数公式，并手工将产成品物料号填入

因为最上层产成品X并未出现在原始BOM表中，因此需要进行最后的手工补充。

此时，BOM批量导入模板已全部赋予了函数公式，将左侧数据清除，就得到了一份自动化的数据转换模板，见图8。企业员工只需要将多层结构的原始BOM复制粘贴到图8左边空白界面即可，右边会自动显示出单层结构的BOM数据。即使是上千行的BOM表，也可以在几分钟内轻轻松松搞定。

以上就是在SAP实施过程中，Excel函数公式在BOM批量导入中的巧妙应用案例之一。

表面上看，本案例只是分析了如何将多层次BOM结构转换成系统能识别的单层BOM结构，但实际上却不是这么简单，考虑到顾问在SAP实施过程中需要的不仅仅是BOM数据，还有其他很多例如Routing，工作中心等数据需要导入，所以其实这是一类问题的典型代表，其背后的深刻本质是：企业员工手中的原始数据如何迅速转换成顾问需要的数据形式？

由于我国企业的管理水平与西方企业相比信息化水平还比较低，当西方企业已经在研究如何做系统迁移时，我们国内很多企业却是第一次上ERP系统，所以避免不了要面对从原始数据到系统可识别数据的转换工作。

令人遗憾的是，多数实施公司都不把数据转换的工作划归实施顾问的工作范畴，这依然需要企业自己去解决。对于实施顾问，以后依然会不断面对这个没有应对方案的问题，继续受到这个问题的困扰。而对于每个企业来说，都是第一次面对这种问题，毫无准备且并无任何经验可遵循，最后只能依靠加大人工的投入，增加人手、加班加点去啃这块硬骨头。即使某个企业总结出了解决该问题的有效方法，但经验最终也只停留在了该企业。

因此，希望通过本文的案例分析，为现实中正为着数据转换一事一筹莫展的人们带来一些启发。同时，也希望本文能起到抛砖引玉的作用，引起各界对数据转换领域的关注，从而带来相关经验的积累和分享。虽然数据的转换活动未必占据整个实施项目的关键路径，但一旦转换不及时造成该活动的延期，则该活动就会自动成为实施项目的关键路径，并以瓶颈的姿态影响整体项目上线。特别是在BOM结构复杂的制造业，这类似“最后一公里”的数据衔接问题，像一道看不见的鸿沟，横亘在顾问和他的客户之间，最终成为阻碍企业按时上线的潜在威胁。

主要参考文献

[1]陈启申.ERP——从内部集成起步[M].第2版.北京：电子工业出版社， 2005.

[2]Excel之家.Excel实战技巧精粹[M].北京：人民邮电出版社，2007.