検索＆置換関数についてご教授ください！！

質問

Excel2010使用です。エクセルマスターの方々下記値を返す関数をご教授ください！！
（※マクロではなく関数にて処理したいです）

【データ(1)】
A列
1行目：アップル_オレンジ_バナナ_レモン
2行目：オレンジ_レモン_アップル_バナナ
・
・
・
というようなアンダーバー区切りの文字列セル(最大30ほど）が10,000件あります。

別データの【データ(2)】
A列（項目名）　　　B列（ID値）
アップル　　　　　　　　1
オレンジ　　　　　　　　2
バナナ　　 　　　　　　15
レモン　　　　　　　　123
・
・
・
こちらは300件ほどのデータでID値は1～3ケタです。

行いたい処理は、「データ(1)の文字列内に含まれるワードがデータ(2)のA列と同じ場合
データ(2)のB列（ID値）を返して
「1_2_15_123」
「2_123_1_15」
と置換したいです。

締切り間近で困っております。どうかお助けください。

回答No.2

kagakusuki · ベストアンサー

今仮に、Sheet1に【データ(1)】があり、Sheet2に【データ(2)】があるものとします。
　そして、Sheet2においては、A1セルには「項目名」、B1セルには「ID値」という具合に、1行目には項目名が入力されていて、実際のデータは2行目以下に入力されているものとします。
　又、置換後の文字列はSheet1のB列に表示させるものとします。
　又、Sheet3を作業用シートとして使用するものとします。

　まず、Sheet3のA1セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(FIND("_"&INDEX(Sheet2!$A:$A,COLUMNS($A:A)+ROW(Sheet2!$A$1))&"_","_"&INDEX(Sheet1!$A:$A,ROW())&"_")),INDEX(Sheet2!$A:$A,COLUMNS($A:A)+ROW(Sheet2!$A$1))<>"",INDEX(Sheet2!$B:$B,COLUMNS($A:A)+ROW(Sheet2!$B$1))<>""),"_"&INDEX(Sheet2!$B:$B,COLUMNS($A:A)+ROW(Sheet2!$B$1)),"")&B1

　次に、Sheet3のA1セルをコピーして、Sheet3のA1セルよりも右側にあるセル範囲に対して、貼り付け先のセル範囲の列数が、Sheet2の表の行数を十分に上回る列数となる様に貼り付けて下さい。
　次に、Sheet3の1行目全体をコピーして、Sheet3の2行目以下に（Sheet1の行数を上回るのに十分な行数となる様に）貼り付けて下さい。

　次に、Sheet1のB1セルに次の関数を入力して下さい。

=REPLACE(INDEX(Sheet3!$A:$A,ROW())&"",1,1,)

　次に、Sheet1のB1セルをコピーして、Sheet1のB2以下に貼り付けて下さい。

　以上です。

お礼

＞kagakusuki様

ご回答有難うございます！
やはり関数で処理しようとすると難解な式になるのですね。。。
でも理解はできました。置換処理もこちらの方法で上手くいきましたので
データ作成が間に合いそうです。
本当にありがとうございました！！

回答No.3

kagakusuki

回答No.2です。
　申し訳御座いません、御質問の内容を少々勘違いをしておりました。
　回答No.2の方法では、単に元の文字列中に、データ(2)に該当する文字列が含まれていた場合に、データ(2)の該当するIDを、データ(2)の表の上に入力されているものから順番に並べて表示させる様にしてしまっております。
　従いまして、例えば「オレンジ_レモン_アップル_バナナ」の場合には、「2_123_1_15」とはならずに、「1_2_15_123」となってしまいます。
　ですから、正しくはSheet3のA1セルに入力する関数を次のものと差し換えて下さい。（Sheet1のB1セルに入力する関数は、そののまま同じもので構いません）

=IF(COLUMNS($A:A)>LEN(INDEX(Sheet1!$A:$A,ROW()))-LEN(SUBSTITUTE(INDEX(Sheet1!$A:$A,ROW()),"_",))+(INDEX(Sheet1!$A:$A,ROW())<>""),"","_"&IF(COUNTIF(Sheet2!$A$2:$A$999,MID(INDEX(Sheet1!$A:$A,ROW()),FIND(CHAR(1),SUBSTITUTE("_"&INDEX(Sheet1!$A:$A,ROW()),"_",CHAR(1),COLUMNS($A:A))),FIND(CHAR(1),SUBSTITUTE(INDEX(Sheet1!$A:$A,ROW())&"_","_",CHAR(1),COLUMNS($A:A)))-FIND(CHAR(1),SUBSTITUTE("_"&INDEX(Sheet1!$A:$A,ROW()),"_",CHAR(1),COLUMNS($A:A))))),VLOOKUP(MID(INDEX(Sheet1!$A:$A,ROW()),FIND(CHAR(1),SUBSTITUTE("_"&INDEX(Sheet1!$A:$A,ROW()),"_",CHAR(1),COLUMNS($A:A))),FIND(CHAR(1),SUBSTITUTE(INDEX(Sheet1!$A:$A,ROW())&"_","_",CHAR(1),COLUMNS($A:A)))-FIND(CHAR(1),SUBSTITUTE("_"&INDEX(Sheet1!$A:$A,ROW()),"_",CHAR(1),COLUMNS($A:A)))),Sheet2!$A$2:$B$9,2,FALSE)&"","《ID無し》"))&B1

お礼

＞kagakusuki様

ご返答が遅くなり申し訳ございません。
追加のご回答有難うございます。
承知しました。大変に助かりました。

回答No.1

tom04

こんばんは！
＞（※マクロではなく関数にて処理したいです）
とありますが、
かなりの難題だと思います。
理由として
（1）区切り位置で区切っても行によってデータ数がバラバラである。
（2）関数で行う場合は一気に！という訳にはいかないと思いますので、作業用の別Sheetを使用する必要がありそう。
（3）仮に関数で出来たとしても長々とした関数になると思います。
ご自身で数式を訂正しようとしてもどこを訂正してよいかわからなくなる。
（4）データが10000件ある。

以上のコトを考慮するとマクロ向きの質問だと思います。
（関数で簡単にできる方法があればごめんなさい。）

差し迫っていらっしゃるようなので、とりあえず結果だけお望みであれば
VBAになってしまいますが、コードの一例を載せておきます。

↓の画像のように【データ1】は左側のSheet1にあり、【データ2】は右側のSheet2のような配置だとします。

Alt+F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）
Sheet1のデータは1行目からあるとします。

Sub test() 'この行から
Dim i As Long
Dim k As Long
Dim m As Long
Dim buf As String
Dim myArray As Variant
Dim wS1 As Worksheet
Dim wS2 As Worksheet
Set wS1 = Worksheets("Sheet1") '←「Sheet1」は実際のSheet名に！
Set wS2 = Worksheets("Sheet2") '←「Sheet2」も実際のSheet名に！

Application.ScreenUpdating = False
On Error Resume Next
For i = 1 To wS1.Cells(Rows.Count, 1).End(xlUp).Row
If InStr(wS1.Cells(i, 1), "_") > 0 Then
myArray = Split(Cells(i, 1), "_")
buf = ""
For k = 0 To UBound(myArray)
m = WorksheetFunction.Match(myArray(k), wS2.Columns(1), False)
buf = buf & wS2.Cells(m, 2) & "_"
Next k
wS1.Cells(i, 2) = Left(buf, Len(buf) - 1)
Else
wS1.Cells(i, 2) = WorksheetFunction.VLookup(wS1.Cells(i, 1), wS2.Columns("A:B"), 2, False)
End If
Next i
wS1.Columns(2).AutoFit
Application.ScreenUpdating = True
MsgBox "処理が完了しました。"
End Sub 'この行まで

※　お望みの方法ではないと思いますが、
この後、関数での回答も何通りか出てくるかと思います。
当面の結果だけをご希望であれば
参考にしてみてください。m(_ _)m

お礼

＞tom04様
早急にご回答いただきありがとうございます！！
マクロは詳しくないので長文になっても関数でなんとかならないかと思ったのですが
やはり無理がありますよね。。

いただいたコードを使用してマクロ実行してみたところ、上手くいきました！
ご丁寧に有難うございます！！大変助かりますm(__)m

関数についての回答をもう少しだけ待ってみますが、マクロの方が良さそうであれば
こちらで処理させていただきます。