excel - 我怎样才能把这个数据集转换成这个特定的顺序？

Question

我正在使用Excel 2016，我有一个包含 492 行且没有标题的数据集。数据从 Cell 开始A1。

数据集的摘录如下所示：

我想转置这个数据集，使它变成这种格式：

我是新手，VBA很难找到正确的解决方案。我已经尝试将转置记录为Macro（逐步）并查看了VBA代码，但我仍然无法将其组合在一起。

标签： excelvbatransposeexcel-2016

Answer 1

试试这个代码，但在你调整顶部的两个常量以匹配工作表上的事实之前。执行代码时，包含数据的工作表必须处于活动状态。

Sub TransposeData()

    Const FirstDataRow As Long = 2              ' presuming row 1 has headers
    Const YearColumn As String = "A"            ' change as applicable

    Dim Rng As Range
    Dim Arr As Variant, Pos As Variant
    Dim Rl As Long, Cl As Long
    Dim R As Long, C As Long
    Dim i As Long

    With ActiveSheet
        Cl = .UsedRange.Columns.Count - .UsedRange.Column + 1
        Rl = .Cells(.Rows.Count, Columns(YearColumn).Column).End(xlUp).Row
        Set Rng = Range(.Cells(FirstDataRow, YearColumn), .Cells(Rl, Cl))
    End With
    Arr = Rng.Value
    ReDim Pos(1 To (UBound(Arr) * UBound(Arr, 2)), 1 To 2)

    For R = 1 To UBound(Arr)
        For C = 2 To UBound(Arr, 2)
            i = i + 1
            Pos(i, 1) = Arr(R, 1)
            Pos(i, 2) = Arr(R, C)
        Next C
    Next R

    R = Rl + 5                                  ' write 5 rows below existing data
    Set Rng = ActiveSheet.Cells(R, YearColumn).Resize(i, 2)
    Rng.Value = Pos
End Sub