Excel:シート間参照し置換したい

質問

初心者です。異なるシート間における表を参照し、置換の
ような事がしたいのですが、ＶＬＯＯＫＵＰが使えるのでしょうか？
やりたいのは、下記の通りです。

Sheet1のような表があります。
Sheet2のＡ列を参照し、Sheet1のＢ列を変換し、
Sheet3のような結果を得たいのです。
近似値もできるみたいなのでＶＬＯＯＫＵＰを使おうと
思ったのですが、「含む」は論理値でどう指定すればいいか
分からず困っています。
そもそもＶＬＯＯＫＵＰでできるかも疑問ですし...
教えてください、よろしくお願いします。

Sheet1
Ａ列　　　　　Ｂ列　　　　　

12345　　　　りんご、みかん、イチゴ
67891　　　　いんげん
23456　　　　チョコ、あめ
67890　　　　みかん、りんご
56789　　　　あめ

Sheet2
Ａ列　　　　　Ｂ列

りんご　　　　くだもの
みかん　　　　くだもの
いちご　　　　くだもの
いんげん　　 野菜
チョコ　　　　おかし
あめ　　　　　おかし

Sheet3
Ａ列　　　　　Ｂ列　　　　　

12345　　　　くだもの
67891　　　　野菜
23456　　　　おかし
67890　　　　くだもの
56789　　　　おかし

回答No.3

noname#52504 · ベストアンサー

これも残念ながら一筋縄ではいかないと思います。
前回のご質問もそうですが、そもそも「一つのセルに複数のデータ」というのが、
Excelの基本的な考え方としてはイレギュラーなので、処理も難しくなってしまいますね。
（まぁ、そうせざるを得ない状況もあるんですが…）

さて。

Sheet3のA列については、
　Sheet3!A1：=Sheet1!A1
として相対参照で素朴に引けば解決ですね。

問題はB列ですが…

Ａ案　Sheet1のB列を[区切り位置]でばらしてから、VLOOKUPで一致検索

　１．Sheet1のB列を選択する。
　２．データ＞区切り位置＞[カンマやタブなど…]＞次へ
　　　[その他]にチェックを入れる＞[その他]の枠内に読点"、"を入力する＞次へ
　　　[表示先]にC1を指定＞完了
　　　　これで、Sheet1のC列以降にデータが一つずつ入りますから、
　　　　後は、普通にVLOOKUPを重ねるだけです。
　３．Sheet3!B1：=VLOOKUP(VLOOKUP(A1,Sheet1!$A$1:$C$99,3,0),Sheet2!$A$1:$B$99,2,0)

　おそらくこれが普通の処理です。

Ｂ案　Sheet2を、A列を基準に昇順で並べ替えてから、VLOOKUPで近似検索

　１．Sheet2のA:B列を選択する
　２．データ＞並べ替え＞最優先されるキー：A列，昇順＞
　　　オプション＞[ふりがなをつかわない]＞OK＞OK
　３．Sheet3!B1：=VLOOKUP(VLOOKUP(A1,Sheet1!$A$1:$B$99,2,0),Sheet2!$A$1:$B$99,2,1)

　挙動が直感的に判り難いので、トラブルが起こりそうな気がします。
　お勧めしません。

Ｃ案　Sheet1のB列の最初のデータを数式で切り出して、VLOOKUPで一致検索

　１．Sheet1!C1：=IF(ISERROR(FIND("、",B1)),B1,LEFT(B1,FIND("、",B1)-1))
　２．Sheet3!B1：=VLOOKUP(VLOOKUP(A1,Sheet1!$A$1:$C$99,3,0),Sheet2!$A$1:$B$99,2,0)

　これも、まぁ、よく見る処理です。

Ｄ案　数式一発でやってみる

　１．Sheet3!B1：=INDEX(Sheet2!$B$1:$B$99,MATCH(FALSE,ISERROR(FIND(Sheet2!$A$1:$A$99,VLOOKUP(A1,Sheet1!$A$1:$B$99,2,0))),0))
　　　を配列数式として入力。
　　　※通常の数式は、入力した後Enterキーで確定しますが、
　　　　配列数式を入力するときは、CtrlとShiftを押しながらEnterで確定します。
　　　略解
　　　　VLOOKUPでIDから対応するグループを引く。
　　　　⇒FINDでSheet2!A列の各行の値をグループから探して、見つかるかどうかを判定
　　　　⇒MATCHでエラーでない（値を含む）ものの行位置を取得
　　　　⇒INDEXで対応する行位置のデータを取得　

　「Sheet2にりんごがない場合でもみかんを見つけてくだものを返す」スグレものですが、
　ある程度慣れた人でないと、実際の運用は難しいかもしれません。

長乱文陳謝

お礼

やっぱり関数だと一筋縄にいかないですか。
マクロの勉強始めましたが、なかなかで...
1つのセルに複数あるデータの数がとても多いので、
Ｄ案でやってみました！でも、これは私が応用できるか
が問題ですね...
いつもありがとうございます。感謝です！

回答No.2

noname#95859

参考です。
Sub Macro1()
Dim r As Integer, i As Integer
Dim rowpos As Integer
Dim myItemNo As Double
Dim myItem As String
Dim myAnswer As String
Dim Lookuptable As Variant

Lookuptable = Worksheets("Sheet2").Cells(1, 1).CurrentRegion.Value 'sheet2の情報をメモリ上に持ってくる
r = UBound(Lookuptable, 1) '行数を得る

rowpos = 1
With Worksheets("Sheet1")
Do While .Cells(rowpos, 1).Value <> ""
myItemNo = .Cells(rowpos, 1).Value
myItem = .Cells(rowpos, 2).Value
myAnswer = ""
For i = 1 To r
If InStr(myItem, Lookuptable(i, 1)) > 0 Then '初めての分類名のみ処理する
If InStr(myAnswer, Lookuptable(i, 2)) = 0 Then
If myAnswer = "" Then myAnswer = Lookuptable(i, 2) Else myAnswer = myAnswer & "," & Lookuptable(i, 2)
End If
End If
Next
If myAnswer = "" Then myAnswer = "該当なし"
Worksheets("Sheet3").Cells(rowpos, 1).Value = myItemNo
Worksheets("Sheet3").Cells(rowpos, 2).Value = myAnswer
rowpos = rowpos + 1
Loop
End With
End Sub

お礼

できました！うれしいです、ありがとうございます♪

回答No.1

n-jun

Excel:条件付き文字列の結合方法
http://okwave.jp/qa3304401.html
こちらの逆バージョンと言う事でしょうか。

シート１及びシート２の１行目は項目名があり、
シート３にはデータは無記入であるとした時、
シート３の２行目以降に置換した結果をだします。

Sub Test2()
　Dim Dic As Object
　Dim r2 As Range, r3 As Range
　Dim v As Variant, vv As Variant
　Dim key As Variant
　Dim i As Integer
Set Dic = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1")
　　　v = .Range(.[A2], .Cells(Rows.Count, "B").End(xlUp)).Value
　　　For i = 1 To UBound(v, 1)
　　　　　Dic(v(i, 1)) = Split(v(i, 2), "、")
　　　Next
End With
With Sheets("Sheet2")
Set r2 = .Range(.[A2], .Cells(Rows.Count, "B").End(xlUp))
End With
Set r3 = Sheets("Sheet3").Range("A2")
　For Each key In Dic.keys
　　　With Application
　　　　　vv = .Index(r2, .Match(Dic.Item(key)(0), r2.Columns(1), 0), 2)
　　　End With
　　If IsError(vv) Then
　　　　r3.Value = key
　　　　r3.Offset(, 1).Value = "該当なし"
　　Else
　　　　r3.Value = key
　　　　r3.Offset(, 1).Value = vv
　　End If
　Set r3 = r3.Offset(1)
　Next
End Sub

シート３のＡ列に既にデータがあるような場合、
質問内容を勘違いしているかも？
その時は、すいません。

補足

さっそく、ご回答いただきありがとうございます。
できました！ありがとうございます！！
ただ、実は、マクロがまったく分からなくて．．．
今回の分はこれでできそうですが、列の行が増えたり、ずれたり
したものやじゃっかん内容の違うものもやることになりそうで、
その時の応用が効かなくて。。。
関数などで行う方法がありますでしょうか？
ご存知でしたら、教えてください！