LARGE・SMALL関数で上位・下位ランキングを抽出する方法｜#NUM!エラーの原因と対処法

「売上上位3名を表にまとめたい」「経費の中で金額が大きい順に3件だけチェックしたい」――そんなときに便利なLARGE・SMALL関数の使い方と、k番目の指定でつまずきやすいポイントを整理します。

月次の営業成績表や経費精算の一覧から「上位◯件」「下位◯件」だけを取り出したいという場面は、事務職の仕事の中で意外とよく出てきます。並べ替え(ソート)機能を使えば一時的には確認できますが、元の表の並び順を崩したくない、他の集計とあわせて自動更新したいという場合には不向きです。この記事では、範囲内のk番目に大きい・小さい値をそのまま数式で取り出せるLARGE関数とSMALL関数について、実務での使い方とよくあるトラブルを解説します。

LARGE・SMALL関数の基本構文

LARGE関数は指定した範囲の中でk番目に大きい値を、SMALL関数はk番目に小さい値を返す関数です。どちらも引数は「範囲」と「順位(k)」の2つだけとシンプルです。

=LARGE(範囲, k)　/　=SMALL(範囲, k)

例：B2:B20の売上金額のうち、1番大きい値(トップの売上)を求める場合
=LARGE(B2:B20, 1)
3番目に小さい値を求める場合
=SMALL(B2:B20, 3)

kに1を指定すれば最大値・最小値と同じ結果になり、kを2、3……と増やしていけば、2位、3位の値を順に取り出せます。ROW関数と組み合わせてkの値を自動で1、2、3と変化させれば、セルをコピーするだけで「上位3件のリスト」を一気に作ることもできます。たとえば=LARGE($B$2:$B$20, ROW()-1)という数式を3行分下にコピーすれば、1位・2位・3位が自動的に並びます。

RANK関数との使い分け

LARGE・SMALLとよく混同される関数にRANK(またはRANK.EQ)があります。この2つは似ているようで、求めている情報がまったく逆です。

RANK関数

「この値」が何位かを求める
入力：値、出力：順位

LARGE関数

「この順位」の値がいくつかを求める(降順)
入力：順位、出力：値

SMALL関数

「この順位」の値がいくつかを求める(昇順)
入力：順位、出力：値

名簿の各行に「この人は全体の何位か」を表示したいならRANK、名簿とは別の場所に「上位3名の売上額だけ」を一覧化したいならLARGEというように、作りたい表の形によって選ぶ関数が変わります。両方を組み合わせて、LARGEで抽出した金額をキーにINDEX・MATCHで氏名を引っ張ってくるランキング表もよく作られます。

実務でのよくある使用シーン

営業事務担当者であれば、月間の個人別売上一覧からLARGE関数で上位3名の売上金額を抽出し、INDEX・MATCHと組み合わせて「営業成績上位3名」の氏名入りランキング表を毎月自動で更新する、という使い方が典型的です。経理担当者であれば、経費精算の科目別集計のうち、金額が特に大きい上位数件を抽出して申請内容に不備がないか重点的にチェックしたり、逆にSMALL関数で金額が小さい(0円に近い)下位の科目を抽出して入力漏れがないか確認したりする場面があります。どちらのケースも、表の並び順そのものを変えずに「上位・下位だけをピックアップして目立たせたい」というニーズにLARGE・SMALLがぴったり合います。

LARGE・SMALLでよくあるエラー・トラブルと対処法

#NUM! エラー（kがデータ件数を超えている）

最も多いパターンです。指定した範囲に存在するデータの個数より大きいkを指定するとエラーになります。

例：=LARGE(B2:B10, 8) で、B2:B10のうち数値が入っているのは5件だけ（8番目に大きい値は存在しない）

対処法として、対象件数が変動する表では=IF(COUNT(範囲)>=k, LARGE(範囲,k), "-")のようにCOUNT関数でチェックしてから計算すると、データが少ない月でもエラー表示にならず安全です。

#NUM! エラー（kが0以下、または未入力に近い状態）

kは1以上の整数である必要があります。0やマイナスの値を指定するとエラーになります。

例：=SMALL(B2:B20, 0) や =LARGE(B2:B20, -1)

ROW関数などでkを自動生成している場合、数式を上方向にコピーしてしまい意図せずkが0や負の数になっていることがあります。数式をコピーした後は、先頭行のkが1になっているか確認しましょう。

データがあるはずなのに結果に反映されない

LARGE・SMALLは数値だけを集計対象とし、文字列や空白セルは自動的に無視する仕様です。この仕様を知らないと「データが消えた」と誤解しがちです。

例：B2:B20の中に「未入金」「検討中」といった文字列や空白セルが混じっていると、そのセルはカウントも順位付けもされず読み飛ばされる

対処法として、金額欄には数値のみを入力するルールを徹底するか、文字列扱いになっている数値がないかセルの左寄せ表示をチェックしてください。件数が想定より少ない場合は、COUNT関数で実際に数値として認識されている件数を確認すると原因の切り分けが早くなります。

初心者がつまずきやすいポイントとコツ

RANKとLARGE・SMALLの関係を混同しない。「順位から値を求める」のがLARGE・SMALL、「値から順位を求める」のがRANKと覚えておくと、どちらを使うべきか迷いません。
範囲は絶対参照（$）で固定する。ROW関数などと組み合わせてkを自動で増やす場合、範囲がずれてしまうと集計結果が崩れるため、範囲側は$B$2:$B$20のように固定しましょう。
文字列・空白セルは自動的に無視される仕様だと理解しておく。データが反映されないときは、まずエラーではなく「数値として認識されていない」可能性を疑うと解決が早くなります。
同じ値が複数ある場合、LARGE・SMALLは同順位をそのまま重複して返す。たとえば1位と2位が同額の場合、LARGE(範囲,1)とLARGE(範囲,2)は同じ値になります。重複を除いた「本当に異なる金額の3位」が欲しい場合は、別途工夫が必要です。

まとめ

LARGE・SMALL関数は、範囲内のk番目に大きい・小さい値をそのまま取り出せる、ランキング表作成の基本となる関数です。RANK関数とは入力と出力が逆の関係にあることを押さえ、「値から順位」ならRANK、「順位から値」ならLARGE・SMALLと使い分けましょう。kがデータ件数を超えたり0以下になったりすると#NUM!エラーになること、文字列や空白セルは自動的に無視される仕様であることの2点さえ押さえておけば、営業成績や経費精算の上位・下位抽出も安心して自動化できます。数式を一から組み立てるのが不安な方は、下のツールで日本語の空欄を埋めるだけで数式を作成できます。

よくある質問

LARGE関数で#NUM!エラーが出るのはなぜですか？

第2引数(k)に指定した順位が、範囲内のデータの個数を超えている場合に#NUM!エラーになります。たとえばデータが5件しかないのにk=8を指定すると、8番目に大きい値は存在しないためエラーになります。データ件数が可変の表では、k=COUNT(範囲)を超えないようIF関数などで上限をチェックすると安全です。

データがあるのにLARGEやSMALLの結果に反映されないのはなぜですか？

LARGE・SMALLは数値だけを集計対象とし、文字列や空白セルは自動的に無視する仕様です。金額欄に「未確定」「-」といった文字列が混ざっていたり、数値が文字列として入力されていたりすると、そのセルは件数にも順位にもカウントされません。セルの表示が左寄せになっていないか確認し、文字列になっている場合は数値に変換してください。

RANK関数とLARGE・SMALL関数はどう使い分ければよいですか？

RANK関数は「特定の値が全体の中で何位か」を求める関数で、SMALL・LARGE関数は「何位の値がいくつか」を求める関数です。両者は入力と出力が逆の関係にあります。個々のデータの順位を一覧に表示したいならRANK、上位・下位だけを抜き出したランキング表を作りたいならLARGE・SMALLを使います。

→ LARGE/SMALLの数式を作ってみる ← 記事一覧に戻る