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

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

 

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

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

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

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

 

VBAでOFFSETを使う

今回は、データを検索抽出して書き出す方法をご紹介します。

そこで検索に必要になる、OFFSET関数とVLOOKUP関数の基礎を紹介します。

 

OFFSET関数とは

OFFSETはひとことで言うとセルの移動のことです。

 

まず、OFFSET関数の書式から見てみます。

 

OFFSET(基準,行数,列数,高さ,幅)

基準・・・セルの起点となる部分です。

行数・列数・・・セルを動かす位置を示しています。

高さ・幅・・・基準セルの高さ(行)と幅(列)を指定します。

 

言葉では分かりづらいので実際に使ってみましょう。

 

上図のような一覧を作成しE2セルに以下のように数式を入力します。

=OFFSET(A2,1,1)

A2セルを起点として1行1列目のデータを参照してください。という意味です。

結果は「鈴木」が返ってきます。

 

もうひとつ高さと幅についても数式で確認します。

E3セルに以下のように入力しましょう。

=SUM(OFFSET(A2,2,2,2,1))

すると「92」という結果が返ってきます。

これは、上図で確認してもらいたいのですが、A2セルを起点として2行下2列右のC4セルを参照しています。

そして、高さ(行)は2なのでC4セルとC5セルをとり、幅(列)は1なのでC列だけを取得します。

すると、C4セルは38、C5セルは54と数字が入っているのでSUM関数で合計して92という結果が返ってくるのです。

高さと幅を使うことはまれですが、一応仕組みは理解しておくと良いでしょう。

 

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セルを起点に縦に0行、横に1列移動した先のデータを参照し、E4セルに書き出します。

②OFFSETの最初の引数を省略すると「0」と解釈されます。よってA2セルを起点に縦に0行、横に2列移動した先のデータを参照します。

実行結果は以下のようになります。

上図のように書き出せれば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)
  • 範囲=A2からC6セル
  • 列番号=2
  • 検索の型=FALSEつまり0を指定

実行すると以下のようになります。

今回は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

上記コードを実行すると以下のようになります。

 

社員番号と合致したデータを抽出する

これに、検索機能を追加していきます。

検索したい社員番号と一覧を比べて合致したら書き出すようにします。

F2セルに検索したい社員番号を入力します。

そして、その社員番号(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を用意し、2~4行目(F2~F4)を回します。

転記先に該当するG列とH列のRangeオブジェクトを変更してください。

 

先ほどのコードと見比べて、どこがどのように変更されたかを理解して覚えるようにしましょう。

コードは完成しました。実行して以下のように抽出できれば成功です。

 

まとめ

いかがでしたか。

今回は、VBAで検索して抽出する方法をOFFSETとVLOOKUPの2つを使ってご紹介しました。

どちらも重要な概念ですのでVBAを使わなくても通常の関数としても威力を発揮します。是非覚えておきましょう。

 

次回は、検索したデータをカレンダーに転記する方法等をご紹介する予定ですのでお楽しみに。

それでは、また次回お会いしましょう。

 

カレンダーアプリ作成記事一覧

【エクセルでカレンダー作成】シリアル値の概念を知り日付関数の基礎をマスター

【エクセルでカレンダー作成】日付と曜日を表現する関数をマスターしよう

【エクセルでカレンダー作成】作業列を設けてカレンダー作りにとりかかる

【エクセルでカレンダー作成】VLOOKUP関数と条件付き書式でカレンダーを作りこむ

【エクセルVBA】VBAでカレンダーを作るときに最も重要な関数

【エクセルVBAでカレンダー作り】DoLoopとDateAdd関数を使って日付を書き出す方法

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

【エクセルVBA】別シートからカレンダーに行事を転記する方法

【エクセルVBA】カレンダーの土日だけ色を変える方法とRangeオブジェクトの応用的な使い方

【エクセルVBA】プロシージャをまとめてカレンダーを完成させよう