決められた順番で並べ替える

  1. Home
  2. Excel
  3. Excel Tips

上図のようなデータを、[名前]列で並べ替えてみます。

実行結果は上図のとおり。正しく"五十音順"で並べ替えられました。このように、Excelは漢字を並べ替えると、五十音順で並べ替わる・・・って考えている人はいませんか？んなこたぁないです！そもそも漢字には、複数の読みを持つものが多いです。"菅野"はカンノですか？スガノですか？どちらも正解です。そんな漢字を、Excelごときが並べ替えられるわけがありません。漢字に対して並べ替えを実行すると、Excelは漢字のフリガナ順に並べ替えます。フリガナとは、その漢字をセルに入力するとき、日本語変換をする前の読みです。たとえば、カンノと入力して変換した"菅野"はカンノですし、スガノと入力した"菅野"はスガノです。上図の漢字には、それぞれフリガナが設定されていたんです。

フリガナが設定されていない漢字は、望んだように並べ替えられません。ということは、セルに手入力したデータでないと無理ってことです。CSVのデータはアウトです。Power Queryから読み込むテーブルや基幹システムなどから提供されるデータもダメ。さらに、FILTER関数やSORT関数などの結果にもフリガナは含まれません。こう考えると、もうね、漢字を五十音順に並べ替えるのって、不可能な時代になったのではないかと。この仕組みは、データを手入力していた頃の名残なんじゃないかと思います。

とまぁ、"五十音順"に関しては諦めるとして。とはいえ、何らかのルールに従ってデータを並べ替えたいときもあります。そんなときのヒントとして、ひとつの方法をご紹介します。ここでは、都道府県名を都道府県コード順に並べてみましょう。まずは、こちらをご覧ください。

元データはテーブル「Data」です。[都道府県]列に、任意の都道府県名を代入しました。[数値]に意味はありません。1列だけだと寂しいので"賑やかし"みたいなもんです。まずは、このリストをSORT関数で昇順に並べ替えてみます。

結果はご覧のとおり。漢字は文字コード順で並べ替わるので、沖縄が上の方にきちゃいました。さて、これを都道府県コードで並べ替えるので、まずは都道府県コードの一覧を用意します。「都道府県コード」というキーワードで検索すると、たくさん見つかります。お好きなデータをコピーして、どこかにテーブル形式で保存してください。

分かりやすいテーブル名や列名をお勧めしますが、ここでは数式を短くしたかったので、短い名前にしました。ちなに都道府県コードは英語でPrefecture codeらしいです。なので頭文字を取りました。まぁ、あまり深く考えないでください。あと、これは必須ではありませんが、左端列に「都道府県名」、右端列に「都道府県コード」としました。逆でもいいんですけど、こうると数式全体が(少し)短くなります。どっちでもいいです。さて、並べ替えをするわけですが、今回使うのはSORT関数ではなくSORTBY関数です。

SORTBY関数の解説をします。その前に、まずはシンプルに並べ替えるとき便利なSORT関数ですけど、引数は次のとおりです。

SORT(配列,並べ替えインデックス,並べ替え順序,並べ替え基準)

引数「配列」は並べ替え前のセル範囲や配列です。引数「並べ替えインデックス」は並べ替えの基準となる列の位置を数値で指定します。引数「並べ替え順序」は昇順か降順を指定し、引数「並べ替え基準」は"列方向"の並べ替えか"行方向"の並べ替えかを指定します。注目すべきは、引数「並べ替えインデックス」です。ここには「何列目」という数値を指定します。つまり、ここで指定できるのは、引数「範囲」で指定した、並べ替える前のセル範囲や配列の一部です。

さて、今回使用するSORTBY関数の引数は、次のとおりです。

SORTBY(配列,基準配列,並べ替え順序,...)

引数「配列」と引数「並べ替え順序」は、SORT関数と同じです。違いは、引数「基準配列」です。こちらは、元のリスト内で「何列目」と数値で指定するのではなく、任意の配列を指定できます。もちろん、並べ替え前のリストに存在しない、別の配列でもOKです。

今回並べ替えたいのは「Data」です。並べ替えの基準は「都道府県コード」です。しかし、元リストの「Data」には「都道府県コード」がありません。だから数値で「何列目」と指定できないんです。このように、基準となる配列を別に作るようなときには、今回のSORTBY関数が便利です。

それでは「Data」内の、各都道府県のコードを調べましょう。これは、VLOOKUP関数で一発です。

先に「都道府県コード」をテーブルにするとき「都道府県名」「コード」の順に並べたのは、このようにVLOOKUP関数を使うためです。もしテーブルが「コード」「都道府県名」の順だったらXLOOKUP関数を使ってください。

ここで作成したVLOOKUP関数の結果をSORTBY関数で使うので、LET関数を使って変数に入れておきます。今回は引数「order」とします。

SORTBY関数の「基準配列」に、この変数orderを指定します。

=LET(order,VLOOKUP(Data[都道府県],PC,2,FALSE),
     SORTBY(Data,order))

もちろん、LET関数を使わないで「=SORTBY(Data,VLOOKUP(Data[都道府県],PC,2,FALSE))」と書いてもいいです。どちらでも、ご自身が理解しやすい書き方をしてください。