【Excel】SMALL・LARGE関数でN番目に小さい・大きい値を取り出す方法

Excelで特定の順位の値を取得したい場面はありませんか。例えば、テストの点数から3番目に低い点数を見つけたい、売上データから2番目に高い数値を抽出したい、といったケースです。そんな時、SMALL関数やLARGE関数が非常に役立ちます。これらの関数を使えば、データの中からN番目に小さい値やN番目に大きい値を簡単に取り出せます。この記事では、SMALL関数とLARGE関数の基本的な使い方から、応用的な活用方法までを詳しく解説します。

SMALL・LARGE関数の基本機能と仕組み

SMALL関数とLARGE関数は、どちらも数値の配列(データの集まり)の中から、指定した順位の値を取得するための関数です。この2つの関数は、データ分析において、上位や下位の値を素早く特定したい場合に非常に便利です。

SMALL関数は、配列の中から「k番目に小さい値」を返します。例えば、kに「1」を指定すれば最小値が、kに「2」を指定すれば2番目に小さい値が返されます。

LARGE関数は、逆に配列の中から「k番目に大きい値」を返します。kに「1」を指定すれば最大値が、kに「2」を指定すれば2番目に大きい値が返されます。

どちらの関数も、第一引数に値を取り出したいデータの範囲(配列)を指定し、第二引数に何番目の値を取り出したいかを示す整数(k)を指定します。この「k」の値は、範囲内のデータの個数を超えることはできません。

SMALL関数でN番目に小さい値を取り出す手順

ここでは、具体的なデータを用いてSMALL関数を使ってN番目に小さい値を取り出す手順を解説します。

1. 対象データの準備
   Excelシートに、値を取り出したい数値データを入力します。例えば、A1セルからA10セルにテストの点数を入力しておきます。
2. SMALL関数の入力
   結果を表示したいセル(例えばB1セル)に、以下の形式で関数を入力します。

   =SMALL(配列, k)

   ここで、「配列」には対象となるデータの範囲(例: A1:A10)を指定します。「k」には、何番目に小さい値を取得したいかの数値を指定します。例えば、3番目に小さい値を取得したい場合は「3」と入力します。

   例: 3番目に小さい値を取得する場合
   =SMALL(A1:A10, 3)

3. Enterキーで確定
   数式を入力したらEnterキーを押し、計算結果を表示します。B1セルにA1:A10の範囲で3番目に小さい値が表示されます。

例えば、A1:A10に「10, 5, 20, 15, 5, 30, 25, 10, 35, 40」と入力されている場合、SMALL(A1:A10, 1)は「5」、SMALL(A1:A10, 2)は「5」、SMALL(A1:A10, 3)は「10」となります。

LARGE関数でN番目に大きい値を取り出す手順

次に、LARGE関数を使ってN番目に大きい値を取り出す手順を解説します。SMALL関数と基本的な考え方は同じです。

1. 対象データの準備
   Excelシートに、値を取り出したい数値データを入力します。例えば、A1セルからA10セルに売上データを入力しておきます。
2. LARGE関数の入力
   結果を表示したいセル(例えばB1セル)に、以下の形式で関数を入力します。

   =LARGE(配列, k)

   ここで、「配列」には対象となるデータの範囲(例: A1:A10)を指定します。「k」には、何番目に大きい値を取得したいかの数値を指定します。例えば、2番目に大きい値を取得したい場合は「2」と入力します。

   例: 2番目に大きい値を取得する場合
   =LARGE(A1:A10, 2)

3. Enterキーで確定
   数式を入力したらEnterキーを押し、計算結果を表示します。B1セルにA1:A10の範囲で2番目に大きい値が表示されます。

例えば、A1:A10に「100, 150, 200, 120, 180, 250, 220, 130, 300, 280」と入力されている場合、LARGE(A1:A10, 1)は「300」、LARGE(A1:A10, 2)は「280」、LARGE(A1:A10, 3)は「250」となります。

SMALL・LARGE関数でよくある質問とトラブルシューティング

SMALL関数とLARGE関数を使う上で、いくつか知っておきたい注意点や、発生しやすい問題とその解決策があります。

kに指定する値についての注意点

SMALL関数やLARGE関数で指定する「k」の値は、対象となる配列内のデータの個数を超えることはできません。例えば、データが10個しかないのに「k」に「11」を指定すると、エラー(#NUM!エラー)が表示されます。

対処法: kに指定する値が、対象範囲のデータ数以下であることを確認してください。COUNT関数などを使ってデータ数を数え、その数を超えないようにkの値を設定します。

配列内に数値以外のデータが含まれる場合

SMALL関数とLARGE関数は、数値データのみを対象とします。配列内に文字列やエラー値が含まれている場合、それらは無視されます。しかし、空白セルは0として扱われる場合があるので注意が必要です。

対処法: 事前にデータ範囲から文字列やエラー値を取り除いておくか、IF関数などを組み合わせて数値データのみを対象とするように工夫します。例えば、IF(ISNUMBER(A1:A10), A1:A10, “”) のように配列数式として使うことで、数値のみを抽出できます(Ctrl+Shift+Enterで確定)。Microsoft 365では、動的配列機能により自動的に展開されます。

重複する値の扱い

SMALL関数とLARGE関数は、重複する値を区別せずに計算します。例えば、「5, 5, 10」というデータでSMALL(…, 1)とすると「5」が、SMALL(…, 2)とすると、もう一方の「5」が返されます。3番目に小さい値は「10」になります。

対処法: 重複を排除したい場合は、UNIQUE関数(Microsoft 365)やPower Queryなどの機能と組み合わせて使用します。あるいは、COUNTIF関数などと組み合わせた複雑な数式を組むことで、重複を考慮したN番目の値を取得することも可能です。

#N/Aエラーが発生する場合

#N/Aエラーは、主に「k」に指定した値が配列内の有効な数値の個数よりも大きい場合に発生します。前述の「kに指定する値についての注意点」を参照してください。

対処法: 対象範囲のデータ数を確認し、「k」の値を適切に設定し直します。

Excel 2019以前のバージョンでの注意点

Excel 2019以前のバージョンでもSMALL関数とLARGE関数は利用できます。基本的な使い方はMicrosoft 365と同じです。ただし、Microsoft 365で追加されたUNIQUE関数やFILTER関数といった動的配列関数との組み合わせは、バージョンによっては利用できないか、配列数式(Ctrl+Shift+Enter)として入力する必要がある点に注意が必要です。

応用編: 条件付きでN番目の値を取得する

SMALL関数やLARGE関数をIF関数と組み合わせることで、特定の条件を満たすデータの中からN番目の値を取得できます。これは、例えば「東京支店の売上データの中で、3番目に高い売上はいくらか」といった分析に役立ちます。

ここでは、IF関数とSMALL関数を組み合わせて、「特定の条件を満たすデータ群」のN番目に小さい値を取得する例を挙げます。

IF関数とSMALL関数を組み合わせる方法

前提条件:

• A列に商品名、B列に数量、C列に単価が入っているとします。
• 「りんご」という商品の中で、2番目に小さい数量を取得したいとします。

以下の数式をExcelシートに入力します。

=SMALL(IF(A1:A10="りんご", B1:B10), 2)

重要: この数式は、Excel 2019以前のバージョンでは配列数式として入力する必要があります。数式を入力した後、CtrlキーとShiftキーを押しながらEnterキーを押してください。数式が {} で囲まれれば配列数式として正しく入力されています。Microsoft 365では、この操作は不要で、通常のEnterキーで確定できます。

数式の解説:

• IF(A1:A10="りんご", B1:B10): A1:A10の範囲で「りんご」と一致する行のB列(数量)の値を取り出します。一致しない場合はFALSEを返します。
• SMALL(..., 2): IF関数で取得された「りんご」の数量のリストの中から、2番目に小さい値を取得します。FALSEは数値として扱われないため、無視されます。

LARGE関数と組み合わせる場合も、同様の考え方で「k番目に大きい値」を取得できます。

AVERAGEIF関数などとの使い分け

条件付きの集計を行う場合、AVERAGEIF関数やSUMIF関数、COUNTIF関数などもよく使われます。これらの関数は、条件に合うデータの「平均」「合計」「個数」を計算するのに適しています。

一方、SMALL関数やLARGE関数は、条件に合うデータの中で「N番目の値」を取得したい場合に特化しています。例えば、「A支店の売上トップ3」をリストアップしたい場合などに、LARGE関数とIF関数を組み合わせた数式が有効です。これらの関数は、分析の目的に応じて使い分けることが重要です。

SMALL・LARGE関数と他の関数との比較

Excelには、データの並べ替えや特定の値を抽出するための関数が他にも存在します。SMALL・LARGE関数と、それらの関数との違いを理解しておくと、より効率的なデータ分析が可能になります。

SMALL・LARGE関数とMIN・MAX関数の違い

MIN関数は配列の中から最小値を、MAX関数は配列の中から最大値を返します。これらは、SMALL関数でk=1を指定した場合、またはLARGE関数でk=1を指定した場合の結果と同じです。

SMALL関数とLARGE関数は、最小値・最大値だけでなく、2番目、3番目といった任意の順位の値を取得できる点が最大の違いです。データセットの全体像を把握するだけでなく、特定のランキングや閾値を設定する際に、これらの関数は非常に強力なツールとなります。

SMALL・LARGE関数とSORT・FILTER関数の違い(Microsoft 365)

Microsoft 365で利用できるSORT関数やFILTER関数は、より高度なデータ操作を可能にします。SORT関数は、指定した範囲のデータを並べ替えた結果を返します。FILTER関数は、指定した条件に一致するデータのみを抽出した結果を返します。

これらの関数とSMALL・LARGE関数を組み合わせることで、さらに複雑な分析が可能です。例えば、FILTER関数で特定の条件を満たすデータだけを抽出し、その結果に対してSMALL関数やLARGE関数を適用することで、条件付きのN番目の値を取得できます。これは、IF関数と配列数式を組み合わせる方法よりも、より直感的で分かりやすい場合があります。

SMALL・LARGE関数とVLOOKUP・XLOOKUP関数の違い

VLOOKUP関数やXLOOKUP関数は、特定の値を基準に、対応する別の列の値を検索して返す関数です。これらは、データベースのような形式で管理されているデータから、特定の項目に関連する情報を引き出す際に使用されます。

一方、SMALL・LARGE関数は、データの「順位」に基づいて値を取得するものであり、特定のキー値で検索するVLOOKUP・XLOOKUP関数とは目的が異なります。例えば、売上ランキングを作成する際に、LARGE関数で上位の売上を抽出し、その売上に対応する担当者名をVLOOKUP関数で検索するといった連携は可能です。

機能	SMALL関数	LARGE関数	MIN関数	MAX関数
目的	k番目に小さい値を取得	k番目に大きい値を取得	最小値を取得	最大値を取得
引数(k)	必須	必須	不要	不要
順位指定	可能	可能	不可能	不可能
重複値の扱い	重複を区別しない	重複を区別しない	重複を区別しない	重複を区別しない
主な用途	下位ランキング、閾値設定	上位ランキング、閾値設定	最小値の特定	最大値の特定

まとめ

SMALL関数とLARGE関数を使えば、データの中からN番目に小さい値や大きい値を簡単に抽出できます。これにより、ランキング作成や特定の閾値設定といった分析が格段に効率化されます。IF関数と組み合わせることで、条件付きでのN番目の値の取得も可能になります。ぜひ、これらの関数を活用して、より高度なExcel分析に挑戦してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】