エクセルで別シートに含まれる文字列から文字列が含まれる隣のセルの値を返す

質問です。
説明がわかりにくく申し訳ありません。

エクセルのシート1にC:C列にある文字列を抽出し、その文字列の中から特定の文字列があればシート2の表から日本語を返したい。

例：シート1
　　　　　　A　　 　　 B 　　　　 C　　　 D
1　　　　　 　　 aa/bbbb/ 5
2　　　　　 　　 ccc/ddd/ 8
3　　　　　 　　 ee/ff/ 0
4　　　　　 　　 gggg/hhhhh/ 6

シート2
　　　　　　A　　 　　 B 　　　　 C　　　 D
1　　　　　 bbbb リンゴ
2　　　　　 ddd　　　　　みかん
3　　　　　 ff いちご
4　　　　　 hhhhh ぶどう

シート3
　　　　　　A　　 　　 B 　　　　 C　　　 D
1　　　　　 リンゴ 5
2　　　　　 みかん　　　 8
3　　　　　 いちご 0
4　　　　　 ぶどう 6

シート3に集計表を作りたいのですが、シート1のc:c列にbbbbがあれば
リンゴにbbbbが含まれる文字列の隣のセルの数値を返したいのです。
※シート3のA列のリンゴ・みかんなどは元から固定して入力済みです。
シート1に外部からのデータを張り付け特定の文字列が含まれる隣のセルの数値を取得したいです。

説明が大変下手で申し訳ありませんが、どうぞよろしくお願いします。

No.5 ベストアンサー 回答者： tom04 回答日時： 2016/07/21 14:58

No.４です。

Sheet1の配置が間違っていました。
↓のような配置なのですね？

前回のコードは消去し、↓のコードに変更してください。

Sub Sample2()
Dim i As Long, c As Range, r As Range
Dim wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
With Worksheets("Sheet3")
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
Set c = wS2.Range("B:B").Find(what:=.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Set r = wS1.Range("C:C").Find(what:=c.Offset(, -1), LookIn:=xlValues, lookat:=xlPart)
If Not r Is Nothing Then
.Cells(i, "B") = r.Offset(, 1)
End If
End If
Next i
End With
Application.ScreenUpdating = True
MsgBox "完了"
End Sub

今度はどうでしょうか？m(_ _)m

この回答へのお礼

tom04　様

ありがとうございます。
希望の形にすることが出来ました。
下手くそな説明にも関わらず、ご理解頂きありがとうございました。
また、丁寧なご説明も感謝いたします。

また、ほかの方もご協力いただきまして誠にありがとうございました。

お礼日時：2016/07/21 18:10

No.4 回答者： tom04 回答日時： 2016/07/21 14:05

こんにちは！

VBAになりますが、一例です。
↓の画像のような配置になっているという前提です。
Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から//
Dim i As Long, c As Range, r As Range, myAry As Variant
Dim wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
With Worksheets("Sheet3")
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
Set c = wS2.Range("B:B").Find(what:=.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Set r = wS1.Range("C:C").Find(what:=c.Offset(, -1), LookIn:=xlValues, lookat:=xlPart)
If Not r Is Nothing Then
myAry = Split(r, "/")
.Cells(i, "B") = myAry(UBound(myAry))
End If
End If
Next i
End With
Application.ScreenUpdating = True
MsgBox "完了"
End Sub 'この行まで//

※　各シート名は実際のシート名にしてください。m(_ _)m

No.3 回答者： 銀鱗 回答日時： 2016/07/21 12:44

VLOOKUP関数

元データのシート（Sheet1）
　　A　　B　　C　　　　D
１　-　　-　black/dog 　1匹　
２　-　　-　white/cat 　 3匹
３　-　　-　yellow/bird　6羽
４　-　　-　blue/bird　　2羽
５　-　　-　hot/dog　　 3匹
６　-　　-　fish　　　　 1尾

変換表（Sheet2）
　　A　　B　　C　　D
１　dog　犬　
２　cat　 猫　
３　bird　鳥　

変換して表示するシート（Sheet3）
　　A　　B　　C　　D
１　犬　1匹　
２　猫　3匹
３　鳥　6羽
４　鳥　2羽
５　犬　3匹
６　fish 1尾　←変換できなかった例

こんな感じにしたいのでしょうか。


No.1に続いてもう一つアドバイス。
元データのシートのC列に入る文字は必ずスラッシュ（/）で区切られ、その区切られた文字を検索するのであれば、
「区切り位置」機能でセルを分けることを強く勧めます。
そうすることで後の処理が簡単になります。

この回答へのお礼

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

まさにおっしゃる通りでございます。

一度区切る事も考えたのですが、c列の文字列が

/プードル/柴犬/犬
/ゴールデンレトリバー/パグ/ポメラニアン/犬/
/三毛猫/黒猫/猫

など、スラッシュの区切り数がそれぞれ異なる為諦めました。
※上記の犬や猫であれば、名前が重複しますが、実際は重複文字はありません。

お礼日時：2016/07/21 13:05

No.2 回答者： d-q-t-p 回答日時： 2016/07/21 11:51

要領を得ない質問に予測で回答していると 質問者の文章力がいつまで経っ

ても上がらない気がするので 本来は返事を待つべきなんでしょうけど

=SUMIF(Sheet1!C:C,"*"&INDEX(Sheet2!A:A,MATCH(A1,Sheet2!B:B,0))&"*",Sheet1!D:D)

違うようなら もうちょっと順序立てて補足し直して下さい。

この回答へのお礼

粗末な文面で申し訳ございません。

①エクセルのSheet1に外部からのデータをAlt+Aで全選択し、値のみで貼付けを行います。
貼付けた結果からSheet１のC列に抽出したデータが約1万行、D列にそのデータの値が同数あります。

/c/apple/ 16(2.44%)
/c/orange/ 12(1.83%)
/c/strawberry/ 10(1.53%)
/c/grape/ 9(1.37%)

②上記のデータから/apple/や/orange/を含む文字列をまず日本語に返したいです。
apple＝りんご
orange＝みかん　など

③上記で取得したデータを

りんご　16(2.44%)
みかん　12(1.83%)
いちご　10(1.53%)

と、まとめたいです。

私がエクセルを理解していない為、伝わらず申し訳ございません。

お礼日時：2016/07/21 12:52

No.1 回答者： 銀鱗 回答日時： 2016/07/21 11:29

＞シート1にC:C列にある文字列を抽出し

具体的にどういうことでしょう。
どのシートのC列の値をSheet1へ何と比較して抽出するのかを明確にしましょう。

＞その文字列の中から特定の文字列があればシート2の表から日本語を返したい。
これも具体的にどうい事でしょう。
どのような対比になっている表なのかを明確にしてください。

・・・
”エムズ69”さんは数学がとても苦手だったのではないでしょうか。
順序立てて説明することが得意ではないようです。
説明が下手と自身で仰っていますが、この点を直すことで自身の考えをまとめ問題点を明確にすることができ、自己解決に導くことができます。

とりあえず、
・VLOOKUP関数の使い方と、MATCH関数とINDEX関数の組み合わせて使う方法の違い。（値の抽出）
・FIND関数とSEARCH関数の違い。（文字列中の検索）
・別シートのセルの参照方法。
の３つを調べて使いこなせるようにしましょう。

この回答へのお礼

ご指摘ありがとうございます。
エクセルのSheet1に外部からのデータをAlt+Aで全選択し、値のみで貼付けを行います。
貼付けた結果からSheet１のC列に抽出したデータが約1万行あります。

上記のデータから特定の文字列を含む値と、その横のセルの値を抽出したいです。

また、Sheet1のC列にある特定の値が全て英数の為、まずはbbbbはリンゴ、dddはみかんなど日本語に変換し、且つリンゴ　5　みかん　8と導きたいです。

bbbb=りんご、ddd＝みかん
など、決まっております。

ご指摘の通り数学が苦手且つ、パソコンはもっと苦手の為読みずらい文面になっている為大変申し訳ございません。
どうしても仕事で必要な為初めてご質問をさせて頂きました。

もし宜しければご回答を頂けるとありがたいです。

まだまだ不明点もあるかと思いますので、よろしければ質問してください。

それではよろしくお願い致します。

お礼日時：2016/07/21 12:03