如何实现Excel中Point A到Point B的数据格式转换？含编程方案

我需要将Point A格式的数据转换为Point B格式，原本尝试用Excel的转置（transpose）功能但未达到预期效果。核心需求是便捷收集管理Point A的数据，再转换成Point B的展示格式，以下是可行的Excel内置方法和编程实现方案：

数据格式示例 #

Point A（原始收集格式） #

Point B（目标展示格式） #

Excel内置方法 #

方法1：Power Query（推荐，适配灵活结构） #

1. 选中Point A的数据区域，点击「数据」选项卡 → 「从表格/范围」（Excel 2016及以上版本支持），确认数据包含表头。
2. 在Power Query编辑器中，选中项目名称列（如第一列），右键选择「逆透视其他列」。
3. 生成的「属性」「值」列可按需重命名，匹配Point B的列名。
4. 点击「关闭并上载」，即可得到目标格式的数据。

方法2：公式组合（适合固定结构数据） #

假设Point A数据位于A1:C3（A列为项目名称，B、C列为属性列），在空白区域（如E1开始）输入以下公式并下拉填充：

• 项目名称列（E1）：=INDEX($A:$A,INT((ROW()-1)/2)+1)
• 属性列（F1）：=INDEX($B$1:$C$1,MOD(ROW()-1,2)+1)
• 值列（G1）：=INDEX($B:$C,INT((ROW()-1)/2)+1,MOD(ROW()-1,2)+1)

编程实现方案（VBA宏） #

编写VBA宏可批量处理任意规模的固定结构数据，代码如下：

Sub ConvertToPointB()
    Dim sourceRange As Range
    Dim targetRange As Range
    Dim sourceRow As Integer
    Dim targetRow As Integer
    Dim colCount As Integer
    Dim i As Integer
    
    ' 自定义源数据区域（示例为Sheet1中A1开始的连续数据）
    Set sourceRange = ThisWorkbook.Sheets("Sheet1").Range("A1").CurrentRegion
    ' 自定义目标数据起始位置（示例为Sheet1的E1单元格）
    Set targetRange = ThisWorkbook.Sheets("Sheet1").Range("E1")
    
    targetRow = 0
    ' 写入目标表头
    targetRange.Offset(targetRow, 0).Value = "项目名称"
    targetRange.Offset(targetRow, 1).Value = "属性"
    targetRange.Offset(targetRow, 2).Value = "值"
    targetRow = targetRow + 1
    
    ' 遍历源数据行（跳过表头行）
    For sourceRow = 2 To sourceRange.Rows.Count
        colCount = sourceRange.Columns.Count
        ' 遍历每个属性列（从第二列开始）
        For i = 2 To colCount
            targetRange.Offset(targetRow, 0).Value = sourceRange.Cells(sourceRow, 1).Value
            targetRange.Offset(targetRow, 1).Value = sourceRange.Cells(1, i).Value
            targetRange.Offset(targetRow, 2).Value = sourceRange.Cells(sourceRow, i).Value
            targetRow = targetRow + 1
        Next i
    Next sourceRow
    
    MsgBox "转换完成！"
End Sub

使用步骤：

1. 按Alt+F11打开VBA编辑器。
2. 插入新模块，粘贴上述代码。
3. 根据实际数据位置修改sourceRange和targetRange的定义。
4. 运行宏即可完成转换。

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