Help us understand the problem. What is going on with this article?

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言語のカスタム関数集です。
標準の関数で実現できない処理があればここを探してみています。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした