絶対に誰でもわかるPowerQuery(パワークエリ),PowerPivot(パワーピボット)の使い方(1)-PowerQuery：データの接続と変換
絶対に誰でもわかるPowerQuery,PowerPivotの使い方(1)-PowerQuery：データの取得と変更
こんにちは。会計士KO（記事の更新や、企業の分析、日々の雑感等つぶやいておりますので、興味のある方は是非Twitterフォローしてください）です。
今回の記事シリーズでは、Excelで、超優秀なのにあんまり知られていない機能、PowerQuery,PowerPivotについて、絶対に誰でもわかるように解説をしていきたいと思います。
そもそも、PowerQuery,PowerPivotという言葉を知らないという人にも理解できるように心がけるので、是非この記事を読んで使いこなしてみてください。
PowerQueryと、PowerPivotで実現できること
そもそも、PoweQuery、PowerPivotを使うと何が出来るのか？
例えば、経理担当者の方であれば、
・毎月、販売データを販売管理システムからダウンロードして、
・納品完了・失注などの案件ステータスをリスト機能で抽出し、
・商材や会社名をIF関数やCOUNTIF関数で振り分け、
・粗利や利益率を示す列を計算式で追加し、
・そして取引先別の売上や分析を行う
等の煩雑な作業をしていたりしませんか？
定常業務だけれども意外と面倒くさい。一回だけ作業するならあんまり面倒ではないと思うのですが、例えば、毎月、毎四半期となると無駄な時間がかかるように思います。
この点、エクセルの隠れた超優秀機能であるPowerQuery,PowerPivotを利用すると、利用するデータは更新されていくけれど、その加工が面倒、、、といった状況を一瞬で解決できます。
しかも、特別な知識は特に必要ありません。
VBAやマクロには取っつきにくいという人も超簡単に使えるので是非習得して欲しいと思っています。
今回の記事ではまず先に、PowerQueryの使い方について説明していきます。
また、想定としてはExcel2018を想定して記載していきますが、基本的にExcel2016では同じような画面での操作が可能なので、ぜひ参考にしつつ、適宜読み替えて確認をしてください。
PowerQuery（パワークエリ）とは
PowerQuery（パワークエリ）とは、MicrosoftOfficeのExcelが提供しているエクセルの機能の一つで、エクセルシートと、外部データとの連携や、連携の際のデータの加工、列の追加や抽出等を定型化する機能です。
連携するデータとしては、xlsxファイルやxlsファイルだけではなく、CSVファイルやWeb上のAPIで提供されているJSONデータなどとの連携も簡単に行うことが出来ます。
また、マクロやVBAなどとは違い、データの連携や変更という点に特化し、グラフィカル（視覚的）に操作を行うことが出来るため、初心者でも非常にわかりやすく操作することが出来ます。
パワークエリの主な機能は、データの接続、変換、結合、管理です。
今回は、そのうち、接続と変換について説明をしていきたいと思います。
PowerQueryで出来ること
最初に実例をお見せします。
想定は、毎月、販売管理ソフトから生成される販売データをダウンロードし、必要な情報（会社区分、粗利）を追加したうえで、会社別の月次売上推移分析、取引先別の月次売上分析を行うといった事例です。
要件は以下の通りです。
⓪：販売管理ソフトでは、親会社（A社）と子会社2社（B社・C社）の販売データがすべて一括で登録され、案件番号にて振り分け（Aから始まれば、A社等）られており、元データである販売データは随時更新される。（基データ）
①：月次で販売データをダウンロードし、不要な列を削除（ステータス：失注を削除）し、必要な情報（会社区分、粗利の判定）を追加する。（加工販売データ）←PowerQueryの領域
②：加工販売データを利用し、会社別の月次売上推移分析、取引先別の月次売上分析を行う。←PowerPivotの領域（次回以降）
まず、基データです。(クリックで別ウインドウが開くのでぜひ拡大してみてください。）
本来であれば、
要求①については、テーブルをリスト化してステータスが「納品完了」となっている列のみを抽出をします。
要求②については、案件番号列をIF関数やCOUNTIF関数を用いて判定式を組み、結果を表示させます。
要求③については、売上から売上原価を差し引いた列を計算します。
これ、この加工が一回だけだったらいいと思うんですが、毎四半期同じことを繰り返すとなるとかなり馬鹿らしくないですか？
冒頭にも説明した通り、PowerQueryを使えば一瞬で解決が出来ます。
例えば、以下は基データにないA00000というデータが追加された場合に再度、同期を行い、データを処理しているものです。
基データ（下のエクセル）には、会社判定カラム、粗利カラムはありませんが、同期を行ったことにより、会社判定列、粗利列を追加したうえでデータを取得できていることが分かります。
今回サンプルでお見せしているのは1行だけですが、実質的に何行足されようと更新ボタンを押せば任意のデータを取り出すことが可能です。
ちょっとだけ便利さが伝わったのではないでしょうか。
では、以下で説明をしていきますね。
PowerQueryの使い方
今回、PowerQueryの使い方として説明するのは以下の4点です。
・外部データの接続
・外部データの変換
・任意の列の追加
・操作の順番変更、取り消し
では、実際に始めていきましょう。
外部データに接続する
データに接続します。
どのようなデータを利用してもいいのですが、同じように作成したい方は、以下に同じデータを用意しました。
今後、以下のエクセルは基データと呼ぶことにします。
手順を学べば、色々なデータで同じことが出来るので、眺めた後に自分の手元のファイルで試せそうな場合は、そちらで実行してみてください。
基データを保存する
まずは、基データを分かりやすいように保存します。
今回はPowerQuerySD.xlsxという名前で保存することとします。なお、SDはソースデータの略です。
また、シート名も分かりやすいように保存をしておきましょう。
こんな感じですね。
作業フォルダと実行用エクセルを作成する
次は、基データを格納する任意のフォルダを作りましょう。
今回はデスクトップ上にPowerQueryというフォルダを作成し、そのフォルダの中にSDというフォルダを作成し、上記の基データを格納しています。
また、その次に、PowerQuery実行.xlsxという新しいエクセルを作成し保存しています。
このエクセルが実際に作業を行う用のエクセルです。今回はこのエクセルを実行エクセルと呼ぶことにします。
実行エクセルで外部データを連携する
では、次に実行エクセルで外部データ（＝基データ）を連携していきましょう。
実行エクセルを開くと、白紙のシートが出ているかと思いますので、以下の画面のようにデータタブを押してください。
そうすると次に、データの取得というボタンがあるので、以下のようにブックからを選択します。
データの取り込みというウインドウが開きます。
データを格納したアドレスを指定して、基データエクセルをインポートしましょう。
データの連携（仮）完了
インポートすると以下のような画面が現れます。
これで取り急ぎ、データの連携は完了です。
でも待ってください、前提や要求などといった不要な要素がそのまま取り込まれてしまっていますね。このため、見出しには（仮）をつけています。
以下にて、使えるようにデータの変換をしていきましょう。
データを変換する
データの編集方法
データの一次連携が完了したら、右側に出てくるクエリと接続ペインにてSDを右クリック→編集していきましょう。
不要なデータを削除する
まずは、不要なデータが入っているので、該当のデータを削除しましょう。
エディターウインドウが開いているかと思いますので、ホームタブから行の削除を選び、上位の行の削除を選択してください。
今回は上位3行が邪魔なので削除してあげましょう。
そうなると、以下の通りとなると思います。
しかし、まだヘッダー（見出し）が変ですね。本来は1行目が見出しの役割を果たすべきです。
左上のテーブルマークのアイコンを右クリックして1行目をヘッダーとして使用を押してあげましょう。
これで、必要なデータになったはずです。
データを抽出する
次に売上ステータスになっているデータのみを抽出したいので、ステータス列の横の▼ボタンを押し、失注を外しましょう。
そうすると、納品完了のみの列となったはずです。
データ型を変換する
また、現状のステータス確定日付が数値表記になっていますね。
この場合は、ステータス確定日付の欄を右クリックをして、日付/時刻という型に更新できます。
すると、以下の通りになったはずです。
ここまでできた方は、左上から
を押して閉じましょう。
以下のようにデータが変換され取り込まれていると思います。
データの変換については、以上です。
次は、必要な列を追加しましょう。
任意の列を追加する
次は、任意の列を追加する必要があります。
要件に記載したように、
・会社分類を行い、
・粗利の計算をしたい
ですね。
条件列を追加する-会社分類を判定する
また、クエリと接続画面で右クリックし、編集をクリックしましょう。
同じようにPowerQueryエディターが開いたかと思います。
まずは、列の追加タブを押して、条件列の追加ボタンを押します。
ボタンを押すと、以下の画面が開きますので、必要な条件を選んでいきます。
今回は、案件番号が、
・Aから始まれば、会社A
・Bから始まれば、会社B
・Cから始まれば、会社C
という分類なので、以下のように条件を追加していきます。
これでOKを押すと、以下の通りになるかと思います。
案件番号A〇〇〇〇〇とB〇〇〇〇〇の境目で正しく会社の分類が判定されていることが分かります。
カスタム列を追加する
次は、粗利を計算する列を追加してあげましょう。
条件列の追加をした際と同じように、列の追加を押して、カスタム列を押して下さい。
出てきたウインドウに必要な関数を打ち込んでいきます。
注意点としては、使用できる列は限られており、セルを選択したような計算をしたい場合は、右の「使用できる列」というウインドウから選択を行う必要があるという点です。
また、実際にはDAXという計算式が利用されているのですが、今回DAXの話は省略します。
計算式を打ち込んで、OKを押すと、粗利列が追加されているかと思います。
計算された結果も正しいですよね。
ここまでできた方は、左上からを押して閉じましょう。
これで会社判定、粗利が計算された列が追加されたデータを作成することが出来ました。
操作の順番を変更する、操作を取り消す
実は今まで適用した操作は、
PowerQueryエディターのクエリの設定＞適用したステップにすべて保存されています。
ドラッグアンドドロップで適用する順番を変更したり、
ボタンを押せば、誤って操作した際の操作を取り消すことも可能です。
データを更新する
データを更新したい場合には、基データを更新し、データタブから、すべて更新をクリックすれば上記の適用したステップが更新後のデータに反映され、新しいデータを得ることが出来ます。
試しに好きなデータをランダムに打ち込んで、再度読み込ませてみてください。
冒頭でお見せした以下のGifは、案件番号A00000から始まるデータを追加し、データを更新したものです。
ちゃんと、会社判定が自動で走り、粗利の計算がされたうえでシートが作成されているのが分かりますね。
まとめ
次回は、PowerQueryを利用して取り込んだデータを基に、PowerPivotを使って毎月のグラフを毎月、自動更新したり、取引先別の分析を毎月、自動更新したりといった手順をご紹介します。
VBAやマクロを利用するよりも何倍も簡単に使える機能、PowerPivot、PowerQuery、ぜひこの記事を参考に使いこなしてみてください。
自分でより深く学んでみたいという人は、以下の書籍もどうぞ。