関数を使わずに最小値・最大値を求める

2023年3月18日 20:50

はじめに

先日「minが効かない」という投稿をして、そのときはsmall関数で乗り切ったことを書きました。でも今回は、ワークシート関数を使わずに最大値・最小値を求める割と単純なvbaコードができたのでご紹介します。

最小値を求めるvbaコード

これです。

Function funcmin(ByVal arr As Variant) As Long
'funcmin：1次元の配列で、最小値を求める　long

Dim a As Long, b As Long, c As Long, d As Long, e As Long, f As Long, g As Long
Dim aa As Long, bb As Long, cc As Long, dd As Long, ee As Long, ff As Long, gg As Long

Dim chk1st() As Long

aa = UBound(arr, 1)
ReDim chk1st(1 To aa)
For a = 1 To aa
If a = 1 Then
chk1st(a) = arr(a)
Else
If chk1st(a - 1) <= arr(a) Then
chk1st(a) = chk1st(a - 1)
Else
chk1st(a) = arr(a)
End If
End If
Next a


funcmin = chk1st(aa)


End Function

変数のa,b,cはいつもコピペでつけているものなので気にしないでください。
読めばわかると思いますが、n番目の数値とその一つ前の数値を比べて、小さい方を採用、それを最後まで繰り返して、最も小さい数字が残る、というものです。
最大値は逆にすればいい。

If chk1st(a - 1) <= arr(a) Then
chk1st(a) = arr(a)
Else
chk1st(a) = chk1st(a - 1)
End If

そして最後の要素の数字を解とします。

MIN関数の限界は？

１回目の投稿でも書きましたが、sum,min,max,small,matchなどのワークシート関数は要素数で限界があります。それがどこにあるのか、以下のコードで探ることとしました。
まずは一定範囲の整数で乱数を発生させるコードをつくります。この書き方は検索すればすぐに見つかります。

Function random_number(ByVal maxnum As Long, ByVal minnum As Long) As Long

'random_number:乱数
'https://uxmilk.jp/48336
'ある範囲の乱数　Int（（最大値－最小値＋１）＊ Rnd +最小値））

Dim a As Long, b As Long, c As Long, d As Long, e As Long, f As Long, g As Long
Dim aa As Long, bb As Long, cc As Long, dd As Long, ee As Long, ff As Long, gg As Long

dd = maxnum - minnum + 1

random_number = Int((dd * Rnd + minnum))

End Function

次に要素数を1万から10万まで10回増やす間のどこに限界があるか、以下のコードで調べてみました。ここでは、例えば a=5 とすれば配列の要素数は50000となります。その50000個の配列の各要素に100から10万の間で乱数を発生させて入れます（それを chk1st 配列とします）。次にchk1st配列の最小値をワークシート関数と私が作成したvbaのfunctionで調べて２つが同じあれば、その要素数を chk2nd 配列に入れる、というものです。最終的に、chk2nd配列の最大値を計算しています。
なお、module10というのは私がいつも使っている関数を集めたモジュールの番号なので適当に変えてください。

Sub test230318()
Dim a As Long, b As Long, c As Long, d As Long, e As Long, f As Long, g As Long
Dim aa As Long, bb As Long, cc As Long, dd As Long, ee As Long, ff As Long, gg As Long
Dim chk1st() As Long, chk2nd() As Long

aa = 10
ReDim chk2nd(1 To aa)
For a = 1 To aa
bb = a * 10000
ReDim chk1st(1 To bb)
For b = 1 To bb
chk1st(b) = Module10.random_number(100000, 100)
Next b
cc = Application.WorksheetFunction.Min(chk1st)
dd = Module10.funcmin(chk1st)
If cc = dd Then
chk2nd(a) = bb
Else
chk2nd(a) = 0
End If
Next a
Debug.Print aa * 10000
Debug.Print Module10.funcmax(chk2nd)

End Sub

これでやってみると、60,000が最大値になります。次に60,000　から 70,000の間で1000ごとに計算させて限界値を探ります。aa　と　bb　を少し変えるだけです。

Sub test230318()
Dim a As Long, b As Long, c As Long, d As Long, e As Long, f As Long, g As Long
Dim aa As Long, bb As Long, cc As Long, dd As Long, ee As Long, ff As Long, gg As Long
Dim chk1st() As Long, chk2nd() As Long

aa = 10
ReDim chk2nd(1 To aa)
For a = 1 To aa
bb = 60000 + a * 1000
ReDim chk1st(1 To bb)
For b = 1 To bb
chk1st(b) = Module10.random_number(100000, 100)
Next b
cc = Application.WorksheetFunction.Min(chk1st)
dd = Module10.funcmin(chk1st)
If cc = dd Then
chk2nd(a) = bb
Else
chk2nd(a) = 0
End If
Next a
Debug.Print aa * 10000
Debug.Print Module10.funcmax(chk2nd)

End Sub

これからmin関数の要素数の限界値は 65,000 だとわかりました。

終わりに

やってみて気づきましたが、このコードで、aa=100　でやってみるとうまく結果がでません。数が大きくなりすぎるのか、挙動が謎です。最初のaaは10ぐらいがよさそうです（あくまでもこのコードの場合）。
この限界値の65,000ですが、sumでも同じぐらいだったと思います。おそらくこれがワークシート関数の限界なのかも。smallはもっと小さいと思います。
要素数が65000個の配列とかありえない！と思う方もいるかもしれませんが、10万でも100×1000ですから、2次元配列、３次元配列を計算するとなるとすぐにその程度の要素数になります。だからワークシート関数の限界を知りつつ、別途vbaで解決するfunctionをつくっておくことが大事だと思っています。