VBAで指定セル範囲からn番目の値を取得する

ワークシート関数のLARGE関数とSMALL関数

指定セル範囲の最大値や最小値はMAX関数やMIN関数で求められます。

その最大値や最小値の次点以降の、上から2番目や下から3番目などの値はLARGE関数とSMALL関数で取得します。

VBAから呼び出す場合はWorksheetFunctionプロパティを使う方法と、Evaluateメソッドを使う方法がありますが、ここではEvaluateメソッドでの方法を紹介します。

なお、WorksheetFunctionプロパティとEvaluateメソッドの利用方法については以下をご参照ください。

VBAからExcelのワークシート関数を使う

上から2番目と下から3番目の値を取得するサンプル

以下のようなセルの入力がある場合に、B列の値から上から2番目と下から3番目の値を取得するサンプルです。

ここでは分かりやすいように最初から7行目でB列の2行目から11行目を範囲選択をしています。

あとは選択範囲のアドレス(B1:B12)を取得し、それを以降のLARGE関数とSMALL関数に渡しています。

実行結果
上から2番目=11
下から3番目=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キーを押すと、変数が展開された状態で表示されます。

関連記事

サブコンテンツ

このページの先頭へ