物料计划中如何使用Excel实现一对多反向查询功能
- 2026-04-06 12:13:08
不知道大家在日常工作中是否也遇到过这样的情况？
当通过某个部件或原料的信息对产成品的信息进行查询时，只要顺着部件信息，我们就可以轻易地查询到这个原料用于哪一次生产、组装，做成了哪几个批次的产品。但是如果手里只有成品信息要反向溯源，这个操作似乎就有点令人窒息了。
但是别怕。咱们不是有功能强大的Excel嘛。有了Excel，有啥问题不能解决呢？
今天咱们就来说一说，如何用Excel解决反向溯源的Excel小工具。
作为一对多反向溯源的关键，首先咱们得解决2个问题：
1.一对多的反向，如何实现一行数据自动变多行？
2.如果建立成品与半成品/组件的唯一关联？
是不是听起来有点晕？
其实！！是蛮复杂的 （不然为啥要长篇大论写个文呢？笑Cry~~）。
在实现自动复制行的过程中，我们需要几个数据：
1.需要被复制的原始数据（这里我们使用成品代码）
2.需要复制或者重复出现的次数（这次我们使用BOM的半成品数量）
3.明确行信息的唯一性（这里我们用辅助列进行行距计算）
简单来说，表格就是长成下面这样:
需要被复制的数据本据 | BOM内半成品的数量 | 辅助列数据2 |
成品1 | 3 | 3 |
成品2 | 4 | 7 |
成品3 | 5 | 12 |
或者如果你并没有BOM的产品数量，从而使用辅助列数据（该辅助数据信息为已知的成品数量需要重复的次数。例如BOM里有5个半成品，则当你每需要复制一次成品信息时，这5个半成品信息都将被自动复制一次。）
需要被复制的数据本据 | 辅助列数据1 | 辅助列数据2 |
成品1 | 1 | 1 |
成品2 | 5 | 6 |
成品3 | 7 | 13 |
辅助列2 的值使用sum公式来计算辅助列1的汇总值，以便决定行信息的数量：SUM($B$2：B2)-B2。
当使用$对首行首列信息进行锁定时，公式下拉后，都是从首行到本行的累加值，减去本行需要重复的数据。从而我们可以确定每一个需要被复制的信息的上一个区段信息。
完成以上这些，我们已经完成了行自动复制的基本操作。接下来就进入自动复制阶段。
首先我们请出2个特殊的函数。
第一个，ROW(reference)。ROW函数可以返回指定单元格的行号。例如：ROW(A3),返回值为3。
第二个，LOOKUP（lookup_value，lookup_vector，result_vector）
lookup_value为我们要在数据表中查找的“值”。
lookup_vector为我们要查找的值得“数据表”。
result_vector为我们通过数据表想要得到的"值“。
此处必须要提到LOOKUP的一个特点，我们就是利用了此特点才能成功实现函数的复制。LOOKUP查询列必按照升序排列。如果所查询值为明确的值，则返回值对应的结果行，如果没有明确的值，则向下取的于所查询值最近的值。这句话什么意思呢，打个比方，就是当你需要在1,2,98,99,100之内 查找99的时候，如果源数据里存在99，则返回99，如没有99，则范围最大近似值98 。
有了ROW,有了LOOKUP,通过将他们组合起来，我们就可以实现行自动复制了。不吹不黑，直接给出公式：
IF(ROW(E1)-1>=SUM(F:F),"",LOOKUP(ROW(E1)-1,G:G,E:E))
自动复制数据完成，后面的操作就相对简单了。既然我们已经实现了根据BOM中的半成品数量自动复制成品数量，那么下一步需要解决的问题就是，如何建立BOM中半成品与成品的一一对应关系？
对此，我们将采用数据结构中建立虚拟表的概念，来实现半成品与成品的一一对应关系。
首先，在BOM中添加一列辅助代码，以成品代码+半成品的个数的方式进行标码。如下图示例，辅助代码为成品1半成品1。
随后，为了实现一一对应，我们在搜索界面中，也新增一列辅助代码（为了清晰显示公式作用，我加了2列辅助代码，以便更清晰的展示公式逻辑）
辅助代码1，使用COUNTIF函数，确立对于成品的多个半成品来说，这是第几个半成品数据。
辅助代码2，使用CONCATENATE函数将成品代码与辅助代码数据进行组合，形成可以与真实半成品代码一一对应的半成品代码搜索信息。
此处着重讲解下辅助代码1的使用。如下图显示，由于成品的数据是重复出现的，故当我们需要通过判断该成品数据为第几次出现来转化为有几个半成品时，我们需要对首行首列进行锁定。这样当我们在进行公式下拉的过程中，我们就可以确保在当前选定行的范围内，该成品数据为第几次出现。这个DEMO是一个简单的演示。实际操作过程中，大家可以通过使用COUNTIFS来将范围精准缩小到，某个订单中某个批号的成品为第几次出现，从而实现操作。
辅助代码2则相对简单，通过CONCATENATE函数，将所有信息进行组合，形成唯一的搜索代码。
随后，使用VLOOKUP函数，在BOM表中对真实的半成品代码进行搜索匹配，从而找到真实的BOM代码。有了真实的半成品代码，后面的操作想必不用我进行赘述了。有任何需要查询的数据，通过VLOOKUP的搭配，都可以轻易找到。
如果在实际操作中，查询信息中涉及不同的订单、成品批号、半成品批号的多个信息，那么只需要通过CONCATENATE函数将可用于查询的唯一搜索代码进行重新组合确立搜索代码的唯一性，即可完成操作。
相关好文如下，点击即可阅读
