我有一张 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: