今回は、ExcelのSORTBY関数を使い、元データを変えずに複数の基準で並べ替えた一覧を作る方法を紹介します。
SORTBY関数とは
SORTBY関数は、指定した配列を別の列や行の値を基準に並べ替えて返す関数です。元データの行順を変更せず、数式を入力した場所へ並べ替え結果を表示できます。
リボンの並べ替え機能は元の表自体の順序を変更します。一方、SORTBY関数は元データを参照して別の一覧を作るため、入力順を残したまま、提出用や確認用の表示順を用意できます。
一つの基準だけでなく、部署、期限、優先度など複数条件を順番に指定できます。並べ替えのルールを数式として残せることが特徴です。
SORTBY関数の基本的な考え方
最初に並べ替えて返したい範囲を指定し、次に基準となる範囲と昇順または降順を指定します。返す範囲と基準範囲は、行数または列数が対応している必要があります。
たとえば、A列からD列までの案件一覧を、D列の期限が早い順に表示するなら、返す範囲へA列からD列、基準範囲へD列を指定します。
結果は数式を入力したセルから複数セルへスピルします。展開先へ別の値、結合セル、ほかのスピル範囲があるとエラーになるため、十分な空き領域を用意します。
昇順と降順を使い分ける
文字列は五十音や文字コードに基づく順序、数値は小さい順または大きい順、日付は古い順または新しい順に並べられます。
期限一覧は古い日付から、売上順位は大きい値からというように、項目の意味へ合わせて指定します。未入力セルや文字列として保存された日付が混在すると、期待した位置へ並ばないことがあります。
並べ替え結果がおかしい場合は、表示形式だけでなく値の種類を確認します。日付に見える文字列、数値に見える文字列、先頭の空白などを整えてから並べ替えます。
複数の基準を指定する
SORTBY関数では、基準範囲と順序の組み合わせを複数追加できます。最初の基準で同じ値になった行を、次の基準で並べ替えます。
たとえば、部署を昇順、部署内では期限を昇順、期限も同じなら優先度を降順というルールを設定できます。引数は、優先順位の高い基準から順に並べます。
結果を見る人が並べ替え規則を理解できるよう、一覧の見出し付近へ「部署順、同一部署内は期限順」などと記載します。基準の優先順位を先に言葉で整理してから数式へすると設定ミスを防げます。
独自の優先順位で並べ替える
「高、中、低」や「未着手、対応中、完了」など、通常の文字順とは異なる業務上の順番で並べたい場合があります。そのまま並べ替えると、五十音順などになり、意図した優先順位になりません。
この場合は、状態を数値へ対応させた補助列を用意し、その列を基準にします。高を1、中を2、低を3とするなど、順序が分かる値を割り当てます。
補助列を元表へ追加したくない場合は、検索関数や条件式で状態を数値へ変換した配列を作り、その結果をSORTBY関数の基準にできます。ただし数式が複雑になるため、共有表では補助列のほうが理解しやすい場合があります。
Excelテーブルと組み合わせる
元データをExcelテーブルにすると、行の追加に応じて参照範囲が広がります。SORTBY関数では、テーブル名と列名を使った構造化参照を指定できます。
元表へ新しい案件を追加すれば、並べ替え結果にも反映されます。固定されたセル範囲を毎回変更する必要がありません。
SORTBY関数の結果自体は、通常、Excelテーブルの外へ置きます。スピル結果は行数が変化するため、テーブル列の中へ配置すると展開できない場合があります。入力表と表示用一覧を別シートへ分ける構成も使いやすい方法です。
FILTER関数と組み合わせる
特定の条件に合う行だけを並べ替えたい場合は、FILTER関数で対象を絞り、その結果をSORTBY関数へ渡します。
たとえば、未完了の案件だけを抽出して期限順に表示する一覧を作れます。抽出対象の配列と並べ替え基準が同じ行へ対応するように数式を組み立てます。
複雑な場合は、LET関数で「対象データ」「期限」「結果」などの名前を付けると読みやすくなります。最初にFILTER関数だけで正しく抽出できることを確認し、その後で並べ替えを追加します。
UNIQUE関数と組み合わせる
重複を除いた担当者名や商品名を並べ替えたい場合は、UNIQUE関数と組み合わせます。UNIQUE関数で固有値を返し、その結果を並べ替えます。
単一列の固有値を単純に昇順へするだけならSORT関数でも対応できます。別の値を基準に並べたい場合や、複数条件を使いたい場合はSORTBY関数が適しています。
関数を選ぶときは、返す範囲自身の列番号で並べるのか、外部の基準配列で並べるのかを考えます。目的が単純なら短い式を選び、後から修正しやすくします。
SORT関数との違い
SORT関数は、返す配列の何列目または何行目を基準にするか指定します。SORTBY関数は、基準となる範囲そのものを指定します。
表示する範囲に基準列が含まれていない場合、SORTBY関数が便利です。たとえば、社員名と部署だけを表示しつつ、非表示の評価点を基準に並べることができます。
列の追加や削除が多い表では、「3列目」のような位置指定より、列名で基準を参照するSORTBY関数のほうが意図を読み取りやすい場合があります。
並べ替え結果へ見出しを付ける
数式の参照範囲へ見出しを含めると、見出しまでデータとして並べ替えられることがあります。通常はデータ行だけをSORTBY関数へ渡し、結果の上へ見出しを別に配置します。
元表と同じ見出しを連動させたい場合は、見出し行を別の数式で参照する方法があります。動的配列をまとめる関数へ対応した環境では、見出しと結果を縦に結合する方法もあります。
利用環境との互換性を優先するなら、見出しは固定文字として置き、データ部分だけを動的にします。
結果が更新されないときの確認
元データを変更しても結果が更新されない場合は、計算方法が手動になっていないか確認します。参照範囲が固定され、新しい行が範囲外になっていることもあります。
スピルエラーなら、展開先の値や結合セルを取り除きます。値のエラーが出る場合は、返す範囲と基準範囲の行数が一致しているか確認します。
同じ基準値を持つ行の順序が重要なら、受付番号や登録日時など、重複しない追加基準を最後に指定します。これにより、更新のたびに同順位の並びが予想外に見える問題を減らせます。
実務で使う際のポイント
- 元データを残し、表示用の一覧を別領域へ作る
- 複数基準は優先順位の高い順に指定する
- 独自順序は補助列や対応表で数値化する
- 追加行へ対応するため元データをテーブル化する
- 共有先のExcelが動的配列関数へ対応しているか確認する
並べ替え一覧を印刷やPDFへ使う場合は、フィルター条件や更新時刻も記載すると、何を基準に作られた一覧か判断しやすくなります。
まとめ
ExcelのSORTBY関数は、元データを変更せず、指定した範囲を一つまたは複数の基準で並べ替えて表示します。部署、期限、優先度などを順番に指定すれば、業務ルールに沿った一覧を数式で作れます。
Excelテーブル、FILTER関数、UNIQUE関数と組み合わせると、データ追加や条件変更へ追随する一覧になります。並べ替え基準の優先順位と値の種類を明確にすることが、期待どおりの結果を得るポイントです。