Excel。Accessのクエリのように、3つのテーブルからピボットテーブルとPower Pivotで表を作る
＜Power Pivot＞
Excel2013からピボットテーブルにリレーションシップの機能が追加されました。Accessを使わなくても、複数のテーブルからAccessのクエリのように表を作れるようになっています。VLOOKUP関数を多用するのも悪くありませんが、複数テーブルからでも、表が作れますので確認しておきましょう。
【テーブルにしてテーブル名を設定しておく】
今回は3つのテーブルを用意しました。Excelの表をそのまま使用することができないので、テーブルにして、テーブル名を設定しておきます。
最初は、売上リストという売上データの表です。
売上コード・販売日・店舗コード・商品コード・販売数
というフィールドで構成されています。
2つ目のテーブルは、店舗リストという店舗名の表です。
店舗コード・店舗名・座席数
というフィールドで構成されています。
最後は、商品リストという商品管理の表です。
NO・商品コード・商品名・商品分類・金額
というフィールドで構成されています。
【売上リストを使える表にしたい】
テーブル：売上リストですが、店舗コード渡河はありますが、店舗名がありません。商品名もないのでわかりにくく、販売数はわかるものの、販売金額も算出されていません。そこで、それらを表示した表を別のシートにつくっていきます。
基軸となる売上リストの中のセルをアクティブにして、ピボットテーブルを挿入していきます。
ピボットテーブルの作成ダイアログボックスで、「このデータをデータモデルに追加する」にチェックマークをいれるのを忘れないようにしましょう。ここがポイントです。
OKボタンをクリックすると、ピボットテーブルを作成するための新しいシートが挿入されます。
ピボットテーブルのフィールド作業ウインドウが表示されますので、「アクティブ」から「すべて」に切り替えます
こうすることで、このブック上にある、テーブルを使うことができるようになります。
【レイアウトは無視してフィールドを設定する】
Accessのクエリを作ったことがあれば、イメージしやすいと思いますが、必要なフィールドをピックアップしていきます。なお、ピボットテーブルのレイアウトがおかしくなりますが、気にせず、設定していきます。
売上リストから「売上コード」を設定しますが、値フィールドに入ってしまうので、行フィールドに移動させます。
販売日を設定すると、列フィールドに挿入されてしまうので、行フィールドの「売上コード」の下に移動させます。
店舗コードを設定すると、行フィールドに挿入されます。
店舗名を表示したいわけですが、テーブルを変更する必要がありますので、店舗リストから「店舗名」を設定します。
売上リストの「商品コード」を設定します。
商品名を表示したいので、商品リストにある「商品名」を設定します。
今のところ、設定したフィールドは、行フィールドに挿入されているのが確認できます。ピボットテーブルのレイアウトは気になりますが、気にしないようにします。
商品の金額がないと、金額×数量で販売金額を算出することができませんので、商品リストにある「金額」を設定します。
「テーブル間のリレーションシップが必要である可能性があります。」と表示されてきたら、「自動検出」ボタンをクリックして、リレーションシップを作成します。
事前に作成しておいてもいいですし、自分で作成してもいいのですが、自動検出が便利です。
リレーションシップの管理ダイアログボックスで確認しておきます。
値ボックスにある「合計/金額」フィールドを行ボックスの一番下に移動します。
【レイアウトを変更する】
「レポートのレイアウト」から「表形式で表示」をクリックします。
レイアウトが変更されますので、列幅を調整します。
「合計/販売数」というフィールド名はわかりにくいので、「販売件数」に変更します。「販売数」にしたいのですが、すでに使用されているフィールド名なので、重複することができません。
【PowerPivotを使って販売金額を算出】
I列に金額×販売件数とすれば、販売金額を算出することができますが、ピボットテーブルの外側になってしまいます。そこで、『PowerPivot』を使うことで販売金額のフィールドを作成し、作成したフィールドをピボットテーブルに挿入していきます。
PowerPivotタブの管理をクリックして、PowerPivotを起動させます。
列の追加に、数式を設定します。
='売上リスト'[販売数]*RELATED('商品リスト'[金額])
と設定すると、販売金額が算出できますので、見出しを「販売金額」に変更します。
変更したら、PowerPivotを閉じます。
再び、ピボットテーブルのシートに戻って、ピボットテーブルのフィールドを確認すると、売上リストが更新されて、先程作成した「販売金額」が表示されているので、設定しましょう。
これで、完成しました。
