我有层次路径值:
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")