如何在excel中构建父子数据表？ [英] how to build parent-child data table 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 below like fashion. This basically needs to end up in an excel spreadsheet. How can I convert the above data into the following:
级别
1 | 2 | 3
AAA | BBB |
AAA | BBB | EEE
AAA | BBB | FFF
AAA | CCC |
AAA | CCC | GGG
AAA | DDD |
AAA | DDD | HHH
推荐答案
我昨天晚上开始并完成了答案。在一天的寒冷中，需要至少一些扩展。
I started and finished the answer below late last night. In the cold light of day it needs at least some expansion.
Sheet2，源数据，运行宏之前：
Sheet2, source data, before the macro is run:
Sheet3，结果，宏运行后：
Sheet3, result, after the macro is run:
该方法的基础是创建将每个子项链接到其父项的数组。然后，宏跟随每个孩子的链，它的插件生成一个字符串：child，parent | child，grandparent | parent | child，...排序后，这是准备保存的结果。
The basis of the method is to create arrays that link each child to its parent. The macro then follows the chain from each child up its ancesters growing a string: child, parent|child, grandparent|parent|child, ... After sorting, this is the result ready for saving.
通过示例数据，可以组合步骤1和3，因为所有的名称和行都是按字母顺序排列的。一步建立名称列表并将它们链接到另一个步骤中，就可以生成一个简单的宏，而不管序列如何。在反思中，我不知道如果步骤2，排序名称是必要的。排序安装名称列表，步骤5是必要的。输出后排序Sheet3是不可能的，因为可能会有三个以上的级别。
With the example data, Steps 1 and 3 could be combined because all the names and rows are in alphabetic order. Building the list of names in one step and linking them in another makes for a simple macro regardless of the sequence. On reflection, I am not sure if step 2, sorting the names, is necessary. Sorting the ancester name lists, step 5, is necessary. Sorting Sheet3 after output is not possible because there might be more than three levels.
我不知道这是否等于一个优雅的解决方案，但它很简单。
I am not sure if this counts as an elegant solution but its pretty simple.
我把源数据放在工作表Sheet2中，我输出到Sheet3。
I have placed the source data in worksheet Sheet2 and I output to Sheet3.
有七个阶段：
- 构建数组包含每个名称的子项。
- 排序数组儿童。我提供了一个简单的排序，足以进行示范。如果您有足够的名称要求，可以在互联网上获得更好的排序。
- 构建数组Parent，以使Parent（N）是Child（N）的父级的Child内的索引。
- 将数组中的数组ParentName通过跟踪数组中的指针从小到母到祖父母到...执行此操作时，确定最大级别数。
- 在输出工作表中生成一个标题行
- 将ParentName复制到输出工作表。
- Build array Child containing every name.
- Sort array Child. I have provided a simple sort which is adequate for a demonstration. Better sorts are available on the internet if you have enough names to require it.
- Build array Parent such that Parent(N) is the index within Child of the parent of Child(N).
- Build array ParentName by following the pointers in array Parent from child to parent to grandparent to ... While doing this, determine the maximum number of levels.
- Sort array ParentName.
- Build a header row in the output sheet.
- Copy ParentName to the output sheet.
我相信我已经包含了足够的注释来代码可以理解。
I believe I have included enough comments for the code to be understandable.
Option Explicit
Sub CreateParentChildSheet()
Dim Child() As String
Dim ChildCrnt As String
Dim InxChildCrnt As Long
Dim InxChildMax As Long
Dim InxParentCrnt As Long
Dim LevelCrnt As Long
Dim LevelMax As Long
Dim Parent() As Long
Dim ParentName() As String
Dim ParentNameCrnt As String
Dim ParentSplit() As String
Dim RowCrnt As Long
Dim RowLast As Long
With Worksheets("Sheet2")
RowLast = .Cells(Rows.Count, 1).End(xlUp).Row
' If row 1 contains column headings, if every child has one parent
' and the ultimate ancester is recorded as having a parent of "Root",
' there will be one child per row
ReDim Child(1 To RowLast - 1)
InxChildMax = 0
For RowCrnt = 2 To RowLast
ChildCrnt = .Cells(RowCrnt, 1).Value
If LCase(ChildCrnt) <> "root" Then
Call AddKeyToArray(Child, ChildCrnt, InxChildMax)
End If
ChildCrnt = .Cells(RowCrnt, 2).Value
If LCase(ChildCrnt) <> "root" Then
Call AddKeyToArray(Child, ChildCrnt, InxChildMax)
End If
Next
' If this is not true, one of the assumptions about the
' child-parent table is false
Debug.Assert InxChildMax = UBound(Child)
Call SimpleSort(Child)
' Child() now contains every child plus the root in
' ascending sequence.
' Record parent of each child
ReDim Parent(1 To UBound(Child))
For RowCrnt = 2 To RowLast
If LCase(.Cells(RowCrnt, 1).Value) = "root" Then
' This child has no parent
Parent(InxForKey(Child, .Cells(RowCrnt, 2).Value)) = 0
Else
' Record parent for child
Parent(InxForKey(Child, .Cells(RowCrnt, 2).Value)) = _
InxForKey(Child, .Cells(RowCrnt, 1).Value)
End If
Next
End With
' Build parent chain for each child and store in ParentName
ReDim ParentName(1 To UBound(Child))
LevelMax = 1
For InxChildCrnt = 1 To UBound(Child)
ParentNameCrnt = Child(InxChildCrnt)
InxParentCrnt = Parent(InxChildCrnt)
LevelCrnt = 1
Do While InxParentCrnt <> 0
ParentNameCrnt = Child(InxParentCrnt) & "|" & ParentNameCrnt
InxParentCrnt = Parent(InxParentCrnt)
LevelCrnt = LevelCrnt + 1
Loop
ParentName(InxChildCrnt) = ParentNameCrnt
If LevelCrnt > LevelMax Then
LevelMax = LevelCrnt
End If
Next
Call SimpleSort(ParentName)
With Worksheets("Sheet3")
For LevelCrnt = 1 To LevelMax
.Cells(1, LevelCrnt) = "Level " & LevelCrnt
Next
' Ignore entry 1 in ParentName() which is for the root
For InxChildCrnt = 2 To UBound(Child)
ParentSplit = Split(ParentName(InxChildCrnt), "|")
For InxParentCrnt = 0 To UBound(ParentSplit)
.Cells(InxChildCrnt, InxParentCrnt + 1).Value = _
ParentSplit(InxParentCrnt)
Next
Next
End With
End Sub
Sub AddKeyToArray(ByRef Tgt() As String, ByVal Key As String, _
ByRef InxTgtMax As Long)
' Add Key to Tgt if it is not already there.
Dim InxTgtCrnt As Long
For InxTgtCrnt = LBound(Tgt) To InxTgtMax
If Tgt(InxTgtCrnt) = Key Then
' Key already in array
Exit Sub
End If
Next
' If get here, Key has not been found
InxTgtMax = InxTgtMax + 1
If InxTgtMax <= UBound(Tgt) Then
' There is room for Key
Tgt(InxTgtMax) = Key
End If
End Sub
Function InxForKey(ByRef Tgt() As String, ByVal Key As String) As Long
' Return index entry for Key within Tgt
Dim InxTgtCrnt As Long
For InxTgtCrnt = LBound(Tgt) To UBound(Tgt)
If Tgt(InxTgtCrnt) = Key Then
InxForKey = InxTgtCrnt
Exit Function
End If
Next
Debug.Assert False ' Error
End Function
Sub SimpleSort(ByRef Tgt() As String)
' On return, the entries in Tgt are in ascending order.
' This sort is adequate to demonstrate the creation of a parent-child table
' but much better sorts are available if you google for "vba sort array".
Dim InxTgtCrnt As Long
Dim TempStg As String
InxTgtCrnt = LBound(Tgt) + 1
Do While InxTgtCrnt <= UBound(Tgt)
If Tgt(InxTgtCrnt - 1) > Tgt(InxTgtCrnt) Then
' The current entry belongs before the previous entry
TempStg = Tgt(InxTgtCrnt - 1)
Tgt(InxTgtCrnt - 1) = Tgt(InxTgtCrnt)
Tgt(InxTgtCrnt) = TempStg
' Check the new previous enty against its previous entry if there is one.
InxTgtCrnt = InxTgtCrnt - 1
If InxTgtCrnt = LBound(Tgt) Then
' Prevous entry is start of array
InxTgtCrnt = LBound(Tgt) + 1
End If
Else
' These entries in correct sequence
InxTgtCrnt = InxTgtCrnt + 1
End If
Loop
End Sub
这篇关于如何在excel中构建父子数据表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
