エクセル入門
SORT関数、SORTBY関数(範囲を並べ替え)

Excelの初心者向け入門解説
公開日:2020-03-10 最終更新日:2020-05-01

SORT関数、SORTBY関数(範囲を並べ替え)


SORT関数は、範囲または配列の内容を並べ替えます。
SORTBY関数は、範囲または配列を対応する範囲または配列の値に基づいて並べ替えます。


SORT関数とSORTBY関数は範囲を並べ替える関数ですが、
同じこともできますが、れぞれの関数でなければできないこともあります。
関数仕様を確認した後、どのように使うかを実践的に見ていきましょう。

SORT関数とSORTBY関数はスピルで登場した新しい関数です。
・スピルとは ・スピルの数式例 ・ゴースト ・スピル範囲での独特な挙動について ・スピルのエラー表示 ・スピル範囲演算子 ・暗黙的なインターセクション演算子 ・従来のスピルしないエクセルとの互換性についての注意点 ・スピル関連記事

SORT関数の書式

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

配列
必須です。
並べ替え処理する元データを指定します。
セル範囲または配列です。

並べ替えインデックス
省略可能。
並べ替えのキーを指定します。
何列目(または何行目)を基準に並べ替えるかを数値で指定します。
範囲の左端列が1(または上端行が1)です。
この引数を省略すると、範囲の1列目(1行目)を基準として並べ替えます。
この引数は1つしか指定できません、つまり並べ替えに指定できるキーは1つだけです。

上記説明における何列目または何行目の違い
並べ替え基準で、
FALSE(行で並べ替え)の場合が何列目 ・・・ 通常の表ではこれ
TRUE(列で並べ替え)の場合が何行目

並べ替え順序
省略可能。
並べ替えの順序、「昇順」か「降順」を指定します。
1 : 昇順
-1 : 降順
この引数を省略すると、1(昇順)で並べ替えられます。

並べ替え基準
省略可能。
並べ替えを行方向に行うか列方向に行うかを指定します。
TRUE : 列で並べ替え ・・・ 横に並べ替える
FALSE : 行で並べ替え ・・・ 縦に並べ替える
この引数を省略するとFALSE(行で並べ替え)で並べ替えられます。
TRUE,FALSEは、1,0で指定しても構いません。

行・列の表現が分かりづらいので、間違えないようにしてください。
一般的なデータ(横に項目、縦にデータ)では、FALSE(行で並べ替え)になります。

SORTBY関数の書式

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

配列
必須です。
並べ替え処理する元データを指定します。
セル範囲または配列です。

基準配列
必須です。
並べ替えのキーとして使うセル範囲または配列を指定します。
SORT関数では元データ範囲の列位置(行位置)を数値で指定しましたが、SORTBY関数ではセル範囲または配列で指定します。
この基準配列は、元データ範囲内にある必要はありません。
元データ範囲とは全く別のセル範囲または配列を指定できます。

この基準配列の配列の向きによって、並べ替えの向きが決定されます。
つまり、
行方向(縦方向)の配列を指定した場合は行方向(縦方向)に並べ替えます。
列方向(横方向)の配列を指定した場合は列方向(横方向)に並べ替えます。
ここで指定する範囲または配列の大きさは、元データと同じ大きさにする必要があります。
行方向(縦方向)で並べ替える場合は、元データの行数と一致した縦の配列を指定します。
列方向(横方向)で並べ替える場合は、元データの列数と一致した横の配列を指定します。

並べ替え順序
省略可能。
並べ替えの順序、「昇順」か「降順」を指定します。
1 : 昇順
-1 : 降順
この引数を省略すると、1(昇順)で並べ替えられます。

SORTBY関数のまとめ

エクセル Excel SORT関数 SORTBY関数


SORT関数、SORTBY関数と、ワークシートの並べ替えの違い

ワークシートの並べ替えは、指定範囲のデータを並べ替えてしまいますが、
SORT関数SORTBY関数は、指定範囲のデータを並べ替えて別のセル範囲に出力します。
ワークシートの並べ替えでは、元表のデータをそのままにしておきたい場合は表範囲をコピーしてから行う必要があります。
しかし、これらの関数を使用すれば、このコピーが不必要になります。
ただし関数では、ワークシートの並べ替えにある以下の指定はできません。
・先頭行を見出しとして使用する。
・大文字と小文字を区別する
・ふりがなを使う

つまり、SORT関数、SORTBY関数では、
・全てデータ行として扱われる
・大文字と小文字を区別しない
・ふりがなを使わない

したがって、このような並べ替えが必要な場合は、今まで通りワークシートで並べ替えを行う必要があります。

最も単純な並べ替え

並べ替え対象内の指定列で並べ替えるだけならSORT関数が簡単です。

SORT関数

=SORT(A2:E51)

エクセル Excel SORT関数 SORTBY関数
※「なんちゃって個人情報」です。

1列目(A列)をキーとして行方向に昇順で並べ替えしています。
[並べ替えインデックス],[並べ替え順序],[並べ替え基準]
これらを全て省略しています。
省略せずに指定するなら、
=SORT(A2:E51,1,1,FALSE)

SORTBY関数

=SORTBY(A2:E51,A2:A51)

1列目(A列)をキーとして行方向に昇順で並べ替えしています。
[並べ替え順序]
これを省略しています。
省略せずに指定するなら、
=SORTBY(A2:E51,A2:A51,1)

引数において範囲を2度指定しなければならず、このような単純な並べ替えであればSORT関数を使ったほうが良いでしょう。

複数キーでの並べ替え

キーを連結した作業列を作成しキーとすれば様々な並べ替えに対応できますが、以下では作業列を作成せずに並べ替える場合の例になります。
※作業列を使って複数キーを結合して並べ替える事自体は決して悪いものではありません。

SORT関数で複数キー並べ替え

SORT関数単独ではできませんが、SORT関数をネストすれば可能です。
都道府県(E列) > 性別(D列) で並べ替えます。

=SORT(SORT(A2:E51,4),5)

エクセル Excel SORT関数 SORTBY関数

注意点としては、関数ネストの内側から順に実行される点になります。
上記では、性別で並べ替えた後に都道府県で並べ替えられます。
つまり、優先度の高い並べ替えを外側の関数で指定します。

場合によっては、このような使い方をすることもあるかもしれませんが、複数キーの場合はSORTBY関数が便利でしょう。

SORTBY関数で複数キー並べ替え

都道府県(E列) > 性別(D列) で並べ替えます。

=SORTBY(A2:E51,E2:E51,1,D2:D51,1)

並べ替え順序の1は省略できるので、以下でも同じです。
=SORTBY(A2:E51,E2:E51,,D2:D51,)
ただし、最後の,カンマは省略できないので注意してください。

列方向(横方向)で並べ替え

SORT関数で列方向(横方向)並べ替え

3行目の単価で昇順に並べ替えています。

=SORT(B1:F3,3,1,TRUE)

エクセル Excel SORT関数 SORTBY関数


SORTBY関数で列方向(横方向)並べ替え

=SORTBY(B1:F3,B3:F3)

エクセル Excel SORT関数 SORTBY関数

範囲を2度指定しなければならないので、このような場合はSORT関数を使ったほうが良いでしょう。
しかし、この下で説明しているように、並べ替え範囲外を指定できるSORTBY関数ならではの使い方があります。

並べ替え範囲(配列)以外の基準で並べ替える

SORT関数は、並べ替え範囲しか並べ替えのキーが指定できませんが、
SORTBY関数は、並べ替え範囲以外をセル範囲または配列で指定できます。

列方向(横方向)を指定順序で並べ替え

=SORTBY(A2:E51,{2,5,1,4,3})

エクセル Excel SORT関数 SORTBY関数

配列定数として、{2,5,1,4,3}これで指定していますが、もちろんセル範囲でも指定できます。

=SORTBY(A3:E52,A1:E1)

エクセル Excel SORT関数 SORTBY関数

さらに出力先の項目名を利用して、MATCH関数と組み合わせるとより便利に使えます。

=SORTBY(A2:E51,MATCH(A1:E1,G1:K1,0))

エクセル Excel SORT関数 SORTBY関数


ランダムに並べ替え

=SORTBY(A2:F51,RANDARRAY(ROWS(A2:A51)))

エクセル Excel SORT関数 SORTBY関数

RANDARRAY関数で行数分の乱数を作り、その乱数を基に並べ替えています。

VLOOKUPの結果で並べ替え

都道府県コードを別表から取得し、取得したコードで並べ替えます。

=SORTBY(A2:F51,VLOOKUP(F2:F51,O:P,2,FALSE))

エクセル Excel SORT関数 SORTBY関数

このように、他の関数(特にVLOOKUP系)で取得した順番で並べ替える方法は、今後は頻繁に使われるようになるかもしれません。
ただし、並べ替え後の結果を見てもそれが正しいかの確認がかなり困難になります。
可能な限り作業列を作成し、その列に他の関数の結果を出力して、それを並べ替えのキーとして使うことをお勧めします。

列全体を範囲指定する場合

スピル関数で一番困るのが、列全体を指定しづらい事です。
単純に列全体を指定すると、SORT関数およびSORTBY関数はエラーとなってしまいます。

エクセル Excel SORT関数 SORTBY関数

そこで、FILTER関数でデータが空白以外(<>"")の行だけに絞ってみると、

=SORT(FILTER(A:E,A:A<>""))

エクセル Excel SORT関数 SORTBY関数

一見良さそうですが、見出し行まで並べ替えに入ってしまいます。
上記の場合の解決方法としては、データの入っている行の判定方法を工夫することで対応できます。

エクセル Excel SORT関数 SORTBY関数

データには、大抵はこのように数値しか入っていない列が存在するはずなので、この手法は幅広く使えるはずです。
もちろん数値とは限らず、データと見出しを区別することができる判定ならどのような条件でも構いません。
ただし、
AND関数で複数条件を指定するとエラーとなってしまうので、単一条件だけで済むようにしてください。

スピルによって新しく追加された関数

関数名 説明
FILTER フィルターは定義した条件に基づいたデータ範囲です。
SORT 範囲または配列の内容を並べ替えます。
SORTBY 範囲または配列の内容を、対応する範囲または配列の値に基づいて並べ替えます。
UNIQUE 一覧表または範囲内から重複データを削除した一覧を返します。
RANDARRAY 0から1までのランダムな数値の配列を返します。
SEQUENCE 1、2、3、4など、配列内の連続した数値の一覧を生成します。
XLOOKUP 範囲または配列を検索し、見つかった最初の一致に対応する項目を返します。
一致が存在しない場合、XLOOKUP は最も近い (概算) 一致を返すことができます。
XMATCH 配列またはセル範囲内の項目の相対的な位置を返します。


ワークシート関数一覧

・文字列関数 ・日付と時刻の関数 ・情報関数 ・論理関数 ・検索/行列関数 ・数学/三角関数 ・統計関数 ・財務関数 ・エンジニアリング関数 ・データベース関数 ・アドイン/オートメーション ・キューブ ・Microsoft Office のサポート

ワークシート関数の一覧(2010以降)
・Excel2010で追加されたワークシート関数 ・Excel2013で追加されたワークシート関数 ・Excel2016/Excel2019で追加されたワークシート関数 ・Excel2021で追加されたワークシート関数 ・Excel2021より後の365で追加されたワークシート関数 ・Microsoft 365 Insider ・Microsoft「Office のサポート」



同じテーマ「エクセル入門」の記事

XLOOKUP関数とスピル入門
スピルについて
FILTER関数(範囲をフィルター処理)
SORT関数、SORTBY関数(範囲を並べ替え)
UNIQUE関数(一意の値)
RANDARRAY関数(ランダム数値)
SEQUENCE関数(連続数値)
XLOOKUP関数(VLOOKUP関数を拡張した新関数)
XMATCH関数(MATCH関数を拡張した新関数)
LET関数(数式で変数を使う)
スピルと新関数の練習(XLOOKUP関数、LET関数、VBAまで)


新着記事NEW ・・・新着記事一覧を見る

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ