如何用Excel公式或VBA扁平化父子层级结构？

一、公式实现（适用于Excel 365/2021及以上） #

假设原始数据包含三列：ID（A列）、名称（B列）、父ID（C列），表头在第1行。在D2单元格输入以下公式，下拉填充即可生成每个节点的完整扁平化路径：

=LET(
    currentID, A2,
    getFullPath, LAMBDA(id,
        IF(id=0, "",
            getFullPath(XLOOKUP(id,A:A,C:C,,0)) & XLOOKUP(id,A:A,B:B,,0) & ">"
        )
    ),
    LEFT(getFullPath(currentID)&B2, LEN(getFullPath(currentID)&B2)-1)
)

如果是Excel 2019及以下版本，可通过多辅助列逐级拼接（适合层级较少的场景）：

• D2（一级父级）：=IF(C2=0,B2,VLOOKUP(C2,A:C,2,FALSE))
• E2（完整路径）：=IF(D2="",B2,D2&">"&B2)
• 若有更深层级，继续添加辅助列递归查找上级。

二、VBA实现（支持任意层级，批量处理） #

场景1：基于父ID的层级结构 #

假设数据在A（ID）、B（名称）、C（父ID）列，运行以下宏可在D列生成扁平化路径：

Sub FlattenByParentID()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, j As Long
    Dim parentID As Variant, currentName As String
    Dim fullPath As String
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    ws.Range("D1").Value = "扁平化路径"
    
    For i = 2 To lastRow
        currentName = ws.Cells(i, "B").Value
        parentID = ws.Cells(i, "C").Value
        fullPath = currentName
        
        Do While parentID <> 0
            For j = 2 To lastRow
                If ws.Cells(j, "A").Value = parentID Then
                    fullPath = ws.Cells(j, "B").Value & ">" & fullPath
                    parentID = ws.Cells(j, "C").Value
                    Exit For
                End If
            Next j
        Loop
        
        ws.Cells(i, "D").Value = fullPath
    Next i
End Sub

场景2：基于单元格缩进的层级结构 #

如果原始数据通过单元格缩进区分父子（无父ID列），使用以下宏将路径输出到B列：

Sub FlattenByIndent()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, level As Integer
    Dim hierarchy() As String
    Dim outputRow As Long
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    outputRow = 2
    ws.Range("B1").Value = "扁平化路径"
    
    ReDim hierarchy(1 To 15) ' 预留15层级空间，可按需调整
    
    For i = 2 To lastRow
        level = ws.Cells(i, "A").IndentLevel + 1
        hierarchy(level) = ws.Cells(i, "A").Value
        
        ' 清空当前层级以下的无效节点
        For j = level + 1 To UBound(hierarchy)
            hierarchy(j) = ""
        Next j
        
        ' 拼接并写入路径
        ws.Cells(outputRow, "B").Value = Join(Filter(hierarchy, ""), ">")
        outputRow = outputRow + 1
    Next i
End Sub

使用步骤：

1. 按Alt+F11打开VBA编辑器
2. 插入模块（右键工作表→插入→模块）
3. 粘贴对应场景的代码，按F5运行宏

内容的提问来源于stack exchange，提问作者AN Đỗ