![层次结构路径:查找孤儿(没有父母的孩子)](https://linux22.com/image/1622434/%E5%B1%82%E6%AC%A1%E7%BB%93%E6%9E%84%E8%B7%AF%E5%BE%84%EF%BC%9A%E6%9F%A5%E6%89%BE%E5%AD%A4%E5%84%BF%EF%BC%88%E6%B2%A1%E6%9C%89%E7%88%B6%E6%AF%8D%E7%9A%84%E5%AD%A9%E5%AD%90%EF%BC%89.png)
我有层次路径值:
FLEET
FLEET \ AIR SYSTEM
FLEET \ AIR SYSTEM \ COMPRESSOR - DRYER
FLEET \ AIR SYSTEM \ VALVES
FLEET \ AIR SYSTEM \ RESERVOIRS
FLEET \ AIR SYSTEM \ HOSES - LINES - FITTINGS
FLEET \ ATTACHMENTS
FLEET \ ATTACHMENTS \ BLADE
FLEET \ ATTACHMENTS \ SALTER
FLEET \ ATTACHMENTS \ BROOM
FLEET \ ATTACHMENTS \ MOWER
FLEET \ ATTACHMENTS \ HITCH
FLEET \ ATTACHMENTS \ MISCELLANEOUS
["FLEET \ BODY EXTERIOR" --> parent is missing]
FLEET \ BODY EXTERIOR \ BODY PANELS
FLEET \ BODY EXTERIOR \ WIPERS
FLEET \ BODY EXTERIOR \ MIRRORS
FLEET \ BODY EXTERIOR \ ACCESSORIES
FLEET \ BODY EXTERIOR \ ACCESSORIES \ CATEGORY 1
FLEET \ BODY EXTERIOR \ ACCESSORIES \ CATEGORY 2
我正在寻找没有父母的孩子。
例如,我想指出第 16 行中的父级对于第 17-20 行而言是缺失的。
有没有办法在 Excel 中做到这一点?
答案1
一个办法:
=IF(COUNTIF(A:A,LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-2))=1,"","Error")
此公式标记了孤立的层次结构路径(没有父母的子路径)。
解释:
=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-2)
2) 用于countif
确定步骤#1中的值是否存在于 A 列中。
换句话说,检查该父级是否存在。
=IF(COUNTIF( ...Step #1... )=1,"","Error")