さて、いよいよExcelも応用編っぽくなってきました。
今回はPower Query（パワークエリ）の素晴らしさを噛み締めていただきたいです。
パワークエリとはExcelやPowerBI（Microsoftのビジネスインテリジェンスツール）で使用できるツールです。
得意なことは、データをくっつけたり加工したりすること。
それってExcelと何が違うの？ と思われるかもしれませんが、大量データでもとんでもなく軽く操作してくれるんですよ…！
関数やVBAを駆使してめちゃくちゃ大量のデータを整形すると、Excelの挙動はもっさりと重くなります。昔みたいに途中でフリーズすることは少なくなったけど、それでも「フリーズするんじゃないか」とヒヤヒヤさせられる程度にExcelが考え込んでしまう。
てな量のデータでもパワークエリを使うとアラ不思議、めちゃくちゃ少サイズでサクサク動くじゃないですか！
以前は大量過ぎるデータを扱うときは、Accessを使っていました。
Accessはデータベース管理システムで、Excelとは比べものにならないくらいの量のデータを管理することができます。
が、あくまでもデータ管理用なので計算は苦手なんですよね。簡単なことはAccessでもできるけれどExcelと使い勝手が異なるし、複雑なことはできない。
なので、データをある程度整えるのにAccessを使用して、それをExcelにエクスポートして加工する、なんて作業をやっていました。
だがしかし！
Excelでパワークエリが使えるようになって試してみると、Accessなんか要らない、Excelだけで完結するじゃん！ というくらい簡単・軽量だったのです。
元々ワタシはAccessが苦手で「やだなあ」と思いながら触っていたので、パワークエリが使い物になると判明したら即Accessファイルは捨てました。
というくらいワタシにとってはパワークエリはありがたい存在なので、ぜひ使えこなせる人が増えてほしいです。
パワークエリで扱うデータはテーブル化
パワークエリでまずやることは「加工に必要なデータをあちこちから取得する」です。
ファイルサーバーのあっちのフォルダに保存されているファイル、こっちのフォルダに保存されているファイル、とよそから持ってこられるのが魅力ですが、今回デモ用でそれは面倒なので同じファイル内のシートから持ってきます。
使用するファイルは、すべてテーブル化しておきましょう。その方がデータの扱いが楽です。
今回の例では、4月から6月の部署別の経費。
テーブル名は「経費4月」などにしています（なぜ「4月経費」でないかというと、テーブル名は頭に数字を持ってこれないから。なぜだ）。
部署ではなく、全社で発生する費用。テーブル名は「全社経費」。
部署コードや部署名のリスト。テーブル名は「部署コード」。
全社費用の負担割合を示す配賦率。テーブル名は「配賦率」。
データの取得と変換｜データのクレンジングが肝！
まずはデータを読み込んでいきましょう。
「データ」タブの「データの取得と変換」から対象のデータを取得します。
今回は、とりあえず「経費4月」のテーブルにカーソルを置いておいて「テーブルまたは範囲から」をぽちっとします。
別のファイルを呼び出したければ「データの取得」の▼を押して、該当のファイル種類などを選んでいきます。
「経費4月」でぽちっとすると、こんな画面が開きます。
これがパワークエリのエディタ画面になります。
元データのテーブルは「クエリ」として左のボックスに名称が表示され、真ん中にテーブルと同じようにヘッダーとデータが並んでいます。
プロパティなどは右側のボックスで確認できます。
なんか気になることはいろいろありますが、とりあえず残りのテーブルも全部読み込んでしまいましょう。
パワークエリエディタを開いた状態では「ホーム」タブの「新しいクエリ」にある「新しいソース」から対象のデータを選択できます。
複数テーブルを選択して一気に読み込むことも可能です。
こんなカンジにクエリの数が増えました。
パワークエリで重要なのは、データのクレンジング
もう一度「経費4月」のデータを見てみましょう。
いろいろ気になりますね。
まず「空白」のレコードがあるのが気になります。元のデータに何か欠損があったのでしょう。
そして日付の表示も何だかなーってカンジになっています。
パワークエリでデータを加工する際は、まずこういう変なデータを綺麗にするところから始まります。これをデータのクレンジングといいます（クリーニングとかスクラビングとも言われます）。
データはスッキリしているものばかりではなく、重複していたりエラーがあったりヘッダーの表記の揺れがあったりと異常値が含まれてしまうことが多いです。
これらの異常をそのままでデータを加工しても上手くいかないので、まずは異常をなくすところから始めます。
最初に気にしてほしいことは、ヘッダーの型です。
型の種類は、上の画像でいうと日付の「ABC123」や部署コードの「ABC」などのアイコンで区別できるようになっています。
どのアイコンがどの種類かは、アイコンをぽちっとやるとリストが出てきます。
「ABC」はテキスト、つまり文字列ですね。
「ABC123」は文字なんだか数字なんだかよくわかんない！ という状態で、パワークエリとしては非常によろしくない状態です。
まずは日付の型を「日付」に変更し、部署コードと費目はテキストでいいのでそのままで、金額は10進数の「1.2」に変更しましょう。
数値の型の種類は10進数と整数がありますが、小数点以下の数字も保持したい場合は10進数にしておくのが無難です。が、10進数は処理が重くなるので、端数は諦めて整数でやっちゃうときもままあります…。
型の種類を変更した「経費4月」がこちらです。
まだなんかいろいろ気になるデータですね。
お次は、日付を弄ってみましょう。
今回は月別データがあればいいわってことで、年や日にちの表示をなくして月だけ残すことにします。
日付の列を選択した状態で、「変換」タブの「日付と時刻の列」を見ると「日付」のボタンがあります。
これをぽちっとやるといろいろとずらずらと出てきます。
今回は「月」を選び、「月の名前」を選択します。そうすると、「4月」という表示になります。
普段ワタシは月は「4」とか「5」とか数値で表示させたい派ですが、パワークエリでデータを取り扱うときは月数の計算とかあまりやらないので、見やすさ優先で「4月」にしちゃいます。
空白になっているレコードも気になりますね。
今回は1レコードだけおかしなことになっているようなので、どこでもいいので列の▼を押して、空白は表示させないようにフィルターをかけてください。
だいぶスッキリしました。
データのクレンジングの過程は「適用したプロパティ」で確認
と、こうしていろいろ弄っていくと、「クエリの設定」の「適用したステップ」に記録されていきます。
「あっ、なんか弄るの失敗したな」と思ったら、このステップを「×」で消して戻すことができます。
データの抽出
同じように5月のデータも見てみましょう。ううむ、これもちょっとアレなデータですね。
ひとまず、日付を「5月」に変換しました。
さて、金額を10進数にするか…の前に、ちょっとお待ちを。
「不明」も気になるけど「15,800円」も気になりますね。なんでこんなモノが混ざるんだ。そもそものデータの入力画面はどうなっているんだ💢
と腹を立てながら、「円」を消して数字のみにする処置をしましょう。
「列の追加」タブの「テキストから」で「抽出」の▼をぽちっとします。
そうすると、こんな画面で区切り位置を訊かれます。
「円」を区切り記号代わりに指定してやって「OK」とすると、こんな風に列が追加されます。
なるほど、区切った結果の列が増えるから「列の追加」リボンにこの機能があるわけですね。
慣れるまではこういう操作はやりづらいですね〜。
今回欲しいのは追加された列にある「15,800」の方なので、元々の「金額」列を削除します。列の削除は右クリックからでイケます。
不要列を削除したら、新しい列の名称を「金額」に変更し（名称をかちかちっとやると編集できます）、型の種類を「1.2」に変更しましょう。
ふむー、まだエラーがあった〜。「不明」ってなっていたレコードですね。
「くっそ、誰だよ、こんな入力したの💢」とぷんぷんしながら、エラーをなんとかしましょう。
エラーの置換・値の置換
今回は「エラーは見なかったことにしよう」方式で、エラーは「0」ということにします。
エラーをゼロにするには、「変換」タブの「任意の列」の「値の置換」の▼をぽちっとします。
すると「エラーの置換」を選択できます。
今回はゼロにするから「0」と指定して「OK」をぽちり。
やれやれ、これで無事に計算できるようにすべて数値に変換できました。
これで5月のクレンジングは終了かな。
…と思ったら、部署コードに変なヤツがいますね！
これの修正は、先ほど見た「値の置換」からやれないこともないですが「部署コードは先頭が大文字が正解」とわかっているのであれば、「変換」タブの「テキストの列」から「書式」を選び、その中の「各単語の先頭文字を大文字にする」をぽちっとしてやれば修正できます。
こういうちょっとしたエラーは数千数万行規模になると当然気づかず、いろいろ処理した後で「え、なんで？」みたいなことになって、慌ててデータの中身をさばくるというカンジになります。
こういう手戻りをなくすため、そしてクレンジング作業を楽にするため、日頃から綺麗なデータを作るよう心がけてください。
ワタシが表記の揺れなどないように、作業者に自由入力をほぼ許さない状態のファイルを渡す理由を、ご理解いただけたでしょうか…。
クエリの追加｜割と単純なパターン
6月のデータも綺麗にしたということにして、次は各月のデータを一気に見られるようにひとまとめにしたクエリを新たに作ります。
ひとまとめにして見やすい表というと、人間的にはどうしてもマトリックスを使いたくなりますが、ダメですよ。
パワークエリで扱いやすいデータはリスト形式、下に下に行を足していくというイメージで作ってください。
今回の4月から6月のテーブルはすべて同じ形式になっていて、型の種類もきちんと揃えたのでまとめるのは簡単です。
まず、「ホーム」タブの「結合」から「クエリの追加」の▼をぽちっとします。
「クエリの追加」か「クエリを新規クエリとして追加」か選べます。
「経費4月」に5月以降をどんどん足してやるという手もありますが、各月のクエリとは別途新規クエリを作ることにしましょう。
追加するテーブルを選択できます。今回は3カ月分あるので「３つ以上のテーブル」を選択して、該当するテーブルを「追加するテーブル」に放り込んでやります。
そうすると、こんなカンジで新しいクエリができました。
新しいクエリの名称は「まとめ」に変更してあります。
これで「まとめ」クエリを利用すれば、どの月のデータも簡単に取り出すことができるようになりました。
クエリのマージ｜割と単純なパターン
しかし「まとめ」のデータを眺めると、なんか違和感があります。
そういえば、ここには部署コードはありますが、部署名がないですね。このままだと人様に見せても「？」となるので、部署名を追加しましょう。
幸い、「部署コード」のテーブルも読み込んであるので、これを使えます。
先ほどの「クエリの追加」は下へ下へデータを足し込んでいきました。
今度は「クエリのマージ」という機能を使います。
クエリのマージは、何かをキーにしてふたつのテーブルを繋ぎ、データを加工していくことです。
この例だと「まとめ」には部署コードがあるけど部署名がない、「部署コード」には部署コードも部署名がある、なので部署コードをキーとして「まとめ」と「部署コード」を繋ぎ、部署名を「まとめ」にも追加する、という作業になります。
マージしたい「まとめ」のクエリを開いた状態で、先ほどのクエリの追加のように「結合」から「クエリのマージ」を選びます。
今回は「まとめ」そのものを弄りたいので新規ではなく「クエリのマージ」でOKです。
すると次の画面でマージさせたいテーブルはどれか、キーとなる照合列はどれか訊かれます。
部署コードをキーにマージしてやると、「まとめ」はこんな風になります。
この状態だと部署コードをキーに「部署コード」テーブルを引っ張ってきたけど、どのデータを表示させるかあやふやという状態です。
使うデータを決めるために、追加された列の左右の矢印ボタンをぽちっとします。
すると、こんなカンジで展開する列を選べます。
今回は、部署コードは「まとめ」にもある、拠点の情報はまあいいやってことで、部署名だけにチェックを入れます。
なお、「元の列名をプレフィックスとして使用します」は、わかりやすいかもしれないけどえっらい長い列名になっちゃうので、ワタシはチェックを外すことが多いです。
これで「OK」すると、こんなデータになりました。
部署名が入った方がわかりやすいですね。
イメージとしては、Excel関数のXLOOKUP関数に近いかもしれません。
何かをキー（条件）にして返す項目を指定する、あのやり方です。
それを関数なしにできるのはありがたいけれど、マージ方法も慣れないとよくわからんですよね。
列の位置が気に入らなかったら、ぐぐーいと移動させることができるのでお好みで。
こういう列の位置の変更とかもステップに記録されていきます。
詳細エディタも活用してね
記録されたステップの内容は、「ホーム」の「クエリ」の「詳細エディター」から確認できます。
…って、こんな呪文わかんないよ！
となりそうですが、慣れてくると「マージしたテーブルの列を展開するのは、エクスパンドテーブルカラムで指定しているんだな」となんとなく記述の法則がわかってきます。
VBAを書くとき、慣れないうちはマクロの記録を使ってからコードを見て…とやっていたのが、慣れてくると直接コードを編集する方が速いように、パワークエリも「ここも同じように置換したい」とか「列はこの順番がいい」みたいな作業は詳細エディターでコピペからの修正でやっつけた方が楽になってきます。
クエリのマージ｜カスタム列を使った全結合パターン
よしよし、これで全部データを整えたぞ。
と一瞬安堵しましたが、全社経費の存在を忘れていました！
どこの部署とかじゃなくて、会社全体で発生する家賃だの光熱費だの、そういう類のものを想定してください。
んで、「配賦率」テーブルで各部の負担割合とその考え方を示している、というカンジ。
この家賃だの光熱費だのの、各部負担金額分も合わせて月別のデータを確認したい。
そんなニーズがあるという体のダミーデータです。
「全社経費」には4月の地代家賃500,000というレコードしかないけれど、その金額を部署コード別・配賦割合別に分けたい、つまり4月の地代家賃の1レコードを4レコードにしたい。そんな作業になります。
これをExcelでやるとすると、どうするかなあ。ちょっと面倒くさそうですね。
しかし、パワークエリだと割と簡単に可能です。
とりあえず、「全社経費」に「配賦率」をマージしてやるか！
という雑なところから出発するとして、今回は「列の追加」タブの中の「カスタム列」から作業してみましょう。
カスタム列の追加では自分で計算式を組んだりできるので、結構よく使います。
カスタム列で何をしたいか訊かれます。
列名は適当に、今回は「配賦マスタ」とでも。
カスタム列の式は、とりあえず今回は配賦率テーブルを全部放り込んだれということで、下のような指示にします。
すると、こんなカンジにテーブルが畳まれた状態でマージされます。先ほどのパターンと同じですね。
んで、これまた同じようにどの項目を展開するか選択します。
今回は部署コードも配賦割合も欲しいので、チェックはこんなカンジ。
すると、「全社経費」のデータがこうなりました。
これをExcelのシートでやろうとすると「えーと、4月を費目別に4行にして、部署コードを入れて、XLOOKUPで配賦割合を引っ張ってきて…」と、ちょっと面倒な作業になりますが、パワークエリだとこの辺の作業は一瞬で済ませてくれます。楽です。
ここまで来たら、部署別の配賦金額算出はちょろいもんです。
もう一度カスタム列を追加して、今度は金額*配賦割合の計算式を作成します。
えー、式書くの面倒くさー。またなんか変なお作法（列名を[ ]で括るとか）あるし。
となりますが、使用できる列で対象列を選択して「<<挿入」をクリックすれば式の方に入ってくれるから、四則演算の記号を書く程度で済みます（複雑な作業をしないのであれば…）。
めでたく月別・費目別・部署コード別の配賦金額を算出できました。
クエリの追加｜ヘッダーをきちんと揃えておく！
さて、あとは「まとめ」と「全社経費」をガッチャンコさせるだけだぞ！
といきたいところですが、その前にもうひと手間かけてスムースに合体させられるようにしましょう。
今、「まとめ」のヘッダーはこんなカンジになっています。
それに対して「全社経費」はこう。
比べてみると、いろいろ問題点があります。
- 月が「月」と「発生月」と異なる名称になっている（型の種類や表記が同じなのはいいこと）
- それぞれの「金額」の数字の意味が異なる（全社経費の「金額」は4部署合計値）
クエリを追加（合体）するときは、列名が完全一致したものを同じデータとして判断されます。
なので「月」と「発生月」は別物とみなされて、別々の列になってしまうんですね。
こうした不備を修正するために「全社経費」のデータをクレンジングしましょう。
- 「発生月」を「月」に修正
- 「金額」を「配賦前」に修正
- 「配賦金額」を「金額」に修正
これでクエリの追加を行います。
「まとめ」に「全社経費」を追加と指定。
すると、「まとめ」のデータはこんなカンジになりました。
「配賦前」「備考」「配賦割合」と、「全社経費」テーブルにしかなかった列も勝手に増えていますね。
追加された列は「まとめ」にはなかったので、nullという「何にもないよ〜」ということを示すエラーになっています。
とりあえず、今回の分析に「配賦前」と「配賦割合」は使わないということで、不要列を削除しました。
nullエラーは計算の邪魔になるので、本来は置換したり削除したりと何かの処理をしなくてはいけません。
が、今回は「備考」というテキスト列に残っているだけで計算処理をしないから、このまま使用しましょう。
「ホーム」から「閉じて読み込む」をぽちっとやると、Excelのシートに「まとめ」が吐き出されます。
やりました！
これで分析しやすいデータの作成完了です。
あとはピボットテーブルやグラフを作成して、データから何が言えるのかを探ります。
参考｜パワークエリを使えばピボットを解除できるよ
なんだよ、パワークエリ簡単じゃん！
あんなにマトリックスを苦労して作ったのに…！ もったいないことをした！
と思ったあなた、パワークエリでマトリックスからリスト形式に簡単に変換できますよ。
対象のデータをパワークエリで読み込んだ後、「変換」タブから「列のピボット解除」をぽちっとしてください。
どこの部分をリスト化するか指定したら、あっという間に縦長のデータに変えてくれます。
元々リスト形式のデータはExcelシート上でピボットテーブルで簡単に変換できるんですけどね。
マトリックスをリストにするのは、パワークエリでやるのが最も楽だと思います。
（もっとも、元のマトリックスデータが見映え重視で結合セルだの空白行だのわんさか含んでいると、クレンジングに骨が折れますが…。）
おわりに
パワークエリは、膨大なデータを軽快に加工してくれる強力ツールです。
Excelのシートを扱うときと使い勝手が異なるので戸惑いますが、パワークエリを使えるとデータの取り扱いが格段に楽になるので、ぜひ使っていただきたいツールです。
そしてパワークエリを使いこなすのが最終目標ではありません。
これでデータから何が言えるかという分析ができる準備が整った、という仕事のとば口に立ったのです。
俺達の戦いはこれからだ…！
