GoogleスプレッドシートのLARGE関数でn番目に大きい値を抽出する方法

スプレッドシートのLARGE関数を使って、n 番目に大きい値を抽出する方法をご紹介します。条件と一致する n 番目の数値を抽出する方法や、重複値(同順位)を含むデータから n 番目に大きい値を抽出するについても解説しているので、ぜひ参考にしてみてください。

LARGE関数の使い方

LARGE関数は指定したデータセットを含む配列または範囲から、 N 番目に大きい値を返します。

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

LARGE(範囲,1) は、最大値を返す MAX(範囲) と同じです。

LARGE関数の構成要素：( データ , n )

まずFILTER関数で、指定した範囲を条件と一致する行または列にフィルタ処理します。

例えば FILTER(C2:C6,C2:C6<1000) なら、価格を1,000円より小さい数値に絞り込みます。なお条件に文字列を指定する場合は、二重引用符(“文字列”)が必要です。

FILTER関数の構成要素：(範囲, 条件1, [条件2, …])

次にLARGE関数のデータ範囲に、FILTER関数の返り値を指定します。

例えば LARGE(FILTER(C2:C6,C2:C6<1000),2) なら1,000円より小さい価格の中から2番目に大きい数値を返すので、抽出結果は 200 です。

まずUNIQUE関数で指定した範囲から重複行を削除し、一意の値を抽出します。

例えば UNIQUE(C2:C6) なら、2 回目に出現する 6 行目の価格 300 は削除され、すべて一意の値のみ抽出されます。

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

次にLARGE関数のデータ範囲に、UNIQUE関数の返り値を指定します。

例えば LARGE(UNIQUE(C2:C6),2) なら一意の価格{150,200,300,100} から2番目に大きい数値を返すので、抽出結果は 200 です。

まず色付け対象の範囲を選択し、表示形式タブから[条件付き書式]を開いてください。

次に、セルの書式設定の条件を[カスタム数式]に設定します。

2番目に大きい数値に色付けする場合の数式は =C2=LARGE($C$2:$C$6,2) です。セル参照(C2)にはトリガー列の先頭のセルを指定し、LARGE関数の範囲は固定します。

書式設定のスタイルからは、塗りつぶしの色やテキストの色を選択可能です。

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

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