excel-formula - 使用 Excel 公式将层次结构树转换为父/子表

我正在尝试将具有 5 个级别的层次结构转换为具有 2 列的父/子表。

我需要使用 Excel 公式而不是 VBA 脚本来执行此操作。我可以用Index和Match功能做到这一点吗？

如果层次结构发生变化，父/子表应该是动态的并自动更新。

我能够使用下面的数组公式填充儿童，即 B2 中的北美：

{=INDEX(A3:E3,MATCH(FALSE,ISBLANK(A3:E3),0))}

但是我正在寻找一个填充父母的公式。

预期的父/子表：

标签： excel-formulaparent-childhierarchy

我认为适用于孩子的INDEXandMATCH方法不适用于父母，因为：

您的数据结构意味着每行只有一条信息，因此{=INDEX(A3:E3,MATCH(FALSE,ISBLANK(A3:E3),0))}始终适用于子值
但是，该子值的父值是前一列中的值，并且在子行上方的行数未知。因此，因为INDEX/MATCH方法不返回子节点的位置，以便我们可以找出需要识别和处理的先前列等。

我们可以考虑父值在列的行中，非空值的最大索引直到并包括该行。

例如对于西班牙，检查父级的范围是：

例如，对于洛杉矶，检查父级的范围是：

因此，要建立正确的范围来检查子项的父值，您需要偏移 -1 列并从最小行 (2) 到最大行（子项的行）。

为此，我们需要得到：

所以它最终会是这样的：

在哪里：

子地址

C_row：=ROW(A2:E2)只是层次结构的行
C_col：=SUMPRODUCT(--NOT(ISBLANK(A2:E2)),COLUMN(A2:E2))是公式的非数组公式版本，它返回非空白单元格的索引而不是值本身
c_add：是per及以上=ADDRESS(G2,H2)子值的地址C_rowC_col

P_col：=H2-1是子列（）的前C_col一列；我们知道有父值
p_add_min：=IF(J2>0,ADDRESS(2,J2),"zzz")是包含父级的范围的最小行的地址，带有一个条件来标识层次结构的根（即Universe没有父级）
p_add_max：是包含父级的范围的最大行的地址，与层次结构的re根=IF(J2>0,ADDRESS(G2,J2),"zzz")条件相同P_add_min
P_row：=IF(J2>0,AGGREGATE(14,4,(NOT(ISBLANK(INDIRECT(K2&":"&L2)))*ROW(INDIRECT(K2&":"&L2))),1),"zzz")表示子节点不是层次结构的根，在上面建立的最小和最大行之间的子节点的前LARGE一列中获取非空值的 st 索引

上述方法考虑了任意数量的组件的层次结构，其中层次结构的最大深度为 5（例如，列中的叶子E）。

如果您还想拥有任意深度，那么您需要将辅助列和输出列分隔到不同的工作表中，例如

乌鸦：=ROW(Sheet1!2:2)
C_col：=SUMPRODUCT(--NOT(ISBLANK(Sheet1!2:2)),COLUMN(Sheet1!2:2))
C_添加：=ADDRESS(A2,B2,1,1,"Sheet1")
P_col：=B2-1
P_add_min：=IF(D2>0,ADDRESS(2,D2),"zzz")
P_add_max：=IF(D2>0,ADDRESS(A2,D2),"zzz")
P_row:=IF(D2>0,AGGREGATE(14,4,(NOT(ISBLANK(INDIRECT("Sheet1!"&E2&":"&F2)))*ROW(INDIRECT("Sheet1!"&E2&":"&F2))),1),"zzz")
家长：=IF(D2>0,INDIRECT(ADDRESS(G2,D2,1,1,"Sheet1")),"zzz")
孩子：=INDIRECT(C2)

您可以继续重命名zzz，""以整理内容。

高温高压