機能改善VLOOKUP (VLOOKUP2)

説明

組み込み関数 VLOOKUP は、Excel の最も強力な関数の 1 つです。しかし、これには重大な欠点が 1 つあります。それは、テーブル内で最初に出現した目的の値のみ、および右端の列でのみ検出されることです。しかし、最後のものではなく、2 番目、3 番目が必要な場合はどうすればよいでしょうか?

問題の例

次のような処理済みローンのテーブルがあるとします。

たとえば、マイクに対して発行された 3 番目の融資の金額や、ジョンが 2 番目の契約をいつ締結したかを知る必要があります。組み込み関数 VLOOKUP テーブル内で最初に出現する名前のみを検索する方法を知っていますが、役に立ちません。

最初のエントリだけでなく、後続 (N 番目) のエントリも検索する関数を作成しましょう。たとえば、VLOOKUP2 と名付けましょう。

VBA 関数 VLOOKUP2 のコード

メニューを開くサービス - マクロ - エディター Visual Basic 、新しいモジュールを挿入します (menu Insert - Module) を選択し、この関数のテキストをそこにコピーします。

Function VLOOKUP2(Table           As Range, _
                  SearchColumnNum As Integer, _
                  SearchValue     As Variant, _
                  N               As Integer, _
                  ResultColumnNum As Integer)        
  Dim i      As Integer
  Dim iCount As Integer
       
  For i = 1 To Table.Rows.Count
    If Table.Cells(i, SearchColumnNum) = SearchValue Then
      iCount = iCount + 1
    End If
    If iCount = N Then
      VLOOKUP2 = Table.Cells(i, ResultColumnNum)
      Exit For
    End If
  Next i
End Function

Visual Basic エディタを閉じて、Excel に戻ります。

関数ウィザードのユーザー定義カテゴリで、VLOOKUP2 関数を見つけて使用できるようになりました。関数の構文は次のとおりです。

=VLOOKUP2(テーブル ; 列番号_どこで_私たち_検索 ;検索値; エントリ番号 ; どの値を取得するかの列番号 )

つまり、Mike に発行された 3 番目のローンの金額を確認するには、次のように入力する必要があります。

=VLOOKUP2(A2:A19 ; 1 ; "Mike"; 3 ; 4 )

トピックに関する記事: