エクセル SORTBY 関数:セルの範囲をキーを指定して並べ替えする

はじめに

エクセルの SORTBY 関数の使い方を紹介します。

SORTBY 関数は指定したセルの範囲や配列をキーを指定して並べ替えた配列を取得します。

指定したセルの範囲外の値を元に並び替えしたいときや、複数のキーを指定したいときに使用します。

この関数は 365 で使用できます。2019 以前のバージョンでは使用できません。
  • 目次
    • 構文
    • 使用例
    • 解説

構文

SORTBY(範囲, 基準範囲1)
基準範囲を昇順に並べ替えし、それに対応して範囲を行単位に並べ替えた配列を取得します。
基準範囲を昇順に並べ替えし、それに対応して範囲を列単位に並べ替えた配列を取得します。

SORTBY(範囲, 基準範囲1, 順序1, 基準範囲2, 順序2 …)
基準範囲で並べ替えた時に同じ値があるときは、その値を次の基準範囲を元に並べ替えできます。順序で降順に並べ替えるように指定できます。

引数「範囲」セルの範囲や配列を指定します。
引数「基準範囲」並べ替えの元となるデータの範囲を 1 行または 1 列で指定します。
引数「順序」省略できます。
1 または省略:昇順で並べ替えをします。
-1:降順で並べ替えをします。

使用例

結果は配列になり複数のセルに表示されます。これはスピルという機能によるものです。詳しくは「スピル、動的配列数式の使い方」をご覧ください。

名前を個数を元に行単位で並べ替えする。

=SORTBY(B3:B6,C3:C6)
1

個数を元に行単位で並べ替えする。

=SORTBY(B3:C6,C3:C6)
2

個数を元に行単位で降順に並べ替えする。

=SORTBY(B3:C6,C3:C6,-1)
3

個数を元に列単位で並べ替えする。

=SORTBY(C2:F3,C3:F3)
4

バージョンを元に並べ替えをし、同じ値のときは個数を元に並べ替えする。

=SORTBY(B3:D6,C3:C6,1,D3:D6,1)
5

ランダムに並べ替え

ランダムに並べ替えたいときは引数「基準範囲」に「RANDARRAY 関数」を使用します。

=SORTBY(B3:B6,RANDARRAY(4))
6

セルが再計算されるたびに結果が変わります。固定するには=SORTBY(RANDARRAY())を入力してから F9 キーで確定します。数式の関数が結果に変わります。

上記ではRANDARRAY(4)と行数を固定で入力しています。それを動的に変更するには「COUNTA 関数」を使用します。同じセルの範囲を複数入力する必要があるので、それを 1 回にするには「LET 関数」を使用します。

=LET(
範囲,B3:B6,
SORTBY(範囲,RANDARRAY(COUNTA(範囲))))

文字列

文字列で並べ替えする。

=SORTBY(B3:C6,B3:B6)
7

日付

日付で並べ替えする。

結果が数値になっているのは日付の実際の値が数値だからです。セルの書式設定を指定して日付形式に表示できます。

=SORTBY(B3:C6,B3:B6)
8

解説

引数「基準範囲」を並べ替えし、それに対応して引数「範囲」を並べ替えた結果を配列で取得します。

引数「基準範囲」が 1 行のとき、引数「範囲」を列単位に並べ替えます。

引数「基準範囲」が 1 列のとき、引数「範囲」を行単位に並べ替えます。

引数「基準範囲」行を指定した時は、その後の引数「基準範囲」も行である必要があります。列についても同じです。そうでないときはエラー#VALUE!になります。

引数「基準範囲」と引数「範囲」の行数または列数が一致していないときはエラー#VALUE!になります。

引数「基準範囲」に複数の行列を指定するとエラー#VALUE!になります。

引数「順序1」は省略できますが、引数「順序2」以降は省略できません。

同じ値のとき

引数「基準範囲」に同じ値があるときは、その値を次の引数「基準範囲」で並べ替えをします。それを指定されている数だけ繰り返します。

最後の引数「基準範囲」にも同じ値があるときは、必ず出現した順になります。次の B と C が同じ個数なので、昇順でも降順でも必ずB, Cの順に並べ替えられます。

=SORT(B3:C6,2)
9

文字列の順番

文字の UNICODE の値の順になります。「UNICODE 関数」を使用して、その文字の値を取得できます。

ABCADEを並べ替えるときは、左側の文字から 1 文字ずつ比較します。

1 回目にAAを比較して同じなので次の文字を比較します。

2 回目にBDを比較してBが小さいので、その順に並べ替えます。

何も入力していないのが一番小さな値です。

  • 目次
    • 構文
    • 使用例
    • 解説