在 Excel 中构建一个类似于数据表示的树? [英] Build a tree like representation of data in Excel?

问题描述

我有一堆这种方式的原始数据:

I have a bunch of raw data in this fashion:

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.

在开始之前，您需要定义两个名称(插入/名称/定义).Data"是数据集的范围，Destination"是你想让树去的地方.

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屋！