求Excel for Mac中垂直表转水平表并保留分组的VBA或替代方案

数据特征 #

• 各组行数不固定
• 所有组均包含A至AH共34列
• 分组数量较多（当前432组，总计约7500行）
• 可通过C列（Library）筛选子分组进行转换

现有VBA脚本（仅选中分组范围） #

Sub SelectCurrentLibrary()
Dim searchValue As String
Dim ws As Worksheet
Dim lastRow As Long
Dim cell As Range
Dim rngToSelect As Range

Set ws = ActiveSheet
searchValue = Cells(ActiveCell.Row, 3).Value

' 查找C列最后一行
lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

' 遍历C列匹配值，选中对应行的A-AH区域
For Each cell In ws.Range("C3:C" & lastRow)
    If cell.Value = searchValue Then
        If rngToSelect Is Nothing Then
            Set rngToSelect = ws.Range("A" & cell.Row & ":AH" & cell.Row)
        Else
            Set rngToSelect = Union(rngToSelect, ws.Range("A" & cell.Row & ":AH" & cell.Row))
        End If
    End If
Next cell

' 选中最终范围
If Not rngToSelect Is Nothing Then
    rngToSelect.Select
Else
    MsgBox "未找到匹配行。"
End If
End Sub

原始数据格式示例 #

转换需求 #

• 将垂直布局转换为水平布局，结果输出到新工作表
• 每个水平分组之间添加空列分隔
• 无需转换表头

目标数据格式示例 #

解决方案 #

方案1：VBA脚本（支持Library筛选，自动转水平） #

Sub ConvertVerticalToHorizontal()
    Dim srcWs As Worksheet, destWs As Worksheet
    Dim lastRow As Long, groupCount As Integer
    Dim groupDict As Object, libraryFilter As String
    Dim groupKey As Variant, currentCol As Integer, maxRows As Integer
    Dim groupRows As Range, i As Integer, j As Integer
    
    ' 设置源工作表和目标工作表
    Set srcWs = ActiveSheet
    Set destWs = ThisWorkbook.Sheets.Add(After:=srcWs)
    destWs.Name = "转换结果"
    
    ' 获取Library筛选值（活动单元格所在行的C列值）
    libraryFilter = srcWs.Cells(ActiveCell.Row, 3).Value
    
    ' 使用字典存储每个GROUP的所有行数据
    Set groupDict = CreateObject("Scripting.Dictionary")
    lastRow = srcWs.Cells(srcWs.Rows.Count, "C").End(xlUp).Row
    
    ' 遍历数据，按GROUP分组（仅匹配指定Library）
    For i = 3 To lastRow
        If srcWs.Cells(i, 3).Value = libraryFilter Then
            groupKey = srcWs.Cells(i, 1).Value
            If Not groupDict.Exists(groupKey) Then
                Set groupDict(groupKey) = srcWs.Rows(i).Range("A1:AH1")
            Else
                Set groupDict(groupKey) = Union(groupDict(groupKey), srcWs.Rows(i).Range("A1:AH1"))
            End If
        End If
    Next i
    
    ' 计算最大行数（所有组中行数最多的那个）
    maxRows = 0
    For Each groupKey In groupDict.Keys
        If groupDict(groupKey).Rows.Count > maxRows Then
            maxRows = groupDict(groupKey).Rows.Count
        End If
    Next groupKey
    
    ' 将每个组的数据水平写入目标工作表，添加空列分隔
    currentCol = 1
    For Each groupKey In groupDict.Keys
        Set groupRows = groupDict(groupKey)
        ' 写入当前组数据
        groupRows.Copy destWs.Cells(1, currentCol)
        ' 计算下一个组的起始列（34列数据 + 1个空列）
        currentCol = currentCol + 35
    Next groupKey
    
    ' 填充空白行（确保所有组对齐到最大行数）
    For i = 1 To maxRows
        For j = 1 To currentCol Step 35
            If destWs.Cells(i, j).Value = "" Then
                destWs.Cells(i, j).Resize(1, 34).ClearContents
            End If
        Next j
    Next i
    
    MsgBox "转换完成，结果已保存到工作表：" & destWs.Name
End Sub

使用说明：

1. 选中需要筛选的Library所在行的任意单元格
2. 运行此脚本，自动生成包含水平分组的新工作表
3. 每组占34列，组间用1个空列分隔

方案2：非VBA方案（Power Query，适合Mac Excel） #

1. 导入数据到Power Query

   • 选中原始数据区域（包含A-AH列）
   • 点击「数据」选项卡 → 「从表格/范围」（若提示表包含标题，勾选确认）

2. 按GROUP和Library分组

   • 在Power Query编辑器中，点击「转换」选项卡 → 「分组依据」
   • 分组列选择：A列（GROUP）和C列（Library）
   • 新列名设为组数据，操作选择所有行，点击确定

3. 处理每个组的转置与空列

   • 添加自定义列：点击「添加列」→ 「自定义列」，输入公式：

     Table.AddColumn(Table.Transpose([组数据]), "空列", each null)
     

   • 展开自定义列：点击自定义列右侧的展开按钮，选择「展开到新行」

4. 合并所有组数据

   • 点击「转换」→ 「转置」，将数据转回水平布局
   • 调整列顺序，确保空列分隔各组

5. 加载到新工作表

   • 点击「主页」→ 「关闭并上载至」→ 选择「仅创建连接」→ 再右键连接选择「加载到」→ 选择新工作表

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