【Excel】SMALL関数－指定されたデータの中で、k番目に小さなデータを返す関数－

約～分
（約文字）

2022-05-30

【Excel】SMALL関数－指定されたデータの中で、k番目に小さなデータを返す関数－

SMALL関数とは

エクセルの「SMALL関数」は指定されたデータの中で、k番目に小さなデータを返す関数です。

• このページで分かること
• SMALL関数でできることと基本の使いどころ
• 構文と引数の意味
• SMALL関数の使用例

データの集まりの中から、相対的な位置にある値を求めることが出来ます。

構文と引数

• 365
• 2019
• 2016
• 2013
• 2010
• 2007

=SMALL(範囲,順位)

重要：日付は、DATE関数を使って入力するか、他の数式または他の関数の結果として指定します。 例えば2020年3月13日を入力する場合は、DATE(2020,3,13) を使用します。 日付を文字列として入力した場合、エラーが発生することがあります。

SMALL関数の解説

• ・配列にデータが含まれていない場合は、エラー値#NUM!が返されます。
• ・順位が0以下、または対象となるデータの個数よりも大きい場合は、エラー値#NUM!が返されます。
• ・範囲に含まれるデータの個数をnとするとき、SMALL(範囲,1) は最小値を返します。
• ・範囲に含まれるデータの個数をnとするとき、SMALL(範囲,n) は最大値を返します。

出典：Microsoft - Office サポート

範囲内の重複値は別の順位として数えられる

SMALL関数は、同じ値が複数あっても「1つにまとめて」数えることはしません。同じ値もそれぞれ別の順位として扱います。

たとえば、A1～A5セルに「10、20、20、30、40」と入力されているとします。

• =SMALL(A1:A5,1) → 10（1番目に小さい値）
• =SMALL(A1:A5,2) → 20（2番目に小さい値）
• =SMALL(A1:A5,3) → 20（3番目も同じ20になります）
• =SMALL(A1:A5,4) → 30（4番目に小さい値）

このように、20が2つあるため、2番目と3番目はどちらも20になり、30は「4番目」になります。重複を除いた順位がほしいときは、SMALL関数だけでは求められないので注意してください。

空白セル・文字列・論理値は無視される

範囲の中に数値以外のデータが含まれていても、SMALL関数はそれらを無視して数値だけを対象に順位を数えます。

たとえば、B1～B6セルが次のような内容だったとします。

• B1：5
• B2：（空白）
• B3：「あ」（文字列）
• B4：TRUE（論理値）
• B5：3
• B6：8

この場合、数値として数えられるのは「5、3、8」の3個だけです。

• =SMALL(B1:B6,1) → 3
• =SMALL(B1:B6,2) → 5
• =SMALL(B1:B6,3) → 8
• =SMALL(B1:B6,4) → #NUM!（数値が3個しかないため、4番目は存在しません）

「データが6セルあるのに4番目がエラーになる」ときは、数値として入力されていないセル（文字列や空白）が含まれていないかを確認しましょう。数字が文字列として入力されている場合も、数値として数えられません。

順位に配列定数{1,2,3}を指定して複数の値を一度に取り出す

SMALL関数の「順位」に、{1,2,3}のような配列定数（波かっこ{ }で囲んだ値の並び）を指定すると、小さい方から複数の値をまとめて取り出せます。

たとえば、C1～C6セルに「50、20、80、10、60、30」と入力されているとき、小さい方から3つの合計は次のように求められます。

• =SUM(SMALL(C1:C6,{1,2,3})) → 10＋20＋30 ＝ 60
• =AVERAGE(SMALL(C1:C6,{1,2,3})) → (10＋20＋30)÷3 ＝ 20

{1,2,3}は「1番目、2番目、3番目」をまとめて指定する書き方です。{1,2}にすれば小さい方から2つ、{1,2,3,4,5}にすれば5つを対象にできます。配列定数の区切りの「,（カンマ）」は、半角で入力してください。

また、Microsoft 365や Excel 2021以降では、=SMALL(C1:C6,{1,2,3}) と入力するだけで、10、20、30が隣り合うセルに自動で表示されます（スピル機能）。それ以前のバージョンでは、上のようにSUM関数やAVERAGE関数で囲んで使うと安全です。

LARGE関数との使い分け

SMALL関数は「小さい方から数えて○番目」、LARGE関数は「大きい方から数えて○番目」を返します。たとえば、=SMALL(A1:A5,1) は最小値、=LARGE(A1:A5,1) は最大値です。成績の下位3人を調べたいときはSMALL関数、上位3人を調べたいときはLARGE関数を使うと覚えておくと分かりやすいでしょう。

SMALL関数の使用例

上のデータの1列目の値のうち、4番目に小さな数値を求めます。

=SMALL(A2:A10,4)

=4

上のデータの2列目の値のうち、2番目に小さな数値を求めます。

=SMALL(B2:B10, 2)

=3

以下では、同じデータ（1列目は A2:A10、2列目は B2:B10）を使って、SMALL関数でよく使う場面を順に紹介します。SMALL関数は「データを小さい順に並べたとき、k番目にある値」を返す関数です。数式を入力するだけで、データを並べ替える必要はありません。

下から3番目の値（3番目に小さい値）を求める

「下から3番目」とは、小さい順に並べたときの3番目のことです。第2引数に 3 を指定します。1列目のデータを小さい順に並べると 2、3、3、4、4、4、5、6、7 となるため、3番目は 3 です。

=SMALL(A2:A10,3)

=3

2列目の場合は、小さい順に 1、3、4、7、8、8、12、23、54 と並ぶので、同じ式で次の結果になります。

=SMALL(B2:B10,3)

=4

1列目には「3」が2つあるため、2番目も3番目も 3 になります。SMALL関数は同じ値があっても1つずつ数えるので、重複を除いた順位にはならない点に注意してください。

最小値・2番目・3番目を並べて表示する

小さい方から3つの値を表に並べたいときは、第2引数だけを 1、2、3 と変えた式を3つのセルに入力します。たとえば D2 から D4 に次のように入力します。

範囲を $B$2:$B$10 のように「$」で固定しておくと、式をコピーしても範囲がずれません。なお、1番目に小さい値は MIN関数（=MIN(B2:B10)）と同じ結果になります。

数式をコピーして順位を自動で増やす

順位を手で書き換えるのが面倒なときは、ROW関数で順位を自動計算します。D2 に次の式を入力し、下へコピー（オートフィル）してください。

=SMALL($B$2:$B$10,ROW()-1)

D2 は ROW()=2 なので 2-1=1 番目、D3 は 2番目、D4 は 3番目というように、コピーするだけで 1、3、4、7、8…と小さい順に並びます。表の開始行が違う場合は、「-1」の部分を調整して 1 から始まるようにします。

小さい方から3つの合計を求める

配列定数 {1,2,3} を使うと、小さい方から3つの値をまとめて合計できます。

=SUM(SMALL(B2:B10,{1,2,3}))

=8（1 + 3 + 4）

LARGE関数と組み合わせて「上から」「下から」を使い分ける

SMALL関数が「小さい方から数える」のに対し、LARGE関数は「大きい方から数える」関数です。同じ1列目のデータで比べてみましょう。

また、最大値と最小値の差（範囲の広がり）を見たいときは、=LARGE(A2:A10,1)-SMALL(A2:A10,1) のように組み合わせられます。この例では 7-2=5 です。

#NUM! エラーが出るときの注意点

第2引数に、データの個数より大きい数や 0 以下の数を指定すると、#NUM! エラーになります。たとえば、データが9個の範囲に =SMALL(A2:A10,10) と入力するとエラーです。範囲内の数値の個数は =COUNT(A2:A10) で確認できます。

なお、範囲内の空白セルや文字列は無視され、数値だけが対象になります。「数字に見える文字列」が混ざっていると個数が合わなくなるので、結果がおかしいときは確認してみてください。

関連する関数

• LARGE関数：指定されたデータの中でk番目に大きなデータを返す関数
• SMALL関数：指定されたデータの中でk番目に小さなデータを返す関数
• MAX関数：指定されたデータの中で最大値を返す関数
• MIN関数：指定されたデータの中で最小値を返す関数

LARGE関数とSMALL関数の違い

「excel large関数 small関数」で調べている方に向けて、違いをまとめます。2つの関数は書き方がまったく同じで、数える向きだけが逆です。

• LARGE関数は、大きい方（上）から数えてk番目の値を返します。
• SMALL関数は、小さい方（下）から数えてk番目の値を返します。

たとえばA1:A5セルに 50、80、20、90、60 と入力されているとします。

• =LARGE(A1:A5,2) の結果は 80（大きい順で2番目）
• =SMALL(A1:A5,2) の結果は 50（小さい順で2番目）

「成績の上位3人の点数」にはLARGE関数、「タイムの速い（数値が小さい）上位3人」や「売上が低い下位3件」にはSMALL関数、というように、知りたい順位の向きで使い分けると覚えやすくなります。

MIN関数・RANK関数・AGGREGATE関数との使い分け

• MIN関数：いちばん小さい値（1番目）だけが欲しいときに使います。=MIN(A1:A5) は =SMALL(A1:A5,1) と同じ結果です。2番目以降が必要ならSMALL関数を使います。
• RANK関数：SMALL関数とは逆向きの関数です。SMALL関数は「順位から値」を求めますが、RANK関数は「値から順位」を求めます。「この点数は何位か」を知りたいときはRANK関数を使います。
• AGGREGATE関数：SMALL関数と同じように「k番目に小さい値」を求められます。さらに、エラー値（#N/Aや#DIV/0!など）や非表示の行を無視して計算できます。範囲にエラーが混ざっていてSMALL関数が使えないときの代わりになります。

SMALL関数は、範囲内にエラー値があると結果もエラーになります。その場合はAGGREGATE関数を使うか、エラーの原因を先に直してください。

よくある質問

Q. SMALL関数は何をするときに使いますか？

SMALL関数は、指定されたデータの中で、k番目に小さなデータを返す関数です。 とくに LARGE関数 など近い関数との違いを合わせて押さえると、使い分けしやすくなります。

Q. SMALL関数がうまく動かないときは何を確認すればよいですか？

まず 範囲、順位 の指定方法を見直し、対象範囲、条件、空白セル、標本と母集団の違い を確認してください。

よくある失敗例・エラー対処

SMALL関数で期待通りの結果にならないときは、次の点を確認してください。

• 範囲、順位 の指定が関数の仕様どおりか確認する
• 空白セルや文字列、エラー値の扱いを確認する
• 標本か母集団か、近い統計関数との違いを確認する

まとめ

このページでは、SMALL関数について以下を確認しました。

• ・SMALL関数でできることと基本の使いどころ
• ・構文と引数の意味
• ・SMALL関数の使用例

前の記事

【Excel関数】Excel 関数 一覧表（アルファベット別）

次の記事

Excel 関数 一覧表（機能別）