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

　　那么如何將圖1所示的二維表格轉(zhuǎn)換為圖2所示的規(guī)范表格呢，利用函數(shù)也是很復(fù)雜的，可以利用VBA編制程序比較方便。下面就是相關(guān)的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("日期", "材料", "單位", "部門(mén)", "數(shù)量", "金額")
??? Set ws0 = Worksheets("不科學(xué)表格")
??? n = ws0.Range("A65536").End(xlUp).Row - 2
??? On Error Resume Next
??? Application.DisplayAlerts = False
??? Worksheets("數(shù)據(jù)清單").Delete
??? Application.DisplayAlerts = False
??? On Error GoTo 0
??? Set wsNew = Worksheets.Add
??? With wsNew
??????? .Name = "數(shù)據(jù)清單"
??????? .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

　　只要運(yùn)行上面代碼，就可以迅速的將二維報(bào)表數(shù)據(jù)轉(zhuǎn)換為數(shù)據(jù)清單。現(xiàn)在就可以利用整理好的“數(shù)據(jù)清單”制作數(shù)據(jù)透視表。