Excel（エクセル）のLARGE関数の使い方｜配列の中で何番目に大きい数値か求める
使用関数：LARGE、SMALL、IF、COUNTIF
LARGE（ラージ）関数とは？
LARGEは「大きい」を意味しますが、これは客観的、数値的な比較で、主観的、感覚的な「大きい」を意味するBIGとは微妙な違いがあります。
LARGEは指定された配列の範囲で大きい方からの順番を求める関数です。「降順」で位置を判定します。LARGEと反対に小さい方からの順番を求めるのはSMALL関数で、この2つの関数はセットで覚えた方がいいでしょう。
配列の中の数値を昇順で求めるSMALL関数の使い方を分かりやすく解説しています。
LARGE関数の引数と書式
「数式」タブ「関数ライブラリ」→「その他の関数」→「統計」→ LARGEをクリックします。
LARGE関数の引数は「配列」と「順位」です。
書式の構成はこうなります。
LARGE関数で大きい順にデータを並び替える
LARGE関数の引数ダイアログボックスで簡単な「降順の並べ替え」を実行してみましょう。
試験の結果をランダムに並べた表をLARGE関数を使って成績順位で並べ替えます。
※氏名の列にはあらかじめ=IFERROR(VLOOKUP(E2,$A$2:$B$16,2,0),””)を入力してあり、総合点が入ると自動で氏名も表示されます。
第一引数「配列」
LARGE関数の引数ダイアログを開き、「配列」に総合点データの列範囲をドラッグで指定します。
※LARGE関数は数値データを対象にする関数なので、「配列」内に空白、文字列、論理値が含まれていた場合は無視されます。
第二引数「順位」
配列の中で大きい方から何番目のセルを抽出するかを数値で指定します。「1」と指定した場合は最大値が返り、「2」と指定すれば大きい方から2番目のデータが返ります。
この例では「1」をセル参照で指定します。
成績順位1位のセルに配列の中の最大値が表示されました。この数式を下までオートフィルコピーして、大きい順序での並べ替えが完了しました。
LARGE関数に重複を除く条件を指定して大きい方から3番目までを取得
数値データの大きい方から○番目までをを取得したいという時、重複する値があると、最悪、同じ数値が並んでしまうようなことになります。
重複のセルをCOUNTIF関数で抽出
重複データを抽出するのには範囲から特定の条件に一致するセルをカウントするCOUNTIF関数を使います。「重複」という列項目を増やし、その最初のセルにCOUNTIFの数式を入れます。
引数「範囲」に数値データの一番上のセルを「$C$2:C2」というように指定します。
「C2セルからC2セルまで」ということですが、ポイントは最初のセル番地を絶対参照にすることです。
そうすることで、オートフィルコピーした時に一つずつ検索するセル範囲が増えていきます。
引数「検索条件」にも数値データの一番上のセルを指定、これは可変させる必要があるため相対参照のままでおきます。
数式は「=COUNTIF($C$2:C2,C2)」となります。これをデータの最終行までオートフィルコピーします。
重複するデータがあるのが「C9」セルなら、C2セルからC9セルまでが「範囲」で、C9の入力値が検索条件になり、「2」がカウントされます。
IF関数で重複を除く条件を指定し、LARGE関数で上から３番目までを取得
IF関数で重複を除外する条件式を組み立てます。
「論理式」にはCOUNTIFで求めた「重複」列のセル範囲を絶対参照で入力し、「=1」を付け加えます。「もし、「重複」列のセルが1だったら、」という条件です。
「真の場合」にはLARGE関数で検索する範囲、点数のデータが入ったセル範囲をドラッグで指定します。「「重複」列のセルが1だったら、この範囲を検索します」という指定です。
「偽の場合」にはLARGE関数が無視する空白を示す「””」を入力します。「「重複」列のセルが1でなかったら、空白を返します」という指定です。
IF関数の数式は「IF(($D$2:$D$11)=1,$C$2:$C$11,“”)」
このIF関数の条件式をLARGE関数の「配列」にネストし、「順位」に「1」をセル参照で指定します。
重複データを除外して大きい方から３番目までの数値が取得できました。
最終的な数式は「=LARGE(IF(($D$2:$D$11)=1,$C$2:$C$11,””),F2)」となります。
大きい方から３番目までの数値に条件付き書式で色を付ける
分かりやすくするためにLARGE関数で求めた上位３セルに色をつけたいと思います。これは条件付き書式で実装します。
条件でセルに色を付けたい範囲を選択して、「ホーム」→「スタイル」グループ→「条件付き書式」をクリックして、プルダウンメニューから「新しいルール」を選択します。
「新しい書式のルール」ダイアログが開いたら、ルールの種類で「数式を使用して、書式設定するセルを設定」を選択し、画面中央の「次の数式を満たす場合に値を書式設定」のテキストボックスにLARGE関数の数式を「=C2>=LARGE($C$2:$C$11,3)」と入力します。「配列内のセルが、配列を検索して取得した大きい方から３番目の数値以上だったら」という条件の数式で、これを満たせばセルに書式設定した色が付くということです。
画面最下部の「書式」ボタンをクリックして、背景の塗り潰しやフォントの色、サイズなど好きな書式を設定してから「OK」。
大きい数値から３番目までのセルに色が付きました。
- LARGEは数値的・客観的に「大きい」という意味で、主観的・感覚的に「大きい」Bigとは微妙に違いがあります
- LARGE関数は指定された配列の範囲で降順（大きい方から）の順番を求める関数です
- LARGE関数とは反対に、昇順（小さい方から）の順番を求めるのはSMALL関数です
- LARGE関数で大きい方から○番目の数値データを取得する時、重複を除くにはCOUNTIF、IF関数と組み合わせます
- LARGE関数で大きい方から○番目のセルに色を付けたければ、条件付き書式に数式を指定します