excel 完成更為普遍的二維報表整理為數據清單的辦法

下面1圖所示的二維報表數據。這種表格設計的初衷是為了方便輸入數據，但是卻為數據匯總和分析造成了麻煩。例如，要統計辦公室的復印紙的耗量和費用，就比較麻煩。對于這樣的表格，最后將其設計為日記流水賬的形式，如圖2所示。盡管在輸入數據時有些麻煩，但統計匯總分析就很方便。

　　那么如何將圖1所示的二維表格轉換為圖2所示的規范表格呢，利用函數也是很復雜的，可以利用VBA編制程序比較方便。下面就是相關的VBA程序代碼。

Public Sub DataList()
??? Dim myArray As Variant
??? Dim n As Long, i As Long, k As Long, j As Long
??? Dim ws0 As Worksheet
??? Dim wsNew As Worksheet
??? myArray = Array("日期", "材料", "單位", "部門", "數量", "金額")
??? Set ws0 = Worksheets("不科學表格")
??? n = ws0.Range("A65536").End(xlUp).Row - 2
??? On Error Resume Next
??? Application.DisplayAlerts = False
??? Worksheets("數據清單").Delete
??? Application.DisplayAlerts = False
??? On Error GoTo 0
??? Set wsNew = Worksheets.Add
??? With wsNew
??????? .Name = "數據清單"
??????? .Range("A1:F1") = myArray
??????? k = 1
??????? For j = 4 To 8 Step 2
??????????? For i = 1 To n
??????????????? If ws0.Cells(i + 2, j) <> "" Then
??????????????????? .Cells(k + 1, 1) = Format(ws0.Cells(i + 2, 1), "yyyy-m-d")
??????????????????? .Cells(k + 1, 2) = ws0.Cells(i + 2, 2)
??????????????????? .Cells(k + 1, 3) = ws0.Cells(i + 2, 3)
??????????????????? .Cells(k + 1, 4) = ws0.Cells(1, j)
??????????????????? .Cells(k + 1, 5) = ws0.Cells(i + 2, j)
??????????????????? .Cells(k + 1, 6) = ws0.Cells(i + 2, j + 1)
??????????????????? k = k + 1
??????????????? End If
??????????? Next i
??????? Next j
??? End With
??? Set ws0 = Nothing
??? Set wsNew = Nothing
End Sub

　　只要運行上面代碼，就可以迅速的將二維報表數據轉換為數據清單。現在就可以利用整理好的“數據清單”制作數據透視表。