多维表父记录还能这样玩

最近多维表出了父记录，很多群友都不知道有什么用？或者是怎么用？

本文介绍一下父记录的玩法：

使用本表的公式如遇到问题或者发现任何BUG，欢迎与我（wx:leeaqua)联系。

父智能编号

📌	IF(ISBLANK([@父记录]),COUNTIFS([编号],"<="&[@编号],[父记录],"")&"",[@父记录]&"."&COUNTIFS([编号],"<="&[@编号],[父记录],[@父记录]))

说明：公式中&""的作用是为了让countifs的值由数字转为文本

父智能编号2

📌	IF(ISBLANK([@父记录])," "&COUNTIFS([编号],"<="&[@编号],[父记录],""),[@父记录]&"."&COUNTIFS([编号],"<="&[@编号],[父记录],[@父记录]))

说明：表面上看上面两个公式的结果是一样的，实际上第二个公式的前方多了一个" ",这样写的目的是为了之后查找引用树的时候，能够排除干扰项，比如如果存在1.1.1和2.1.1，在查找1.1的时候会把这两个都匹配到，因此如果是： 1.1.1和 2.1.1的话，在查找 1.1的时候，就不会匹配 2.1.1了。

分级父编号

📌	IF(ISBLANK([@父记录]),1,1--TRIM([@父记录]))

分级父编号2

📌	IF(ISBLANK([@父记录]),1&"级",1--SUBSTITUTE([@父记录],"级",)&"级")

分级父编号3

📌	SWITCH([@父记录],,"1️⃣","1️⃣","2️⃣","2️⃣","3️⃣","3️⃣","4️⃣","4️⃣","5️⃣","5️⃣","6️⃣")

颜色父编号

📌	SWITCH([@父记录],,"🔴","🔴","🟠","🟠","🟡","🟡","🟢","🟢","🔵","🔵","🟣") IF(ISBLANK([@父记录]),"🔴",IF([@父记录]="🔴","🟠",IF([@父记录]="🟠","🟡",IF([@父记录]="🟡","🟢",IF([@父记录]="🟢","🔵",IF([@父记录]="🔵","🟣")))))) IFS(ISBLANK([@父记录]),"🔴",[@父记录]="🔴","🟠",[@父记录]="🟠","🟡",[@父记录]="🟡","🟢",[@父记录]="🟢","🔵",[@父记录]="🔵","🟣")

颜色父编号2

📌	SWITCH([@父记录],,"⭕","⭕","⭕⭕","⭕⭕","⭕⭕⭕","⭕⭕⭕","⭕⭕⭕⭕","⭕⭕⭕⭕","⭕⭕⭕⭕⭕","⭕⭕⭕⭕⭕","⭕⭕⭕⭕⭕⭕")

颜色父编号3

📌	SWITCH([@父记录],,"⭕⭕⭕⭕⭕⭕","⭕⭕⭕⭕⭕⭕","⭕⭕⭕⭕⭕","⭕⭕⭕⭕⭕","⭕⭕⭕⭕","⭕⭕⭕⭕","⭕⭕⭕","⭕⭕⭕","⭕⭕","⭕⭕","⭕")

筒子父编号

📌	SWITCH([@父记录],,"🀙","🀙","🀚","🀚","🀛","🀛","🀜","🀜","🀝","🀝","🀞") IF(ISBLANK([@父记录]),"🀙",IF([@父记录]="🀙","🀚",IF([@父记录]="🀚","🀛",IF([@父记录]="🀛","🀜",IF([@父记录]="🀜","🀝",IF([@父记录]="🀝","🀞")))))) IFS(ISBLANK([@父记录]),"🀙",[@父记录]="🀙","🀚",[@父记录]="🀚","🀛",[@父记录]="🀛","🀜",[@父记录]="🀜","🀝",[@父记录]="🀝","🀞")

复制编号

📌	IF(ISBLANK([@父记录]),COUNTIFS([编号],"<="&[@编号],[父记录],"")&"",[@父记录])

同组编号

📌	同级编号： IF(ISBLANK([@名称]),"",IF(ISBLANK([@父记录]),COUNTIFS([编号],"<="&[@编号],[父记录],"")&"",XLOOKUP([@父记录],[名称],[辅助]))) 辅助： IF(ISBLANK([@名称]),"",IF(ISBLANK([@父记录]),[@同组编号],XLOOKUP([@父记录],[名称],[同组编号]))) 说明：这里的公式利用了一个循环机制，两个公式字段相互引用。

父子树

📌

上级编号：

IF(ISBLANK([@父记录]),"",XLOOKUP([@父记录],[名称],[树编号],""))

树编号：

IF(ISBLANK([@父记录]),IF(ISBLANK([@父记录])," "&COUNTIFS([编号],"<="&[@编号],[父记录],""),COUNTIFS([编号],"<="&[@编号],[父记录],[@父记录]))&".",[@上级编号]&IF(ISBLANK([@父记录]),COUNTIFS([编号],"<="&[@编号],[父记录],""),COUNTIFS([编号],"<="&[@编号],[父记录],[@父记录]))&".")

级别：

TEXT(LEN([@树编号])-LEN(SUBSTITUTE([@树编号],".",)),"[dbnum1]")&"级"

上级地址：

IF(ISBLANK([@父记录]),"",XLOOKUP([@父记录],[名称],[组合名称],""))

组合名称：

IF(ISBLANK([@父记录]),[@名称],[@上级地址]&[@名称])