【VBAリファレンス】エクセル入門直積（クロス結合、交差結合）とピボット解除

エクセルにおける「直積」と「ピボット解除」の重要性

データ分析の現場において、Excelの操作スキルは単なる表計算の域を超え、いかに効率的にデータを「正規化（整理）」できるかにかかっています。多くのユーザーが直面する課題は、縦持ち（リスト形式）と横持ち（マトリックス形式）の変換、そして異なるデータソースを論理的に結合する手法の欠如です。

本記事では、データベース理論における「直積（クロス結合）」の概念をExcel上で再現する方法と、分析の最大の敵である「ピボット解除（Unpivot）」の技術について、VBAを用いた自動化の視点を交えて徹底解説します。

直積（クロス結合）とは何か

直積（Cartesian Product）とは、集合論における演算の一つで、2つのデータセットの全組み合わせを生成する手法です。例えば、Aテーブルに3つの商品、Bテーブルに4つの店舗がある場合、直積を行うと「3 × 4 = 12」のレコードが生成されます。

Excelにおいて、この操作は「全店舗で全商品を網羅した売上予測テンプレートを作る」といった場面で極めて有効です。手作業で行うとミスが生じやすく、データ量が増えるほど現実的ではありません。これをVBAで処理することで、一瞬にして膨大な組み合わせパターンを生成することが可能になります。

ピボット解除（Unpivot）の必要性

ピボット解除とは、横方向に展開されたヘッダー（例：1月、2月、3月…）を、1つの列（例：月、値）に変換する操作です。Excelのピボットテーブルは「ピボット解除されたデータ」をソースとすることを前提に設計されています。横持ちのデータは視認性は良いものの、集計や検索には不向きです。Power Queryを使えば標準機能で可能ですが、VBAで実装することで、動的なレポート生成や他システムとの連携が容易になります。

VBAによる直積の生成実装

以下のコードは、シート「Products」のリストと「Stores」のリストを読み込み、シート「Result」に全ての組み合わせ（直積）を出力するプロシージャです。

Sub CreateCartesianProduct()
    Dim wsP As Worksheet, wsS As Worksheet, wsR As Worksheet
    Dim lastRowP As Long, lastRowS As Long
    Dim i As Long, j As Long, count As Long
    
    Set wsP = ThisWorkbook.Sheets("Products")
    Set wsS = ThisWorkbook.Sheets("Stores")
    Set wsR = ThisWorkbook.Sheets("Result")
    
    wsR.Cells.Clear
    wsR.Range("A1:B1").Value = Array("Product", "Store")
    
    lastRowP = wsP.Cells(wsP.Rows.Count, "A").End(xlUp).Row
    lastRowS = wsS.Cells(wsS.Rows.Count, "A").End(xlUp).Row
    
    count = 2
    For i = 2 To lastRowP
        For j = 2 To lastRowS
            wsR.Cells(count, 1).Value = wsP.Cells(i, 1).Value
            wsR.Cells(count, 2).Value = wsS.Cells(j, 1).Value
            count = count + 1
        Next j
    Next i
    
    MsgBox "直積の生成が完了しました。"
End Sub

このコードのポイントは、二重ループ構造にあります。外側のループで商品リストを回し、その各要素に対して内側のループで店舗リストを網羅します。これにより、論理的に全ての組み合わせが生成されます。

VBAによるピボット解除の実装

次に、横持ちのデータを縦持ちに変換するロジックです。ここでは、各列に日付が並んでいる表を、行方向のリストに変換します。

Sub UnpivotData()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim r As Long, c As Long, targetRow As Long
    
    Set wsSource = ThisWorkbook.Sheets("Source")
    Set wsTarget = ThisWorkbook.Sheets("Target")
    
    wsTarget.Cells.Clear
    wsTarget.Range("A1:C1").Value = Array("Item", "Date", "Value")
    targetRow = 2
    
    For r = 2 To wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
        For c = 2 To wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column
            wsTarget.Cells(targetRow, 1).Value = wsSource.Cells(r, 1).Value
            wsTarget.Cells(targetRow, 2).Value = wsSource.Cells(1, c).Value
            wsTarget.Cells(targetRow, 3).Value = wsSource.Cells(r, c).Value
            targetRow = targetRow + 1
        Next c
    Next r
End Sub

このコードは、行と列の交差地点を走査し、それを「1行ずつのリスト」として出力し直します。データ量が多い場合は、配列（Variant型）を使用してメモリ上で処理した後に一括出力することで、処理速度を劇的に向上させることが可能です。

実務アドバイス：なぜ自動化が必要か

実務においてこれらの処理を自動化すべき理由は、単なる時短ではありません。「データの不整合を排除する」という目的が最も重要です。

1. 再現性の担保：手作業でのコピペは、行のズレやコピー漏れを誘発します。VBA化することで、常に同じロジックで正確なデータセットを生成できます。
2. 拡張性：店舗が増えた、あるいは商品ラインナップが変更された場合、VBAであればデータ範囲を自動取得するように記述しておくことで、コードの修正なしに対応可能です。
3. Power Queryとの使い分け：近年ではPower Query（パワークエリ）が標準搭載されており、ピボット解除はボタン一つで可能です。しかし、VBAが必要な場面は「ユーザーにUIを提供し、特定のボタン一つで完結させたい場合」や「複雑な条件分岐を含むデータ変換」を行う場合です。プロフェッショナルとしては、両方の武器を持ち、状況に応じて使い分けることが求められます。

パフォーマンス向上へのアプローチ

大量のデータ（数万行以上）を扱う場合、セルへの直接アクセスは極めて低速です。実務レベルでは、以下の最適化を推奨します。

– 画面更新の停止：Application.ScreenUpdating = False を記述することで、描画をスキップし、処理速度を大幅に向上させます。
– 配列処理（Array）：セルを直接操作するのではなく、一度Variant型の変数にデータを格納し、処理後に一括でシートへ書き出すことで、Excelの再計算負荷を最小限に抑えられます。
– 最終行の動的取得：固定の範囲を指定せず、RangeオブジェクトのEndプロパティを適切に使用し、どのようなデータ量にも対応できる柔軟なコードを記述してください。

まとめ

直積の生成とピボット解除は、データ分析の前処理において避けては通れない「データの整形」という重要なプロセスです。これらの概念を理解し、VBAやPower Queryで自動化できるようになることで、Excel作業の質は劇的に向上します。

多くのエンジニアが「分析」そのものに時間をかけようとしますが、実際には「分析可能な状態にするための前処理」に作業時間の8割が費やされています。今回紹介した手法をマスターし、単なる表計算ソフトのユーザーから、真のデータエンジニアへとステップアップしてください。

自動化の第一歩は、日々の単純作業を「パターン化」することから始まります。ぜひ、お手元のデータで今回紹介したコードを試し、その効率化の恩恵を実感してください。プロフェッショナルなExcel活用は、常に「構造化」への意識から始まります。