Power Query（パワークエリ）とは、Excel に標準搭載されたデータ処理ツールです。
毎月の集計作業に何時間もかかっている、複数ファイルを手作業でコピー＆ペーストしている――そのような業務を 自動化できる のが Power Query です。
複数ファイルの一括取込みや大量データの結合、整形処理までを、基本的には マウス操作だけ で実行できます。
一度仕組みを作ってしまえば、従来1週間以上かかっていた集計作業が、「更新」ボタンひとつで完了 するようになります。
本記事では「Power Query とは何か？」という基本から、実務で使える具体的な操作方法 までを図解付きで分かりやすく解説します。
初心者の方でも、実際の業務で活用できるレベルまで理解できる構成になっていますので、これから Power Query を学びたい方はぜひ最後までご覧ください。
Power Query とは？
どれだけ便利なツールかは、「百聞は一見に如かず」 まずは下の動画をご確認ください。
ユーザーの操作は ファイルを保管 して Excel の 「更新」ボタンを押すだけ です。
この操作だけで、新しいデータの取り込み ／ データ加工 ／ 集計 ／ グラフの更新 という一連の処理が全て自動で終わってしまいます。
作業の効率化どころか、作業自体が無くなってしまっていると言っても過言ではありません。
一体これはどんな仕組みになっているのでしょうか？
Power Query ができること
Power Query は図のような ３ステップ でデータ処理を行います。
Power Query は一度ユーザーが操作した内容を全て記憶しており、次回からはボタン一つで実行できます。
これにより 複雑で何ステップにも渡るデータ処理の作業を全自動化 することが可能なのです。
STEP1 データ取得
- ファイル や データベース など様々なデータ源からデータ取得できます。
- 特に フォルダ内の 複数ファイルをまとめて処理 できる機能が大変便利です。
- 読み取り専用 で開くので データ破損の心配がありません。
STEP2 データ加工
- マウス操作のみ でデータ加工ができます。しかもユーザーの操作を記憶して 次回から自動実行 します。
- Excel 関数には無い ダイナミックなデータ加工が可能です。
- VLOOKUPよりも強力 なデータ結合機能があります。
STEP3 データ出力
- Power Pivot と連携させると高度なデータ分析が可能です。
- 使い慣れた Excel の 表やグラフなど に簡単に出力できます。
どんな仕事に使えるか？
強力な Power Query ですが、具体的にどんな場面で利用できるのでしょうか？
複数の Excel ファイル を自動集計
各部署や店舗から送られてくる Excel ファイルを、フォルダに入れるだけで自動集計できます。
従来はコピー＆ペーストしていた作業を完全自動化できます。
CSV や システムデータを自動整形・取込み
形がそれぞれ異なるような業務システムからのダウンロードデータを自動で整形して取り込むことができるようになります。
一度データ処理を組んでしまえば次回からは「更新」ボタン一つでデータ取込みから整形まで一気に処理してくれます。
複数のデータを結合する（VLOOKUPの代替）
商品IDなどのキー列データに基づいて複数のデータを結合する処理をVLOOKUPより簡単かつ、安定動作で結合することが可能です。
人の操作ミスが無くなり、業務のミスを激減させることが可能です。
データの集計・分析用データを作る
Power Query は分析のためのデータ作成にも最適です。
大量のデータを読み込ませた上でデータ加工をして分析用データを仕上げるのもマウス操作で非常に簡単に完了させることができます。
Excel関数 や VBAマクロ より優位
Power Query は新しい機能だけあって、様々な面で機能が洗練されています。
Excel関数 や VBAマクロ と比較すると、下の表の通り、自動化、学習の負担 の観点で圧倒的に優位です。
ハードルが低い上に高機能 ということで、これは使わない手はありません。
「データ」のカタチの秘密
Power Queryはご紹介した通り大変強力な機能ですが、それには少し秘密があるのです。
それはPower Queryが扱う「データ」のカタチ です。
Power Query で主に扱うデータは 下の図のようなルール に従ったものになります。
何故なら、コンピュータが最も処理し易い形だから です。ですから、データをこの形にすることで コンピューティングパワーの恩恵を受けることが可能になる のです。
お持ちのデータがこの形になっていなくても心配ありません、Power Query には 強力なデータ加工機能 があり、データのカタチを整えることができるのです。
このことを良く理解しておくと、Power Query の習得がスムーズになりますので是非覚えておいてください。
まずはさわってみよう！はじめてのチュートリアル 【例題1】
ではいよいよPower Queryを実際に使っていきましょう。この例題を一通りこなすだけで Power Query がいかに強力なツールであるかを実感して頂けると思います。
では、例題です。図をご覧ください。
この例題のサンプルファイル は以下のリンクからダウンロードして入手して下さい。
さて、この例題をご覧になって以下に気付かれたかと思います。
- 売上を計算するには表1 の 販売数 と 表2 の 価格 を掛けないといけない
- 製品カテゴリは 表2 にしかない
つまり表1の情報と表2の情報を うまく繋げなければならない わけです。
Excel 関数 でやる場合は VLOOKUP関数 などを使った上で、SUMIF関数 で集計とかなり面倒なことになります。しかし、Power Queryなら非常にスマートに解決 してくれます。
具体的な操作に入る前にPower Queryでの処理の手順を整理します。次のように処理していきます。
データ取得
- ２つの表（テーブル）をPower Queryに 読み込む 【クエリ作成】
データ加工
- 作成した２つのクエリを 製品IDをキーに結合 する 【結合】
- 販売数 と 価格 を掛けて売上を計算する 【掛け算】
- 製品 カテゴリー毎に売上を集計 する 【グループ集計】
データ出力
- Excelのシートに 処理結果を出力 する
- データを更新して集計が更新されることを確認する
一見するとかなり面倒そうな処理ですが、Power Queryなら簡単ですのでご安心下さい。では早速 Excel を操作していきます。
表を読み込む ＜データ取得＞
まずダウンロードしたサンプルファイルを開いて下さい。続いて次のように操作します。
操作手順
- Sheet1の 表のどこかのセルを選択した状態 にしておきます。
- メニューから「データ」を選択、左の方にある「テーブルの取得と変換」を押すと Power Query エディター が開きます。これでテーブルの内容が Power Query に取り込まれました。
- 一旦ここで Power Query エディターを閉じますが、閉じる際にコツがあります。画像のように 「ホーム」→「閉じて読み込む」→「閉じて次に読み込む…」 とするとダイアログが出てきますので、ここで「接続の作成のみ」を選択 してください。Power Query エディター を ×印ボタン で閉じた場合、読み込んだデータが再び別のシートにテーブルとして出力されてしまう のです。
- 同じ手順でSheet2の表も Power Queryに読み込みます。
この操作でこれまでの Excel 操作では見たことのなかった 「Power Query エディター」というウィンドウが開いたと思います。
これから Power Query を使う際に毎回操作するウィンドウですので、ここで簡単に解説させていただきます。
Power Query エディターのウィンドウ構成は図のようになっています。
- メニュー： Power Query で実行できる様々なデータ処理のボタンが並んでいます。Excel 関数 と違って、コマンドを覚えておく必要は無く、マウス操作で利用可能です。
- プレビュー： 画面中央はデータが表示されていますが、セルを直接編集することはできません。セルを一つずつ編集するのではなく、メニューにある機能でデータ全体を一括で処理していきます。
- 適用したステップ： Power Query では次々とデータに処理を加えていきます。この処理手順は自動的に右側のサイドウィンドウに記録されていきます。この処理の手順を記録しておくことで、次回以降はこれを自動で実行することが可能になるのです。
- クエリ一覧： Power Queryで作成した一連のデータ処理は「クエリ」と呼びます。作成したクエリの一覧を表示しています。クリックするとそのクエリを編集できます。
解説
今回の例題ではファイル内のテーブルからデータを Power Query で取得しました。
Power Query は様々なデータ取得が可能で、特にフォルダに保管されたファイルから一括してデータを取得する機能が大変便利です。
フォルダからのデータ取得について興味のある方は フォルダからデータ取得の解説記事 をご覧ください。
クエリを結合する ＜データ加工＞
2つの表を Power Query に取り込んだら図のような状態になっていると思います。
ウィンドウの右に「クエリと接続」とあって、そこに作成したクエリ「テーブル１」と「テーブル２」が表示されています。
これが Power Query に読み込んだデータ を示しています。
「テーブル１」と「テーブル２」が作成されたクエリの名前ですが、これは作成する元となったテーブルの名前がそのままクエリの名前になっています。
下の動画のように操作して２つのクエリを結合します。
操作手順
- クエリと接続の欄にある「テーブル１」を 右クリック
- 出てきたダイアログから「結合」を選択
- 子ウィンドウの上側にはテーブル１が選択されています。下のプルダウンから テーブル２を選択 します。
- 続いて 結合するキー項目を指定 します。テーブル１、テーブル２の「製品ID」を選択して「OK」ボタン を押します。
- 続いて テーブル２ の項目から 表示する項目を選択 します。
- 「テーブル２」の右に折れ曲がった２つ矢印 があるのでこれをクリックします。
- メニューで必要な「製品カテゴリー」と「価格」のみにチェック を入れます。
- ここでもう一つコツがあります、「元の列名をプレフィックスとして使用します」という欄にチェックが入っていますが、通常ではこれは 不要なのでチェックを外しておきます。
- 「OK」ボタンを押すと、テーブル１ と テーブル２ のデータが 結合されて出力 されます。
解説
VLOOKUP関数を使う場合、関数の入力ミスが起きやすかったり、検索アンマッチ時の処理が複雑であったりして不便な点が多いです。
一方で、Power Query のテーブル結合は、簡単で処理も安定していて動作に不安がありません。
テーブル結合について詳しく知りたい方は Power Query テーブル結合記事 をご覧下さい。
販売数 と 価格 を掛けて売上を計算する【掛け算】＜データ加工＞
２つの表が結合されたおかげで、販売数と価格が同じテーブルに表示されています。
同じテーブルに「販売数」と「価格」が入りましたので、これで売上の計算をします。画像のように操作して下さい。
操作手順
- 「販売数」の項目名をクリックして列全体を選択します。
- 続いて、コントロールキーを押しながら「価格」の項目名をクリック して、「販売数」と「価格」の２つの列を選択した状態にします。
- メニューから「列の追加」→「標準」→「乗算」をクリックすると、列が追加されて売上の値が表示されます。
- 列の名前 が「乗算」となってしまっているので、ダブルクリックして「売上」に書き換え ます
カテゴリー毎に集計する【グループ集計】＜データ加工＞
売上が計算できましたので、最後に製品カテゴリ毎に集計します。画像のように操作して下さい。
操作手順
- メニューから「変換」をクリック
- 一番右にある「グループ化」をクリック
- 子ウィンドウで上の段はグループ化する項目ですので、ここは「製品カテゴリ」を選択 します。
- 下の段はグループ化の対象の項目です、ここでは 売上をグループ毎に集計したい ので、「操作」は「合計」、「列」に「売上」を選択して、「新しい列名」は「売上」にしておきましょう。
- 「OK」ボタンを押すとあっという間に製品カテゴリ毎の集計表ができてしまいました。
解説
ここで、「グループ化」とは何かを解説します。グループ化とは、特定のカテゴリーに基づいてデータをまとめることを指します。この例では、「製品カテゴリ」に基づいてデータをまとめ、「売上」をそのグループごとに合計しています。
Excelのシートに処理結果を出力する ＜データ出力＞
いよいよ最終工程です。Power Query エディタ で仕上げた計算結果をExcelのシートに出力させます。これも画像のように操作して下さい。
操作手順
- メニューから「ホーム」をクリック
- 「閉じて読み込む」→「閉じて次に読み込む…」をクリック
- 子ウィンドウで ブックでの表示に「テーブル」が選択されていることを確認、データを返す先は「新規ワークシート」になっていることを確認して、「OK」ボタンを押します。
- 新しいシートが作成されて集計結果が無事出力されました。
解説
Power Query で処理したデータは Excel のテーブル以外にも出力が可能です。
Power Pivot という従来のピボットテーブルを大幅に強化したツールにデータを繋いで高度なデータ分析をすることができます。
Power Pivot は DAX関数 という高度なプログラム言語も使えるデータ分析ツールです。
Power Pivot の入門は Power Pivot への導入記事 にまとめていますので、興味があれば参照してみて下さい。
「更新」ボタン でデータ処理を再実行 ＜データ出力＞
実際にはデータは日々に更新されていくと思いますが、Power Queryは従来の関数よりもずっとスマートに対応できますので、これを試していきましょう。
下の図のようにデータを追加してみて下さい。
データが追加され、更に製品カテゴリも増えてしまいました。従来の関数で集計表を作成している場合、新しいカテゴリが追加された場合は、それに合わせた集計を追加しなければなりません。
しかし、Power Queryはこれも自動で処理 してくれます。
では、以下のように操作して下さい。
操作手順
- Power Queryで出力した 集計表のセルを右クリック
- 出てきたメニューから「更新」を選択
- すると、計算が回って 集計表が自動的に更新 されました。
解説
「更新」とは、Power Query のデータ処理を最初からもう一度やり直す操作 になります。
この機能により、データがどのように更新されても、ワンクリックで全ての作業手順を自動で実行してくれる のです。
プログラムを組まなくても複雑で何ステップもあるデータ処理を自動化することができます。
ここが Power Query の素晴らしいポイントです。
Power Query のよくある質問
Q. Power Query は無料？
Excelの標準機能ですので、Excelが使える環境なら追加費用は無料です。
VBA との違いは？
VBA はプログラムによりかなり柔軟な動作が可能ですが、プログラミングの負担が非常に大きいです。
一方で Power Query ではマウス操作したものを自動で記録して、「更新」ボタン一つで再実行が可能で、プログラミングの負担をゼロにすることが可能です。
また、VBA は様々な機能を持っていますが、Power Query はデータ処理に特化したものになっています。
Power BI との違いは？
Power BI はデータの可視化の機能に特化しています。データを見る、Webで広く共有するという目的に対しては非常に有効です。
一方で Power Query は Excel内で動くので従来のExcel 関数を使った計算も併用できます。データを多くの人に見せたいというニーズが無ければ Power Query の方がやれることが多いと思います。
Power Query はどんな人にオススメ？
事務、経理、営業 の方や、これらの方が作成したデータを分析する経営企画のような方にはもはや使わないと時代に取り残されてしまうレベルの必須のツールです。
まとめ
このガイドを通して、Power Queryの導入知識と基本的な使い方について学んできました。
Power Queryは、データを取得し、必要な形に変換し、そしてそれをExcelシートに出力したり、Power Pivot へ接続できる強力なツールです。
さらに、データが更新された際には、手間をかけずに集計を更新することが可能です。
チュートリアルの例題では、それぞれの手順において、以下の操作や概念を学びました：
- データ変換：データを特定の条件に基づいて変換する操作。
この例では、「販売数」の表と製品情報の表を結合したり、「製品カテゴリ」ごとの「売上」を集計しました。 - データ出力：処理結果をExcelシートに表示させる操作。
新規ワークシートが作成され、集計結果が表示されました。 - データ更新：新たに追加されたデータを考慮に入れて集計を更新する操作。
Power Queryは一手間で全ての作業手順を自動で実行してくれます。
これらのを理解し、活用することで、データを効率的に扱うことが可能となります。
Power QueryはExcelの強力な機能であり、活用できるようになれば データ処理業務の強化に大いに貢献 します。これからも学習を続けて、その全ての機能を最大限に活用してください。
次のステップ
この記事は導入編でした。さらに学習を続けたい方にオススメは 実践チュートリアル記事 です。
導入編よりもさらに難しい例題を Power Query で処理していくチュートリアルになっています。
マウス操作だけで高度なデータ処理ができるので Power Query を使うことが面白くなってくると思います。是非トライしてみて下さい。
【図で分かる！】Power Query入門シリーズ記事一覧
パワフルで使いやすい Power Query は知れば知るほど便利になってきます。
【図で分かる！】 Power Query 入門シリーズ の記事では Power Query の基礎から応用、活用法までを紹介していますので、是非参考にしてみて下さい。
