ようこそ！このページに来られた方は Power Query に興味を持たれたのだと思います。Power Query は非常に有用なツールですので、「知らないと大損！」と断言できると筆者は考えています。
ですから、皆さんに是非 Power Query について知って欲しいと思います。
この Power Query 入門 は 導入知識から高度な使い方までを解説 します。
この記事は 導入編 です。導入編だけでも一通りの使い方が分かるようになっています。更にレベルアップしたい場合は次の記事に読み進んで下さい。
Power Queryとは？
Power Query（パワークエリ）はExcelの比較的新しい機能で、データ処理の強力なツールです。非常に高機能にもかかわらずマウス操作だけで誰でも簡単に使えるツールです。
どれだけ有用なツールかは、まずは 百聞は一見に如かず 下の動画をご確認ください。
いかがですか？ こんなに簡単に仕事がこなせたら かなり楽ではありませんか？
Power Query で一度データの処理手順を作成したら、次回以降は全自動で処理してくれる ので、大幅に仕事の効率を上げることができます。
Power Query の活用例
Power Query は様々なデータ処理業務で活用できます。例えば図のような業務に有効です。
Power Query はデータを丸ごと処理する
Power Queryは Microsoft Excel に組み込まれたデータ取り込みおよび、データ操作のツールです。SUM関数などのExcel関数 は セル を処理するのに対して、Power Queryは データ丸ごと一気に処理 します。
実際この データを丸ごと処理できる能力が非常に強力 で、データベースシステムを Excel だけで構築できてしまうほどです。
Power Query 利用の３つのステップ
Power Query を利用した処理は、下の図のように大きく分けて３つのステップになっています。
STEP１ データ取得
様々なデータを取得できることが分かります。特に実務では フォルダ内の複数のファイルからデータを取得 できる機能が大変便利です。更に、Power Query では元データは読み取るだけで、変更することは無いので、元データを破壊する心配が無い のも安心です。
STEP２ データ加工
Excel関数と同じように 文字列を加工したり、計算したり できる他に、データ同士をキー列で結合 する処理が可能です。この処理は、Excel関数では VLOOKUP関数 などを駆使する必要があり、手間で処理も重たい のですが、Power Queryではクリック数回で簡単かつ高速 に処理できてしまいます。
STEP3 データ出力
データを欲しい形に見える化する ステップです。表やグラフなどExcelの機能で様々な見せ方が可能です。これは Power Query だけでなく、Power Pivot も活用すると更に強力です。
Excel関数 や VBAマクロ より優位
Excelの関数 を使う場合、必ず手作業が必要 で自動化できません。
一方、Power Query は完全に自動化できます。
VBAマクロ でも自動化は可能です。しかし、 プログラム作成の スキル、時間、労力 が必要です。
一方、Power Query はマウス操作だけで自動化を構築できます。
このように Power Query は Excel関数 や VBAマクロ より格段に作業効率が高く、優位なのです。
「データ」のカタチの秘密
Power Queryはご紹介した通り大変強力な機能ですが、それには少し秘密があるのです。それはPower Queryが扱う「データ」のカタチ です。
一般にデータと言えば電子上のもの全てを指します。しかし、Power Query で主に扱うデータは 下の図のようなルール に従ったものになります。
何故なら、この形のデータは コンピュータで処理し易い のです。ですから、データをこの形にすることで コンピューティングパワーの恩恵を受けることが可能になる のです。
ただ、お持ちのデータがこの形になっていない場合も心配ありません、Power Query には 強力なデータ加工機能 があり、データのカタチを整えることができるのです。
このことを良く理解しておくと、Power Query の習得がスムーズになりますので是非覚えておいてください。
まずはさわってみよう！チュートリアル ：例題1
ではいよいよPower Queryを実際に使っていきましょう。この例題を一通りこなすだけで Power Query がいかに強力なツールであるかを実感して頂けると思います。
では、例題です。図をご覧ください。
このデータの Excel ファイル は以下のリンクからダウンロードして入手して下さい。
さて、この例題をご覧になって以下に気付かれたかと思います。
- 売上を計算するには表1 の 販売数 と 表2 の 価格 を掛けないといけない
- 製品カテゴリは 表2 にしかない
つまり表1の情報と表2の情報を うまく繋げなければならない わけです。
Excel 関数 でやる場合は VLOOKUP関数 などを使った上で、SUMIF関数 で集計とかなり面倒なことになります。しかし、Power Queryなら非常にスマートに解決してくれます。
具体的な操作に入る前にPower Queryでの処理の手順を整理します。次のように処理していきます。
データ取得
- ２つの表（テーブル）をPower Queryに 読み込む 【クエリ作成】
データ加工
- 作成した２つのクエリを 製品IDをキーに結合 する 【結合】
- 販売数 と 価格 を掛けて売上を計算する 【掛け算】
- 製品 カテゴリー毎に売上を集計 する 【グループ集計】
データ出力
- Excelのシートに 処理結果を出力 する
- データを更新して集計が更新されることを確認する
一見するとかなり面倒そうな処理ですが、Power Queryなら簡単ですのでご安心下さい。
２つの表を Power Query に読み込む ＜データ取得＞
まずダウンロードしたファイルを開いて下さい。続いて次のように操作します。
操作手順
- Sheet1の 表のどこかのセルを選択した状態 にしておきます。
- メニューから「データ」を選択、左の方にある「テーブルの取得と変換」を押すと Power Query エディター が開きます。これでテーブルの内容が Power Query に取り込まれました。
- 一旦ここで Power Query エディターを閉じますが、閉じる際にコツがあります。画像のように 「ホーム」→「閉じて読み込む」→「閉じて次に読み込む…」 とするとダイアログが出てきますので、ここで「接続の作成のみ」を選択 してください。Power Query エディター を ×印ボタン で閉じた場合、読み込んだデータが再び別のシートにテーブルとして出力されてしまう のです。
- 同じ手順でSheet2の表も Power Queryに読み込みます。
Power Query エディター について
この操作でこれまでの Excel 操作では見たことのなかった 「Power Query エディター」というウィンドウが開いたと思います。
これから Power Query を使う際に毎回操作するウィンドウですので、ここで簡単に解説させていただきます。
Power Query エディターのウィンドウ構成は図のようになっています。
- メニュー： Power Query で実行できる様々なデータ処理のボタンが並んでいます。Excel 関数 と違って、コマンドを頭で覚えておく必要は無く、マウス操作で利用可能です。
- データ： 画面中央は通常の Excel の画面と同様にデータが表示されていますが、セルを直接編集することはできません。セルを一つずつ編集するのではなく、メニューにある機能でデータ全体を一気に処理していきます。
- 適用したステップ： Power Query では次々とデータに処理を加えていきます。この処理手順は自動的に右側のサイドウィンドウに記録されていきます。この処理の手順を記録しておくことで、次回以降はこれを自動で実行することが可能になるのです。
作成した２つのクエリを製品IDをキーに結合する ＜データ加工＞
2つの表を Power Query に取り込んだら図のような状態になっていると思います。
ウィンドウの右に「クエリと接続」とあって、そこに作成したクエリ「テーブル１」と「テーブル２」が表示されています。
これが Power Query に読み込んだデータ を示しています。
「テーブル１」と「テーブル２」が作成されたクエリの名前ですが、これは作成する元となったテーブルの名前がそのままクエリの名前になっています。
下の動画のように操作して２つのクエリを結合します。
操作手順
- クエリと接続の欄にある「テーブル１」を 右クリック
- 出てきたダイアログから「結合」を選択
- 子ウィンドウの上側にはテーブル１が選択されています。下のプルダウンから テーブル２を選択 します。
- 続いて 結合するキー項目を指定 します。テーブル１、テーブル２の「製品ID」を選択して「OK」ボタン を押します。
- 続いて テーブル２ の項目から 表示する項目を選択 します。
- 「テーブル２」の右に折れ曲がった２つ矢印 があるのでこれをクリックします。
- メニューで必要な「製品カテゴリー」と「価格」のみにチェック を入れます。
- ここでもう一つコツがあります、「元の列名をプレフィックスとして使用します」という欄にチェックが入っていますが、通常ではこれは 不要なのでチェックを外しておきます。
- 「OK」ボタンを押すと、テーブル１ と テーブル２ のデータが 結合されて出力 されます。
販売数 と 価格 を掛けて売上を計算する【掛け算】＜データ加工＞
２つの表が結合されたおかげで、販売数と価格が同じテーブルに表示されています。
同じテーブルに「販売数」と「価格」が入りましたので、これで売上の計算をします。画像のように操作して下さい。
操作手順
- 「販売数」の項目名をクリックして列全体を選択します。
- 続いて、コントロールキーを押しながら「価格」の項目名をクリック して、「販売数」と「価格」の２つの列を選択した状態にします。
- メニューから「列の追加」→「標準」→「乗算」をクリックすると、列が追加されて売上の値が表示されます。
- 列の名前 が「乗算」となってしまっているので、ダブルクリックして「売上」に書き換え ます
製品カテゴリー毎に売上を集計する【グループ集計】＜データ加工＞
売上が計算できましたので、最後に製品カテゴリ毎に集計します。画像のように操作して下さい。
操作手順
- メニューから「変換」をクリック
- 一番右にある「グループ化」をクリック
- 子ウィンドウで上の段はグループ化する項目ですので、ここは「製品カテゴリ」を選択 します。
- 下の段はグループ化の対象の項目です、ここでは 売上をグループ毎に集計したい ので、「操作」は「合計」、「列」に「売上」を選択して、「新しい列名」は「売上」にしておきましょう。
- 「OK」ボタンを押すとあっという間に製品カテゴリ毎の集計表ができてしまいました。
解説
ここで、「グループ化」とは何かを解説します。グループ化とは、特定のカテゴリーに基づいてデータをまとめることを指します。この例では、「製品カテゴリ」に基づいてデータをまとめ、「売上」をそのグループごとに合計しています。
Excelのシートに処理結果を出力する ＜データ出力＞
いよいよ最終工程です。Power Query エディタ で仕上げた計算結果をExcelのシートに出力させます。これも画像のように操作して下さい。
操作手順
- メニューから「ホーム」をクリック
- 「閉じて読み込む」→「閉じて次に読み込む…」をクリック
- 子ウィンドウで ブックでの表示に「テーブル」が選択されていることを確認、データを返す先は「新規ワークシート」になっていることを確認して、「OK」ボタンを押します。
- 新しいシートが作成されて集計結果が無事出力されました。
解説
この操作で「新規ワークシート」が作成され、そこに集計結果が表示されます。「新規ワークシート」とはExcelの新しいシートのことで、データを整理しやすくするために使用されます。
データを更新して集計が更新されることを確認する ＜データ出力＞
実際にはデータは日々に更新されていくと思いますが、Power Queryは従来の関数よりもずっとスマートに対応できますので、これを試していきましょう。
下の図のようにデータを追加してみて下さい。
データが追加され、更に製品カテゴリも増えてしまいました。従来の関数で集計表を作成している場合、新しいカテゴリが追加された場合は、それに合わせた集計を追加しなければなりません。
しかし、Power Queryではこれについても自動で対応してくれる のです。
では、以下のように操作して下さい。
操作手順
- Power Queryで出力した 集計表のセルを右クリック
- 出てきたメニューから「更新」を選択
- すると、計算が回って 集計表が自動的に更新 されました。
解説
ここで、「更新」とは何かを説明しましょう。「更新」とは、Power Query のデータ処理を最初からもう一度やり直す操作 になります。
この機能により、データがどのように更新されても、ワンクリックで全ての作業手順を自動で実行してくれる のです。
いかがでしょうか？ここがPower Queryの素晴らしい機能だと思います。
まとめ
このガイドを通して、Power Queryの導入知識と基本的な使い方について学んできました。
Power Queryは、データを取得し、必要な形に変換し、そしてそれをExcelシートに出力したり、Power Pivot へ接続できる強力なツールです。さらに、データが更新された際には、手間をかけずに集計を更新することが可能です。
チュートリアルの例題では、それぞれの手順において、以下の操作や概念を学びました：
- データ変換：データを特定の条件に基づいて変換する操作。
この例では、「販売数」の表と製品情報の表を結合したり、「製品カテゴリ」ごとの「売上」を集計しました。 - データ出力：処理結果をExcelシートに表示させる操作。
新規ワークシートが作成され、集計結果が表示されました。 - データ更新：新たに追加されたデータを考慮に入れて集計を更新する操作。
Power Queryは一手間で全ての作業手順を自動で実行してくれます。
これらのを理解し、活用することで、データを効率的に扱うことが可能となります。
Power QueryはExcelの強力な機能であり、活用できるようになれば データ処理業務の強化に大いに貢献 します。これからも学習を続けて、その全ての機能を最大限に活用してください。
この記事は導入編です。更に学習を続けたい方は次の記事に進んでみて下さい。更に難しい例題を Power Query でスイスイ処理していくチュートリアルで、Power Query を使うことがより面白くなってくると思いますよ！
