程式好好玩

原始資料

VBA轉換程式碼

Sub 轉換表格()
    '清除三欄內容
    Sheet2.Columns(1).ClearContents
    Sheet2.Columns(2).ClearContents
    Sheet2.Columns(3).ClearContents
    
    '展開群組
    Sheet1.Outline.ShowLevels RowLevels:=2
    Sheet1.Outline.ShowLevels ColumnLevels:=2
    
    '遍歷欄、列
    Row = 1
    For i = 2 To Sheet1.Range("A" & Rows.Count).End(xlUp).Row
        For j = 2 To Sheet1.Cells(1, Sheet1.Columns.Count).End(xlToLeft).Column
            If Sheet1.Cells(i, j).Value <> "" Then
                Sheet2.Cells(Row, 1).Value = Sheet1.Cells(i, 1).Value 'ABC
                Sheet2.Cells(Row, 2).Value = Sheet1.Cells(1, j).Value '123
                Sheet2.Cells(Row, 3).Value = Sheet1.Cells(i, j).Value '內容
                
                 Row = Row + 1
            End If
           
            DoEvents
        Next
    Next
End Sub

轉換後資料