Excel ETL入门困惑：多表头脏数据清洗方案求助

我正处于Excel ETL入门阶段，现有一套老旧ERP导出的多表头杂乱数据集，需要清洗成能用于数据透视表、VLOOKUP及Power BI分析的规范格式。目前纠结选VBA还是Power Query，自己写的VBA代码有bug，求可行方案。

清洗前数据样式：

清洗后目标样式：

方案一：Power Query（推荐给入门者） #

Power Query是Excel内置的可视化ETL工具，无需复杂编码，步骤可复用，完美适配这类结构化清洗需求，操作步骤如下：

1. 导入数据：选中数据区域 → 「数据」选项卡 → 「自表格/区域」（如果是Excel表格），或通过其他来源导入
2. 处理多表头与键值对：
   • 先筛选掉空行空列，保留有效数据
   • 对左侧*:*格式的键值对行，用「拆分列」按冒号拆分，再将这部分转成列表后逆透视，合并为标准键值对
   • 对底部以Due Date开头的表格区域，将其识别为表头，把对应数据行合并到之前的键值对集合中
3. 合并整理：将所有键值对合并为一行规范记录，最后加载回Excel或直接导入Power BI

优势 #

• 可视化操作，易调试，步骤可保存复用
• 无需编写复杂代码，适合ETL入门阶段
• 天然支持与Power BI无缝衔接

方案二：修正后的VBA代码 #

原VBA代码存在遍历效率低、Due Date行判断逻辑易漏数据、字典与集合赋值冲突等问题，以下是修正后的版本：

Sub CleanERPData()
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim keyDict As Object
    Dim i As Long, j As Long, destRow As Long
    
    ' 设置工作表
    Set wsSource = ActiveSheet
    Set wsDest = ThisWorkbook.Sheets.Add(After:=wsSource)
    wsDest.Name = "清洗结果"
    Set keyDict = CreateObject("Scripting.Dictionary")
    
    lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
    
    ' 处理键值对部分（左侧*:*格式）
    For i = 1 To lastRow
        If wsSource.Cells(i, 1).Value Like "*:*" And wsSource.Cells(i, 2).Value <> "" Then
            ' 去除键的多余空格和冒号
            Dim key As String
            key = Trim(Replace(wsSource.Cells(i, 1).Value, ":", ""))
            If Not keyDict.Exists(key) Then
                keyDict.Add key, wsSource.Cells(i, 2).Value
            End If
        End If
        
        ' 处理底部表格区域
        If wsSource.Cells(i, 1).Value = "Due Date" Then
            lastCol = wsSource.Cells(i, wsSource.Columns.Count).End(xlToLeft).Column
            For j = 1 To lastCol
                key = Trim(wsSource.Cells(i, j).Value)
                If Not keyDict.Exists(key) Then
                    keyDict.Add key, wsSource.Cells(i + 1, j).Value
                End If
            Next j
            Exit For ' 找到表格行后退出循环，避免重复遍历
        End If
    Next i
    
    ' 输出表头和数据
    destRow = 1
    ' 输出表头
    j = 1
    For Each key In keyDict.Keys
        wsDest.Cells(destRow, j).Value = key
        j = j + 1
    Next key
    ' 输出数据行
    destRow = 2
    j = 1
    For Each key In keyDict.Keys
        wsDest.Cells(destRow, j).Value = keyDict(key)
        j = j + 1
    Next key
    
    MsgBox "数据清洗完成，结果已保存到「清洗结果」工作表", vbInformation
End Sub

修正点说明 #

• 新增独立结果工作表，避免污染原数据
• 优化键值对提取逻辑，去除冗余符号和空格，确保键唯一
• 找到Due Date行后直接退出循环，提升遍历效率
• 用字典统一存储键值，避免重复表头冲突

方案选择建议 #

• 若为入门新手，优先选Power Query：操作直观，无需编码，步骤可追溯，后续对接Power BI更顺畅
• 若需自动化批量处理（如每周固定导出数据清洗）且熟悉VBA，可使用修正后的VBA代码，配合任务计划实现全自动化

内容的提问来源于stack exchange，提问作者Pavlo Kobzar