excel - 将多列表转换为具有重复模式的单列

Question

假设您在 Excel 中有一个列范围（表格），如下所示：

并需要将其转换为如下格式：

我已经搜索了像这里这样的 VBA 脚本，市场上有几种工具，但无法让它在所需的输出中工作。

如何使用常规 Excel 公式或 VBA 脚本做到这一点？

标签： excelvba

Answer 1

尝试：

Option Explicit

Sub test()

    Dim LC As Long, LR As Long, Number As Long, i As Long, j As Long, NR As Long
    Dim str As String

    'Everything included in "With Statement" refer to Sheet1
    With ThisWorkbook.Worksheets("Sheet1")

        'Find Last Column
        LC = .Cells(1, .Columns.Count).End(xlToLeft).Column

        'Find Las Row
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row

        'Loop from column two to last column
        For i = 2 To LC

            'Number get the value of row 1 and column i
            Number = .Cells(1, i).Value

            'Loop from row two to last row of each i
            For j = 2 To LR

                'str get the value of row j in column 1
                str = .Cells(j, 1).Value

                    NR = .Cells(.Rows.Count, "A").End(xlUp).Row

                    .Cells(NR + 1, 1).Value = Number
                    .Cells(NR + 1, 2).Value = str
                    .Cells(NR + 1, 3).Value = .Cells(j, i).Value


            Next j

        Next i

    End With

End Sub

输出：