Excel(エクセル)にはデータを並べ替えすることができる「フィルター」機能と「並べ替え」機能があります。
最近になって、関数でもデータの並べ替えが出来るようになったのをご存知でしょうか。
並べ替えに利用するのは、SORT関数という新関数です。
SORT関数には、「フィルター」機能や「並べ替え」機能にはない以下のメリットがあります。
SORT関数のメリット
１．元データ自体は並べ替えせず、並べ替え後のデータを別のセルに作成できる
２．横方向にも縦方向にも並べ替えが可能
３．データ範囲が膨大でも、SORT関数の挿入は１セルだけで済む
今回の記事では、SORT関数の使い方を事例と合わせながら詳しく紹介していきます。
SORT関数を使わず、フィルターを使いたい場合は、以下の記事を参考にしてください。
SORT関数とは？
SORT関数の読み方は、”ソート”です。
SORT関数とは、指定したデータを別の空いたスペースに並べ替えする関数です。
「フィルター」機能と「並べ替え」機能は元データ自体が並べ替えされますが、SORT関数は元データはそのままで、異なる場所に並べ替え後のリストを作成します。
そのため、SORT関数は元データは動かしたくない、複数のパターンで並べ替えしたデータを残したい、といった場合に便利です。
さらに、SORT関数は縦方向だけでなく、横方向でも並べ替えができます。
SORT関数が役立つケース
１．元データは動かしたくない
２．複数のパターンで並べ替えを実施して、そのデータは残しておきたい
３．縦方向、横方向にも並べ替えがしたい
ただし、SORT関数が使えるのは、ExcelのバージョンがMicrosoft365(サブスク)とExcel2021以降のユーザーに限定されています。
バージョンが分からない方は、「ファイル」タブ⇒「その他」⇒「アカウント」画面からバージョンを確認してください。
SORT関数の書式と引数
まず、SORT関数の書式と引数を詳しく見ていきましょう。
SORT関数の書式は以下のとおりで、４つの引数で構成されています。
＝SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])
SORT関数の使い方と手順
SORT関数の使い方を詳しく解説していきます。
仮に、並べ替えするデータが20セルあったとしても、20個のSORT関数を挿入する必要はありません。
SORT関数を１つのセルに挿入するだけで、複数の行と列に結果が拡張されて並べ替え結果が表示されます。(この機能のことをスピルといいます)
SORT関数でデータを縦方向に並べ替えする方法
下の表にSORT関数を使って、「生年月日」列を昇順で並べ替えする方法を紹介します。
手順１．見出し行をコピペ
見出し行はSORT関数では複製されません。そのため、空いているセルに行見出しをコピペします。
手順２．SORT関数を第１引数を指定
見出しの左下のG3セルにSORT関数を挿入します。第１引数「配列」は、並べ替えしたいデータ範囲(B3:E11)を見出しを除いて選択します。
=SORT(B3:E11,
手順３．SORT関数を第２引数を指定
SORT関数の第２引数「並べ替えインデックス」は、並べ替えしたい列が先頭から何列目かを数字で指定します。
「生年月日」列は、左端から4列目にあるので、「4」と入力します。
=SORT(B3:E11,4,
手順４．SORT関数を第３引数を指定
SORT関数の第３引数「並べ替え順序」は、手順３で指定した列を昇順で並べ替えしたいか降順で並べ替えしたいかを指定します。
昇順(小さい順)の場合は「1」(もしくはブランク)、降順(大きい順)の場合は「-1」を入力します。
=SORT(B3:E11,4,1
手順５．SORT関数を第４引数を指定
SORT関数の第４引数「並べ替え基準」は、縦方向に並べ替えするか、横方向に並べ替えするかを決めます。
縦方向の場合は「FALSE」(もしくは0)、横方向の場合は「TRUE」(もしくは１)を入力します。
=SORT(B3:E11,4,1,FALSE)
手順６．SORT関数を確定
Enterキーを押してSORT関数を確定させてください。そうすると、「生年月日」列を昇順で並べ替えすることができます。
ただ、今回のように日付セルを並べ替えすると、下の図のように日付がシリアル値で表示されるので、セルの書式設定を「日付」に変更してください。
手順７．SORT関数による並べ替えが完成
以上の操作で、下の図のようにデータを「生年月日」を基準に昇順で並べ替えすることができました。
SORT関数はG3セルの１つのセルにしか挿入していませんが、スピル機能によりG3セル以外のセルにも並べ替えされた結果が拡張されます。
SORT関数でエラーが発生する場合
SORT関数でエラー値「#スピル!」が発生する場合があります。
このケースは、スピル機能によってSORT関数の結果を表示させようとしている周りのセル範囲に、文字や数値が入力されていることが原因です。
エラーが発生した際は、並べ替え後のデータが表示されるセル範囲に文字が入力されていないか確認し削除してください。
SORT関数を使った事例を紹介
SORT関数の基本的な使い方は上記で紹介したとおりです。
続いて、SORT関数の応用編として、SORT関数を使った事例を5つ紹介します。
実務で活用できる場面がないかイメージしてみてください。
SORT関数/事例１：横方向に並べ替え
SORT関数は、データを横方向に並べ替えすることもできます。
下の表の４行目の「生年月日」列を左から順に昇順で並べ替えする方法を紹介します。
横方向に並べ替えするときは、SORT関数の第4引数「並べ替え基準」を「TRUE」もしくは「１」を指定します。
=SORT(C2:I4,3,1,TRUE)
フィルターでは横方向に対応できませんが、SORT関数を使えば、上記のように簡単に並べ替えが可能になります。
SORT関数/事例２：データの追加を自動で反映させて並べ替え
下の図のように最下行に元データを追加しても、残念ながらSORT関数の結果は自動で反映されません。
データを反映させるためには、G3セルに挿入したSORT関数で指定したセル範囲を修正する必要があります。
しかし、IF関数とSORT関数を組み合わせることで、データの追加を自動反映させることができるので、その方法を解説します。
元データを後から追加する前提で、SORT関数のセル範囲を空白セルも含めて広めに設定しておくと、SORT関数の結果は「0」と表示されます。
そのため、IF関数を使って、SORT関数の結果が「0」であればセルがブランクとなるように設定します。
利用する数式は以下のとおりです。
IF関数の第１引数「論理式」に「SORT関数の結果が0以外の場合」を設定し、第２引数「値が真の場合」にSORT関数、第3引数「値が偽の場合」に「””」を設定します。
=IF(SORT(B3:E13,4,-1,FALSE)<>0,SORT(B3:E13,4,-1,FALSE),"")
そうすると、上の図のように、SORT関数のセル範囲は13行目までのブランクなセル範囲を指定していますが、SORT関数の結果は0と表示されません。
この数式を一度作成してしまえば、元データを追加しても、SORT関数の結果は自動で反映されて並べ替えが実行されます。
SORT関数/事例３：複数の列を優先順位をつけて並べ替え (配列数式)
SORT関数は基本的に１つの列に対して並べ替えを行いますが、優先順位をつけて複数の列で並べ替えするルールを設定することもできます。
利用するのは、「配列数式」という複雑な計算を可能にする「{}」を使った数式です。
SORT関数の第２引数と第２引数に配列数式({})を使用します。
例えば、優先順位１位で並べ替えする列は３列目、優先順位２位で並べ替えする列は４列目で設定したい場合は、SORT関数の第２引数「並べ替えインデックス」は、{3,4}と入力します。
同様に、SORT関数の第３引数「並べ替え順序」は、３列目と４列目を昇順(1)か降順(-1)かをそれぞれ指定し、{1,-1}といったように入力します。
=SORT(B3:E11,{3,4},{1,-1},FALSE)
配列数式は「=SORT(B3:E11,{3,4,1},{1,-1,1},FALSE)」といったように、３つ以上で利用することもできます。
配列数式を使う際の注意点は、優先順位が高い列(行)から順番に数式を作成することです。
「{3,4}」とすべきところを「{4,3}」と数式を作成すると、４列目が最優先で並べ替えされるので、結果は変わってしまいます。
SORT関数/事例４：複数の列を優先順位をつけて並べ替え (入れ子)
SORT関数の中にSORT関数を挿入する「入れ子」(別名：ネスト）というテクニックを利用することで、複数の列で並べ替えすることができます。
事例３で紹介した配列数式の利用が苦手な方は、こちらを利用してください。
まず、SORT関数で優先順位が低い列の並べ替えの数式を作成します。(４列目を降順(-1)で並べ替え)
=SORT(B3:E11,4,-1,FALSE)
次に、作成したSORT関数を囲うように、２つ目のSORT関数を挿入し、優先順位が高い列の並べ替えを実施します。(３列目を昇順(1)で並べ替え)
=SORT(SORT(B3:E11,4,-1,FALSE),3,1,FALSE)
そうすると、上の表のように、元データの３列目「性別」を優先順位１位で、４列目「生年月日」を優先順位２位で並べ替えすることができました。
同様の要領で、SORT関数を３つ使用することで、３つ以上の列に対して、並べ替えの列を設定することも可能です。
SORT関数/事例５：FILTER関数と組み合わせる
SORT関数とFILTER関数を組み合わせると、条件に合うデータの抽出と並べ替えを同時に行うことができます。
FILTER関数の書式は、「=FILTER(配列,含む,[空の場合])」で、引数の内容は以下のとおりです。
FILTER関数の引数
第１引数「配列」：データを抽出するセル範囲を指定する
第２引数「含む」：抽出する条件を論理式で入力する
第３引数「空白」：一致するデータがない場合の表示方法を指定します。
基本的な使い方を１つ紹介します。
B列からE列のデータに対して、以下のFILTER関数を挿入します。そうすると、性別が「男」に合致する「性別」「生年月日」のデータをG列、H列に表示させることができます。
=FILTER(D3:E11,D3:D11="男","")
このFILTER関数をSORT関数の第１引数に挿入することで、「男」のデータを抽出して、さらに「生年月日」を昇順で並べ替えすることができます。
=SORT(FILTER(D3:E11,D3:D11="男",""),2,1)
SORTBY関数は複数の基準で並べ替えできる！
SORT関数に似た関数に、SORTBY関数(ソートバイ)という関数があります。
SORTBY関数は、データを複数の列を基準に並べ替えを実施することができます。
SORT関数でも配列数式や入れ子を利用することで実現することができますが、SORTBY関数の方が引数の設定が簡単です。
以下の記事で使い方を詳しく紹介しているので、参考にしてください。
