规范化连接的Excel数据

Question

我有一个Excel电子表格，里面有这样的数据：

Task  |TaskRepeat  
Task1 |M,W,F  
Task2 |T,Th  

我想把它转换成这样：

Task1  | M  
Task1  | W  
Task1  | F  
Task2  | T  
Task2  | Th  

我熟悉VBA，但在Access中使用它的次数比Excel多得多。此任务是否有Excel解决方案（公式或VBA）？

Answer 1

这是代码，它假设您的列表在A和B列，并将列表放在D和E中，并且数据从第2行开始

Sub Normalize()
    Dim lngFirstRow As Long
    Dim lngLastRow As Long
    Dim cRow As Long
    Dim shSrc As Worksheet
    Dim lngNextDestRow As Long
    Dim varDay As Variant
    Dim arrDays() As String
    Application.ScreenUpdating = False
    lngFirstRow = 2
    lngNextDestRow = 2

    Set shSrc = ActiveWorkbook.ActiveSheet
    With shSrc
     .Range("D1") = .Range("A1")
     .Range("E1") = .Range("B1")
      lngLastRow = .Cells.Find(What:="*", After:=.Cells.Cells(1), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
      For cRow = lngFirstRow To lngLastRow Step 1
           arrDays() = Split(.Range("B" & cRow), ",")
            For Each varDay In arrDays
                .Range("D" & lngNextDestRow) = .Range("A" & cRow)
                .Range("E" & lngNextDestRow) = varDay
                 lngNextDestRow = lngNextDestRow + 1
            Next varDay
        Next cRow
    End With
         Application.ScreenUpdating = True
 End Sub