はじめに

皆さんはPower Query（パワークエリ）をご存じでしょうか？
Microsoftのツールで、データの抽出、変換、読み込み (ETL) 処理を実行できます。
ExcelやPower BIで利用可能で、プログラミングの知識が無くてもデータベースやファイル、Excelシートなどさまざまなデータソースからデータを取得して、簡単にETL処理を実装できます。
ここでは、簡単なサンプルをもとにExcelでのPower Queryの使用方法を紹介します。

サンプルはこちらからダウンロードしてください。
クリックでダウンロード

Power Queryは、Excelの[データ]タブにある[データの取得]をクリックして[Power Query エディターの起動]を選択すると起動します。

Power Queryでは、クエリという単位でデータを管理します。
データベースやExcelシートなどからデータを取得してクエリを作成し、それを様々な形に加工したり、複数のクエリをマージ（統合）したクエリを作成できます。
その結果はExcelシートへ反映されます。

ここでは、Excelファイルの複数シートからデータを取得して、集計する処理を作成する手順を紹介します。

1. データの取得

サンプルとするExcelファイルには、担当者、部署、商品、販売実績の各シートがあり、次のような項目（列）のデータが格納されているとします。
[担当者]シート：担当者コード、氏名、性別、所属部署コード
[部署]シート：部署コード、部署名
[商品]シート：商品コード、商品名、仕入単価、売価
[販売実績]シート：販売日付、担当者コード、商品コード、販売地域、販売数量

まず[担当者]シートをもとにクエリを作成していきます。
[担当者]シートの項目列を範囲選択します。

次に[データ]タブの下図のアイコンをクリックします。

するとテーブル作成のダイアログが表示されますので、[先頭行をテーブルの見出しとして使用する]にチェックを入れて[OK]をクリックします。

Power Query エディターが立ち上がり、[テーブル1]というクエリが作成されます。

クエリの設定で名前を「担当者」に変更します。
[ホーム]タブの[行の削除]をクリックして[空白行の削除]を選択すると下図のような状態になります。
実行した操作は[適用したステップ]に順番に追加されます。追加されたステップは×ボタンで削除できます。

Power Query エディターを閉じる際、「変更を保持しますか？」と表示されるので[保持]をクリックします。
同様の手順で[部署]シートおよび[商品]シートからもクエリを作成してください。
作成したクエリの結果は新しいシートに出力されますが、不要なシートは削除しても構いません。

2. クエリのマージ（統合）

担当者、部署、商品のクエリを作成したら、次に[販売実績リスト]というクエリを作成します。
[販売実績]シートの項目列を範囲選択して、同様の手順でクエリを作成します。
クエリの設定で名前を「販売実績リスト」に変更して、不要な空白行を削除します。

ここで[販売日付]列が[日付/時刻]型になっているため、日付型に変更します。
自動で追加された[変更された型]のステップを選択し、
テーブルの[販売日付]列を選択して、[ホーム]タブの[データ型]から[日付]を選択します。

「ステップを挿入しますか？」のメッセージが表示されたら[挿入]を、
「既存の変換を置き換えますか？」のメッセージが表示されたら[現在のものを置換]をクリックします。
そうすると[販売日付]項目が日付型に変換されます。

続いて、[担当者]クエリとマージ（統合）をしていきます。
[販売実績リスト]クエリの一番最後のステップを選択した状態で
[ホーム]タブの[クエリのマージ]をクリックすると[マージ]ウィンドウが表示されます。
プルダウンで[担当者]クエリを選択して、[販売実績リスト]の[担当者コード]と[担当者]の[担当者コード]をそれぞれ選択してキー項目の紐づけを行います。
もし複数のキー項目で紐づけする場合は、Ctrlキーを押しながら複数の項目を順に選択します。
[結合の種類]は最初（[販売実績]クエリ）を基準にした[左外部]にします。

[OK]をクリックすると[担当者]の列が追加され、値には[Table]と表示されます。
[担当者]列のタイトル行の右の方にあるボタン（展開ボタン）をクリックします。

[展開]か[集計]を選択できますが、ここでは[展開]を選択します。
項目の[担当者コード]は同じ列が存在するのでチェックを外し、[元の列名をプレフィックスとして使用します]のチェックも外してから
最後に[OK]をクリックします。

すると[担当者]の列が展開されて表示されます。

同じように[部署]クエリを部署コードをキーに、[商品]クエリを商品コードをキーに[販売実績リスト]クエリへマージします。
マージが終わったらテーブルの列のタイトルをドラッグすることで、列の位置を変更できます。
[担当者コード]の右側に[氏名]を、[商品コード]の右側に[商品名]を、[販売数量]の右側に[売価]を移動します。
[売価]列のタイトル行を右クリックしてポップアップメニューから[名前の変更]を選択して、「単価」に変更します。

3. カスタム列の追加

次に新しく列を追加する方法を説明します。
ここでは[販売実績リスト]の[単価]列の右側に[売上]列、[利益]列を追加します。

[列の追加]タブから[カスタム列]をクリックすると[カスタム列]ウィンドウが表示されます。
[新しい列名]に列名を入力して、[カスタム列の式]に計算式として”[販売数量]*[単価]”を入力します。
[使用できる列]から[<<挿入]ボタンで項目を選択して式を入力できます。最後に[OK]をクリックします。

そうするとテーブルの一番右端にカスタム列が追加されますので、ドラッグして[単価]の右側へ移動します。

同様にカスタム列で[利益]（式：”[売価]-[販売数量]*[仕入単価]”）を作成します。
尚、カスタム列作成後も、ステップの右側に表示されている[設定]アイコンをクリックすれば修正が可能です。

[ホーム]タブの[閉じて読み込む]をクリックすると、Excelには[販売実績リスト]シートが追加されて、クエリで実行した操作の結果が表示されます。

4. データの集計

次に[販売実績リスト]から販売月別、担当者別に売上、利益を集計するクエリを作成します。
Power Query エディターの起動して、[販売実績リスト]クエリを選択して、[ホーム]タブの[管理]をクリックして[参照]を選択します。
すると[販売実績リスト]を参照したクエリが作成されます。
クエリの設定で名前を「月別、担当者別販売実績」に変更します。

次に[販売日付]列を選択して、[変換]タブの[抽出]をクリックして[範囲]を選択します。
開始インデックスに「0」、文字数に「7」を入力して[OK]をクリックします。

すると販売日付が年月の表示になり、列の名前も「販売年月」に変更します。

次にCtrlキーを押しながら[販売年月]列、[担当者コード]列、[氏名]列を選択します。
その状態で[ホーム]タブの[グループ化]をクリックすると[グループ化]ウィンドウが表示されます。
新しい列名に「売上」を入力して、操作に「合計」を選択して、列に「売上」を選択します。
[集計の追加]ボタンをクリックして、新しい列の枠を追加します。
新しい列名に「利益」、操作に「合計」、列に「利益」を選択します。最後に[OK]をクリックします。

すると販売年月、担当者コード、氏名毎に売上、利益が集計された状態になります。

[ホーム]タブの[閉じて読み込む]をクリックすると、Excelには[月別、担当者別販売実績]シートが追加されて、クエリで実行した操作の結果が表示されます。

クエリで作成されたシートには[クエリ]タブが表示されます。
[クエリ]タブの[更新]をクリックすると、データソースから最新データを取得してクエリの結果が更新されます。
[プロパティ]で定期的に更新する設定にすることも可能です。

おわりに

さて、ここまでPower Queryの基本的な機能をご紹介しましたが、いかがでしたでしょうか？
今後は、Excelでデータ集計を自動化する手段としてマクロ（VBA）以外に、Power Queryも検討してみてはいかがでしょうか。