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

Excelの初心者向け入門解説
最終更新日:2020-03-20

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


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


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

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

SORT関数とSORTBY関数はスピルで登場した新しい関数です。
2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。今までは数式を入れたセルにしか結果を出せませんでしたが、スピルでは隣接するセルにまで結果が表示されます。

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 配列またはセル範囲内の項目の相対的な位置を返します。


ワークシート関数一覧

Excelワークシート関数の一覧と解説です、詳細解説ページへのリンクもあります。Excel2007までの関数です。2010以降追加の関数一覧 ワークシート関数の一覧(2010以降) 引数、関数構文については Excelシートの複雑な計算式を解析するVBAの関数構文 文字列関数…27 日付と時刻の関数…21 情報関数…17 論理関数…7 検索/行列関数…1…

ワークシート関数の一覧(2010以降)
Excelワークシート関数の一覧と解説です。Excel2010以降に追加された関数一覧になります。2007までのワークシート関数一覧ワークシート関数の一覧 引数、関数構文については Excelシートの複雑な計算式を解析するVBAの関数構文 Excel2010で追加されたワークシート関数 Excel2013で追加されたワークシート関数 関数名 種類と説明 …



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

IFS関数(複数条件)
SWITCH関数(複数値での切替)
スピルについて
FILTER関数(範囲をフィルター処理)
SORT関数、SORTBY関数(範囲を並べ替え)
UNIQUE関数(一意の値)
RANDARRAY関数(ランダム数値)
SEQUENCE関数(連続数値)
XLOOKUP関数(範囲を検索し一致する対応項目を返す)
XMATCH関数(範囲から値を検索し一致する相対位置)
LET関数(数式で変数を使う)


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

ユーザーに絶対に停止させたくない場合のVBA設定|VBA技術解説(4月1日)
CharactersプロパティとCharactersオブジェクト|VBA技術解説(3月31日)
指数近似/対数近似/累乗近似(掲載順位とCTR)|エクセル関数超技(3月31日)
練習問題32(連続数値部分を取り出し記号で連結)|VBA練習問題(3月24日)
連続数値部分を取り出し記号で連結|エクセル関数超技(3月24日)
数式バーの高さを数式の行数で自動設定|VBAサンプル集(3月21日)
LET関数(数式で変数を使う)|エクセル入門(3月21日)
スピルに対応したXSPLITユーザー定義関数(文字区切り)|VBAサンプル集(3月15日)
XMATCH関数(範囲から値を検索し一致する相対位置)|エクセル入門(3月14日)
XLOOKUP関数(範囲を検索し一致する対応項目を返す)|エクセル入門(3月14日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.変数宣言のDimとデータ型|VBA入門
7.ひらがな⇔カタカナの変換|エクセル基本操作
8.繰り返し処理(For Next)|VBA入門
9.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
10.セルに文字を入れるとは(Range,Value)|VBA入門




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


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



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