517行BOM秒转树型，AI一口气写了3种，10万行却直接劝退

#多维表格
古哥计划
古哥计划 Lv.4 核心创作者KVP

Lv.4 核心创作者

🔥517行BOM秒转树型，AI一口气写了3种，10万行却直接劝退

做BOM的人，应该都遇到过这种纠结。

手里的BOM是父子型的：一行一个父件带一个子件，清一色的对应关系。算MRP需求、跑齐套分析，它是最好用的。可你要想看产品结构——一个成品下面套了几层、每层装了什么——父子型就抓瞎了，眼睛得在几百行里来回对。

树型BOM没这个烦恼：层级缩进一眼到底，0层是成品，.1是第一层，..2是第二层，整个产品自上而下拆得明明白白。

问题来了：父子型怎么一键变树型？

这期「AI写表格公式」第14天，我把一张517行的父子型BOM丢给灵犀，它一口气写了3种公式，最短的242个字符。先说结论：几百行的量级，公式完全扛得住；10万行往上，就得换Python或者多维表脚本了——文末有AI自己算的一笔账，很震撼。

一、先分清：父子型 vs 树型

父子型长这样：父件编码、子件编码，一行一对。

它适合MRP运算——上下层对应关系清楚，一个父件对应一个或多个子件，算毛需求、追欠料都靠它。缺点也明显：产品套了几层，表格上完全看不出来。

树型长这样：多了一列BOM层级，0、.1、..2，点号越多层越深。

它适合看产品结构——自上而下分解，一个成品拆到最末端的包装袋。两种形态没有谁好谁坏，是两套场景：

  • 算需求、跑MRP → 父子型

  • 看结构、查层级 → 树型

麻烦在于系统里的BOM往往按父子型存，想看结构就得自己转。手工缩进？517行你能对到眼花。

二、方法1：树视图前序缩排（673字符，速度85）

业务场景：要一张和附件一样的缩进树——每棵树从根行开始，子件紧跟，层越深缩进越多，共享件在每棵树里各出现一次。

AI的思路一句话：先把0层成品筛出来（父件编码从不出现在子件列的，就是根），再对每棵树做深度优先——根行后面紧跟.1子件行，子件自己也是父件的（中间件），它的子边再紧跟在后面，一路压栈。

公式：树视图前序缩排（写入I2，源数据A2:G518）

=LET(a,A2:A518,c,C2:C518,d,D2:D518,r0,UNIQUE(FILTER(A2:B518,COUNTIF(c,a)=0)),rr,CHOOSECOLS(r0,1),rn,CHOOSECOLS(r0,2),mset,UNIQUE(FILTER(a,COUNTIF(c,a)>0)),mtag,ISNUMBER(XMATCH(c,mset)),DROP(REDUCE("",SEQUENCE(ROWS(r0)),LAMBDA(x,ri,VSTACK(x,HSTACK(IF(SEQUENCE(1),INDEX(rr,ri)),IF(SEQUENCE(1),"0"),INDEX(rr,ri),INDEX(rn,ri)),DROP(REDUCE("",FILTER(SEQUENCE(ROWS(a)),a=INDEX(rr,ri)),LAMBDA(y,k,LET(o,HSTACK(IF(SEQUENCE(1),INDEX(rr,ri)),IF(SEQUENCE(1),".1"),INDEX(c,k),INDEX(d,k)),IF(INDEX(mtag,k),LET(f,FILTER(HSTACK(c,d),a=INDEX(c,k)),VSTACK(y,o,HSTACK(IF(SEQUENCE(ROWS(f)),INDEX(rr,ri)),IF(SEQUENCE(ROWS(f)),"..2"),CHOOSECOLS(f,1),CHOOSECOLS(f,2)))),VSTACK(y,o))))),1)))),1))

  • r0=UNIQUE(FILTER(A2:B518,COUNTIF(c,a)=0))：筛0层根，父件编码在子件列里数出来是0的就是成品，连产品名称一起取

  • mset+mtag：标记哪些子件自己是别人的父件（中间件），这些行后面要挂第二层

  • 外层REDUCE遍历92棵树，内层REDUCE压该树的1层边，中间件的子边紧跟其后

效果：92个根+468条1层+137条2层，输出697行，层级列文本0/.1/..2，0层代码列每行都是所属根编码，点筛选就能单看一棵树。推荐指数五星。

限制也摆在明面上：固定2层，3层得再嵌一层REDUCE；673个字符是三个方法里最长的；通用件被多棵树引用时按引用树重复展开（这是树视图的本意，但行数会膨胀）。

三、方法2：路径法（272字符，速度80）

业务场景：BOM超过2层怎么办？方法1要层层嵌REDUCE，层数一多公式就爆炸。方法2换个思路：不递归，给每条边算一个"路径编号"——根序号3位+逐层父行号4位，比如0010001表示1号根下的1号子边。

妙就妙在这里：这串编号按字典序一排，恰好就是深度优先的顺序。排序天然出树形。

辅助列机制（Q到X列，8层上限）：

  • Q列：父件是根的行，路径=根序&本行号

  • R到X列：每列沿父链推进一层，XLOOKUP找到父行的路径，接上自己的行号

公式：主公式（写入I2）

=LET(a,A2:A518,b,B2:B518,c,C2:C518,d,D2:D518,r,UNIQUE(FILTER(a,COUNTIF(C2:C518,a)=0)),E,X2:X518,SORTBY(VSTACK(HSTACK(r,IF(SEQUENCE(ROWS(r)),"0"),r,XLOOKUP(r,a,b)),HSTACK(INDEX(r,VALUE(LEFT(E,3))),REPT(".",(LEN(E)-3)/4)&((LEN(E)-3)/4),c,d)),VSTACK(XLOOKUP(r,a,Q2:Q518),E)))

  • 层级从路径长度反推：(LEN(路径)-3)/4，7字符是1层，11字符是2层

  • REPT(".",层级)生成缩进点号，几个点就是几层

  • 0层代码=按路径前3位反查所属根

效果：输出609行（92根+468条1层+49条2层），和方法3逐行比对0差异。辅助列复制一列就多支持一层，理论上任意深度。推荐指数五星。

边视图语义：共享件只归父件首个出现的位置展开一次。609行对比方法1的697行，差的88行就是共享件的重复展开。

四、方法3：固定5层+二分优化（242字符，速度90）

方法2已经很短了，AI还嫌不够快——它把目标直接定在5万行：筛根不再用COUNTIF全表扫，改用二分查找。

套路三步：

  • O列=SORT(C)，子件编码全量排序，这就是二分查找区

  • R列筛根：MATCH近似匹配定位，反查对不上的就是根

  • U列起5层辅助列，逐层推进路径

公式：筛根（写入R2）

=LET(q,Q2:Q182,m,MATCH(A2:A518,q,1),UNIQUE(FILTER(A2:A518,IFERROR(INDEX(q,m)<>A2:A518,TRUE))))

公式：主公式（写入I2）

=LET(a,A2:A518,b,B2:B518,c,C2:C518,d,D2:D518,r,R2:R93,E,AC2:AC518,SORTBY(VSTACK(HSTACK(r,IF(SEQUENCE(ROWS(r)),"0"),r,XLOOKUP(r,a,b)),HSTACK(INDEX(r,VALUE(LEFT(E,3))),REPT(".",(LEN(E)-3)/4)&((LEN(E)-3)/4),c,d)),VSTACK(XLOOKUP(r,a,U2:U518),E)))

亮点：主公式242字符全场最短，速度分90全场最高，517行秒出；5万行预期10~30秒（二分130万次+5层575万次+排序）。推荐指数五星。

固定5层上限，超过5层的边路径=0不输出；辅助列O到AC是命根子，删任何一列主公式直接瘫。

五、三种方法怎么选

方法

长度

速度

一句话思路

适用场景

方法1

673字符

85

REDUCE双层压栈

要附件那种缩进树，共享件按树展开

方法2

272字符

80

路径前缀排序

任意深度（8层起步可扩），边视图

方法3

242字符

90

二分筛根+固定5层

5万行级性能优先

短的未必适合你：要和附件长得一模一样，只有方法1的树视图；要看全部父子边，方法2、3更干净；数据量上万，直接方法3。

选型口诀：附件缩排用方法1，任意深度用方法2，5万行性能用方法3。

六、AI自己算的一笔账：10万行公式扛不住

写完三个方法，我问了灵犀一个扎心的问题：这些公式跑10万行会怎么样？

它先说结论：扛不住，预计10分钟到小时级，或者直接超时、超限。

它把账算得很细：

  • 共同瓶颈在0层筛根：COUNTIF(c,a)=0是N的平方复杂度，10万行=100亿次字符串比较，光这一步就1~5分钟

  • 方法1的内层REDUCE：每棵树里FILTER全表扫，一两万棵树×10万行=百万亿级比较，10~30分钟起步

  • 更要命的是输出：树视图20~30万行×4列=80~120万个单元格，超过spill上限（约100万），直接失败

所以AI给的建议很直接：10万行级转树型BOM，别用公式，用Python或者多维表的AirScript脚本——同样的深度优先逻辑，脚本一次遍历就完事。

公式不是万能的，知道它什么时候失效，比记住它更重要。

这也是日课做到第14天，第一次明确划出公式的边界：公式负责几百行级的日常（92棵树秒出），数据量再上一个量级，就该换工具了。

七、避坑清单

都是实测踩出来的：

  • WPS里FALSE不等于0：FILTER条件写=0碰到FALSE全表筛空直接#CALC!，要改成<>语义加TRUE兜底

  • 排序区的XLOOKUP不保证返回首个匹配：二分碰到重复值取哪个看运气，"首个出现"的语义必须回原始区取数

  • SORT不稳定：同值段内顺序会乱，想靠"排序加段首"定方案要留个心眼

  • 公式写进去必须读回来验一遍：方法2的主公式曾被静默清空，下游整条链#N/A，看着有数其实是错的

  • HSTACK塞单个值不广播：只有首行有值其余全是#N/A，层级列必须用IF(SEQUENCE(n),...)数组化

  • 辅助列是方案的一半：方法2的Q到X、方法3的O到AC，删任何一列主公式都瘫

最后说一句

以前转树型BOM，要么手工缩进对到眼花，要么开VBA写几十行。

现在517行丢进去，一个公式秒出，还能在3种思路里按数据脾气挑。

这就是「AI写表格公式」项目的第14天：每天一个PMC高频场景，AI写、我验、沉淀进技能库。14天下来，从填充空值到树型BOM，公式库已经攒了70多个方法。

想要完整公式清单的，评论区扣「树型」，我把云文档链接发你。

术语解释

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

BOM（Bill of Materials，物料清单）：工厂里"产品由什么组成"的清单，比如一台电饭煲等于1个锅盖加1个内胆加1个机身加几颗螺丝。

父子型BOM：一行记一对关系的清单写法，父件带子件，行数多但没有层级，适合电脑算需求。

树型BOM：带层级缩进的清单写法，像文件夹目录一样一层层展开，适合人看产品结构。

0层成品：BOM最顶上的东西——只当父件、从不当子件，也就是卖出去的那个成品。

MRP（Material Requirements Planning，物料需求计划）：根据订单和BOM自动算"要买什么、买多少、什么时候买"的方法。

REDUCE：动态数组函数，像流水线一样把一个列表逐个喂进去累加处理，最后吐出一份拼好的结果。

XLOOKUP：查找函数，拿一个值去表里找对应的另一个值，比VLOOKUP更灵活。

spill（溢出）：一个公式算出一片结果自动铺开的机制，一片结果有上限（约100万个单元格），超了直接报错。

AirScript：WPS多维表格内置的脚本语言，跑批量逻辑比公式快得多。

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

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

#AI写表格公式 #动态数组 #BOM #PMC #WPS表格

本文由 AI 辅助创作

工具：灵犀专业版

模型：GLM-5.3-Flash 超高

灵犀 灵犀
浏览 288
收藏
11
分享
11 +1
+1
全部评论