LAMBDA函数递归：根据系统导入需求调整BOM清单

shaowu459

BOM清单（Bill of Material, BOM）也即物料清单，指产品所需要的零部件的清单及组成结构，物料清单又叫做产品结构树或产品结构表，是以数据形式来描述产品结构的技术文件，通俗点讲，就是配方或要素表，包括了原料、产品、半成品、消耗品、协作件等许多与生产息息相关的物料。

下图是BOM清单图形表达的一种简化示例，例如，生成1个单位A需要5个单位B，3个单位C，2个单位D，1个单位E；BDE都是直接买来的最终原材料，而C是要由其他原材料生产而成，比如说生产一个单位C需要FGH各2个单位，FG是最终原材料，H要由若干单位的IJKL生产而成。

目前企业生产管理主要是用计算机系统控制生产程序，要使计算机系统识别企业所制造的产品构成和所有要涉及的物料，必须把用图形表达的产品结构转化成系统需要的特定数据格式，这样才能将BOM清单导入系统以顺利组织生产。由于生产管理系统的不同，导入的BOM清单数据格式也不尽相同，本例来介绍一种利用LAMBDA函数递归方式实现BOM清单按规定格式调整的方法。

shaowu459

我们先来看一下原始数据的特点：
1）A列为父件名称，B列为子件名称，C列的数量为生产1单位母件需要的子件数量。如下图，生产A这个产品需要B1、B2和B3这3个子件：


2）某个子件可能不是最终原材料，仍然需要其他子件生产而成。如下图，B1这个对A来说的子件，需要由C1和C2这两个子件生产而成。相对C1和C2来说，B1就是母件。


3）目前的数据是这样排列的：生产A需要B1、B2、B3这3个子件，在A的下面顺序摆放生产B1和B2所需子件的情况（B3是最终原材料），下面继续按顺序摆放生产B1和B2所需的各种子件，以此类推。


4）根据系统要求，现有的数据需要调整，先在图中看下规律：
生产A需要的第一个子件是B1，所以B1放在A下面，生产B1需要的第一个子件是C1，因此把C1放在B1下面，生产C1的第一个子件是D1，因此把D1放在C1下面，生产D1需要E1~E4这4个最终原材料，因此放在D1下面。下面轮到生产C1的第二个子件D2，D2由E5和E6生成，因此陆续摆放在下面。然后轮到生产C1的第三个子件D3，继续往下摆放。同时，第一列添加了一个层级的标志，最终产成品A算0级，A的子件算1级，子件的子件算2级，以此类推。



一句话总结一下规律就是：将生产某个产品的某个子件一直向下展开到最末级材料，然后再展开下一个子件，每个母件都按这个程序展开并堆叠起来。

shaowu459

首先，我们要为LAMBDA自定义的函数提供一个初始参数，因为要逐层展开，所以初始参数应该是所有的0层母件，判断标准就是：A列的母件名称在B列没有出现过，就是0级母件，因为它不是任何产品的子件。


我们可以用以下公式来筛选出A列的所有0级母件（去重前）：

1. =FILTER(A2:A32,1=IFNA(MATCH(A2:A32,B:B,),1))

复制代码

因为最终结果要3列，第一列是层级，第二列是名称，第三列是数量，所以我们将0级母件也扩展成3列。因为是0级母件，所以第一列是0，第三列是1（生产1个单位母件），因此将公式可以修改成：

1. =FILTER(CHOOSE({1,2,3},0,A2:A32,1),1=IFNA(MATCH(A2:A32,B:B,),1))

复制代码

最后，再用UNIQUE函数去重即可。

1. =UNIQUE(FILTER(CHOOSE({1,2,3},0,A2:A32,1),1=IFNA(MATCH(A2:A32,B:B,),1)))

复制代码

shaowu459

因为每个产品下面都可能包含多个子件（也就是多行），并且返回值也是3列的数组，因此不能简单用BYROW函数处理，可以用REDUCE函数，通过循环子件数组的行数，逐行提取出来，然后再纵向堆积。例如假设筛选出的0级产品2行的数组为T，SEQUENCE(ROWS(T))可以获得{1；2}这个行数组，然后在LAMBDA函数里可以用INDEX(T,行数1或2,)来提取出数组T的第一行或第二行，然后提取产品名称去查找下层子件。

以0级的两行数据来说，可以用以下的公式模式进行处理：

1. =REDUCE(0,
   
2.                   SEQUENCE(ROWS(T)),
   
3.                   LAMBDA(x,y,
   
4.                                     LET(s,INDEX(T,y,),
   
5.                                            t,FILTER($B$2:$C$32,$A$2:$A$32=INDEX(s,2),0),
   
6.                                            对改行数据的处理
   

复制代码

上面公式里的s为提取出数组T中的第若干行，INDEX(s,2)提取出改行中的第2个值，也就是产品名称。t的作用是将A列等于该产品名称的BC列数据筛选出来，也就是筛选出该产品作为母件对应的所有子件行。如果一个产品名称在A列里筛选已经没有了，也就是该产品是最终原材料，FILTER筛选将出现错误，因此设置FILTER的第三参数为0，如果筛选最终原材料的产品名称则返回0，否则返回该产品作为母件对应的所有子件行。

shaowu459

下面分别说明对FILTER函数筛选出来的两种情况如何分别处理。

情况1：如果筛选名称是最终原材料，则FILTER函数筛选出结果为空，返回第三参数0。这时，当前处理的行s就是最终原材料，只需要正常堆积就可以了。

1. =IF(COUNTA(t)=1,VSTACK(x,s),其他处理)

复制代码

用IF函数来判断，如果t里的元素是1个（也就是FILTER函数筛选出来的0），则直接在x的基础上堆积上当前处理的行s即可，公式表示是VSTACK(x,s)。

情况2：如果FILTER函数筛选结果有1行或者多行，则说明当前的产品名称仍由下层的子件生成，所以首先仍然要用VSTACK(x,s)将x和当前行堆积在一起，然后要继续调用LAMBDA定义的自定义函数（比如命名dx），继续查找当前产品对应的所有子件行，然后重复上一楼层说明的计算过程，直至查找到最终原材料，执行上面的情况1相关运算。此部分公式参考：

1. =IF(COUNTA(t)=1,
   
2.       VSTACK(x,s),
   
3.       VSTACK(x,s,
   
4.                        dx(HSTACK(@s*TAKE(t,,-1)^0+1,t))
   
5.                  )
   
6.       )

复制代码

因为自定义函数的参数要为3列，第一列是层级，第二列是产品名称，第三列是数量，所以再重复调用dx函数时，需要构造新的三列数组作为参数。因为t已经是筛选出来的BC列的子件名称和数量了，因此只需要在左侧多堆积上一个层级号，号码应该是当前行s的第一个元素+1，因为再往下查询的是当前行的下一层。又因为t是筛选出来的子件数组，可能是多行，所以这个层级号需要扩展成t这个数组的行数。又因为t的最后一列是数值，因此可以用TAKE(t,,-1)^0来获得一个和t同行且都是1的数组，然后用@s提取出s的第一个层级号相乘就可以了。所以下面这一部分就为dx提供了进一步运算的参数。

1. HSTACK(@s*TAKE(t,,-1)^0+1,t)

复制代码

例如当前处理的行s是0级产品的第一行：


所以t返回的结果是组成A的所有子件：


因为t不是0，所以要提取s的第一个元素0加上1作为新的层级号，然后和t合并在一起，为dx继续计算提供参数：


如果t是0，则直接堆积当前行s即可。

shaowu459

分析完毕后，我们将下面的公式定义为dx名称：

1. =LAMBDA(z,DROP(REDUCE(0,SEQUENCE(ROWS(z)),LAMBDA(x,y,LET(s,INDEX(z,y,),t,FILTER($B$2:$C$32,$A$2:$A$32=INDEX(s,2),0),IF(COUNTA(t)=1,VSTACK(x,s),VSTACK(x,s,dx(HSTACK(@s*TAKE(t,,-1)^0+1,t))))))),1))

复制代码

在任意空单元格输入以下公式，为dx提供0级产品数组为参数即可得到调整后的BOM清单：

1. =dx(UNIQUE(FILTER(CHOOSE({1,2,3},0,A2:A32,1),1=IFNA(MATCH(A2:A32,B:B,),1))))

复制代码

shaowu459

最后，再对上述公式进行简单说明：


1）dx的初始参数：
=UNIQUE(                                                                     对筛选出来的0级母件数组去重

                FILTER(                                                          筛选0级母件
                           CHOOSE({1,2,3},0,A2:A32,1),         给列产品名称前面加上0，后面加上1，形成多行3列的数组，作为FILTER函数第一参数
                           1=IFNA(MATCH(A2:A32,B:B,),1)    筛选0级母件，标准是A列名称在B列没有出现过。直接用ISERR(MATCH())或其他公式均可
                          )
              )

2）定义的dx名称：
=LAMBDA(z,
                  DROP(REDUCE(0,
                                             SEQUENCE(ROWS(z)),  获取筛选的子件数组行数序列

                                             LAMBDA(x,y,
                                                                   LET(s,INDEX(z,y,), 提取筛选出来的每一行，逐行进行处理
t,FILTER($B$2:$C$32,$A$2:$A$32=INDEX(s,2),0), 筛选当前产品的子件，如果没有返回0
IF(COUNTA(t)=1,    如果FILTER部分返回0，则说明当前产品已经是最终原材料
VSTACK(x,s),       直接堆叠当前行
VSTACK(x,s,         堆叠当前行的基础上继续堆叠dx返回结果

dx(HSTACK(@s*TAKE(t,,-1)^0+1,t))   重复调用dx，参数为当前层级号+1和t组成的数组
)
)
)
                                                           )
                                            ),
                            1)
                 )

alan57

收藏...感谢版主..

cnmlgb9998

好难啊！！！！！！！！！！！递归 ！！！！！！！！！

mjm04

递归 ！！
学习了