使用Excel，如何在层次结构中查找父单元格？ [英] Using Excel, how to find parent cell in a hierarchy?
问题描述
我在Windows 10上运行Excel 2013，内存为16 GB。
Am running Excel 2013 on Windows 10 with 16 GB memory.
我有一个类似于下面图1的分层结构。我的结构是1000多行，深达27个级别。
I have a hierarchical structure similar to figure 1 below. My structure is 1000+ rows and as deep as 27 levels.
对于上面描述的树，如何识别单元格的父母？
这是我迄今为止所做的。使用条件格式，并且这个公式：
Here's what I've done so far. Using conditional formatting, and this formula:
=AND(A1<>"",OFFSET(A1,1,1)="")
我能够识别树的叶节点，如图2所示
I am able to identify the leaf nodes of the tree, as shown in figure 2 below.
现在我需要找到直接的祖先树（除了没有父项的第一行除外）的每个单元格（子）的父（父））。
Now I need to find the immediate ancestor (parent) of every cell (child) in the tree (with the exception of the first row that does not have a parent).
图3（下图）显示了手动完成的示例：
Figure 3 (below) shows an example done manually:
此外，我可以将树与轮廓相关联。
然后，我可以计算大纲中的小数位数，以便使用以下公式跟踪级别更改：
Further, I can associate the tree with an outline. Then I am able to count the number of decimals in the outline in order to track level changes using this formula:
=LEN(A2)-LEN(SUBSTITUTE(A2,".",""))
如下图4所示：
如何以编程方式完成图3中手动完成的操作？
对于每行（记录在树外部的列中）打印单元格的单元格的位置。
这是一个类似的问题，手动提供级别信息。
Here is a similar question that manually provides "level" information.
在我的情况下，我需要从一个大纲（如图所示）或使用条件格式的公式。
In my case, I need to work from an outline (as shown) or by using a formula for Conditional formatting.
推荐答案
根据您的规则，单元格的父级是第一个非空单元格，从底部到顶部，立即在左边的列中：
According to your rules, the parent of a cell is the first non-empty cell, bottom to top, in the column immediately to the left:
public function GetParentCell(byval c as range) as range
set GetParentCell = c.offset(0,-1).end(xlUp)
end function
把它放在一个模块中，你可以从工作表中使用它，例如
Having put this in a module, you can use it from the sheet, e.g.
=CELL("address", GetParentCell(D4))
显示 D4 。
如果你想完全避免VBA，你必须找到一种方法来转换结束（xlUp）到一个可能会很困难的公式，因为Excel查找从上到下的工作你想要另一回事。如果我这样做，我将从列中的最后一个非空单元格开始。
If you want to avoid VBA completely, you have to find a way to convert End(xlUp) into a formula which may prove difficult because Excel lookups work from top to bottom and you want the other way round. If I was to do that, I would start from Last non-empty cell in a column.
鉴于层次结构包含在 A1：G19 中：
Given that the hierarchy is contained in A1:G19:
-
在
H1中，放置：
=MATCH("x",$A1:$G1,0)
c $ c> I1 ，put
=CELL("address",INDEX(INDEX($A$1:$G$19,1,H1-1):INDEX($A$1:$G$19,ROW(),H1-1),MAX((INDEX($A$1:$G$19,1,H1-1):INDEX($A$1:$G$19,ROW(),H1-1)<>"")*(ROW(INDEX($A$1:$G$19,1,H1-1):INDEX($A$1:$G$19,ROW(),H1-1))))))
作为数组公式（ CTRL + Shift + 输入）
as an array formula (CTRL+Shift+Enter).
信用转到 https://stackoverflow.com/a/5442955/11683 ，因为它只不过是一个动态版本的 = INDEX（A：A，MAX（（A：A<> 中）*（ROW（A：A））））。
The credit goes to https://stackoverflow.com/a/5442955/11683 because it is nothing more than a dynamic version of =INDEX(A:A,MAX((A:A<>"")*(ROW(A:A)))).
拖动所有
列 I 中的公式返回每个单元格的父项作为真正的引用，然后由 CELL（）函数检查以显示地址。您可以删除 CELL（）并以不同的方式使用计算的引用。
The formula in column I returns the parent of each cell as a true reference which is then examined by the CELL() function to show the address. You can remove the CELL() and use the calculated reference in a different way.
这篇关于使用Excel，如何在层次结构中查找父单元格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
