Excelでピボットテーブルのデータモデルを作成する方法（6つの簡単なステップ）

これまで当サイトでは、単一のデータテーブルから生成されるピボットテーブルに焦点を当ててきました。しかし、データモデル機能はピボットテーブルやピボットグラフにさらなる利点をもたらします。データモデルを活用すれば、複数のテーブルのデータを1つのピボットテーブルで扱えるようになります。そのためには、データ同士を結びつけるための「テーブルリレーションシップ」を1つ以上作成する必要があります。データモデルはExcel 2013で導入された機能です。この記事では、Excelでピボットテーブルのデータモデルを作成する6つのステップをわかりやすく解説します。

Excelでピボットテーブルのデータモデルを作成する手順

次の図は、1つのブック内にある3つのテーブルの一部を示しています。各ワークシートは同じブック内にあり、ここでは別々のウィンドウで表示しています。ワークシート名は「Orders（注文）」「Customers（顧客）」「Regions（地域）」です。各ワークシートにはテーブルがあり、整理しやすいように3つのテーブルにも同じく「Orders」「Customers」「Regions」という名前を付けています。「Orders」テーブルには製品の注文情報、「Customers」テーブルには会社の顧客情報、「Regions」テーブルには地域情報が含まれています。

テーブルをよく見ると、「Orders」テーブルと「Customers」テーブルにはCustomerID列が共通しており、「Customers」テーブルと「Regions」テーブルにはState列が共通していることがわかります。これらの共通列を使って、テーブル間のリレーションシップを作成していきます。

あわせて読む：Excelピボットテーブルを手動で作成する方法

一対多のリレーションシップとは

テーブル間のリレーションシップは「一対多」であることに注目してください。「Orders」テーブルの各行に対して、「Customers」テーブルにはCustomerID列によって決まる対応行が必ず1つ存在します。同様に、「Customers」テーブルの各行に対して、「Regions」テーブルにはState列によって決まる対応行が必ず1つ存在します。

💡 注意：データモデルを使用して作成したピボットテーブルには、単一のデータテーブルから作成した場合と比べていくつかの制限があります。最も顕著なのは、グループ化ができないことです。さらに、集計フィールドや集計アイテムも作成できません。

この例での目標は、州別・地域別・年別に売上を集計することです。売上と日付の情報は「Orders」テーブルに、州の情報は「Customers」テーブルに、地域名は「Regions」テーブルにそれぞれ含まれています。したがって、目的のピボットテーブルを作成するには、これら3つのテーブルすべてを使用します。

以下が、ピボットテーブル作成に使用したステップバイステップの手順です。

ステップ1：ピボットテーブルの挿入

最初のステップでは、データセットからピボットテーブルを挿入します。「Orders」テーブル内の任意のセルを選択し、挿入 ➪ ピボットテーブル ➪ テーブル/範囲からを選択します。ピボットテーブルの作成ダイアログボックスが表示されます。

• まず、「Orders」シートのセルC5を選択しました。
• 次に、挿入タブ ➪ ピボットテーブル ➪ テーブル/範囲からを選択します。

ステップ2：データモデルへのデータ追加

分析するデータの選択とピボットテーブルレポートの配置場所の指定は、デフォルトのままにしておきます。「このデータをデータモデルに追加」チェックボックスにチェックを入れ、OKをクリックします。

• すると、ダイアログボックスが表示されます。
• 「このデータをデータモデルに追加」を選択します。
• その後、OKを押します。

• 新しく作成されたワークシートの右側にあるピボットテーブル フィールド作業ウィンドウを見ると、今回はデータモデルを使用するよう選択したため、少し様子が異なっていることがわかります。
• 作業ウィンドウには「アクティブ」と「すべて」の2つのタブがあります。「アクティブ」タブには「Orders」テーブルのみが表示され、「すべて」タブにはブック内のすべてのテーブルが表示されます。
• 「すべて」タブの任意のテーブルを「アクティブ」タブに移動できます。「Customers」テーブルを「アクティブ」タブに表示するには、「すべて」タブを開き、「Customers」テーブルを右クリックして「アクティブ タブに表示」を選択します。同じ操作を「Regions」テーブルにも行います。
• これは任意の操作です。後ほど「すべて」タブからフィールドを選択します。
• 次の図は、ピボットテーブル フィールド作業ウィンドウの「アクティブ」タブです。CustomersテーブルとRegionsテーブルが展開され、列見出し（フィールド名）が表示されています。また、作業ウィンドウのレイアウトも変更しました。この変更を行うには、ツールをクリックし、ドロップダウンメニューから「フィールドのセクションとエリアのセクションを並べて表示」を選択します。

あわせて読む：意味のあるデータ分析のためのピボットテーブル作成術！

ステップ3：リレーションシップの管理

次に、テーブル間のリレーションシップを設定します。ピボットテーブル分析 ➪ 計算 ➪ リレーションシップを選択すると、リレーションシップの管理ダイアログボックスが表示されます。

• まず、新しいピボットテーブル内の任意の場所を選択します。
• 次に、ピボットテーブル分析タブからリレーションシップを選択します。

• すると、別のダイアログボックスが表示されます。
• 次に、新規をクリックします。

類似記事

• Excelピボットテーブルのコピー方法！
• Excelピボットテーブルの編集方法
• Excelピボットテーブルの書式設定（完全ガイド）
• Excelピボットテーブルの8つの活用例 – ピボットテーブルの作り方！
• ピボットテーブルの自動作成

ステップ4：リレーションシップの作成

テーブルドロップダウンで「Orders」を選択し、列（外部）で「CustomerID」を選択します。続いて、関連テーブルで「Customers」を選択し、関連列（主）で「CustomerID」を選択します。

• すると、別のダイアログボックスが表示されます。

• その後、以下のように設定します：
  • テーブル：データモデルテーブル：Orders
  • 関連テーブル：ワークシートテーブル：Customers
  • 列（外部）：CustomerID
  • 関連列（主）：CustomerID

• 次に、OKをクリックすると、リレーションシップの管理ダイアログボックスに戻ります。
• その後、再度新規をクリックします。今度は「Customers」テーブルと「Regions」テーブルの間にリレーションシップを作成します。以下の図のように設定します。
  • テーブル：データモデルテーブル：Customers
  • 関連テーブル：ワークシートテーブル：Regions
  • 列（外部）：State
  • 関連列（主）：State
• そして、OKを押します。

• これで、リレーションシップの管理ダイアログボックスに2つのリレーションシップが表示されます。

💡 注意：事前にテーブルリレーションシップを設定し忘れても心配いりません。別のデータテーブルからピボットテーブルにフィールドを追加しようとした時点で、Excelがリレーションシップの設定を促すメッセージを表示してくれます。

あわせて読む：PowerPivotへのデータ取り込みとピボットテーブル/ピボットグラフの作成方法

ステップ5：ピボットテーブルフィールドを各エリアへ配置

テーブルリレーションシップの設定が完了しました。あとは、フィールド名をピボットテーブル フィールド作業ウィンドウの適切なエリアにドラッグするだけです。

• まず、「Total」フィールドを「値」エリアにドラッグします。
• 次に、「Year」フィールドを「列」エリアに移動します。
• 続いて、「Region」フィールドを「行」エリアにドラッグします。
• 最後に、「StateName」フィールドを「行」エリアに移動します。

• すると、次の図のようなピボットテーブルの一部が表示されます。

ステップ6：スライサーの追加

最後のステップとして、メーリングリスト登録者の有無や製品別にピボットテーブルを絞り込めるよう、2つのスライサー（MailListとProduct）を追加しました。

• まず、ピボットテーブル内の任意の場所を選択します。
• 次に、ピボットテーブル分析からスライサーの挿入を選択します。
• その後、すべてタブから以下を選択します：
  • Customers：MailList
  • Orders：Product
• そして、OKを押します。

• 最終的に、完成したピボットテーブルはこのようになります。

💡 ヒント：ピボットテーブルを数式に変換することもできます。その場合は、ピボットテーブル内の任意のセルを選択し、ピボットテーブル分析 ➪ OLAP ➪ 数式に変換を選択します。ピボットテーブルは数式を使用するセルに置き換えられます。これらの数式はCUBEMEMBER関数とCUBEVALUE関数によって生成されます。変換後の範囲はピボットテーブルではなくなりますが、元のデータが変更されると数式も自動的に更新されます。

あわせて読む：スライサーでExcelピボットテーブルを絞り込む方法！

まとめ

以上、Excelでピボットテーブルのデータモデルを作成する6つの簡単なステップをご紹介しました。複数のテーブルを組み合わせれば、より深い分析が可能になります。ご不明な点があれば、下のコメント欄でお気軽にお尋ねください。それでは、快適なExcelライフを！

関連記事

• Excelピボットテーブルでゼロ値を表示する方法：2つのプロのコツ
• ピボットテーブルの反転 – 集計データの非ピボット化（Unpivot）
• Excelでピボットテーブルレポートを作成する方法
• 初心者向けExcelピボットテーブルチュートリアル ステップバイステップ | PDFダウンロード
• ピボットテーブルを絞り込むタイムラインをExcelで作成する方法！