Excelを勉強しましょう EnjoyExcel

アイコンの機能や関数の説明、VBA初心者向けの勉強方法について情報掲載しています。関数、VBAともに理解が進むように出来るだけ事例を交えて説明しています。

VBA⑭ VLOOKUP関数を知らない人でも使えるツールを作ってみよう。

f:id:EnjoyExcel:20210628150154p:plain

キャッチ画像にもある様にVBAのシリーズとしては完結編になります。もう少しお付き合いください。よろしくお願い致します。

 

前回はワークシート関数のVLOOKUPを使ってみようという記事でしたね。
enjoyexcel.hatenablog.com

 

でも検索対象となるデータがデータベースに無い時にはエラーになってしまうという事で不完全なコードでした。エラーが出る原因からすると今後も起こる可能性があります。

 

ではどうしたら良いでしょうか?エラーが出るときと出ないときの条件がほとんど明確なので条件分岐を使って考えてみましょうか。

 

データがあるときはVLOOKUPを実行しデータが無い時はVLOOKUPを実行せず「データが無いよ」って言ってあげれば良さそうな気がします。

 

ってことはやっぱり条件分岐が使えそうな感じがしてきますね。IF文採用しましょう。

 

続いてデータの有無を確認するにはいろいろな方法がありますが今回もワークシート関数を使いましょう。COUNTIF関数を使います。

 

ワークシート関数のCOUNTIFは

COUNTIF(第1引数,第2引数)という形です。

 

第1引数

関数実行時の対象となるデータベースの範囲です。

 

第2引数

データベースの中で検索したい文字です。

 

COUNTIF(第1引数,第2引数)>0  という条件にしたらデータベースの中に第2引数の情報が居るのか確認出来ますね。

 

上記をIF文の先頭にしてTRUEだったらVLOOKUP、FALSEだったら”該当なし”等の言葉を出してあげればエラーは出ないです。

 

前回のワークシートのネタを元にコードを組んでみました。

これでエラーが出ない様になってます。

 

f:id:EnjoyExcel:20210628152701p:plain

 

では仕上げです。課題としては今まで勉強してきたことでほとんどは対応可能ですがちょっと背伸びしてるところもあります。教えてないところもありますがやってみましょう。

 

データシートは2枚になります。

プロジェクトエクスプローラは以下のようになっています。

f:id:EnjoyExcel:20210628153343p:plain

こちらは Sheet1(データベース) という名前のシートです。

これも「なんちゃって個人情報」で作成しているダミーデータです。

各自コードを書くときは名前列とは別にあと2つの列の情報を適当に作成してみてください。件数は10件もあれば良いと思います。

f:id:EnjoyExcel:20210628153046p:plain

 

こちらは Sheet2(抽出シート) という名前のシートです。

f:id:EnjoyExcel:20210628153139p:plain

 

データベースは5000件です。抽出シートのD列は200件です。私が用意したデータと同じ量は必要無いと思います。

 

課題

抽出シートにある名前とデータベースの名前が一致したら抽出シートのE、F列に都道府県とカレーの食べ方を引っ張ってくるという仕様のデータを用意してみてください。

 

抽出シートの実行ボタンで作業を行う様コードを作りこんでみてください。

抽出シートの削除ボタンは抽出シートのE、F列を削除する際に使用します。

 

ってことはプロシージャとしては2つですね。実行と削除を分けて作ってください。

 

ヒント

・コードは標準モジュールに書きましょう。

 

・For~Next でループさせるのは抽出シートのD列の情報です。

 

・ループは5行目、もしくは6行目から最終行とします。

 

・データベースの情報についても最終行と最終列を認識する必要があります。

 

・抽出シートの名前がデータベースの中に居るのか確認しましょう。名前が居たらVLOOKUPを行い居なかったら”該当なし”を表示させましょう。

 

都道府県とカレーの食べ方を抽出するのでVLOOKUPは2つ必要です。

 

・シート間の情報を扱いますのでRangeやCellsの前にSheetを指定する必要があります。(これはまだ紹介していません)

 

・削除データは単純に範囲を決めて削除という形でもOKです。

 

いかがでしょうか。前の記事に戻っていただき探すのも良しYahooさんやGoogleさんに頼るも良しです。考えながら書いてテストするという事が重要です。こんな感じで実務ではやりたい事を整理してコードに変換し自力で書いてテストするという事が求められます。

 

コード書いてるだけでしょ?って思っている人も居るかもしれませんが実際は依頼された仕事を理解してどうやって使うのか考えながら設計図を作りコードを書いてテストし依頼者へ納品する。納品後使用感をみてもらい修正を行うまでやり通すという事がほとんどだと思います。

 

スキルの無い人の依頼は「どんな使い方したい」とかアウトプットのイメージなんて無くただ「楽にして」です。伝わりにくいのですがこちらが考える事は結構多いです。

 

ではコードの話に戻ります。

答え載せておきます。変数に日本語使うのは抵抗あるという人も居ますが私は問題無いと思います。誤作動もしませんので。私は変数を英語でも書きますが日本語の方が引継ぎが楽な印象があります。何やってるか伝わりやすい事が多のでメリットもありますよ。

 

実行ボタンのコードです。


Sub vlookup()

'変数の定義
Dim 行 As Long
Dim 列 As Long
Dim 抽出行 As Long

'画面更新の停止
Application.ScreenUpdating = False

'抽出シートの4列目をループする
For 抽出行 = 6 To Cells(Rows.Count, 4).End(xlUp).Row

'データベースの最終行と最終列を確認する
With Sheet1
行 = .Cells(Rows.Count, 1).End(xlUp).Row
列 = .Cells(5, Columns.Count).End(xlToLeft).Column
End With

'抽出シートの名前がデータベースに居るのか確認。
If WorksheetFunction.CountIf(Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(行, 1)), Sheet2.Cells(抽出行, 4)) > 0 Then

'居たらVLOOKUPで結果を抽出
Cells(抽出行, 5) = WorksheetFunction.vlookup(Sheet2.Cells(抽出行, 4), Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(行, 列)), 8, False)
Cells(抽出行, 6) = WorksheetFunction.vlookup(Sheet2.Cells(抽出行, 4), Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(行, 列)), 10, False)

Else
'居ない時は「該当なし」と表示させる
Cells(抽出行, 5) = "該当なし"
Cells(抽出行, 6) = "該当なし"

End If

Next
'画面更新停止の解除
Application.ScreenUpdating = True
End Sub

f:id:EnjoyExcel:20210628160319p:plain

削除ボタンのコードです。


Sub clear()

'変数の定義
Dim 行 As Long
Dim 行2 As Long

'抽出シートの都道府県とカレーの食べ方が入力されている列の最終行を認識
行 = Cells(Rows.Count, 5).End(xlUp).Row
行2 = Cells(Rows.Count, 6).End(xlUp).Row

'抽出シートの都道府県とカレーの食べ方が入力されている列の最終行が5より大きかったら・・・
If 行>5 and 行2>5 then

'結果をクリアにする(都道府県とカレー食べ方の抽出結果を消去する)
Range(Cells(6, 5), Cells(行, 6)).ClearContents

End If

End Sub

f:id:EnjoyExcel:20210628160340p:plain

vlookupプロシージャの Application.ScreenUpdating や clearプロシージャの比較演算子、IF文の中でのAndの使い方は興味がある方は調べてみてください。そこからまた勉強が始まると思いますが知識の積み重ねだと思ってやってみてください。

 

長くなりましたが以上となります。

ここまで出来れば書店に行ってもVBA初級編の本なら結構読めると思います。厚みのある専門書みたいなものではなく「いけそうな気がする」本を手に取ってください。

 

それでも???になったらもう1回勉強し直しましょう。ネタはなんでも良いです。1回でうまくいくことは無いので繰り返し勉強しましょう。

 

この時期にこういった記事を書いたのは自分なりに意味があって8月の連休をターゲットにしているます。そこまでで下地を作ってもらって8月の連休でコードをたくさん書いてもらえると連休明けから皆様の仕事が片付くのでは?という思いがあります。

 

私事ですが7月には社内でVBAの勉強会も予定しています。募集をしたら思ったより集まっていただきました。他拠点の方にもオンラインで聞いていただく事になっているのでしっかりやらなきゃなと思っています。このブログをベースに勉強会をやろうと思っています。

 

では次からはVBAかもしれないし関数やピボットテーブルの記事にするのか全く決めていませんが不定期でブログを書いていきます。お疲れ様でした。