如何对Excel中十万行垂直数据进行转换与向下填充处理

一、VBA自动化解决方案（适配十万行大数据） #

直接用VBA脚本完成全流程自动化，效率最高：

Sub CleanAndFillData()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, blockStartRow As Long
    Dim idVal As String, lastName As String, firstName As String, levelVal As String
    Dim deleteRows As Range
    
    ' 指定目标工作表，可改为具体表名如Sheets("原始数据")
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set deleteRows = Nothing
    
    i = 1
    Do While i <= lastRow
        ' 识别每个ID块的起始行（前四列均非空）
        If ws.Cells(i, "A").Value <> "" And ws.Cells(i, "B").Value <> "" And _
           ws.Cells(i, "C").Value <> "" And ws.Cells(i, "D").Value <> "" Then
            blockStartRow = i
            ' 记录当前块的四个核心值
            idVal = ws.Cells(i, "A").Value
            lastName = ws.Cells(i, "B").Value
            firstName = ws.Cells(i, "C").Value
            levelVal = ws.Cells(i, "D").Value
            
            ' 向下遍历至总计行，填充空白行
            i = i + 1
            Do While i <= lastRow And InStr(ws.Cells(i, "A").Value, "Totals for") = 0
                If ws.Cells(i, "A").Value = "" Then
                    ws.Cells(i, "A").Value = idVal
                    ws.Cells(i, "B").Value = lastName
                    ws.Cells(i, "C").Value = firstName
                    ws.Cells(i, "D").Value = levelVal
                End If
                i = i + 1
            Loop
            ' 标记总计行待删除
            If Not deleteRows Is Nothing Then
                Set deleteRows = Union(deleteRows, ws.Rows(i))
            Else
                Set deleteRows = ws.Rows(i)
            End If
        End If
        i = i + 1
    Loop
    
    ' 批量删除所有总计行
    If Not deleteRows Is Nothing Then deleteRows.Delete
    
    ' 删除指定列（根据实际列标题调整）
    Dim col As Range
    For Each col In ws.Rows(1).Cells
        If col.Value = "会话结束" Or col.Value = "时间" Then
            col.EntireColumn.Delete
            ' 删除后列索引偏移，回退循环避免漏删
            Set col = col.Offset(0, -1)
        End If
    Next col
    
    MsgBox "数据处理完成！"
End Sub

使用步骤 #

1. 打开原始数据Excel，按Alt+F11打开VBA编辑器
2. 右键左侧VBAProject → 插入 → 模块
3. 粘贴上述代码，根据你的实际情况调整：
   • 工作表名称（把ActiveSheet改成Sheets("你的表名")）
   • 总计行的识别条件（如果"Totals for"不在A列，修改ws.Cells(i, "A")的列号）
   • 待删除列的标题（确保和你的列名完全匹配）
4. 按F5运行宏，或回到Excel界面通过「开发工具→宏」执行

二、核心逻辑说明 #

• 块起始识别：通过前四列均非空判断每个ID组的第一行
• 空白填充：把组起始行的四个值，批量填充到该组内的所有空白行，直到遇到总计行
• 批量删除：先标记所有总计行再统一删除，避免循环中删行导致的索引混乱
• 列删除：通过列标题匹配删除，避免硬编码列号出错

三、注意事项 #

• 运行前必须备份原始数据，防止操作失误丢失数据
• 十万行数据运行需几秒到十几秒，请勿中途中断
• 如果你的数据块有特殊格式差异，可微调代码中的判断条件

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