如何使用EXCEL工具帮忙快速整理BOM

来源：•作者：•2021-06-03 14:02

摘要：

1. 线束，特别是总成线束，前期评估时的 BOM 整理一直是个难题，也是最耗费时间的工作；

2. 借助 EXCEL 强大的计算功能，能帮助你解决以上的问题，快速自动化地物料计算和汇总功能让你工作效率提升 50%以上，也能方便后续的个人和他人的检查与识别，更能针对后续的上面任何部分的设变，能做到轻易的切换和得出结果，助你事半功倍；

关键词：EXCEL，线束主要物料 BOM 整理，简易基础版；

一、背景

线束在我们的身边所使用的行业范围很广，根据不同需求，因此对应的线束产品也是千差万别。由于其结构不同，其 BOM 的复杂程度差别也非常大。

对于简单的产品（比如一个总头加几个分支的类型，或者那种单段长度的数据线束），对于产品熟悉的人，一眼看过去就能把物料心算出个八九不离十的，因此也就没有太多的研究的必要，而当前也没有借助辅助工具的必要迫切性。

而对于复杂的线束，比如总成线，其连接器分支就达到百十个，主干也达到数十之多，回路达到数百个，而且回路也是四面八方，来来回回，还包括不少复杂的并线连接，整理这样的 BOM，相对很多人而言就很头大和吃力，在没有特殊的工具辅助前提下或者技巧下，其各种信息，数据和结果全靠人工一个个地罗列，分类，计算和统计，可想而知工作效率会有多缓慢，而且其中一旦有什么差错，遗漏或者变更，前后相关则无法快速定位识别和连贯起来，只能从前往后地重新再做一遍。在目前这个计算机软件发达的时代，也是非常低效和错误百出的工作方法；这样的办法可发生在初学者，但不应该发生在有一定行业和专业经验的熟手上。

总所周知，总成线束上的物料很多很杂，因此如果要做完一篇完整的前后均能连贯和快速自动 BOM整理表格，需要一定的技术和时日，而且同时也是对于这样表格的操作者也要求很高（这是个独立存在的有诸多的小型数据库页面以及众多函数公式等演算出来的结果，因此也是需要操作者对表格中的数据源前后因果关系等有一定的基础，熟悉和使用技巧，因此不建议EXCEL 小白使用），而此文就仅针对总成线束中主要的物料（连接器，端子，以及连接器上的附属品（盲堵，防水栓，线夹，TPA 等）线缆和分支和主干上管类）进行快速整理，以上物料整理完毕后，也就解决绝大部分主要的物料汇总和整理了，其他辅助的物料的整理，有两种办法：

1. 可以在单独的页面上，在图纸上一个个地罗列标识出，并人工汇总即可，时间花费不多。

2. 或者使用高级表格完成；

任何一个工具和方法都有其阶段性和局限性，因此此表所适用的范围如下：

1.仅针对产品的前期评估时所使用，因此计算方法比较简单和粗犷，不适合在实际生产中的制程文件的精细化数据拟定（需要在此基础上进行细化和深化）；

2.适合 WiringHarness 类的产品；

3.更适合复杂类型的产品：因为太简单的产品就有种高射炮打蚊子的感觉：大材小用；后续如果有新的需求以及更多更高级的功能（如根据连接器和线缆规格自动识别和配置对应的端子，防水栓等物料，精确化自动计算数据），就需要在此基础上进行拓展；话题一转，在进入正文前，先插入一段貌似不相关的内容；如图 1，大家看到此图有怎样的感觉。粗看画面，也就是简单是数字 0 和 1.组成的一个系列的莫名其妙的数字串。除此之外，也就再去其他信息。稍微有点留意的人可能就会有点觉察，这难道不是计算机的二进制数字语言吗？答对了！

再继续看图 2，可能大家就更熟悉了，黑色的底加上绿黄色的数字雨，这个就是曾经的经典科幻电影《黑客帝国》的片头。并且在剧中也有很多类似的画面出现。

这部大剧的整个剧情大家也都非常熟悉了：

特工史密斯，实际上就是母体矩阵中的杀毒软件。在矩阵中有一系列的特权，比如远超常人的战斗能力、复制自己的代码，占用别人身体等。但在《黑客帝国》第一部结尾，尼奥和史密斯在地铁大战不敌逃走。特工史密斯抢先一步来到传输端口，用枪打死了尼奥。之后尼奥觉醒复活，获得了可以看穿代码的能力。不但轻松击败了这群追捕的特工，还杀死了史密斯。

此处探讨的重点不是剧情，而是剧中的男主角尼奥在计算机系统中最终结束终极反派BOSS 特工史密斯的终极技能：“看穿代码”，“看穿代码”，“看穿代码”；（重要事情说三遍）

看穿了它，其实对手也就是一堆用“0”和“1”数据组成的信息组合，那么你就掌握了对方，这也就是我们常说的“通过现象看本质”，这也就和佛法中的第三层境界“回看还是山”一样了，此时的对手将不在话下了。

古人云“授之以鱼不如授之以渔”。因此言归正传，此文的表象是介绍一个表格，一个方法，但是更多的是一种思想的传达。思想到了，方法也就到了，并且也能根据自己的需要诞生出更多的方案，就像史密斯的复制和衍生新程序的功能。否则，没有思想的方案就是没有灵魂了，缺少了思想的掌舵，这个方法也就只能为了使用而用，就不能活用，更不能达到触类旁通的效果。因此我抛砖引玉的内容，希望能对大家不止是方法，更是希望去看到一种思想；如果做不到，那么你就只能：被对手碾压式击倒！

主题回归，本文的核心思想就是：像尼奥那样，看穿对方（让对手在你眼中变成了一连群的数字串），解剖对方，并将其细化到最单独，最底层的单元，并找出相互之间的关联或逻辑关系，再打倒它。这样你就能成为造物主，任何信息都可以在你的手中进行编织，最后就能建立起你的黑客帝国；

二、产品建模&思路；

1. 前提条件：

1.1 回到主题，线束在外人看来就是一堆不同的物料堆积而来，有线缆，连接器，端子，管类等。如果要整理这些物料，常用的办法就是根据起止点的位置，将每段上的物料一段一段地加起来。这个是找到了作业方法，但是还没涉及到更深一层的底层，也没找到相互之间的关系：1.1.1没有达到底层，也就没办法找到最根本的单元来识别，我称之为细胞单元；

1.1.2 没有找到单元之间的关系，那么我们也就无法对其进行逻辑关联；最后也就没法使用一种简单快捷而又通用的办法来把上面所有的信息和需求串起来；

1.2 使用“看穿代码”技能，其实再复杂的线束也是简单的线段组合而成。所以：

1.2.1 把线束这个产品解剖分解为单独的独立的枝头或分支，不管是连接器，端子，还是其中某一段的分支/主干长度，以及每个回路，并且使用代码将这些细胞单元全部标识出；这样，就相当于把这些信息打散为最原始的计算机信息单元"0"和"1"；

1.2.2 找到对应的逻辑关系，具体见正文；

1.2.3使用函数或者计算方式，再将这些细胞单元组合成自己所需要的形体。就像不同的信息，在计算机中也只是一堆“0”和“1”组成的信息集而已；

2. 准备工作：

2.1产品解剖：

2.1.1按照一定的顺序将每个连接器分支头进行序号标识好，P1，P2……Pn；一般是按照从左向右，从上到下，并且沿着枝干从前到后，进行标识；这也是为了后续在回路识别中能快速方便定位；此时的 Pn代表两个涵义，一个是分支头，与2.1.2中的主干 Zn的性质一样，其次，也是分支头上连接器的序号识别；

2.1.2 再对主干如方法 1一样，进行分段标识：Z1，Z2……Zn；

2.1.3 根据图纸上的回路表或者原理图，对每一根线进行序号标识；针对并线结点

（J**），只需要考虑其相关联的接线回路即可；

2.2 分支和主干识别：在“分支长度”页面中完成；

2.2.1手工填写 Pn 各分支头的连接器 P/N，标签内容和各分支长度；

2.2.2完成后进入“回路&组装明细表”页面:

备注：这是基础表格，基本还是以手动填写为主，除了部分内容是需要使用函数或者公式进行计算的；而该部分内容，见后面对应的操作步骤中会有单独对应说明；

2.3 回路识别：在“回路&组装明细表”页面中完成；

2.3.1在“PIN 脚”一列中，根据连接器孔位的数量填写完毕PIN #；

2.3.2 与之对应的“HSG 代号”一列，对应地填写上相同的Pn；如图所示；

2.3.3 与之同时，根据图纸回路表，将对应PIN 孔位置上填写对应的线号；

2.3.4~2.3.10 参照以下图示分别填写对应栏位内容；

备注：此表中，包含了几处函数：

1. B 列的“HSG P/N”使用 VLOOKUP 与 IF 条件判断结合使用的组合函数：如果“A4=A5,"",VLOOKUP 函数……”；其语句的意思解释为：当左边对应 A 栏位中的内容与上一行一样时，此栏位内容为空，否则就执行查找功能；如果放在B8 栏位，其解释就是如果A8 和A7 的内容一样，说明这是一个相同的HSG 中，因此为了让图表简洁，就显示是空白内容。当走到B9 的时候，此时A9 的内容就是P2，而A8 的内容就是 P1，则触发了新的条件：这行开始进入了新的一个HSG 中了，所以就开始显示P2 的HSG 的料号了；而数据链接的原始数据就是前文2.2.1 中的“分支长度”页面内容；这样的操作有三个目的：A. IF 的语句让界面更加简洁，也是为了第二个功能做铺垫B.VLOOKUP 函数让数据链接更加简单和准确；C.并且也是为了后续的 HSGP/N 的计数做准备；

2. C 列的“标签”栏位中显示的内容，则是为了识别，对于 BOM 的整个整理起到的作用不是主要，只是个辅助性的作用；因此一样使用了IF 和VLOOKUP 函数的组合函数：在B 列对应栏位内容有内容时，就开始启动“分支长度”页面内容中链接查找功能，否则也显示为空白；

3. D 列的“HSG数量”，这计数点就更简单了，直接使用个 IF 判断语句来实现：如果对应 B 列或者C 列的栏位中有内容，就直接显示数字 1，不然，一样显示为空白。这样这个对HSG 的所有信息和计数准备就完成了；

4. F 列的“排位”，就是连接器的孔位数量。个人的做法和建议就是把所有的孔位都显示出来，后面再根据线位和回路关系，将每个线放在对应的PIN 位上，这样做的目的也有三个：

A. 能识别入孔位置（也就是后续端子的使用数量统计做铺垫）；

B. 能区分开不入孔的情况，也是为连接器如果需要使用到盲堵的位置识别和数量统计做铺垫；

C. 能识别有重复错位的情况(理论上，即使是并线，其并接点也尽量使用在线段中部，而不是直接并接在端头端子上)，同时有入线的孔位，其对应的行上，也就一定有对应的端子和对应的线缆；否则就是错误的，这个逻辑也是为了方便后续我们的检查和识别，如图 9；

5. 与之同时的每款物料的隔壁一列与之对应，如果有信息进来（即填写了物料的P/N 后），则对应的计数列中就有函数开始自动计数。而且因为每栏的内容只能完成一个物料，所以这计数栏位的内容就是 0 或者 1。再考虑到因为没有内容，其实也就更没需要显示出来，因此，再把 0 换成空白。所以就变成了目前这样的界面。这些隐藏在幕后的运算栏位，为了让界面简单，也为了放置放在外面不小心被删除或者动到了，因此，尽可能地将这些运算的栏位隐藏或者密封固定起来；同时为了肉眼简单区分和识别，对应手工填写或者可以编辑的栏位使用白底，而有运算的栏位则使用黄底。同时对于后续要计重识别的，再在白底基础上增加个红底识别功能。这样如果有内容重复时，栏位自动显示红色，这也是为了反向识别：某些内容内容的单一存在。就比如线序，如果栏位中有内容显示，却是白底，那么说明这根线号的线，在整个回路表中只有一根单头的，回路不完整。此时可能有些人会说，比如有些线缆回路本身就是一边接入，一边是 OPEN 状态，其实此时，你就可以将OPEN 端一样标识好，就把它看成一个接头，照样进行序号标识即可，与此同时的接地端子也如此，一样有两种方案，或者把端子当成特殊连接器在“分支长度”页面将其 P/N 定义在 HSGP/N 列中，也可以在“回路表”中，直接在此表手工填写一样的。这样两种处理的方法，最后处理的结果，都是一样的，只会在最后的“BOM”页面中表现的位置不同而已。前者是将接地端子当成特殊连接器而与连接器统计在一起，而后者则是统计在端子汇总表中；但是两者的性质和结构都一样，没有影响。不过于此同时，特别注意，特别是接地端子，如果是并线，建议还是使用并接端子（J**）的方式进行与其他分支连接。其次，这样的端子只在“HSGP/N”或“端子P/N”中只能出现一次，否则后续系统会进行重复计算。这对于其他地方填写要求也是一样的，因此每个物料P/N 后面都有一个计数的函数在跟着，因此，任何一点有P/N出现，系统就会进行自动计数统计到后面BOM 中。这是填写此表的关键必须知悉的原则，不可违背；

2.4 配线&确定线长：在“回路&组装明细表”页面中完成；

2.4.1在“线号序号”一列中，任意取一组相同数字的线号进行筛选；

2.4.2 根据表中的 HSG 代号识别，在“图纸”中快速找到该两个接头的位置；

2.4.3 识别该两个分支头所经过的主干分支段，并在对应的主干分支代号下，划上"V"字标识，如果是并线的，则建议直接选择最远的两端点之间的回路作为主干，其余的均为分支连接到主干上；

比如此图，选择线号 1，则筛选出来了 P1,P3,P6 和J01，就说明这是个并线，都是 1# 一起的回路，通过图纸知悉，P1 和 P6 分别位于产品最远的两侧，因此在P1~P3,P3~P6 和P1~P6三种主干定义中，P1~P6 最长，就定义为主干，而 P3 作为枝干存在，其就直接搭接再主干上了。因此其和主干连接时，已经不需要再考虑有其他额外的衍生线段长度叠加。我们在实际中，实际上就会有遇到这样的情况，假如 P3 到主干不是直接连接到主干上，两者之间还有段 Z4 的分支距离。此时如果只是计算物料数量，那么你可以直接将Z6 放在主干上（即使将 Z1~Z3 以及 Z4直接挂在 P1 或者 P6 段上即可），对最后的结果没有影响。但是如果是计算每段线的长度时做参考时，则按照理论 Z4 就只能放在 P3段。此时这个表的涵义就变成了，主干为 P1~P6,其长度为 L总=L(P1)+L(P6)+Z1+Z2+Z3；而 L(P3 到主干的分支长度)=L(P3)+Z4;这个原理务必清晰和熟悉，这对后续物料的计算起到基础作用；

2.4.4 并在“调试与否”一列中，也划上划上"V"字标识；同一个回路的线缆理论上要同色，而且这样也能知悉，经过这样一筛选后，这样就能快速识别出某些线段的颜色定义出现了偏差，这样也就能随时修正；

2.4.5 完成后，需要将“调试与否”一列，只勾选“空白”；这是为了后续，能将已经处理完毕的回路进行过滤:这样每处理完一个回路，在界面中显示的内容就越来越少，直到所有的线路处理完毕；

2.4.6 再按照 2.4.1~2.4.5 的顺序重复操作，依次将所有的线号处理完毕；

2.4.7 所有回路配好后，再将“调试与否”展开，将其中所有的选项都选择上并打开；

2.4.8 再分别对每段主干对有标识的筛选出，并参照“分支长度”上的主干长度的数字代替先前做的“V”字符号标识；（当然，这个标识符，可以根据自己的喜好换成别任意符号或标识均可，而这个临时字符在此时也均会被数字全部替代）

根据 2.4.3 上的原理，我们就知道了计算线长的原理和方法了，因此，在主干一列再插入一列求和公式即可。将各分支的长度以及主干的长度进行叠加，就完成了线段长度的计算和初步统计了；

2.5 定管：在“分支长度”页面中完成；

2.5.1 再回到“分支长度”页面，并结合前表，确定每个主干和分支上主要的线缆规格以及对应的 OD 大小；这是个比较麻烦的事情，因此其思路如下：计算各分支和主干的线的大小和粗细，这样才能计算线缆组的外切圆的直径 OD，根据 OD，才能选择适当的管类。

2.5.1.1 对于分支，我们可以直接对“回路&组装明细表”中主干的每一列进行计数统计，和左边对应栏位上的 Pn 对应起来，直接使用 COUNTIFS 的双条件函数，就能自动统计出分支上的线缆数量。此时的窍门：可以根据 2.3步骤中，去识别“线号”， “端子 P/N”,“线缆型号”或者“线缆大小”等任意列中内容：如果有内容就能识别和计数上；（“线缆颜色”一列建议不以此去判断和统计，因为如前所述，此表更多是前期的物料初步统计，因此在定义好线缆规格和大小后，颜色对于成本考量基本无影响，所以也就可以不需要再处理。）

2.5.1.2 对于主干，那也就更简单了。可以直接使用COUNT 函数来统计有数字的列上，有多少填写了数字的（步骤 2.4.8），就都能迅速统计出；

2.5.2 填写完毕后，定义出主要线缆的规格和直径（这个参数才是最为关键的），总所周知，一个分支或者主干上的线缆不可能只有一种线缆存在，一定会有大有小，此时确定线缆的主要规格和大小就全靠自己了，目前我还没有找到个一个明确的通用的办法来定义不能完全按照 OD 最大的一根线缆来定，更不能完全按照 OD 最小的线缆来定。比如一段枝干，有 1根 OD2.0 的线，而其余的 50 根更小的线缆，OD只有 0.8,那么你就不能按照 2.0*51D 这个规格来作为标准来计算后面的线缆组，可能此时可以按照 0.8*51 的标准来计算倒是更精准点。因为这样的情况无限可能，因此没找到规律前，也就只能靠人工来判断和识别，一把线缆组中，哪种线缆是使用频率最多的。定义了这个参数（主要线缆的OD），表格才能自动会计算出总线缆的大致外切圆直径；这个可参照线缆技术中计算线缆外切圆外径的计算公式进行；

2.5.3 根据此直径就可以自定义选择对应的管；

2.5.4 以此方式完成每段上的管类选择，包括主干和分支；

2.6 BOM 汇总：在“BOM”页面中完成；

2.6.2 使用 EXCEL 自带的“数据”下拉菜单中的“全部刷新”指令，就能更新出所有的数据，完成后就得到了更新后的 BOM 清单；

此处对“分支长度”中的“管规格”和“长度 L”两列以及“回路&组装明细表”中的HSG,端子，防水栓，盲堵，附属品，以及线缆各段长度进行透视表的方式进行汇总统计到“BOM”中展示出来。

2.6.2 在使用 BOM 总表中的个数时，需要记得过滤或者删除掉自己不需要的信息统计栏位；因为表格的计数范围比较广泛的矩阵，因此有一些的栏位内容为空白时，也是会被统计在其中，这就成为了最后统计的总数，如下所示。此时我们就不能只看统计表格中最后一列的“总计”，而是要删除“空白”的。所以，这个产品最后统计出来的端子是 1928498677

（10PCS）和 282109-1（4PCS），共计 14PCS，而不是“总计”中的 617PCS；

这样，按照前面这样的操作顺序，最后，一个刷新，所有统计的数据就出来了：

备注：

1. 文中白色部分为手工可以填写的区域，其他带颜色的区域为自动运算部分；

2. 手工填写的内容，内容填写要100%完全前后一致（包括内容，格式，内容顺序等均完全一致），否则系统会将其识别为两个不同的规格：

比如管：“PP■开口■黑色■4”和“PP■■开口■黑色■4”，格式中的■代表空格，也就是说我们在填写的时候，其中有一栏如果不小心多敲了个空格出来，那么最后在计算机识别的时候，就会把它看为一款新规格的物料，而最后在BOM 汇总的时候，你就会发现里面有两款看上去好像时一样的物料，但是其结果却没有合并在一起。此处，我是用■代表空格，所以肉眼比较容易识别，而在实际操作时，■刚好是肉眼无法识别和判断的。这点需要特别注意。与此类似的情况如“PP开口黑色 4”和“PP 开口 4 黑色”，也是一样的。遇到这样的情况解决也很简单，用正确的一款的内容直接粘贴覆盖错误的信息，再将参照 2.6.1 的方法数据刷新一下，这样两组数据就合并在一起了；