エクセルの知識

VLOOKUP関数をVBAで実現!行を追加しても引数の修正は不要です


エクセルにはたくさんの関数がありますが、その中でも「vlookup関数」は最も使用頻度が高く、便利な関数のひとつですね。

指定された範囲の中から縦方向にデータを検索し、検索条件に一致したデータを取り出すことができるので、顧客管理や商品管理をエクセルで行っている人はよく利用する関数だと思います。

ですがこの便利な「vlookup関数」も万能ではありません。

「VLOOKUP関数」は検索範囲を関数の引数で指定しているので、データの追加によりその範囲を超えてしまう場合は各セルに設定した引数を修正する手間が発生します。

またエクセルのセルに直接関数を記述するので、検索結果をDELETEで消してしまうと、せっかく入力した関数も消えてしまいます。

複数の関数を組み合わせたり、「セルのロック」や「シートの保護」といったエクセルの機能を利用すれば、この2つの欠点を解消することも可能です。

ですが正直な所、かなりの手間がかかるので実用的とは言えません。

こんなに手間をかけるならエクセルのVBAで処理したほうが早くない?

という訳で、エクセルVBAを使って「VLOOKUP関数」と同じ処理を実現してみましたが、これがかなり便利!

行の追加が頻繁に発生する顧客管理や商品管理などをエクセルで管理している人は必ず役に立つと思います。

誰でも簡単に実装できるので、ぜひ参考にしてみて下さい。

VBAで「VLOOKUP関数」を実現したシート

今回作成した、VBAで「VLOOKUP関数」と同じ処理を実現したエクセルはこちらになります。

使い方

顧客IDのセル(B1)に検索したい顧客IDを設定します。

となりの「抽出ボタン」を押すと、顧客マスターテーブルから顧客IDが一致したデータを取得。
氏名、性別、都道府県、電話番号を表示します。

「クリアボタン」を押せば、検索条件および、検索結果を一度に消すことができます。

VBAにて顧客マスターに入力されたデータの最終位置を取得しているので、何百件データを追加してもプログラムの修正不要。

「VLOOKUP関数」のように引数を直すことなく、常に正しい結果を取得することが可能です。

VBAでVLOOKUPを実現するシートの作成手順

このシートは簡単なVBAしか利用していないので、これから解説する手順で作業すれば誰でも作成することができますよ!

まず大前提として、エクセルにボタンを配置したり、VBAを記述するためには、エクセルのメニューに「開発タブ」を表示させる必要があります。

ですがデフォルト状態では「開発タブ」はメニューに表示されていません。

メニューに「開発タブ」が見当たらない人は以下の手順で表示させましょう。

開発タブの表示方法!

1,ファイル→オプションを押して、「Excelのオプション」を表示。
「リボンのユーザー設定」を押します。

2,右側にある「メインタブ」にある「開発」にチェックを入れて「OK」を押します。

メニューに「開発タブ」が表示されました!

次のステップで処理を実行するボタンを作成しますが、その前にエクセルの体裁を整えておきましょう。

セルの位置をVBAで特定して処理を実行するので、サンプルと同じ位置に作成して下さい。

エクセル上にボタンを配置する

では次に、「抽出ボタン」と「クリアボタン」をエクセルに配置します。

「開発」→「挿入」→「フォームコントロール」の左上にあるボタンをクリック。

場所はどこでも良いので、エクセル上にボタンを配置します。
マクロの登録画面が開きますが、ここでは何もせずにOKを押しておきましょう。

同じ手順で、2つめのボタンも作成しておきます。

ボタンの右クリックで位置の変更、および名前の変更ができるので、「抽出」と「クリア」に変更して顧客IDの右側に並べます。

これで見た目は整いました!

エクセルでVBAを記述するためには、エクセルをマクロ有効ブックにする必要があります。
「名前をつけて保存」を選んで、拡張子を「xlsm」で保存しておきましょう。

次は実際に処理を実行するVBAを記述していきます。

検索とクリアを実行するVBAを記述する

シートに配置した抽出ボタンを右クリック、マクロの登録を選択します。
マクロの登録画面が開いたら、右上の「新規作成」を押します。

VBAを記述するエディターが起動します。
右側の「Sub ボタン1_Click()」から「End Sub」の間に以下のVBAをコピペして下さい。

貼り付けが終わったら一度エディタを閉じます。

同じ手順で、クリアのボタンには以下のコードをコピペして下さい。

これで「抽出ボタン」と「クリアボタン」にVBAの処理が登録されました!

試しに「抽出ボタン」を押してみましょう。

「インデックスが有効範囲にありません」というエラーが表示されてしまいました。

ポップアップに表示されている「デバック」を押すと、VBAを入力するエディターが起動。
エラーが発生した箇所が黄色で表示されます。

今回のエラーの原因は、エクセルのシート名が「sheet1」であったのに対して、VBA側ではシート名を「vba」と指定していたことが原因です。
エクセルのシート名を「vba」に修正すれば、このエラーは発生しなくなります。

これですべての処理は完成です。

実際に顧客データを入力して、検索処理を試してみて下さい!

無料ダウンロード

今回作成したシートは、こちらのページから無料でダウンロードできるようにしました。

作成するのが面倒な人や、すぐに利用したい人はダウンロード版を使ってみて下さいね。

無料ダウンロード

人気の投稿

1

会員制のサイトにログインするために必要なパスワード、どうやって管理していますか?エクセルやメモ帳などよりも便利で使いやすいソフトを作成しました。

2

自営業の人や個人事業主の人は、顧客管理をエクセルで行っている人も多いかと思います。 エクセルで顧客管理のメリット パソコンにエクセルが入っていれば費用ゼロで使え ...

顧客管理 3

ちょっとした顧客管理や請求書などの作成にエクセルを利用している会社は多いと思います。 特に自営業や個人事業を営んでいる人、まだ顧客数が少ない中小企業などでは、導 ...

-エクセルの知識
-

Copyright© ビジツールラボ , 2020 All Rights Reserved.