如何用VBA将多列数据透视合并为单列并输出到独立工作表?
Excel VBA 宽表转规整格式实现方案
代码逻辑匹配提问中初始表、最终表的结构转换需求,转换结果自动写入独立工作表
可直接运行的实现代码
按Alt+F11打开VBA编辑器,插入标准模块后粘贴以下代码,调整对应参数即可运行:
Sub 表格格式转换() Dim wsSource As Worksheet, wsOutput As Worksheet Dim lastRow As Long, lastCol As Long Dim i As Long, j As Long Dim outputRow As Long Dim fixedColCount As Integer ' 按实际初始表结构调整的固定维度列数 ' 参数配置区 fixedColCount = 3 ' 示例:初始表前3列为固定维度(如品类、区域、负责团队) Set wsSource = ActiveSheet ' 指定源数据为当前激活的工作表 ' 创建独立输出工作表,避免直接修改源数据 On Error Resume Next Set wsOutput = ThisWorkbook.Worksheets("转换结果表") If wsOutput Is Nothing Then Set wsOutput = ThisWorkbook.Worksheets.Add(after:=wsSource) wsOutput.Name = "转换结果表" Else wsOutput.Cells.Clear ' 清空表内旧数据,防止重复写入 End If On Error GoTo 0 ' 写入结果表表头(可按最终表实际列名自定义) With wsOutput .Range("A1").Value = "维度1" .Range("B1").Value = "维度2" .Range("C1").Value = "维度3" .Range("D1").Value = "指标名称" .Range("E1").Value = "指标值" End With ' 获取源表的有效数据范围 lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column outputRow = 2 ' 结果表从第2行开始写入明细数据 ' 逐行遍历源表数据 For i = 2 To lastRow ' 自动跳过空行、含"合计"标识的汇总行 If Len(Trim(wsSource.Cells(i, 1).Value)) = 0 Then GoTo NextValidRow If InStr(wsSource.Cells(i, 1).Value, "合计") > 0 Then GoTo NextValidRow ' 逐列拆分横向排布的指标值 For j = fixedColCount + 1 To lastCol ' 跳过空值单元格,无冗余写入 If Len(Trim(wsSource.Cells(i, j).Value)) <> 0 Then ' 复制固定维度值 wsOutput.Cells(outputRow, 1).Value = wsSource.Cells(i, 1).Value wsOutput.Cells(outputRow, 2).Value = wsSource.Cells(i, 2).Value wsOutput.Cells(outputRow, 3).Value = wsSource.Cells(i, 3).Value ' 写入对应指标名、指标值 wsOutput.Cells(outputRow, 4).Value = wsSource.Cells(1, j).Value wsOutput.Cells(outputRow, 5).Value = wsSource.Cells(i, j).Value outputRow = outputRow + 1 End If Next j NextValidRow: Next i ' 自动适配结果表列宽 wsOutput.Columns.AutoFit MsgBox "转换完成,共输出" & outputRow - 2 & "条有效明细" End Sub
调整说明
- 代码中
fixedColCount参数需匹配初始表的固定维度列数量,例如初始表前4列为固定维度,就将参数值修改为4 - 结果表的表头列名可根据最终表的实际要求自定义,无需和示例保持一致
- 若初始表的汇总行标识为"总计"而非"合计",可将代码中匹配关键词
"合计"替换为"总计" - 代码默认不写入空值单元格,从根源减少结果表的冗余数据
- 输出工作表默认命名为
转换结果表,可直接修改代码中的工作表名称参数自定义
内容的提问来源于stack exchange,提问作者Melvin Ong
相关产品推荐
相关产品推荐

