excel-formula - 如何根据 Id 列将行转置为列

Question

我想根据 A 列将行转换为列。有人可以帮助我使用公式或任何设置吗？

标签： excel-formulaexcel-2010

Answer 1

我认为用 excel 公式创建类似的东西是不可能的。我尝试创建一个可能有助于使用 VBA 的代码。

尝试：

Option Explicit

Sub test()

    Dim LR As Long, LC As Long, i As Long, No As Long

    With ThisWorkbook.Worksheets("Sheet1")

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

        .Cells(LR + 3, 1).Value = "Event_Id"
        .Cells(LR + 4, 1).Value = "1"
        .Cells(LR + 5, 1).Value = "2"
        .Cells(LR + 6, 1).Value = "3"

        For i = 2 To LR
            No = .Cells(i, 1).Value

            If No = "1" Then

                LC = .Cells(LR + 4, .Columns.Count).End(xlToLeft).Column

                If .Cells(LR + 3, LC + 1).Value = "" Then
                    .Cells(LR + 3, LC + 1).Value = "Unit_Id"
                End If
                    .Cells(LR + 4, LC + 1).Value = .Cells(i, 2).Value

                If .Cells(LR + 3, LC + 2).Value = "" Then
                    .Cells(LR + 3, LC + 2).Value = "Qty"
                End If
                    .Cells(LR + 4, LC + 2).Value = .Cells(i, 3).Value

            ElseIf No = "2" Then
                LC = .Cells(LR + 5, .Columns.Count).End(xlToLeft).Column

                If .Cells(LR + 3, LC + 1).Value = "" Then
                    .Cells(LR + 3, LC + 1).Value = "Unit_Id"
                End If
                    .Cells(LR + 5, LC + 1).Value = .Cells(i, 2).Value

                If .Cells(LR + 3, LC + 2).Value = "" Then
                    .Cells(LR + 3, LC + 2).Value = "Qty"
                End If
                    .Cells(LR + 5, LC + 2).Value = .Cells(i, 3).Value

            ElseIf No = "3" Then
                LC = .Cells(LR + 6, .Columns.Count).End(xlToLeft).Column

                If .Cells(LR + 3, LC + 1).Value = "" Then
                    .Cells(LR + 3, LC + 1).Value = "Unit_Id"
                End If
                    .Cells(LR + 6, LC + 1).Value = .Cells(i, 2).Value

                If .Cells(LR + 3, LC + 2).Value = "" Then
                    .Cells(LR + 3, LC + 2).Value = "Qty"
                End If
                    .Cells(LR + 6, LC + 2).Value = .Cells(i, 3).Value

            End If

        Next i

    End With

End Sub