売上表から「2番目に大きい金額」や「下から2番目の金額」を取り出したいことがあります。結論から言うと、LARGE関数は=LARGE(範囲,順位)で大きいほうから、SMALL関数は=SMALL(範囲,順位)で小さいほうから、指定した順位の値を取り出す関数です。この記事では2つの関数の基本と、上位の合計や名前の取り出し方を解説します。
目次
LARGE関数・SMALL関数の基本構文
次のような支店別の売上表（A1:B5）を例に見ていきます。
| A（支店） | B（売上） | |
|---|---|---|
| 2 | 東京 | 320,000 |
| 3 | 大阪 | 280,000 |
| 4 | 名古屋 | 195,000 |
| 5 | 福岡 | 150,000 |
A列=支店、B列=売上という対応を踏まえて、次のように書きます。
| 取り出したいもの | 数式 | 結果 |
|---|---|---|
| 売上が1番大きい金額 | =LARGE(B2:B5,1) |
320,000（東京） |
| 売上が2番目に大きい金額 | =LARGE(B2:B5,2) |
280,000（大阪） |
| 売上が1番小さい金額 | =SMALL(B2:B5,1) |
150,000（福岡） |
| 売上が2番目に小さい金額 | =SMALL(B2:B5,2) |
195,000（名古屋） |
順位に1を指定したLARGEは最大値、SMALLは最小値と同じ結果になります。
LARGE関数で上位の合計や支店名を取り出す応用
上位3支店の売上合計は、順位を{1,2,3}のようにまとめて指定し、SUM関数で合計します。
=SUM(LARGE(B2:B5,{1,2,3}))B列（売上）の上位3つは320,000・280,000・195,000なので、合計の「795,000」が返ります。SUM関数の基本はSUM関数の基本的な使い方｜合計を計算する方法で解説しています。
1位の支店名を取り出すには、LARGEで求めた金額をMATCHで探し、INDEXでA列（支店）の値を返します。
=INDEX($A$2:$A$5,MATCH(LARGE($B$2:$B$5,1),$B$2:$B$5,0))最大の320,000はB列の1行目にあるので、「東京」が返ります。INDEXとMATCHの組み合わせ方はINDEXとMATCHを組み合わせて柔軟に検索する方法を参照してください。
LARGE関数・SMALL関数のよくある注意点
- 順位がデータの個数より大きいと#NUM!エラーになる: 上の表はデータが4件なので、
=LARGE(B2:B5,5)は#NUM!になります。順位に0以下を指定した場合も同じです。対処法は#NUM!エラーの原因と対処法で解説しています。 - 同じ値があると、同じ金額が続けて返る: たとえば売上が320,000・280,000・280,000・150,000なら、
LARGE(範囲,2)もLARGE(範囲,3)も280,000を返します。INDEXとMATCHで支店名を取り出すと、どちらも上にある行の支店名になる点に注意しましょう。 - 順位そのものを付けたいときはRANK関数: 「この支店は何位か」を表示したい場合は、RANK関数で同順位を考慮した順位付けをする方法で紹介しているRANK関数が向いています。
LARGE関数・SMALL関数が登場する記事
LARGE関数・SMALL関数の使い方まとめ
LARGE関数は大きいほうから、SMALL関数は小さいほうから、指定した順位の値を取り出します。SUMと組み合わせれば上位の合計、INDEXとMATCHと組み合わせれば順位に対応する名前も取り出せます。データの真ん中の値（中央値）を条件付きで求めたい場合は、サイトの人気記事エクセル関数で条件付きで中央値（MEDIAN）を計算する方法もあわせてご覧ください。