如何使用VBA为含空行与重复表头的工作表插入数据透视表

直接生成透视表会因为重复表头、空行导致数据源识别不完整，核心思路是先清理规整原始数据，再基于干净的连续数据创建透视表，具体实现如下：

操作步骤 #

1. 遍历源工作表，删除所有重复表头行和空行，将数据整理为单表头的连续区域
2. 基于清理后的数据源，创建指定字段的透视表

完整VBA代码 #

Sub CreatePivotFromDuplicateHeaders()
    Dim wsSource As Worksheet
    Dim wsPivot As Worksheet
    Dim lastRow As Long, i As Long
    Dim headerRange As Range
    Dim pivotCache As PivotCache
    Dim pivotTable As PivotTable
    Dim dataRange As Range
    
    ' 指定源工作表（替换成你的实际工作表名称）
    Set wsSource = ThisWorkbook.Worksheets("原始数据")
    ' 新建工作表存放透视表
    Set wsPivot = ThisWorkbook.Worksheets.Add
    wsPivot.Name = "数据透视表"
    
    ' 获取源表最后一行行号
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    ' 定义表头区域（假设表头在第一行，覆盖A到C列，按需调整列范围）
    Set headerRange = wsSource.Range("A1:C1")
    
    ' 从下往上遍历删除重复表头和空行（避免删除行后索引错乱）
    For i = lastRow To 2 Step -1
        ' 判断当前行是否为重复表头（与第一行表头完全匹配）
        If Application.WorksheetFunction.CountIf(wsSource.Range(wsSource.Cells(i, "A"), wsSource.Cells(i, "C")), headerRange.Cells(1, 1).Value) = headerRange.Columns.Count Then
            wsSource.Rows(i).Delete
        ' 判断当前行是否为空行
        ElseIf Application.WorksheetFunction.CountA(wsSource.Rows(i)) = 0 Then
            wsSource.Rows(i).Delete
        End If
    Next i
    
    ' 获取清理后的完整数据源区域
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    Set dataRange = wsSource.Range("A1:C" & lastRow)
    
    ' 创建透视缓存
    Set pivotCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=dataRange)
    
    ' 在新工作表创建透视表
    Set pivotTable = pivotCache.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A1"), _
        TableName:="业务数据透视表")
    
    ' 设置透视表字段（示例：行字段为"地区"，列字段为"月份"，值字段为"销售额"求和，按需修改字段名）
    With pivotTable
        .PivotFields("地区").Orientation = xlRowField
        .PivotFields("月份").Orientation = xlColumnField
        .AddDataField .PivotFields("销售额"), "总销售额", xlSum
    End With
End Sub

关键细节说明 #

• 表头匹配逻辑：代码默认通过判断当前行与第一行表头的单元格内容完全匹配来识别重复表头，可根据实际表头列数调整Range("A1:C1")及对应列的判断范围
• 逆序删除：从最后一行往上遍历删除行，避免因删除操作导致后续行号偏移，出现漏删情况
• 透视表字段配置：最后一段的字段设置需替换为你实际的数据字段名称，比如把"地区""月份""销售额"改成你的业务字段

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