Excelで売上や点数の上位3件、下位5件をすぐ出したいのに、MAXやMINでは1件しか取れず困ったことはありませんか。LARGE関数とSMALL関数を使えば、順位を指定して必要な値だけを抽出できます。この記事では基本構文から複数抽出、条件付き集計、エラー対策、実務でそのまま使える数式テンプレートまで、古いExcel向けとMicrosoft 365向けの両方をわかりやすく整理します。
LARGE関数・SMALL関数とは？構文と基本をサクッと解説
結論から言うと、LARGE関数は上から何番目か、SMALL関数は下から何番目かを返す関数です。
MAXやMINが最大値と最小値の1件だけを返すのに対し、LARGEとSMALLは2位、3位、5位のように順位指定できるため、ランキング集計で非常に使いやすいのが強みです。
売上トップ3を抜き出す不良率の低い順に5件出す点数の下位者を把握する
LARGE関数の構文と引数【コピペOK】
=LARGE(範囲,順位) が基本形です。
たとえば =LARGE(B2:B11,1) なら最大値、=LARGE(B2:B11,3) なら3番目に大きい値を返します。
第1引数は数値の範囲、第2引数は上から何位かを示す数値です。Microsoft公式でも、LARGEはデータセットの k 番目に大きい値を返すと案内されています。参考: Microsoft Support
SMALL関数の構文と引数【コピペOK】
=SMALL(範囲,順位) が基本形です。
=SMALL(B2:B11,1) なら最小値、=SMALL(B2:B11,5) なら5番目に小さい値を返します。
第2引数の順位は下から数える点だけがLARGEとの違いです。Microsoft公式でも、SMALLはデータセットの k 番目に小さい値を返すと説明されています。参考: Microsoft Support
LARGE・SMALL関数の動作イメージを図解で理解
動き方はとても単純で、まず対象範囲の数値を大小順に見たうえで、指定順位の値を返します。
元データが 91、75、88、60 の4件あるLARGEは 91、88、75、60 の順で見て2位なら88を返すSMALLは 60、75、88、91 の順で見て2位なら75を返す
つまり並べ替え作業を手でしなくても、式だけで必要順位の値を取れるのが最大のメリットです。
LARGE関数・SMALL関数の基本的な使い方【実例付き】
実務では、順位ごとの値を即座に抜き出したい場面で使います。
ここでは B2:B11 に数値データが入っている前提で、トップ3とワースト5を取り出す基本形を確認します。
最初は 1位や最下位だけを返す式から覚え、次に複数順位へ広げると理解しやすいです。
LARGE関数で売上トップ3を抽出する
売上トップ3を出すなら、まず 1位から3位までの式を別セルに並べるのが最短です。
1位: =LARGE(B2:B11,1)2位: =LARGE(B2:B11,2)3位: =LARGE(B2:B11,3)
たとえば売上が 120、95、210、180 なら、返り値は 210、180、120 になります。定例レポートで上位実績だけ確認したいときに、そのまま貼って使える基本テンプレートです。
SMALL関数でワースト5を抽出する
下位データを確認したいときは、SMALL関数を同じ考え方で使います。
最下位: =SMALL(B2:B11,1)下位2位: =SMALL(B2:B11,2)下位5位: =SMALL(B2:B11,5)
たとえば試験点数の低い受講者を5人把握したい、在庫回転率の低い商品を5件見たい、という場面で役立ちます。ワースト抽出は改善対象の発見に直結するため、実務では上位抽出と同じくらい重要です。
MAX/MIN関数との違いと使い分け
使い分けの基準は、1件だけ知りたいか、順位指定で知りたいかです。
目的使う関数返すもの最大値だけ知りたいMAX最大の1件最小値だけ知りたいMIN最小の1件上位3位を知りたいLARGE指定順位の値下位5位を知りたいSMALL指定順位の値
Microsoft公式でも、MAXは最大値、MINは最小値を返す関数です。最大値だけならMAX、ランキング型の抽出ならLARGEと覚えると迷いません。参考: MAX ・ MIN
LARGE・SMALL関数で複数の値を一括抽出するテクニック
トップ10のように複数順位を並べたいなら、順位番号を自動生成するのがコツです。
従来のExcelではROW関数、新しい環境ではSEQUENCE関数を使うと、同じ式を何度も打ち直さずに済みます。
【従来版】ROW関数と組み合わせて連番を自動生成
旧来のやり方では、ROW関数で 1、2、3 の連番を作り、それを順位引数に渡します。
たとえば D2 に =LARGE($B2:B$11,ROW(A1)) を入力し、下へコピーすると、ROW(A1) が 1、ROW(A2) が 2 となり、トップ1から順に抽出できます。
SMALLでも同じ発想で =SMALL($B2:B$11,ROW(A1)) とすれば、下位順位を一覧化できます。古いExcelでも使えるため、互換性重視の現場では今でも定番です。
【Microsoft 365】SEQUENCE関数でスマートに一発抽出
Microsoft 365なら、SEQUENCE関数で複数順位を一気に生成できるため、式は1本で済みます。
=LARGE(B2:B11,SEQUENCE(3)) と入力すれば、1位から3位までが縦にスピル表示されます。下位3件なら =SMALL(B2:B11,SEQUENCE(3)) です。
Microsoft公式でも、SEQUENCEは連番配列を返し、結果はスピルすると説明されています。大量のランキング抽出を1セルで済ませたいなら最優先で覚えたい組み合わせです。参考: Microsoft Support
抽出した値に対応する項目名も取得する（INDEX+MATCH連携）
値だけではなく商品名や担当者名まで表示したいなら、INDEX関数とMATCH関数を組み合わせます。
商品名が A2:A11、売上が B2:B11 のとき、1位の商品名は =INDEX($A2:A11,MATCH(LARGE(B2:B11,1),B2:B$11,0)) で取得できます。
Microsoft公式でも、MATCHで位置を探し、その結果をINDEXの引数に渡す流れが紹介されています。なお同額の重複があると先頭一致が返るため、重複対策は後述の方法とセットで考えるのが安全です。参考: Microsoft Support
LARGE・SMALL関数で条件付き抽出を行う方法
特定部署や特定カテゴリの中だけで順位を出したい場合は、条件に合う値だけを先に絞り込むのが基本です。
従来版ではIF関数、Microsoft 365ではFILTER関数を使うと整理しやすくなります。
特定カテゴリ内のトップ3を抽出する（IF関数連携）
カテゴリ別の上位抽出は、IFで条件一致した値だけを配列化し、その中からLARGEで順位を取る方法が定番です。
カテゴリが A2:A11、売上が B2:B11、対象カテゴリが E2 の場合、1位から順に出す式は =LARGE(IF($A2:A11=E2,B2:B$11),ROW(A1)) です。
旧バージョンでは Ctrl+Shift+Enter が必要な場合がありますが、部署別トップ3や店舗別上位者の抽出では非常に実用的です。
0や空白を除外して最小値・上位値を求める
0や空白が混ざる表では、そのままSMALLを使うと不要な 0 が最小値として拾われるため、除外条件を先に入れる必要があります。
Microsoft 365なら =SMALL(FILTER(B2:B11,(B2:B11<>0)*(LEN(B2:B11)>0)),1) とすると、0 と空白を除外した最小値を返せます。
同じ考え方で LARGE に置き換えれば、未入力やダミーの 0 を含まない上位集計が可能です。売上未計上行や欠席者の 0 点を外したい場面で特に有効です。
【Microsoft 365】FILTER関数と組み合わせる方法
Microsoft 365では、FILTERで条件一致データだけを抜き出し、その結果にLARGEやSMALLを直接かけるのが最もスマートです。
カテゴリが E2 のトップ3なら =LARGE(FILTER(B2:B11,A2:A11=E2),SEQUENCE(3)) と書けます。
Microsoft公式でも、FILTERは条件で配列を絞り込み、結果をスピル表示すると説明されています。複数条件も掛け算で追加できるため、地域かつ商品別のランキングにも発展しやすい方法です。参考: Microsoft Support
LARGE・SMALL関数のエラー原因と対処法
LARGEとSMALLは便利ですが、順位や参照範囲に問題があるとエラーになりやすい関数です。
よく出るのは #NUM! と #VALUE! で、原因を知っておけばほとんどは数分で直せます。
#NUM!エラーの原因と解決策
#NUM! が出る主因は、順位 k が 0 以下、またはデータ件数を超えているケースです。
たとえばデータが8件しかないのに =LARGE(B2:B9,10) とすると、10位は存在しないため #NUM! になります。
Microsoft公式でも、配列が空、または k が不正なときに #NUM! になると明記されています。COUNTAで件数を確認し、順位が範囲内かを先にチェックすると防ぎやすいです。参考: LARGE ・ SMALL
#VALUE!エラーの原因と解決策
#VALUE! は、数式の書き方に問題がある、または参照セルに文字列や不要スペースが混ざっているときに起きやすいエラーです。
特に順位セルを手入力している場合、見た目は 3 でも文字列として保存されていると計算が崩れることがあります。
対策は、対象セルを数値化し、数式タブの数式の検証で途中結果を追うことです。Microsoft公式でも、#VALUE! は式の記述や参照セルの不整合が原因になりやすいと案内されています。参考: Microsoft Support
重複値がある場合の挙動と対策
LARGEとSMALLは値を返す関数なので、同じ数値が重複していれば同じ値を複数回返します。
たとえば売上が 100、95、95、80 なら、2位も3位も 95 になります。これは誤動作ではなく、同順位をそのまま返している状態です。
重複を避けたいなら、Microsoft 365では UNIQUE関数で重複排除してからLARGEを使う方法が有効です。項目名取得でMATCHを併用すると先頭行だけ返るため、重複が多い表では補助列を作るほうが安全です。
LARGE・SMALL関数と他の関数を比較【早見表付き】
似た関数が多いので、役割の違いを整理しておくと式選びが速くなります。
関数主な用途返るものLARGE上位順位の抽出指定順位の値SMALL下位順位の抽出指定順位の値RANK順位付け順位SORT並べ替え並び替え済み配列SORTBY別列基準の並べ替え並び替え済み配列
RANK関数との違い（順位を返す vs 値を返す）
最大の違いは、LARGEとSMALLが値を返し、RANKが順位そのものを返す点です。
たとえば 88 点が全体で何位かを知りたいならRANK、3位の点数そのものを知りたいならLARGEを使います。
Microsoft公式でも、RANKは数値の相対的な順位を返す関数と説明されています。レポートで『トップ3の数値』が必要なのか、『各人の順位』が必要なのかで使い分けましょう。参考: Microsoft Support
SORT/SORTBY関数との使い分け
上位値を数件だけ欲しいならLARGEやSMALL、表全体を順番に並べ替えて見せたいならSORT系が向いています。
たとえばトップ3の値だけ必要ならLARGEが軽く、商品名と売上の表全体を売上順に見せたいならSORTBYのほうが自然です。
Microsoft公式では、SORTBYは対応する別配列の値を基準に範囲を並べ替える関数です。抽出か、表示用の並べ替えかを基準に選ぶと迷いません。参考: Microsoft Support
【判断フロー】どの関数を使うべきか一目でわかる
選び方に迷ったら、欲しい結果が 値 なのか 順位 なのか 一覧 なのかで判断するとすぐ決まります。
最大値や最小値が1件だけ欲しい → MAXまたはMIN3位や5位の値が欲しい → LARGEまたはSMALL各データの順位が欲しい → RANK系表全体を並べ替えて見せたい → SORTまたはSORTBY
実務では、集計セルにはLARGE、一覧表示にはSORTBYという使い分けにすると、シートの役割が分かれ管理しやすくなります。
実務で使えるLARGE・SMALL関数の活用例3選
ここからは、現場でそのまま応用しやすい代表例を3つ紹介します。
どれも上位抽出だけでなく、下位抽出や条件付き抽出へすぐ展開できるパターンです。
活用例1：売上ランキングTOP10を自動生成
営業レポートでは、トップ10商品やトップ10担当者を毎月自動で出す用途が定番です。
Microsoft 365なら =LARGE(B2:B101,SEQUENCE(10)) で上位10件の値を一発表示でき、名前列はINDEXとMATCHを組み合わせれば対応できます。
月次会議資料で毎回並べ替え直す手間がなくなり、更新は元データ差し替えだけで済みます。件数が多いほど効果が大きく、50人規模の営業表でも作業時間を数分単位で削減しやすいです。
活用例2：外れ値・異常値の検出（上位・下位5%の特定）
品質管理やアクセス解析では、極端に高い値や低い値を見つけるためにLARGEとSMALLが使えます。
たとえば100件のデータなら、上位5パーセントは 5件なので =LARGE(B2:B101,SEQUENCE(5))、下位5パーセントは =SMALL(B2:B101,SEQUENCE(5)) で候補を抽出できます。
平均値だけでは見えない異常注文、極端な在庫滞留、急落した成績を早く把握できるため、チェックシートとの相性が良い活用法です。
活用例3：試験成績の上位者・要フォロー者リスト作成
教育現場や社内研修では、上位者表彰と要フォロー者抽出を同じデータから作れるのが便利です。
上位3名は LARGE、下位5名は SMALL を使い、氏名表示は INDEX+MATCH で連携すれば、表彰対象と補講対象の両方を自動更新できます。
点数が更新されるたびに結果も即時反映されるため、手作業で並べ替えるよりミスが減ります。重複点が多い試験では、同点の扱いをあらかじめ決めておくと運用が安定します。
まとめ：LARGE・SMALL関数を使いこなすポイント
LARGEとSMALLは、順位付きの値抽出を最短で実現できる実務向け関数です。
1件だけならMAXやMIN、順位指定ならLARGEやSMALLを使う複数件抽出は従来版ならROW、Microsoft 365ならSEQUENCEが効率的条件付き抽出はIFまたはFILTERを組み合わせる#NUM! は順位超過、#VALUE! は参照や型の不整合を疑う値だけでなく名前も必要ならINDEX+MATCHまでセットで覚える
まずは自分の表で =LARGE(範囲,1) と =SMALL(範囲,1) から試し、次にトップ3や条件付き抽出へ広げると、日々の集計作業が一気に楽になります。
コメント