ようこそ！このページに来られた方は Power Query に興味を持たれたのだと思います。Power Query は非常に便利かつ高性能なツールですので、「知らないと大損！」と断言できると筆者は考えています。
ですから、皆さんに是非 Power Query について知って欲しいと思います。
ここではPower Queryについて 導入知識から高度な使い方までを3回に分けて解説 します。
この記事は以下の３部構成の中の導入編です。導入編だけでも一通りの使い方が分かるようになっています。更にレベルアップしたい場合は次の記事に読み進んで下さい。
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の関数を使えばある程度は似たことができますが、必ず手作業が入ってきてしまい結局手間が減りません。
パワークエリは人の手作業を入れずに完全に自動化できるところが利点なのです。
マクロVBAでも自動化は可能です。しかし、マクロVBAで自動化のプログラムを作成するのはスキルが必要ですし、どんな熟練者でもプログラム作成に時間と労力がかかってしまいます。
一方、Power Query はマウス操作だけ自動化が完成してしまいますので、業務効率がケタ違いに高いと言えます。
「データ」のカタチの秘密
Power Queryはご紹介した通り大変強力な機能ですが、それには少し秘密があるのです。それはPower Queryが扱う「データ」のカタチ です。
一般にデータと言えば電子上のもの全てを指します。しかし、Power Query で主に扱うデータは下の図のようなルールに従ったものになります。
何故なら、この形のデータはコンピュータで処理し易いのです。ですから、データをこの形にすることでコンピューティングパワーの恩恵を受けることが可能になるのです。
ただ、お持ちのデータがこの形になっていない場合も心配ありません、Power Query は非常に多くのデータ加工機能があり、データのカタチを整えることができるのです。
このことを良く理解しておくと、Power Queryの習得がスムーズになりますので是非覚えておいてください。
Power Query の導入方法
Excel 2016 以降は標準搭載
Power Query は、Excel 2016以降のバージョンでは標準搭載されていますのでそのまま利用可能です。「データ」タブに「取得と変換」グループが表示されていることを確認して下さい。Power Query はここから利用できます。
Excel 2010, Excel 2013 ではアドインをインストール
Power Query は、Excel 2010以降のバージョンで利用可能ですが、アドインをインストールする必要があります。 Microsoft ダウンロードセンター から Microsoft Power Query for Excel のインストーラをダウンロードしてインストールして下さい。
Power Query を有効にする手順
Power Query のアドインをインストールしたら以下の手順で有効にします。既に有効になっているならそのままでOKです。
- Excelを開く：Excelを起動します。
- アドインを確認：「ファイル」→「オプション」→「アドイン」→「COMアドイン」を選択し、「Microsoft Power Query for Excel」を有効にします。
まずはさわってみよう！ Power Query チュートリアル ：例題1
ではいよいよPower Queryを実際に使っていきましょう。この例題を一通りこなすだけでPower Queryが強力なツールであることを実感して頂けると思います。
例題に入る前に、Power Query の編集で使う Power Query エディター のウィンドウについて解説しておきます。
データの取り込みをすると自動的に Power Query エディター のウィンドウが開きます。
画面は図のようになっていて、適用したステップ とある部分にユーザーがデータ処理を加えるごとに自動で処理に名前がつけられて記録されていくところが通常のExcelには無い部分です。
では、例題です。図をご覧ください。
このデータの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 エディターを閉じますが、閉じる際にコツがあります。画像のように 「ホーム」→「閉じて読み込む」→「閉じて次に読み込む…」 とするとダイアログが出てきますので、ここで「接続の作成のみ」を選択してください。これをしておかないと読み込んだデータが再び別のシートに出力されてしまうのです。
- 同じ手順でSheet2の表も 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 でスイスイ処理していくチュートリアルなど、深く知ることでより面白くなってくると思いますよ！
