スポンサーリンク
はじめに
今回から、ExcelのPowerQuery(パワークエリ)についての記事を書いていきます。
PowerQueryは、私も今まさに学習中であり、Twitterでたまに質問をしたりしており、まだまだ知らないことが多い。
それでも、基礎レベルならかなり解説できるようにはなってきたと思うので、分かるところから記事にしていきたい。
PowerQueryとは
PowerQueryの機能概要
まずPowerQueryとは何かというのを説明するのは難しいが、ExcelとかCSV、ほか様々なデータを加工分析する強力な機能を備えた機能だ。
例として次のようなことができる。
- Excelファイルの単一シートだけでなく、複数シートのデータを結合して扱う
- CSVファイルを扱う
- フォルダ内のExcelファイルやCSVファイルを全部まとめて結合して扱う
そしてこれら操作をするに当たって、元データとなるCSVファイル等には直接手を加えることはない。
また、元データの中にある何千行などのデータを、直接Excelファイルに展開するだけでなく、データ加工の手順だけを作り込んでおくことができる。
データ量が多いときは、その加工手順だけ作っておいて、必要なときだけ展開するようにすれば良いだろう。
CSVファイルを扱う
CSVファイルというのは、データをカンマ「,」で区切ったファイルのことだが、従来は扱いがひどく難しかった。
迂闊にExcelで開くと「0001」が「1」に化けるとかの問題が多く、それを避け正しく開くのにいまいち決定打がなかった。
旧来のExcelの機能にテキストファイル取り込みウィザードとかあったが、かなり使いにくかったし、あるいは専用マクロを使うとかになるのだが、それも正しく作り込むのが難しかった。
しかしPowerQueryを使えば、その問題もほぼなかったことのようにできる。
CSVファイルだろうとExcelファイルだろうと、まずPowerQueryで開いてみるようにすれば良く、ほぼ同類同質のファイルを扱う感覚にすることができる。
もちろん、「0001」が「1」に化けるとかの問題も普通にクリアできる。
データの結合・加工
- 同じフォーマットの表が、1つのExcelブックに複数シートあり、それらを1つに結合したい。
- 同じフォーマットの表のExcelファイルが、フォルダ内に複数ファイルあって、それらを1つに結合したい。
などといったデータ結合は、専門的なマクロ(VBA)の領域だったが、PowerQueryで結構手軽にやれるようになった。
(常にVBAを使わなくて良くなったという意味じゃないし、常にVBAより簡単だとも限らない)
そしてPowerQueryの加工分析機能でできることを列挙してみると
- 沢山の列がある中から、自分の必要な列だけ残して他は削除し、列の順序も好きに入替えてカスタマイズする。
- コード「111」と名称「A社」といった列どうしを結合し「111:A社」という新しい列を作成する。
- 逆に、列を何らかの文字で区切って複数列に分割する。
- Excelセンスがイマイチな人が作った、変なセル結合とか空白セルが入りまくったデータを、柔軟に加工する。
- 「部門コードなどのマスタのデータしかないExcelファイル」「日々の取引明細のExcelファイル」など別々のExcelファイルどうしのデータを結合して、一体化したデータに作り直す。
などといったものになる。
PowerQueryの注意点
PowerQueryは非常に便利だが、弱点とか注意点もある。
VBAより簡単とは言い切れない
PowerQueryは、VBAのようにソースコードを書かなくてもマウス操作だけでかなりやれる。
いや上手い人なら、M言語というやつのソースコードを書くのだけど、現状の私もそれはできない。
しかし、ソースコードを書かずにやれるからって、VBAより簡単とは言い切れない。
いや操作が分かってる人なら、VBAより簡単にやれる部分が多いのだけど、初学者にとってのハードルは決してVBAより低いとは言い切れないだろう。
PowerQueryではクイックアクセスツールバーのカスタマイズとかできなくて、リボンをガンガン使わないといけないし、その中にある専用メニューの把握が非常に難しい。
それら専用メニューは、表記の意味が分かりにくい物が多いし、どういう場面でどれを使えば良いのか習得には結構な時間が掛かるはずだ。
元データの変更が難しい
PowerQueryでは、元データとして使うファイル(データソースという)について、フォルダやファイルの場所(パス)を明確に示して扱う。
そしてその元データの場所を、変更するのが煩雑なのだ。
もちろん、元データを変更すること自体は普通にできる。しかし、VBAならファイルを開く専用のダイアログを出すなどして、元データを毎回柔軟に変更させることができるのだが、PowerQueryでそれはちと難しい。
PowerQueryでは、元データを変更するには必要な操作ステップが多い。
使い方を分かってる人にとってはそれは大した問題にならないかもしれないが、PowerQueryについての予備知識が一切ない人にも元データを簡単に変更できるように作り込むのは不可能に近い。
元データの変更に限らず、操作全般について、VBAのように「ボタン一発で終えられる」ように作り込むことが難しいのだけどね。
PowerQueryが使えるExcelのバージョン
PowerQueryは、Excel2016以降のバージョンから追加されたものだ。
Excel2013のバージョンでも、下記サイトからアドインをダウンロードすれば使えるが、なんか使い勝手がイマイチ良くないと思う。
Power Query
Microsoft Download Centerひとまず今後の記事では、常に最新版のExcelが提供される「Office365」のExcelを用いて、PowerQueryについて書いていく。
PowerQueryについての本
私は「Excelについては、ネットだけで勉強すればいいだろ、本は要らない」派なのだが、それでもPowerQueryについては本で読んで勉強しないとかなりキツいと思う。
PowerQueryは、純粋にExcelの機能というよりは、M言語というやつを使ったもので、VBAとかよりもExcelから独立した感がある。
比較的新しい機能だし、インターネット上で効率的に学ぶのは現状難しいのではないかと思う。
では、PowerQueryについては何の本を読めば良いかと言うと、必ず下記の2冊を読むように強調したい。
下記の2冊は、PowerQuery初学者にとって必携だ。どちらか一方でなく、必ず両方とも読もう。
いちばんやさしいExcelピボットテーブルの教本 人気講師が教えるデータ集計が一瞬で終わる方法
公認会計士の羽毛田睦土 先生の著作。
タイトル通り、ピボットテーブルの内容がメインですが、PowerQueryの入門内容についてこの上なく分かりやすく言及した章があるので、まずこれを読みましょう。
もちろん、ピボットテーブルそのものの勉強にもうってつけの本です。
Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本
鷹尾祥 先生の著作。
「パワーピボット」ということでピボットテーブルの形にしたものを扱うわけで、単純なPowerQueryとは少し離れる内容もありますが、PowerQueryの根底となる考え方について非常に濃くまとまった本。
高度な内容まで踏み込むので、私も深く学べていないところはありますが、PowerQueryの習得にあたっては全体を読んで大まかに把握しておきたい。
実際のPowerQuery操作例：Excelファイルを1つ開いてみる
PowerQueryの操作例として、単一のExcelファイルを1つ開いて少し加工してみる例を示す。
今回は下図のような、見出しが4行目から始まるファイルを取り込んでみる。
このExcelファイルサンプルは、こちらに保存してある。フォルダ内に4ファイル保存しているが、そのうち1ファイルだけ取り込んでみる。
操作手順
Excelで、
リボン「データ」タブ
→「データの取得」
→「ファイルから」
「ブックから」
と進める。
ファイルを開く画面が出てくるので、該当のExcelファイルを選択し「インポート」
(もちろん、Excelファイルをダブルクリックでも良い)
左側で、シートを選ぶ箇所があるので、該当のシートを選択。データのプレビューが表示される。
→「データの変換」を押す。
表示された画面を見ると、なんか上の2行がnullとか表示されていて邪魔だと分かる。
リボン「ホーム」タブ
→行の削除
→「上位の行の削除」
としよう。
ちなみにこのPowerQueryのリボンやクイックアクセスツールバーは、通常のExcelのそれとは違い、まるでカスタマイズできない。
「上位の行の削除」について行数を入力する画面が出てくるので、邪魔な行数である「2」を入力して「OK」
これで邪魔な2行は削除されるが、見出しにColumn2とか書かれていて、どうにも見出しが正しく認識されていないようだ。
リボン「変換」タブの「1行目をヘッダーとして使用」を押そう。
ひとまずこれで加工を終わりとする。もちろん、後で追加・訂正が必要になったらできる。
リボン「ファイル」タブの「閉じて読み込む」を押そう。
Excelに、見出しを正しく設定したデータが出力される。
今回は、とりあえずファイルを開ければ良い程度の操作例を示したが、今後、色々な例を解説していきたい。
スポンサーリンク
