Google検索

Windows10 & Office2016 Excel2016

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

投稿日:2019年3月20日 更新日:

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

リレーションシップ

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

Excel2013ではリレーションシップを使おう

Excel2013では、テーブル同士の関連づけが簡単にできます。 VLOOKUP関数を使うより簡単だと思います。 参考[POWERPIVOT ...

続きを見る

参考[データ]タブの[リレーションシップ]のコマンドが無効(グレイの状態)になっているかもしれませんが、この[リレーションシップ]のボタンはテーブルを2個作成した時点で有効になります。または、[Power Pivot for Excel]ウィンドウに2個以上のデータモデルがある場合に有効になります。

リレーションシップが無効

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

表(リスト)の確認

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

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

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

2つのリスト

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

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

VLOOKUP関数

VLOOKUP関数

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

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

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

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

[挿入]タブの[テーブル]

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

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

[テーブルの作成]ダイアログボックス

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

テーブル名を入力

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

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

テーブルの名前付け

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

テーブルのスタイル変更

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

テーブルの名前付け

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

2つのテーブルの確認

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

テーブルの便利機能(テーブルに変換して効率アップ)

Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...

続きを見る

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

テーブルやピボットテーブルでうまくいかない場合の見直し点

Excelのテーブルやピボットテーブルは、データベースとしての管理や分析が簡単にできるようになる、とても便利な機能です。 でも、既存の表をテ ...

続きを見る

[データ]タブの[リレーションシップ]

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

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

テーブル間のリレーションシップを作成または編集すると、テーブル間の関連データが1つのレポートに表示されます。

[データ]タブの[リレーションシップ]

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

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

[リレーションシップの管理]ダイアログボックス

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

[リレーションシップの作成]ダイアログボックス

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

Accessに親しまれた方なら難なく設定できるかもしれませんが、[テーブル]と[関連テーブル]があると、どっちのテーブルを選択すればいいか悩まれるかもしれません。主にデータを追加していく方のテーブル(変化が大きい)を[テーブル]にして、ある程度固定されているようなテーブル(変化が少なく、共通フィールドで重複がない)を[関連テーブル]に指定します。

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

[リレーションシップの作成]ダイアログボックスで設定

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

[リレーションシップの管理]ダイアログボックス

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

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

Excel2016の場合は、[PowerPivot]タブを表示していなくても[データモデルの管理]でデータモデルを確認することができます。

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

POWERPIVOTタブを表示させよう

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

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

PowerPivotウィンドウに移動

データを追加して準備するか、このブックに既にあるデータの操作を続けます。

[データ]タブの[データモデルの管理]

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

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

[Power Pivot for Excel]ウィンドウ

ダイアグラムビュー

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

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

ダイアグラムビュー

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

データビュー

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

ピボットテーブルの作成

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

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

ピボットテーブル作成ボタン

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

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

[ピボットテーブルの作成]ダイアログボックス

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

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

Excel2013の既存の接続

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

ピボットテーブル

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

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

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

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

ピボットテーブルのレイアウト変更

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

レポートのレイアウト[表形式で表示]

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

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

PowerPivot2013のリレーションシップ

Excel2013のPowerPivotで、以下の操作でExcelのデータをインポートしました。 Excel2013のPowerPivotで ...

続きを見る

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

PowerPivotのリレーションシップ

PowerPivotでは、複数のテーブルに共通フィールドがあるとリレーションシップが自動的に検出されます。 Excel2010のPowerP ...

続きを見る

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

データモデルのデータを使ってピボットテーブルを作成する方法

Excel2013以降にはPowerPivotというビッグデータを分析できるアドイン機能が標準で搭載されています。 ただし、既定では有効にな ...

続きを見る

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

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

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

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

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

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

数式を入力

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

ピボットテーブルに[売上合計]フィールドを追加

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

数式バーで名前を変更

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

POWERPIVOT2013の計算フィールド

Excel2013のPowerPivotでは、[計算フィールド]というボタンが追加されました。 Excel2010のPowerPivotでは ...

続きを見る

2つのテーブルから計算フィールドを作成

Excelでデータモデルに追加したテーブルを使って計算フィールドを作成する方法です。 3つのデータの確認 作成した3つの表があります。 下は ...

続きを見る

Profile

執筆者/はま

おすすめ記事と広告

-Windows10 & Office2016 Excel2016

Copyright © 2001-2019 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2019 AllRights Reserved Powered by AFFINGER4.