🌳树型BOM里每行的上层物料怎么找？3种公式写法，最快73个字符

昨天把父子型BOM转成了树型BOM（前序缩排版），后台马上有粉丝问了下一步：树是出来了，那每一行的物料，它的上一层是谁？

这个问题在PMC里太常见了。算齐套要往上追父件，算成本要归集到成品，查一个半成品挂在哪个组件下面，都得先知道"上层是谁"。触发词就是那几个：上层代码、上层物料、父件物料、母件代码——说的全是同一件事。

今天这个问题我用3种写法跑了一遍，697行树型BOM实测，最快的写法主公式只有73个字符，5W行数据预估2~4秒。

一、数据长什么样

【截图：方法1表A~D列树型BOM数据】

A~D列是昨天的成果：0层代码、BOM层级、存货编码、存货名称。B列层级有三种标记：0是根成品，.1是一层子件，..2是两层子件。697行里，0层92行、.1层468行、..2层137行。

树视图有个天然规律：

..2行的爹，是它前面最近的一个.1行；.1行的爹，是最近的一个0层行；0层行是根，没有爹。前序缩排保证了这个规律100%成立。所以全部方法都是在回答同一个问题：怎么找"最近"。

二、方法1：MAP层级回溯，一行一行往上找（155字符）

=LET(B,B2:B698,C,C2:C698,D,UNIQUE(B),A,MAP(B,C,XLOOKUP(B,D,VSTACK(0,DROP(D,-1))),LAMBDA(x,y,z,XLOOKUP(z,B2:x,C2:y,A2,,-1))),HSTACK(A,XLOOKUP(A,C,D2:D698)))

三步走：

• UNIQUE(B)：把层级列收成谱系 0 / .1 / ..2

• XLOOKUP(B,D,VSTACK(0,DROP(D,-1)))：把每行映射成"上一层级"标记——.1行标记"0"，..2行标记".1"，0层标记数值0。VSTACK头尾一凑，正好错位一格

• MAP逐行把查找范围扩到当前行，XLOOKUP第7参数**-1从后往前找**，命中的就是最近一个上一层级的编码，HSTACK后再XLOOKUP带出上层描述

亮点在那个**-1**（从后往前找）。"最近一次出现"这种问题，别人还在想怎么定位，-1直接一步干掉。0层行查不到（数值0和文本"0"对不上类型），落默认值A2，正好是根。

这个思路是我写的，优点是直觉——人翻树就是一层一层往上翻。代价是MAP逐行扩展范围，697行大概24万次比较，行数上5W就是250亿，想都别想。

【截图：方法1表E2公式与输出】

三、方法2：SCAN双游标，两个游标往前推（196字符）

=LET(B,B2:B698,C,C2:C698,D,D2:D698,pa,SCAN("",IF(B="0",C,""),LAMBDA(a,b,IF(b="",a,b))),pb,SCAN("",IF(B=".1",C,""),LAMBDA(a,b,IF(b="",a,b))),E,IF(B="0",C,IF(B=".1",pa,pb)),HSTACK(E,XLOOKUP(E,C,D)))

思路从"往回找"换成"往前扫"。扫的过程中维护两个游标：

• pa：记住最近一个0层行的编码

• pb：记住最近一个.1层的编码

每行到了，看自己什么层级，直接取对应游标就是爹。IF(b="",a,b)是灵魂——当前行不是目标层级就传空，空就保留旧值，游标就这么"粘"住了最近值。

SCAN的累加器是标量文本，没有压栈开销，两次线性扫描干完全部活，比MAP快一个档。

【截图：方法2表E2公式】

四、方法3：LOOKUP二分+辅助列，主公式73字符

E2主公式：
=LET(p,P2:P698,IF(p=0,C2:C698,CHOOSE(p,Q2:Q698,R2:R698,S2:S698,T2:T698)))

Q2（1层父编码，R/S/T同构改条件数字）：
=LET(s,SEQUENCE(697),LOOKUP(s,s/(P2:P698=0),C2:C698))

方法3把"快"拆成三刀：

• 深度用 LEN(B)-1 算，P列一个公式得0/1/2/3/4

• 每层一条LOOKUP：s/(dp=k)把非本层全变成#DIV/0!，LOOKUP二分天生跳错误值，取≤当前行的最大行号，直接吐父编码。四层四条，全内建函数，一次LAMBDA都没调

• 主公式 CHOOSE(p,Q,R,S,T) 按深度配对选列，0层走IF取自身

F列上层描述也别全表XLOOKUP，那是又一个O(n²)。建一个273行的唯一编码查找表（SORT(UNIQUE(C2:C698))升序），XLOOKUP(E,V,W,,,2)第6参数写2走二分，查找量从行数平方降到行数×log273。

【截图：方法3表辅助列P~T与主公式】

【截图：公式汇总表目录】

五、0层那92行，两种口径

0层是顶层成品，没有爹，但公式总得给个数：

• 方法1：填A2占位（MAP没传本行A列，默认值写死了第一行根），92个0层行的"上层"全是第一个成品编码

• 方法2/方法3：填它自己，当"根标识"用

92行里有91行不一样。业务上我更倾向填自己——和树视图0层代码列呼应，筛选也方便。但两种都不算错，动手前先定口径。

六、这次踩的3个坑

1. 层级列会变数值。 把".1"复制到新表，ET直接转成数值0.1，"0"变数值0。文本比较IF(B="0",...)全落空，公式静默清空。修复：clear之后撇号前缀重写，"'"&值强制文本。

2. LET变量名别带数字。p0/p1被解析成单元格地址P0/P1——P1是合法引用——#NAME?，公式再次静默清空。改名pa/pb。这个坑上次写多列合并单元格时踩过，这是第二次。

3. 查找表范围别按经验写死。 我按昨天的"181个唯一子件"写了V2:V182，今天树视图唯一编码实际273个（多了92个根），F列498行#N/A。查找表范围必须实测，别跨场景套数。

七、选型口诀

八、最后说一句

树型BOM判断上层，说到底是"找最近"问题家族的新成员。快速填充空值是找最近一条有值的记录，合并单元格转一维是找最近的组头，这次的BOM上层是找最近的上一层级行——底层逻辑一模一样。

SCAN游标和LOOKUP二分这两把刀，一个管线性扫、一个管排序找，建议都收好。下次再遇到"最近一条XX"，先想想这两把，十有八九够用。

术语解释

文章里出现的技术名词，一句话说清楚：

树型BOM（Tree BOM）：把BOM的父子关系按缩进层级排成一棵树的展示形式，根成品在最上面，子件一层层往下缩。

前序缩排：父行排在子行前面、靠点号数表示深度的排列方式，比如0层没有点、一层一个点、两层两个点。

0层：BOM最顶层的成品，没有上级物料。

SCAN：动态数组函数，从前往后逐行扫描，每行带着一个累加值往前传，适合做"最近的XX"这类滚动记忆。

LOOKUP二分：LOOKUP函数在升序数组里折半查找，自动跳过错误值，取小于等于查找值的最大一条，比逐行扫快得多。

辅助列：把一条大公式拆成多列驻留在表格里的中间结果，每列干一小步，主公式只做最后拼装。

O(n²)：运算量随行数的平方增长，行数翻10倍、耗时翻100倍的数据量级说法。

古老师（古哥计划）｜中小制造数字化专家｜金山 KVP（金山办公最有价值专家）｜金山多维表格应用场景专家｜金山 WPS 社区优秀创作者

深耕中小制造业数字化落地，擅长用 WPS 多维表格 + AI 低代码方案，帮工厂快速搭建进销存、生产计划、质量追溯等轻量化系统。不用复杂 IT，低成本落地，已服务数百家制造企业，实战经验丰富。

#多维表格 #工厂管理 #AI应用

本文由 AI 辅助创作

工具：灵犀专业版模型：GLM-5.3-Flash 超高