ワークシート関数のLARGE関数とSMALL関数
指定セル範囲の最大値や最小値はMAX関数やMIN関数で求められます。
その最大値や最小値の次点以降の、上から２番目や下から３番目などの値はLARGE関数とSMALL関数で取得します。
VBAから呼び出す場合はWorksheetFunctionプロパティを使う方法と、Evaluateメソッドを使う方法がありますが、ここではEvaluateメソッドでの方法を紹介します。
なお、WorksheetFunctionプロパティとEvaluateメソッドの利用方法については以下をご参照ください。
上から２番目と下から３番目の値を取得するサンプル
以下のようなセルの入力がある場合に、B列の値から上から２番目と下から３番目の値を取得するサンプルです。
ここでは分かりやすいように最初から７行目でB列の2行目から11行目を範囲選択をしています。
あとは選択範囲のアドレス（B1:B12）を取得し、それを以降のLARGE関数とSMALL関数に渡しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Sub LargeSmallTest() Dim r As Range '// 選択セル範囲 Dim sAddress As String '// 選択範囲アドレス Dim ret '// 戻り値 '// 範囲選択 Range("B2:B11").Select '// 選択範囲を取得 Set r = Selection '// 選択範囲のアドレスを取得（"B1:B12"） sAddress = r.Address(False, False) '// 上から２番目の値を取得（LARGE(B1:B12,2)） ret = Application.Evaluate("LARGE(" & sAddress & ", 2)") Debug.Print "上から２番目=" & ret '// 下から３番目の値を取得（SMALL(B1:B12,3)） ret = Application.Evaluate("SMALL(" & sAddress & ", 3)") Debug.Print "下から３番目=" & ret End Sub |
実行結果
上から２番目=11
下から３番目=2
戻り値にエラー2015が返ってくる場合
Evaluateメソッドを使うとよく遭遇するエラーが、戻り値にエラー2015を返される場合です。
この原因は大抵の場合、ワークシート関数に渡す引数の書き方が良くないのが原因です。
その中でも多いのが、ダブルクォーテーションを必要ないのにつけているケースです。
例えばLARGE関数ですが、セル上に関数をして入力すると以下のように「=LARGE(B2:B11,2)」と書きます。
これをVBAでEvaluateメソッドを使って書く場合に変数を使うことににより結果として「LARGE(“B2:B11″,2)」と、セル範囲をダブルクォーテーションで誤って囲ってしまうと、エラー2015が発生します。
よく原因が分からない場合は、実際にセルに関数を書いてみて、それがVBAのコードと一致しているのかを確認してみてください。
上のサンプルコードであれば、Evaluate()の括弧の中にある「”LARGE(” & sAddress & “, 2)”」をイミディエイトウィンドウで、「? “LARGE(” & sAddress & “, 2)”」＋Enterキーを押すと、変数が展開された状態で表示されます。
