【VBAリファレンス】VBA100本ノック74本目攻略！複数表をDB形式へ変換するプロの技術

スポンサーリンク

概要

Excel VBAのスキルアップにおいて避けて通れない「データの正規化」という壁。その中でも、1つのシート内に複数の表が乱立している、いわゆる「人間が見るための帳票」を、データ分析やシステム連携に最適な「DB（データベース）形式」に変換する作業は、実務で最も頻繁に遭遇する難所の一つです。

「VBA100本ノック」の74本目として名高いこの課題は、単なるループ処理の習得にとどまりません。セルのアドレスを動的に制御し、不規則なレイアウトから必要なデータだけを抽出・再配置する論理的思考力が試されます。本記事では、この課題を最短かつ堅牢なコードで解決するためのプロフェッショナルなアプローチを徹底解説します。

詳細解説

DB形式とは、一般的に「1行1レコード」のフラットな構造を指します。一方、今回対象とするような「1シート複数表」は、見出しが点在し、行と列に無駄な空白や結合セルが含まれることが多々あります。

この変換を成功させるためのアルゴリズムは、以下の3ステップに集約されます。

1. ループの境界設定：シート内の各表がどこから始まり、どこで終わるかを特定する。
2. ヘッダーの固定と動的値の取得：表ごとに共通する項目（例えば、表のタイトルや日付など）を保持しつつ、個別のデータ行を順次走査する。
3. 配列への格納と一括出力：セルへの直接書き込みは速度低下の最大の要因です。メモリ上で二次元配列を構築し、最後にシートへ一括出力するのが「プロの作法」です。

特に重要なのは「Findメソッド」または「Range.End(xlDown)」を駆使して、動的に表の範囲を特定することです。固定値をコードに書き込むハードコーディングは、レイアウト変更に弱い「使い捨てのマクロ」を生む原因となります。

サンプルコード

以下に、汎用性を重視したサンプルコードを提示します。


Sub ConvertTablesToDB()
    Dim wsSrc As Worksheet, wsDest As Worksheet
    Dim rStart As Range, rEnd As Range
    Dim dataArr() As Variant
    Dim i As Long, j As Long, k As Long
    Dim rowCount As Long
    
    Set wsSrc = ThisWorkbook.Sheets("Sheet1")
    Set wsDest = ThisWorkbook.Sheets.Add
    
    ' データ格納用の配列を準備（列数は必要に応じて調整）
    ReDim dataArr(1 To 10000, 1 To 4)
    k = 1
    
    ' 表の開始位置を検索（例として「表タイトル」という文字列を基準にする）
    Set rStart = wsSrc.Cells.Find(What:="表タイトル", LookAt:=xlWhole)
    
    Do While Not rStart Is Nothing
        ' 表の範囲を特定（空行までを1つの表とみなす）
        Set rEnd = rStart.Offset(2, 0).End(xlDown)
        
        ' データ行を走査して配列に格納
        For i = rStart.Offset(2, 0).Row To rEnd.Row
            dataArr(k, 1) = rStart.Value ' 共通情報
            dataArr(k, 2) = wsSrc.Cells(i, 1).Value
            dataArr(k, 3) = wsSrc.Cells(i, 2).Value
            dataArr(k, 4) = wsSrc.Cells(i, 3).Value
            k = k + 1
        Next i
        
        ' 次の表を探す
        Set rStart = wsSrc.Cells.FindNext(rStart)
        ' ループ脱出条件（無限ループ回避）
        If rStart.Address = wsSrc.Cells.Find(What:="表タイトル").Address Then Exit Do
    Loop
    
    ' 結果を一括出力
    wsDest.Range("A1").Resize(k - 1, 4).Value = dataArr
End Sub

実務アドバイス

実務において最も陥りやすい罠は、「例外的なレイアウト」への対応漏れです。例えば、表の途中に小計行が入っていたり、一部のセルが結合されていたりする場合、上記のシンプルなコードではエラーが発生します。

1. エラーハンドリングの徹底：`On Error Resume Next`を安易に使用するのではなく、`If Not … Is Nothing`でオブジェクトの存在を確認する習慣をつけてください。
2. 結合セルの判定：結合セルがある場合、`Range.Cells(1, 1).Value`で値を取得するように制御しないと、期待した値が取得できません。
3. データの型変換：DB形式に変換する際、数値や日付が文字列として認識されることがあります。出力後に`NumberFormatLocal`プロパティで書式を整えるか、あるいは配列格納時に明示的に型変換（`CDbl`や`CDate`）を行うことが、後のデータ分析の精度を決定づけます。

また、大規模なデータを取り扱う場合は、配列のサイズを最初から固定せず、`ReDim Preserve`を使用して動的に拡張する手法も有効ですが、処理速度を優先するなら、あらかじめ最大行数を見積もって配列を確保しておく方が効率的です。

まとめ

「1シート複数表をDB形式に変換する」という作業は、単なる事務作業の自動化を超え、データ基盤を整えるためのエンジニアリング的アプローチが求められるタスクです。

今回紹介した「動的な範囲特定」「配列による高速処理」「例外を考慮した論理設計」という3つの軸を意識することで、あなたのVBAコードの質は劇的に向上するでしょう。100本ノックの74本目は、あなたが単なる「マクロ記録者」から「自動化エンジニア」へとステップアップするための重要な試金石です。

ぜひ、今回学んだ技術を自分の手でコードに落とし込み、現場のデータ構造に合わせた最適なソリューションを構築してください。VBAの可能性は、あなたの論理的思考力によって無限に広がります。

スポンサーリンク
タイトルとURLをコピーしました