エクセルで文字列検索

質問

エクセルでＢ６から下に無制限で入力されたリストの中からＢ２、Ｂ３に入力された２つの文字列による部分一致、ＡＮＤ検索をかけて、該当するリストをオートフィルタの抽出のように表示させるシステムを作りたいと思っています。

説明が下手で申し訳ありません。
マクロで作ろうと思いましたがいろいろ調べながらやってみましたがなかなか思った通りにできませんでした。
上記のようなマクロはどうすればできますか？

回答No.2

noname#146898 · ベストアンサー

マクロの記録で作ってみました。
前処理として下記を入力しておきます。
ワイルトカードとしての「*」が必要になりますので、とりあえずC1にでも「*」を入力することとします。
C2には「=C1&B2&C1」
C3には「=C1&B3&C1」
B列と全く同一の内容の列を作っておきます。C列にしておきます。C6には「=B6」として下方にコピーします。
C列は誤って記述を削除することを避けるために非表示にしてもいいでしょう。

データベースの範囲はB5に項目があると仮定し、その行に対してオートフィルタの「▼」が表示されている状態で下記のマクロを実行します。

Range("B5").Select
Selection.AutoFilter Field:="2", Criteria1:=Range("C2"), Operator:=xlAnd
Selection.AutoFilter Field:="3", Criteria1:=Range("C3"), Operator:=xlAnd
Range("B2").Select

抽出が終わったら即時次の検索ができるようにB2のセルがアクティブになります。
別途、オートフィルタを解いてまた5行目にオートフィルタの「▼」が表示されている状態にしておくマクロを別のボタンにでも登録しておくといいと思います。

お礼

回答参考にして作ってみたらイメージしていたものができました。
ありがとうございました。

回答No.6

imogasi

やさしいのはＢ２セルに入力した語句でやることだが、本件はＢ２，Ｂ３ということらしい。実例を６－７例挙げて書くこと。
部分一致、ＡＮＤ検索のからみで。どういう結果を望むのか？
ーー
部分一致　（Ａ）Ｂ２で部分一致単独、（Ｂ）Ｂ３で部分一致単独、（３）そのほかに（Ａ）（Ｂ）のＡＮＤ、ＯＲ（こちらは（Ａ）（Ｂ）のことと一致？）
ＡＮＤ検索　（Ａ）を部分的に含み、かつ（Ｂ）も部分的に含むで良いのか。
ーー
それにしてもオートフィルタの操作など屋って、「マクロの記録」をとって、考えたか。
それも「オプション」の仕組みを使ってね。
例データ　質問には例データぐらい考えて、あげること。
検索語
Ｂ２　aa
Ｂ３ bb
--
データ
Ｂ５：Ｂ１００について（データ項目見出しはＢ５に入れておいたほうが良いだろう）
データ
aasd
ssdf
aadfbb
bbghjk
のようなもの。以下行略
ーーーーー
そのマクロの記録を一部修正して
○長押内で、下記などやってみて質問者が考えるべきだ「。
Sub Macro4()
s1 = "=*" & Range("B2") & "*": MsgBox s1
s2 = "=*" & Range("B3") & "*": MsgBox s2
Range("B5:B100").AutoFilter
Range("B5:B100").AutoFilter Field:=1, Criteria1:=s1, Operator:=xlOr, _
Criteria2:=s2
End Sub
Sub Macro41()
s1 = "=*" & Range("B2") & "*": MsgBox s1
s2 = "=*" & Range("B3") & "*": MsgBox s2
Range("B5:B100").AutoFilter
Range("B5:B100").AutoFilter Field:=1, Criteria1:=s1, Operator:=xlAnd, _
Criteria2:=s2
End Sub
Sub Macro5()
s1 = "=*" & Range("B2") & "*": MsgBox s1
s2 = "=*" & Range("B3") & "*": MsgBox s2
s3 = "=*" & Range("B2") & "*" & Range("B3") & "*": MsgBox s3
Range("B6:B100").AutoFilter Field:=1, Criteria1:=s3
End Sub
ーーー
＞調べながらやってみましたがなかなか思った通りにできませんでした。
ほんとにやってみたなら、何処で、どううまくいかないのか「絞れるだろう。
丸投げになっているのでは。

お礼

みなさんの回答を参考に作ったらできました。
これからもっと勉強したいと思います。
ありがとうございました。

回答No.5

keithin

＞オートフィルタの抽出のように表示させる

単純に「オートフィルタを掛けて絞り込みたい」ということなら

sub macro1()
　range("B5:B" & range("B65536").end(xlup).row).autofilter _
　　field:=1, criteria1:="*" & range("B2") & "*", _
　　operator:=xland, criteria2:="*" & range("B3") & "*"
end sub

でいいです。

遅くて重くてもイイから総ナメでイチイチ調べるのが好みなのでしたら

sub macro2()
　dim i as long, n as long
　n = 6
　for i = 6 to range("B65536").end(xlup).row
　if cells(i, "B") like "*" & range("B2") & "*" and cells(i, "B") like "*" & range("B3") & "*" then
　　cells(n, "C") = cells("i, "B")
　　n = n + 1
　end if
　next i
end sub
とかでいいです。

お礼

みなさんの回答を参考に作ったらできました。
ありがとうございました。

回答No.4

yugere

質問のでの不明点が幾つかあります。
リストから検索して一致したら別の場所に転記するということでよろしいでしょうか？

サンプルを作成して見ました。
新規ブックに追加してみてください。

ご希望通りに動作しますでしょうか。

Sub リストからの抽出()

Const cnt As Long = 50000 '抽出対象リストの行数
Const col As Long = 200 '抽出対象リストの項目（列数）
Const pstsh As String = "Sheet2" '出力シート

Dim pstrn As Range '出力開始位置
Dim j1 As Variant '条件１の検索条件
Dim j2 As Variant '条件２の検索条件
Dim st As Variant '検索対象
Dim ct As Long 'ヒット数

Set pstrn = Range("B2")

j1 = Range("B2").Value
j2 = Range("B3").Value

For i = 1 To cnt Step 1
st = Range("B" & i + 5).Value
If st Like "*" & j1 & "*" = True And st Like "*" & j2 & "*" = True Then
For j = 0 To col Step 1
Sheets(pstsh).Cells(pstrn.row + ct, pstrn.Column + j).Value = Cells(5 + i, 2 + j).Value
Next j
ct = ct + 1
End If
Next i
End Sub

お礼

みなさんの回答を参考に作ったらできました。
ありがとうございました。

回答No.3

KURUMITO

マクロで対応するほどのことも無いように思いますね。マクロの場合にはある操作をしなければデータが追加や修正されたときに対応できませんね。関数を使うことでそれらの操作を必要としません。
初めに例えば元のデータがシート1のA列からF列の下方の行に入力されていて、B2およびB3セルの文字列を両方含む行を別のシート、例えばシート2に表示させるのでしたら次のようにします。

シート1のG6セルに次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTA(B$2:B$3)<>2,"",IF(IF(ISERROR(FIND(B$2,B6)),0,1)+IF(ISERROR(FIND(B$3,B6)),0,1)=2,MAX(G$5:G5)+1,""))

次にシート2の例えば1行目にはシート1での項目名の入った行をコピーして貼り付けます。
A2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR(ROW(A1)>MAX(Sheet1!$G:$G),COLUMN(A1)>6),"",INDEX(Sheet1!$A:$F,MATCH(ROW(A1),Sheet1!$G:$G,0),COLUMN(A1)))

お礼

みなさんの回答を参考に作ったらできました。
ありがとうございました。

回答No.1

mt2008

作業セルとフィルタオプションの組み合わせで出来ますから、それをマクロ記録しましょう。

まず、B6から始まるデータには項目名を付けます（例：B5に「データ」）
次に、検索条件を作業セル範囲に作ります。
添付の図ではD1:E2。
部分一致のために前後に「*」を付けておきます。
検索条件を横に並べるとAND検索になります。

あとは、フィルタオプション。

お礼

回答参考にして作ってみたらイメージしていたものができました。
ありがとうございました。