2021～365

SORT / SORTBY：数式で動的に「並び替え」を実現する

💡 やりたいこと

「データベースから抽出した結果を、売上の高い順（降順）に自動で並べたい」
「わざわざデータ表にフィルターをかけて、手動で[昇順に並び替え]ボタンを押す作業をなくしたい」

新関数での解決法：SORT / SORTBY

SORT関数もまた、従来は手作業（またはマクロ）でしかできなかったアクションを「関数ベースで全自動化」した革命的なスピル関数の1つです。
元データが更新されれば、抽出先の一覧も自動的に「常にソートされ直した状態」を保ちます。

SORT（配列の中の列番号を基準に並び替え）

=SORT(配列, [並び替えの基準列番号], [順序: 1昇順/-1降順], [列方向: TRUE/FALSE])

=SORT(A2:E1000, 5, -1)

意味：A2:E1000の範囲を、左から5番目の列（E列＝売上など）の値を基準にして「-1（降順＝大きい順）」に並び替える。

SORTBY（配列「外」の範囲も含めて複数条件で並び替え）

SORT単体では「複数の列を基準にしたソート（第1キーは部署順、第2キーは売上順など）」がやりにくいですが、SORTBYを使えば自在です。

=SORTBY(配列, 基準配列1, [順序1], 基準配列2, [順序2], ...)

=SORTBY(A2:E1000, B2:B1000, 1, E2:E1000, -1)

意味：A2:E1000を、まず「B列（部署）の昇順」で並べ、次にその中で「E列（売上）の降順」で並べる。
※SORTBYは列番号での指定ではなく、範囲そのもの（B2:B1000など）で指定するのが特徴。

🔥 FILTER・UNIQUEとの合わせ技（THE・神コンボ）
=SORT(UNIQUE(A:A)) （マスタ一覧をあいうえお順に）
=TAKE(SORT(FILTER(A2:E100, B2:B100="東京"), 5, -1), 10)
（東京のデータだけを抽出し、売上の高い順に並び替え、その上位10件だけを表示する）
このように、関数をマトリョーシカのように入れ子（ネスト）にしていくことで、VBAで数十行書かざるを得なかった集計処理が1行で完結します。

🔍 SORT vs SORTBY ── どう使い分ける？

① 2つの関数の引数を並べて比較

データテーブル
比較項目	SORT	SORTBY
基準列の指定方法	列「番号」で指定 `=SORT(A:E, 3, -1)` ← 3列目で降順	列「範囲」で指定 `=SORTBY(A:E, C:C, -1)` ← C列で降順
複数キーのソート	配列定数で可能だがやや書きにくい `=SORT(A:E, {3,5}, {1,-1})`	引数をペアで追加するだけ直感的で読みやすい `=SORTBY(A:E, C:C, 1, E:E, -1)`
配列「外」の値でソート	❌ 不可（配列内の列しか基準にできない）	✅ 可能（別シートの列や計算式も基準にできる）
計算値でソート（文字数・月日など）	❌ 不可	✅ 可能 `=SORTBY(A:B, LEN(A:A))`
カスタム順序でソート（高・中・低など）	❌ 不可	✅ 可能 MATCH/XMATCHと組み合わせ
横方向（列方向）のソート	第4引数 `TRUE` で明示指定	基準配列の形状から自動判定
列の追加・削除への耐性	弱い（列番号がズレる）	強い（範囲参照なのでズレない）

💡 使い分けの結論
「単純な1列ソート」→ SORT で十分（最も短く書ける）
「複数キー / 計算値 / カスタム順序 / 配列外の基準」→ SORTBY 一択

📘 SORT関数 ── 引数と挙動を深掘り

② 全4引数の詳細

データテーブル
引数	必須?	説明	既定値
配列	必須	ソート対象のセル範囲 or 配列	─
sort_index	任意	何列目（横方向なら何行目）を基準にするか	1（先頭列）
sort_order	任意	1 = 昇順（A→Z / 小→大） -1 = 降順（Z→A / 大→小）	1（昇順）
by_col	任意	TRUE = 列方向（横）にソート FALSE = 行方向（縦）にソート	FALSE（行方向）

③ 基本例：1列ソート（名前を五十音順に）

データテーブル
	A	B	D	E
1	名前	売上	名前	売上
2	田中	500	伊藤	180
3	佐藤	320	加藤	210
4	伊藤	180	佐藤	320
5	山田	410	田中	500
6	加藤	210	山田	410

=SORT(A2:B6)

D2に入力 → D2:E6にスピル。引数省略で「1列目の昇順」。名前の五十音（文字コード）順に全列がまとめて並び替わります。

④ 売上の降順でソート

=SORT(A2:B6, 2, -1)

データテーブル
	D	E
2	田中	500
3	山田	410
4	佐藤	320
5	加藤	210
6	伊藤	180

2列目（売上）を基準に降順。ランキング表の自動生成に直結します。

⑤ 複数キーのソート（配列定数を使う方法）

SORTで複数列を基準にするには、sort_index と sort_order に 配列定数 {} を使います。

データテーブル
	A	B	C
1	部署	名前	売上
2	営業	田中	500
3	開発	佐藤	320
4	営業	伊藤	180
5	開発	山田	410
6	営業	加藤	210

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

第1キー：1列目（部署）を昇順 → 第2キー：3列目（売上）を降順。
結果：営業グループの中では売上の高い順、開発グループの中でも売上の高い順に並ぶ。

データテーブル
	E	F	G	← 結果
2	営業	田中	500
3	営業	加藤	210
4	営業	伊藤	180
5	開発	山田	410
6	開発	佐藤	320

⚠️ 配列定数 {1,3} と {1,-1} の対応
{1,3} の「1」が部署列（昇順1）、「3」が売上列（降順-1）に対応。
順序と基準が1対1で対応しているので、数が一致しないとエラーになります。

📗 SORTBY関数 ── SORTにはできない技

⑥ SORTBYの複数キーソート（より直感的）

上記と同じ「部署昇順 → 売上降順」を SORTBYで書くとこうなります。

=SORTBY(A2:C6, A2:A6, 1, C2:C6, -1)

配列定数ではなく「範囲, 順序」のペアを繰り返すだけ。列の追加・削除があっても列番号がズレないので保守性が高い。

⑦ 計算値でソート：文字数順

「名前の短い順に並べたい」など、データそのものにはない「計算した値」を基準にする場合、SORTBYの独壇場です。

データテーブル
	A	C
1	国名	結果
2	Japan	Cuba
3	Australia	Japan
4	Cuba	France
5	France	Australia

=SORTBY(A2:A5, LEN(A2:A5))

LEN（文字数）の結果を基準にソート。Cuba(4) → Japan(5) → France(6) → Australia(9) の順になる。

⑧ カスタム順序ソート（高→中→低など）

「高・中・低」のような独自の優先順位で並べたい場合、XMATCH（またはMATCH）で順位を数値化し、それを基準にSORTBYへ渡します。

データテーブル
	A	B	D
1	タスク	優先度	順序マスタ
2	レポート作成	低	高
3	顧客対応	高	中
4	資料整理	中	低
5	見積提出	高

=SORTBY(A2:B5, XMATCH(B2:B5, D2:D4))

XMATCH がマスタ内の位置番号（高=1, 中=2, 低=3）を返し、SORTBYがその数値順に並べる。
結果：顧客対応(高) → 見積提出(高) → 資料整理(中) → レポート作成(低)

⑨ ランダムシャッフル

=SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10)))

RANDARRAYが乱数を生成し、SORTBYがその乱数の小さい順に並べる → 毎回ランダムな順序になる。
抽選・座席シャッフルなどに便利。シートを再計算するたびに順序が変わります。

⑩ 日付の月日だけでソート（誕生日カレンダー）

=SORTBY(A2:B10, TEXT(B2:B10, "mmdd"))

TEXT関数で月日だけを "0718" のような文字列に変換し、それを基準にソート。
年を無視した「カレンダー順」が実現できる。

🔒 知っておくべき：安定ソート（Stable Sort）

SORT・SORTBYはどちらも「安定ソート」です。これは「ソート基準の値が同じ行は、元データでの並び順がそのまま維持される」ということを意味します。

データテーブル
	A	B	D	E
1	名前	部署	名前	部署
2	田中	営業	田中	営業
3	佐藤	開発	伊藤	営業
4	伊藤	営業	佐藤	開発
5	山田	開発	山田	開発

=SORT(A2:B5, 2, 1)

部署の昇順でソート。営業が2人（田中・伊藤）いるが、元データの並び順（田中→伊藤）がそのまま維持される。
これが「安定ソート」。旧来の LARGE+INDEX+MATCH では実現が困難でした。

🚀 他の関数との組み合わせ ── 実務の神コンボ集

SORT / SORTBY の真価は、他のスピル関数と組み合わせたときに発揮されます。ここからが本番です。

コンボ① FILTER + SORT：条件抽出 → 並び替え

最も使用頻度が高い鉄板の組み合わせ。FILTERで絞り込んだ結果を、そのままSORT（またはSORTBY）に渡します。

データテーブル
	A	B	C	D
1	地域	商品	売上	月
2	東京	PC	500	4月
3	大阪	モニター	200	4月
4	東京	キーボード	80	5月
5	東京	マウス	30	4月
6	大阪	PC	450	5月
7	東京	モニター	350	5月

=SORT(FILTER(A2:D7, A2:A7="東京"), 3, -1)

データテーブル
	F	G	H	I	← 結果
2	東京	PC	500	4月
3	東京	モニター	350	5月
4	東京	キーボード	80	5月
5	東京	マウス	30	4月

FILTERで東京だけ抽出 → SORTで3列目（売上）の降順に並び替え。
元データが追加されても自動で再計算されます。

💡 SORTBYでFILTER結果を並べる場合の注意
SORTBYの基準配列はFILTER後の配列と同じサイズでなければなりません。
=SORTBY(FILTER(A2:D7, A2:A7="東京"), FILTER(C2:C7, A2:A7="東京"), -1)
このように、基準配列にも同じFILTERを適用する必要があります。
面倒な場合は SORT(..., 列番号, 順序) の方が簡潔です。

コンボ② FILTER + SORT + TAKE：Top N 抽出（上位○件）

=TAKE(SORT(FILTER(A2:D7, A2:A7="東京"), 3, -1), 3)

東京のデータを売上降順に並べ、上位3件だけを返す。
TAKEの第2引数が取得行数。マイナスにすると末尾から取得できます。

データテーブル
	F	G	H	I
2	東京	PC	500	4月
3	東京	モニター	350	5月
4	東京	キーボード	80	5月

コンボ③ UNIQUE + SORT：重複排除 → 五十音順マスタ

=SORT(UNIQUE(B2:B100))

B列から重複を除去し、昇順に並べる。
ドロップダウンリスト（入力規則）のソースや、集計用のマスタ一覧の自動生成に最適。

=SORT(UNIQUE(A2:C100), 3, -1)

複数列の組み合わせで重複排除し、3列目の降順でソート。

コンボ④ XLOOKUP + SORT：検索結果をソートして表示

XLOOKUPが複数列を返す場合、その結果をさらにSORT / SORTBYで並べ替えるパターンです。

データテーブル
	A	B	C	D	E
1	社員ID	名前	4月	5月	6月
2	E001	田中	120	150	180
3	E002	佐藤	200	180	220
4	E003	伊藤	90	110	100

「E002の月別売上を降順で見たい」場合：

=SORT(XLOOKUP("E002", A2:A4, C2:E4), , -1, TRUE)

XLOOKUPで E002 の行（200, 180, 220）を横方向に取得。
SORTの第4引数 TRUE で横方向ソート → 220, 200, 180 の降順で返る。

コンボ⑤ SORTBY + XLOOKUP：別テーブルの値を基準にソート

SORTBYの基準配列にXLOOKUPを入れると、「別テーブルにある値を基準にして、手元のテーブルを並べ替える」ことができます。

データテーブル
	A	B	D	E
1	商品ID	在庫数	商品ID	優先度
2	P-101	25	P-101	3
3	P-205	42	P-205	1
4	P-312	150	P-312	2

=SORTBY(A2:B4, XLOOKUP(A2:A4, D2:D4, E2:E4))

XLOOKUPが各商品IDの優先度（1, 2, 3）を返し、SORTBYがその値で昇順ソート。
結果：P-205(優先度1) → P-312(優先度2) → P-101(優先度3) の順に並ぶ。
在庫テーブルに「優先度」列を追加しなくても、別テーブルの値で並べ替えられるのがポイント。

コンボ⑥ FILTER + SORT + UNIQUE：フル・コンボ

=SORT(UNIQUE(FILTER(A2:C100, B2:B100="営業")), 3, -1)

① FILTER で「営業」部門だけを抽出
② UNIQUE で重複行を排除
③ SORT で 3列目（売上）の降順に並べ替え
→ 営業部門のユニークなデータが売上順で一覧になる。

コンボ⑦ LET + SORT：複雑な数式を読みやすく

ネストが深くなったら LET関数で中間結果に名前をつけると格段に読みやすくなります。

=LET(
    data,    A2:D100,
    filtered, FILTER(data, INDEX(data,,1)="東京"),
    sorted,   SORT(filtered, 3, -1),
    TAKE(sorted, 10)
)

LET関数で data → filtered → sorted と名前をつけて段階的に処理。
最終行の TAKE(sorted, 10) で上位10件を返す。
やっていることは「=TAKE(SORT(FILTER(...),3,-1),10)」と同じだが、読みやすさが段違い。

⚡ SORTが切り拓く「バイナリ検索」── 大規模データの速度革命

SORT関数の活用法は「見た目の並び替え」だけではありません。SORT で作ったソート済みデータを XLOOKUP や XMATCH のバイナリ検索モードに渡すことで、数万〜数十万行の検索を劇的に高速化できます。これは実務で最も見落とされがちな、しかし最もインパクトの大きい SORT の使い方です。

⑪ 線形検索 vs バイナリ検索 ── 何がどう違うのか

データテーブル
比較項目	線形検索（デフォルト）	バイナリ検索（search_mode=2）
アルゴリズム	先頭から1件ずつ順番にチェック	中央で半分に分割 → 該当する半分をさらに半分… を繰り返す
データのソート	不要	必須（昇順 or 降順）
10万行の最悪ケース	100,000回の比較	最大17回の比較
100万行の最悪ケース	1,000,000回の比較	最大20回の比較
計算量（Big O）	O(n) ── データ量に比例	O(log n) ── データ量の対数
体感速度	数千行まではOK。万単位で重くなる	100万行でも一瞬

🔥 衝撃の数字
100万行のデータから1件を探す場合、線形検索は最悪100万回の比較が必要です。
バイナリ検索ならたった20回。これは約5万倍の差です。
XLOOKUP数式を数百〜数千行にコピーしているシートでは、この差がシート全体の再計算時間に直結します。

⑫ XLOOKUP / XMATCH でバイナリ検索を有効にする

どちらの関数も、最後の引数 search_mode に 2（昇順）または -2（降順）を指定するだけです。

データテーブル
関数	通常（線形検索）	バイナリ検索（昇順データ）
XLOOKUP	`=XLOOKUP(値, 検索列, 戻り列)`	`=XLOOKUP(値, 検索列, 戻り列, , 0, 2)`
XMATCH	`=XMATCH(値, 検索列)`	`=XMATCH(値, 検索列, 0, 2)`

⚠️ バイナリ検索の絶対条件
検索列のデータが正しくソートされていること。ソートされていない状態でバイナリ検索を使うと、エラーにならず間違った結果が返ります。見た目は正常なので発見が非常に困難です。

⑬ 実践ワークフロー：SORT で作業シートを作り、バイナリ検索で爆速化

元データがソートされていないケースは実務で非常に多いです。「入力順」「更新順」などバラバラの並びが普通です。
そんなときこそ SORT関数で「ソート済みの作業シート」を1回だけ自動生成し、以後のすべての検索をバイナリ検索で行うアーキテクチャが有効です。

ステップ1：作業シート「_sorted」にソート済みデータをスピル

シート「_sorted」のA1セルに入力する数式（1つだけ）

=SORT(rawData[#All], 1, 1)

rawData はExcelテーブル名。1列目（例：商品ID）の昇順でソートされた全データが、作業シートにスピルで展開されます。
元データが更新されるたびにこのシートも自動更新されるので、手動でソートし直す必要はありません。

ステップ2：検索シートからバイナリ検索で参照

検索シートの各行に入力する数式

=XLOOKUP(A2, _sorted!A:A, _sorted!C:C, "", 0, 2)

_sorted!A:A（ソート済みの商品ID列）に対して、search_mode = 2（バイナリ検索・昇順）を指定。
見つからない場合は空文字 "" を返す。

構成図

データテーブル
シート「rawData」（元データ・未ソート）	→ SORT関数（1回だけ）	シート「_sorted」（ソート済み・自動更新）	→ XLOOKUP バイナリ検索	シート「検索結果」（爆速で値を取得）
商品ID：P-312, P-101, P-510... （入力順・バラバラ）		商品ID：P-101, P-205, P-312... （昇順にソート済み）		XLOOKUP(..., 0, 2) で各行を高速検索

💡 なぜ「作業シート」なのか？
XLOOKUP の中に直接 SORT を入れる（=XLOOKUP(A2, SORT(rawData[ID]), SORTBY(rawData[金額], rawData[ID]), "", 0, 2)）ことも可能ですが、検索の数式1つごとにSORT計算が走るため、数百行にコピーすると逆に遅くなる場合があります。
作業シートに SORT を1回だけスピルさせ、その結果を参照する構成にすれば、ソート処理は1回きりで済みます。

⑭ INDEX + XMATCH でも同じ高速化が可能

=INDEX(_sorted!C:C, XMATCH(A2, _sorted!A:A, 0, 2))

XMATCHの第4引数 2 でバイナリ検索を有効化。XLOOKUPと同じ速度で動作する。
見つからない場合のハンドリングは IFNA で囲む：
=IFNA(INDEX(_sorted!C:C, XMATCH(A2, _sorted!A:A, 0, 2)), "")

⑮ 応用：数式内で完結する「自己ソート型」バイナリ検索

作業シートを作りたくない場合、XLOOKUP の検索配列・戻り配列を数式内で直接ソートする方法もあります。

=XLOOKUP(
    A2,
    SORT(rawData[商品ID]),
    SORTBY(rawData[金額], rawData[商品ID]),
    "",
    0,
    2
)

⚠️ 「自己ソート型」の注意点
検索列は SORT(rawData[商品ID]) でソート、戻り列は SORTBY(rawData[金額], rawData[商品ID]) で同じキーでソートしないと行がズレて誤った値が返ります。
また、この数式を100行にコピーすると SORT / SORTBY が100回ずつ計算されるため、データが大きい場合は作業シート方式（⑬）の方が高速です。検索が少数（10件以下など）なら問題ありません。

⑯ LETで読みやすくする

自己ソート型の数式はLETで整理すると可読性が大幅に向上します。

=LET(
    key,    rawData[商品ID],
    val,    rawData[金額],
    s_key,  SORT(key),
    s_val,  SORTBY(val, key),
    XLOOKUP(A2, s_key, s_val, "", 0, 2)
)

LETの中で中間変数を定義。s_key / s_val にソート済み配列を入れ、最終行のXLOOKUPでバイナリ検索。

⑰ バイナリ検索を使うべきタイミング

データテーブル
状況	推奨	理由
データ数千行 × 検索数十回	線形検索で十分	体感差なし。設定の手間がもったいない
データ数万行 × 検索数百回	バイナリ検索を検討	再計算で数秒〜十数秒かかり始める境界
データ10万行以上 × 検索数百〜数千回	バイナリ検索を強く推奨	線形検索では「応答なし」が頻発するレベル
データが頻繁に更新される	作業シート方式（⑬）	SORT関数なら自動でソート維持。手動ソート不要
検索が1〜10件程度	自己ソート型（⑮）でOK	ソート計算の繰り返しが少ないので問題なし

💡 SORT関数がバイナリ検索を「実用化」した
旧来のExcelでもバイナリ検索的な手法（MATCH関数の match_type = 1）は存在しました。しかし元データを手動でソートしておく必要があり、データが追加・更新されるたびにソートし直すのが現実的ではありませんでした。

SORT関数の登場により、「ソート済みデータの自動維持」が数式だけで可能になりました。これはバイナリ検索を「理論上使える」から「実務で使える」に変えた決定的な進歩です。

SORT + XLOOKUP（バイナリ検索）の組み合わせは、大規模データを扱うExcelシートの計算速度を根本的に改善できる、覚えておくべきアーキテクチャパターンです。

⚠️ よくあるエラーと注意点

データテーブル
エラー	原因	対処法
#SPILL!	スピル先のセルに値が入っている	出力先を空にする
#VALUE!	sort_index が範囲外 / SORTBYの基準配列サイズ不一致	列番号・配列サイズを確認
#CALC!	FILTERの結果が0件（空配列）	FILTERの第3引数（if_empty）を設定： `FILTER(..., ..., "該当なし")`
#REF!	参照先のブックが閉じている	参照先ブックを開く

⚠️ ヘッダーを含めないこと
SORT / SORTBY はすべての行を「データ」として扱います。A1:D100 のようにヘッダー行を含めると、ヘッダーもソート対象になり意図しない結果になります。
必ず A2:D100 のようにデータ行だけを指定してください。

⚠️ 範囲の自動拡張について
=SORT(A2:D100, 3, -1) のように固定範囲を指定すると、101行目以降にデータを追加しても反映されません。
自動拡張させたい場合はExcelテーブル（Ctrl+T）を使うか、TRIMRANGE 関数（対応環境のみ）で動的範囲を作成してください。

旧バージョンでの代替法（LARGE / RANK）

SORT登場以前、数式だけで「トップ10の表を自動更新させる」のは至難の業でした。

⚠️ 旧来の手法：LARGE関数で数値を取り、INDEX+MATCHで紐付け
=LARGE(売上範囲, ROW(A1)) で1位、2位の売上高を取り出し、それをキーにして =INDEX(名前範囲, MATCH(1位の売上高, 売上範囲, 0)) とやって「誰の売上か」を照合・抽出する手法。

この旧来手法には「同率1位（同じ売上高）の人が複数いると、数式が先に見つけた1人目の名前ばかりを重複して取得してしまう」という致命的な欠陥がありました。これを回避するためには「売上データの末尾に +ROW()/100000 のように微小な行番号をわざと足して、無理やり数値を一意にずらす」といったトリッキーな細工（作業列）が必要不可欠でした。

現在のSORT関数を使えば、同率タイの順位も「元データの並び順」を維持して正しく全員分表示してくれます（安定ソート）。

📌 まとめ：SORT / SORTBY 使い分けチャート

Q. ソート基準は配列「内」の列？
　├ YES → 単一キー？ → SORT(配列, 列番号, 順序)
　├ YES → 複数キー？ → SORT(配列, {列1,列2}, {順1,順2}) or SORTBY
　└ NO（配列外 / 計算値 / カスタム順序） → SORTBY 一択

Q. 他の関数と組み合わせる？
　├ 条件抽出 → FILTER + SORT
　├ 重複排除 → UNIQUE + SORT
　├ 上位N件 → FILTER + SORT + TAKE
　├ 別テーブルの値で並べ替え → SORTBY + XLOOKUP
　└ 数式が長い → LET で中間変数を定義して可読性UP

Q. 大規模データの検索が遅い？
　├ 作業シートに SORT でソート済みデータを1回スピル
　└ XLOOKUP / XMATCH の search_mode = 2 でバイナリ検索 → 爆速化

💡 最後に
SORT / SORTBY は「手作業の並び替え」を完全に関数化した、スピル時代を代表する関数です。FILTER・UNIQUE・TAKE・XLOOKUP・LET と組み合わせることで、従来はVBAで何十行も書いていた集計処理がたった1行の数式で完結します。

さらに、SORT で作ったソート済みデータは XLOOKUP / XMATCH のバイナリ検索モードの「燃料」になります。大規模データの検索速度に悩んでいるなら、SORT + バイナリ検索のアーキテクチャを今日から試してみてください。