前回紹介した結合方法のほかにも、「クエリのマージ」にはさまざまな結合方法が用意されている。今回は、ふたつのデータ表から「重複しているデータ」だけを抜き出して結合する方法を紹介していこう。それぞれの表で共通しているデータだけを抽出したい場合に活用できるので、ぜひ使い方を覚えておくとよい。

  • 重複しているデータだけを結合　クエリのマージ(3)

結合するクエリの準備

今回も「クエリのマージ」について。前回は「左外部」と呼ばれる方法でふたつのデータ表を結合したが、そのほかにも全部で6種類の結合方法が用意されている。今回は、その一例として「ふたつの表に共通するデータ」だけを抜き出して結合する方法を紹介していこう。

まずは、今回の例で使用するExcelファイルから見てみよう。以下の図は、ある24時間ジムの「4月1日の利用状況」を記録したデータ表だ。前回や前々回の連載で使用したものと同じデータ表で、各会員のID、入館時刻、退館時刻が記録されている。

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

一方、以下に示した図は、同じ24時間ジムの「4月2日の利用状況」を記録したデータ表となる。データの記録方法は先ほどのExcelファイルと同じで、各会員のID、入館時刻、退館時刻が記録されている。

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

これらのデータ表をもとに「2日間連続でジムを利用した人」を求めてみよう。より具体的に示すと、4月1日と4月2日の両方に「同じIDが存在するデータ」だけを抜き出す、という処理になる。これを「クエリのマージ」で実現してみよう。

最初に、それぞれのデータ表をクエリとして取得する。以下の図は、「4月1日のデータ表」を接続専用のクエリとして取得したものだ。クエリ名には「4月1日」という名前を指定してある。なお、「接続専用」については本連載の第46回で詳しく紹介しているので、よくわからない方は先にこちらを一読いただきたい。

  • 「4月1日」のクエリ(接続専用)

続いて、「4月2日のデータ表」を「Power Query エディター」に取得する。以下の図は、データ表を取得したあと、「入館時刻」と「退館時刻」のデータ型に「時刻」を指定した様子だ。現時点では、クエリ名は「Sheet1」になっている。

  • 取得されたデータ表

クエリ名が「Sheet1」のままでは内容を把握しづらいので、こちらもクエリ名を変更しておこう。普通に考えると「4月2日」という名前になりそうだが、後ほど「クエリのマージ」により4月1日のデータを追加するので、こちらには「4月2日(2日間連続)」というクエリ名を指定した。

  • クエリ名の変更

以上で、準備作業は完了。これらふたつのクエリ(データ表)のうち、IDが重複しているデータ(IDが共通して存在するデータ)だけを抜き出して結合してみよう。

重複しているデータだけ「クエリのマージ」で結合

それでは、重複しているデータだけを「クエリのマージ」で結合する方法を紹介していこう。「4月2日(2日間連続)」のクエリを開き、「ホーム」タブにある「クエリのマージ」をクリックする。

  • 「クエリのマージ」の開始

以下の図のような設定画面が表示されるので、「どのクエリと結合するか」を指定する。今回の例の場合、「4月1日」のクエリを選択すればよい。

  • 結合するクエリの選択

続いて、「どの列を基準にデータを照合するか」を指定する。今回の例の場合、それぞれのデータ表で「ID」の列を選択すればよい。

  • 基準とする列の選択

ここまでの手順は前回の連載と同じ。前回と異なるのは「結合の種類」の指定だ。ふたつのデータ表で重複(共通)しているデータだけを抜き出すときは、結合の種類に「内部」を選択する。

  • 結合の種類に「内部」を指定

「OK」ボタンをクリックすると「クエリのマージ」が実行され、「4月1日」のデータがTableとして追加される。また、結合の種類に「内部」を指定しているため、両方のデータ表に「同じID」が存在するデータだけが抽出される。逆に考えると、どちらか一方にしかIDが存在しないデータは、この時点で削除されることになる。

  • Tableとして結合された「4月1日」のクエリ

続いて、Tableとして結合された「4月1日」のデータを展開する。ここでは「入館時刻」のデータだけをピックアップするように指定した。

  • 展開する列の指定

  • 展開された列

あとは、データ表の見た目を整理するだけ。それぞれの「入館時刻」を区別できるように、列名を「04-01入館時刻」と「04-02入館時刻」に変更し、「退館時刻」の列を削除する。その後、列の順番を並べ替えると、以下に示したようなデータ表になる。

  • 列を整理したデータ表

以上で、パワークエリでの作業は完了。「閉じて読み込む」のアイコンをクリックし、データ表をExcelに出力する。

  • Excelに出力したデータ表

これで「2日間連続でジムを利用した人のデータ」だけを抜き出すことができた。ただし、IDの情報しかないため「誰が2日間連続で利用したのか」までを把握することはできない。続いては、この問題をパワークエリで解決していこう。

氏名データの結合

この作業は、前回の連載で紹介した例とまったく同じ処理内容になる。その“おさらい”として、操作手順を簡単に紹介しておこう。

各IDに対応する会員情報は、「会員マスタ」というExcelファイルに記録されている。こちらも前回や前々回の連載で使用したものと同じデータ表となる。

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

このデータ表を「接続専用のクエリ」としてExcelに取得する。クエリ名には「会員情報」という名前を指定した。

  • 接続専用で「会員情報」のクエリを追加

続いて、先ほどデータ表を結合した「4月2日(2日間連続)」のクエリを開き、「ホーム」タブにある「クエリのマージ」をクリックする。

  • 「クエリのマージ」の開始

「クエリのマージ」の設定内容は、以下の図に示した通り。「ID」の列を基準に、今回は「左外部」でデータ表を結合すればよい。

  • 左外部で「会員情報」のクエリを結合

「会員情報」のデータがTableとして追加されるので、これを展開する。今回は「氏名」のデータだけを展開するように指定した。

  • 展開する列の指定

展開された列の列名を「氏名」に変更し、列の並び順を変更すると、以下の図のようなデータ表に仕上げることができる。あとは「閉じて読み込む」のアイコンをクリックして、Excelに出力済みのデータ表を更新するだけだ。

  • 列名の変更、列の並べ替え

これで「誰が2日間連続で利用したのか」、また「それぞれ何時に入館したのか」を把握できるようになる。

  • 更新されたデータ表

なお、このデータ表には、少しイレギュラーなデータも存在している。それは、IDが「A000011」の「岸 剛」さんのデータだ。

  • 結合時に複製されたデータ

この会員は、4月1日に2回ジムを利用している(10:05:00と16:10:33に入館)。このため、2行にわたってデータが記録されている。一方、4月2日の利用は1回しかなく、その入館時刻データは「16:00:02」のひとつだけ、となる。この場合、それぞれの行に「16:00:02」のデータが取得(複製)されることになる。

「クエリのマージ」を使用するときは、このような仕様になっていることも知っておく必要がある。データの複製が便利に機能するケースもあれば、トラブルの原因になってしまうケースもあるので、その仕組みをよく理解しておくことが大切だ。

なお、「クエリのマージ」には「左外部」や「内部」のほかにも、「右外部」、「完全外部」、「左反」、「右反」といった計6種類の結合方法が用意されている。次回は、それぞれの結合方法について詳しく紹介していこう。