みなさん、こんにちはケンケンです。
エクセルVBAを使ったカレンダー作りを紹介しています。
前回は、DoLoopとDateAdd関数を使ってシートに日付を書き出すところまで紹介しました。
今回は、OFFSET関数やVLOOKUP関数を使って検索する方法をVBAを使ってご紹介します。
VBAでサクッと検索できるようになると幅が広がるので是非覚えて下さい。
VBAでOFFSETを使う
今回は、データを検索抽出して書き出す方法をご紹介します。
そこで検索に必要になる、OFFSET関数とVLOOKUP関数の基礎を紹介します。
OFFSET関数とは
OFFSETはひとことで言うとセルの移動のことです。
まず、OFFSET関数の書式から見てみます。
OFFSET(基準,行数,列数,高さ,幅)
基準・・・セルの起点となる部分です。
行数・列数・・・セルを動かす位置を示しています。
高さ・幅・・・基準セルの高さ(行)と幅(列)を指定します。
言葉では分かりづらいので実際に使ってみましょう。
上図のような一覧を作成しE2セルに以下のように数式を入力します。
=OFFSET(A2,1,1)
A2セルを起点として１行１列目のデータを参照してください。という意味です。
結果は「鈴木」が返ってきます。
もうひとつ高さと幅についても数式で確認します。
E３セルに以下のように入力しましょう。
=SUM(OFFSET(A2,2,2,2,1))
すると「９２」という結果が返ってきます。
これは、上図で確認してもらいたいのですが、A２セルを起点として２行下２列右のC4セルを参照しています。
そして、高さ(行)は２なのでC4セルとC5セルをとり、幅(列)は１なのでC列だけを取得します。
すると、C４セルは３８、C５セルは５４と数字が入っているのでSUM関数で合計して９２という結果が返ってくるのです。
高さと幅を使うことはまれですが、一応仕組みは理解しておくと良いでしょう。
VBAでOFFSETを使う
エクセル関数でOFFSETの基本的な使い方を見てきましたので、次はVBAでOFFSETを使ってみます。
Rangeオブジェクトと組み合わせるのがコツです。
Sub kensaku1()
Range("E4").Value = Range("A2").Offset(0, 1).Value'①
Range("E5").Value = Range("A2").Offset(, 2).Value'②
End Sub
①A2セルを起点に縦に０行、横に１列移動した先のデータを参照し、E4セルに書き出します。
②OFFSETの最初の引数を省略すると「０」と解釈されます。よってA２セルを起点に縦に０行、横に２列移動した先のデータを参照します。
実行結果は以下のようになります。
上図のように書き出せればOKです。
VBAでVLOOKUP関数を使う
VBAでVLOOKUP関数を使ってみましょう。
WorkSheetFunctionという接頭語が必要になりますが、おまじないのようなものです。
それでは、コードを書いてみましょう。
Sub kensaku_vlook()
Range("H2").Value = WorksheetFunction.VLookup(Range("G2"), Range("A2:C6"), 2, 0)
End Sub
おさらいとしてVLOOKUP関数の書式を記載しておきます。
=VLOOKUP（検索値、範囲、列番号、検索の型）
でしたね。
今回は、
- 検索値＝G2セル(001)
- 範囲＝A２からC６セル
- 列番号＝２
- 検索の型＝FALSEつまり０を指定
実行すると以下のようになります。
今回はOFFSETで書き出します
OFFSETとVLOOKUPを紹介してきましたが、今回は、OFFSETのみで検索抽出するコードを完成させます。
まずは単純にひとつのデータをOFFSETを使って書き出します。
OFFSETでデータをひとつ抽出する
Sub kensaku_offset1()
Range("G2").Value = Range("A2").Offset(0, 1).Value
Range("H2").Value = Range("A2").Offset(0, 2).Value
End Sub
上記コードを実行すると以下のようになります。
社員番号と合致したデータを抽出する
これに、検索機能を追加していきます。
検索したい社員番号と一覧を比べて合致したら書き出すようにします。
F２セルに検索したい社員番号を入力します。
そして、その社員番号(F2)と合致したら名前と年齢を書き出すようにコードを変更します。
Sub kensaku_offset1()
If Range("A2").Value = Range("F2").Value Then
Range("G2").Value = Range("A2").Offset(0, 1).Value
Range("H2").Value = Range("A2").Offset(0, 2).Value
End If
End Sub
実行して先ほどと同じように書き出せたら成功です。
また、F2セルの値を001以外に変更すると書き出せなくなることも確認しておきましょう。
ForNextで検索元をループさせる
このままだと、検索元がひとつしかないので機能として意味がありません。
そこで、検索元をループさせるためにForNext構文を使います。
コードを以下のように変更します。
Sub kensaku_offset1()
Dim cnt
For cnt = 2 To 6
If Range("A" & cnt).Value = Range("F2").Value Then
Range("G2").Value = Range("A" & cnt).Offset(0, 1).Value
Range("H2").Value = Range("A" & cnt).Offset(0, 2).Value
End If
Next
End Sub
カウント変数cntを用意し、先ほどのコードのRange(“A2”)の部分をRange(“A” & cnt)と変更します。
すると、F2の検索値を変更しても一覧から適切にデータを取り出せます。
検索機能を完成させよう
ここまでできたらあともう一歩です。
最後は、検索値を増やしても検索できるようにコードを修正します。
具体的にはF2セルからF4セルに検索値を設けます。
Sub kensaku_offset1()
Dim cnt
Dim kensakucnt
For kensakucnt = 2 To 4
For cnt = 2 To 6
If Range("A" & cnt).Value = Range("F" & kensakucnt).Value Then
Range("G" & kensakucnt).Value = Range("A" & cnt).Offset(0, 1).Value
Range("H" & kensakucnt).Value = Range("A" & cnt).Offset(0, 2).Value
End If
Next
Next
End Sub
検索値を回すためのカウント変数kensakucntを用意し、２～４行目(F2～F4)を回します。
転記先に該当するG列とH列のRangeオブジェクトを変更してください。
先ほどのコードと見比べて、どこがどのように変更されたかを理解して覚えるようにしましょう。
コードは完成しました。実行して以下のように抽出できれば成功です。
まとめ
いかがでしたか。
今回は、VBAで検索して抽出する方法をOFFSETとVLOOKUPの２つを使ってご紹介しました。
どちらも重要な概念ですのでVBAを使わなくても通常の関数としても威力を発揮します。是非覚えておきましょう。
次回は、検索したデータをカレンダーに転記する方法等をご紹介する予定ですのでお楽しみに。
それでは、また次回お会いしましょう。
カレンダーアプリ作成記事一覧
【エクセルでカレンダー作成】シリアル値の概念を知り日付関数の基礎をマスター
【エクセルでカレンダー作成】日付と曜日を表現する関数をマスターしよう
【エクセルでカレンダー作成】作業列を設けてカレンダー作りにとりかかる
【エクセルでカレンダー作成】VLOOKUP関数と条件付き書式でカレンダーを作りこむ
【エクセルVBA】VBAでカレンダーを作るときに最も重要な関数
【エクセルVBAでカレンダー作り】DoLoopとDateAdd関数を使って日付を書き出す方法
【エクセルVBAでカレンダー作り】OFFSETとVLOOKUPを使って検索抽出する方法
【エクセルVBA】別シートからカレンダーに行事を転記する方法
【エクセルVBA】カレンダーの土日だけ色を変える方法とRangeオブジェクトの応用的な使い方
【エクセルVBA】プロシージャをまとめてカレンダーを完成させよう
