アクセス上位ページ
 
最近の更新内容

Excel VBA ≫ 6.ワークシート関数 - 特徴と使用例(1)-f.ワークシート関数による検索処理

【ページ内リンク】

ワークシート関数による検索処理

このページではある値の検索を行う MatchVLookup 関数について説明します。

ともに指定されたセル範囲から任意の値を探すを関数ですが、マッチ時に返却されるものが異なります。

  • Match 関数 ・・・ マッチしたセルの相対行を返却
  • VLookup 関数 ・・・ マッチしたセルと同じ行で異なる列の値を返却




主な使用用途としては、Match 関数はある値が存在している位置の取得や、値が存在しているかどうかの存在チェックVlookup 関数はキーに対応する値を取得する連想配列としての使い方が挙げられます。 これらは様々な場面で要求される処理ですので、ぜひ使い方をマスターしておきたいところです。

検索関数を使用する際、注意すべき点がいくつかあります。
  • 検索時に大文字、小文字は区別されません。"abc"と"ABC"は同じものとみなされ、マッチしてしまいます。
  • 検索タイプを"近似値検索"にした場合、検査範囲を昇順または降順でソートしておく必要があります。
  • 検索タイプを"完全一致"にした場合、検索する値が存在しないと以下のようなエラーが発生し、処理が停止してしまいます。
    1004         WorksheetFunction クラスの *** プロパティを取得できません。
    
    (***には関数名が入ります。)

    この状況を回避する方法として、 On Error ステートメント を使用する方法があります。 これは、検索関数実行後にエラーコードをチェックし、上記"1004"のエラーが発生していた場合は値が見つからなかったとみなして、 処理を継続する方法です。
    (詳細は後述の使用例を参照してください。)
上記の点に注意しながら、うまく使用してください。


Match 関数

Match 関数は、検査範囲内で見つかった検査値の相対位置を返します。 第3引数により、検索タイプ("完全一致検索"、"近似値検索")を選択することができます。

  • 【構文】
    WorksheetFunction.Match( 検査値 , 検査範囲 [, 照合の型] )

      【戻り値】 倍精度浮動小数点型 (Double)


  • 引数名 省略 説明
    検査値 × 検索する値を指定します。
    検査範囲 × 検索対象のセル範囲(レンジオブジェクト)または配列を指定します。
    照合の型 -1:近似値(検査値以上の最小値)を検索。検査範囲を降順にソートしておく必要あり。
    0:完全に一致する値のみを検索。検査範囲をソートしておく必要なし。
    1:近似値(検査値以下の最大値)を検索。検査範囲を昇順にソートしておく必要あり。
    (省略時は 1 )


  • Match 関数による"完全一致検索"の使用例
  • 以下のマクロは、インプットボックスより入力された店舗番号について、売上表の店舗番号列に存在するかどうか Match 関数でチェックを行う例です。 店舗番号が存在する場合はその相対位置と行を表示します。
    Sub sample_wf016_01()
        Dim mise As String
        Dim sRng As Range
        Dim str As String
        Dim i As Long
        Dim r As Long
    
        '検査値の入力受付
        mise = ""
        Do Until mise <> ""
            mise = InputBox("店舗番号を入力してください。" & vbLf & _
                            "※大文字と小文字は区別されません。")
            'キャンセルのチェック
            If StrPtr(mise) = 0 Then Exit Sub
        Loop
    
        '検査範囲
        Set sRng = Range("A4:A13")
    
        'エラーが発生しても処理を続行する
        On Error Resume Next
    
        '完全一致検索
        i = orksheetFunction.Match(mise, sRng, 0)
    
        'エラー発生時の処置
        If Err.Number = 1004 Then
            '一致する値がない場合
            i = 0
            Err.Clear   'エラーをクリアして処理続行
    
        ElseIf Err.Number > 0 Then
            '上記以外のエラーが発生した場合
            MsgBox "エラー発生" & vbLf & _
                    Err.Number & ":" & Err.Description, vbCritical
            Exit Sub
        End If
    
        str = "店舗番号『" & mise & "』"
        If i = 0 Then
            MsgBox str & "は登録されていません。", vbExclamation
        Else
            r = sRng.Row + i - 1    '相対位置から行を計算
            MsgBox str & i & "は番目(" & r & "行目)に登録されています。", _
                   vbInformation
        End If
    End Sub
    
    ⇒ サンプルマクロ集 へ


    <データ> 全店舗1〜3月分売上表
    • 存在する店舗番号を入力した場合
    • 存在する店番号を入力 存在している位置を表示
    • 存在しない店舗番号を入力した場合
    • 存在しない店番号を入力 存在しない旨を表示


VLookup 関数

VLookup 関数は、検査範囲内で見つかった検査値と同じ行にある値を返します。 検査範囲内の左端列が検索の対象となり、返却する値の列はこの左端列からの相対位置で指定します。
第3引数により、検索タイプ("完全一致検索"、"近似値検索")を選択することができます。
第3引数を省略すると"近似値検索"になります。"完全一致検索"を行う場合は第3引数を設定し忘れないように、十分注意してください。
  • 【構文】
    WorksheetFunction.VLookup( 検査値 , 検査範囲 , 列番号 [, 検査の型] )

      【戻り値】 バリアント型 (Variant)


  • 引数名 省略 説明
    検査値 × 検索する値を指定します。
    検査範囲 × 検索対象のセル範囲(レンジオブジェクト)を指定します。値を取得するための列が必要になるので、最低2列以上の範囲を指定する必要があります。
    列番号 × 検査値が見つかった場合に値を取得するめの列番号を指定します。列番号は検査範囲内の左端列を1とした相対値で指定します。
    検査の型 True:近似値(検査値以下の最大値)を検索。検査範囲を昇順にソートしておく必要あり。
    False:完全に一致する値のみを検索。検査範囲をソートしておく必要なし。
    (省略時は True )


  • VLookup 関数による"完全一致検索"の使用例
  • 以下のマクロは、インプットボックスより入力された店舗番号の3月分売上を VLookup 関数で取得して表示する例です。
    Sub sample_wf016_02()
        Dim mise As String
        Dim sRng As Range
        Dim str As String
        Dim uri As Long
    
        '検査値の入力受付
        mise = ""
        Do Until mise <> ""
            mise = InputBox("店舗番号を入力してください。" & vbLf & _
                            "※大文字と小文字は区別されません。")
            'キャンセルのチェック
            If StrPtr(mise) = 0 Then Exit Sub
        Loop
    
        '検査範囲
        Set sRng = Range("A4:D13")
    
        'エラーが発生しても処理を続行する
        On Error Resume Next
    
        '完全一致検索
        uri = WorksheetFunction.VLookup(mise, sRng, 4, False)
    
        'エラー発生時の処置
        If Err.Number = 1004 Then
            '一致する値がない場合
            uri = 0
            Err.Clear   'エラーをクリアして処理続行
    
        ElseIf Err.Number > 0 Then
            '上記以外のエラーが発生した場合
            MsgBox "エラー発生" & vbLf & _
                    Err.Number & ":" & Err.Description, vbCritical
            Exit Sub
        End If
    
        str = "店舗番号『" & mise & "』"
        If uri = 0 Then
            MsgBox str & "は登録されていません。", vbExclamation
        Else
            MsgBox str & "の3月分の売上は" & uri & "万円です。", _
                   vbInformation
        End If
    End Sub
    
    ⇒ サンプルマクロ集 へ


    <データ>
    全店舗1〜3月分売上表
    • 存在する店舗番号を入力した場合
    • 存在する店番号を入力 入力された店舗番号の3月分売上を表示
    • 存在しない店舗番号を入力した場合
    • 存在しない店番号を入力 存在しない旨を表示

  • VLookup 関数による"近似値検索"の使用例
  • 以下のマクロは、商品リストからある日付時点における商品価格を VLookup 関数により取得する例です。
    Sub sample_wf016_03()
        Dim sCode As String
        Dim sDate As String
        Dim sKey As String
        Dim var As Variant
        Dim sRng As Range
        Dim str As String
        Dim code As String
        Dim price As Long
    
        '検査値の入力受付
        sCode = ""
        sDate = ""
        Do Until sCode <> "" And IsDate(sDate)
            sCode = InputBox("商品コードと日付を" & _
                             "カンマ区切りで入力してください。" & _
                             vbLf & _
                             "※大文字と小文字は区別されません。")
    
            'キャンセルのチェック
            If StrPtr(sCode) = 0 Then Exit Sub
            '入力値をカンマで分割
            var = Split(sCode, ",")
            If UBound(var) = 1 Then
                sCode = Trim(var(0))
                sDate = Trim(var(1))
            End If
        Loop
    
        '検索用キー
        sKey = sCode & "-" & Format(CDate(sDate), "yyyymmdd")
    
        '検査範囲
        Set sRng = Range("A3:D8")
    
        'エラーが発生しても処理を続行する
        On Error Resume Next
    
        '近似値検索では入力値と異なる商品コードの行と取得してしまう
        '可能性があるため、まずは商品コードをチェック
        code = WorksheetFunction.VLookup(sKey, sRng, 2, True)
    
        If StrComp(code, sCode, vbTextCompare) = 0 Then
            '商品コードがマッチしていたら近似値検索で価格を取得
            price = WorksheetFunction.VLookup(sKey, sRng, 4, True)
        Else
            '商品コードまたは適用開始日が登録されていない
            price = 0
        End If
    
        'エラー発生時の処置
        If Err.Number = 1004 Then
            '一致する値がない場合
            price = 0
            Err.Clear   'エラーをクリアして処理続行
    
        ElseIf Err.Number > 0 Then
            '上記以外のエラーが発生した場合
            MsgBox "エラー発生" & vbLf & _
                    Err.Number & ":" & Err.Description, vbCritical
            Exit Sub
        End If
    
        If price = 0 Then
            MsgBox "『" & sCode & "』の" & sDate & _
                    "時点における価格は登録されていません。", _
                    vbExclamation
        Else
            MsgBox "『" & sCode & "』の" & sDate & _
                    "時点における価格は" & price & "円です。", _
                    vbInformation
        End If
    End Sub
    
    ⇒ サンプルマクロ集 へ


    <データ>
    商品リストには商品コード(B列)とその価格(D列)、そして価格の適用開始日(C列)があります。 同一商品コードに複数の適用開始日がある場合、価格は同一行の適用開始日から次行の適用開始日前日まで有効とします。 VLookup 関数による"近似値検索"を行うため、商品コードと価格の適用開始日を数式で結合したもの(日付はフォーマット変換しています)を検索キー(A列)とし、 商品リストはこのキーの昇順に並べられています。 商品価格表
    • 商品コード『S010』、日付『2014/12/31』を入力した場合
    • 商品『S010』、日付『2015/1/1』を入力 合致した行の価格を表示
    • 商品コード『S010』、日付『2015/1/1』を入力した場合
    • 商品『S010』、日付『2014/12/31』を入力 検査値以下の最大値に対応する価格を表示
    • 商品コード『S001』、適用開始前の日付『2000/9/30』を入力した場合
    • 商品『S010』、日付『2000/9/30』を入力 キーが存在しない旨を表示
    • 存在しない商品コード『S100』を入力した場合
    • 商品『S001』を入力 キーが存在しない旨を表示

<サイト内関連リンク> 関連リンクもチェック!!
- ワークシート関数を使用するメリット
カテゴリ内ページ



Page
Top