大きい/小さい方からn番目の値を求めよう　～LARGE関数・SMALL関数～

time 2018/02/26 甘党

※解説記事内の画像はExcel 2010のものです。

こんにちは、甘党です。
私の好きな飲み物TOP3に入る白バラコーヒーという飲み物があります。
出身地ではどこのスーパーでも置いてある飲み物なのですが、東京では中々置いておらず最近悲しい思いをしております。

今のところ成城石井に置かれていることは判明しているので、売上に貢献してまいります。
今後、大人気となって白バラコーヒーがどこでも買える社会になることを願っております。

さて、皆さんはデータを集計する際に「最大値の次に大きい値ってどれだろう？」や「3番目に小さい値ってどれだろう？」と思うことはありませんか？
最大値や最小値を求める場合は「MAX関数」や「MIN関数」が使えますが、2番目、3番目など最大値や最小値以外の値を求めたい場合には不便ですよね。
そんな時に役立つのが「LARGE関数」と「SMALL関数」です。

今回はそんな「LARGE関数」と「SMALL関数」をご紹介したいと思います。
それでは、さっそく関数の説明に入りたいと思います。

LARGE関数で最大値からn番目の値を求める

下の表を例に、関数の説明をしたいと思います。
この表では月別での各製品の売り上げを示しております。
LS21
では、LARGE関数を使って各月の2番目に大きい値を求めたいと思います。
LARGE関数の使い方ですが、
　　=LARGE(配列, 順位)
このように指定することで、指定した配列内の最大値から任意の順位の値を求めることができます。
上の表のC8セルに“=LARGE(C2:C5, 2)”と入力し関数を伸ばすと、
各月での最大値から2番目の値が表示されます。
LS22
以上がLARGE関数の使い方になります。
続きまして、SMALL関数の使い方の説明に移ります。

SMALL関数で最小値からn番目の値を求める

次はSMALL関数を使って最小値から2番目の値を求めます。
SMALL関数もLARGE関数と同じ使い方をします。
　　=SMALL(配列, 順位)
では、表のC9セルに“=SMALL(C2:C5, 2)”と入力すると関数を伸ばすと、
各月での最小値から2番目の値が表示されます。
LS13

おまけ　～LARGE関数/SMALL関数で並び替えを行う～

並べ替えを行う際、多くの人はリボンメニューにある“並べ替えとフィルター”から昇順もしくは降順を選択して並べ替えを行うかと思います。
この並べ替えをLARGE関数・SMALL関数を使って行ってみます。
これらの関数を使って並べ替えることの利点として、
複数列に存在する値を1列に大きい順、もしくは小さい順に並べることもできます。
では早速下の表で関数を使って並べ替えを行います。
LS14
LARGE関数やSMALL関数を使って並べ替えを行うには、引数にROW関数を使うと便利です。
ROW関数については下記のリンクをご覧ください。
　　　表に行番号と列番号を関数でふってみよう～ROW関数、COLUMN関数
H2セルに関数を入力します。今回は大きい順に並べ替えを行うためLARGE関数を使います。
小さい順に並べ替える際はSMALL関数を使ってください。
ではH2セルに
　　=LARGE( $C$2:$E$5, ROW()-1 )
と入力してください。
LS15
一番大きい値の“510”が返ってきました。
下まで関数を伸ばすと、
LS14
1列に降順で並べ替えることができました。

また、ROW関数ではなくCOLUMN関数を使うことで横に並べ替えすることも可能です。
ROW関数を使った時と同じように、下の表にK2セルに
　　=LARGE( $C$2:$E$5, COLUMN()-10 )
と入力し、関数を伸ばすと
LS17
横1列に並べ替えることができました。
このようにLARGE関数やSMALL関数と他の関数を組み合わせて使うことで、作業の効率化や手動によるミス防止につながるかもしれません。