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

質問

Excelで困っています(>_<)
今、A列に観光地のデータがあります。

＜A列＞
大理石村ロックハート城
善光寺
東京ドイツ村
護国寺
東武ワールドスクエア
金閣寺
レオマワールド
日光東照宮
スペースワールド
平等院鳳凰堂
ハウステンボス
首里城
宮崎シーガイア

B列のデータには、県名も記載されたテーマパークのデータがあります。

＜B列＞
大理石村ロックハート城(群馬県)
東京ドイツ村(千葉県)
東武ワールドスクエア(栃木県)
レオマワールド(香川県)
スペースワールド(福岡県)
ハウステンボス(長崎県)
宮崎シーガイア(宮崎県)

A列には数千にも及ぶ観光地のデータがあり、B列には、県名込みの、テーマパークデータのみが入っています。

この状態から、A列にあるテーマパークのデータ全てに、B列と同じように、県名を加えたいのですが…とりあえず私は、B列から

＜C列＞
大理石村ロックハート城
東京ドイツ村
東武ワールドスクエア
レオマワールド
スペースワールド
ハウステンボス
宮崎シーガイア

C列(県名が入力されていないテーマパークのデータ全て)を作成し、「A列の中に、C列のデータに該当する文字列があれば、全てB列の文字列に置換する」というやり方でなんとかしようと思い、substitute関数かな？等と考えたり、いろいろ試行錯誤をしてみたのですが、全然うまく置換できません(ToT)
皆様のお知恵をお借りしたい次第です。
よろしくお願いします<m(__)m>

回答No.6

hige_082 · ベストアンサー

＃４です
＃４のお礼読みました

For文ですが、
＞大量のデータを簡単に指定できる？
ちょっと違うかな、リンク先にもあるように
同じ処理を繰り返す時に使用する
と言う方が理解しやすいと思います

＞終端セルを参照する(Endプロパティ)↓
＞「xlUp」を「xlDown」にすることも、可能なのでしょうか？
リンク先にもありますが、可能です

＞Instrの後ろ、Thenの手前にある「>0」というのは、一体何を意味しているのでしょうか？
＞●Instr
＞ある文字列の中から指定した文字列を検索し、最初に見つかった文字位置(先頭からその位置までの文字数)を返す文字処理関数。
にあるように
先頭からその位置までの文字数が数値での返ってきます
例えば
大理石村ロックハート城と大理石村ロックハート城(群馬県)を
instrで比較した場合は　１が返ってきます
大理石村ロックハート城(群馬県)とロックハートの場合は　５が
大理石村ロックハート城(群馬県)と東京ドイツ村の場合は　０が返ってきます
これをIF文にセットして　０の場合は処理をしない、０より大きい場合は処理をするようにしています

こんな説明で分かるかな？

お礼

higeさんが書いてくださったマクロをそのままコピペして実行したら、うまくいきました(>_<)

Instrも、なんとなくですが理解できました。「大理石村ロックハート城」「大理石村ロックハート城(群馬県)」は、“大”が1文字目に見つかるから1、1>0なので、その場合はCells(i, 1) ＝Cells(ii, 2)の処理を実行せよということですね。

For文も、下↓の解説と一緒に考え、なんとか理解できました。
http://excelfactory.net/inukouza/inu06.htm

iとiiの動きは、 Cells(65336, 1)、Cells(65536, 2)で指定されているのですね。

そして、higeさんが書いてくださったマクロを、

If InStr(Cells(ii, 2), Cells(i, 1)) > 0 Then
Cells(ii, 2) = Cells(i, 1)

に変更してみると、なぜかB列のデータが、A列に変更されてしまいました(驚)

If InStr(Cells(i, 1), Cells(ii, 2)) > 0 Then
Cells(ii, 2) = Cells(i, 1)

や、

If InStr(Cells(i, 1), Cells(ii, 2)) > 0 Then
Cells(i, 1) = Cells(ii, 2)

に変更すると、何も起きなかったです。
「対象文字列」と「検索文字列」↓
http://homepage2.nifty.com/pasocon/nyumon/instr.html
というのが関係しているんですかね、この点はまだ謎ですが・・・

いやはや、貴重なお時間でご指導いただき、本当にありがとうございます<m(__)m>

回答No.7

DOUGLAS_

[回答番号：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列の末尾にデータを補いつつ、すべてのデータに県名を加えるという考え方もできますが、この場合は(※１)の式のままで作業します。

お礼

なるほど理解できました！
B列とC列が同じ行番号であることを生かして、INDIRECT関数を使うのですね(>_<)
最初戸惑いましたが、

=MATCH(A1,C:C,0)

だけでオートフィルしてみると「54」等の値が、

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

だけでオートフィルしてみると「B54」等の値が出てきたので、そこでようやく理解できました。このセル番地「B54」を参照するのが、INDIRECT関数の役目なのですね～ISNAの使い方もなんとなくわかりました。
ISERRORと似ていますよね。

いやはや、二回も回答していただき本当にありがとうございました<m(__)m>

回答No.5

sekkii

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

お礼

できました！
私にとって複雑な関数でしたので、理解に時間がかかりましたが、オートフィルをしていくと、C列に期待通りの文字列が表示されました！
ありがとうございます(>_<)

回答No.4

hige_082

マクロなら簡単なのに

'標準モジュールへ
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

Ｃ列は要りません
Ａ列、Ｂ列のみがあればよい
関数が希望なら、飛ばしてね

お礼

マクロは全然知らないのですが・・・でもC列とかいらないんですか・・・すごいですね！
と思い、今日勉強してみました。
マクロは「Sub」で始まり「End Sub」で終わる1つのVBAプログラムだと。このプログラムの1単位を「プロシージャ」と呼ぶ。

から始まり、higeさんが記述してくださった文字列を少しずつ調べていきました。

●Dim
変数(データを格納しておく「入れ物」のようなもの)の宣言を示すキーワード
●Cells
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_cell.html#cells
●If...Then...Else ステートメント
If 条件式 Then
条件が成り立つときに実行する処理
Else
条件が成り立たなかったときに実行する処理
End If
●Instr
ある文字列の中から指定した文字列を検索し、最初に見つかった文字位置(先頭からその位置までの文字数)を返す文字処理関数。

ここまではなんとか調べあげ、なんとなく理解したつもりなのですが、「For文」「End(xlUp).Row」「> 0」の3つの箇所に関して、まだ理解することができません(ToT)

For文はNext文とセットにする↓
http://msft.ems.okayama-u.ac.jp/vba/chapter2.htm
ことで、大量のデータを簡単に指定できる？

という感じに今は解釈しているのですが、そのFor文に続く「End(xlUp).Row」というのに、また疑問が湧きました。

終端セルを参照する(Endプロパティ)↓
http://www.moug.net/tech/exvba/0050088.htm

というページを参考にすると、たぶん「End(終端から)、xlup(↑の方向に移動しろ)、Row(行の)」ということで、行の「65336」番目から上のデータをすべて指定している、という考えに至ったのですが、正しいでしょうか？

そうすると、もしかしてこの箇所は、「xlUp」を「xlDown」にすることも、可能なのでしょうか？

また、Instrの後ろ、Thenの手前にある「>0」というのは、一体何を意味しているのでしょうか？

いろいろ質問してしまってほんとにすいません・・・もし、差し支えなければ、再度ご回答いただければ幸いです(>_<)

回答No.3

DOUGLAS_

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

お礼

INDIRECT(参照文字列 , 参照形式)
らしいですね。

DOUGLASさんが書いてくださった式は、参照形式が省略されていることに気づくのに、時間がかかりました(汗)

確かに希望通りの文字列が表示されたのですが、式の意味が理解できません(ToT)

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

というのは、B列を参照し、かつ、C列の中からA1にMATCHするものを探し、もしMATCHすれば・・・う～ん、よくわかりません・・・差し支えなければ、式の意味を解説していただけないでしょうか？

回答No.2

web2525

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

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

回答No.1

web2525

=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で確定してください

お礼

返信遅くなってすいません(>_<)
即レスありがとうございます<m(__)m>

配列計算というのは、よく知らないのでなかなか理解できませんでして…(汗)

特にINDEX関数のところは、ココの使用例４↓
http://www.kenzo30.com/ex_kisotyu/ex_ks_tyukyu9_9_7.htm

等を参考にしてみたのですが、まだ頭を悩ませています(悶)
まず、IF関数とISERROR関数が組み合わさっているので、

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))

を返すと、解釈して大丈夫でしょうか？