Excel。ピボットテーブルでAccessのクエリのように一つの表を作ってみる
＜ピボットテーブル＞
完成した表は、次のようなピボットテーブルの表です。
この表を作った元になるデータは次の表です。
大きな売上データなので、C列の旅行番号はあるのですが、旅行番号に該当する旅行名は、別の表で管理しています。
その表が、
で、この2つの表を使って、旅行先別の2019年の参加人数の合計とその合計金額の表を作成していきます。
旅行名などVLOOKUP関数を使ったりして追加してもいいのですが、最終的に『集計』しなければいけないので、Accessだったらクエリと演算フィールドを作ると簡単に作れますが、今回はピボットテーブルをつかってみようと思います。
【テーブルにしてテーブル名を設定】
あとで関わることなのですが、まず2つの表をそれぞれテーブルに設定して、テーブル名を設定していきます。
売上管理の表は、『T売上』というテーブル名を設定します。
もう一つの旅行リストは、『T旅行』というテーブル名を設定しました。
T売上テーブルをクリックして、挿入タブからピボットテーブルをクリックします。
複数のテーブルを分析するかどうかの「このデータをデータモデルに追加する」にチェックマークをつけて、OKボタンをクリックしましょう。
右側に表示された、ピボットテーブルのフィールドを「アクティブ」から「すべて」に変更すると、テーブルにしたすべてのリストが表示されます。
テーブルにしていないと、複数のテーブルを使うことができませんので、テーブルにしました。
【日付を年としてフィルターで使う】
日付フィールドをそのままフィルターのボックスにいれてしまうと、”日”でフィルターを設定することができるのですが、”年”にしたい場合、チェックマークをはずすのが面倒です。
なので、一度チェックを外してから、列か行フィールドのボックスにいれます。
年・四半期・月・日付でそれぞれグループ化されますので、「日付（年）」をフィルターのボックスに移動して、他のグループのチェックは外します。
2019年のみにチェックマークをつけておきます。
行のボックスに、
T旅行の「旅行番号」・「旅行名」・「金額」の順番で設定します。
このレイアウトでは、わかりにくいので、ピボットテーブルツールのデザインタブにある「レポートのレイアウト」から「表形式で表示」をクリックします。
レイアウトが表形式に変わったことが確認できました。
そして、いよいよ、T売上テーブルの人数フィールドを値ボックスに設定します。
すると、「テーブル間のリレーションシップが必要である可能性があります。」とメッセージが表示されますので、作成ボタンをクリックします。
リレーションシップの作成ダイアログボックスが表示されます。
【Excel2013からリレーションシップが登場】
2つの表には幸い旅行番号という共通した列名が存在しています。そこで、Excel2013からAccessのようにリレーションシップ機能が追加されました。
リレーションシップを設定することで、複数テーブルのフィールドを使うことが可能になります。
このリレーションシップもテーブルにしておく必要があります。
テーブルのボックスには、T売上を設定します。
このテーブルは、”多”（何度も旅行番号が登場するテーブル）を設定します。
関連テーブルのボックスには、T旅行を設定します。
このテーブルは、”一”（テーブル内に重複していない）というのがポイントになります。
OKボタンをクリックします。
ピボットテーブルは、次のようになりました。
折角、金額と人数のフィールドがありますので、合計金額を算出してみましょう。
Accessのように演算フィールドを作れませんので、E4に=C4*D4と入力します。
セルをクリックして数式を作ってしまうと、GETPIVOTDATA関数で数式を作ってしまって、オートフィルで数式をコピーするのが大変になってしまいますので、注意が必要です。
あとは、総計をオートSUMボタンで算出して、列幅や、書式を整えて完成ですね。
