Windows 10 & Office 2016 Excel 2016

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

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

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

ダイアグラムビューのリレーション

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

リレーションシップを表示(ダイアグラムビューで共通フィールドを関連付け)

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]ボタンをクリックします。

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

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

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

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

リレーションシップの作成と参照整合性の設定

Accessでは、複数のテーブルがあって、そのテーブル間の共通フィールドを関連付けることができます。この関連付けのことを[リレーションシップ ...

続きを見る

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

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

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]を指定します。

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

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

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

[挿入]タブの[ピボットテーブル]-[データモデルから]

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

[データモデルからのピボットテーブル]ダイアログボックス

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

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

Excel2013の既存の接続

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

ピボットテーブル

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

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

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

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

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

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

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

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

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

PowerPivot2013のリレーションシップ(自動検出と作成)

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

続きを見る

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

PowerPivot2010のリレーションシップ

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

続きを見る

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

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

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

続きを見る

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

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

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

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

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

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

数式を入力

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

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

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

数式バーで名前を変更

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

PowerPivot2013の計算フィールド

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

続きを見る

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

Excelでデータモデルに追加したテーブルを使って計算フィールドを作成する方法です。 ここでは、3つのデータをデータモデルに追加してピボット ...

続きを見る

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

Power Queryで2つのデータの共通項目を関連付け(マージ)して取り込む

Power Queryでは、2つのデータの共通項目を関連付けして、1つのテーブルを作成することができます。 Excel2013以降であれば、 ...

続きを見る

Power Queryを使って共通項目でファイルを結合

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

created by Rinker
★★★★★ 分かりやすい!Excelの基本はこれでOK♪
Amazonのレビューを見る

Profile

執筆者/はま

-Windows 10 & Office 2016 Excel 2016

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

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