エクセルのSMALL関数を使って、n 番目に大きい値を抽出する方法をご紹介します。SMALL関数に複数の条件を指定する方法や、2 番目に小さい数値に自動で色付けする方法についても解説しているので、ぜひ参考にしてみてください。
SMALL関数の使い方
SMALL関数は指定した範囲から、相対的な順位に基づいて n 番目に小さな値を返します。
例えば SMALL(C2:C6,2) なら、抽出されるのは 2 番目に小さい価格 150 です。このように小さい方から数えて第 1 位、第 2 位、または第 n 位の値を取得できます。
SMALL関数の順位に 1 を指定した場合は、最小値を取得するMIN関数と同義です。
SMALL関数の構成要素：(配列,順位)
SMALL関数に複数条件を指定する方法
Excelのバージョンが Excel 2021 以降または Microsoft 365 なら、 FILTER関数で条件指定できます。Excel 2019 以前をお使いの方は、IF関数とAND関数を組み合わせて代用可能です。
IF関数で条件を指定する方法
まずIF関数で条件と一致するセルを取得し、結果をSMALL関数の範囲に指定します。
例えば IF(A2:A6=”果物”,C2:C6,””) はカテゴリーが果物なら価格を、それ以外なら空白を返します。これをSMALL関数の範囲に指定すると、SMALL(IF(A2:A6″果物”,C2:C6,””),2) です。
数式内に配列を使用しているので、確定時は [Ctrl]+[Shift]+[Enter] を押してください。
IF関数の構成要素：(論理式, [値が真の場合], [値が偽の場合])
スピル非対応のバージョンでは確認できませんが、配列を指定したIF関数は例のように A2=果物、A3=果物、A4=果物…の真偽を順番に判定していきます。
SMALLはこれを範囲として受け取るので、{200、300、1200} から 2 番目に小さい値を検索します。
AND関数で複数条件を指定する方法
AND関数は論理的の真偽判定を行い、すべて成立する場合のみ TRUE を返します。
例えば IF(AND(A2=”果物”,C2>200),C2,””) ならテゴリーが果物かつ価格が200円より大きい場合に、真の場合の値(価格)を返します。
AND関数の構成要素：(論理式1, [論理式2, …])
SMALL関数の範囲にIF関数の結果を指定します。
例えば SMALL(E2:E6,2) なら、抽出されるのは{300,1200}の中で2番目に小さい値 1200 です。
FILTER関数で複数条件を指定する方法
FILTER関数は条件に従ってフィルタ処理を行い、結果を指定した範囲から返します。
複数条件を満たすデータを抽出する場合は、条件同士を乗算演算子 (*) で掛け算してください。
例えば SMALL(FILTER(C2:C6,(A2:A6=”果物”)*(C2:C6>200)),2) なら、カテゴリーが果物かつ価格が200円より大きい行に絞り込みます。両方の条件を満たすのは {300,1200} です。
FILTER関数の構成要素：(範囲, 条件, [空の場合])
同順位から2番目に小さい値を抽出する方法
Excelのバージョンが Microsoft365 または Excel2021 なら、UNIQUE関数を使って一意のデータを簡単に抽出可能です。Excel2019 以前の場合は、IF関数を組み合わせます。
UNIQUE関数で重複削除する方法
UNIQUE関数は指定した範囲から、すべての一意の値を返します。
例えば UNIQUE(C2:C6) なら6行目の 重複値 100 は削除され、先に出現する 100 のみ返します。
UNIQUE関数の構成要素：(範囲, [列の比較], [回数指定])
SMALL関数の範囲に、UNIQUE関数の返り値(一意の値)を指定します。例えば2番目に小さい値を抽出するなら、数式は SMALL(E2:E5,2) です。
UNIQUE関数を直接範囲に指定する場合は SMALL(UNIQUE(C2:C6),2) になります。
IF関数で重複削除する方法
まずCOUNTIF関数で、同じ値が出現する回数を取得します。
ポイントは $C$2:C2 のように、条件範囲の開始位置のみ固定することです。これにより数式をコピーしたときに、範囲の終点位置のみ１行ずつ下に拡張されます。
COUNTIF関数の構成要素： (条件範囲,検索条件)
次にIF関数で、値の出現回数が 1 の場合のみ価格を取得します。
これをSMALL関数の範囲に指定すればいいので、SMALL(IF(E2:E6=1,C2:C6,””),2) です。数式内に配列を使用しているので、[Ctrl]+[Shift]+[Enter] で確定してください。
2番目に小さい値に色付けする方法
まず、色付けする範囲を選択してください。
次にホームタブの[条件付き書式]から、[新しいルール]を開きます。
ルールーの種類を、[数式を使用して、書式設定するセルを決定]に設定します。
2番目に小さい値に色付けするなら、数式は =C2=SMALL($C$2:$C$6,2) です。セル参照(C2)にはトリガー列の先頭のセルを指定し、SMALL関数の範囲は固定します。
範囲の行全体に色付けする場合は、ルールの適用範囲を変更します。
新しいルールの作成時に範囲全体を選択しておくか、[ルールの管理]から変更可能です。
トリガー列のセルは $C2 のように、列だけ固定してください。これにより2番目に小さい値かどうかの検証はC列に対して行い、色付けは行全体に適用されます。
