更新日:、 作成日:

エクセル SMALL 関数:昇順で指定した順位の値を取得する

はじめに

エクセルの SMALL 関数の使い方を紹介します。

SMALL 関数は昇順で指定した順位の値を取得します。1, 2, 3 のような小さい順が昇順です。

=SMALL(A1:A3,2) のようにして、2 番目に小さい値を取得できます。

=SMALL(IF(A1:A3="エクセル",B1:B3),2) のようにして、条件を指定してセルの値が「エクセル」中で 2 番目に小さい値を取得できます。

上位 20 % の値を取得するには「PERCENTILE 関数」を使用します。
小さい順に並べるには「SORTBY 関数」を使用します。

SMALL 関数の引数

SMALL(範囲, 順位)
範囲の中から昇順の順位の値を取得します。

引数「範囲」セルの範囲、数値を指定できます。
引数「順位」引数「範囲」の中から取得する順位を指定します。スピル化

スピル化:セルの範囲や配列を指定すると結果が「スピル」します。

使い方

SMALL 関数の使い方を紹介します。

2 番目に小さい値を取得する

セルの範囲「B3」~「B7」の 2 番目に小さい値を取得します。

=SMALL(B3:B7,2)
9

セルの値の順位を取得する

値の中からセル「C3」の順位の値を取得します。

=SMALL($B$4:$B$8,C3)
10

非表示のセルを無視して取得する

非表示のセルを無視して小さい順の値を取得するには「AGGREGATE 関数」を使用します。

高度な使い方

SMALL 関数と他の関数を組み合わせた使い方などを紹介します。

条件を指定する

条件に一致しているセルの小さい順の値を取得するには、判定用の列を作成してその中に「IF 関数」で条件を入力します。その結果を引数「範囲」に指定して小さい順の値を取得します。

名前が エクセル の条件を入力します。

=IF(B2="エクセル",C2)

その結果を引数「範囲」に指定して小さい順の値を取得します。

=SMALL(D3:D7,2)
12
文字列かどうかや空白など、特定の値を条件にする方法は「COUNTIF 関数」にまとめています。

判定用の列を作成しない (スピル)

判定用の列を作成しないで小さい順の値を取得するには「スピル」を使用します。

=SMALL(IF(B3:B7="エクセル",C3:C7),2)

IF 関数」の中にセルの範囲を入力しているのでスピルします。

IF 関数」の条件に一致するときは、小さい順を求めるための値を返しています。それを SMALL 関数で小さい順の値を取得します。

条件に「AND 関数」や「OR 関数」はスピルされないため使用できません。

スピルで AND や OR 条件にするには「スピル、動的配列数式の使い方」をご覧ください。
スピルは 365 または 2021 から使用できます。

数式を Ctrl + Shift + Enter で確定すると「配列数式」になり、すべてのバージョンで使用できます。

判定用の列を作成しない (ラムダ)

判定用の列を作成しないで小さい順の値を取得するには「MAP 関数」を使用します。複雑なので詳細は関数のページをご覧ください。

=SMALL(
MAP(B3:B7,C3:C7,
LAMBDA(b,c,
IF(b="エクセル",c,FALSE))),2)

MAP 関数」に条件を入力します。OR 条件にしたり、「EXACT 関数」を使用して大文字小文字を区別できます。

ラムダは 365 で使用できます。

解説

引数「範囲」が日付なら、日付の実際の値は数値なのでその値として扱います。

引数「範囲」に文字列や TRUE や FALSE、空のセルが含まれるときは、そのセルを無視します。

引数「範囲」に 1 つも有効な数値が含まれていないときはエラー #NUM! になります。

引数「順位」に引数「範囲」の数を超える値のときはエラー #NUM! になります。引数「範囲」が 3 個の値なら引数「順位」に指定できるのは 1 ~ 3 です。

引数「順位」が 1 なら「MIN 関数」と同様に最小値を取得できます。

同率順位

同率順位の値があるときは、その順位でも次の順位でも値を取得できます。次のように 2 位が複数あるときは、2 位でも 3 位でも取得できます。

11