如何用R代码或Excel宏实现指定数据集的格式转换？

R代码实现 #

根据格式转换需求，使用tidyr和dplyr包即可完成转换，核心逻辑是先为每个分组生成列名序号，再将长格式数据转为目标宽格式：

# 加载依赖包
library(tidyr)
library(dplyr)

# 示例原始数据（替换为你的实际数据即可）
df <- data.frame(
  ID = c(1, 1, 2, 3, 3, 3),
  Value = c("A", "B", "C", "D", "E", "F")
)

# 为每个ID内的行生成带序号的列名
df_processed <- df %>%
  group_by(ID) %>%
  mutate(col_name = paste0("Value", row_number())) %>%
  ungroup()

# 转换为目标宽格式
result_df <- df_processed %>%
  pivot_wider(
    names_from = col_name,
    values_from = Value
  )

# 查看转换结果
print(result_df)

运行后，同一ID下的多个值会被整理到同一行的Value1、Value2等列中，无对应值的位置自动填充NA，完全匹配目标格式。

Excel宏实现 #

如果需要用Excel宏完成转换，可参考以下VBA代码（需根据你的数据列位置调整）：

Sub ConvertToTargetFormat()
    Dim srcWs As Worksheet, destWs As Worksheet
    Dim lastRow As Long, destRow As Integer, i As Integer, colIdx As Integer
    Dim currentID As Variant
    
    ' 指定原始数据工作表和结果工作表
    Set srcWs = ThisWorkbook.Sheets("Sheet1")
    Set destWs = ThisWorkbook.Sheets.Add(After:=srcWs)
    destWs.Name = "转换结果"
    
    ' 写入表头（可根据需求扩展列数）
    destWs.Cells(1, 1).Value = "ID"
    destWs.Cells(1, 2).Value = "Value1"
    destWs.Cells(1, 3).Value = "Value2"
    destWs.Cells(1, 4).Value = "Value3"
    
    lastRow = srcWs.Cells(srcWs.Rows.Count, "A").End(xlUp).Row
    destRow = 2
    currentID = srcWs.Cells(2, "A").Value
    colIdx = 2
    
    For i = 2 To lastRow
        If srcWs.Cells(i, "A").Value = currentID Then
            destWs.Cells(destRow, colIdx).Value = srcWs.Cells(i, "B").Value
            colIdx = colIdx + 1
        Else
            destRow = destRow + 1
            currentID = srcWs.Cells(i, "A").Value
            destWs.Cells(destRow, 1).Value = currentID
            destWs.Cells(destRow, 2).Value = srcWs.Cells(i, "B").Value
            colIdx = 3
        End If
    Next i
End Sub

使用时确保原始数据的ID在A列、值在B列，运行宏后会自动生成新工作表存放转换结果。

内容的提问来源于stack exchange，提问作者J-20