Excelで2番目に大きい値を抽出・色付けする方法

エクセルのLARGE関数を使って、n 番目に大きい値を抽出する方法をご紹介します。同順位が存在する数値から 2 番目に大きい値を抽出する方法や、2 番目に大きい数値に自動で色付けする方法についても解説しているので、ぜひ参考にしてみてください。

LARGE関数の使い方

LARGE関数は指定した範囲から、相対的な順位に基づいて n 番目に大きな値を返します。

例えば LARGE(C2:C6,2) なら、抽出されるのは 2 番目に大きい価格 300 です。このように大きい方から数えて第 1 位、第 2 位、または第 n 位の値を取得できます。

LARGE(範囲,1) は、MAX(範囲) と同じです。

LARGE関数の構成要素：(配列,順位)

Excelのバージョンが Microsoft365 または Excel2021 なら、UNIQUE関数を使って重複なしのデータを簡単に抽出可能です。Excel2019 以前の場合は、IF関数を組み合わせます。

UNIQUE関数は指定した範囲から、すべての一意の値を返します。

例えば UNIQUE(C2:C6) なら6行目の重複値 300 は削除され、先に出現する 300 のみ返します。

UNIQUE関数の構成要素：(範囲, [列の比較], [回数指定])

LARGE関数の範囲に、UNIQUE関数の返り値(一意の値)を指定します。これを当てはめると、数式は LARGE(E2:E5,2) になります。

UNIQUE関数を直接範囲に指定する場合は LARGE(UNIQUE(C2:C6),2) です。

COUNTIF関数は条件範囲から、検索条件に一致する要素の個数を返します。

ポイントは $C$2:C2 のように、条件範囲の開始位置のみ固定することです。これにより数式をコピーしたときに、終点位置のみ１行ずつ下に拡張されます。

COUNTIF関数の構成要素： (条件範囲,検索条件)

IF関数は論理式が成立する場合に TRUE値 を、不成立の場合に FALSE値を返します。

つまりCOUNTIF関数の返り値が 1 のときそのまま価格を、1 ではない（1 より大きい）ときに空白を指定すれば、一意の値のみ抽出可能です。

IF関数の構成要素：(論理式, TRUE値, FALSE値)

LARGE関数の範囲に、IF関数の返り値を指定します。

IF関数を直接指定する場合の数式は LARGE(IF(E2:E6=1,C2:C6,””),2) です。数式内に配列を使用しているので、[Ctrl]+[Shift]+[Enter] で確定してください。

まず、色付けする範囲を選択してください。

次にホームタブの[条件付き書式]から、[新しいルール]を開きます。

ルールーの種類を、[数式を使用して、書式設定するセルを決定]に設定します。

次を満たす場合に入力する数式は =C2=LARGE($C$2:$C$6,2) です。セル参照(C2)にはトリガー列の先頭のセルを指定し、LARGE関数の範囲は固定します。

範囲の行全体に色付けする場合は、ルールの適用範囲を変更します。

数式のセル参照は $C2 のように、列だけ固定してください。これによりLARGE(範囲,2番目) が成立するかの検証はC列に対して行い、色付けは行全体に適用されます。