构建一个树，就像Excel中的数据表示一样？ [英] Build a tree like representation of data in Excel?

问题描述

我有一堆原始数据：

Parent  |  Data
---------------
Root    | AAA
AAA     | BBB
AAA     | CCC
AAA     | DDD
BBB     | EEE
BBB     | FFF
CCC     | GGG
DDD     | HHH

哪些需要转换成像时尚一样的树。
这基本上需要一个excel电子表格。
如何将上述数据转换成以下数据：

Which needs to be converted into a tree like fashion. This basically needs to end up in an excel spreadsheet. How can I convert the above data into the following:

AAA |      |
    | BBB  |
    |      | EEE
    |      | FFF
    | CCC  |
    |      | GGG
    | DDD  |
    |      | HHH

有没有办法只使用VBA？

Is there any easy way to do this using only VBA?

推荐答案

我相信你可以整理这个，但这可以用于你提供的数据集。

I'm sure you can tidy this up, but this will work on the data set you've provided.

开始之前，您需要定义两个名称（插入/名称/定义）。 数据是您的数据集的范围，目的地是您想要树的位置。

Before you start, you will need to define two Names (Insert / Name / Define). "Data" is the range of your dataset, "Destination" is the spot where you want the tree to go.

Sub MakeTree()

    Dim r As Integer
    ' Iterate through the range, looking for the Root
    For r = 1 To Range("Data").Rows.Count
        If Range("Data").Cells(r, 1) = "Root" Then
            DrawNode Range("Data").Cells(r, 2), 0, 0
        End If
    Next

End Sub

Sub DrawNode(ByRef header As String, ByRef row As Integer, ByRef depth As Integer)
'The DrawNode routine draws the current node, and all child nodes.
' First we draw the header text:
    Cells(Range("Destination").row + row, Range("Destination").Column + depth) = header

    Dim r As Integer
    'Then loop through, looking for instances of that text
    For r = 1 To Range("Data").Rows.Count
        If Range("Data").Cells(r, 1) = header Then
        'Bang!  We've found one!  Then call itself to see if there are any child nodes
            row = row + 1
            DrawNode Range("Data").Cells(r, 2), row, depth + 1
        End If
    Next
End Sub

这篇关于构建一个树，就像Excel中的数据表示一样？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！