多字段大容量Excel纵向采集数据批量转置快捷方法问询

以下三种方案都可以实现全自动化处理，完全不需要手动重复执行转置操作：

方案1：Power Query（零代码，Excel 2016及以上版本自带，最推荐普通用户使用） #

操作步骤：

• 选中原始数据任意单元格，点击「数据」选项卡下的「从表格/区域」，将数据导入Power Query编辑器
• 选中日期列，右键选择「分组依据」，分组字段选日期，新列名设置为「当日数据」，操作项选择「所有行」
• 若需要给转置后的时段列加序号，可添加自定义列，输入公式Table.AddIndexColumn([当日数据], "时段", 1, 1)
• 点击「当日数据」列右上角的扩展按钮，只勾选需要转置的数值字段确认，此时每个日期对应的48条数据就会自动横向排列
• 最后点击「关闭并上载」，即可将处理好的结果导出到Excel工作表，3年数据完整处理仅需几分钟

方案2：VBA脚本（适合需要高频处理同类型数据的场景） #

按以下步骤操作即可：

• 打开文件后按Alt+F11调出VBA编辑器，插入新模块
• 粘贴下方代码，修改对应工作表名、日期列、数值列的参数后直接运行

Sub 批量转置时序数据()
    Dim srcSheet As Worksheet, resSheet As Worksheet
    Dim lastRow As Long, i As Long, currDate As Date, col As Long, rowNum As Long
    ' 替换为实际的原始数据工作表名
    Set srcSheet = Sheets("原始数据")
    Set resSheet = Sheets.Add(after:=srcSheet)
    resSheet.Name = "转置结果"
    ' 假设日期存储在A列，可替换为实际列号
    lastRow = srcSheet.Cells(Rows.Count, "A").End(xlUp).Row
    currDate = srcSheet.Cells(2, "A").Value
    ' 写入表头
    resSheet.Cells(1, 1) = "日期"
    For i = 1 To 48
        resSheet.Cells(1, i + 1) = "时段" & i
    Next
    rowNum = 2
    col = 2
    For i = 2 To lastRow
        If srcSheet.Cells(i, "A").Value = currDate Then
            ' 假设数值存储在B列，可替换为实际列号
            resSheet.Cells(rowNum, col) = srcSheet.Cells(i, "B").Value
            col = col + 1
        Else
            rowNum = rowNum + 1
            currDate = srcSheet.Cells(i, "A").Value
            resSheet.Cells(rowNum, 1) = currDate
            col = 2
            resSheet.Cells(rowNum, col) = srcSheet.Cells(i, "B").Value
            col = col + 1
        End If
    Next
End Sub

方案3：Python pandas（适合超100万行的超大容量数据场景） #

几行代码即可完成处理，运行效率远高于Excel自带工具：

import pandas as pd

# 替换为实际的文件路径
df = pd.read_excel("原始数据文件.xlsx")
# 替换为实际的日期列名、数值列名
res = df.groupby("日期")["采集值"].apply(list).apply(pd.Series).reset_index()
# 重命名列名
res.columns = ["日期"] + [f"时段{i+1}" for i in range(48)]
# 导出结果
res.to_excel("转置结果.xlsx", index=False)

提示：如果存在部分日期数据不足48条的情况，可在处理前先添加数据校验步骤，补全缺失行的空值，避免转置后出现列错位问题。

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