検索対象にワイルドカードを使用した Vlookup, Match 類似の関数はないか

通常Vlookupや、Match関数では検索条件にワイルドカードを使用できますが、
逆に、検索対象にワイルドカードを利用できるような関数はないでしょうか？
例）
A1="AB*"
A2="C*"
A3="*C"
=match("ABC",A1:A3,0)=1
=match("CAB",A1:A3,0)=2
=match("BDC",A1:A3,0)=3
=match("AAA",A1:A3,0)=エラー（下のコードでは "0"）
競合する場合は先にヒットした結果を返します。

VBAにて Functionプロシージャーは組みましたが、
順にHitするか確認する繰り返し処理をするためか、
対象が多いとPCによって処理時間に倍ほど差が出てしまいました。
（別のプロシージャー内でこのFunctionプロシージャーを利用した処理を1000回ほどLoopしている。
　　PC A で10数秒、PC Bで20数秒）
もともとExcelに装備されている関数があれば利用したいと考えています。
'-----------------------------------------------------------------------------------
Private Function xMatch(stg As String, rng As Range) As Long
Dim n As Long
Dim buf As Variant

For n = 1 To rng.Rows.Count
buf = Application.Match(rng(n, 1), Array(stg), 0)
If Not IsError(buf) Then Exit For
Next n

If IsError(buf) Then
xMatch = 0
Else
xMatch = n
End If

buf = Empty

End Function

質問者からの補足コメント

• 

  各位、ご回答ありがとうございます。
  勝手ながらワークシート内処置としては一先ず解決とさせていただき、
  VBAコード内で、使用できる関数処理があれば、ご教示いただければと思います。
  
  よろしくお願いいたします。

    補足日時：2016/10/15 21:04

No.4 ベストアンサー 回答者： WindFaller 回答日時： 2016/10/16 02:05

なるほど!

>Functionプロシージャーを利用した処理を1000回
それで、こちらに結びつくわけですね。

>VBAコード内で、使用できる関数処理があれば、ご教示いただければと思います。

速いか遅いかという問題を、関数とVBAで解決したいという伏線があるわけですよね。スピードはいかがでしょうか。何か、自分の回答という実感がわからないです。普段は、こういうコードは書かないせいだと思います。この先のスピードは、オブジェクトに出し入れしている限りは、遅いとも言われますが、やってみなければわかりませんね。

見た感じ、Test1 のほうが、収まりがよいようです。しかし、解が、2次元配列なので、それを１次元にしたのが、Test1Revised

'//
Sub Test1()
　Dim c As Variant
　Dim ret1
　Dim i As Long
　Dim buf As String
　Application.ScreenUpdating = False
　For Each c In Range("B1:B24")
　　ret1 = Application.CountIf(c, Range("A1:A3"))
　　For i = 1 To UBound(ret1)
　　　If ret1(i, 1) <> 0 Then
　　　　buf = buf & " " & i
　　　End If
　　Next
　　c.Offset(, 1).Value = Replace(Trim(buf), " ", ",")
　　buf = ""
　Next
　Application.ScreenUpdating = True
End Sub
'//

Sub Test1Revised()
　Dim c As Variant
　Dim ret1
　Dim i As Long
　Dim buf As String
　Dim crit
　Application.ScreenUpdating = False
　crit = Application.Transpose(Range("A1:A3").Value)
　For Each c In Range("B1:B24")
　　 ret1 = Application.CountIf(c, crit)
　　For i = 1 To UBound(ret1)
　　　If ret1(i) <> 0 Then
　　　　buf = buf & " " & i
　　　End If
　　Next
　　c.Offset(, 1).Value = Replace(Trim(buf), " ", ",")
　　buf = ""
　Next
　Application.ScreenUpdating = True
End Sub
'//

Sub Test2()
　Dim c As Variant
　Dim ret2
　Dim buf As String
　Application.ScreenUpdating = False
　For Each c In Range("B1:B24")
　　ret2 = Evaluate("Transpose(COUNTIF(" & c.Address(0, 0) & ",A1:A3)*{1;2;3})")
　　buf = Join(ret2, ",")
　　buf = Replace(buf, "0,", "")
　　buf = Replace(buf, ",0", "")
　　c.Offset(, 2).Value = Trim(buf)
　　buf = ""
　Next
　Application.ScreenUpdating = True
End Sub

この回答へのお礼

回答ありがとうございます。
テストに時間がかかって返答が遅くなりました。

いただいた内容も参考にしながらいろいろと検討してみましたが、
以下に記述するようなコードに落ち着きました。
が、何れもほとんどスピードは変わりませんでした。

実際のコードではほかの記載もありますので
的外れにそちらで時間を食っている、、、のかもしれません。
（申し訳ありませんが本コードの記載はご容赦ください）

しかしながら初期目標であったExcel VBA内の関数によるコードの記載
については達成できたと思います。

ありがとうございました。

'-----------------------
Public Function zMatch(target As Range, list As Range) As Long
'target 検索対象文字列
'list 検索条件文字列（ワイルドカード含む文字列）のリスト
Dim buf As Variant
　buf = Application.Match(1, Application.CountIf(target, list), 0)

　If IsError(buf) Then
　　zMatch = 0
　Else
　　zMatch = buf
　End If
End Function

お礼日時：2016/10/18 23:53

No.3 回答者： ママチャリ 回答日時： 2016/10/15 20:42

残念ながらお望みの関数は標準では難しそうです。

やはり、ユーザ定義関数での実装が無難でしょう。
あまり参考にはならないかもしれませんが、単純なワイルドカードの比較で良いのであれば、こんな感じで書いた方が早いですよ。

Function yMatch(stg As String, rng As Range) As Long
Dim n As Long
For n = 1 To rng.Rows.Count
If stg Like rng(n) Then
yMatch = n
Exit For
End If
Next n
End Function

この回答へのお礼

回答ありがとうございます。
ご指摘のLike使ってのコードの単純化はできたものの、
残念ながら処理時間はあまり変わりませんでした。
難しいですね・・・

お礼日時：2016/10/16 23:01

No.2 回答者： CoalTar 回答日時： 2016/10/14 12:15

計算速度はわかりかねます

=IF(B1="","",MATCH(1,MATCH($A$1:$A$3,B1,0),0))
[Ctrl]+[Shift]+[Enter]配列数式、{}で囲まれる。
下へオートフィル

エラー表示を組み込んでみた場合
=IF(B1="","",IFNA(MATCH(1,MATCH($A$1:$A$3,B1,0),0),"エラー"))

この回答へのお礼

回答ありがとうございます。

match + matchでも可能なんですね。
参考にさせていただきます。

お礼日時：2016/10/15 20:55

No.1 回答者： WindFaller 回答日時： 2016/10/13 09:25

こんにちは。

おなじような質問が、別のハンドルの人が出ていた気がしますが、このような内容なら、私も分かります。私のように、VBAとかがんじがらめになっているせいか、すぐには分からず、やっと了解しました。

これは、見かけ上は、ワイルドカードを検索するようですが、原理としては、表計算のChoose関数、/VBA関数のSwitch関数　Filter関数　の考え方と似ています。また、実際には、COUNTIF関数で代用が利くはずです。

これを実際には、表計算上でどのように使うのか、私は見当がつきませんが。
しかし、このようなユーザー定義関数自体のスピードは、速いとは言い難いでしよう。ユーザー定義関数を使うなら、実際には、プロシージャマクロに取り込んでしまったほうがよいと思います。

>Excelに装備されている関数があれば利用したい
これに関しては、引数の範囲だけですね。

例えば、
A10に、ABC と置き、後は、同じ条件で、

=COUNTIF(A10,A1:A3)
配列の確定（Shift+Ctrl - Enter)
をすれば、配列ですが、解は取り出せます。

=IFERROR(SMALL(INDEX(COUNTIF($A$10,$A$1:$A$3)*ROW($A$1:$A$3),,),ROW(A1)),"")

で下にドラッグすれば、個々の数字は取り出せます。

ユーザー定義関数(関数名はセンスが悪いかもしれません)
（わざわざ、引数をParamArrayにする必要はないかもしれませんが、Range型と文字列型両方を想定しました。)
=myCHOOSE("CBC","AB*","C*","*C")
=myCHOOSE("CBC",A1:A3), 縦横可能ですが、１列、１行に限ります。

'//
Function myChoose(idxValue As String, ParamArray arg() As Variant)
Dim i As Long: i = 1
Dim j As Long: j = 1
Dim x(), a, t As Long
Dim buf
Dim ans()
For Each a In arg()
　 If TypeName(a) = "Range" Then
　　 x = Application.Transpose(a)
　　 '1次元にする
　　 On Error Resume Next
　　　t = UBound(x, 2)
　　 On Error GoTo 0
　　 If t > 0 Then
　　　 x = Application.Transpose(x)
　　 End If
　 Else
　　 ReDim Preserve x(i)
　　　x(i) = a
　　　i = i + 1
　 End If
Next
ReDim ans(UBound(x))
For i = 1 To UBound(x)
　 If StrConv(idxValue, vbUpperCase) Like StrConv(x(i), vbUpperCase) Then
　　　ans(j) = i
　　　j = j + 1
　 End If
Next i
buf = Trim(Join(ans, " "))
buf = Replace(buf, Space(1), ",")
myChoose = buf
End Function
'//

こちらも、配列のままにすれば、CountIf関数とおなじになります。

この回答へのお礼

回答ありがとうございます。

関数の方参考にさせていただき、
{=match(1,countif(A10,A1:A3),0)}
で取り出せました。
countifは何かと便利ですね。なかなか発想が出てきません。

お礼日時：2016/10/15 20:53