本文是數(shù)據(jù)透視表系列教程十，介紹一個更為普遍的二維報表整理為數(shù)據(jù)清單的方法。
下面1圖所示的二維報表數(shù)據(jù)。這種表格設(shè)計的初衷是為了方便輸入數(shù)據(jù)，但是卻為數(shù)據(jù)匯總和分析造成了麻煩。例如，要統(tǒng)計辦公室的復(fù)印紙的耗量和費用，就比較麻煩。對于這樣的表格，最后將其設(shè)計為日記流水賬的形式，如圖2所示。盡管在輸入數(shù)據(jù)時有些麻煩，但統(tǒng)計匯總分析就很方便。
那么如何將圖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("日期", "材料", "單位", "部門", "數(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
只要運行上面代碼，就可以迅速的將二維報表數(shù)據(jù)轉(zhuǎn)換為數(shù)據(jù)清單。現(xiàn)在就可以利用整理好的“數(shù)據(jù)清單”制作數(shù)據(jù)透視表。
Excel數(shù)據(jù)透視表系列教程十
