【エクセルVBAでカレンダー作り】OFFSETとVLOOKUPを使って検索抽出する方法

2019年8月4日
2019年8月31日
カレンダーアプリ
offset, VBA, カレンダー

カレンダーアプリ

みなさん、こんにちはケンケンです。

エクセルVBAを使ったカレンダー作りを紹介しています。

前回は、DoLoopとDateAdd関数を使ってシートに日付を書き出すところまで紹介しました。

今回は、OFFSET関数やVLOOKUP関数を使って検索する方法をVBAを使ってご紹介します。

VBAでサクッと検索できるようになると幅が広がるので是非覚えて下さい。

- 0.1
1 VBAでOFFSETを使う
2 VBAでVLOOKUP関数を使う
- 2.1
- 2.2
3 今回はOFFSETで書き出します
4 まとめ

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

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

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

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

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

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

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