英文:
Excel Reverse recursive(?) Search
问题
I have a hierarchy in an Excel spreadsheet.
我在Excel电子表格中有一个层次结构。
I've been need a formula to return the UUID of the parent. The parent is not always the cell above, neither are the hierarchy identifiers always decreasing. The offspring are always beneath their 'parent'.
我需要一个公式来返回父级的UUID。父级不总是在上面的单元格，层次标识也不总是递减的。子级总是在他们的“父级”下面。
My data looks as below and the 'ParentId' column is what I'd like to get to.
我的数据如下，我想要得到的是“ParentId”列。
| UUID | Hierarchy Ids | Hierarchy | ParentId |
|---|---|---|---|
| 961 | 1 | Granma Mavis | |
| 974 | 2 | Mother Mary | 961 |
| 987 | 3 | Child Chastity | 974 |
| 1000 | 4 | Baby Blue | 987 |
| 1013 | 4 | Baby Bjorn | 987 |
| 1026 | 4 | Baby Annabell | 987 |
| 1039 | 4 | Baby Maybe | 987 |
| 1052 | 3 | Child Sarah | 974 |
| 1065 | 2 | Mother Moira | 961 |
| 1078 | 3 | Child Claire | 1065 |
| 1091 | 4 | Baby Bo | 1078 |
| 1104 | 3 | Child Sally | 1065 |
| 1117 | 1 | Grandpa Joe | |
| 1130 | 2 | Father Gerald | 1117 |
| 1143 | 3 | Child Orson | 1130 |
| 1156 | 2 | Father Darren | 1117 |
| 1169 | 3 | Child Mylo | 1156 |
I've tried formulas including XLOOKUP, a bunch of nested ifs (doomed), VLOOKUP and LAMDA.
我尝试过包括XLOOKUP、一堆嵌套的if（注定失败）、VLOOKUP和LAMDA在内的公式。
VLOOKUP was close (but no cigar) as it'd only ever return the first parentid - meaning that all Children would have the same parent (a very busy person!).
VLOOKUP接近（但没有成功），因为它只会返回第一个parentid - 这意味着所有的子项都将有相同的父项（一个非常忙碌的人！）。
英文:
I have a hierarchy in an Excel spreadsheet.
I've been need a formula to return the UUID of the parent. The parent is not always the cell above, neither are the hierarchy identifiers always decreasing. The offspring are always beneath their 'parent'.
My data looks as below and the 'ParentId' column is what I'd like to get to.
| UUID | Hierarchy Ids | Hierarchy | ParentId |
|---|---|---|---|
| 961 | 1 | Granma Mavis | |
| 974 | 2 | Mother Mary | 961 |
| 987 | 3 | Child Chastity | 974 |
| 1000 | 4 | Baby Blue | 987 |
| 1013 | 4 | Baby Bjorn | 987 |
| 1026 | 4 | Baby Annabell | 987 |
| 1039 | 4 | Baby Maybe | 987 |
| 1052 | 3 | Child Sarah | 974 |
| 1065 | 2 | Mother Moira | 961 |
| 1078 | 3 | Child Claire | 1065 |
| 1091 | 4 | Baby Bo | 1078 |
| 1104 | 3 | Child Sally | 1065 |
| 1117 | 1 | Grandpa Joe | |
| 1130 | 2 | Father Gerald | 1117 |
| 1143 | 3 | Child Orson | 1130 |
| 1156 | 2 | Father Darren | 1117 |
| 1169 | 3 | Child Mylo | 1156 |
I've tried formulas including XLOOKUP, a bunch of nested ifs (doomed), VLOOKUP and LAMDA.
VLOOKUP was close (but no cigar) as it'd only ever return the first parentid - meaning that all Children would have the same parent (a very busy person!).
答案1
得分: 2
你可以使用XLOOKUP公式来实现这个。E2单元格中的公式（复制到下方单元格）：
=XLOOKUP(B2-1,$B$1:B1,$A$1:A1,"",0,-1)
假设: 父级将是从底部到顶部搜索中找到的第一个案例。
英文:
You can do this using an XLOOKUP formula. Formula in cell E2 (to be copied down) :
=XLOOKUP(B2-1,$B$1:B1,$A$1:A1,"",0,-1)
Assumption: Parent will be the one who is first case found from bottom to top search.
答案2
得分: 2
=TAKE(FILTER($A$2:$A2,$B$2:$B2<B3,""),-1)
英文:
You may try the following formula-
=TAKE(FILTER($A$2:$A2,$B$2:$B2<B3,""),-1)
答案3
得分: 0
以下是要翻译的内容：
根据这个公式，就可以使用 VLookup() 来完成，就像示例中的方式一样：
=VLOOKUP(E4,B$3:E$19,3,FALSE)
截图：
英文:
As far as I can judge, this can be done using a VLookup(), like in the example, based on this formula:
=VLOOKUP(E4,B$3:E$19,3,FALSE)
Screenshot:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论