ExcelでPowerQueryを使ってデータ収集分析

  • 83
    いいね
  • 0
    コメント

はじめに

Excelにデータを集めようとするとき、数式とVBAを駆使して行う方法が一般的です。
キーを使って複数のデータを結合するときには、Accessなども用いられます。

ですが、データが増えてくると下記のような課題が発生してきます。

  • 収集に必要な手順が増え、数式やVBAが複雑化する
  • データ収集に長い時間がかかる
  • サイズが巨大化して格納しきれなくなる

これらの課題を解消するツールとして PowerQuery を紹介します。

PowerQueryとは

2016年に公開されたMicrosoft製のデータ分析用Excelアドインであり、Excel2010以降に対応しています。
ファイル・DB・Webサービス等からデータを読み込み、変換・加工してExcelのシートに出力できます。
VBAのマクロの記録と同じように、画面上で操作するとクエリ(読み込み手順の定義)が生成されます。
複雑な加工をしない限りプログラミングは必要ありません。

Excel内に全ての情報を格納するため、一つのファイルで完結し他のツールは必要ありません。
PowerQueryの入っていない端末では普通のExcelファイルとして閲覧することができます。

中間処理は全てメモリ上で行われ、処理が非常に高速であることが特徴です。
ファイルに出力されるのは最終の出力結果のみであるため、ファイルサイズも少なく済みます。

PowerQueryのインストール

Excel2010,Excel2013であれば、下記からダウンロードしてインストールします。

https://www.microsoft.com/ja-jp/download/details.aspx?id=39379

Excel2016からは標準機能となっているのでインストールの必要はありません。
PowerQueryではなく 取得と変換 と名付けられていますが、同じ機能です。

PowerQueryの起動

Excel2010, Excel2013

PowerQueryタブをクリックします。

alt

Excel2016

取得と変換から新しいクエリをクリックします。
Office365 を利用している場合、最新版ではExcelの標準機能としてデータタブに統合されています。

alt

PowerQueryを使ってみる

PowerQueryを使ってデータを読み込むサンプルです。
単純な読み込み処理で加工・変換は行っていませんが、その分手順の少なさがわかります。

フォルダ内のファイルを一覧にする

複数のフォルダに分散したファイルを一覧にしていきます。

手順

フォルダーから をクリックします。

2017-06-17 12_24_33-Clipboard.png

取得するフォルダーパスを指定します。

2017-06-17 12_25_40-Clipboard.png

読み込み をクリックします。

2017-06-17 12_26_09-Clipboard.png

ファイルの一覧が読み込まれます。

2017-06-17 12_26_33-Clipboard.png

読み込んだデータを更新する

読み込んだデータを最新化するには、すべて更新 をクリックします。

2017-06-17 12_28_28-Clipboard.png

新しく追加されたファイルが読み込まれました。
データの更新はPowerQueryが入ったExcelであれば、どの端末であっても実施できます。

2017-06-17 12_28_51-Clipboard.png

複数のExcelファイルを結合する

複数のファイルを結合して読み込みます。

手順

フォルダーから をクリックします。

2017-06-17 12_24_33-Clipboard.png

取得するフォルダーパスを指定します。

2017-06-17 12_25_40-Clipboard.png

結合および読み込み をクリックします。

2017-06-17 12_31_20-Clipboard.png

読み込む対象ファイルの例が表示されるので、OKをクリックします。

2017-06-17 12_32_31-Clipboard.png

Excelに結合したデータの一覧が読み込まれます。

2017-06-17 12_32_59-Clipboard.png

Webサービスから読み込む

Webサービスから読み込みます。サンプルに用いているのはQiitaの記事投稿数が多いタグを取得するAPIです。

手順

Webから をクリックします。

2017-06-17 12_33_14-Clipboard.png

APIのURLを指定します。

2017-06-17 12_33_26-Clipboard.png

テーブルへの変換 をクリックします。
JSONはPowerQuery内でリストとして取り込まれ、テーブルに変換することで加工可能になります。

2017-06-17 12_34_14-Clipboard.png

必要に応じて区切り記号など選択しOKをクリックします。

2017-06-17 12_34_40-Clipboard.png

テーブル形式に変換されるので、列タイトルにある展開ボタンをクリックします。

2017-06-17 20_56_51-Clipboard.png

テーブルが展開されたら、閉じて読み込むをクリックします。

2017-06-17 12_35_24-Clipboard.png

ExcelにAPIから取得したデータが読み込まれます。

2017-06-17 12_35_48-Clipboard.png

その他の機能について

PowerQueryは読み込んだデータを変換・加工することで真価を発揮します。
Excelの数式で実現できることは一通りPowerQueryでも実装されており、
クエリーエディタ上で手順をプレビューしながら変換・加工していくことができます。

PowerQueryでできることの例

下記は一例ですが、画面操作だけで他にも様々な処理を定義することができます。

  • 必要な行・列のみを取得する
  • 値を加工して出力する(LEFT,MID,RIGHT関数に相当)
  • 特定の条件に該当する行にマークをつける(IF関数に相当)
  • 複数の行をグループ化して集計する
  • ピボット集計する/ピボット集計されたデータを元に戻す
  • 複数のクエリを参照して新しいクエリを作成する
  • IDを使って複数のクエリを結合する(VLOOKUP関数に相当)
  • 特定の加工をまとめて実施するカスタム関数を定義する
  • ファイルパスなどの変化する値をパラメータ化する
  • データソースにセキュリティをかけ、アクセス制限を行う

また、PowerQueryで作成したクエリはM言語(PowerQueryFormulaLanguage)のプログラムとして出力されます。
GUI上にない処理であっても、M言語を駆使すると自由にクエリを組み立てられるようになります。
クエリを編集するには 詳細エディター を開いてください。

2017-06-17 12_49_47-.png

下記は Webサービスから読み込む で出力されたプログラムです。

2017-06-17 12_48_09-詳細エディター.png

役に立つリンク

基本的な情報

Power Query - 概要と学習

Microsoftの公式サイトです。日本語訳がわかりにくいこともありますが、情報が豊富に載っています。

Road to Cloud Office

PowerQueryに関する情報が日本語で書かれています。
解説もアニメーションGIFを交えて丁寧に行われていてわかりやすいです。

M言語の情報

Power Query メモ

日本語で書かれたM言語の関数およびデータ型のリファレンスです。
必要な関数を検索するときに使っています。
日本語訳はartfulplace.netさんが実施されています。

Power Query M function reference

Microsoftの公式関数リファレンスです。
日本語訳がなくExampleが少ないのが難点ですが、全ての関数を網羅しています。

Chris Webb's BI Blog
The Ken Puls (Excelguru) Blog
Matt Masson

M言語のサンプルが豊富に載っています。
GUIだけでは実現できない高度なデータ収集手順を作成するのに役に立ちます。

Github:pquery

M言語のカスタム関数集です。
標準の関数で実現できない処理があればここを探してみています。