MS Excel:将行转换为列

Question

如果Excel电子表格中有几行，那么：

Number      Email-1      Email-2      Email-X   Email-XX
12345       address1     address2     addressX  addressXX
67890       address1     address2     addressX
98765       address1     address2     addressX  addressXX

我需要将所有的电子邮件地址集中在一列中，并在相邻的一列中附上它们的编号：

Number      Email
12345       address1
12345       address2
12345       addressX
12345       addressXX
67890       address1
67890       address2
67890       addressX
98765       address1
98765       address2
98765       addressX
98765       addressXX

我怎样才能做到这一点？我尝试过改变粘贴特别->转置，但这让我是这样的：

 12345
 address1
 address2
 addressX
 addressXX

想法？

Answer 1

此宏假定原始数据在Sheet1中，输出数据将用Sheet2编写。

Sub dural()
    Dim N As Long, s1 As Worksheet, s2 As Worksheet, _
        i As Long, j As Long, M As Long, v As Variant, _
        k As Long
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    N = s1.Cells(Rows.Count, "A").End(xlUp).Row
    k = 1
    For i = 2 To N
        v = s1.Cells(i, "A").Value
        M = s1.Cells(i, Columns.Count).End(xlToLeft).Column
        For j = 2 To M
            s2.Cells(k, 1).Value = v
            s2.Cells(k, 2).Value = s1.Cells(i, j).Value
            k = k + 1
        Next j
    Next i
End Sub

Answer 2

在行和列之间移动数据，在一个或多个列或行中复制数据。在粘贴复制的数据之前，右键单击第一个目标单元格(要粘贴数据的行或列的第一个单元格)，然后单击“粘贴特殊”。在“粘贴特殊”对话框中，选择“转置”，然后单击“确定”。