如何将单列Excel数据转换为多列？已尝试WEEKNUM/数据透视表遇阻

一、动态数组公式法（适用于Excel 365/2021） #

假设原始数据为A:C列（A=ID，B=日期，C=数值），目标区域ID列（如H列）、周数列标题（如I1=48、J1=49…）已准备：

• 提取唯一ID：在H2输入 =UNIQUE(A:A)，自动生成所有不重复ID
• 匹配周数对应数值：在I2输入 =XLOOKUP($H2&"|"&I$1, $A:$A&"|"&WEEKNUM($B:$B,21), $C:$C, "")，向右向下填充
  • WEEKNUM函数参数21为ISO周规则，可根据实际周数定义调整
  • 用ID|周数作为联合匹配键，避免不同ID同周数的冲突

二、数据透视表修正方案 #

针对排序和年份合并问题，按以下步骤调整：

解决ID排序混乱 #

1. 选中透视表ID列，右键→排序→自定义排序
2. 勾选按数字顺序排序（或对应“字母数字排序”选项）；若无效，先给原始ID列加辅助列=VALUE(RIGHT(A2,LEN(A2)-2))提取数字，透视表用辅助列排序，显示原ID

解决年份合并问题 #

1. 给日期列加辅助列=TEXT(B2,"YYYY")&"-"&WEEKNUM(B2,21)，生成带年份的周数（如2022-48）
2. 透视表用该辅助列作为列标签，之后按需将列标题修改为纯数字；或直接在透视表字段设置中，取消原周数分组，重新按“年份+周数”分组

三、VBA脚本实现 #

以下脚本将Sheet1的原始数据转换为目标格式，输出到Sheet2：

Sub ConvertToHorizontal()
    Dim wsSrc As Worksheet, wsDest As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim idDict As Object, key As Variant
    Dim i As Long, j As Long
    Dim weekNum As Integer
    
    Set wsSrc = ThisWorkbook.Sheets("Sheet1")
    Set wsDest = ThisWorkbook.Sheets("Sheet2")
    Set idDict = CreateObject("Scripting.Dictionary")
    
    ' 读取原始数据，按ID分组存储周数与数值
    lastRow = wsSrc.Cells(wsSrc.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow
        key = wsSrc.Cells(i, "A").Value
        weekNum = WorksheetFunction.WeekNum(wsSrc.Cells(i, "B").Value, 21)
        ' 若需区分年份，取消下一行注释：
        ' key = key & "_" & Year(wsSrc.Cells(i, "B").Value)
        If Not idDict.Exists(key) Then Set idDict(key) = CreateObject("Scripting.Dictionary")
        idDict(key)(weekNum) = wsSrc.Cells(i, "C").Value
    Next i
    
    ' 写入结果：ID及对应周数数值
    lastCol = wsDest.Cells(1, wsDest.Columns.Count).End(xlToLeft).Column
    j = 2
    For Each key In idDict.Keys
        wsDest.Cells(j, "A").Value = Split(key, "_")(0) ' 区分年份时取ID部分
        For i = 2 To lastCol
            weekNum = wsDest.Cells(1, i).Value
            wsDest.Cells(j, i).Value = IIf(idDict(key).Exists(weekNum), idDict(key)(weekNum), "")
        Next i
        j = j + 1
    Next key
    
    ' 按ID数字排序，修正WG1、WG10的顺序
    wsDest.Range("A2:A" & j - 1).Sort _
        Key1:=wsDest.Range("A2:A" & j - 1), Order1:=xlAscending, _
        Header:=xlNo, DataOption1:=xlSortTextAsNumbers
End Sub

使用提示：

• 按需修改Sheet名称、列号
• 若周数需区分年份，取消脚本中对应注释行

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