これまでの回では、LARGE・INDEX・MATCHを組み合わせてランキング表を作ってきました。機能としては十分完成していますが、「式が複雑で覚えにくい」と感じた方もいるのではないでしょうか。そこで今回はSORT・FILTER関数を紹介します。同じことが、ずっとシンプルな式で実現できます。
ただし1つ注意点があります。SORT・FILTERはGoogleスプレッドシート専用の比較的新しい関数で、Excelでは動作しません（Excel 365では一部使えますが挙動が異なります）。この連載はGoogleスプレッドシートを前提にしているので問題ありませんが、頭の片隅に置いておいてください。
SORT関数とFILTER関数、それぞれ何をする関数か
| 関数 | 一言でいうと | ランキングでの役割 |
|---|---|---|
| SORT | 範囲を指定した列で並べ替えて出力する | 安打・打点・盗塁などを多い順に並べてランキング表を作る |
| FILTER | 条件を満たす行だけを抽出して出力する | 「打席数18以上の選手だけ」など条件付きで絞り込む |
この2つを組み合わせれば、「規定打席以上の選手だけを打率降順に並べる」という打率ランキングを、驚くほど短い式で作れます。
SORT関数の基本構造
=SORT(範囲, 並べ替え列, 昇順か降順か)
| 引数 | 意味 | 今回の設定 |
|---|---|---|
| 範囲 | 並べ替えたいデータ全体 | 年間(野手)シートの選手名〜盗塁列まで |
| 並べ替え列 | 「何列目の値で並べるか」を数値で指定 | 範囲の中で安打が何列目かを数える |
| 昇順か降順か | FALSE で降順（大きい順）、TRUE で昇順 | ランキングなのでFALSE（降順）を使う |
年間(野手)シートのB2:J25（選手名〜盗塁）を、安打（G列＝範囲の中で6列目）の降順で並べる式は次のとおりです。
=SORT('年間(野手)'!B2:J25, 6, FALSE)
B2:J25：選手名（B列）から盗塁（J列）まで全列を範囲に含める6：範囲の左から数えて6列目＝G列（安打）で並べ替えるFALSE：降順（安打が多い順）で出力する
この1式を1つのセルに入力するだけで、安打が多い順に並んだ表全体が自動で展開されます。Googleスプレッドシートでは、この動きを「スピル（溢れ出し）」と呼びます。
SORT関数の結果をサンプルデータで確認する
例えば、年間(野手)シートにこんなサンプルデータがあったとして、SORT関数を適用すると上位は次のようになります。
| 表示順 | 選手名 | 安打 | 備考 |
|---|---|---|---|
| 1 | SUZUKI | 14 | 単独1位 |
| 2 | NAKATA | 10 | 単独2位 |
| 3 | TANAKA | 7 | 同率3名（TANAKA・YAMADA・SAKAI）を元の行順に表示 |
| 4 | YAMADA | 7 | 同上 |
| 5 | SAKAI | 7 | 同上 |
LARGE・INDEX・MATCHの方式では、同率3名が「TANAKA・TANAKA・TANAKA」と同じ名前で繰り返し表示されてしまう問題がありました。SORTを使うと同率の選手が元のシートの行順にそのまま並ぶため、自然に別々の名前が表示されます。前回までの補助列も不要で、同率問題が自動的に解消されるのが大きなメリットです。
FILTER関数の基本構造
=FILTER(範囲, 条件)
| 引数 | 意味 | 今回の設定 |
|---|---|---|
| 範囲 | 抽出したいデータ全体 | 年間(野手)シートの選手名〜盗塁列まで |
| 条件 | 「どの行を残すか」を判定する式 | 打席数列 >= 規定打席 |
条件を満たす行だけが結果として出力されます。LARGE・IF配列数式の代わりに、FILTERで「対象外の選手を除く」ことができます。
盗塁が1個以上の選手だけを抽出する式は、次のようになります。
=FILTER('年間(野手)'!B2:J25, '年間(野手)'!J2:J25>0)
J列（盗塁）が0より大きい行だけが抽出されます。盗塁0の選手（試合には出ていても盗塁がない選手）は表示されません。
SORT＋FILTERの組み合わせ：打率ランキングへの応用
2つを組み合わせると、規定打席条件付きの打率ランキングが1つの式で書けます。第70回では配列数式（Ctrl＋Shift＋Enter）が必要でしたが、SORT＋FILTERなら通常のEnterで確定できます。
=SORT(
FILTER('年間(野手)'!B2:J25, '年間(野手)'!E2:E25>=$H$1),
3, FALSE
)
FILTER(…, E2:E25>=$H$1)：打席数（E列）が規定打席（H1セル）以上の選手だけを抽出SORT(…, 3, FALSE)：そのFILTER結果を3列目（D列＝打率）の降順で並べ替える
FILTERで「規定打席クリア」、SORTで「打率の高い順」——この2つの役割を、2つの関数で分担しています。配列数式ではないので、通常のEnterで確定するだけです。
例えば、こんなサンプルデータがあったとします。規定打席18以上、かつ打率降順で並べると結果は次のようになります。
| 表示順 | 選手名 | 打率 | 打席数 |
|---|---|---|---|
| 1 | SUZUKI | .636 | 30 |
| 2 | TANAKA | .368 | 24 |
| 3 | SAKAI | .368 | 23 |
| 4 | NAKATA | .357 | 28 |
| 5 | YAMADA | .318 | 27 |
規定打席未達のY.KATAMOTOさん（打率.400・5打席）は除外され、同率2位のTANAKAとSAKAIも別々に正しく表示されています。第70回の配列数式と第72回の補助列方式でようやく実現できていたことが、この1式だけで完結してしまうわけです。
スピル（溢れ出し）の仕組みを理解する
SORT・FILTERを使うときに、もう1つ知っておきたいのが「スピル」です。
SORT・FILTER関数は、結果が複数行・複数列になります。式を入力した1つのセルから、結果に必要な分だけ自動で隣接するセルに溢れ出す——これがスピルです。
| 注意点 | 対処法 |
|---|---|
| スピル先のセルに別のデータが入っていると #SPILL! エラーになる | SORT・FILTERを入力するセルの右・下を空けておく |
| スピルで展開された範囲は直接編集できない | 編集したいときは元の式（左上セル）を修正する |
| 選手が増減すると展開行数も自動で変わる | これはメリット——データが増えても自動で対応できる |
LARGE・INDEX・MATCHとSORT・FILTERを比較する
2つのアプローチは「どちらが正解」というものではなく、用途によって使い分けるものです。
| 観点 | LARGE・INDEX・MATCH | SORT・FILTER |
|---|---|---|
| 式の複雑さ | やや複雑（3関数の組み合わせ） | シンプル（1〜2関数） |
| 同率の扱い | 補助列が必要 | 自動で別々に表示 |
| 表示行数の制御 | 「上位5位まで」など行数を固定しやすい | 全員分が出力される（行数制御が必要なら別途対応） |
| Excelとの互換性 | Excelでも動く | Googleスプレッドシート専用（Excel非対応） |
| 配列数式（Ctrl+Shift+Enter） | 規定打席条件付きは必要 | 不要（通常のEnterで確定） |
| スピルの管理 | 不要（セルごとに式を入れる） | スピル先を空けておく必要がある |
どちらを使えばいいか：シンプルな使い分けの目安
- 「上位5位まで」という行数制限が不要で、全選手を並べ替えて表示したいとき
- 同率の扱いを自動化したい（補助列を管理したくない）とき
- 式をできるだけシンプルに保ちたいとき
- 「打率TOP5だけ表示」など、表示行数を厳密に固定したいとき
- ランキングシートの特定のセル範囲に値を収めたいレイアウト設計のとき
- ExcelとGoogleスプレッドシートの両方で使える式にしたいとき
SORT・FILTERは全行が出力されます。「上位5件だけ」に絞りたい場合は、さらに外側をARRAY_CONSTRAINで囲みます。
=ARRAY_CONSTRAIN(SORT(FILTER(…), 3, FALSE), 5, 9)
ARRAY_CONSTRAIN(配列, 行数, 列数)で「上5行・全9列」に切り取れます。ただし式がやや複雑になるため、「上位N件だけ」にこだわるなら、かえってLARGE・INDEX・MATCHのほうがすっきりまとまる場合もあります。
- SORT関数は
=SORT(範囲, 何列目, FALSE)で指定列の降順に並べ替える——1式で全選手のランキング表が自動展開される - FILTER関数は
=FILTER(範囲, 条件)で条件を満たす行だけを抽出する——規定打席未達の選手を除外するのに使える - SORT＋FILTERの組み合わせで、第70回の配列数式と第72回の補助列が不要になり、打率ランキングが通常のEnterだけで完成する
- 同率の選手が自動で別々に表示されるのが、SORT・FILTERの大きなメリット
- 「上位N件に固定」「Excel互換」が必要ならLARGE・INDEX・MATCHのほうが向いている——どちらが正解ではなく、用途で使い分けるのがポイント
- 次回はランキングシートの見た目を整える回——背景色・フォント・罫線でランキング表をぐっと見やすくします
次回予告
次回は「ランキングシートの見た目を整える」回です。ここまででランキングシートの計算式はすべて完成しました。最後の仕上げとして、背景色・フォント・罫線を設定し、チームメンバーが見やすいランキング表に整えていきます。条件付き書式を使って1位を自動的に目立たせる方法も紹介する予定です。
▶︎ 次回：【第74回】ランキングシートの見た目を整える
コメント