エクセルにはたくさんの関数がありますが、その中でも「vlookup関数」は最も使用頻度が高く、便利な関数のひとつですね。
指定された範囲の中から縦方向にデータを検索し、検索条件に一致したデータを取り出すことができるので、顧客管理や商品管理をエクセルで行っている人はよく利用する関数だと思います。
ですがこの便利な「vlookup関数」も万能ではありません。
「VLOOKUP関数」は検索範囲を関数の引数で指定しているので、データの追加によりその範囲を超えてしまう場合は各セルに設定した引数を修正する手間が発生します。
またエクセルのセルに直接関数を記述するので、検索結果をDELETEで消してしまうと、せっかく入力した関数も消えてしまいます。
複数の関数を組み合わせたり、「セルのロック」や「シートの保護」といったエクセルの機能を利用すれば、この２つの欠点を解消することも可能です。
ですが正直な所、かなりの手間がかかるので実用的とは言えません。
という訳で、エクセルVBAを使って「VLOOKUP関数」と同じ処理を実現してみましたが、これがかなり便利！
行の追加が頻繁に発生する顧客管理や商品管理などをエクセルで管理している人は必ず役に立つと思います。
誰でも簡単に実装できるので、ぜひ参考にしてみて下さい。
VBAで「VLOOKUP関数」を実現したシート
今回作成した、VBAで「VLOOKUP関数」と同じ処理を実現したエクセルはこちらになります。
使い方
顧客IDのセル（B1）に検索したい顧客IDを設定します。
となりの「抽出ボタン」を押すと、顧客マスターテーブルから顧客IDが一致したデータを取得。
氏名、性別、都道府県、電話番号を表示します。
「クリアボタン」を押せば、検索条件および、検索結果を一度に消すことができます。
「VLOOKUP関数」のように引数を直すことなく、常に正しい結果を取得することが可能です。
VBAでVLOOKUPを実現するシートの作成手順
このシートは簡単なVBAしか利用していないので、これから解説する手順で作業すれば誰でも作成することができますよ！
まず大前提として、エクセルにボタンを配置したり、VBAを記述するためには、エクセルのメニューに「開発タブ」を表示させる必要があります。
ですがデフォルト状態では「開発タブ」はメニューに表示されていません。
メニューに「開発タブ」が見当たらない人は以下の手順で表示させましょう。
開発タブの表示方法！
１,ファイル→オプションを押して、「Excelのオプション」を表示。
「リボンのユーザー設定」を押します。
2,右側にある「メインタブ」にある「開発」にチェックを入れて「OK」を押します。
メニューに「開発タブ」が表示されました！
次のステップで処理を実行するボタンを作成しますが、その前にエクセルの体裁を整えておきましょう。
エクセル上にボタンを配置する
では次に、「抽出ボタン」と「クリアボタン」をエクセルに配置します。
「開発」→「挿入」→「フォームコントロール」の左上にあるボタンをクリック。
場所はどこでも良いので、エクセル上にボタンを配置します。
マクロの登録画面が開きますが、ここでは何もせずにOKを押しておきましょう。
同じ手順で、2つめのボタンも作成しておきます。
ボタンの右クリックで位置の変更、および名前の変更ができるので、「抽出」と「クリア」に変更して顧客IDの右側に並べます。
これで見た目は整いました！
エクセルでVBAを記述するためには、エクセルをマクロ有効ブックにする必要があります。
「名前をつけて保存」を選んで、拡張子を「xlsm」で保存しておきましょう。
次は実際に処理を実行するVBAを記述していきます。
検索とクリアを実行するVBAを記述する
シートに配置した抽出ボタンを右クリック、マクロの登録を選択します。
マクロの登録画面が開いたら、右上の「新規作成」を押します。
VBAを記述するエディターが起動します。
右側の「Sub ボタン1_Click()」から「End Sub」の間に以下のVBAをコピペして下さい。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
Sub ボタン1_Click() Dim lastRoW As Long '顧客IDの最終行を格納する変数 Dim i As Integer Dim Hitflg As String '該当データ無しを判別する変数 '=== 取得領域のクリア === Worksheets("vba").Cells(3, 2) = "" '氏名の初期化 Worksheets("vba").Cells(4, 2) = "" '性別の初期化 Worksheets("vba").Cells(5, 2) = "" '都道府県の初期化 Worksheets("vba").Cells(6, 2) = "" '電話番号の初期化 '=== 顧客IDの最終行を取得 === lastRoW = Worksheets("vba").Range("a65536").End(xlUp).Row '顧客IDの最終行を取得 'Debug.Print "顧客IDの最終行 = " & lastRoW '=== 変数の初期化 === Hitflg = "OFF" '=== 顧客IDが一致するデータを算出する === For i = 10 To lastRoW '縦 If Worksheets("vba").Cells(1, 2) = Worksheets("vba").Cells(i, 1) Then Worksheets("vba").Cells(3, 2) = Worksheets("vba").Cells(i, 2) '氏名をセット Worksheets("vba").Cells(4, 2) = Worksheets("vba").Cells(i, 4) '性別をセット Worksheets("vba").Cells(5, 2) = Worksheets("vba").Cells(i, 5) '都道府県をセット Worksheets("vba").Cells(6, 2) = Worksheets("vba").Cells(i, 6) '電話番号をセット Hitflg = "ON" End If Next i '=== 顧客IDが一致するデータが存在しない場合 === If Hitflg <> "ON" Then MsgBox "一致するデータがありません。", vbExclamation, "該当データ無し" End If End Sub |
貼り付けが終わったら一度エディタを閉じます。
同じ手順で、クリアのボタンには以下のコードをコピペして下さい。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub ボタン2_Click() '=== 検索領域のクリア === Worksheets("vba").Cells(1, 2) = "" '顧客IDの初期化 '=== 取得領域のクリア === Worksheets("vba").Cells(3, 2) = "" '氏名の初期化 Worksheets("vba").Cells(4, 2) = "" '性別の初期化 Worksheets("vba").Cells(5, 2) = "" '都道府県の初期化 Worksheets("vba").Cells(6, 2) = "" '電話番号の初期化 End Sub |
これで「抽出ボタン」と「クリアボタン」にVBAの処理が登録されました！
試しに「抽出ボタン」を押してみましょう。
「インデックスが有効範囲にありません」というエラーが表示されてしまいました。
ポップアップに表示されている「デバック」を押すと、VBAを入力するエディターが起動。
エラーが発生した箇所が黄色で表示されます。
エクセルのシート名を「vba」に修正すれば、このエラーは発生しなくなります。
これですべての処理は完成です。
実際に顧客データを入力して、検索処理を試してみて下さい！
無料ダウンロード
今回作成したシートは、こちらのページから無料でダウンロードできるようにしました。
作成するのが面倒な人や、すぐに利用したい人はダウンロード版を使ってみて下さいね。
