如何在Google Sheets中基于层级编码自动生成父节点编码？

编码层级规则 #

Level 1 XX000000
Level 2 XXXX0000
Level 3 XXXXXX00
Level 4 XXXXXXXX

需求说明 #

现有超过75000个节点，需为每个节点生成对应父节点编码：

• Level 1节点（后6位为0）无父节点，返回空值
• Level 2节点（后4位为0）父节点为对应Level 1编码（前2位+6个0）
• Level 3节点（后2位为0）父节点为对应Level 2编码（前4位+4个0）
• Level 4节点（后2位非0）父节点为对应Level 3编码（前6位+2个0）

示例对照 #

测试数据集（含错误标注） #

CodedName   Parent
16099000    16090000
16099090    16099000
16100000    16000000
16100100    16100000
16100190    16100100
16100200    16100000
16100201    16100200
16100202    16100200
16100203    16100200
16100290    16100200
16100300    16100000
16100301    16100300
16100302    16100300
16100390    16100300
16109000    16100000
16109090    16109000
16110000    16100000
16110100    16110000
16110190    16110100
16110200    16110000
16110290    16110200
16110300    16110000
16110390    16110300
16110400    16110000
16110490    16110400
16110500    16110000
16110590    16110500
16110600    16110000
16110690    16110600
16110700    16110000
16110790    16110700
16110800    16110000
16110890    16110800
16110900    16110000
16110901    16110900
16110990    16110900
16111000    16110000
16111001    16111000
16111002    16111000
16111090    16111000
16111100    16111000
16111101    16111100
16111102    16111100
16111103    16111100
16111190    16111100
16119000    16110000
16119090    16119000
16120000    16100000
16120100    16120000
16120190    16120100
16120200    16120000
16120290    16120200
16129000    16120000
16129090    16129000
16130000    16100000
16130100    16130000
16130190    16130100
16139000    16130000
16139090    16139000
16140000    16100000
16140100    16140000
16140190    16140100
16140200    16140000
16140290    16140200
16149000    16140000
16149090    16149000
16150000    16100000
16150100    16150000
16150101    16150100
16150102    16150100
16150103    16150100
16150190    16150100
16150200    16150000
16150201    16150200
16150290    16150200
16150300    16150000
16150301    16150300
16150390    16150300
16159000    16150000
16159090    16159000
16160000    16100000
16160100    16160000
16160101    16160100
16160190    16160100
16160200    16160000
16160201    16160200
16160202    16160200
16160203    16160200
16160204    16160200
16160290    16160200
16160300    16160000
16160390    16160300
16169000    16160000
16169090    16169000
16170000    16100000
16170100    16170000
16170101    16170100
16170102    16170100
16170103    16170100
16170104    16170100
16170105    16170100
16170106    16170100
16170190    16170100
16170200    16170000
16170201    16170200
16170202    16170200
16170203    16170200
16170204    16170200
16170205    16170200
16170206    16170200
16170207    16170200
16170290    16170200
16179000    16170000
16179090    16179000
16180000    16100000
16189000    16180000
16189090    16189000
17000000    10000000 xx wrong, should be blank
17010000    17000000
17010100    17010000
17010190    17010100
17010191    17010190 xx wrong, should be 00
17010192    17010190 xx wrong, should be 00
17010200    17010000
17010290    17010200
17010400    17010000
17010490    17010400
17010491    17010490 xx wrong, should be 00
17010492    17010490 xx wrong, should be 00
17010700    17010000
17010790    17010700
17010791    17010790 xx wrong, should be 00
17010792    17010790 xx wrong, should be 00
17139000    17130000

公式解决方案 #

假设CodedName列在A列，在B2单元格输入以下公式，下拉填充即可：

=IF(RIGHT(A2,6)="000000","",IF(RIGHT(A2,4)="0000",LEFT(A2,2)&"000000",IF(RIGHT(A2,2)="00",LEFT(A2,4)&"0000",LEFT(A2,6)&"00")))

公式解释 #

• 第一层判断：如果编码后6位是000000（Level 1），返回空值
• 第二层判断：如果编码后4位是0000（Level 2），取前2位拼接6个0得到Level 1父编码
• 第三层判断：如果编码后2位是00（Level 3），取前4位拼接4个0得到Level 2父编码
• 默认情况：Level 4编码，取前6位拼接2个0得到Level 3父编码

验证说明 #

将公式应用到测试数据集后，可自动修正其中的错误：

• 17000000的父节点会返回空值，纠正原错误标注
• 17010191、17010192的父节点会生成17010100，纠正原错误标注
• 17010491、17010492的父节点会生成17010400，纠正原错误标注
• 17010791、17010792的父节点会生成17010700，纠正原错误标注

内容的提问来源于stack exchange，提问作者Isak La Fleur