VBA100本ノック42本目：クロス集計表をデータベース形式へ正規化する技術
業務効率化の現場において、最も頻繁に遭遇する「厄介なデータ構造」の一つが、人間が見やすいように加工されたクロス集計表です。行に項目、列に日付やカテゴリが配置された表は、視認性は高いものの、VBAやPower Queryで分析を行うには非常に不向きです。
本稿では、VBA100本ノックの第42本目として、「クロス集計形式のデータをデータベース形式（リスト形式）に変換する」というテーマを深掘りします。この技術を習得することは、データ加工の自動化において避けて通れない登竜門です。
データベース形式への変換とは何か
データベース形式（フラットファイル構造）とは、1行が1レコードとして完結し、項目名が列見出しとして固定されている形式を指します。一方、クロス集計表は、列見出しが「値」そのもの（例えば日付や商品名）を含んでいます。
変換の目的は、データの「正規化」です。これを実現することで、ピボットテーブルでの集計、フィルター操作、あるいは外部データベースへの取り込みが容易になります。具体的には、以下の変換を行います。
・クロス集計表の「行見出し」と「列見出し」を、リスト形式の新しい列として配置する。
・交差する「値」を、対応するレコードとして抽出する。
詳細解説：二重ループによるデータ抽出のロジック
この課題を解決するための最も直感的かつ汎用的な手法は、二重ループ（For Next文）を使用することです。
1. 行の起点となる項目（例：担当者名や商品名）を特定する。
2. 列の起点となる項目（例：日付や月）を特定する。
3. 外側のループで行を走査し、内側のループで列を走査する。
4. 交差するセルに値が存在する場合、配列または直接セルへ転記する。
ここで重要なのは、セルを一つずつ読み込んで転記する際、画面更新（ScreenUpdating）を停止させることです。VBAの処理速度を低下させる最大の要因は、セルの読み書き回数です。実務レベルでは、メモリ上で配列操作を行うのがベストですが、初心者から中級者へのステップアップとしては、まずは標準的なセル操作のアルゴリズムを完璧に理解することが求められます。
サンプルコード：クロス集計からリスト形式への変換
以下に、汎用性の高い変換ロジックを提示します。このコードは、A列に行項目、1行目に列項目がある表を想定しています。
Sub ConvertToDatabaseFormat()
Dim wsSource As Worksheet, wsDest As Worksheet
Dim lastRow As Long, lastCol As Long
Dim i As Long, j As Long, destRow As Long
' 画面更新停止による高速化
Application.ScreenUpdating = False
Set wsSource = ThisWorkbook.Worksheets("Sheet1")
Set wsDest = ThisWorkbook.Worksheets.Add
wsDest.Name = "DatabaseFormat"
' ヘッダーの設定
wsDest.Range("A1:C1").Value = Array("項目名", "列見出し", "値")
destRow = 2
' 最終行と最終列の取得
lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column
' 二重ループによる抽出
For i = 2 To lastRow
For j = 2 To lastCol
' 値が空でない場合のみ転記
If Not IsEmpty(wsSource.Cells(i, j)) Then
wsDest.Cells(destRow, 1).Value = wsSource.Cells(i, 1).Value
wsDest.Cells(destRow, 2).Value = wsSource.Cells(1, j).Value
wsDest.Cells(destRow, 3).Value = wsSource.Cells(i, j).Value
destRow = destRow + 1
End If
Next j
Next i
Application.ScreenUpdating = True
MsgBox "変換が完了しました。"
End Sub
実務アドバイス：プロフェッショナルが意識するポイント
上記のコードは基本形ですが、実務で扱うデータはさらに複雑です。以下の3つの観点を意識することで、あなたのVBAコードはよりプロフェッショナルなものになります。
1. データ型の整合性
クロス集計表の「値」には、数値だけでなくエラー値や空白が含まれることがあります。特に日付データが文字列として入力されている場合、変換後に日付型として認識されないトラブルが多発します。転記時に `Val` 関数や `CDate` 関数を用いて、明示的に型変換を行う習慣をつけましょう。
2. 配列への格納（Array）
数万行を超えるデータに対して上記のコードを実行すると、処理が重くなります。実務では、`wsSource.Range(“A1”).CurrentRegion.Value` で範囲全体を一度に配列変数に格納し、メモリ上で処理を行ってから、一気にシートへ出力する手法が推奨されます。これにより、処理時間は劇的に短縮されます。
3. エラーハンドリング
「列見出しが途中で途切れていないか」「行見出しに重複がないか」など、入力データの品質は常に不安定です。`On Error GoTo` を使用したエラー制御や、データチェック用のサブルーチンを挟むことで、予期せぬ停止を防ぐことができます。
まとめ
VBA100本ノックの42本目である「データベース形式への変換」は、単なる表の組み替え以上の意味を持ちます。それは、「人間が読みやすい表」から「コンピュータが処理しやすいデータ」へ変換する、データエンジニアリングの第一歩です。
このアルゴリズムをマスターすれば、複雑な月次レポート、在庫管理表、勤怠集計表など、あらゆるクロス集計データを一瞬で分析可能な状態に整理できるようになります。
まずは上記のサンプルコードを写経し、自分の手元にある「整っていないデータ」に対して適用してみてください。コードを動かし、自分の意図通りにデータが展開される瞬間こそが、VBAプログラミングの醍醐味です。さらに高度な実装を目指すなら、次は「配列を用いた高速化」と「Power Queryとの比較」に挑戦することをお勧めします。VBAは万能ですが、時にExcel標準機能であるPower Queryの方が適しているケースもあります。その判断ができるようになることこそが、真のベテランエンジニアの道です。
