VBAを使用してExcelで独自のルックアップ関数を作成する

VBAを使用してExcelで独自のルックアップ関数を作成する

Excelには、ユーザーがスプレッドシート内の情報を見つけるのに役立ついくつかの組み込みの検索ツールがあります。最も一般的なものの1つは、テーブルを検索し、ルックアップ値に関連付けられた別の値を返すvLookup関数です。

たとえば、顧客IDがわかっている場合は、それを使用して顧客の名前または電話番号を検索できます。

Id、顧客名、電話番号

123、Teriana Smith、689562365

432、ペドロ・ゴンザレス、3454567
Teriana Smithの電話番号を見つけるためのセル構文は次のようになります。

= vLookup(123、a2:c3,3)または一般的に:

= vLookup(searchValue、range、returnColumn)

ただし、複数の値を検索する必要がある場合、またはターゲット列が最初の列でない場合は、独自のVBAコードを開発する必要がある場合があります。特定の検索パターンが繰り返し使用される場合、カスタマイズされたソリューションは特に重要です。

ルックアップ関数を作成する簡単なVBAコード

独自のルックアップ関数を作成するには、currentRegionコマンドを使用できます。上記のサンプルデータを使用して、このコードはメッセージボックスに顧客の電話番号を返します。

searchText = “123”

searchColumn = 1
‘必要な検索列を選択します

Range( “a1″)。currentRegion.columns(searchColumn).select

選択した各cについて

c.value = searchTextの場合

msgbox “Phone:”&c.offset(0,2).value

ダイアログと結果ボックスをミックスに追加して、コードを強化できます。このようにして、コードを使用して、ワークシート全体で一般的な検索機能として動作させることもできます。 VBAの上級ユーザーの場合、ユーザーフォームはさらに多くの機能を許可します。

検索機能の拡張

上記のコードを使用した検索関数は少し一次元であり、あなた自身の状況によりエレガントなものが必要になるかもしれません。

過去6週間に購入した英国(UK)のすべての顧客を検索するシナリオを見ていきます。ワークシートの構造は次のようになります。

名前、国、最後の購入日

ジョン・スミス、英国、2012年8月20日

アメリカ合衆国、Teriana Lopez、2011年7月13日

ピーター・ヨハンソン、英国、2011年2月12日
すべての顧客が「顧客」と呼ばれるシートにあり、「出力」と呼ばれるワークシートが検索結果を記録すると仮定します。検索テキストは小文字に変換され、完全一致ではなくすべての出現に一致します。

‘出力対象の列、検索テキスト、および最初の行番号を選択します

searchText = “uk”

searchText = LCase(searchText)

targetCol = 2

outPutRow = 1
Sheets( “customers”)。Activate
‘ヘッダー列を無視し、国列を選択します

last = range( “b2″)、end(xlDown).address

Range( “b2:”&last).Select

選択範囲ごとに

日付差機能を使用して、過去6週間に購入した顧客を見つけます。このようにして、andやパラメーターなどの他の要件に合わせて検索を変更できます。

LCase(r)= searchText And DateDiff( “w”、r.Offset(0、1).Text)<= 6 Then

custName = r.Offset(0、-1).Value
一致するレコードが見つかり、コードが結果を出力シートに出力します。

Sheets( “output”)。Activate

Range( “a”&outPutRow).Activate

ActiveCellを使用

.Value = custName

で終わる

outPutRow = outPutRow + 1

Sheets( “customers”)。Activate

終了する


概要

いくつかの単純なVBAコードのいくつかの機能強化により、標準のExcelツールよりも多くの機能を備えた基本的な検索機能が作成されました。少しの創造性で、コードを自分のExcelの使用に適合させて、毎日の仕事の成果を高めることができます。

Andy L Gibsonは、以前のWebサイトプログラマであり、中小企業向けのソフトウェアアプリケーションへの関心を再発見しました。

Leave a Reply

Your email address will not be published. Required fields are marked *