excel - 有没有办法根据单元格值中的多个条目在 excel 中通用重复行

Question

这是我目前的格式

我想要一个公式，以便输出是这样的

标签： excelduplicatesrows

Answer 1

对于A和B列中的数据，试试这个简短的宏：

Sub SplitList()
    Dim i As Long, j As Long, N As Long
    Dim v As String, arr, a

    N = Cells(Rows.Count, "A").End(xlUp).Row
    j = 1

    For i = 1 To N
        v = Cells(i, 1).Value
        arr = Split(Cells(i, 2).Value, ",")
        For Each a In arr
            Cells(j, 3).Value = v
            Cells(j, 4).Value = a
            j = j + 1
        Next a
    Next i
End Sub

输出在列C和D中。