複数の値で検索するユーザー定義関数「XLOOKUP」
Excelには「VLOOKUP」というワークシート関数があります。
VLOOKUP(検索値,範囲,列番号,検索の型)
このように引数に検索値を指定します。検索値に指定できる値は一つだけです。
そこで、検索値としてカンマ区切りなどで複数の値を指定して検索するユーザー定義関数「XLOOKUP」を作ってみました。
検索結果が複数ある場合、カンマ区切りの文字列が返されます。
VBA
Function XLOOKUP(mystr As String, myrange As Range, mycol As Long, Optional delimiter = ",")
Dim x As Long
Dim t, parts() As String
Dim one As Variant
parts = Split(mystr, delimiter)
t = ""
For x = 1 To myrange.Rows.Count
For Each one In parts
If myrange.Cells(x, 1).Value = one Then
If t <> "" Then
t = t & delimiter
End If
t = t & myrange.Cells(x, mycol).Value
Exit For
End If
Next
Next x
If t = "" Then
t = "#N/A"
End If
XLOOKUP = t
End Function
Dim x As Long
Dim t, parts() As String
Dim one As Variant
parts = Split(mystr, delimiter)
t = ""
For x = 1 To myrange.Rows.Count
For Each one In parts
If myrange.Cells(x, 1).Value = one Then
If t <> "" Then
t = t & delimiter
End If
t = t & myrange.Cells(x, mycol).Value
Exit For
End If
Next
Next x
If t = "" Then
t = "#N/A"
End If
XLOOKUP = t
End Function
書式
XLOOKUP(検索値,範囲,列番号,区切り文字)
検索値 範囲の左端の列で検索する値を指定します。
範囲 2列以上のデータ列です。範囲を指定します。範囲の左端の列の値が、検索値で検索される値です。
列番号 範囲内で目的のデータが入力されている列を、左端からの列数で指定します。列番号に1を指定すると、範囲の左端の列の値が返され、列番号に2を指定すると、範囲の左から2列目の値が返されます。
区切り文字 検索値として「a,b」や「x/y/z」など区切り文字を含んだ文字列を指定することができます。省略可能です。省略した場合「,」が指定されます。
※VLOOKUPと違い、検索の型は指定しません。FALSEに似た動作をします。
使用例
次のような範囲について、数式をセットします。
| A | B | |
|---|---|---|
| 1 | code | name |
| 2 | a | Apple |
| 3 | b | Banana |
| 4 | c | Chocolate |
| 5 | d | Donut |
| 数式 | 結果 |
|---|---|
| =XLOOKUP("c",$B$2:$C$6,2) | Chocolate |
| =XLOOKUP("a,b",$B$2:$C$6,2) | Apple,Banana |
| =XLOOKUP("a/c/d",$B$2:$C$6,2,"/") | Apple/Chocolate/Donut |
[ 2012年10月23日 | カテゴリー: Excel | タグ: VBA , 関数 ]
« 世界四大ミス・コンテスト | 容量の大きいダンボール »
コメントを残す