【エクセル】特定の文字列を含むセルを検索する方法を教えてください

質問

エクセルで以下のようなことを行いたいのですが、どの様な方法で行ったらよいのかわかりません。
どなたか詳しい方がいらっしゃいましたらお教え下さい。

(1)sheet1のＡ列に町レベルまでの住所が入っています。
（例）Ａ1　a1県b1市c1町
　　　Ａ2　a2県b2市c2町
　　　Ａ3　a3県b3市c3町
　　（以下続く）

(2)sheet2のＡ～Ｄ列にエリアで分かれた複数の市のリストがあります。
（例）Ａ1　北エリア／Ｂ1　東エリア／Ｃ1　南エリア／Ｄ1　西エリア
　　　Ａ2　n1市　　／Ｂ2　e1市　　／Ｃ2　s1市　　／Ｄ2　w1市
　　　Ａ3　n2市　　／Ｂ3　e2市　　／Ｃ3　s2市　　／Ｄ3　w2市
　　（以下続く）

(3)sheet2の2行目以降にある各市の名称でsheet1の"b市"に検索をかけ、それがsheet2の1行目にある何エリアかを区分し、その結果をsheet1のＢ列に反映する。
（例）Ａ1　a1県b1市c1町／Ｂ1　北エリア　　⇒　b1 = n2　など
　　　Ａ2　a2県b2市c2町／Ｂ2　東エリア　　⇒　b2 = e3　など
　　　Ａ3　a3県b3市c3町／Ｂ3　西エリア　　⇒　b3 = w256など
　　（以下続く）

今までは置換機能やフィルタ機能を用いてまずは"b市"をより分けた上で手作業を行ってきたのですが、時間がかかるので関数もしくはマクロで出来るようにしたいと考えています。

拙い説明で恐縮ですがお分かりになる方がいらっしゃいましたら是非ともお教えいただければと思います。よろしくお願いします。

回答No.9

imogasi · ベストアンサー

VBAで
Sheet1　　B列が実行結果
A列　　　　　　　B列
a1県b1市c1町 南エリア
a2県b2市c2町 北エリア
a3県b3市c3町 西エリア
a4県b4市c4町 東エリア
a5県b5市c5町 南エリア
a6県b6市c6町 東エリア
Sheet2
A-D列
北エリア 東エリア 南エリア 西エリア
b2市 b4市 b1市 b3市
n2市 b6市 s2市 b7市
n3市 b9市 b5市 b8市
ーー
コード
Sub test01()
Dim sh1, sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
d = sh1.Range("a65536").End(xlUp).Row
For i = 1 To d
a = sh1.Cells(i, "A")
b = Mid(a, InStr(a, "県") + 1, InStr(a, "市") - InStr(a, "県"))
'MsgBox b
Set x = sh2.Range("A1:D50").Find(b) '市名をシート２で検索
y = x.Column '列番号をとる
sh1.Cells(i, "B") = sh2.Cells(1, y) 'その列の第１行目をとる
Next
End Sub
ーー
Sheet2のデータは５０行までと仮定ー＞Range("A1:D50").
結果
Sheet1のB列
ーー
こんなとこか。質問の意味を少し誤解していても、少し修正すれば使えるのでは。

お礼

なるほど。スマートで良い形ですね。
"市"だけでなく"郡"もあるのでその部分を工夫してみます。
ありがとうございます！

回答No.8

tom04

こんにちは！
参考になるかどうか判りませんが・・・

↓の画像のように表を作ってみました。

作業用の列をSheet1のC列に設けさせてもらいました。
元データの○○県より後の3文字を検索対象としましたので
Sheet2の表内も3文字のみ入力しておきます。

Sheet1の作業列C2セルに
=MID(A2,4,3)
としてオートフィルで下へコピーします。

そして、B2セルに
=IF(A2="","",IF(COUNTIF(Sheet2!$A$2:$D$4,C2),INDEX(Sheet2!$A$1:$D$1,,MAX(INDEX((Sheet2!$A$2:$D$4=Sheet1!C2)*COLUMN(Sheet1!A:D),))),"不明"))

という数式を入れ、オートフィルで下へコピーすると
画像のような感じになります。

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

お礼

ご回答ありがとうございます！
先に挙げていただいた方にもmid関数を使ったものがあったかと思うので合わせて検討してみます！

回答No.7

tarinko_06

No.5です。
エラーの原因は「Sheet1がない」
もしくは「sheet1がアクティブになってない」のどちらかだと
思います。
selectはシートがアクティブになってないとエラーになるってのは
参考書とかにはあんまり乗ってないんじゃないかと思います。
こちらの不手際です。ごめんなさい。
selectにしたのは、まあ99%ないとは思ったんですが
結合セルがあった時にselectなしだとエラーになるからです。

下の[1]～[3]で直ると思います。こちらでは動きはしたんで。

１．「Sheet1がない」の対処
with sheet1　→　with worksheets("住所があるシート名")
sheet1とWorkSheets("Sheet1")では意味が違います。
同じ様にsheet2もworksheets("エリアがあるシート名")に変えてください。
WorkSheets("シート名")の短所はマクロ知らない人でも
シート名を変えられるので、それでエラーになる可能性がある点です。
sheet1の方はコードを貼った所の左上から変えられます。

２．「sheet1がアクティブになってない」の対処１
.Range(.Cells(InpSta, InpCOL), .Cells(InpEnd, InpCOL)).Offset(0, 1).Select
Selection.ClearContents
を
.Range(.Cells(InpSta, InpCOL), .Cells(InpEnd, InpCOL)).Offset(0, 1).ClearContents
に変える

３．「sheet1がアクティブになってない」の対処２
エラーの一つ前に「.activate」を入れる
２、３両方に言えるんですが
sheet1,sheet2が全く関係ないシートだと動きはするけど
正常な結果は返ってきません。

あと、ついでなんですが↓のも修正した方がいいかもです。
これでエリアの初期位置をヘッダがある"A1"にしても
明細が始まる"A2"にされても問題ないはずです。

RowSTA = .Range(エリア_START).CurrentRegion.Row
ColSTA = .Range(エリア_START).CurrentRegion.Column

もし、正常に動けば次からはオートシェイプや図などを用意して
それに右クリックからマクロの登録を選べば
ボタン一つで処理出来る様になるはずです。

お礼

今回はとりあえずエクセル式で出来ることがわかったので、そちらで作業を行いつつ頂いた式を見ながらマクロを使っていきたいと思います。
重ねて丁寧にご説明いただきありがとうございました！

回答No.6

sige1701

こんな感じでもいいかな
=IF(SUMPRODUCT(COUNTIF(A3,"*"&Sheet2!$A$3:$D$5&"*"))=1,INDEX(Sheet2!$2:$2,SUMPRODUCT(COUNTIF(A3,"*"&Sheet2!$A$3:$D$5&"*")*COLUMN(Sheet2!$A$3:$D$5))),"不明")

補足

実はエリアごとの市郡の数がバラバラのため、エリアによってはリストに空欄ができてしまうのです。
いただいた式ですとその改変も比較的簡単で済みました。
ご回答いただきありがとうございます。

回答No.5

tarinko_06

マクロだったらこんな感じ。
Currentregionは間に空白レコードがあると
そこでデータが途切れるんで気をつけて。
***ここをいじって***って所を弄ればセル位置が変わっても対処出来ます。

[ツール]→[マクロ]→[Visual Basic Editor]
→[挿入]→[標準モジュール]→下記コードを貼付ける

貼り付けたらＥＸＣＥＬに戻って保存
[ツール]→[マクロ]→[マクロ]→[実行]→処理開始
もしくは
[表示]→[ツールバー]→[Visual Basic]→マクロ実行ボタン押す
もしくは
コードを貼った画面でF5

'************　ここのアドレスをいじって！　******************
Public Const 住所_START As String = "A1"
Public Const エリア_START As String = "A1"
'************************************************************

Sub エリア検索()
Dim x As Long
Dim y As Long
Dim z As Long
Dim RowSTA As Long
Dim ColSTA As Long
Dim RowEnd As Long
Dim ColEnd As Long
Dim InpSta As Long
Dim InpEnd As Long
Dim InpCOL As Long
Dim strArea As String
Dim strGrp As String

Application.ScreenUpdating = False
With Sheet1
InpSta = .Range(住所_START).Row
InpEnd = .Range(住所_START).End(xlDown).Row
InpCOL = .Range(住所_START).Column
.Range(.Cells(InpSta, InpCOL), .Cells(InpEnd, InpCOL)).Offset(0, 1).Select
Selection.ClearContents
End With

With Sheet2
RowSTA = .Range(エリア_START).Row
ColSTA = .Range(エリア_START).Column
RowEnd = .Range(エリア_START).CurrentRegion.Rows.Count
ColEnd = .Range(エリア_START).CurrentRegion.Columns.Count
For y = ColSTA To ColEnd
strGrp = Trim(.Cells(RowSTA, y))
For x = RowSTA To RowEnd
strArea = Trim(.Cells(x, y))
If strArea <> "" Then
With Sheet1
For z = InpSta To InpEnd
If InStr(1, .Cells(z, InpCOL), strArea) <> 0 Then
.Cells(z, InpCOL).Offset(0, 1) = strGrp
Exit For
End If
Next
End With
End If
Next
Next
End With

With Sheet1
For z = InpSta To InpEnd
If Trim(.Cells(z, InpCOL).Offset(0, 1)) = "" Then
.Cells(z, InpCOL).Offset(0, 1) = "不明"
End If
Next
End With
Application.ScreenUpdating = True
End Sub

それにしてもひどいコードだ…。

補足

ありがとうございます。

実行したいファイルでは「住所」のセルがsheet1-A2、「エリア」のセルがsheet1-B2、「長野エリア」のセルがsheet2-A1となっているので作成していただいた構文を貼り付けて2つのアドレスをA2に変更して早速試してみました。

試してみたところ、『rangeクラスのselectメソッドに失敗しました。』と出てしまいます。
デバックの際にひっかかってくるのは下の箇所でした。
.Range(.Cells(InpSta, InpCOL), .Cells(InpEnd, InpCOL)).Offset(0, 1).Select

・・・すみません。自分で「出来ればマクロで・・・。」などと書いておきながら正直教科書片手に独学している状態で、自分で修正したりすることも全くおぼつかないレベルなのです。。。
アドレスの入力ミスか何かでしょうか？？？

回答No.4

maron--5

◆この式の方が短いですね
B3=IF(SUM(COUNTIF(A4,"*"&Sheet2!$A$3:$D$5&"*")),INDEX(Sheet2!$A$2:$D$2,SUM(COUNTIF(A4,"*"&Sheet2!$A$3:$D$5&"*")*{1,2,3,4})),"不明")
★この式は「配列数式」です。式を入力後、CtrlとShiftを押しながらEnterを押して式を確定させてください
★式が確定すれば、式の両端に、｛　｝がつきます
★式を確定させてから下にコピー

補足

配列数式、そんなすごい機能がエクセルにもあるんですね。
（ただの勉強不足なだけですが…（＾＾；）

今後継続的にフォーマットを改善しつつ使っていくことや他の人が更新することを考えて、挙げられている幾つかの例を参考に最適の方法で作ってみたいと思います。ありがとうございます。

回答No.3

maron--5

◆Sheet1の式
B3=IF(SUMPRODUCT(COUNTIF(A3,"*"&Sheet2!$A$3:$D$5&"*")),LOOKUP(0,0/(ISNUMBER(FIND(Sheet2!$A$3:$D$3,A3))*1+ISNUMBER(FIND(Sheet2!$A$4:$D$4,A3))*1+ISNUMBER(FIND(Sheet2!$A$5:$D$5,A3))),Sheet2!$A$2:$D$2),"不明")
★下にコピー

回答No.2

135ok

一例です。
添付資料を参照ください。
sheet2の表を添付（水色）のように変更する。

sheet1のＣ１から右に表の市名を記入する。
Ｃ、Ｂの順で説明。
Ｃ２　=IF(ISNUMBER(FIND(C$1,$A2)),COLUMN(A1),"")
　　　*Ｃ１の市名が住所に含まれている場合、番号を、以外は空欄。
　　　*COLUMN(A1)でＣ１から１、２と連番をつける。
　　　*各セルにコピー

Ｂ２　=IF(SUM(C2:L2)>0,VLOOKUP(SUM($C2:$L2),$B$11:$D$21,3,0),"不明")
　　　*Ｃ２行の数が０を超える場合、表から値を表示、以外は不明を表示。

補足

画像と一緒の丁寧なご説明ありがとうございます。
実は今回は住所とエリア以外にも複数の列項目があるシートの加工となるので、試してみる際には第3のシートで行うことを含めて考えてみます。

回答No.1

KURUMITO

県および市が入った住所であることが必要です。
Ｂ1セルには次の式を入力して下方にオートフィルドラッグすればよいでしょう。
=IF(COUNTIF(Sheet2!A:A,MID(A1,FIND("県",A1)+1,FIND("市",A1)-FIND("県",A1)))>0,Sheet2!A$1,"")&IF(COUNTIF(Sheet2!B:B,MID(A1,FIND("県",A1)+1,FIND("市",A1)-FIND("県",A1)))>0,Sheet2!B$1,"")&IF(COUNTIF(Sheet2!C:C,MID(A1,FIND("県",A1)+1,FIND("市",A1)-FIND("県",A1)))>0,Sheet2!C$1,"")&IF(COUNTIF(Sheet2!D:D,MID(A1,FIND("県",A1)+1,FIND("市",A1)-FIND("県",A1)))>0,Sheet2!D$1,"")

補足

早速のレスありがとうございます。
find関数って数値と同様に演算出来るんですね。
行いたいことはこの式でほぼ間違いないです。
"郡"もあるので参考にさせていただきもう少し考えてみます。