Excelで既に表になってるデータをピボットテーブル用に並べ替える

Excelのピボットテーブルは、使いこなせるようになるとかなり強力なのだが、元になるデータがいわゆるリレーショナルな値の組になっていることが前提となっている。外部DBなどからSQLで取ってきたデータならそのまま使えるが、元データが既に表になっている場合は、わざわざリレーショナル形式に並び替える必要がある。

例えば、

という表があった場合は、これを

という形に変換しなければならない。

これを手動でやっていてはたまらんのでVBAで一発変換できるようにしてみた。

Option Explicit

Const DATASHEET_NAME As String = "_DATA_"

Sub PivotData()
   Dim sht1 As Worksheet ' コピー元のシート
   Dim sht2 As Worksheet ' コピー先のシート
   Dim rng1 As Range     ' コピー元の範囲
   Dim rng2 As Range     ' コピー先の範囲
   Dim col1 As Integer, row1 As Integer ' コピー元のセル位置
   Dim col2 As Integer, row2 As Integer ' コピー先のセル位置

   'On Error GoTo HANDLER
   'Application.ScreenUpdating = False

   Set rng1 = Selection 'Range
   Set sht1 = rng1.Worksheet
   Set sht2 = GetSheetOrNew(DATASHEET_NAME)

   ' コピー先をアクティブにしておく
   sht2.Activate

   ' コピー先の行位置を現在使用中の範囲の次にする
   row2 = sht2.UsedRange.Row + sht2.UsedRange.Rows.Count

   ' 行ごとにコピーを繰り返す
   For col1 = rng1.Column + 1 To rng1.Column + rng1.Columns.Count - 1
       For row1 = rng1.Row + 1 To rng1.Row + rng1.Rows.Count - 1
           CopyAsLink sht1.Cells(rng1.Row, col1), sht2.Cells(row2, 1)    '列タイトル
           CopyAsLink sht1.Cells(row1, rng1.Column), sht2.Cells(row2, 2) '行タイトル
           CopyAsLink sht1.Cells(row1, col1), sht2.Cells(row2, 3)        'データ
           row2 = row2 + 1
       Next row1
   Next col1

   ' １秒後にコピー元をアクティブに戻す
   Application.Wait Now + TimeValue("0:00:01")
   sht1.Activate
   ' コピーモードを解除
   Application.CutCopyMode = False
HANDLER:
   'Application.ScreenUpdating = True
End Sub

Function GetSheetOrNew(sSheetName As String) As Worksheet
   Dim sht As Worksheet

   On Error GoTo HANDLER
   Set GetDataSheet = ActiveWorkbook.Sheets(sSheetName)
   Exit Function
HANDLER:
   On Error GoTo 0
   Set sht = ActiveWorkbook.Worksheets.Add
   sht.Name = sSheetName
   Resume
End Function

Sub CopyAsLink(rng1 As Range, rng2 As Range)
   rng1.Copy
   rng2.Select
   rng2.Worksheet.Paste Link:=True
End Sub

行見出しや列見出しが多段階になっている場合は、見出しを「大項目:中項目::小項目」のように適当にセパレータを入れて正規化した列・行をつくってこのマクロを実行し、リレーショナル形式になってから分解しなおすとよい。まあ、面倒だけど全部手でやることを考えたら格段に楽になる。

さて、iPhone Developer登録がやっと完了。結局アカウント情報に日本語が含まれていると文字化けして申請処理が進まないらしい。サポートにメールしたらあっという間に対応してもらえた。