Excel:将包含多列的索引表转换为两列

Excel:将包含多列的索引表转换为两列

我有一张 Excel 表,其中有如下行:

Fruit     | Apple  | Banana | Grape | Peach
Vegetable | Cabbage| Lettuce| Carrot|

并希望输出重复索引的两列(第一列):

Fruit     | Apple
Fruit     | Banana
Fruit     | Grape
Fruit     | Peach
Vegetable | Cabbage
Vegetable | Lettuce
Vegetable | Carrot

由于数据量小,用户缺乏经验,因此使用简单比效率更重要。谢谢

答案1

如果我们有这个工作表1

在此处输入图片描述

并运行这个简短的宏:

Sub ReOrganize()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    Dim N As Long, M As Long, i As Long, j As Long, K As Long
    Dim t1 As String, t2 As String
    N = sh1.Cells(Rows.Count, "A").End(xlUp).Row
    K = 1

    For i = 1 To N
        t1 = sh1.Cells(i, 1).Value
        M = sh1.Cells(i, Columns.Count).End(xlToLeft).Column
        For j = 2 To M
            sh2.Cells(K, 1).Value = t1
            sh2.Cells(K, 2).Value = sh1.Cells(i, j)
            K = K + 1
        Next j
    Next i
End Sub

我们将工作表2

在此处输入图片描述

相关内容