Excelで最大値・最小値を求める方法！MAX・MIN・LARGE・SMALLの使い分けも！

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

ですよね。

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

についてまとめます！

完成イメージ

最大値や最小値を目で探していると、見落としや勘違いが起きやすく、件数が増えるほど大変になります。
今回は
・MAX・MIN・LARGE・SMALL・MAXIFSを使って最大値・最小値を自動で求める方法
をゴールにします。
「組ごとの最高点」や「欠席の0点を除いた最低点」まで、数式だけで出せるようになります。

なお、最高得点の氏名を出すINDEX/MATCHの使い方や、うまくいかないときの対処法（トラブルシュート）、VBAでまとめて求める方法もあわせてご紹介します。
早速実装して試してみましょう！

上の画像が完成形です。最大値・最小値・上位3位・下位3位・組ごとの最高点まで、数式だけで一覧にまとめています。

STEP1. テスト結果表を用意する

まずは集計したいテスト結果表を用意します。
今回は10名分のテスト結果を使います。
A列が氏名、B列が組、C列が点数です。
山本さんは欠席のため、点数は0点になっています。
右側には、これから結果を書き出すための欄を用意しました。
この表から、最大値と最小値を求めていきましょう。

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

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

STEP2. MAX関数とMIN関数で最大値・最小値を求める

まずは基本のMAX関数とMIN関数です。
結果を出したいセルに、次のように入力します。

=MAX(C2:C11)（最大値）
=MIN(C2:C11)（最小値）

最高点は95点、最低点は0点になりました。
「あれ、0点？」と気づいた方はするどいです。
これは山本さんが欠席のためで、本当の最低点ではありません。
0を除いた最低点の求め方はSTEP6でご紹介します。
範囲を指定するだけなのでカンタンですね。

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

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

STEP3. LARGE関数とSMALL関数で上位・下位n番目を求める

「2番目に高い点数は？」「ワースト3は？」を知りたいときはLARGE関数とSMALL関数を使います。
LARGEは大きい方から数えてn番目、SMALLは小さい方から数えてn番目の値を返します。
順位は次のように指定します。

=LARGE(C2:C11,1)（1位）→ 95
=LARGE(C2:C11,2)（2位）→ 92
=LARGE(C2:C11,3)（3位）→ 92
=SMALL(C2:C11,1)（最下位）→ 0
=SMALL(C2:C11,2)（下から2番目）→ 65
=SMALL(C2:C11,3)（下から3番目）→ 71

2位と3位がどちらも92点で並んでいるのがわかります。
順位の数字を変えるだけで、上位何位でも取り出せるので便利ですね。
SMALLの1番目は0点（欠席）なので、こちらも0に引っ張られます。

LARGE関数の入力画面

LARGE関数も、数式バーから入力できます。
第2引数の「順位」に 2 を指定すると、大きい方から2番目の値が返ります。

STEP4. INDEX/MATCHで最高得点の氏名を求める

点数だけわかっても、「誰が」最高点なのか知りたいときがあります。
そんなときはINDEX関数とMATCH関数を組み合わせます。

=INDEX($A$2:$A$11, MATCH(MAX($C$2:$C$11), $C$2:$C$11, 0))

やっていることは2段階です。
まずMATCHで「最大値が上から何番目にあるか」を調べます。
その番号をINDEXに渡して、氏名の列から同じ位置の名前を取り出しています。
結果は渡辺になりました。
最高得点の人が自動で表示されるので、確認作業がぐっとラクになります。

STEP5. 条件付き（MAXIFS）で組ごとの最高点を求める

「1組の中での最高点」のように、条件を付けて最大値を出すときはMAXIFS関数を使います。
書き方は =MAXIFS(最大値を求める範囲, 条件の範囲, 条件) です。

=MAXIFS(C2:C11, B2:B11, “1組”) → 95
=MAXIFS(C2:C11, B2:B11, “2組”) → 92

1組は95点、2組は92点と、組ごとの最高点が一発で出ました。
条件に合うデータだけを見てくれるので、いちいち並べ替えをしなくて済みます。

STEP6. 0（欠席）を無視して最小値を求める

STEP2で見たとおり、MIN関数やSMALL関数は欠席の0点もいっしょに拾ってしまいます。
0を除いた本当の最低点を出したいときは、MINIFS関数に条件を付けます。

=MINIFS(C2:C11, C2:C11, “>0”)

「C2:C11の中で0より大きいもの」だけを対象にする、という意味です。
結果は65点（田中さん）になりました。
MINIFSが使えない古いExcelでは、=SMALL(C2:C11, COUNTIF(C2:C11,0)+1) でも同じ結果になります。
0の件数だけ順位をずらす、という考え方です。
欠席を0で入力している現場では、ぜひ覚えておきたいテクニックですね。

使う関数の比較

今回登場した関数を表にまとめました。
出したいものに合わせて選ぶと迷いません。

VBAで最大値・最小値をまとめて求める

毎回セルに数式を入れるのが手間なときは、VBAでまとめて求めるのもおすすめです。
Alt+F11でVBAエディターを開き、標準モジュールに次のコードを貼り付けます。

Sub MaxMinSummary()
    Dim rng As Range
    Set rng = Range("C2:C11")
    Range("F2").Value = Application.WorksheetFunction.Max(rng)
    Range("F3").Value = Application.WorksheetFunction.Min(rng)
    Range("F4").Value = Application.WorksheetFunction.Large(rng, 1)
    Range("F5").Value = Application.WorksheetFunction.Large(rng, 2)
    Range("I2").Value = Application.WorksheetFunction.Small(rng, 2)
End Sub

実行すると、点数列から最大値・最小値・上位・下位がまとめてセルに入ります。
ボタンに登録しておけば、クリック1つで最新の結果に更新できます。

タカヒロ

VBAでは WorksheetFunction.Max(範囲) のように書きます。
SmallやLargeの第2引数に順位を渡せば、下位・上位も取り出せますよ。
数式を消したくないセルでは、表示用のセルに分けて書き出すのがおすすめです。

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

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

関数の説明

今回使う関数の概要です。
どれも集計の基本になるので、違いを押さえておきましょう。

MAX関数（マックス）

=MAX(C2:C11)

MIN関数（ミン）

=MIN(C2:C11)

LARGE関数（ラージ）

=LARGE(C2:C11, 2)

SMALL関数（スモール）

=SMALL(C2:C11, 2)

MAXIFS関数・MINIFS関数（マックスイフス／ミンイフス）

=MAXIFS(C2:C11, B2:B11, "1組")

完成

最大値・最小値から上位3位、下位3位、組ごとの最高点まで、すべて数式で出せるようになりました。
思ったとおりに動きましたか？
目で探していた作業がなくなるので、確認ミスも減らせます。
点数を書き換えれば、結果は自動で最新に更新されますよ。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

まずは今回のサンプル表で、ぜひ一度試してみてください。
最大・最小はMAXとMIN、順位ごとならLARGEとSMALL、条件付きならMAXIFSとMINIFSと使い分けてみてくださいね。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。

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