☰

将不相等的 Excel 列转置为行

tag-icon • tag-icon microsoft-excel transpose
将不相等的 Excel 列转置为行

我收到了一个 Excel 表，其中每个类别包含不同数量的项目：

Category A   Category B   Category C
22           11           1
34           15           6
55           4            18
33
36

我想把它变成：

Category     Item
Category A   22
Category A   34
Category A   55
Category A   33
Category A   36
Category B   11
Category B   15
[...]

有什么快捷方法可以实现这一目标？

答案1

宏可以轻松实现这一点。下面的宏会将结果放入新的工作表中。

Sub TransposeStuff()
Dim lLastRow As Long, lColLoop As Long, lLastCol As Long
Dim shtOrg As Worksheet, shtDest As Worksheet

'turn off updates to speed up code execution
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With


Set shtOrg = ActiveSheet
Set shtDest = Sheets.Add

shtDest.[a1] = "Category"
shtDest.[B1] = "Item"

lLastCol = shtOrg.Cells(1, Columns.Count).End(xlToLeft).Column


For lColLoop = 1 To lLastCol
    lLastRow = shtOrg.Cells(Rows.Count, lColLoop).End(xlUp).Row

    shtOrg.Range(shtOrg.Cells(2, lColLoop), shtOrg.Cells(lLastRow, lColLoop)).Copy
        shtDest.Cells(Rows.Count, 2).End(xlUp).Offset (1)

    shtDest.Range(shtDest.Cells(Rows.Count, 1).End(xlUp).Offset(1), _
                    shtDest.Cells(Rows.Count, 2).End(xlUp).Offset(, -1)).Value = shtOrg.Cells(1, lColLoop)

Next lColLoop

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

相关内容