如何使用VBA重排Excel输入表格得到指定输出格式

前置确认 #

先确认你的Excel文件中两张表的名称，下述代码默认输入表名为输入数据表，输出表名为预期输出样例，如果你的表名不同，直接修改代码中对应引号内的表名即可。

操作步骤 #

• 打开目标Excel文件，按下Alt + F11调出VBA编辑器
• 在左侧项目资源管理器中右键点击你的工作簿，依次选择「插入」→「模块」
• 将下方的VBA代码粘贴到弹出的空白模块窗口中
• 按下F5直接运行，或者回到Excel界面按下Alt + F8，选择转换为指定输出格式宏执行即可

核心VBA代码 #

Sub 转换为指定输出格式()
    Dim inputSheet As Worksheet, outputSheet As Worksheet
    Dim lastInputRow As Long, lastInputCol As Long
    Dim dhgpCol As Long, dhgtCol As Long, i As Long, j As Long, outputRow As Long
    
    ' 绑定工作表，可修改为实际表名
    Set inputSheet = ThisWorkbook.Worksheets("输入数据表")
    Set outputSheet = ThisWorkbook.Worksheets("预期输出样例")
    
    ' 清空输出表原有数据，如需保留原有内容可删除该行
    outputSheet.Cells.Clear
    
    ' 获取输入表的数据范围
    lastInputRow = inputSheet.Cells(Rows.Count, 1).End(xlUp).Row
    lastInputCol = inputSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    
    ' 定位两个特殊字段的列位置，不存在则标记为0
    dhgpCol = 0: dhgtCol = 0
    For j = 1 To lastInputCol
        If inputSheet.Cells(1, j).Value = "DHGP_UPPER" Then dhgpCol = j
        If inputSheet.Cells(1, j).Value = "DHGT_UPPER" Then dhgtCol = j
    Next j
    
    ' 复制表头到输出表
    inputSheet.Rows(1).Copy outputSheet.Rows(1)
    outputRow = 2
    
    ' 逐行处理数据
    For i = 2 To lastInputRow
        ' 复制整行原始数据
        inputSheet.Rows(i).Copy outputSheet.Rows(outputRow)
        ' 处理DHGP_UPPER缺失值，字段不存在则补到最后一列
        If dhgpCol = 0 Or inputSheet.Cells(i, dhgpCol) = "" Then
            outputSheet.Cells(outputRow, IIf(dhgpCol = 0, lastInputCol + 1, dhgpCol)) = -9999
        End If
        ' 处理DHGT_UPPER缺失值，字段不存在则补到最后一列
        If dhgtCol = 0 Or inputSheet.Cells(i, dhgtCol) = "" Then
            outputSheet.Cells(outputRow, IIf(dhgtCol = 0, lastInputCol + 2, dhgtCol)) = -9999
        End If
        outputRow = outputRow + 1
    Next i
    
    ' 自动调整列宽
    outputSheet.Columns.AutoFit
    MsgBox "转换完成，共处理" & lastInputRow - 1 & "条数据"
End Sub

注意事项 #

• 运行宏前请先备份原文件，避免误操作导致数据丢失
• 如果输入表的表头不在第一行，可修改代码中Cells(1, j)的行号参数
• 如果输出表的列顺序和输入表不同，可自行在代码中增加列映射逻辑，按预期输出的列顺序逐字段写入
• 如需长期使用该功能，可将宏保存到个人宏工作簿，后续打开任意Excel文件都可以直接调用

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