ようこそ！このページに来られた方は Power Query に興味を持たれたのだと思います。
Power Query（パワークエリ）はExcelの比較的新しい機能で、データ処理の強力なツールです。非常に高機能にもかかわらずマウス操作だけで誰でも簡単に使えます。
百聞は一見に如かず！ 下の動画をご覧ください。
いかがですか？こんなに簡単に仕事がこなせたらかなり楽ではありませんか？
Power Query で一度データの処理手順を作成したら、次回以降は全自動で処理してくれるので、大幅に仕事の効率を上げることができます。
この記事ではPower Queryの使い方を 導入知識から高度な使い方までを解説 します。
この記事は以下の３部構成の中の導入編です。導入編だけでも一通りの使い方が分かるようになっています。更にレベルアップしたい場合は次の記事に読み進んで下さい。
- 導入編： 必須の予備知識、まずは触ってみる！【チュートリアル】
- 応用編： 実務ですぐに使える自動集計 【チュートリアル】
- 上級編： 高度な使い方
Power Queryとは？
冒頭にもご紹介したように、Power Queryはデータ処理のツールです。SUM関数などのExcel関数の処理対象が セル なのに対して、Power Queryは データ丸ごと が処理対象になります。
これだけ見ても Power Query の処理が強力そうに見えますね。
実際この データを丸ごと処理できる能力が非常に強力 で、データベースシステムをExcelだけで構築できてしまうほどです。
Power Query の処理は下の図のように大きく分けて３つのステップになっています。
STEP１：データ取得では様々なデータを取得できることが分かります。特に実務では共有フォルダに保管したファイルからデータを取得できる機能が大変便利です。
STEP２：Excel関数と同じように文字列を加工したり、計算したりできる他に、データ同士をキーを介して結合する処理が可能です。これはExcelの関数では VLOOKUP など色々と駆使しなければならず非常に手間な操作なのですが、Power Queryではクリック数回で簡単にできてしまいます。
STEP3：データを欲しい形にする ステップです。表やグラフなどExcelの機能で様々な見せ方が可能です。
Excel関数 や マクロVBA より優位
Excelの関数を使えばある程度は似たことができますが、必ず手作業が入ってきてしまい結局手間が減りません。
パワークエリは人の手作業を入れずに完全に自動化できるところが利点なのです。
マクロVBAでも自動化は可能です。しかし、マクロVBAで自動化のプログラムを作成するのはスキルが必要ですし、どんな熟練者でも時間がかかってしまいます。
一方、Power Query はマウス操作だけ自動化が完成してしまいます。
ですから Power Query で出来る処理であればマクロより Power Query の方が圧倒的に効率的なのです。
「データ」の形の秘密
Power Queryはご紹介した通り大変強力な機能ですが、それには少し秘密があるのです。それは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 エディターを閉じますが、閉じる際にコツがあります。画像のように 「ホーム」→「閉じて読み込む」→「閉じて次に読み込む…」 とするとダイアログが出てきますので、ここで「接続の作成のみ」を選択してください。これをしておかないと読み込んだデータが再び別のシートに出力されてしまうのです。
- 同じ手順で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シートに出力する強力なツールです。さらに、データが更新された際には、手間をかけずに集計を更新することが可能です。
それぞれの手順において、以下の操作や概念を学びました：
- データ変換：データを特定の条件に基づいて変換する操作。
この例では、「販売数」の表と製品情報の表を結合したり、「製品カテゴリ」ごとの「売上」を集計しました。 - データ出力：処理結果をExcelシートに表示させる操作。
新規ワークシートが作成され、集計結果が表示されました。 - データ更新：新たに追加されたデータを考慮に入れて集計を更新する操作。
Power Queryは一手間で全ての作業手順を自動で実行してくれます。
これらの概念を理解し、適切に使用することで、大量のデータを効率的に扱うことが可能となります。
Power QueryはExcelの強力な機能の一つであり、その利用はデータ処理の効率化に大いに貢献します。これからも学習を続けて、その全ての機能を最大限に活用してください。
