データを好きな順番に並べ替えるテクニック(クエリのマージの応用)

「クエリのマージ」はデータ表を結合するときだけでなく、データを好きな順番に並べ替えるときにも活用できる。今回は「クエリのマージ」の応用編として、住所データを「北海道」から「沖縄」の順番に並べ替える方法を紹介していこう。通常の昇順/降順では対処できない並べ替えを実現するテクニックとして覚えておくとよいだろう。

• 

  データを好きな順番に並べ替え(クエリのマージの応用)

昇順/降順では対応できない並べ替え

今回は「クエリのマージ」を応用して、データを「好きな順番」に並べ替えるテクニックを紹介していこう。昇順/降順では対処できない並べ替えに活用できる。

ここでは、以下の図に示した「住所の一覧表」を例に解説を進めていこう。たとえば、この一覧表を「北海道、青森県、岩手県、宮城県、(中略)、鹿児島県、沖縄県」という具合に、都道府県を北から並べた順番に並べ替えたいとする。

• 

  会員の住所を記録したExcelファイル

もちろん、通常の昇順/降順では、こういった並べ替えを実現できない。そこで「クエリのマージ」を応用して並べ替えを行ってみよう。先ほどのデータ表を「Power Query エディター」に取得する。

• 

  「Power Query エディター」に取得したデータ表

続いて、「住所」の列から「都道府県」を切り分ける。この手順は本連載の第15回で紹介した通りだ。よくわからない方は、復習も兼ねて先に一読いただきたい。

• 

  住所から都道府県を分離したデータ表

その後、このクエリに適当な名前を付ける。ここでは「会員住所一覧」という名前に変更した。

• 

  クエリ名の変更

「閉じて読み込む」をクリックしてデータ表をExcelに出力すると、以下の図のような結果が得られる。

• 

  Excelに出力したデータ表

現時点では「都道府県」を切り分けただけなので、まだ「北海道」から「沖縄」の順番に並べ替えることはできない。続いては、「並び順を指定するデータ表」をクエリとして取得していこう。

並び順を指定するデータ表の取得

「クエリのマージ」を使ってデータを好きな順番に並べ替えるときは、あらかじめ「並び順を指定するデータ表」を作成しておく必要がある。今回の例の場合、以下の図のようにデータ表を作成すればよい。

• 

  並べ替える順番を指定したデータ表

このデータ表を「Power Query エディター」に取得すると以下の図のようになる。こちらは、クエリ名に「都道府県リスト」という名前を指定した。

• 

  データ表を取得してクエリ名を変更

このデータ表はExcelに出力するものではなく、処理の過程で利用するクエリとなる。よって、接続専用として保持しておく。「閉じて読み込む」→「閉じて次に読み込む」を選択し、出力方法に「接続の作成のみ」を指定する。

• 

  接続専用のクエリとして保持

ここまでの作業が済むと、Excelに「会員住所一覧」と「都道府県リスト」の2つのクエリが保持されることになる。

• 

  利用可能な2つのクエリ

以上で準備は完了。あとは「クエリのマージ」で2つのデータ表を結合し、都道府県の「コード」の順番に並べ替えればよい。

クエリのマージを使ったデータの並べ替え

念のため、以降の操作手順も紹介しておこう。「会員住所一覧」のクエリを開き、「クエリのマージ」をクリックする。

• 

  クエリのマージの開始

「クエリのマージ」の設定方法は以下の図に示した通り。追加するクエリに「都道府県リスト」を選択し、それぞれの「都道府県」を照合列に指定する。今回は「操作中のクエリ」にもとづいてデータ表を結合するので、結合方法は「左外部」のままでよい。

• 

  クエリのマージの設定

「OK」ボタンをクリックすると、「都道府県リスト」のデータがTableとして表の右端に追加される。

• 

  Tableとして結合された「都道府県リスト」

追加されたTableを展開していこう。今回は「コード」の列を展開するように指定した。

• 

  展開する列の指定

これで、それぞれの「都道府県」を北から順に並べた場合の数値データを追加できた。あとは、この列を基準にデータを並べ替えるだけ。「都道府県リスト.コード」の列を選択して「昇順」をクリックする。

• 

  「都道府県リスト.コード」の昇順に並べ替え

「都道府県リスト.コード」の小さい順にデータが並べ替えられる。つまり、「北海道」→「青森県」→「岩手県」→(中略)→「鹿児島県」→「沖縄県」という順番にデータが並べ替えられることになる。

• 

  「都道府県リスト.コード」の昇順に並べ替えられたデータ表

なお、Tableを展開した時点で、元の並び順が変更されてしまっていることにも注意しなければならない。たとえば、同じ「北海道」の中で見ると、データが「ID」の順番に並んでいないことに気付くと思う。続けて「ID」の列を選択し、「昇順」をクリックする。

• 

  「ID」の昇順に並べ替え

本連載の第16回でも紹介したように、連続して「並べ替え」の処理を行うと、「1番目の並べ替え」→「2番目の並べ替え」の優先順位で並べ替えが実行される。つまり、「コード」→「ID」の優先順位で並べ替えたデータ表になる。

• 

  「コード」→「ID」の優先順位で並べ替えられたデータ表

以上で「都道府県」を北から順番に並べ替える作業は完了。もう「コード」の列は必要ないので、この時点で削除しておこう。

• 

  不要になった列の削除

「閉じて読み込む」をクリックすると、先ほどExcelに出力したデータ表が更新され、以下の図のような結果が得られる。

• 

  好きな順番に並べ替えたデータ表(1)

「北海道」のデータがたくさんあるので、少し下へスクロールした様子も紹介しておこう。「都道府県」→「ID」の優先順位で、「都道府県を北から並べた順」にデータが並べ替えられているのを確認できるだろう。

• 

  好きな順番に並べ替えたデータ表(2)

このように「クエリのマージ」を使って「好きな順番」にデータを並べ替えることも可能だ。もういちど、その大まかな手順をおさらいしておこう。

1. 並べ替えの基準になるデータを切り分ける
2. 並び順を指定するデータ表を作成し、接続専用のクエリとして保持する
3. 2つのデータ表を「クエリのマージ」で結合する
4. 結合したデータを基準に並べ替えを実行する
5. 必要に応じて、元の並び順を維持するための並べ替えを行う
6. データ表をExcelに出力する

これで「好きな順番」にデータを並べ替えられるようになる。たとえば、「支社を北から並べた順」、「役職を上から並べた順」、「優先度の高い順」などの並べ替えも、同様の手順で実現することが可能だ。

さまざまな場面に使える、パワークエリの利点を活かしたテクニックとなるので、その仕組みをよく理解しておくと、きっと役に立つだろう。