365新函数：BOM结构父级子级-使用双循环替代递归

shaowu459

下图A列和B列是BOM结构数据，A列是父件型号，B列是子件型号，要求获得D:E列结果，也即所有末级子件对应的最顶层母件型号。需要注意的是，某些末级子件会被不同的顶层母件使用，如下图棕色部分所示。

关于BOM的概念，参见以下链接：根据BOM清单计算产成品成本。

shaowu459

分析下思路：
1）首先提取出A列所有在B列没有出现过的型号，也即所有的顶层母件型号。这个功能可以使用以下公式完成：

1. =FILTER(A2:A48,COUNTIF(B:B,A2:A48)=0)

复制代码

2）循环每个母件，查找母件对应的B列子件。再将查找出来的子件作为母件，继续查找是否有下层子件，直到再无底层子件为止。
以CP1这个母件为例，第一次查找它对应的全部子件：


第二次，循环第一次查找出来的每个型号，继续查找下层子件：

以此类推，直到在B列无法查找下层子件位置。

因为每次循环和查找模式一样，因此该问题是可以使用递归来完成的。

shaowu459

不使用递归方法，按照分析思路，直接使用REDUCE函数双循环也是可以的。用循环的方法，首先需要解决的问题就是如何在循环过程中保留顶层母件和下层查找子件。在循环过程中，横向和纵向堆叠数组是比较麻烦的，比如前面提到的查找CP1对应子件的前两步：


如果按上面的数组方式堆叠，下次循环时提取型号参与运算也不是很方便。在这种情况下，我们可以考虑使用字符串的方式来实现。还是以查找CP1对应子件的前两步为例：
第一步，查找CP1对应的子件，然后用逗号连接起来：



第二步，分别查找后续子件，继续用逗号连接起来：


这样操作完成后，只需要循环每个字符串，然后查找字符串最后一个“,”后面的型号，查找结果使用逗号和循环的字符串连接起来即可。


等循环完毕之后，每个字符串第一个“,”之前就是顶层母件，每个字符串最后一个“,”之后就是对应的末级子件。分别提取出母件和子件数组，横向堆叠在一起，然后去重即可。

shaowu459

有了以上分析，我就可以写出如下公式（仅供参考）：

1. =LET(A,A2:A48,B,B2:B48,v,REDUCE(FILTER(A,COUNTIF(B,A)=0),A,LAMBDA(x,y,DROP(REDUCE(0,x,LAMBDA(m,n,LET(u,TEXTAFTER(n,",",-1,,1),VSTACK(m,n&","&FILTER(B,A=u,u))))),1))),VSTACK(A1:B1,UNIQUE(HSTACK(TEXTSPLIT(v,","),RIGHT(v,4)))))

复制代码

公示简要说明如下：
=LET(
    A, A2:A48,
    B, B2:B48,
    v, REDUCE(
        FILTER(A, COUNTIF(B, A) = 0),   筛选最顶层母件，标准是A列型号未在B列出现过，也即该型号不是任何型号的子件，作为x初始值
        A,
        LAMBDA(x, y,
            DROP(
                REDUCE(
                    0,
                    x,                  内层循环，循环x中的每一个字符串，查找对应子件
                    LAMBDA(m, n,
                        LET(
                            u, TEXTAFTER(n, ",", -1, , 1),   n代表x中的每一个值，提取n最后一个“,”后面的型号
                            VSTACK(m, n & "," & FILTER(B, A = u, u))   查找u这个型号对应的子件，并和n用逗号连接起来，也即和x中每个字符串连接起来
                        )
                    )
                ),
                1
            )
        )
    ),
    VSTACK(A1:B1, UNIQUE(HSTACK(TEXTSPLIT(v, ","), RIGHT(v, 4))))  v得到的是一列字符串，第一个“,”前是母件，最后一个“,”后是最终子件，分别提取出来横向堆叠，去重
)

amazeyeli

AmaZeYeLi35.rar (26.43 KB, 下载次数: 28)

2023-8-13 13:30 上传

点击文件名下载附件

shaowu459

amazeyeli 发表于 2023-8-13 13:30

感谢参与

alan57

1. =LAMBDA(m,LET(
   
2. _b,UNIQUE(DROP(REDUCE("",m,LAMBDA(x,y,VSTACK(x,FILTER($B$2:$B$48,$A$2:$A$48=y,y)))),1)),
   
3. _c,DROP(REDUCE("",_b,LAMBDA(x,y,VSTACK(x,FILTER($B$2:$B$48,$A$2:$A$48=y,"")))),1),
   
4. IF(AND(AND(_c="")),_b,f1x(_b))))

复制代码

递归

1. =DROP(REDUCE(0,UNIQUE(FILTER(A2:A48,COUNTIF(B2:B48,A2:A48)=0)),LAMBDA(x,y,VSTACK(x,IF({1,0},y,f1x(y))))),1)

复制代码

比超人老师弄复杂的多

cutecpu

用兩列棧，每次只更新第二列:

1. =LET(A,A2:A48,B,B2:B48,v,REDUCE(FILTER(A:B,COUNTIF(B,A)=0),A,LAMBDA(x,y,DROP(REDUCE(0,SEQUENCE(ROWS(x)),LAMBDA(m,n,LET(u,INDEX(x,n,2),VSTACK(m,IF({1,0},INDEX(x,n,1),FILTER(B,A=u,u)))))),1))),VSTACK(A1:B1,UNIQUE(v)))

复制代码

shaowu459

cutecpu 发表于 2023-8-14 15:40
用兩列棧，每次只更新第二列:

更新两列的方法也写了一下，细节稍有不同：

1. =LET(A,A2:A48,B,B2:B48,UNIQUE(REDUCE(FILTER(A:B,COUNTIF(B,A)=0),A,LAMBDA(x,y,REDUCE(A1:B1,SEQUENCE(ROWS(x)),LAMBDA(m,n,LET(o,INDEX(x,n,2),VSTACK(m,IF({1,0},INDEX(x,n,1),FILTER(B,A=o,o))))))))))

复制代码

shaowu459

下面是一个例子，来说明3楼说的那种比较麻烦的堆积方式。如下图，要根据A:C列的层级关系，来生成右侧绿色区域的组织架构。



在右侧空单元格输入以下公式，回车。结构还是上面的双循环，只不过每层新查询的结果，继续堆积在原数组右侧。

1. =LET(P,B2:B46,Q,C2:C46,v,REDUCE(FILTER(P,Q=0),P,LAMBDA(x,y,DROP(REDUCE(0,SEQUENCE(ROWS(x)),LAMBDA(m,n,LET(o,INDEX(x,n,),u,TAKE(o,,-1),VSTACK(m,IF(ISNA(MATCH(u,Q,)),o,HSTACK(o,SORT(FILTER(P,Q=u)))))))),1))),w,IFNA(v&CHAR(10)&XLOOKUP(v,B:B,A:A),""),VSTACK("第"&SEQUENCE(,COLUMNS(w))&"级",w))

复制代码

公式简单说明如下：

=LET(
    P, B2:B46,
    Q, C2:C46,
    v, REDUCE(
        FILTER(P, Q = 0),   筛选出最顶层的级别
        P,
        LAMBDA(x, y,
            DROP(
                REDUCE(
                    0,
                    SEQUENCE(ROWS(x)),  循环x中的每一行
                    LAMBDA(m, n,
                        LET(
                            o, INDEX(x, n, ),  逐行提取出x的每一行
                            u, TAKE(o, , -1),  提取当前行最后一个元素，用于查找下一级
                            VSTACK(m, IF(ISNA(MATCH(u, Q, )), o, HSTACK(o, SORT(FILTER(P, Q = u))))) 如果没有下级，则返回当前行，否则在右侧堆积下级查询结果
                        )
                    )
                ),
                1
            )
        )
    ),
    w, IFNA(v & CHAR(10) & XLOOKUP(v, B:B, A:A), ""),  查询级别对应的汉字，合并起来
    VSTACK("第" & SEQUENCE(, COLUMNS(w)) & "级", w)     添加标题行
)