キーワードから同一列の情報を取得する

今回は特定のキーワードから同じ列にある情報を取得してくる
VLOOKUP関数について紹介します。

今までに紹介してきた文字の抽出、COUNTIFと
今回のVLOOKUPはどこの現場に行っても使用頻度が高く、
VBAを組む際にも多用します。

今までのものより難しいですが、
仕組みを覚えればとっても便利になりますので、
がんばって読んでください。

見本として商品情報と商品在庫の一覧を作成しました

<商品情報>

<商品在庫>

商品名と在庫数は別々のシートで管理されており、
在庫総数と秋葉原店の情報が必要という状況を想定して説明します。

Excel関数

関数で情報取得する場合の書き方

=VLOOKUP(対象キーワード,範囲,対象列の指定[,検索方法])

入力例
=VLOOKUP(A8,商品在庫!A:F,6,False)

関数の入力値が4つもあるのでわかりづらそうですが、
慣れればそこまで難しくはありません。

特にわかりづらいのは範囲と対象列の指定だと思います。
"対象キーワード"は検索ワード
"範囲"はどこからどこまでを情報取得の範囲とするか
"対象列の指定"は範囲の始点列を1として、右に行くごとに1増えていきます。
"検索方法"についてはTrue(近似一致)とFalse(完全一致)で指定できますが、
基本はFalseの完全一致で良いと思います。
入力しない場合はTrueの近似一致とみなされてしまいます。
True、Falseと入力が面倒な場合は数字の0と1でも代用可能となっており、
True=1
False=0
で代用可能です。

入力例で実施した際の動作見本

"対象キーワード"の"KN1010"を
"範囲"で指定した始点のA列で探していき、合致した場合は
"対象列の指定"である6列目のF列の同一行のセルの値を返してきます。

動作結果

このVLOOKUPを覚えておくと他のシート、
他のブックからでも情報を取得してこれるので、
情報整理に大いに役立ちます。

※使い方について注意
注意点としては同一の"対象キーワード"が複数あった場合、
上から順番に見ていき、合致した時点で次の行移行は情報取得されません

このように本来は先月の在庫総数の値を取得したい場合でも
上に同一の"対象キーワード"がある場合、
上にある行が必ず優先されてしまいます。
同一の値がある場合などは工夫して使用する必要があります。

もう一つ使うにあたって注意するのが
"対象キーワード"の列から右側しか情報が取得できません。
左側の値が欲しい場合は列を入れ替えたり、
一番左の列に"対象キーワード"の列を挿入する必要があります。

解決策としてはINDEX関数を使う事で取得は可能です。
が、VBAの方でまるっと解決するのでINDEX関数は紹介しませんｗ

ExcelVBA

前述した通り、VBAでVLOOKUP関数と同じ動きをさせます。
大まかな流れとしては以下の通りです。


Sub 情報取得()

    '変数名"x"、"y"、"z"、"row1"、"row2"をVariant方式で指定します
    Dim x, y, z, row1, row2 As Variant
    
    '変数名"wb"をWorkbook方式で指定します
    Dim wb As Workbook
    
    '今回は同じブック内で実施するので不要ですが、
    '別のブックから情報を取得する場合を考えて指定します。
    Set wb = ActiveWorkbook
    
    '変数名"ws1"、"ws2"をWorksheet方式で指定します
    Dim ws1, ws2 As Worksheet
    
    'どのシートから情報を取得するのかわかりやすいように指定します。
    Set ws1 = wb.Sheets("商品情報")
    Set ws2 = wb.Sheets("商品在庫")

    '変数"row1"にws1の商品情報の最終行を取得します。(今回は8が入ります)
    row1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row

    '変数"row2"にws2の商品情報の最終行を取得します。(今回は11が入ります)
    row2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    
    '変数"x"に2～row2(11)までを代入してプログラムをループさせます。
    For x = 2 To row2
        
        'ws1のcells(2,1)(A2)とws2のcells(x,1)(Ax)は同じかを確認して、
        '同じ場合は中のプログラムを実行させます。
        If ws1.Cells(2, 1) = ws2.Cells(x, 1) Then
                
                '商品在庫から商品情報へそれぞれ転記
                ws1.Cells(2, 3) = ws2.Cells(x, 6)
                ws1.Cells(2, 4) = ws2.Cells(x, 4)
                
        'ココまでがif文です。
        End If
    
    'ココまでをループさせます。
    Next x
    
    '確認用のメッセージボックス
    MsgBox ws1.Cells(2, 2) & "在庫総数は、" & ws1.Cells(2, 3) & "。秋葉原の在庫は、" & ws1.Cells(2, 4)

End Sub

長いですね・・・

実行結果

でも情報は正常に取得できています。

しかしこのままではA2の一つしかチェックできません。
どうすれば良いかというと、ループの中でもう一つループを回します。

商品情報のA2を商品在庫全体から検索して情報を取得
↓
完了後
↓
商品情報のA3を商品在庫全体から検索して情報を取得
↓
完了後
↓
商品情報のA4を商品在庫全体から検索して情報を取得....

と順番に処理していきます。

Sub 情報取得()

    '変数名"x"、"y"、"z"、"row1"、"row2"をVariant方式で指定します
    Dim x, y, z, row1, row2 As Variant
    
    '変数名"wb"をWorkbook方式で指定します
    Dim wb As Workbook
    
    '今回は同じブック内で実施するので不要ですが、
    '別のブックから情報を取得する場合を考えて指定します。
    Set wb = ActiveWorkbook
    
    '変数名"ws1"、"ws2"をWorksheet方式で指定します
    Dim ws1, ws2 As Worksheet
    
    'どのシートから情報を取得するのかわかりやすいように指定します。
    Set ws1 = wb.Sheets("商品情報")
    Set ws2 = wb.Sheets("商品在庫")

    '変数"row1"にws1の商品情報の最終行を取得します。(今回は8が入ります)
    row1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row

    '変数"row2"にws2の商品情報の最終行を取得します。(今回は11が入ります)
    row2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    
    '変数"y"に2～row1(8)までを代入してプログラムをループさせます。
    For y = 2 To row1
    
        '変数"x"に2～row2(11)までを代入してプログラムをループさせます。
        For x = 2 To row2
            
            'ws1のcells(2,1)(A2)とws2のcells(x,1)(Ax)は同じかを確認して、
            '同じ場合は中のプログラムを実行させます。
            If ws1.Cells(y, 1) = ws2.Cells(x, 1) Then
                    
                    '商品在庫から商品情報へそれぞれ転記
                    ws1.Cells(y, 3) = ws2.Cells(x, 6)
                    ws1.Cells(y, 4) = ws2.Cells(x, 4)
                    
            'ココまでがif文です。
            End If
        
        
        'ココまでws2をチェックするループをさせます。
        Next x
    
    '確認用のメッセージボックス
    MsgBox ws1.Cells(y, 2) & "在庫総数は、" & ws1.Cells(y, 3) & "。秋葉原の在庫は、" & ws1.Cells(y, 4)

    'ココでws1のチェックするループをさせます。
    Next y
    
End Sub

実行結果（ループが分かりやすいようにメッセージボックスをループ中に入れました。）

以下省略・・・

このようにVLOOKUPと同じように情報を確認していき、
条件が合った場合は情報を取得してくる。
とプログラム出来れば一覧を作成する時、申請書を作成する時、
申請書の処理をする時など色々な場面で使えます。

VLOOKUPで問題だった情報比較した左側からは情報が取得できない問題点も
取得してくるセルの場所を指定すれば問題ありません。

今回初めて使用した部分について補足していきます。

    Dim wb As Workbook
    Dim ws1, ws2 As Worksheet

殆どコメントアウトの部分で書いてありますが、
ブック名とシート名を入れる変数の入れ物です。

    Set wb = ActiveWorkbook
    Set ws1 = wb.Sheets("商品情報")
    Set ws2 = wb.Sheets("商品在庫")

こちらもコメントアウトで記載してありますが、
ブック名とシート名を変数に指定しています。
これをしないと毎回プログラム文にsheets("xxxx")と書かないといけなかったり面倒になります。
1回しか出てこない場合などは良いのですが、
複数回出て来たり、様々なブック、シートが存在する場合などは
誰かが別のシートで保存していた場合、別のシートを読み込んでしまう可能性がありますので、指定しておいた方が無難です。

    For x = 2 To row2
        ループの中身
    Next x

ループプログラムです。
今回は変数"x"に2~11を順番に入れていき、
if文を使って2行目から11行目まで同じ商品No.かをチェックさせるという動きをさせました。

ループにはFor文とWhile文の2種類ありますが、
今回は指定の数までループさせるFor文を紹介しました。

    If ws1.Cells(2, 1) = ws2.Cells(x, 1) Then
        If文の中身
    End If

IF文です。
今回は同じ文字列探す目的なので、
比較演算子は"="で結んであります。
ちなみに異なる数字、文字で動かすのであれば
比較演算子は"<>"となります。

また、数字や日付などを比較する際には
"<"　左は右より小さい
"<="　左は右以下
">"　左は右より大きい
"<="　左は右以上
などを使います。
算数で言う、以上、以下、未満のような使い分けと思ってください。

最後にLike演算子というものがあり、
IF文の比較を"Like"で結び、ワイルドカードを混ぜるとあいまい検索にできます。

Sub Like演算子()

    If "ABCDEFGHIJKLMN" Like "*GHI*" Then
        MsgBox "ヒット"
    Else
        MsgBox "ノン"
    End If
    
End Sub

セルに自分の名前以外にも名前が入っている場合、
商品名の一部が同じ場合（文字抽出のリンゴの例等）
そんな時に条件として付けると情報抽出に便利な演算子です。

基本的なVBAを紹介しました。