层次结构路径:查找孤儿(没有父母的孩子)

层次结构路径:查找孤儿(没有父母的孩子)

我有层次路径值:

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

在此处输入图片描述

此公式标记了孤立的层次结构路径(没有父母的子路径)。


解释:

1)从层次结构路径中删除最低级别:

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

相关内容