Excelで、複数の文字列を検索し、該当するものがあれば一気に置換したい

Question

Excelで困っています(>_<) 今、A列に観光地のデータがあります。＜A列＞大理石村ロックハート城善光寺東京ドイツ村護国寺東武ワールドスクエア金閣寺レオマワールド日光東照宮スペースワールド平等院鳳凰堂ハウステンボス首里城宮崎シーガイア B列のデータには、県名も記載されたテーマパークのデータがあります。＜B列＞大理石村ロックハート城(群馬県) 東京ドイツ村(千葉県) 東武ワールドスクエア(栃木県) レオマワールド(香川県) スペースワールド(福岡県) ハウステンボス(長崎県) 宮崎シーガイア(宮崎県) A列には数千にも及ぶ観光地のデータがあり、B列には、県名込みの、テーマパークデータのみが入っています。この状態から、A列にあるテーマパークのデータ全てに、B列と同じように、県名を加えたいのですが…とりあえず私は、B列から＜C列＞大理石村ロックハート城東京ドイツ村東武ワールドスクエアレオマワールドスペースワールドハウステンボス宮崎シーガイア C列(県名が入力されていないテーマパークのデータ全て)を作成し、「A列の中に、C列のデータに該当する文字列があれば、全てB列の文字列に置換する」というやり方でなんとかしようと思い、substitute関数かな？等と考えたり、いろいろ試行錯誤をしてみたのですが、全然うまく置換できません(ToT) 皆様のお知恵をお借りしたい次第です。よろしくお願いします

hige_082 · Accepted Answer

＃４です
＃４のお礼読みました

For文ですが、
＞大量のデータを簡単に指定できる？
ちょっと違うかな、リンク先にもあるように
同じ処理を繰り返す時に使用する
と言う方が理解しやすいと思います

＞終端セルを参照する(Endプロパティ)↓
＞「xlUp」を「xlDown」にすることも、可能なのでしょうか？
リンク先にもありますが、可能です

＞Instrの後ろ、Thenの手前にある「>0」というのは、一体何を意味しているのでしょうか？
＞●Instr
＞ある文字列の中から指定した文字列を検索し、最初に見つかった文字位置(先頭からその位置までの文字数)を返す文字処理関数。
にあるように
先頭からその位置までの文字数が数値での返ってきます
例えば
大理石村ロックハート城と大理石村ロックハート城(群馬県)を
instrで比較した場合は　１が返ってきます
大理石村ロックハート城(群馬県)とロックハートの場合は　５が
大理石村ロックハート城(群馬県)と東京ドイツ村の場合は　０が返ってきます
これをIF文にセットして　０の場合は処理をしない、０より大きい場合は処理をするようにしています

こんな説明で分かるかな？

DOUGLAS_ · Answer

[回答番号：No.3] の DOUGLAS_ です。

=INDIRECT("B" & MATCH(A1,C:C,0))
・・・(※１)
について解説いたします。

　先ず、
=MATCH(A1,C:C,0)
ですが、[MATCH] 関数の書式は
MATCH(検査値,検査範囲,照合の型)
となっておりますので、「A1 と全く同じ値をC列から検索し、あればC列での行番号、なければ #N/A を返す」という意味になります。

　従って、「A1 と全く同じ値」を持つ「C列」の値の「セル番地」は
"C" & MATCH(A1,C:C,0)
ということになりますが、そもそもC列の値は、B列の値から「（***）」の部分を削除しただけのものですから、dj-s さんが
＞A列の中に、C列のデータに該当する文字列があれば、全てB列の文字列に置換する
とお書きの「B列の文字列」の「セル番地」が
"B" & MATCH(A1,C:C,0)
ということになります。

　「セル番地」を参照して、その値を取り出すのは [INDIRECT] 関数ですから、
=INDIRECT("B" & MATCH(A1,C:C,0))
としました。


＞DOUGLASさんが書いてくださった式は、参照形式が省略されていることに気づくのに、時間がかかりました(汗)
　エクセルの計算式で「引数」を省略するのはよくあることです。
　都度、ヘルプをご覧になることをお薦めいたします。


＞Ａ列にはあってＢ列にはない観光地の場合
＞#N/A
＞が返ります。
というのがお気に召さない場合は、
=IF(ISNA(MATCH(A1,C:C,0)),A1,INDIRECT("B" & MATCH(A1,C:C,0)))
となります。

　もし「ISNA(MATCH(A1,C:C,0))」なら「A1」を返し、そうでなければ(※１)の値を返します。

＞もし「ISNA(MATCH(A1,C:C,0))」なら
につきましては、「#N/A」を返すのは [MATCH] 関数が検索に失敗したからですから、その部分を指定して [ISNA] 関数で検索の結果を判別し、それに応じて「元々のA列の値（A1）」を返すか、「県名を加えた値(※１)」を返すのかを選択します。

　ちなみに、この際、あえて「#N/A」を返させておいて、B列の末尾にデータを補いつつ、すべてのデータに県名を加えるという考え方もできますが、この場合は(※１)の式のままで作業します。

sekkii · Answer

これでいいのかな？
Ａ列、Ｂ列はそのままで
Ｃ１セルに
=IF(ISERROR(VLOOKUP(A1 &"*",B:B,1,FALSE)),A1,VLOOKUP(A1 &"*",B:B,1,FALSE))
で下にオートフィル。

hige_082 · Answer

マクロなら簡単なのに

'標準モジュールへ
Sub test()
Dim i, ii
For i = 1 To Cells(65336, 1).End(xlUp).Row
For ii = 1 To Cells(65536, 2).End(xlUp).Row
If InStr(Cells(ii, 2), Cells(i, 1)) > 0 Then
Cells(i, 1) = Cells(ii, 2)
Exit For
End If
Next ii
Next i
End Sub

Ｃ列は要りません
Ａ列、Ｂ列のみがあればよい
関数が希望なら、飛ばしてね

DOUGLAS_ · Answer

=INDIRECT("B" & MATCH(A1,C:C,0))
でいけるかと存じますが、Ａ列にはあってＢ列にはない観光地の場合
#N/A
が返ります。

web2525 · Answer

あ、ちょっと質問を取り違えていました。

C1セルに先ほどの計算式を入れて下方向にフィルすると、県名が表記された一覧が出来上がるのでそれをコピーして値のみで貼り付けるって言う方法になります。

web2525 · Answer

=IF(ISERROR(MATCH(A1,LEFT($B$1:$B$7,LEN(A1)),0)),A1,INDEX($B$1:$B$7,MATCH(A1,LEFT($B$1:$B$7,LEN(A1)),0)))

取り合えずこんな感じで
配列計算になっているのでArt+Ctrl+Enterで確定してください

Excelで、複数の文字列を検索し、該当するものがあれば一気に置換したい

＃４です

[回答番号：No.3] の DOUGLAS_ です。

これでいいのかな？

マクロなら簡単なのに

=INDIRECT("B" & MATCH(A1,C:C,0))

あ、ちょっと質問を取り違えていました。

=IF(ISERROR(MATCH(A1,LEFT($B$1:$B$7,LEN(A1)),0)),A1,INDEX($B$1:$B$7,MATCH(A1,LEFT($B$1:$B$7,LEN(A1)),0)))

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング