テーブル間でリレーションシップを設定してピボットテーブルを作成

Excel2013以降では、テーブル間でリレーションシップを設定して1つのレポートを作成できるようになっています。

以下の記事では、［PowerPivot］タブからデータモデルに追加する方法で解説していますが、ここではExcelのテーブルを2個作成して、共通フィールドを［リレーションシップ］で紐づけして、ピボットテーブルを作成する方法を解説します。

参考[データ］タブの［リレーションシップ］のコマンドが無効（グレイの状態）になっているかもしれませんが、この［リレーションシップ］のボタンはテーブルを2個作成した時点で有効になります。

または、［Power Pivot for Excel］ウィンドウに2個以上のデータモデルがある場合に有効になります。

ここでは［PowerPivot］タブを表示させなくても操作する方法です。Excel2016で解説します。

表（リスト）の確認

以下のような2つの表（リスト）があります。売上一覧の［商品ID］と商品一覧の［商品ID］は共通フィールドです。

［売上一覧］と［商品一覧］の商品IDの紐づけができれば、商品名と数量を並べて表示できるようになります。

こういう表を見ると、VLOOKUP関数を使って表示することをすぐ発想されると思いますが、ここではVLOOKUP関数は使わずに、リレーションシップを設定してピボットテーブルで作成します。

ちなみにVLOOKUP関数を使うと、以下のようになります。

VLOOKUP関数については、以下の記事で解説しています。

VLOOKUP関数

テーブルの作成と名前付け

まず、リレーションシップの機能を使用するには、テーブルに変換します。

［売上一覧］と［商品一覧］の表をテーブルに変換します。

［売上一覧］の表内のセルをクリックして、［挿入］タブの［テーブル］グループにある［テーブル］をクリックします。

自動的に範囲が選択されて［テーブルの作成］ダイアログボックスが表示されます。

範囲が間違いないことと、［先頭行をテーブルの見出しとして使用する］のチェックボックスがオンになっていることを確認して［OK］ボタンをクリックします。

リボンが［テーブルツール］になり、自動でスタイルも設定されます。

テーブル名を入力

［デザイン］タブの［プロパティ］グループにある［テーブル名］のテキストボックスにテーブル名を入力します。

自動で［テーブル1］と入力されていると思いますが、分かりやすい名前を付けておきます。ここでは、［売上］というテーブル名にします。

同様に［商品一覧］の表もテーブルに変換します。分かりやすくするために、商品一覧のスタイルは変更しておきます。

商品一覧のテーブルに名前を付けます。ここでは、［商品］という名前にします。

これで、［売上］と［商品］というテーブルができました。この２つのテーブルの［商品ID］フィールドをリレーションシップで紐づけします。

テーブルについては、以下の記事で解説しています。テーブルに変換するメリットも列記しています。

また、テーブルに変換できないという場合は、以下の記事を参考にしてください。

［データ］タブの［リレーションシップ］

テーブルが2個以上になると、［データ］タブの［データツール］グループにある［リレーションシップ］のボタンが有効になって使用できるようになります。

ポップヒントには、以下のように記されています。

［データ］タブの［リレーションシップ］をクリックします。

［リレーションシップの管理］ダイアログボックスが表示されます。［新規作成］をクリックします。

［リレーションシップの作成］ダイアログボックスが表示されます。ここでリレーションシップの設定をします。

テーブルに［売上］、関連テーブルに［商品］を指定します。［列］と［関連列］はどちらも［商品ID］を指定します。

Accessに親しまれた方なら難なく設定できるかもしれませんが、［テーブル］と［関連テーブル］があると、どっちのテーブルを選択すればいいか悩まれるかもしれません。

主にデータを追加していく方のテーブル（変化が大きい）を［テーブル］にして、ある程度固定されているようなテーブル（変化が少なく、共通フィールドで重複がない）を［関連テーブル］に指定します。

テーブルやフィールドは、プルダウンメニューから選択できます。［OK］ボタンをクリックします。

［リレーションシップの管理］ダイアログボックスは、以下のようになります。［閉じる］ボタンで閉じます。

参考Accessのリレーションシップについては、以下の記事で解説しています。

データモデルの管理でリレーションシップの確認

データモデルでリレーションシップの確認をしなくてもピボットテーブルは作成できますが、データモデルがどうなっているかだけ簡単に見てみるといいと思います。

Excel2016の場合は、［PowerPivot］タブを表示していなくても［データモデルの管理］でデータモデルを確認できます。

Excel2013の場合は、［POWERPIVOT］タブを表示させて確認します。

POWERPIVOTタブを表示させよう

［データ］タブの［データツール］グループにある［データモデルの管理］をクリックします。

ポップヒントには以下のように記されています。

新しいウィンドウ［Power Pivot for Excel］が表示されます。シート名には、2つのテーブルの名前が表示されています。

ウィンドウの右上の［表示］グループにある［ダイアグラムビュー］か、右下の［ダイアグラム］ボタンをクリックします。

ダイアグラムビュー

ダイアグラムビューを表示すると、［売上］テーブルの［商品ID］と［商品］テーブルの［商品iD］が紐づけられているのが分かると思います。

結合線（接続線）をポイントすると、リレーションされているフィールドが枠で囲まれるので分かりやすくなります。

［表示］グループの［データビュー］か、ウィンドウの右下にある［グリッド］をクリックすると、元の表示に戻ります。

ここでは、確認だけなので、［Power Pivot for Excel］ウィンドウの［閉じる］ボタンをクリックして閉じておきます。

ピボットテーブルの作成

［売上］と［商品］というテーブルを作成して、［商品ID］でリレーションシップの設定をしました。

新規にピボットテーブルを作成します。

［ピボットテーブルの作成］ダイアログボックスが表示されます。

［このブックのデータモデルを使用する］を選択して、ピボットテーブルを配置する場所を指定します。ここでは、既存シート［Sheet2!$A$1］を指定します。

追記バージョンアップにより、以下のように変更になっています。Excel2016、2019、2021も同じです。

［挿入］タブの［ピボットテーブル］の下方をクリックして、［データモデルから］を選択します。

［データモデルからのピボットテーブル］ダイアログボックスが表示されます。

参考Excel2013には、［ピボットテーブルの作成］ダイアログボックスに［このブックのデータモデルを使用する］は選択肢としてありません。

［外部データソースを使用］から接続の選択をクリックして、［テーブル］タブをクリックすると［このブックのデータモデル］があります。選択して、［開く］ボタンをクリックします。

ピボットテーブルを作成する準備が整いました。

ピボットテーブルのフィールドのレイアウト

［商品］テーブルの［商品名］フィールドを［行］ボックスへ、［売上］テーブルの［数量］フィールドを［値］ボックスへレイアウトすると、商品名と数量を並べて表示できるようになります。

この後は、ピボットテーブルの操作に慣れているならば、自由に求めたいデータをレイアウトできますね。

上記のピボットテーブルのレイアウトは、［レポートレイアウト］から［表形式で表示］を選択しています。

Access2013では、ピボットテーブルが廃止になりました。ピボットテーブルを使ったビッグデータの分析は、Excelで行うという流れのようです。

リレーションシップは、自動で検出される場合があります。この場合も［リレーションシップの作成］ダイアログボックスが表示されます。

以下はリレーションシップに関する記事です。Excel2010でもPowerPivotを使用すると、リレーションシップを設定できます。

［Power Pivot for Excel］からピボットテーブルを作成する方法は、以下の記事で解説しています。

計算フィールドを追加して売上合計を求めるには

ピボットテーブルで［単価］と［数量］から売上合計を求めることもできます。

計算フィールドはデータモデルから行います。

［データ］タブの［データツール］グループにある［データモデルの管理］をクリック［Power Pivot for Excel］ウィンドウを表示します。

［列の追加］をクリックして数式バーに数式［=[定価]*[合計/数量]］と入力します。

数式バーに［ =[ ］まで入力すると、補助メニューが表示されるので、その中から選択すると入力しやすいです。

以下のようなピボットテーブルを作成できます。

参考上記のピボットテーブルでは、［Calculated Column 1］は、数式バーで名前を［売上合計］に変更しています。

データモデルでの計算フィールドについては、以下の記事でも解説しています。

Power Queryを使って、共通項目でファイルを結合して取り込むこともできます。ここの記事と同じデータを使って解説しています。