Edited at

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

More than 1 year has passed since last update.


はじめに

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言語のカスタム関数集です。

標準の関数で実現できない処理があればここを探してみています。