Excel関数で上位を抽出する方法をお探しですね。
広告
Excelで「上位3つ」「2番目に大きい値」をサクッと取り出す方法
Excelで売上や点数、アクセス数なんかを集計していると、「最大値だけじゃなくて、2番目に大きい数字も知りたいな」とか「上位3つだけ別のセルに並べたい」って思うこと、ありますよね。
そんなときに使えるのが、**LARGE関数**と**SMALL関数**です。
MAX関数やMIN関数だと一番大きい値・一番小さい値しか分かりませんが、LARGE・SMALL関数を使えば、ランキングを作ったり、上位・下位のデータをパパッと抜き出したりできるんです。
この記事では、「上位3つまで取り出したい」「2番目に大きい値だけ知りたい」といった場面で役立つLARGE・SMALL関数の使い方を、初心者の方にも分かりやすく解説していきます。
LARGE・SMALL関数って何？「2番目に大きい値」が簡単に取れる便利な関数
**LARGE関数**は、指定したセル範囲の中から「大きい順で○番目の値」を教えてくれるExcel関数です。
たとえば、売上データがB2:B11に入っているとき、2番目に大きい売上を知りたければ「**=LARGE(B2:B11,2)**」と入力するだけ。
第1引数には対象の数値範囲を、第2引数には「何番目の値が欲しいか」を指定します。
1を入れれば最大値、2なら2番目、3なら3番目…という具合です。
逆に、**SMALL関数**はLARGE関数の反対で、「小さい順で○番目の値」を返してくれます。
作業時間が短い順に確認したいときや、在庫が少ない商品を見つけたいときに便利です。
B2:B11の中で2番目に小さい値を取るなら「**=SMALL(B2:B11,2)**」でOK。
LARGE関数とSMALL関数は書き方がほぼ同じなので、片方を覚えればもう片方も自然に使えるようになりますよ。
RANK関数との違いも押さえておこう
ちなみに、**RANK関数との違い**も知っておくと便利です。
– **RANK関数**：「この値が範囲内で何位か」を調べる
– **LARGE・SMALL関数**：「○位の値そのもの」を取り出す
つまり、「この商品の売上は何位？」ならRANK関数、「売上2位の金額はいくら？」ならLARGE関数という使い分けですね。
ランキング表を作るときは、この2つの関数を組み合わせると、すごく分かりやすい表が作れます。
LARGE関数で上位3つまでの値を取り出す基本のやり方
Excelで「上位3つまで」を抽出したいときは、LARGE関数の順位部分を1、2、3と変えて入力します。
たとえば、B2:B11に売上金額が入っているとしましょう。
1位の売上をD2、2位をD3、3位をD4に表示したい場合は、こんな感じで入力します。
– D2：`=LARGE($B$2:$B$11,1)`
– D3：`=LARGE($B$2:$B$11,2)`
– D4：`=LARGE($B$2:$B$11,3)`
これだけで、元のデータを並べ替えなくても、上位3つの数値がパッと取り出せます。
順位をセルで指定すると、もっと便利
実務でよく使われるのが、**順位をセルに入力しておいて、そのセルを参照する方法**です。
たとえば、C2:C4に「1、2、3」と順位を入力して、D2に「**=LARGE($B$2:$B$11,C2)**」と入力して下にコピーします。
こうしておけば、あとからC列の順位を変更するだけで、上位5位や上位10位にも簡単に対応できます。
**ポイント**：数式をコピーするときは、対象範囲を「**$B$2:$B$11**」のように絶対参照（$マーク付き）にしておくこと。
これをしないと、コピーしたときに参照範囲がずれて、おかしな結果になっちゃいます。
新しいExcelなら1つの数式で上位3つを一気に表示
Microsoft 365やExcel 2021以降を使っている人は、**SEQUENCE関数**と組み合わせると、もっとスマートに書けます。
たとえば「**=LARGE(B2:B11,SEQUENCE(3))**」と入力すると、上位1位から3位までの値が縦方向に自動で並びます（これを**スピル**といいます）。
古いExcelでは使えませんが、対応している環境なら、ランキング表の作成がかなり楽になりますよ。
上位3つの合計を出したいときは？
上位3つの合計を出したい場合は、「**=SUM(LARGE(B2:B11,{1,2,3}))**」のように指定できます。
ただ、Excelのバージョンによってはうまく動かないこともあるので、その場合は「**=LARGE(B2:B11,1)+LARGE(B2:B11,2)+LARGE(B2:B11,3)**」と分けて足してもOKです。
大事なのは、**LARGE関数が「順位に対応する値」を返してくれる関数だと理解すること**。
これさえ分かっていれば、上位3つだけじゃなく、上位10件、上位20％の境界値なんかにも応用できます。
2番目に大きい値・小さい値を取る具体例とSMALL関数の使い方
「**2番目に大きい値**」を取得する一番基本的な式は「**=LARGE(範囲,2)**」です。
たとえば、テストの点数がC2:C21に入っている場合、2番目に高い点数を求めるには「**=LARGE(C2:C21,2)**」と入力します。
最大値だけならMAX関数で十分ですが、2位や3位の値を知りたいときはLARGE関数が便利です。
営業成績、アクセス数、利益額、評価点など、数値の大きさで上位を判断するデータなら、幅広く使えますよ。
2番目に小さい値ならSMALL関数
「**2番目に小さい値**」を取りたい場合はSMALL関数を使って、「**=SMALL(範囲,2)**」と入力します。
たとえば、作業時間がD2:D15に入っていて、2番目に短い作業時間を知りたいなら「**=SMALL(D2:D15,2)**」です。
在庫数が少ない商品、納期が短い案件、コストが低い項目など、小さい値に注目したい場面ではSMALL関数が活躍します。
LARGE関数とSMALL関数はセットで覚えておくと、上位分析と下位分析の両方に対応できて便利です。
同じ値が複数あるときの注意点
ただし、**同じ値が複数ある場合の扱いには注意**が必要です。
たとえば、データが「100、90、90、80」のとき、「=LARGE(A1:A4,2)」の結果は90で、「=LARGE(A1:A4,3)」の結果も90になります。
Excelは同じ値を別々にカウントするので、重複を飛ばして「異なる値として2番目」を取りたい場合は、Microsoft 365なら**UNIQUE関数**と組み合わせて「**=LARGE(UNIQUE(A1:A4),2)**」のように指定します。
重複を順位に含めるのか、ユニークな値だけで判断するのかは、集計の目的に合わせて決めてくださいね。
空白セルや0の扱いにも気をつけよう
空白セルや文字列は基本的にLARGE関数・SMALL関数の計算対象から除外されますが、**数値の0は有効なデータとして扱われます**。
そのため、未入力を0で埋めている表では、SMALL関数で下位を抽出したときに0が返ることがあります。
0を除外したい場合は、**FILTER関数**が使える環境なら「**=SMALL(FILTER(B2:B20,B2:B20<>0),1)**」のように条件を付けると便利です。
データの入力ルールによって結果が変わるので、関数を使う前に「空白」「0」「文字列」が混在していないか確認しておくと、ミスを防げます。
商品名や担当者名も一緒に抽出する応用テクニックとエラー対策
LARGE関数やSMALL関数だけだと、取得できるのは順位に対応する「**値**」だけです。
たとえば売上2位の金額は分かっても、それがどの商品や担当者のものかまでは分かりません。
そこで実務では、**INDEX関数とMATCH関数を組み合わせて**、順位に対応する名称を取り出す方法がよく使われます。
商品名がA2:A11、売上がB2:B11にある場合、売上が2番目に大きい商品の名前を取得する式はこちら。
“`
=INDEX(A2:A11,MATCH(LARGE(B2:B11,2),B2:B11,0))
“`
この数式では、まずLARGE関数で売上2位の金額を求めて、MATCH関数でその金額がB列の何番目にあるかを探し、INDEX関数で同じ位置の商品名を返しています。
ランキング表を作るときは、金額だけじゃなく「誰が」「どの商品が」「どの店舗が」という情報も必要になることが多いので、この組み合わせはすごく実用的です。
ただし、**同じ売上金額が複数ある場合、MATCH関数は最初に見つかった位置を返します**。
重複が多いデータで正確に複数件を抽出したい場合は、SORTBY関数やFILTER関数、補助列を使った方法を検討するといいでしょう。
条件付きで上位を抽出したいときは？
条件付きで上位を抽出したい場合もありますよね。
たとえば、地域が「東京」のデータだけを対象にして、売上の上位3つを取得したいケースです。
Microsoft 365なら「**=LARGE(FILTER(B2:B20,C2:C20=”東京”),1)**」のように、FILTER関数で対象データを絞り込んでからLARGE関数を使えます。
従来のExcelでは配列数式として「**=LARGE(IF(C2:C20=”東京”,B2:B20),1)**」のように入力する方法がありますが、バージョンによって確定方法が違うので注意してください。
条件付き抽出を行う場合は、まず**対象範囲と条件範囲の行数をそろえること**が基本です。
よくあるエラーと対処法
最後に、LARGE関数・SMALL関数でよくあるエラーも確認しておきましょう。
**「#NUM!」エラー**が出る場合、指定した順位がデータ数を超えていたり、順位に0以下の数値を指定していることが原因です。
たとえば数値データが10個しかないのに「=LARGE(B2:B11,11)」と入力すると、11番目の値が存在しないのでエラーになります。
また、順位に文字列を指定した場合は「**#VALUE!**」エラーになることがあります。
入力ミスを防ぐには、こんな感じで**IF関数でチェック**する方法が有効です。
“`
=IF(C2<=COUNT($B$2:$B$11),LARGE($B$2:$B$11,C2),"順位が範囲外です")
```
さらに応用：条件付き書式と組み合わせる
LARGE関数とSMALL関数は、単に上位・下位の値を取り出すだけじゃなく、**条件付き書式と組み合わせて上位3つのセルに色を付ける**、ランキング表を自動更新する、異常値候補を見つけるといった使い方にも発展できます。
たとえば、売上範囲B2:B20で上位3つに色を付けたい場合は、条件付き書式の数式に「**=B2>=LARGE($B$2:$B$20,3)**」を設定します。
表の並び順を変えずに重要な値が見つけられるので、報告資料や確認作業の効率化にもつながりますよ。
まとめ
まずは次の3パターンを押さえましょう。
– `=LARGE(範囲,2)` → 2番目に大きい値
– `=LARGE(範囲,1〜3)` → 上位1〜3位の値
– `=SMALL(範囲,2)` → 2番目に小さい値
必要に応じて、INDEX/MATCHやFILTERへ広げていけば、Excelでの順位データ分析がグッと扱いやすくなります。
ぜひ試してみてくださいね！
広告
