![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
通常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
No.4ベストアンサー
- 回答日時:
なるほど!
>Functionプロシージャーを利用した処理を1000回
それで、こちらに結びつくわけですね。
>VBAコード内で、使用できる関数処理があれば、ご教示いただければと思います。
速いか遅いかという問題を、関数とVBAで解決したいという伏線があるわけですよね。スピードはいかがでしょうか。何か、自分の回答という実感がわからないです。普段は、こういうコードは書かないせいだと思います。この先のスピードは、オブジェクトに出し入れしている限りは、遅いとも言われますが、やってみなければわかりませんね。
見た感じ、Test1 のほうが、収まりがよいようです。しかし、解が、2次元配列なので、それを1次元にしたのが、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
No.3
- 回答日時:
残念ながらお望みの関数は標準では難しそうです。
やはり、ユーザ定義関数での実装が無難でしょう。あまり参考にはならないかもしれませんが、単純なワイルドカードの比較で良いのであれば、こんな感じで書いた方が早いですよ。
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使ってのコードの単純化はできたものの、
残念ながら処理時間はあまり変わりませんでした。
難しいですね・・・
No.1
- 回答日時:
こんにちは。
おなじような質問が、別のハンドルの人が出ていた気がしますが、このような内容なら、私も分かります。私のように、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), 縦横可能ですが、1列、1行に限ります。
'//
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は何かと便利ですね。なかなか発想が出てきません。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセル折れ線グラフについて...
-
エクセル IF計算式?でしょうか?
-
【マクロ】for nest について ...
-
EXCELで数値が異なった数字を足...
-
Excelについて。Excelに縦1列に...
-
エクセル、LibreOffice Calcプ...
-
エクセルを使用して、円周率を...
-
エクセルの数式バーのフォント...
-
【マクロ】マクロが割当てされ...
-
エクセルでファイルの最終更新...
-
【マクロ】AブックからBブック...
-
index関数の説明をお願いします。
-
Excelで時間の計算の方法 7時45...
-
【Excel】Powerクエリーの取り...
-
エクセルマクロを教えてほしい...
-
エクセルのセルの書式ユーザー...
-
エクセルで曜日を入れたい
-
【マクロ】アクティブセルの、...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルの数式バーのフォント...
-
エクセルでファイルの最終更新...
-
2列に入っているデータを1列...
-
データチェックを行うエクセル...
-
再質問です。マクロの修正箇所...
-
エクセルで 自動的に◯や数字を...
-
数字入力後他の文字等が表示さ...
-
Excel 小さくなったスクロール...
-
F9キーについて。
-
【ExcelVBA】ダブルクォーテー...
-
Excelに詳しい方! B列が「日...
-
セルの数を求めたい
-
Excelを無料で使うには? パソ...
-
IFとIFS関数
-
【Excel】別シートから条件に合...
-
EXCELの散布図で日付が1900年に...
-
ある表にフィルターをかけて出...
-
【ExcelVBA】名前を付けて保存→...
-
Excelセルを跨いで合計を出す方法
-
エクセルのツールバーから数値...
おすすめ情報
各位、ご回答ありがとうございます。
勝手ながらワークシート内処置としては一先ず解決とさせていただき、
VBAコード内で、使用できる関数処理があれば、ご教示いただければと思います。
よろしくお願いいたします。