絶対に誰でもわかるPowerQuery(パワークエリ),PowerPivot(パワーピボット)の使い方(2)-PowerQuery：複数データの接続とPowerPivot：テーブルとグラフの自動更新
こんにちは、会計士KOです。
今回は、絶対に誰でも分かるPowerQuery,PowerPivotに関する記事の第2回を更新していきたいと思います。
前回はPowerQueryの基本的な使い方を説明しました。PowerQueryを使えば、データの取り込みから加工がかなり楽になったかと思います。
特に、基礎データを毎月、定常的に加工するなどの作業はPowerQueryで一瞬で解決できますね。
今回は、これに加えて、PowerQueryで複数のデータを連携させる方法とPowerPivotを使って一瞬で必要なテーブルを作成したり、グラフとして視覚化することを目標にしていきます。
なお、この記事はPowerQueryについても復習を兼ねていますが、PowerQueryが分からないとどうにもなりませんので、是非、まずは以下の記事からPowerQueryについて勉強してください。
絶対に誰でもわかるPowerQuery(パワークエリ),PowerPivot(パワーピボット)の使い方(1)-PowerQuery：データの接続と変換
今回の成果物イメージ
まずは、成果物をお見せします。
PowerQueryとPowerPivotを組み合わせれば、読み込むデータを更新するだけで、一瞬でテーブルやグラフを更新することが出来ます。
従来は、基データを手作業で加工して、そして加工したデータをグラフにして、テーブルを組んで、、、といったことが必要になるかと思いますが、
以下のように、データを最新版のものに置き換え、「すべてを更新」を押すだけで、チームごとの売上を示すグラフを一瞬で半期分更新したり、
「すべてを更新」を押すだけで、取引先別の売上推移のテーブルを一瞬で更新したり出来るんです。
これ、やっていることは、
読み込むデータを最新のものに更新し、SDのフォルダに格納して更新
しているだけです。
必要な集計やグラフ化、テーブル化など一度、PowerQueryとPowerPivotをちゃんと組んでしまえば、それ以降はデータを更新し続ければ良いのです。
これがPowerQueryとPowerPivotの使い方です。
取引先別の分析や、取締役会への報告資料など、毎月、グラフやテーブルを作っていらっしゃる方、ぜひ、その作業をPowerQueryとPowerPivotに置き換えましょう。
PowerQueryの使い方
さて、PowerQueryの復習も兼ねて、今回は複数の外部データを取り扱う方法も紹介していきます。
PowerQueryのすごいところの1つは、取り扱えるデータが単一ではないこと。
裏でやっていることの基本はデータベース操作と同じなので、データ間の連携さえ、ちゃんと設定されていれば、いくらでもデータを連携して抽出したり、集計したりすることが出来ます。
複数の外部データに接続する
では、PowerQuery を使って複数の外部データに接続してみましょう。
基礎データの前提
（今回使う基データ：前回と同じ）
取り扱うデータは前回の記事と変わっていませんが、前提の確認です。
青字で前提となっているところを確認してくださいね。
チーム構成という設定の追加
今回はこれに加えて、
担当者ごとに各社横断的なチームに所属している
という設定があったとします。イメージは以下です。
チームごとの成績が良い（＝1人あたりの粗利（＝（売上マイナス原価）/チーム人数合計）が大きいチームには決算賞与が出るので、年度が終了した段階でこれを算出する必要があります。
しかしながら、基データには、チームというデータ列はありません。また、チームの人数もバラバラです。
このため、この計算にはどこかのタイミングで担当者ごとのチーム列を追加する必要があるのですが、かなり手間ですよね。
今回はデータの都合上、人数はかなり少ないですが、これが100人、1,000人となっていくとさらに集計は面倒くさくなります。
この点、PowerQueryを使えば、簡単にそのデータを結合することが出来ます。
以上のチーム構成表を以下にリンクしておきました、今回はこちらを使っていきます。
基データを保存する
まずは、上記のデータをSDデータと同じフォルダに保存して下さい。
このSDというフォルダからすべてのデータを読み込ませていきます。
実行エクセルでチーム構成データを連携する
次に前回の記事で作成した実行エクセルを使って、前回と同様にチーム構成データを取り込みます。
データタブから、データの取得を選んで、ブックからを選びましょう。
データの取り込みウインドウが開いたら、以下のようにSDフォルダに格納したデータを選んでインポートします。
チーム構成.xlsxにはSheet1しかありませんので、該当のシートを選択し、読込を実行します。
読込が完了するかと思うので、クエリと接続という画面に以下の通り、Sheet1が読み込まれたことが分かりました。
しかし、相変わらず要らないデータまで入ってしまうので、このデータを削除していきましょう。
データとクエリを右クリックすると、編集ボタンが出てくるかと思いますので、編集ボタンを押して、以下の通りの画面を選んでください。
不要なデータを削除する
まずは、不要なデータが入っているので、該当のデータを削除しましょう。
エディターウインドウが開いているかと思いますので、ホームタブから行の削除を選び、上位の行の削除を選択してください。
5行分が余計なので、5行分を削除すればよいですね。
しかし、まだヘッダー（見出し）が変ですね。本来は1行目が見出しの役割を果たすべきです。
次に1行目をヘッダーとして使いたいので、
左上のテーブルマークのアイコンを右クリックして1行目をヘッダーとして使用を押してあげましょう。
このデータで、閉じて読み込むを押せば、必要なデータになったんじゃないでしょうか。
ほら、こんな感じですね。必要十分なテーブルになっているかと思います。
クエリとシートの名前を変更する
読み込ませるデータが増え、クエリが多くなると訳が分からなくなってくるので、読み込まれたデータのクエリの名前を変更しておきましょう。
クエリのタブからプロパティを選択して、
クエリ名を変更すれば、
名前が変更できるかと思います。以下のような感じですね。
また、シート名についても変更をしておきましょう。
ALT+H+O+R
を押せば、シート名の変更が出来るので是非覚えておいてくださいね。
↓
PowerQueryの実行データとチーム構成データを連携する
PowerQueryの実行データとチーム構成データを連携していきましょう。
イメージとしては、以下の通りです。
担当者というキーに紐づけてチーム構成シートを参照すれば、その担当者のチームが分かりますよね。
PowerQueryを使えば、このように複数のデータを連携することもできます。
SDデータをデータモデルに取り込む（★絶対必要★）
では、ここまで出来たらいよいよ連携！と行きたいところですが、その前にSDデータをデータモデルに読み込みます。
これを行うことによって、PowerPivotとの連携が出来ることになります。
クエリタブから、読み込み先を選んで、
出てきたウインドウから、このデータをデータモデルに追加するを選びます。
なにやら沢山ウインドウが出てきますが、無視して取り込んでください。
なお、Pivotテーブルを作った後にこの操作をするとせっかく作ったデータが初期化される可能性があるので、ご留意ください。
リレーションシップを作成する
では、いよいよ2つのデータ間を連携させていきましょう。
クエリツールから、以下のようなリレーションシップというボタンを押してください。
そうすると、以下のようなウインドウが開くので、新規作成ボタンを押しましょう。
新規作成を押すと、さらに以下のようなウインドウが開きますので、
テーブルにデータモデルのテーブルであるSDと、連携させる担当者列を選び、
そして、関連テーブルにおいて、チーム構成テーブルと、関連列に担当者列を選択してください。
そうすると以下の通り、リレーションが出来上がります。
イメージとしては、最初に示した以下の通り、２つのテーブルがつながった感じです。
ちょっとした型のデータ修正をしておきます
PowerPivotを使う前に、SDデータに修正したいデータが発見されたので、ここで修正をしておきます。
実はSDデータの粗利のデータ型が数値データではなく、文字列データになっていました。
後程Pivotテーブルの集計をしたいので、ここで数値データに修正をしておきます。
SDデータを開いて、クエリの編集を選び、粗利列を右クリック、型の変更から、整数を選択しておいてください。
PowerQueryはこのようにいつでも事後的な変更が出来ます。
連携が完了したらいよいよ、PowerPivotの領域です。
PowerPivotの使い方
ピボットテーブルを作成し、PowerQueryで作成したデータベースと接続する
では、いよいよピボットテーブルを作成し、PowerQueryで作成したデータベースと接続していきましょう。
ピボットテーブルの挿入
では、まずピボットテーブルを作成します。
挿入タブからピボットテーブルを選びましょう。
こちらをクリックすると、以下の画面が開くかと思いますので、このブックのデータモデルを使用するにチェックを入れてください。
このデータモデルを使用するにチェックを入れることで、ピボットテーブルが常にPowerQuery で作ったデータモデルとデータ連携することとなります。
また、作成時には、新規ワークシートを作成するにチェックを入れてください。
必要であれば、既存のシートから挿入を選んでくださっても大丈夫です。
新しいピボットテーブルが出来ましたね、以下の通りです。
テーブル間連携は出来ている？
テーブル間連携が出来ているか、確認してみましょう。
ピボットテーブルの編集画面を見ると、以下のようになっているかと思います。
データモデルのインポートだけではなく、それに紐づいたチーム構成データについても取り込まれていることが分かりますね。
チームごとの売上テーブルを作成しよう
次に、出来たピボットテーブルを利用して、適当にデータを選び、データをテーブル表示させてみましょう。
例えば、以下のように選ぶと、
チームごとの売上が月次で表示できますね。
ピボットテーブルの使い方の詳細については今回作成するもの以外は省略するので、ぜひ色々試してみてください。
視覚的かつ直感的にデータを扱えるようにしている機能ですので、ピボットテーブルについては、
習うより慣れろ
です。もし分からない方がいれば、是非色々と検索してみてくださいね。
なお、上記のチームごとの売上のデータは後程、データを更新する項目で利用するので、別シートで取っておくと楽かもしれません。
チームごとの一人当たり粗利を集計しよう-基礎となるテーブルの作成練習
では、今回の目的の一つであるチームごとの一人当たり粗利を集計してみましょう。
ピボットテーブルのフィールドから、以下の通り、表示項目を選択しましょう。
すると、月ごとの合計粗利がチームごとに集計されるかと思います。
チームという項目はそもそもSDデータにはありませんでしたが、上記のデータ連携をしたことによって追加できるようになっていますね。
もし、期間のデータが正しく表示されていない方は、ピボットテーブルの上で右クリックをして、以下のように年と月のデータでグループ化をすると同じようなデータが得られるかと思います。
グループ化を選んで、月と年を単位に選択します。
チームごとの一人当たり粗利を集計しよう-基礎となるグラフの作成練習
次に、マストではないですが、練習がてらピボットグラフを作成します。
ピボットグラフとは、その名の通り、ピボットテーブルに連動したグラフのことです。
ピボットテーブルのタブから、ピボットグラフを選択しましょう。
今回は棒グラフを選択いたしましたが、そのまま選択すると、以下の通りとなります。
色が分かりにくいので、右クリックをして色を更新しましょう。
これでわかりやすくなりましたね。
これで月ごとのチームごとの粗利の推移が確認できます。
あれでも待ってください。このままでは、１人当たりの粗利が計算できませんね。
正直、この程度であれば、計算された粗利の合計額からチーム人数で電卓で計算してしまったほうが早いのですが、今回はPowerQueryとPowerPivotの使い方を知ることがテーマです。
PowerQueryとPowerPivotの機能を利用して、ピボットテーブルに１人当たりの粗利を計算できる項目の追加をします。
これがメジャーです。
チームごとの一人当たり粗利を集計しよう-メジャーを追加する
今回は上記の問題を解決するために、
ピボットテーブルに、
メジャー
という計算データを足していきます。
メジャーの追加は、ピボットテーブルの編集画面で、右クリック→メジャーの追加で出来ます。
こちらを押すとメジャーの編集画面が開きます。
DAX式という専用の数式を利用しなければならないのですが、今回やりたいことは、
粗利の合計/チームごとの担当者数
という単純な計算です。
そこで、「'(シングルクオーテーション）」を打ち込むと必要なデータを選ぶことが出来ます。
（DAX式については正直、これくらい出来ればあんまり困りません。）
簡単な計算や集計であれば、ここから使えますので必要そうなデータを選択して式を完成させましょう。
シングルクォーテーションを選ぶと、合計粗利と、担当者数のカウントというのがありましたので、こちらを選択し、割り算していきます。
以下のような画面ですね。
メジャーの名前についても、「１人当たり粗利」と記載してOKを押しましょう。
そうすると、メジャーが追加されています。
以下の通りにピボットテーブルに表示させると、一人当たり粗利が計算できました。
これでブルーチームが１番稼いでいることが分かりましたね。
では、最後に冒頭に見せたようにデータを更新したら自動的にグラフやテーブルが更新されるように設定していきましょう。
PowerPivotのデータを一瞬で更新する
今回は、チームごとの売上データを表すグラフと、取引先別の売上推移テーブルを一瞬で更新していきます。
想定としては、2021年3月末時点までだった基データが半年分、つまり2021年9月時点まで更新された例を想定しています。
以下の通りです。
更新後のデータを置いておきました。こちらです。
やることはPivotテーブルを組んで、データを更新するだけ
とはいっても、やることは、データを格納しなおして、更新するだけです。
使用したデータは、上記の
「チームごとの売上テーブルを作成しよう」で作ったピボットテーブル
に加えて、ピボットグラフを表示したものです。
更新後SDデータを、SDフォルダに格納しましょう。
そして、すでに組んである実行済エクセルを開き、すべて更新を選択です。
そうすると、以下の通り、古いSDではなく、最新のデータまで更新されたSDに読み取りデータが置き換わり、グラフが自動で更新されます。
また、得意先別のデータについても、ピボットテーブルで以下のように事前に得意先別の売上推移テーブルを作成しておけば、
最新のデータを格納して、すべて更新を押せば、すべて更新されます。
こんな感じですね。
このように、PowerQuery、PowerPivotは、
一度、操作手順や、テーブル、グラフの作成手順を決めてしまえば、あとはデータを更新するだけ
で、超簡単に集計やテーブル、グラフを作成できます。
まとめ
ちょっと長くなってしまいましたが、PowerQueryの使い方、PowerPivotの使い方、少しは伝わったでしょうか。
なんとなく、すごさが伝わってくれれば、そして無駄な作業をしている方が少しでも減ってくれれば幸いです。
詳しく知りたい方は、以下も是非ご参考ください。