はじめに
Excelにデータを集めようとするとき、数式とVBAを駆使して行う方法が一般的です。
キーを使って複数のデータを結合するときには、Accessなども用いられます。
ですが、データが増えてくると下記のような課題が発生してきます。
- 収集に必要な手順が増え、数式やVBAが複雑化する
- データ収集に長い時間がかかる
- サイズが巨大化して格納しきれなくなる
これらの課題を解消するツールとして PowerQuery を紹介します。
PowerQueryとは
2016年に公開されたMicrosoft製のデータ分析用Excelアドインであり、Excel2010以降に対応しています。
ファイル・DB・Webサービス等からデータを読み込み、変換・加工してExcelのシートに出力できます。
VBAのマクロの記録と同じように、画面上で操作するとクエリ（読み込み手順の定義）が生成されます。
複雑な加工をしない限りプログラミングは必要ありません。
Excel内に全ての情報を格納するため、一つのファイルで完結し他のツールは必要ありません。
PowerQueryの入っていない端末では普通のExcelファイルとして閲覧することができます。
中間処理は全てメモリ上で行われ、処理が非常に高速であることが特徴です。
ファイルに出力されるのは最終の出力結果のみであるため、ファイルサイズも少なく済みます。
PowerQueryのインストール
Excel2010,Excel2013であれば、下記からダウンロードしてインストールします。
Excel2016以降からは標準機能となっているのでインストールの必要はありません。
PowerQueryではなく 取得と変換 と名付けられていますが、同じ機能です。
PowerQueryの起動
Excel2010, Excel2013
PowerQueryタブをクリックします。
Excel2016 以降
取得と変換から新しいクエリをクリックします。
Office365 を利用している場合、最新版ではExcelの標準機能としてデータタブに統合されています。
PowerQueryを使ってみる
PowerQueryを使ってデータを読み込むサンプルです。
単純な読み込み処理で加工・変換は行っていませんが、その分手順の少なさがわかります。
フォルダ内のファイルを一覧にする
複数のフォルダに分散したファイルを一覧にしていきます。
手順
フォルダーから をクリックします。
取得するフォルダーパスを指定します。
読み込み をクリックします。
ファイルの一覧が読み込まれます。
読み込んだデータを更新する
読み込んだデータを最新化するには、すべて更新 をクリックします。
新しく追加されたファイルが読み込まれました。
データの更新はPowerQueryが入ったExcelであれば、どの端末であっても実施できます。
複数のExcelファイルを結合する
複数のファイルを結合して読み込みます。
手順
フォルダーから をクリックします。
取得するフォルダーパスを指定します。
結合および読み込み をクリックします。
読み込む対象ファイルの例が表示されるので、OKをクリックします。
Excelに結合したデータの一覧が読み込まれます。
Webサービスから読み込む
Webサービスから読み込みます。サンプルに用いているのはQiitaの記事投稿数が多いタグを取得するAPIです。
手順
Webから をクリックします。
APIのURLを指定します。
テーブルへの変換 をクリックします。
JSONはPowerQuery内でリストとして取り込まれ、テーブルに変換することで加工可能になります。
必要に応じて区切り記号など選択しOKをクリックします。
テーブル形式に変換されるので、列タイトルにある展開ボタンをクリックします。
テーブルが展開されたら、閉じて読み込むをクリックします。
ExcelにAPIから取得したデータが読み込まれます。
その他の機能について
PowerQueryは読み込んだデータを変換・加工することで真価を発揮します。
Excelの数式で実現できることは一通りPowerQueryでも実装されており、
クエリーエディタ上で手順をプレビューしながら変換・加工していくことができます。
PowerQueryでできることの例
下記は一例ですが、画面操作だけで他にも様々な処理を定義することができます。
- 必要な行・列のみを取得する
- 値を加工して出力する（LEFT,MID,RIGHT関数に相当)
- 特定の条件に該当する行にマークをつける(IF関数に相当）
- 複数の行をグループ化して集計する
- ピボット集計する/ピボット集計されたデータを元に戻す
- 複数のクエリを参照して新しいクエリを作成する
- IDを使って複数のクエリを結合する（VLOOKUP関数に相当）
- 内部結合、外部結合などSQLで実現できる一通りの結合処理が行えます
- ExcelでPowerQueryを使ってデータ収集分析に詳しい手順を記載しています。
- 特定の加工をまとめて実施するカスタム関数を定義する
- ファイルパスなどの変化する値をパラメータ化する
- データソースにセキュリティをかけ、アクセス制限を行う
また、PowerQueryで作成したクエリはM言語(PowerQueryFormulaLanguage)のプログラムとして出力されます。
GUI上にない処理であっても、M言語を駆使すると自由にクエリを組み立てられるようになります。
クエリを編集するには 詳細エディター を開いてください。
下記は Webサービスから読み込む で出力されたプログラムです。
役に立つリンク
基本的な情報
Microsoftの公式サイトです。日本語訳がわかりにくいこともありますが、情報が豊富に載っています。
PowerQueryに関する情報が日本語で書かれています。
解説もアニメーションGIFを交えて丁寧に行われていてわかりやすいです。
M言語の情報
日本語で書かれたM言語の関数およびデータ型のリファレンスです。
必要な関数を検索するときに使っています。
日本語訳はartfulplace.netさんが実施されています。
Power Query M function reference
Microsoftの公式関数リファレンスです。
日本語訳がなくExampleが少ないのが難点ですが、全ての関数を網羅しています。
Chris Webb's BI Blog
The Ken Puls (Excelguru) Blog
Matt Masson
M言語のサンプルが豊富に載っています。
GUIだけでは実現できない高度なデータ収集手順を作成するのに役に立ちます。
M言語のカスタム関数集です。
標準の関数で実現できない処理があればここを探してみています。