最大値や最小値を調べる

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

VBAで、最大値や最小値を調べる方法を解説します。最大値にしても最小値にしても、考え方はほとんど同じなので、とりあえず"最大値"で話を進めます。なお、最後の解説では、両者でちょこっと考え方が変わります。

関数を使う

下図のように、複数の数値が「セルに入力されていた」とします。

実は、このように調査対象の数値たちが「セルに入力されている」というのが、最も簡単です。セルに入力されていない数値たちを扱う方法も下記に解説しますが、そうなると難易度が上がります。さて、セルに入力されている数値だったら、関数を使えば一発です。とはいえ、VBAには最大値を求めるMAX関数や、最小値を求めるMIN関数がありません。じゃ、どーするかというと、セルの中で使う"ワークシート関数"のMAX関数をVBAの中で使ってやります。

Sub Macro1()
    MsgBox WorksheetFunction.Max(Range("A1:A9"))
End Sub

最小値だったら。

Sub Macro2()
    MsgBox WorksheetFunction.Min(Range("A1:A9"))
End Sub

○番目に大きい数値だったら。

Sub Macro3()
    Dim A As String, i As Long
    For i = 1 To 3
        A = A & WorksheetFunction.Large(Range("A1:A9"), i) & vbCrLf
    Next i
    MsgBox A
End Sub

データの範囲(ここではセルA1:A9)が可変だったら。

Sub Macro4()
    MsgBox WorksheetFunction.Max(Range("A1").CurrentRegion)
End Sub

とか、

Sub Macro5()
    MsgBox WorksheetFunction.Max(Range(Range("A1"), Range("A1").End(xlDown)))
End Sub

みたく、何とかしてください。最後のMacro5で使っている「Range(Range("A1"), Range("A1").End(xlDown))」の意味が分からない方は、下記動画をご覧ください。実務では必須です。

また、ここで使っているWorksheetFunctionに関しては、下記動画で詳しく解説していますので、よく分からないという方は、ぜひご覧ください。

いずれにしても、最大値を求める調査対象の数値たちが「セルに入力されている」のでしたら、このようにワークシート関数を使うのが簡単です。

配列に入れる

では、次のケースを考えてみましょう。

このB列に入力されている数値の中で「A列の日付が日曜日である最大値」を求めてみます。言ってる意味分かりますよね？ちなみに正解はセルB8の「91」です。これ、もしA列に入力されているのが、"田中"や"広瀬"などの文字列だったら、MAXIFS関数で調べられます。

MAXIFS関数も、VBAの中ではWorksheetFunctionで呼び出せるのですが、これはExcel 2019で追加された関数なので、Excel 2019以降か最新のProPlusでないと使えません。さらに言えば、今回のようにセルに入力されている日付の「曜日」を条件にする場合は、MAXIFS関数でも調べることはできません。

セル範囲B1:B9に入力されている全部の数値を対象とすることはできませんので、A列の日付が日曜日である「90」「79」「71」「91」という4つの数値からなる配列を作ってやります。実は、MAX関数などでは、引数に"セル範囲"だけでなく"配列"を指定することもできます。ちょっと、やってみましょう。

Sub Macro6()
    Dim A(3) As Long
    A(0) = 90
    A(1) = 79
    A(2) = 71
    A(3) = 91
    MsgBox WorksheetFunction.Max(A)
End Sub

しかし、A列に入力されている日付のうち、日曜日が毎回必ず4つとは限りません。配列の要素数は決まっていないんです。そうなると、配列の中でも動的配列を使うことになります。動的配列って何それ？美味しいの？という方は、下記動画を見てください。メチャクチャ詳しく解説しています。

今回でしたら、次のようにします。

Sub Macro7()
    Dim A() As Long, i As Long, N As Long
    On Error Resume Next
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Format(Cells(i, 1), "aaa") = "日" Then
            N = UBound(A) + 1
            ReDim Preserve A(N)
            A(N) = Cells(i, 2)
        End If
    Next i
    MsgBox WorksheetFunction.Max(A)
End Sub

このコードの意味も、上記の動画で解説しています。

仕組みを自分で作る

調査対象の数値たちがセルに入力されていたら簡単です。でも、そうではないケースも、実務ではよくあります。むしろ、よくあります。そうなったら、該当する数値だけの動的配列を作るのですが、VBAの中で最も多くの人を悩ませているであろう配列の、さらに難易度がアップした動的配列を使うなんて、考えただけでも恐ろしいですね。そこで最後に、どんなケースにでも対応できる、かつ配列なんて使わない方法をご紹介します。

プログラミングの世界には「車輪の再発明」という言葉があります。本意はともかくとして、一般的には次のような意味で使われることが多いようです。

  • 広く受け入れられ確立されている技術や解決法を(知らずに、または意図的に無視して)再び一から作ること
  • 誰かがすでに生み出した何かを自分で生み出そうとして時間を無駄にすること
(Wikipediaより)

個人的な感想ですが、この言葉は「それは車輪の再発明だよ、そんな無駄はよくないよ」みたくネガティブな意味で使われることが多いように感じます。たとえばExcelだったら、セル内の数値を合計するSUM関数があります。

このように便利な"車輪"があるのに、自分で同じ"車輪"を作ることは無駄です。

Function 合計(Target As Range) As Long
    Dim C As Range, A As Long
    For Each C In Target
        If IsNumeric(C) Then A = A + C
    Next C
    合計 = A
End Function

これだったら私も「そんなFunction自作しないで、SUM関数使えばいいやん！」って思います。しかし、重要なことは、このFunctionを「作れるか？」だと私は考えています。確かに、すでに存在しているのと同じ"車輪"を作ることは無駄です。しかし「作ろうと思えば作れる。でも、無駄だから既存の"車輪"を使う」という状態というか、スキルレベルというか。それが、優れたプログラマなのではないかと、私は考えています。もちろん、大規模な仕組みを最初から作ることは現実的に難しいでしょう。でも、数値を合計したり、最大値を求めるような、ちょっとした仕組みだったら「まぁ、いざとなったら作れるけどね」ってのが理想ではないかと。だって、世の中に存在する"車輪"が、どんなときでも自分のケースにマッチするとは限らないでしょ。ほんの少しだけ違う処理をしたい。でも既存の"車輪"では、それができない。そういうことって、実務のプログラミングでは日常茶飯事です。そんなとき「しょうがねぇ～な、既存の"車輪"とはちょっと違う新しい"車輪"を作っちゃえ」という技術と発想が重要だと、私はそう考えています。何でもかんでも、どんなときも、何かと"車輪"を探して利用するだけではプログラミングはできませんよ。

前置きが長くなりましたが、最大値を求める仕組みを自作しちゃいましょう。最大値とは、複数の数値の中で最も大きい数値のことです。これは、次のように考えれば求められます。ここでは、話を簡単にするため「1」「3」「2」という3つの中で、最も大きい数値を調べてみます。

まず、1つ変数を用意します。特に深い意味はありませんが、Aという名前にしました。ここ数年、短い1文字の変数名を使うのがマイブームなものでしてｗ

数値を入れる変数ですから、Long型(長整数型)としました。Long型の変数は、宣言した直後は0が入っています。言い換えれば、変数Aの初期値は0です。

ここから、3つの数値を順番に取り出して、次のような操作をします。「もし、変数に入っている値より数値の方が大きかったら、その数値を変数の中に代入する」と。最初の「1」は、変数Aの初期値である「0」より大きいです。したがって、変数Aには「1」が代入されます。

次の数値「3」に対しても同じ「もし、変数に入っている値より数値の方が大きかったら、その数値を変数の中に代入する」を実行します。今度の数値「3」は、現在変数Aに入っている「1」より大きいです。なので、この「3」を変数Aに代入します。変数Aの値は「3」に変わりました。

次の数値「2」も同じです。「もし、変数に入っている値より数値の方が大きかったら、その数値を変数の中に代入する」ですが、今度の数値「2」は現在変数Aに入っている「3」より大きくないです。だから、変数Aに「2」は代入しません。

この「もし、変数に入っている値より数値の方が大きかったら、その数値を変数の中に代入する」という操作を数値の数だけ繰り返し、最後に変数の中に残っている数値が"最大値"です。こうしたやり方やロジックのことを、一般的に「アルゴリズム」などと呼びます。さて、先のデータでやってみましょう。まずは、B列に入力されているすべての数値から最大値を求めてみます。

Sub Macro8()
    Dim i As Long, A As Long
    For i = 1 To 9
        If A < Cells(i, 2) Then A = Cells(i, 2)
    Next i
    MsgBox A
End Sub

今回、調べる対象は「B列のすべての数値」ではなく「A列の日付が日曜日である数値」だけです。なので、最後に条件分岐を加えます。

Sub Macro9()
    Dim i As Long, A As Long
    For i = 1 To 9
        If Format(Cells(i, 1), "aaa") = "日" Then
            If A < Cells(i, 2) Then A = Cells(i, 2)
        End If
    Next i
    MsgBox A
End Sub

いかがですか？先の動的配列を使ったコードと見比べてください。こちらの方が簡単だし、何ならさらにカスタマイズできませんか？こうした、いわゆる"力わざ"は、ぜひできるようになってください。そして、常に与えられた"車輪"だけに頼るのではなく、自分で"車輪"を作るという発想を忘れないでくださいね。

最小値を求めるときは注意

この発想で最大値を求めることができました。ただし、同じ考え方で最小値を求めるときは注意が必要です。なぜ注意が必要なのか、実際にやってみます。

さっきと同じようにLong型の変数Aを用意します。変数Aの中には最初0が入っています。今回の最小値は、2つめの「1」です。まずは最初の数値に対して「もし、変数に入っている値より数値の方が小さかったら、その数値を変数の中に代入する」を実行します。現在の変数Aには0が入っています。最初の数値「3」は変数Aの0より小さくないです。なので変数Aに「3」を代入しません。変数Aの中は0のままです。

次の数値「1」に対しても「もし、変数に入っている値より数値の方が小さかったら、その数値を変数の中に代入する」とします。変数Aの0と比べて今度の数値「1」も小さくありません。あれ？最小値であるはずの「1」が変数に入りませんでした。

念のため、最後の「2」もやってみます。もちろん、現在の変数Aに入っている0よりと比較して「2」は小さくありませんから、変数には代入しません。

変数Aは0のままでした。本当にそうなるのか、実際のセルでも試してみましょう。

Sub Macro10()
    Dim i As Long, A As Long
    For i = 1 To 9
        If A > Cells(i, 2) Then A = Cells(i, 2)
    Next i
    MsgBox A
End Sub

最大値を求めるときと、最小値を求めるときで、何が異なっているのか分かりますか？最大値を見つけるときは、調査対象の数値たちである「1」「3」「2」と、変数の初期値である「0」という全部で4つの数値の中で"最も大きい数値"を探しました。このとき、変数の初期値「0」を意識する必要はありません。変数の初期値「0」が最後まで変数内に残るのは、調査対象の数値たちが"すべて「0」のとき"だけです。どっちにしろ「0」なのですから問題ありません。

しかし、最小値となると話は変わってきます。同じように、調査対象の数値たち「3」「1」「2」と、変数の初期値「0」という全部で4つの数値の中で"最も小さい数値"を探したのですから、それは変数の初期値「0」だと判断されます。

したがって、この考え方で最小値を見つけるときは、調査を始める前に、変数の値を「実際に見つけたい最小値(ここでは「1」)よりも大きい値」に、しておかなければならないのです。といっても、その「実際に見つけたい最小値」を探そうというのですから、事前に分かっているはずはありません。なので、こういうケースでは、正確にやるのなら「調査対象の数値たちの中で最も大きい数値(最大値)」を変数に入れてやります。あるいは、簡便にやるのなら「あり得ない大きい数値」を入れてやります。たとえば、こんな感じです。

Sub Macro11()
    Dim i As Long, A As Long
    A = 1000
    For i = 1 To 9
        If A > Cells(i, 2) Then A = Cells(i, 2)
    Next i
    MsgBox A
End Sub

正直このへんは、ケースバイケースです。また、今回は調査対象の数値たちが、すべて「正の数」でしたが、調査対象に「負の数」が含まれていたらどうするか、なども検討すべきでしょう。いずれにしても、マクロの中で、変数の値がどのように変化するかをイメージするのがポイントです。