今回から「クエリのマージ」の使い方を紹介していく。「クエリのマージ」とは、クエリとして取得した2つのデータ表を組み合わせて、1つのデータ表に結合する処理のことを指す。これによりルックアップと同じような処理を実現することが可能となる。今回は、その準備編として「接続専用」でデータを取得する方法を紹介していこう。

パワークエリでルックアップを実現するには？

パワークエリは、複数のクエリ（データ表）を組み合わせて、さまざまな処理を行うことも可能となっている。ここでは、その代表例として「クエリのマージ」の使い方を紹介していこう。

  • 接続専用でデータを取得する方法　クエリのマージ（1）

具体的な例を用いながら解説していこう。以下の図は、ある24時間ジムの「4月1日の利用状況」を記録したデータ表だ。ここには各会員のIDと入館時刻、退館時刻が記録されている。ただし、このままでは「誰が利用したのか？」を把握することはできない。

  • 4月1日の利用状況を記録したExcelファイル

「誰が利用したのか？」を把握するには、会員情報をまとめた別のExcelファイルを参照して、それぞれのIDをもとに氏名などの情報を照らし合わせていく必要がある。

  • 会員情報を記録したExcelファイル

こういった処理を通常のExcelで行う場合、VLOOKUPやXLOOKUPといったルックアップ系の関数を利用するのが一般的だ。では、パワークエリで処理する場合はどうだろうか？　もちろん、パワークエリでもルックアップ的な処理を実現することが可能である。

以下の図は、2つのExcelファイルを組み合わせて、新しいデータ表を作成した例だ。具体的には、それぞれのIDをもとに「会員情報」から氏名と性別のデータをピックアップし、それを「4月1日の記録」に追加する、という処理が行われている。これで「4月1日に誰がジムを利用したのか？」を即座に確認することが可能となる。

  • 「氏名」と「性別」を追加したデータ表

このように2つのデータ表（クエリ）を結合してくれる処理が「クエリのマージ」となる。とても便利な機能なので、ぜひ使い方を学んでおくとよいだろう。

接続専用でデータを取得するには？

それでは、実際の操作手順を示していこう。まずは、会員情報が記録されているExcelファイルを「Power Query エディター」に取得する。Excelを起動し、「データ」タブにある「データの取得」をクリックし、「ファイルから」→「Excelブックから」を選択する。

  • データの取得方法の指定

ファイルの選択画面が表示されるので、データを取得するExcelファイルを指定する。今回の例では「会員マスタ.xlsx」というファイルに会員情報が記録されているので、このファイルを選択して「インポート」ボタンをクリックする。

  • データを取得するExcelファイルの指定

続いて、データを取得するワークシートを選択し、「データの変換」ボタンをクリックする。

  • データを取得するワークシートの選択

会員情報のデータ表が「Power Query エディター」に取得される。ここまでの操作手順は、一般的なパワークエリの使い方と同じだ。よって、特に戸惑うことなく操作を進められるだろう。なお、現時点では、このクエリに「Sheet1」という名前が自動命名されている。

  • 取得されたデータ表

クエリを1つしか作成しないのであれば、「Sheet1」というクエリ名のままでも特に問題は生じないだろう。しかし、複数のクエリを扱うとなると、そうはいかない。各クエリが「何のデータを記録したものなのか？」を一目で判別できるように、適切な名前に変更しておく必要がある。

クエリの名前を変更するときは、「クエリの設定」にある「名前」の項目に好きな文字を入力すればよい。今回の例では、このクエリの名前を「会員情報」に変更した。なお、クエリの名前を変更すると、それに合わせて「クエリの一覧」に表示されるクエリ名も自動更新される仕組みになっている。

  • クエリ名の変更

以上で、このクエリに関する処理は完了となる。続いて、「Power Query エディター」を閉じる操作を行うが、このとき普通に「閉じて読み込む」をクリックすると、会員情報のデータがExcelに出力されてしまう。このクエリは「4月1日の記録」にデータを追加するために利用するものであり、すべての会員情報をExcelに出力する必要はない。

このような場合に活用できるのが「接続専用」と呼ばれるクエリの作成方法だ。「閉じて読み込む」の▼をクリックし、「閉じて次に読み込む」を選択する。

  • 接続専用としてクエリを保持する操作（1）

データの出力方法を指定する画面が表示されるので、「接続の作成のみ」を選択して「OK」ボタンをクリックする。

  • 接続専用としてクエリを保持する操作（2）

「Power Query エディター」が終了し、先ほどのデータ表が「接続専用」としてExcelに保持される。この場合、Excelにデータ表は出力されず、ワークシートは空白のままになる。

  • 接続専用として保持されたクエリ

このように、データの出力方法に「接続の作成のみ」を指定すると、

・「Power Query エディター」ではデータ表を利用できる
・Excelにはデータ表が出力されない

といった形でクエリ（データ表）を保持することが可能となる。複数のクエリを扱う際に必須となる操作なので、その仕組みを含めて必ず覚えておこう。

データを追加するExcelファイルの取得

次は、もう一方のデータ表となる「4月1日の記録」を「Power Query エディター」に取得していこう。「データ」タブにある「データの取得」をクリックし、「ファイルから」→「Excelブックから」を選択する。

  • データの取得方法の指定

Excelファイルを指定する画面が表示されるので、「4月1日の記録」が記録されているExcelファイルを選択し、「インポート」ボタンをクリックする。

  • データを取得するExcelファイルの指定

続いて、データを取得するワークシートを選択し、「データの変換」ボタンをクリックする。

  • データを取得するワークシートの選択

4月1日の情報を記録したデータ表が「Power Query エディター」に取得される。こちらも、データ取得までの操作手順は一般的なパワークエリの使い方と同じだ。なお、取得されたデータ表をよく見ると、「入館時刻」や「退館時刻」の日付が「1899/12/31」と表示されているのを確認できる。

  • 取得されたデータ表

時刻だけを記したデータを「Power Query エディター」に取得すると、上図のように1899/12/31の日付が付加されてしまうケースがある。これはシリアル値の仕様によるもので、別に不思議な現象ではない。その理屈を知りたい方は、いちど「シリアル値」について詳しく学んでみるとよいだろう。単に時刻として扱えればよい場合は、データ型を「時刻」に変更する、と覚えておけばよい。これで時刻のみのデータとして扱うことできる。

  • データ型の変更

こちらもクエリ名が「Sheet1」のままでは内容を判別しづらいので、適切な名前に変更しておこう。今回の例では「4月1日の記録」というクエリ名に変更した。

  • クエリ名の変更

あとは、普通にExcelに出力するだけ。「閉じて読み込む」のアイコンをクリックする。

  • Excelに出力する操作

Excelにデータ表が出力される。ただし、現時点では「氏名」などのデータを結合（追加）する処理を行っていないため、取得したデータ表がそのまま出力されることになる。

  • Excelに出力されたデータ表

ここまでの作業が済んだら、いちどExcelファイルを保存しておくとよい。作成した2つのクエリはExcelに保持されているものの、まだファイルには保存されていない。Excelファイルにクエリを保存するには、「上書き保存」または「名前を付けて保存」を実行しておく必要がある。忘れないように注意しておこう。

利用可能なクエリの確認

続いては、それぞれのクエリを開くときの操作手順を紹介しておこう。この手順は、画面右端に表示されているクエリ名をダブルクリックするだけ。

  • 「Power Query エディター」の起動

すると「Power Query エディター」が起動し、ダブルクリックしたクエリのデータ表が表示される。左側に一覧表示されているクエリ名をクリックして、操作するクエリを切り替えることも可能だ。

  • 利用可能なクエリの一覧

念のため、各クエリの状況を示したチャート図も確認しておこう。「表示」タブを選択し、「クエリの依存関係」をクリックする。

  • クエリの依存関係

上図に示したようなチャート図が表示され、各クエリの依存関係を確認できるようになる。「会員情報」のクエリは、「会員マスタ.xlsx」からデータを取得、データは出力していない（ワークシートに読み込まれていない）、という状況になっている。一方、「4月1日の記録」のクエリは、「04月01日の入出記録.xlsx」からデータを取得、ワークシートに出力済み（ワークシートに読み込み済み）、という状況になっている。

このように、1つのExcelファイルに「複数のクエリ」を保持することも可能である。ということで、次回は本テーマの主題ともいえる「クエリのマージ」について詳しく紹介していこう。