Excel(エクセル)のSORTBY関数は、複数の列(行)を基準にデータを並べ替えすることができる関数です。
「フィルター」機能と「並べ替え」機能では元データ自体を並べ替えしていました。
しかし、SORTBY関数は元データはそのままで、異なる場所に並べ替え後のリストを作成できる、というメリットがあります。
この記事では、SORTBY関数の使い方を詳しく紹介していきます。
SORTBY関数とは？
SORTBY関数の読み方は、”ソートバイ”です。英語の「sort by」が「分別する」を意味しているところが名前の由来です。
SORTBY関数とは、指定したデータを複数の列(行)を基準に並べ替えする関数です。
「フィルター」機能と「並べ替え」機能とは違い、SORTBY関数は元データはそのままで、異なる場所に並べ替え後のリストを作成します。
ただし、SORTBY関数が使えるのは、バージョンがMicrosoft365(サブスク)とExcel2021以降のユーザーに限定されています。
一方、SORTBY関数に似た関数にSORT関数があります。
SORT関数とSORTBY関数との違いは、SORT関数が１つの列を基準にしているのに対し、SORTBY関数は複数の列(行)を基準に並べ替えすることができます。
SORTBY関数を使うメリットは以下のとおりです。
SORTBY関数を使うメリット
・元データは並べ替えせずに、並べ替えデータを新規で作成できる
・縦方向だけでなく、横方向にも並べ替えが可能
・データが大きくても、SORTBY関数の挿入は１セルだけで済む
・データを更新しても自動で並べ替えに反映される
なお、SORT関数の使い方は、下の記事で使い方を紹介しているので合わせて参考にしてください。
SORTBY関数の書式と引数
まず、SORTBY関数の書式と引数を詳しく見ていきましょう。
SORTBY関数の書式は以下のとおりです。
「基準列」と「順序」の引数を１セットとして、複数の並べ替えのルールを設定することができます。
＝SORTBY(配列,基準列1,順序1,[基準列2],[順序2]・・・)
第１引数「配列」
並べ替えしたいデータのセル範囲を指定します。(例：B3:E11)
第２引数「基準列1」
優先順位１位で並べ替えしたい列(行)を指定します。(例：C3:C11、F4:H4)
第３引数「順序1」
第２引数で指定した列(行)を昇順で並べ替えする場合は「1」は、降順の場合は 「-1」を指定します。(例：1，-1)
第４引数「基準列2」
優先順位２位で並べ替えしたい列(行)を指定します。(例：D3:D11、F7:H7)
第５引数「順序2」
第4引数で指定した列(行)を昇順で並べ替えする場合は「1」は、降順の場合は 「-1」を指定します。(例：1，-1)
SORTBY関数の使い方を紹介
SORTBY関数の使い方を詳しく解説していきます。
SORTBY関数は１つのセルに挿入するだけで、複数の行と列に結果が拡張されて並べ替えの結果が表示されます。(スピル機能)
SORTBY関数で複数の列に優先順位をつけて並べ替え
下の表にSORTBY関数を使って、優先順位１位で「性別」列を小さい順に、優先順位２位で「体重」列を大きい順に並べ替えする方法を紹介します。
手順１．見出し行をコピペ
SORTBY関数を挿入する前に、空いているセルに、行見出しをコピペします。
手順２．SORTBY関数を第１引数を指定
見出しの左下のF3セルにSORTBY関数を挿入します。第１引数「配列」は、見出しを除いてた並べ替えしたいデータ範囲(B3:D11)を選択します。
=SORTBY(B3:D11,
手順３．SORTBY関数を第２引数を指定
SORTBY関数の第２引数「基準列1」は、優先順位１位で並べ替えしたい列のセル範囲を指定します。
「性別」列はC列にあるので、「C3:C11」を指定します。
=SORTBY(B3:D11,C3:C11,
手順４．SORTBY関数の第３引数を指定
SORTBY関数の第３引数「順序1」は、手順３で指定した列を昇順で並べ替えしたいか降順で並べ替えしたいかを指定します。
昇順(小さい順)の場合は「1」(もしくはブランク)、降順(大きい順)の場合は「-1」を入力します。
=SORTBY(B3:D11,C3:C11,1,
手順５．SORTBY関数の第４引数以降を指定
手順３，４と同じように、SORTBY関数の第４引数「基準列2」と第５引数「順序２」に、優先順位２位の「体重」列を降順で並べ替えする設定を行います。
=SORTBY(B3:D11,C3:C11,1,D3:D11,-1)
手順６．SORTBY関数による並べ替えが完成
Enterキーを押してSORTBY関数を確定させてください。以上の操作で、下の図のようにデータを「性別」列と「体重」列を優先順位をつけて並べ替えすることができました。
SORTBY関数はF3セルの１つのセルにしか挿入していませんが、スピル機能によりF3セル以外のセルにも並べ替えされた結果が拡張されます。
SORTBY関数でエラーが発生する原因と解決策
SORTBY関数でエラー値「#スピル!」や「#VALUE!」が発生する場合があります。
「#スピル!」が発生するのは、SORTBY関数の結果を表示させようとしている周りのセル範囲に文字や数値が入力されていることが原因です。
エラーが発生した際は、並べ替え後のデータが表示されるセル範囲に文字が入力されていないか確認し削除してください。
「#VALUE!」が発生する場合は、SORTBY関数で指定したデータ範囲や列のセル範囲のサイズを確認してください。
下の図のように、SORTBY関数の第２引数のセル範囲が「C3:C11」とすべきところを「C3:C13」となっていると、「#VALUE!」は発生します。
SORTBY関数の事例を紹介
SORTBY関数の基本的な使い方は上記で紹介したとおりです。
続いて、SORTBY関数の応用編として、SORTBY関数を使った事例を４つ紹介します。
SORTBY関数/事例１：横方向に複数条件で並べ替え
SORTBY関数で、データを横方向に複数の列を基準に並べ替えすることもできます。
横方向に並べ替えするときは、SORTBY関数の第2引数「基準列1」と第4引数「基準列2」を水平方向にセル範囲を指定するだけです。
=SORTBY(C2:I4,C3:I3,1,C4:I4,-1)
そうすると、「性別」行を左から順に昇順に、「体重」行を左から順に降順に並べ替えすることができます。
SORTBY関数/事例２：データの追加を自動で反映
下の図のように最下行に元データを追加しても、残念ながらSORTBY関数の結果は自動で反映されません。
データを反映させるためには、G3セルに挿入したSORTBY関数で指定したセル範囲を修正する必要があります。
しかし、IF関数とSORTBY関数を組み合わせることで、データの追加を自動反映させることができるので、その方法を解説します。
元データを後から追加する前提で、SORTBY関数のセル範囲を空白セルも含めて広めに設定しておくと、SORTBY関数の結果は「0」と表示されます。
そのため、IF関数を使って、SORTBY関数の結果が「0」であればセルがブランクとなるように設定します。
利用する数式は以下のとおりです。
IF関数の第１引数「論理式」に「SORTBY関数の結果が0以外の場合」を設定し、第２引数「値が真の場合」にSORTBY関数、第3引数「値が偽の場合」に「””」を設定します。
=IF(SORTBY(B3:D11,C3:C11,1,D3:D11,-1)<>0,SORTBY(B3:D11,C3:C11,1,D3:D11,-1),"")
SORTBY関数/事例３：列の見出しの並び順も変更する
SORTBY関数の中にSORTBY関数を挿入することで、縦方向と横方向を同時に並べ替えすることができます。
例えば、下の表の「性別」列を昇順に「体重」列を降順に並べ替えし、さらに列の並びを「氏名⇒性別⇒体重」から「性別⇒体重⇒氏名」に変更するといった場合です。
それでは、数式の作成方法を手順に沿って解説します。
まず、空いているセルに並べ替え後の見出し列を作成します。次に、縦方向に並べ替えするため、SORTBY関数を作成します。
=SORTBY(B3:D11,C3:C11,1,D3:D11,-1)
次に、元データの見出しの上に、並べ替えしたい列の順番を数字で１，２，３・・と入力します。
最初に作成したSORTBY関数を囲うように、２つ目のSORTBY関数を囲います。
２つ目のSORTBY関数の第１引数は「１つ目のSORTBY関数」、第２引数は「見出しの上の数値のセル範囲」、第３引数は「１」を入力します。
=SORTBY(SORTBY(B3:D11,C3:C11,1,D3:D11,-1),B1:D1,1)
そうすると、上の表のように、列と行の並びを同時に変更することが出来ます。