LARGE関数の使い方|配列の中で何番目に大きい数値か求める

Excel(エクセル)のLARGE関数の使い方
Excel関数

Excel(エクセル)のLARGE関数の使い方|配列の中で何番目に大きい数値か求める

使用関数:LARGE、SMALL、IF、COUNTIF

LARGE(ラージ)関数とは?

LARGEは「大きい」を意味しますが、これは客観的、数値的な比較で、主観的、感覚的な「大きい」を意味するBIGとは微妙な違いがあります。

LARGEは指定された配列の範囲で大きい方からの順番を求める関数です。降順」で位置を判定します。LARGEと反対に小さい方からの順番を求めるのはSMALL関数で、この2つの関数はセットで覚えた方がいいでしょう。

配列の中の数値を昇順で求めるSMALL関数の使い方を分かりやすく解説しています。

https://www.tschoolbank.com/excel/function-small/

LARGE関数の引数と書式

「数式」タブ「関数ライブラリ」→「その他の関数」→「統計」→ LARGEをクリックします。
「関数ライブラリ」→「その他の関数」→「統計」→ LARGEをクリック
LARGE関数の引数は「配列」と「順位」です。

書式の構成はこうなります。
LARGE関数の書式の構成

LARGE関数で大きい順にデータを並び替える

LARGE関数の引数ダイアログボックスで簡単な「降順の並べ替え」を実行してみましょう。

試験の結果をランダムに並べた表をLARGE関数を使って成績順位で並べ替えます。
LARGE関数で大きい順に並べ換える表
※氏名の列にはあらかじめ=IFERROR(VLOOKUP(E2,$A$2:$B$16,2,0),””)を入力してあり、総合点が入ると自動で氏名も表示されます。

第一引数「配列」

LARGE関数の引数ダイアログを開き、「配列」に総合点データの列範囲をドラッグで指定します。
LARGE関数の引数ダイアログの「配列」に総合点データの列範囲をドラッグで指定
※LARGE関数は数値データを対象にする関数なので、「配列」内に空白、文字列、論理値が含まれていた場合は無視されます。

第二引数「順位」

配列の中で大きい方から何番目のセルを抽出するかを数値で指定します。「1」と指定した場合は最大値が返り、「2」と指定すれば大きい方から2番目のデータが返ります。

この例では「1」をセル参照で指定します。
LARGE関数の引数「順位」にセル参照で「1」を指定

成績順位1位のセルに配列の中の最大値が表示されました。この数式を下までオートフィルコピーして、大きい順序での並べ替えが完了しました。
成績順位1位のセルのLARGE関数をオートフィルコピーして大きい順序での並べ替えが完了

LARGE関数に重複を除く条件を指定して大きい方から3番目までを取得

数値データの大きい方から○番目までをを取得したいという時、重複する値があると、最悪、同じ数値が並んでしまうようなことになります。
LARGE関数で重複データのある範囲から上位3番目までを取得

重複のセルをCOUNTIF関数で抽出

重複データを抽出するのには範囲から特定の条件に一致するセルをカウントするCOUNTIF関数を使います。「重複」という列項目を増やし、その最初のセルにCOUNTIFの数式を入れます。
引数「範囲」に数値データの一番上のセルを「$C$2:C2」というように指定します。
「C2セルからC2セルまで」ということですが、ポイントは最初のセル番地を絶対参照にすることです。
そうすることで、オートフィルコピーした時に一つずつ検索するセル範囲が増えていきます。

引数「検索条件」にも数値データの一番上のセルを指定、これは可変させる必要があるため相対参照のままでおきます。
重複データを抽出するCOUNTIF関数の範囲と検索条件を指定

数式は「=COUNTIF($C$2:C2,C2)」となります。これをデータの最終行までオートフィルコピーします。

重複するデータがあるのが「C9」セルなら、C2セルからC9セルまでが「範囲」で、C9の入力値が検索条件になり、「2」がカウントされます。
重複データが入った行でのCOUNTIFの引数の構成

IF関数で重複を除く条件を指定し、LARGE関数で上から3番目までを取得

IF関数で重複を除外する条件式を組み立てます。

論理式」にはCOUNTIFで求めた「重複」列のセル範囲を絶対参照で入力し、「=1」を付け加えます。「もし、「重複」列のセルが1だったら、」という条件です。

真の場合」にはLARGE関数で検索する範囲、点数のデータが入ったセル範囲をドラッグで指定します。「「重複」列のセルが1だったら、この範囲を検索します」という指定です。

偽の場合」にはLARGE関数が無視する空白を示す「””」を入力します。「「重複」列のセルが1でなかったら、空白を返します」という指定です。
IF関数で重複を除外する条件をダイアログで指定

IF関数の数式は「IF(($D$2:$D$11)=1,$C$2:$C$11,“”)

このIF関数の条件式をLARGE関数の「配列」にネストし、「順位」に「1」をセル参照で指定します。
IF関数の条件式をLARGE関数の「配列」にネストし、「順位」に「1」をセル参照で指定

重複データを除外して大きい方から3番目までの数値が取得できました。
重複データを除外して大きい方から3番目までの数値が取得できた

最終的な数式は「=LARGE(IF(($D$2:$D$11)=1,$C$2:$C$11,””),F2)」となります。

大きい方から3番目までの数値に条件付き書式で色を付ける

分かりやすくするためにLARGE関数で求めた上位3セルに色をつけたいと思います。これは条件付き書式で実装します。

条件でセルに色を付けたい範囲を選択して、「ホーム」→「スタイル」グループ→「条件付き書式」をクリックして、プルダウンメニューから「新しいルールを選択します。
「ホーム」→「スタイル」グループ→「条件付き書式」→「新しいルール」を選択

新しい書式のルール」ダイアログが開いたら、ルールの種類で「数式を使用して、書式設定するセルを設定」を選択し、画面中央の「次の数式を満たす場合に値を書式設定」のテキストボックスにLARGE関数の数式を「=C2>=LARGE($C$2:$C$11,3)」と入力します。「配列内のセルが、配列を検索して取得した大きい方から3番目の数値以上だったら」という条件の数式で、これを満たせばセルに書式設定した色が付くということです。

画面最下部の書式」ボタンをクリックして、背景の塗り潰しやフォントの色、サイズなど好きな書式を設定してから「OK」。
LARGE関数の数式を条件にした条件付き書式の新しい書式のルールを設定
大きい数値から3番目までのセルに色が付きました。
LARGE関数で指定した条件付き書式によって大きい数値から3番目までのセルに色が付いた

LARGE関数のまとめ
  • LARGEは数値的・客観的に「大きい」という意味で、主観的・感覚的に「大きい」Bigとは微妙に違いがあります
  • LARGE関数は指定された配列の範囲で降順(大きい方から)の順番を求める関数です
  • LARGE関数とは反対に、昇順(小さい方から)の順番を求めるのはSMALL関数です
  • LARGE関数で大きい方から○番目の数値データを取得する時、重複を除くにはCOUNTIF、IF関数と組み合わせます
  • LARGE関数で大きい方から○番目のセルに色を付けたければ、条件付き書式に数式を指定します
タイトルとURLをコピーしました