ピボットテーブルは同時に複数の表を集計することができます
共通の項目を持つ「複数の表」を１つのピポットテーブルとして扱うことができます。
- ピボットテーブルを使えば、単純な表から様々な分析をすることができます
- 共通の項目を使って、複数の表を関連付ける
- 複数の表を「連携」させることが「リレーション」です。
- 内容別に「４つ」の表（テーブル）を作成
- 複数の表（テーブル）に分けるメリット
- それぞれの表を「テーブル化」します
- テーブルに「名前」を付けます
- 「リレーションシップ」で共通のフィールドを関連付ける
- 「テーブル」と「関連テーブル」の意味
- 複数テーブルから「ピボットテーブル」を作成
- 「商品名」と「支社名」をクロス集計した売上金額の集計表を作成します
ピボットテーブルを使えば、単純な表から様々な分析をすることができます
今回は「複数の表」から１つのピボットテーブルを作成して集計をします。
この機能は「リレーションシップ」と呼ばれ、Access等のデータベースを使っている方にはなじみがあると思います。
ExcelではExcel2013のバージョンから「リレーションシップの設定」という機能が新たに追加され複数の表を関連付けて１つにまとめて扱うことが簡単にできるようになりました。
共通の項目を使って、複数の表を関連付ける
例えば「顧客の所在地別」に売上状況分析をする場合を想定します。
「売上明細表」には「所在地のデータ」はない
１つの表に多くの項目（フィールド）を盛り込むと表が大きくなりすぎ作業効率が悪くなってしまいます。
そのため通常、「顧客データ」、「商品マスター」のように複数の表に分けて作成し、VLOOKUP関数などで参照・連携させていると思います。
複数の表を「連携」させることが「リレーション」です。
「複数の表を連携」させて集計・分析させることで、もとの表に項目（フィールド）を加える等、手を加える必要がありません。
分析のために「新たな項目を加えたい」ときも、必要な要素だけの「別の表」を作成するだけですみます。
内容別に「４つ」の表（テーブル）を作成
内容別に４つの表（テーブル）を集計・分析します。
- 「顧客」➡ 顧客の情報を管理するテーブル ➡ 「どの顧客が」
- 「商品」➡ 商品の情報を管理するテーブル ➡ 「何の商品を」
- 「注文」➡ 注文日、販売先など注文の概要を管理するテーブル ➡ 「何月何日に」
- 「明細」➡ 注文された商品の商品名、数量、金額などの明細を管理するテーブル ➡ 「何個購入したのか」
複数の表（テーブル）に分けるメリット
-
新たな項目（フィールド）を追加する時に、既存の表に手を加えることなく「別の表」を作るだけでよい
-
表がコンパクトになり、入力作業が簡単になる
-
VLOOKUP関数等で参照形式にしておくことで、「顧客名」や「商品の単価」といった共通の要素が変更された場合に、参照テーブルを変更するだけで良い。
関連性のある「共通の項目」を持った、４つの表（テーブル）
「顧客」テーブル
「商品」テーブル
「注文」テーブル
「明細」テーブル
それぞれの表を「テーブル化」します
リレーションシップを設定するには、表が「テーブル化」されている必要があります。
- リボンの「挿入」タブを選択します
- テーブル化したい表の「どこか１カ所」を選択します
- 「テーブル」をクリックします
- 「表」が正しく選択されていることを確認
- 「テーブルの作成」ボックスで「OK」をクリック
テーブルに「名前」を付けます
作成した「テーブル」に、分かりやすくするために「名前」を付けます。
顧客のテーブルに、「顧客」の名前を付ける
- テーブル内のどこか１カ所を選択
- 「テーブルデザイン」タブを選択
- テーブル名のボックスに「顧客」と入力
※ その他の表も同様に「テーブル化」して「名前」を付けます。
「リレーションシップ」で共通のフィールドを関連付ける
「顧客テーブル」と「注文テーブル」の２つの表の共通のフィールド「顧客コード」に「リレーションシップ」を設定する。
「リレーションシップ」を作成する
リレーションシップは「データタブ」のリレーションシップで作成していきます。
- 「顧客コード」の表の１カ所を選択
- リボンフィールドの「データ」タブを選択
- リボンフィールドの「リレーションシップ」をクリック
- 「リレーションシップの管理」ボックスの新規作成をクリック
「注文」と「顧客」の共通項目の「顧客コード」でリレーションシップを設定する
リレーションシップのダイアログで、リレーションシップを作成します。
- テーブルのラジオボタン「▼」をクリックして、ボックスから「テーブル：注文」を選択
- 列（外部）のボックスから「顧客コード」を選択
- 関連テーブルのボックスから「テーブル：顧客」を選択
- 関連列（プライマリ）のボックスから「顧客コード」を選択
「注文のテーブル」と「顧客のテーブル」のリレーションシップが作成されました
「明細」と「商品」の共通項目の「商品コード」でリレーションシップを設定する
- テーブルのラジオボタン「▼」をクリックして、ボックスから「テーブル：明細」を選択
- 列（外部）のボックスから「商品コード」を選択
- 関連テーブルのボックスから「テーブル：商品」を選択
- 関連列（プライマリ）のボックスから「商品コード」を選択
「注文」と「明細」の共通項目の「注文コード」でリレーションシップを設定する
- テーブルのラジオボタン「▼」をクリックして、ボックスから「テーブル：注文」を選択
- 列（外部）のボックスから「注文コード」を選択
- 関連テーブルのボックスから「テーブル：明細」を選択
- 関連列（プライマリ）のボックスから「注文コード」を選択
４つのテーブルが「３つのリレーションシップ」により関連付けされました
「注文」、「顧客」、「明細」、「商品」の４つのテーブルが３つのリレーションシップで関連付けされました。
- 「注文」と「顧客」のリレーションシップ
- 「明細」と「商品」のリレーションシップ
- 「注文」と「明細」のリレーションシップ
「テーブル」と「関連テーブル」の意味
主にしたいテーブルを「テーブル」に配置し、関連付けたいテーブルを「関連テーブル」に配置します。
- テーブル ➡ 「主」となるテーブル
- 関連テーブル ➡ 「主テーブルに関連付ける」テーブル（リレーション）
※ 「テーブル」と「関連テーブル」のテーブル名が入れ替わっていても、Excelが自動判断してくれます。
複数テーブルから「ピボットテーブル」を作成
複数のテーブルを関連付けて「ピボットテーブル」を作成していきます
- テーブル内の「どこか１カ所」を選択
- リボンフィールドの「挿入」を選択
- 「ピボットテーブル」をクリック
- ピボットテーブルの作成ボックスで、「複数のテーブルを分析するかどうかを選択」で「このデータをデータモデルに追加する」にチェックを入れる
ピボットテーブルのフィールドで「すべて」を選択すれば、４つのテーブルが表示される
ピボットテーブルのフィールドの「すべて」のタブを選択すると、リレーションシップが設定されている「４つのテーブル」が表示され、選択可能になります。
テーブル名の横の「印」をクリックすると、フィールド（項目）が表示
テーブル名の横にある「印」をクリックすると、テーブルのフィールド（項目）が表示されて選択可能になります。
「商品名」と「支社名」をクロス集計した売上金額の集計表を作成します
リレーションシップが設定されている、３つのテーブルから必要なフィールドをドラッグしてピボットテーブルを作成します。
３つのテーブルから、それぞれ必要なフィールド（項目）をドラッグ
- 「顧客」テーブルから、列のボックスにに「支社名」をドラッグ
- 「商品」テーブルから、行のボックスに「商品名」をドラッグ
- 「注文」テーブルから、∑ 値のボックスに「金額」をドラッグ
新たな条件で分類する時に、必要なフィールド（項目）を含むテーブルを新たに作成し「リレーションシップ」を設定すれば元の表（データ）に手を加えずに集計することができます。
リレーションシップの機能を使わない場合は、一つの表に分析（分類）したい項目（フィールド）をすべて盛り込む必要があり、「扱いづらい表」になったり、時にはデータの打ち直しの必要が生じる場合もあります。
リレーションシップの機能を使えば、「VLOOKUP関数等で「参照」されている複数の表」を加工することなく分析（集計）することができます。
※ その２では、操作が複雑でわかりにくい「関連付け」簡単にできる、「PowerPivotを使って関連付けをする方法」を説明します。
- ピボットテーブルを使えば、単純な表から様々な分析をすることができます
- 共通の項目を使って、複数の表を関連付ける
- 複数の表を「連携」させることが「リレーション」です。
- 内容別に「４つ」の表（テーブル）を作成
- 複数の表（テーブル）に分けるメリット
- それぞれの表を「テーブル化」します
- テーブルに「名前」を付けます
- 「リレーションシップ」で共通のフィールドを関連付ける
- 「テーブル」と「関連テーブル」の意味
- 複数テーブルから「ピボットテーブル」を作成
- 「商品名」と「支社名」をクロス集計した売上金額の集計表を作成します
