如何用VBA宏实现Excel数据透视表多列的动态重排

你现有的普通表格列重排逻辑可以直接复用，针对数据透视表的结构化特性调整后，可完全满足你的三个约束条件，无需修改源数据，适配动态变化的列名。

实现逻辑 #

• 自动识别当前透视表的所有列字段，提取x day/x days格式列名中的数字部分，转换为数值作为排序权重
• 按权重升序生成列的目标顺序数组
• 按目标顺序调整透视表列的位置，无需操作单元格剪切插入，直接调用透视表原生属性完成重排

可直接使用的VBA代码 #

Function Reorder_PivotColumns()
    Dim pt As PivotTable
    Dim colFields As PivotFields
    Dim tempArr As Variant, sortArr As Variant
    Dim i As Long, j As Long, tempVal As Long, tempStr As String
    Dim pivotCol As PivotField
    Dim targetPosition As Integer
    
    ' 读取当前活动工作表的第一个透视表，可修改为指定名称的透视表：Set pt = ActiveSheet.PivotTables("你的透视表名称")
    Set pt = ActiveSheet.PivotTables(1)
    Set colFields = pt.DataFields ' 如果是列区域字段则改为 pt.ColumnFields
    
    ' 第一步：提取所有列名和对应排序权重存入数组
    ReDim tempArr(1 To colFields.Count, 1 To 2)
    For i = 1 To colFields.Count
        tempArr(i, 2) = colFields(i).Name
        ' 提取列名中的数字部分，适配x day/x days格式
        tempVal = CLng(Split(tempArr(i, 2), " ")(0))
        tempArr(i, 1) = tempVal
    Next i
    
    ' 第二步：按权重升序排序数组
    For i = LBound(tempArr, 1) To UBound(tempArr, 1) - 1
        For j = i + 1 To UBound(tempArr, 1)
            If tempArr(i, 1) > tempArr(j, 1) Then
                ' 交换权重
                tempVal = tempArr(j, 1)
                tempArr(j, 1) = tempArr(i, 1)
                tempArr(i, 1) = tempVal
                ' 交换列名
                tempStr = tempArr(j, 2)
                tempArr(j, 2) = tempArr(i, 2)
                tempArr(i, 2) = tempStr
            End If
        Next j
    Next i
    
    ' 第三步：按排序后的顺序调整透视表列位置
    Application.ScreenUpdating = False
    targetPosition = 1 ' Row Label固定在第一列，从第二列开始排，若有调整可修改
    For i = LBound(tempArr, 1) To UBound(tempArr, 1)
        Set pivotCol = colFields(tempArr(i, 2))
        If pivotCol.Position <> targetPosition Then
            pivotCol.Position = targetPosition
        End If
        targetPosition = targetPosition + 1
    Next i
    Application.ScreenUpdating = True
    
    pt.RefreshTable
End Function

注意事项 #

• 若你的时间字段放在透视表的「列」区域而非「值」区域，将代码中Set colFields = pt.DataFields修改为Set colFields = pt.ColumnFields即可
• 代码默认保留Row Label在第一列不动，仅排序后续时间列，符合你给出的示例要求
• 你提到的Raymond Wu和Tim Williams给出的两个方案运行耗时仅差0.04秒，对于常规数据量的透视表可忽略差异，按使用习惯选择即可

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