父子、上下级、BOM、关联关系那点事

shaowu459

根据父子关系、上下级信息或展示数据关系的列表来求路径、层级或整合数据，是一种比较常见的题型，大部分可以用循环或递归解决，本帖主要提供几个案例供大家参考。本帖不涉及常规的类似会计科目代码展开或填充那种例子，也不涉及下拉公式解法，主要是自动溢出结果的写法。帖中的公式并非最优解，主要起到抛砖引玉的作用，供各位坛友仅参考。

如果大家有其他类型的上下级、父子关系的题目，也欢迎把文件和要求发上来，大家共同探讨。

shaowu459

第一个问题，根据家庭父子信息整理族谱数据。

下图中A列是人名，B列是父辈的人名，要求展开成右侧表格数据。张老黑和张老祖是族谱中最顶级的人，因为原始数据中没有这两个人的父亲信息，右侧依次是左侧人名的子辈。




参考公式如下：

1. =LET(t,A2:A15,s,B2:B15,REDUCE(UNIQUE(FILTER(s,COUNTIF(t,s)=0)),s,LAMBDA(x,y,DROP(REDUCE(0,SEQUENCE(ROWS(x)),LAMBDA(m,n,LET(o,INDEX(x,n,),u,TAKE(o,,-1),IFNA(VSTACK(m,IF(OR(s=u),IFNA(HSTACK(o,FILTER(t,s=u)),o),o)),"")))),1))))

复制代码

上面的公式写法和Microsoft 365：机灵鬼小y中18楼的题目写法基本一致，可以参考阅读。

shaowu459

第二个问题，获取包含父子、上下级关系表格中的层级信息。

如下图A:B列仍是某家族人员信息，要求获得每个人的层级。无父辈信息的人为1级，他的子辈为2级，以此类推。



参考公式如下：

1. =LET(s,UNIQUE(TOCOL(A2:B15)),t,MAP(s,LAMBDA(z,MATCH(,SCAN(z,ROW(1:9),LAMBDA(x,y,XLOOKUP(x,A:A,B:B,0))),))),SORTBY(HSTACK(s,t),t))

复制代码

shaowu459

第三个问题，求根节点到叶节点路径。

下图A:B列为父子层级信息，与上面族谱信息整理的例子不同，这里最顶级的父辈对应的B列是空单元格，除此之外没有什么不同。要求将父子信息展开成右侧表格样式。


第一个例子中，是先找到最顶级父辈信息，然后向右逐个查找子辈，用的是普通循环公式。这个例子，换一种递归写法，并且先查找最底层的子辈信息，然后逐层往上查找父辈信息。
首先定义名称fx：

1. =LAMBDA(x,LET(s,VLOOKUP(x,$A:$B,2),IF(s=0,x,HSTACK(fx(s),x))))

复制代码

任意空单元格输入以下公式：

1. =IFNA(DROP(REDUCE(0,FILTER(A2:A8,ISNA(MATCH(A2:A8,B:B,))),LAMBDA(x,y,VSTACK(x,fx(y)))),1),"")

复制代码

shaowu459

第四个问题，BOM展开。

下图A:C列示BOM清单父件和子件信息表，要求展开成右侧示例结果样式。例如A下级有B1、B2、B3，但是要从A开始，展开B1的下级，B1下级的下级……展开到最末级为止。然后展开A的第二个下级B2，然后直到末级。



这个例子也可以用递归方法来解决。首先定义名称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))

复制代码

在空单元格输入以下公式：

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

复制代码

shaowu459

第五个问题，BOM成本计算。

要求根据BOM清单和采购价格表来求生产成本。BOM清单如下图所示：


采购价格表如下图所示：


要求根据A:C列信息计算D列黄色区域成本。


此类问题用递归解决比较方便。首先定义名称kx：

1. =LAMBDA(x,SUM(BYROW(FILTER($C$11:$F$27,$A$11:$A$27&$B$11:$B$27=CONCAT(x)),LAMBDA(y,INDEX(y,3)/INDEX(y,4)*IF(INDEX(y,2)=0,VLOOKUP(@y,$A$31:$B$40,2,),kx(TAKE(y,,2)))))))

复制代码

D列输入以下公式向下填充即可：

1. =C3*kx(A3:B3)

复制代码

shaowu459

第六个问题，BOM结构展开。

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



参考公式如下：

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))))))))))

复制代码

其他方法参考以下链接：BOM结构父级子级

世界客都

第一个送花，日常支持超人版主

shaowu459

第七个问题，无人驾驶公交车线路问题。

题目描述如下图红框所示：


首先定义名称hx：

1. =LAMBDA(x,y,z,IF(@z="B",y,LET(s,FILTER(TAKE(x,,-2),(TAKE(x,,1)=@z)*ISERR(FIND(INDEX(x,,2),y)),0),REDUCE(0,SEQUENCE(ROWS(s)),LAMBDA(m,n,IF(SUM(s)=0,m,LET(P,INDEX(s,n,),VSTACK(m,hx(x,TEXTBEFORE(y,"：")+SUM(P)&"："&TEXTAFTER(y,"：")&"-"&@P,P)))))))))

复制代码

任意空单元格输入以下公式：

1. =LET(s,hx(VSTACK(A2:C13,SORTBY(A2:C13,{2,1,3})),"0：E","E"),t,--TEXTSPLIT(s,"："),FILTER(s,t=MIN(IF(t>0,t))))

复制代码

shaowu459

第八个问题，关联字母整合至一行。

下图左侧是原数据，要求整理成右侧的样式。举例来说，A和B、D在一行，三个都有关系，B又和C、R在一行，所以C、R和A、B、D都有关系，需要放在同一行，以此类推。


参考公式如下：

1. =DROP(REDUCE(0,A3:D17,LAMBDA(x,y,IF(OR(y=x,y=""),x,IFNA(VSTACK(x,REDUCE(y,ROW(1:99),LAMBDA(m,n,UNIQUE(HSTACK(m,TOROW(FILTER(A3:D17,BYROW(A3:D17,LAMBDA(z,OR(TOCOL(z)=m)))),1)),1)))),"")))),1)

复制代码