Excelで表を自動並べ替えしたい場合は、SORT関数を使うと便利です。
SORT関数を使うと、元データを直接並び替えずに、別の場所へ並び替え済みの一覧を表示できます。
Excelで作業していると、売上順、日付順、名前順などで表を並び替えたい場面はよくある。
ただ、元データをそのまま並び替えるのは少し怖い。
「あとで元の順番に戻せるだろうか」
「他の人が見る元データまで変わってしまわないか」
「並び替えたあとに、集計や確認がずれないか」
こういう不安があるときに使いやすいのが、ExcelのSORT関数だ。
SORT関数は、表そのものを直接並び替える関数ではない。
元データはそのまま残し、並び替えた結果だけを別の場所に表示する関数として考えると分かりやすい。
Excelで表を自動並べ替えするならSORT関数
通常の並び替えは、選択した表そのものの行順を変える。
一方で、SORT関数は数式を入力した場所に、並び替え後の一覧を表示する。
元データの並び順は変わらない。
ここが一番大きな違いだ。
たとえば、売上一覧の元データを残したまま、別シートや右側の空きスペースに「売上が高い順の一覧」を表示したい。
こういう場面では、手動の並び替えよりSORT関数の方が扱いやすい。
SORT関数は、Excel for Microsoft 365 や Excel 2021 以降などで使える動的配列関数です。
古いExcelでは使えない場合があるため、#NAME? が出るときはバージョンも確認しておきたいところです。
SORT関数は元データを変えずに並び替え結果を表示できる
SORT関数は、指定した範囲を並び替えて、その結果をセルに表示する関数だ。
イメージとしては、次のような使い方になる。
=SORT(A2:D10)
この式を入力すると、A2:D10 の範囲を並び替えた結果が、数式を入力したセルから下方向・右方向に広がって表示される。
この広がって表示される動きは「スピル」と呼ばれる。
数式を1つのセルに入力すると、結果が隣接セルへ自動的に展開される仕組みだ。
これはよく困るポイント。
SORT関数は、元の表をその場で並び替えるものではない。
あくまで「並び替え済みのコピーを表示する」感覚で使う。
そのため、実務では次のような場面に向いている。
- 元データの順番を変えたくない
- 確認用の一覧だけ並び替えたい
- 売上順、日付順、名前順などの表示を別で作りたい
- 元データ更新時に、並び替え後の一覧も自動反映させたい
手作業で並び替えていると、どこかのタイミングで戻し忘れたり、範囲選択を間違えたりする。
SORT関数なら、元データを残したまま結果だけを作れるので、その不安を減らせる。
SORT関数の基本構文
SORT関数の基本構文は次の形だ。
=SORT(配列, [並べ替えインデックス], [並べ替え順序], [列で並べ替え])
それぞれの意味は次のとおり。
| 引数 | 意味 |
|---|---|
| 配列 | 並び替えたい範囲 |
| 並べ替えインデックス | 何列目、または何行目を基準に並び替えるか |
| 並べ替え順序 | 昇順なら 1、降順なら -1 |
| 列で並べ替え | 通常は省略。横方向に並び替える場合に使う |
よく使うのは、最初の3つだ。
=SORT(A2:D10, 1, 1)
これは、A2:D10 の範囲を、1列目を基準に昇順で並び替える式になる。
=SORT(A2:D10, 3, -1)
これは、3列目を基準に降順で並び替える式だ。
1列目を昇順で並び替える基本例
まずは一番シンプルな例から。
次のような表があるとする。
| 氏名 | 部署 | 売上 |
|---|---|---|
| 佐藤 | 営業 | 120000 |
| 鈴木 | 総務 | 80000 |
| 田中 | 営業 | 150000 |
| 山田 | 開発 | 100000 |
この表を、1列目の「氏名」で昇順に並び替える場合は、次のように書く。
=SORT(A2:C5, 1, 1)
意味はこうだ。
A2:C5を並び替える- 1列目を基準にする
- 昇順で並び替える
昇順なので、Excelの並び替えルールに従って氏名が並ぶ。
ふりがなや表記ゆれがある場合は、思った順番にならないこともある。
ここで大事なのは、元の A2:C5 の表は変わらないこと。
SORT関数を入力したセルの位置に、並び替え後の表が表示される。
手動の並び替えに慣れていると、「元データが動かない」のが少し不思議に見えるかもしれない。
でも、実務ではこれがかなり助かる。
元データは入力順のまま残し、確認用の一覧だけ見やすく並び替えられるからだ。
降順で並び替えるには「-1」を指定する
降順にしたい場合は、3つ目の引数に -1 を指定する。
たとえば、売上が高い順に並び替えるなら、次のように書く。
=SORT(A2:C5, 3, -1)
この式では、3列目の「売上」を基準に、数値が大きい順で並び替える。
| 引数 | 指定内容 |
|---|---|
| 配列 | A2:C5 |
| 並べ替えインデックス | 3 |
| 並べ替え順序 | -1 |
昇順と降順は、次のように覚えるとよい。
| 指定値 | 並び順 |
|---|---|
1 | 昇順 |
-1 | 降順 |
数値なら、小さい順が昇順、大きい順が降順。
日付なら、古い順が昇順、新しい順が降順。
文字列なら、Excelの並び替えルールに沿って並ぶ。
売上ランキングや新しい日付順の一覧を作る場合は、降順の -1 をよく使う。
SORT関数で複数列を基準に並び替える方法
実務では、1つの列だけで並び替えるより、複数の条件で並び替えたい場面が多い。
たとえば、次のような並び替えだ。
- 部署順に並べ、その中で売上の高い順にする
- 日付順に並べ、同じ日付の中では名前順にする
- 担当者順に並べ、同じ担当者の中では金額順にする
SORT関数では、並び替えインデックスと並び替え順序を配列で指定すると、複数列を基準に並び替えられる。
たとえば、2列目の部署を昇順、3列目の売上を降順にしたい場合は、次のように書く。
=SORT(A2:C10, {2,3}, {1,-1})
意味はこうだ。
| 指定 | 内容 |
|---|---|
{2,3} | まず2列目、次に3列目を基準にする |
{1,-1} | 2列目は昇順、3列目は降順にする |
つまり、部署ごとに並べたうえで、同じ部署内では売上が高い順になる。
なお、環境によっては配列定数の区切り文字が異なる場合がある。
式がそのまま通らない場合は、Excelの区切り設定も確認したい。
ここは少しややこしい。
複数条件の並び替えはSORT関数でも書けるが、式を見たときに分かりづらくなることがある。
特に、列の追加や削除が多い表では、2 や 3 という列番号だけでは、あとから見たときに何を基準にしているのか分かりにくい。
その場合は、後述するSORTBY関数を使った方が読みやすい。
最初は、単純な並び替えならSORT関数。
基準列を明示したいならSORTBY関数。
このくらいの分け方で十分だ。
元データを追加したときに自動反映させるには
SORT関数は、参照している元データが変わると、並び替え結果も更新される。
つまり、元データの値を変更すれば、並び替え済みの一覧も自動で変わる。
ただし、注意点がある。
次のように通常の範囲で指定している場合、
=SORT(A2:C10, 3, -1)
参照範囲は A2:C10 までだ。
その下の A11:C11 に新しいデータを追加しても、数式の範囲に含まれていなければ、並び替え結果には出てこない。
ここで「自動並べ替えされない」と感じることがある。
SORT関数自体は動いている。
ただ、参照範囲に新しい行が含まれていないだけ、というパターンだ。
実務では、元データをテーブル化しておくと扱いやすい。
Excelのテーブルにしておけば、行を追加したときにテーブル範囲が広がる。
そのテーブルをSORT関数で参照すれば、追加行も並び替え結果に入りやすくなる。
例として、テーブル名を 売上一覧 にしている場合は、次のように書ける。
=SORT(売上一覧, 3, -1)
または、必要な列だけを指定するなら、構造化参照を使う方法もある。
=SORT(売上一覧[[氏名]:[売上]], 3, -1)
ここは実務でかなり大事だ。
「SORT関数を入れたのに、新しいデータが反映されない」ときは、関数より先に参照範囲を確認した方がいい。
範囲指定が固定になっていると、追加行は勝手には含まれない。
SORT関数がうまく動かない原因
SORT関数がうまく動かないときは、関数の書き方だけでなく、表示先や元データも確認する。
よくある原因は次の5つだ。
スピル範囲に値が入っている
SORT関数は、結果を複数セルに広げて表示する。
その表示先に既存の値があると、結果を展開できない。
この場合、#SPILL! エラーが出ることがある。
対処はシンプルだ。
SORT関数の結果が表示される範囲を空ける。
または、数式を別の空いている場所に移動する。
Excelのバージョンが古い
SORT関数は、すべてのExcelで使えるわけではない。
Excel for Microsoft 365 や Excel 2021 以降などでは利用できるが、古い買い切り版のExcelでは使えない場合がある。
セルに #NAME? が出る場合は、関数名が認識されていない可能性がある。
その場合は、Excelのバージョンを確認した方が早い。
参照範囲がずれている
SORT関数では、並び替え対象の範囲と、並び替えインデックスの指定がずれていると、意図しない結果になる。
たとえば、A2:C10 を指定しているのに、4列目を基準にしようとしている場合はおかしい。
=SORT(A2:C10, 4, -1)
A2:C10 は3列しかない。
この状態で4列目を指定しても、基準列が存在しない。
式を見るときは、次の順番で確認するとよい。
- 並び替え対象の範囲はどこか
- その範囲の何列目を基準にしているか
- 昇順・降順の指定は合っているか
いきなり式全体を見ようとすると、意外と見落とす。
結合セルがある
並び替え対象の範囲に結合セルがあると、意図した形で扱いにくくなる。
SORT関数に限らず、Excelで表を扱うとき、結合セルはトラブルの元になりやすい。
見た目は整っていても、データとしては扱いづらい。
関数で並び替えたい表では、できるだけ結合セルを避けた方がいい。
見た目を整える場合は、結合ではなく、中央揃えや表示形式で対応できないかを考える。
参照元にエラーがある
元データに #N/A や #VALUE! などのエラーがあると、SORT関数の結果にも影響する。
SORT関数が悪いのではなく、参照元の値にエラーが混ざっているケースだ。
まずは元データ側にエラーがないか確認する。
FILTER関数やIFERROR関数と組み合わせて、エラー行を除外する方法もあるが、最初は原因を見つける方が大事だ。
SORT関数とSORTBY関数の違い
SORT関数と似た関数に、SORTBY関数がある。
どちらも並び替えに使う関数だが、使いどころが少し違う。
ざっくり分けると、次のようになる。
| 関数 | 向いている場面 |
|---|---|
| SORT関数 | 表の中の何列目で並び替えるかを指定したい |
| SORTBY関数 | 並び替え基準の列を範囲として明示したい |
SORT関数は、次のように「何列目か」で指定する。
=SORT(A2:C10, 3, -1)
これは、A2:C10 の3列目を基準に降順で並び替える式だ。
一方、SORTBY関数は、並び替え基準の範囲を直接指定する。
=SORTBY(A2:C10, C2:C10, -1)
これは、A2:C10 を、C2:C10 の値を基準に降順で並び替える式になる。
どちらを使うか迷ったら、最初はこう考えるとよい。
- 単純に「この表の3列目で並び替えたい」ならSORT関数
- 「この列を基準に並び替える」と明示したいならSORTBY関数
- 列の追加・削除が多い表ならSORTBY関数の方が安全
- 読みやすさを重視するならSORTBY関数も候補にする
SORT関数は短く書ける。
SORTBY関数は、基準が見えやすい。
どちらが上位というより、表の作り方や保守のしやすさで選ぶのがよい。
SORT関数を使わない方がいい場面
SORT関数は便利だが、すべての並び替えを関数化する必要はない。
一度だけ並び替えればよい表や、元データの順番を変えても問題ない資料なら、通常の並び替えで十分なこともある。
逆に、SORT関数が向いているのは次のような場面だ。
- 元データを壊したくない
- 更新されるデータを自動並び替えしたい
- 確認用・集計用の一覧を別で作りたい
- 手動並び替えのミスを減らしたい
実務では、元データを直接触るほどリスクが増える。
SORT関数は、そのリスクを減らしながら並び替え結果だけを見たいときに使いやすい。
まとめ：元データを壊さずに並び替えたいときに便利
ExcelのSORT関数は、表を自動並べ替えしたいときに便利な関数だ。
大事なのは、SORT関数が「元データを直接並び替える関数」ではなく、「並び替えた結果を別の場所に表示する関数」だということ。
この違いを理解しておくと、使いどころが見えてくる。
SORT関数でよく使う形は次のとおり。
=SORT(配列, 並べ替えインデックス, 並べ替え順序)
昇順なら 1、降順なら -1。
複数列で並び替える場合は、並び替えインデックスと並び替え順序を配列で指定する。
=SORT(A2:C10, {2,3}, {1,-1})
元データを追加したときに自動反映させたいなら、参照範囲にも注意したい。
通常の範囲指定では追加行が漏れることがあるため、継続して使う表ならテーブル化しておくと管理しやすい。
SORT関数がうまく動かないときは、まず次を確認する。
- スピル範囲に値が入っていないか
- Excelのバージョンが対応しているか
- 参照範囲と列番号が合っているか
- 結合セルが混ざっていないか
- 元データにエラーがないか
手動の並び替えは手軽だが、元データの順番を変えてしまう。
元データを残したまま確認用の並び替え一覧を作りたいなら、SORT関数はかなり使いやすい選択肢になる。
Excelで元データを壊さずに抽出・並び替え・集計したい場合は、FILTER関数やUNIQUE関数と組み合わせて考えると使い道が広がる。
関連するExcel関数の記事も、あわせて整理していきたい。
関連記事
Excelで元データを壊さずに一覧を作りたい場合は、FILTER関数やUNIQUE関数も一緒に覚えておくと便利です。
・FILTER関数で条件に合うデータだけを抽出する方法【新関数シリーズ第1弾】
コメント