Excelで上位3件を抽出する方法！LARGEとINDEX_MATCH

Excelで売上や金額の上位3件だけを手早く抽出したいときはないでしょうか。

けど、そんな中で悩むことは、

・並べ替えを使わずに上位3件だけを取り出したい
・1位〜3位の商品名まで自動で表示させたい
・同じ金額があると、違う商品なのに同じ名前が出てしまう

ですよね。

今回はそんなお悩みを解決する

・Excelで上位3件を抽出する方法（LARGE関数＋INDEX/MATCH関数）

についてまとめます！

完成イメージ

上位3件を出したいときに並べ替えを使うと、元の並び順が崩れてあとで困ることがあります。
今回は
・LARGE関数で金額のベスト3を出し、INDEX/MATCH関数で商品名まで自動表示する方法
をゴールにします。
順位の数字を変えるだけで、金額と商品名が自動で追従するようになります。

上の画像が完成形です。金額の上位3件と商品名が自動で表示され、順位を変えても内容が追従します。

なお、同じ金額があるときの対処（作業列でタイブレーク）と、SMALL関数で下位3件を出す方法、うまくいかないときの対処法（トラブルシュート）もあわせてご紹介します。
早速実装して試してみましょう！

STEP1. 元データを用意する

まずは上位を取り出したい売上表を用意します。
A列が商品、B列が金額の、10件のデータです。
今回はこの表から「金額の上位3件」を取り出していきます。
D列から右は、結果を表示するエリアとして空けておきます。

下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。

商品	金額
ノートPC	128000
タブレット	98000
モニター	98000
プリンタ	58000
Webカメラ	29800
ハードディスク	12800
USBメモリ	9800
スピーカー	6800
キーボード	4800
マウス	2200

※ 数式で求める列は空にしています。すでに値が入っている列はそのまま残して試せます。

STEP2. LARGE関数で上位3件の金額を出す

まずは金額の上位3件を出します。
結果エリアの F3セル にLARGE関数を入力しましょう。

=LARGE($B$2:$B$11, D3)

第1引数が金額の範囲、第2引数が「何番目に大きいか」です。
D3には順位の「1」が入っているので、1番大きい金額が出ます。
あとはF5までコピーすれば、2位・3位の金額も自動で表示されます。
範囲を $B$2:$B$11 と絶対参照にするのがポイントです。
ここさえ押さえれば、あとはコピーするだけなのでカンタンですね。

数式はどのセルに入れる？

セルを選んで、数式バーにそのまま入力するだけです。
実際の入力画面がこちらです。
入力中は引数のヒントが表示されるので、順番を確認しながら進められます。

STEP3. INDEX/MATCHで商品名を出す

金額だけでは、どの商品が上位なのか分かりません。
次に商品名を自動で表示させます。
E3セルに次の式を入力します。

=INDEX($A$2:$A$11, MATCH(F3, $B$2:$B$11, 0))

MATCH関数で「F3の金額が何行目にあるか」を調べ、INDEX関数でその行の商品名を返しています。
最後の 0 は「完全一致で探す」という意味です。
これで1位の商品名が出ました。E5までコピーしておきましょう。

INDEXとMATCHの組み合わせ方

実際の入力画面です。
MATCHが「行番号」を返し、INDEXがその行の値を取り出す、という役割分担になっています。
2つの関数を1つの式にまとめるのがコツです。

同じ金額があると、商品名が重複する

今回のデータは、タブレットとモニターの金額がどちらも98,000円です。
そのため、2位も3位も「タブレット」と表示されてしまいました。
MATCH関数は最初に見つかった行を返すので、同じ金額が複数あるとこうなります。
このままだと、3位の商品が正しく分かりません。

STEP4. 同額があるときの対処（作業列でタイブレーク）

同額があっても正しく取り出すには、作業列を使います。
H列に「金額から行番号のごく小さい値を引いた数」を作り、順位付けに使います。
H2セルに次の式を入力し、H11までコピーします。

=B2-ROW()/100000

ROW()は行番号を返すので、同じ金額でも行ごとにほんの少しだけ値が変わります。
このわずかな差で、同額のときは上の行を優先できます。
表示形式を #,##0.00000 にすると、その差が見えるので確かめてみてください。
あとは商品名の式を、この作業列を見るように変えるだけです。

=INDEX($A$2:$A$11, MATCH(LARGE($H$2:$H$11, D3), $H$2:$H$11, 0))

すると3位に「モニター」が表示され、商品名の重複が解消されました。

STEP5. SMALL関数で下位3件を出す

下位（ワースト）3件を出したいときは、LARGEの代わりにSMALL関数を使います。
使い方は同じで、小さい順にn番目の値を返します。
下位3件エリアに次の式を入れてみましょう。

=SMALL($B$2:$B$11, D9)

=INDEX($A$2:$A$11, MATCH(SMALL($H$2:$H$11, D9), $H$2:$H$11, 0))

これで金額の下位3件が自動で表示されました。
上位を出したいのか下位を出したいのかで、LARGEとSMALLを選ぶだけです。

LARGE・SMALL・RANKの使い分け

似た役割の関数にRANK関数があります。
違いを整理しておきましょう。

関数	できること	向いている場面
LARGE	大きい方からn番目の値を返す	上位n件を抽出したい
SMALL	小さい方からn番目の値を返す	下位n件を抽出したい
RANK	ある値が全体の何位かを返す	全件に順位を振りたい

POINT

上位n件の値は =LARGE(範囲, n) で取り出せます。
商品名など別の列を取り出すときは =INDEX(商品列, MATCH(値, 金額列, 0)) と組み合わせます。
同じ金額があるときは作業列でわずかな差を付けて、順位を一意にするのがコツです。

VBAで上位3件を一括表示する

毎回式を入れるのが手間なときは、VBAでまとめて表示するのがおすすめです。
ボタン1つで上位3件を書き出せます。

Sub Top3()
    Dim i As Long, r As Long
    For i = 1 To 3
        r = i + 2
        Cells(r, 6).Formula = "=LARGE($B$2:$B$11," & i & ")"
        Cells(r, 5).Formula = "=INDEX($A$2:$A$11,MATCH(LARGE($H$2:$H$11," & i & "),$H$2:$H$11,0))"
    Next i
End Sub

タカヒロ

LARGEで金額を取り、INDEX/MATCHで名前を取り寄せるのが基本の型です。
同額があるときだけ作業列の出番なので、まずはシンプルな式から試してみてくださいね。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、思ったとおりに動かないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。

症状	原因	対処
2位と3位が同じ商品になる	金額が同額で、MATCHが最初の行だけを返す	作業列（=B2-ROW()/100000）で順位を一意にする
#N/A になる	検索する値が金額の範囲に見つからない	LARGE/SMALLの範囲とMATCHの範囲を同じにする
コピーしたら範囲がずれる	範囲が相対参照になっている	$B$2:$B$11のように絶対参照にする
金額がうまく拾えない	金額が文字列として入力されている	金額列を数値に変換する／=VALUE() で数値化する
作業列が同じ数に見える	表示形式で端数が丸められている	表示形式を #,##0.00000 にして差を確認する

関数の説明

今回使う4つの関数をまとめます。

LARGE関数（ラージ）

書式	=LARGE(配列, 順位)
できること	指定した範囲の中で、大きい方から数えてn番目の値を返します。
戻り値	n番目に大きい値（数値）
こんなときに	上位3件など、大きい順の値を取り出したいとき。
注意	返すのは値だけで、商品名などは取り出せません。

SMALL関数（スモール）

書式	=SMALL(配列, 順位)
できること	指定した範囲の中で、小さい方から数えてn番目の値を返します。
戻り値	n番目に小さい値（数値）
こんなときに	下位3件など、小さい順の値を取り出したいとき。
注意	LARGEの小さい版です。引数の順番はまったく同じです。

INDEX関数（インデックス）

書式	=INDEX(配列, 行番号, [列番号])
できること	範囲の中から、指定した行番号・列番号にある値を返します。
戻り値	指定した位置にある値
こんなときに	MATCHと組み合わせて、検索した行の別の列を取り出したいとき。
注意	行番号は範囲の先頭からの数えなので、シート上の行番号とは違います。

MATCH関数（マッチ）

書式	=MATCH(検索値, 範囲, [照合の種類])
できること	範囲の中で検索値が何番目にあるかを返します。
戻り値	範囲の先頭から数えた位置（数値）
こんなときに	INDEXと組み合わせて、行番号を調べたいとき。
注意	同額があると最初に見つかった位置を返します。0で完全一致になります。

=INDEX($A$2:$A$11, MATCH(LARGE($H$2:$H$11, D3), $H$2:$H$11, 0))

完成

上位3件の金額と商品名が、自動で表示されました。
3位には「モニター」が入り、同額でも商品名が重複していません。
順位の数字を変えれば、その順位の内容がすぐに切り替わります。
思ったとおりに動きましたか？
自分でデータを差し替えても、同じ式がそのまま使えます。

さいごに

いかがでしょうか。

今回は、

・Excelで上位3件を抽出する方法（LARGE関数＋INDEX/MATCH関数）

についてまとめました。

LARGEで金額を取り、INDEX/MATCHで商品名を取り寄せる、という流れがつかめれば十分です。
同額があるときの作業列も、一度覚えておくとランキング作成で役に立ちます。
まずは今回のサンプル表で、ぜひ一度試してみてください。
順位が自動で追従すると、集計やレポート作りがぐっとラクになりますよ。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。

ぜひ試してみてください。