【Excel】ピボットテーブルで「データモデル」を使う!Excelの複数テーブルをリレーションで結合する方法

Excelで複数のテーブルを扱っていて、それぞれのデータをまとめて分析したい場面はありませんか。

通常、ピボットテーブルは単一のテーブルを元に作成しますが、Excelの「データモデル」機能を使えば、複数のテーブルを関連付けて1つのピボットテーブルで分析できます。

この記事では、Excelのデータモデルを使って複数テーブルをリレーションで結合し、ピボットテーブルで分析する手順を解説します。

データモデルの仕組みとメリット

Excelのデータモデルは、複数のテーブルを「リレーションシップ」で結びつけることで、あたかも1つの大きなテーブルであるかのように扱えるようにする機能です。

これにより、本来は別々のテーブルに格納されているデータを、ピボットテーブルで横断的に集計・分析できるようになります。

従来のExcelでは、複数テーブルのデータを統合するには、VLOOKUP関数などでデータを1つのテーブルに集約する手間がかかりました。データモデルを使えば、この手間を大幅に削減できます。

ピボットテーブルでデータモデルを利用する準備

データモデルを利用するには、まず分析したいデータをExcelのテーブル形式で用意する必要があります。

各テーブルは、互いに関連付けられる「共通の列」を持っていることが重要です。

例えば、売上データテーブルと商品マスタテーブルを結合する場合、「商品ID」などの共通の列が必要です。

テーブルの準備と確認

分析対象となる各テーブルをExcelシート上に準備します。

各テーブルの1行目には、列名を意味するヘッダー行を設定してください。

各テーブルのデータ範囲を選択し、「挿入」タブの「テーブル」をクリックして、Excelテーブルに変換しておくと管理しやすくなります。

テーブルに変換する際は、「先頭行をテーブルヘッダーとして使用する」にチェックが入っていることを確認してください。

テーブル名は、後でリレーションシップを作成する際に分かりやすい名前に変更することをお勧めします。

データモデルへの追加

各テーブルをデータモデルに追加するには、以下の手順を実行します。

1. テーブル内のセルを選択
   データモデルに追加したいテーブル内の任意のセルをクリックします。
2. 「ピボットテーブル」の作成
   「挿入」タブの「ピボットテーブル」をクリックします。
3. 「ピボットテーブルから」を選択
   「ピボットテーブルの作成」ダイアログボックスが表示されます。「テーブル/範囲から」が選択されていることを確認します。
4. 「このデータをデータモデルに追加する」を選択
   ダイアログボックスの下部にある「このデータをデータモデルに追加する」にチェックを入れます。
5. 配置場所を選択
   「新規ワークシート」または「既存のワークシート」を選択し、「OK」をクリックします。

この手順を、結合したいすべてのテーブルに対して繰り返します。

各テーブルをデータモデルに追加すると、ピボットテーブルのフィールドリストには、追加したテーブルがすべて表示されるようになります。

テーブル間のリレーションシップを作成する

データモデルにテーブルが追加されたら、次にテーブル間の関連付け(リレーションシップ)を作成します。

リレーションシップは、テーブル間で共通する列(キー)を基に設定されます。

例えば、「売上データ」テーブルの「商品ID」列と、「商品マスタ」テーブルの「商品ID」列を結びつけます。

リレーションシップの作成手順

リレーションシップは、ピボットテーブルのフィールドリストから作成できます。

1. ピボットテーブルのフィールドリストを表示
   データモデルを元に作成したピボットテーブルの任意の場所をクリックして、フィールドリストを表示させます。
2. 「すべて」タブを選択
   フィールドリストの上部にある「すべて」タブをクリックして、追加したすべてのテーブルを表示させます。
3. 「リレーションシップ」を選択
   「ピボットテーブル分析」タブ(または「分析」タブ)にある「リレーションシップ」をクリックします。
4. 「新規作成」をクリック
   「リレーションシップ」ダイアログボックスが表示されたら、「新規作成」ボタンをクリックします。
5. テーブルと列を指定
   「テーブルの選択」で、リレーションシップを作成する一方のテーブルを指定します。次に、「関連する列の選択」で、共通の列(キー)を選択します。
6. もう一方のテーブルと列を指定
   「関連テーブルの選択」で、もう一方のテーブルを指定します。次に、「関連する列の選択」で、対応する共通の列を選択します。
7. 「OK」をクリック
   設定が完了したら、「OK」をクリックしてリレーションシップを作成します。

この手順で、必要なテーブル間のリレーションシップをすべて作成してください。

リレーションシップが正しく作成されると、異なるテーブルのフィールドを組み合わせてピボットテーブルに配置できるようになります。

リレーションシップの確認と編集

作成したリレーションシップは、「リレーションシップ」ダイアログボックスで確認・編集できます。

「ピボットテーブル分析」タブから「リレーションシップ」を選択し、「編集」または「削除」を行えます。

リレーションシップの方向やカーディナリティ(一対多、一対一など)もここで設定できますが、基本的にはExcelが自動で判断してくれる場合が多いです。

リレーションシップで結合されたデータを使ったピボットテーブルの作成

データモデルにテーブルを追加し、リレーションシップを作成したら、いよいよそれらのデータを統合したピボットテーブルを作成します。

ピボットテーブルの作成手順は、単一テーブルの場合とほぼ同じですが、フィールドリストに複数のテーブルが表示される点が異なります。

ピボットテーブルの作成手順

以下の手順で、リレーションシップで結合されたデータを使ったピボットテーブルを作成します。

1. ピボットテーブルのフィールドリストを表示
   データモデルを元に作成したピボットテーブルの任意の場所をクリックして、フィールドリストを表示させます。
2. 「すべて」タブを選択
   フィールドリストの上部にある「すべて」タブをクリックして、追加したすべてのテーブルを表示させます。
3. フィールドの配置
   分析したいデータを、各テーブルから選択して「行」「列」「値」「フィルター」エリアにドラッグ&ドロップします。

例えば、「売上データ」テーブルから「売上金額」を「値」エリアに、「商品マスタ」テーブルから「商品名」を「行」エリアに配置できます。

リレーションシップが正しく設定されていれば、異なるテーブルのフィールドを自由に組み合わせて集計できます。

データモデルの更新

元となるテーブルのデータが更新された場合は、データモデルも更新する必要があります。

ピボットテーブルの任意の場所を右クリックし、「更新」を選択してください。

これにより、データモデル内のデータが最新の状態に反映され、ピボットテーブルの表示も更新されます。

データモデルとピボットテーブルの注意点

データモデル機能は非常に強力ですが、いくつかの注意点があります。

これらを理解しておくことで、より効果的に活用できます。

パフォーマンスに関する注意点

取り込むテーブルの数やデータ量が多い場合、データモデルの処理に時間がかかることがあります。

特に、リレーションシップが複雑になったり、データ量が膨大になったりすると、パフォーマンスが低下する可能性があります。

不要な列や行は事前に削除しておく、リレーションシップを最適化するなどの工夫が有効です。

データ形式の整合性

リレーションシップを作成する共通の列は、データ形式が一致している必要があります。

例えば、一方のテーブルの「商品ID」が数値で、もう一方のテーブルの「商品ID」が文字列になっていると、正しく関連付けられない場合があります。

事前に各テーブルのデータ形式を確認し、必要であれば統一しておきましょう。

リレーションシップの方向

リレーションシップには「一対多」「一対一」などのカーディナリティがあります。

基本的にはExcelが自動で判断しますが、意図しない結果になる場合は、リレーションシップのプロパティを確認・修正する必要があります。

通常は「一対多」の関係で、マスタテーブル(例:商品マスタ)からトランザクションテーブル(例:売上データ)へ向かうリレーションシップになります。

Power Pivotアドイン

より高度なデータモデルの操作や、DAX(Data Analysis Expressions)関数を使った計算フィールドの作成を行いたい場合は、Power Pivotアドインの利用が推奨されます。

Power Pivotは、Excelの「ファイル」>「オプション」>「アドイン」から有効化できます。

Power Pivotを使うと、データモデルの管理がより柔軟になり、複雑な分析が可能になります。

データモデルとPower Queryの連携

データソースがExcelファイルだけでなく、データベースやWebなど多様な場合は、Power Queryとの連携が非常に有効です。

Power Queryでデータを整形・変換し、その結果をデータモデルに追加することで、より効率的なデータ準備と分析が可能になります。

Power Queryでのデータ準備

まず、Power Queryエディターを開き、各データソースからテーブルを読み込みます。

必要に応じて、列の削除、データ型の変更、不要な行のフィルター処理などを行います。

Power Queryでデータ整形が完了したら、「閉じて読み込む」オプションで「接続の作成のみ」を選択し、「データの取得元」ダイアログボックスで「このデータをデータモデルに追加する」にチェックを入れて読み込みます。

Power Queryとデータモデルの連携メリット

Power Queryでデータソースからの読み込みと前処理を自動化し、その結果をデータモデルに追加することで、データの更新作業が大幅に効率化されます。

データソースに変更があった場合でも、Power Queryのクエリを更新するだけで、データモデルとピボットテーブルに最新のデータが反映されるようになります。

これにより、手作業によるミスを防ぎ、分析の信頼性を高めることができます。

まとめ

Excelのデータモデル機能を使えば、複数のテーブルをリレーションシップで結合し、統合されたピボットテーブルで高度な分析が可能です。

テーブルの準備、データモデルへの追加、リレーションシップの作成、そしてピボットテーブルでの集計という一連の手順を習得することで、より複雑なデータ分析業務を効率化できます。

さらに、Power Queryと連携させることで、データの前処理から分析までを自動化し、業務効率を飛躍的に向上させることができるでしょう。

機能	データモデル	単一テーブル
複数テーブルの結合	可能 リレーションシップで結合	不可 VLOOKUP等で事前準備が必要
分析の柔軟性	高 複数テーブルのデータを横断分析	中 単一テーブル内のデータに限定
データ準備の手間	低 リレーションシップ設定のみ	高 VLOOKUP等によるデータ統合が必要
パフォーマンス	中〜低 データ量・リレーション数に依存	高 単一テーブルのため高速

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】