SORT関数とSORTBY関数の使い方: 数式で並べ替える

=SORT(A2:C7,3,-1)は、表A2:C7を3列目の売上で大きい順に並べ替えて返します。元の表はそのままで、並べ替えたコピーが数式のセルからスピルし、数値が変わると並べ替え直されます。C7を300に変えると、Finnがいちばん上に移ります。

数式リセット

E2fx

=SORT(A2:C7,3,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Cara	North	200
3	Ben	South	80		Dan	East	150
4	Cara	North	200		Ann	North	120
5	Dan	East	150		Eve	South	95
6	Eve	South	95		Ben	South	80
7	Finn	North	60		Finn	North	60

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

SORT関数の構文

=SORT(array, [sort_index], [sort_order], [by_col])

• array(配列)は並べ替えて返す範囲です。
• sort_index(並べ替えインデックス)は、arrayの中で基準にする列の番号です。既定は1です。
• sort_order(並べ替え順序)は、昇順なら1(既定)、降順なら-1です。
• by_col(並べ替え基準)は、行を並べ替えるならFALSE(既定)、列を左から右へ並べ替えるならTRUEです。

SORTとSORTBYにはExcel 2021、Excel 2024、Microsoft 365のいずれかが必要で、それより古いバージョンでは#NAME?になります。Googleスプレッドシートにも両方ありますが、SORTは列番号とTRUEかFALSEを組で受け取ります。

昇順では、数値が先に、次に文字列がAからZの順に、その後にFALSEとTRUEが並びます。文字列は大文字と小文字を区別せずに比べます。arrayの空のセルは、結果では0として返ります。

Excelでアルファベット順に並べる

リストをその場で並べ替えるには、リストのどれかのセルをクリックして データ > 昇順(または降順)を選びます。リストの隣に列があると、Excelは各行がまとまったまま表全体を並べ替えます。2つ以上の列やユーザー設定リストで並べ替えるには、データ > 並べ替え を使います。

元の順番を残したまま、自動で更新されるアルファベット順のコピーを作るには、ほかの引数なしでSORTを使います：

数式リセット

C2fx

=SORT(A2:A8)

	A	B	C
1	Name		A to Z
2	Mia		Ann
3	Ben		Ben
4	Zoe		Cara
5	Ann		Dan
6	Leo		Leo
7	Cara		Mia
8	Dan		Zoe

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

A3にAbeと入力すると、Abeが最初に表示されます。=SORT(A2:A8,,-1)はZからAの順になります。2つのカンマでsort_indexを飛ばしています。重複のないリストをアルファベット順にするには、UNIQUEの結果を並べ替えます：=SORT(UNIQUE(A2:A8))(UNIQUEを参照)。

SORTBY: 返さない列を基準に並べ替える

SORTは、返す範囲の中の列でしか並べ替えられません。SORTBYは並べ替えの基準を別の範囲として受け取るので、名前だけを売上の順に返せます：

数式リセット

E2fx

=SORTBY(A2:A7,C2:C7,-1)

	A	B	C	D	E
1	Name	Region	Sales		By sales
2	Ann	North	120		Cara
3	Ben	South	80		Dan
4	Cara	North	200		Ann
5	Dan	East	150		Eve
6	Eve	South	95		Ben
7	Finn	North	60		Finn

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

結果はCara、Dan、Ann、Eve、Ben、Finnです。構文は=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)です。各by_arrayはarrayと同じ行数でなければならず、そうでないとSORTBYは#VALUE!を返します。

上位3件なら、結果の最初の行を取り出します。=TAKE(SORTBY(A2:A7,C2:C7,-1),3)はCara、Dan、Annを返します。TAKEにはMicrosoft 365かExcel 2024が必要で、Excel 2021では=INDEX(SORTBY(A2:A7,C2:C7,-1),SEQUENCE(3))を使います。

複数の列で並べ替える

SORTBYには、段階ごとに基準と順序の組を1つずつ渡します。次の数式は地域をAからZの順に並べ、各地域の中では売上を大きい順に並べます：

数式リセット

E2fx

=SORTBY(A2:C7,B2:B7,1,C2:C7,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Dan	East	150
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Ann	North	120
5	Dan	East	150		Finn	North	60
6	Eve	South	95		Eve	South	95
7	Finn	North	60		Ben	South	80

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

Eastが最初で、次にNorthの3行がCara(200)、Ann(120)、Finn(60)の順に並び、その後にSouthが続きます。SORTでも列番号と順序の配列で同じことができますが、SORTBYのほうが読みやすくなります：

=SORT(A2:C7,{2,3},{1,-1})

自分で決めた順番で並べ替える

アルファベット順が欲しい順番とは限りません。地域をNorth、East、Southの順に並べるには、MATCHで各地域をそのリストの中の位置に変え、その位置で並べ替えます：

数式リセット

E2fx

=SORTBY(A2:C7,MATCH(B2:B7,{"North","East","South"},0))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Finn	North	60
5	Dan	East	150		Dan	East	150
6	Eve	South	95		Ben	South	80
7	Finn	North	60		Eve	South	95

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

MATCHはNorthに1、Eastに2、Southに3を返し、SORTBYはその数値で並べ替えます。基準が同じ行は元の順番を保つので、Northの行はAnn、Cara、Finnのままです。同じ方法で、月や曜日を名前で並べ替えられます。MATCHの中にカレンダーの順に並べておきます。リストにない地域はMATCHが#N/Aを返します。そうした行を最後に回すには、基準にIFERROR(MATCH(B2:B7,{"North","East","South"},0),99)を使います。

練習: 売上の順に並べた名前

数式リセット

E2fx

	A	B	C	D	E
1	Name	Region	Sales		By sales
2	Ann	North	120
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: E2で、名前だけを売上の大きい順に返してください。

答え合わせ答えを見る

SORT関数と並べ替えボタン

どちらも並べ替えますが、役割が違います：

表を一度整えるならボタンを使います。データが変わっても並べ替えた状態を保たなければならないレポートやダッシュボードには関数を使います。数式の下や右のセルが空でないと、関数は並べ替えたリストの代わりに#SPILL!を表示します。そのセルを空にすると表示されます(#SPILL!エラーを参照)。