エクセルチュートリアル①Power Queryを利用した簡単なデータ分析　その２ PowerPivotを利用した集計

エクセルチュートリアル①その2です

エクセルチュートリアル①その2では前回読み込んだファイルをPowerPivotを利用して、リレーション（データを関連付けること）を作成した上で、ピポットテーブルで集計する方法について扱います具体的には下記を取り扱います。
・その１で読み込んだ複数のテーブルにリレーションシップを作成して分析をしやすくする
・ピポットテーブルでデータを集計する
・スライサーというフィルタ機能でデータをフィルタする
・特定の商品群で集計⇒個別の商品で集計というように粒度を変えながら集計を行う
・データバーを利用してデータの可視化を行う

前回の振り返り

ファミレスの売上減少理由解明のために、商品マスタと店舗マスタなどの、マスタ情報を読み込み、複数月の売上ファイル（トランザクションのデータ）をマージしたファイルを作成した。

4.ピポットテーブルで集計する

必要なデータはそろったのでピポットテーブルで売上の集計を行います。先ほど作成した売上データのテーブル上のセルを選択してAlt+N+V（ピポットテーブルのショートカット）を入力しOKを押します。

するとピポットテーブルを作成する画面がたちが上がります。

このままデータを集計してもよいのですが、以前述べたように生データのままでは分析がしにくいです。分析用に3つのテーブル(売上データ、商品マスタ、店舗マスタ)を作成・読み込んでいるので、これらのテーブルにリレーションシップを作成し必要な情報を参照できるようにしましょう。ピポットテーブル分析タブ＞計算方法＞リレーションシップをクリックします。するとリレーションシップの管理というウインドウが表示されるので、新規作成をクリックします。

テーブル欄に「分析対象」（売上のマージファイル）列（外部）に「商品コード」、関連テーブルに「商品マスタ」、関連列に「商品コード」を設定してOKをクリックします。

二つのテーブルにリレーションが作成されました。

同様にマージファイルと店舗マスタにもリレーションを作成します。

Note:リレーションシップ
リレーションシップとは簡単にいうとある表と別の表を特定のキー列を目印に関連付けることです。例えば今回でいうと、商品コードはただのアルファベットと数字からなる文字列でしかないので、これで分析を進めていっても「お、C0008の商品が売れているな......C0008ってどの商品だっけ？」という事態になりかねません。リレーションシップを設定することで、ピポットテーブル作成時に、C0008の属性情報を簡単に参照できるようになり、商品名や商品カテゴリなどを素早く分析の軸に取り込むことができるのです。

多くの方がC0008が紅茶だと識別するために売上データのテーブルにvlookupを用いて商品マスタから商品名の列を持ってくるという操作を行っていると思います。この方法では、
・キーとなるコードが一番左にある必要があるので、データの前処理が必要になることがある(この点はxlookupで改善されるそうですが)
・ほしい情報を取得するために複数列vlookupをする必要がある（めんどい）
・パフォーマンスが良くない(大量の行を処理させると処理完了に時間がかかってストレス)
・参照先のテーブルレイアウトが変更するたびに関数を組みなおす必要がある
といったデメリットがあります。おもに3番目の理由のため大量レコードに対するvlookupはあまりお勧めできません。特に今回はレコード数が12万と量が多いので2列も3列もvlookupをしてたら大分しんどくなります。

ピポットテーブルでデータを集計する
リレーションシップの作成が完了したら、先ほど作成したピポットテーブルのシートに移り、ピポットテーブルフィールドのその他のテーブルをクリックして、新たにピポットテーブルを作成します。

するとピポットテーブルにこれまで読み込んだテーブルが表示されるので、集計したい項目を突っ込んで集計を行います。

なんの項目を集計に利用すべきでしょうか？売上の減少がいつから始まったのかを確認するために、日付（日付列ではなく日付型を設定したdate列を利用しますが）は必須そうです。あとは売上が特定の店舗で減少しているのか、それとも特定の商品で減少しているのかを見るためにそれぞれの項目で集計を行ったほうがよさそうですね。

まずは店舗ごとの売上がどうなっているかを確認します。ピポットテーブルフィールドの行欄に分析対象から「date」を、列欄には店舗マスタから「店舗名」を、値欄には分析対象から「売上額」をドラック&ドロップします。

日単位だと傾向が見えにくいので集計を月単位に変えます。ピポットテーブルの行ラベル列のデータを一つ選択し、右クリック＞グループ化をクリックします。

するとグループ化ウインドウが現われるので、月を選択してOKをクリックします。

月単位で売上額がまとめられました。

数字だけでは変化が見にくいのでちょっとだけグラフィカルに見てみましょう。A店の4月セルからF店の7月セルを選択した上で、ホームタブ＞条件付き書式＞データバー＞好きな色・パターン（今回は単色の黄色を選びました。）をクリックします。するとA店とE店の売上が6月、7月で下がっていることが分かりました。他の店舗の売上は多少の増減があるものの大きく減少はしていないようです。

なぜA店とE店の売上だけが下がっているのでしょうか。もし特定の商品の売上が下がっているなら、すべての店舗で売上の減少が見られるはずです。そうではないということは、A店とE店の店舗属性に原因がありそうだと考えられます。ですので一度店舗毎ではなく、店舗の立地を表す店舗区分毎で売上をまとめ治して見てみます。
ピポットテーブルのフィールの列欄に店舗マスタから店舗区分をドラックし、店舗名の上にドロップします。すると、これまでの店舗名の上に店舗区分が追加されました。

A店E店は「ロードサイド」であり、この店舗区分のお店の売上が下がっていると言えそうです。念のため店舗区分でまとめて売上の変化を見てみます。下記のように店舗区分・店舗名の列をすべて選択して、右クリック＞展開/折りたたみ＞フィールド全体の折りたたみをクリックします

店舗区分毎に売上額が集計されました。同じようにデータバーを設定して値の大少を見てみます。(今回は青の単色のデータバーを設定しました。)するとロードサイド店の売上減少が顕著であることが確認されました。今後の分析はロードサイド点に絞って行えばよさそうです。

ではなぜロードサイド店の売上が下がっているのでしょうか。もしかしたら特定の商品群の売上が落ちているのかもしれません。ロードサイド店の商品群毎の売上を見てみましょう。
ピポットテーブルフィールドの列欄にある店舗区分と店舗名を外し、商品マスタから、商品区分と商品名を列欄にドラック&ドロップしてデータバーを設定します。

抽出対象をロードサイド点に絞るためにスライサーを設定します。(フィルタを利用してもよいのですがこちらのほうがより直感的にポチポチ分析できるので)ピポットテーブル分析タブ＞フィルター＞スライサーの挿入をクリックします。スライサーの挿入ウインドウがあられるので店舗マスタの店舗区分にチェックしてOKをクリックします。

スライサーが現れますので、ロードサイドをクリックします。

（複数選択したい場合はShift押しながら対象をクリックします）。すると該当データのうちロードサイドに紐づくデータのみが集計されます。

先ほどと同様に、商品区分だけで売上の推移を見てみます。どうやらカフェ類の売上が下がっているのが原因のようです。

カフェ類の商品のうち特定の商品の売上が下がっているのでしょうか。それともすべての商品の売上が下がっているのでしょうか。カフェ類の各商品の売上を見みるために、カフェの左の+マークをクリックし、各商品ごとの売上の推移を確認してみます。どうやら下がり方は違えど、どの商品も売上が下がっているような印象を受けます。

以上から、どうやら売上が減少したことの原因はロードサイド店のカフェ類のメニューの売上が減少したからと言えそうです。実際の業務ではこれから更になぜロードサイド店のカフェ類のみ売上が落ちているか深堀を行っていく必要があります。適当に考えるだけでもいつかの原因が考えられそうです。例えば
・新しく進出したカフェチェーンにこれまでカフェメニューを利用していた顧客が奪われたのでは（顧客の流出）⇒カフェチェーンが出店した時期とその繁盛具合を確認
・ロードサイド店が立地している道路で工事が行われており、これまでカフェを利用していたお客さんが物理的にお店に来れなくなったのでは？（顧客層の減少）⇒そのような工事があるのか確認。カフェ利用の顧客は車での来店が多かったのかを確認

などの可能性が考えられますが、今回のデータからはこれ以上の深堀は難しそうです。

いかがでしたか。すごく簡単に、かつ機能を絞っての紹介となりましたがマクロとかを使わなくても十分にエクセルだけでいろんなことができることが分かっていただけと思います。今回紹介したPowerQueryを利用した方法は
・ノンプログラミングで今までマクロなどのプログラミングが必要なことが実現できる
・一度設定してしまえば使いまわしが容易
・どのような編集が行っているかM言語を見ればパッとわかるのでメンテナンスがしやすい
という強力な利点を持っています。PowerQueryをはじめ最近のOffice製品には普段の業務を効率化するあれやこれやがたくさんあるのでそれらを積極的に紹介してもらいたいと思います。本記事が皆さんの日々の業務に役に立てれば幸いです。

次回以降の記事では下記について紹介したいなと思います。
・Sharepoint上にあるファイルの読み込み方
・エクセルブックの読み込み方
・時系列グラフの作成方法
・グラフの結合
・ちょっと高度なピポットテーブルを利用したダッシュボード作成

それではまた次回。