エクセル 参照先 ワイルドカード

先ほどに続いて失礼します。

「KB10_1A1」という検索値に対して参照先が「KB*_*A1」と
「DZ5」というのに対し「DZ1~32」といった感じで参照値がワイルドカードを使用している場合の
参照方法を探してます。
検索値はSh4-C5、参照値はSh3-G4、返す値はSh3-E4です。
先ほどと同様参照値がひとつのセル内に複数段で入力されております。

こちらは先ほどより難しくお手上げ状態です。
ご教授お願いいたします。

マクロ、関数など方法は問いません

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

• 

  わかりづらくて申し訳ないです。
  ID「KB10_A1」がこの範囲「KB*_*A1」(条件？)で検索したいみたいなことなんですけど...
  KB*_*A2やKB*_*A3などと条件があるので困ってます。
  
  例えばセルC5の値(KB10_A1)の、頭(KB)と尾(A1)で検索(参照)かけたりってできますでしょうか？
  「DZ5」のほうは頭(DZ)で検索できるとは思うのですが...
  頭と尾がつくかどうかも何とかなりそうですが、できるだけ勝手に探してくれる(IF記述少)ものにしたいと思ってます。
  
  自分で考えたのは検索値をひたすらIFで判定しまくるってやつですが、それだと150以上もあほみたいになりまして、PCがふるいのであまり重くすると落ちちゃうのですっきりスリムな方法がないかと思いまして...

  No.1の回答に寄せられた補足コメントです。 補足日時：2016/10/04 08:55

No.3 回答者： WindFaller 回答日時： 2016/10/07 13:19

私の作った関数で、

http://oshiete.goo.ne.jp/qa/9448573.html
の問題を解いてみました。その上で、少し手を加え、完成形とさせていただくことにしました。
---------
元データ
A1234B-1X-BQQ,120-140cm
A2542C-6R-BQQ,120-140cm
A3642D-11X-BQQ,180-200cm
A3642D-1111-B QQ, 180-200 cm
--------------
検索後のデータ
A1234B-1X
A2542C-6R
A3642D-11X
--------------
数式：
=FindRegEx("(A\d{4}\w\-\d{1,2}[XRHF]).*",$A$1:$A$10,,ROW(A1))

() でくくることで、必要な部分だけを取り出すことが可能です。
INDEX関数が不要になりました。

すでに、私の関数コレクション・アドインに加えました。
私は、とても便利なものができたと思っています。

Function FindRegEx(serTxt As String, rng As Range, Optional ig As Boolean = True, Optional idx As Long = 0)
'para1:正規表現, para2:範囲, para3:オプション ig=大文字小文字無視（デフォルト無視),index
　Dim Re As Object
　Dim Ms As Object
　Set Re = CreateObject("VBScript.RegExp")
　Re.Pattern = serTxt
　Re.Global = True
　If ig = False Then
　　Re.IgnoreCase = False
　Else
　　Re.IgnoreCase = True
　End If
　Dim c As Range
　Dim gl As String
　Dim arArea
　Dim i As Long
　ReDim arArea(1 To 30)
　For Each c In rng.SpecialCells(xlCellTypeConstants, 23).Cells
　　If Re.Test(c.Value) Then
　　Set Ms = Re.Execute(c.Value)
　　　i = i + 1
　　　If Ms(0).Submatches.Count > 0 Then
　　　　arArea(i) = Ms(0).Submatches(0)
　　　Else
　　　　arArea(i) = c.Value
　　　End If
　　End If
　Next
　If i > 0 Then
　　ReDim Preserve arArea(1 To i)
　Else
　　arArea(1) = 0
　End If
　If UBound(arArea) >= idx Then
　　FindRegEx = arArea(idx)
　Else
　　FindRegEx = ""
　End If
End Function

No.2 回答者： WindFaller 回答日時： 2016/10/06 07:59

#1の回答者です。

補足で、同じような説明をされても、私のほうとしては、申し訳ありませんが、今の段階で、手を加えるつもりはありません。目的にはかなっていると思うからです。

ただ、正規表現自体は、今回は必須です。

=FindRegEx("KB\d{1,2}_\dA1",$A$1:$H$10)

"KB\d{1,2}_\dA1" 検索値

$A$1:$H$10　検索範囲

結果：KB10_1A1
　　KB12_2A1
　　KB2_1A1

が検出されます。

もちろん、ご質問の前言を翻して、マクロは不可とするなら、それも結構ですが、マクロ以外のお答えをする予定はしておりませんので、あらたに関数の質問を出してください。

>自分で考えたのは検索値をひたすらIFで判定しまくるってやつですが、
そういう部分を見せると相手には良く伝わるはずです。何に対して、どのようにして、結果を得ようとしているか、ワークシートに数式で、このように検出しているのだという、より具体的なイメージを与えるのがよろしかろうと思います。

No.1 回答者： WindFaller 回答日時： 2016/10/03 23:18

ご質問が、わかりにくいです。

>「KB10_1A1」という検索値に対して参照先が「KB*_*A1」と

ワイルドカードの用語に意味を取り違えていませんか？

もし、その質問どおりなら、無理です。逆ではありませんか？

KB*_*A1 という検索値（正規表現なら、KB\d{1,2}_\dA1)
に対して、「KB10_1A1」などが被検索値があるというのではないでしょうか。

>「DZ5」というのに対し「DZ1～32」といった感じで参照値がワイルドカードを使
ではなくて、
「DZ* (正規表現なら DZ\d{1,2})

>検索値はSh4-C5、参照値はSh3-G4、返す値はSh3-E4です。
返す値？それもヘンです。
正規表現なら、Sh\d\-C\d

ということになります。

正規表現自体を理解していないと、話が見えないとは思いますが、簡単ですから、それは勉強してください。

-------------
'標準モジュール

Function FindRegEx(serTxt As String, rng As Range, Optional ig As Boolean = True)
'para1:正規表現, para2:範囲, para3:オプション ig=大文字小文字無視（デフォルト無視)
　Dim Re As Object
　Set Re = CreateObject("VBScript.RegExp")
　Re.Pattern = serTxt
　Re.Global = True
　If ig = False Then
　　Re.IgnoreCase = False
　Else
　　Re.IgnoreCase = True
　End If
　Dim c As Range
　Dim gl As String
　Dim arArea
　Dim i As Long
　ReDim arArea(1 To 30)
　For Each c In rng.SpecialCells(xlCellTypeConstants, 23).Cells
　　If Re.Test(c.Value) Then
　　　i = i + 1
　　　arArea(i) = c.Value
　　End If
　Next
　If i > 0 Then
　　ReDim Preserve arArea(1 To i)
　Else
　　arArea(1) = 0
　End If
　FindRegEx = arArea
End Function


数式例：
=FindRegEx("KB\d{1,2}_\dA1",$A$1:$H$10)

出力は配列ですから、INDEX関数を使い、複数の解を求めます。

=INDEX(FindRegEx("sh\d\-C\d",$A$1:$H$10),ROW(A1))
ドラッグコピーします。

大文字・小文字を分ける場合 (Falseが入る)
=INDEX(FindRegEx("sh\d\-C\d",$A$1:$H$10,FALSE),ROW(A2))