如何将该数据集转换为特定顺序? [英] How can I transpose this data set into this specific order?

问题描述

我正在使用Excel 2016，并且我有一个包含492行且没有标题的数据集.数据从单元格A1开始.

I am using Excel 2016 and I have a data set with 492 rows and no headers. Data starts at Cell A1.

数据集的摘录如下:

我想转置此数据集，使其变为以下格式:

I want to transpose this data set so that it becomes into this format:

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

I am new to VBA and I am having a hard time finding the right solution. I have tried recording the transpose as a Macro (step by step) and viewed the VBA codes but I still can't make it come together.

推荐答案

尝试此代码，但是在您调整顶部的两个常量以匹配工作表上的事实之前，请尝试该代码.执行代码后，带有数据的工作表必须处于活动状态.

Try this code, but before you do adjust the two constants at the top to match the facts on your worksheet. The worksheet with the data must be active when the code is executed.

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

这篇关于如何将该数据集转换为特定顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！