Ｅｘｃｅｌの関数を使ってキーワード検索の表示

質問

Ｅｘｃｅｌ上で関数を使って
特定のセルにキーワードを入れると
別のワークシートにある複数行のテキストデータのリストから検索してきて
その複数あるデータを　先ほどのセルの下に一覧で表示させたいのですがどうすればうまくいくででしょうか？
関数の設定で出来ればと思っていますが　他に方法があるのでしたら
そちらもご教授いただけませんでしょうか？

回答No.4

-yellowtail- · ベストアンサー

#2です。
ごめんなさい。ちょっと間違ってました。
説明のため、Findの引数を以下のようにして話しますね。
Set tmp = Sheets("別シート").Columns(1).Find(a, lookat:=xlWhole)
このLookatが「xlwhole」だと完全一致、「xlPart」だと部分一致になります。
下の例だと「xlwhole」の場合、東京で検索して東京都はhitしません。「xlPart」の場合はHITします。
hitしない場合は「一致するデータはありません」と表示されるのでその辺が原因では？
ちなみにimogasiさんがおっしゃるようにDo～LoopもしくはFor～Nextで総なめにしたほうが簡単そうです。
部分一致ならLikeを使えばよいと思いますので。
そちらもおためし下さい。
Sub 検索2()
Dim y As Integer, a, i As Integer, lastrow As Integer
a = Sheets("検索用シート").Range("A1")
y = 2: i = 1
lastrow = Sheets("別シート").Range("A65536").End(xlUp).Row
Do
If Sheets("別シート").Range("A" & i) Like a & "*" Then
Sheets("検索用シート").Range("A" & y) = Sheets("別シート").Range("A" & i)
Sheets("検索用シート").Range("B" & y) = Sheets("別シート").Range("B" & i)
y = y + 1
End If
i = i + 1
Loop Until i = lastrow
End Sub
骨組みはこんな感じでしょうか？

お礼

ありがとうございました
xlPartで
無事に解決しました
おかげで助かりました

回答No.3

imogasi

OKWAVEでimogasi方式と自称して、回答を相当数載せてますが（照会すれば沢山出ます）、その方式でできます。
（例）
Ｓｈｅｅｔ２のＡ列に例文がある。
Ａ２以下とし、第１行は空白とする。
ーーー
朝の食事は味噌汁がよい。
昨日の朝は雨が降ってました。
梅雨も近い
音がしたのは朝方のことでした
東京の寒い朝の光景です。
朝食のに献立。
ーーーー
Ｓｈｅｅｔ２のＥ２に
=IF(ISERROR(FIND(Sheet1!$A$1,A2)),"",MAX($E$1:E1)+1)
と入れて下方向に式を複写する。
1
2

3
4
5
となります。
条件該当行に連番を振りました。
ーーーー
Ｓｈｅｅｔ１でＡ１に
朝
Ａ２セルに
=IF(ROW()-1>MAX(Sheet2!$E$2:$E$100),"",INDEX(Sheet2!$A$2:$A$100,MATCH(ROW()-1,Sheet2!$E$2:$E$100,0),1))
（骨子はＩＮＤＥＸがあるところ以後の部分です）
と入れて、下方向に式を複写する。
結果
朝の食事は味噌汁がよい。
昨日の朝は雨が降ってました。
音がしたのは朝方のことでした
東京の寒い朝の光景です。
朝食に献立。
質問が、「検索してきて」という表現が、あいまいで、上記で意に充るかどうか判りませんが。
ーー
しかし、私も確かにＶＢＡ向きの問題だと思います。
ＶＢＡならＦＩＮＤメソッドを使わず、全行総なめチェックでも１万行ぐらいまでなら瞬時に結果が出るでしょう。
検索はＦｉｎｄだけではだめで、Ｆｉｎｄ、ＦｉｎｄＮｅｘｔを使うと思うが、初めの方にはむつかしいと思います。

回答No.2

-yellowtail-

マクロもありですか？まさにマクロでやるようなことだと思いますので…。
関数だけでやるのは困難だと思います。
検索用のシートのA1セルにキーワードを入れます。
そのキーワードを元に別シートのA列を検索しています。
この例では持ってくるデータは別シートのA列とB列です。
その辺は適宜修正してください。
エクセルが2003以降でしたら下記を修正します。
Set tmp = Sheets("別シート").Columns(1).Find(a, , xlWhole)
ここを、Set tmp = Sheets("別シート").Columns(1).Find(a, , , xlWhole)に変更
＊カンマがみっつです

Sub 検索()
Dim tmp As Range
Dim y As Integer, a, firstAddress
'***** 結果を表示する部分をクリアします
Sheets("検索用シート").Range("A2:B65536").ClearContents
'***** キーワードを取得
a = Sheets("検索用シート").Range("A1")
'***** キーワードを含むデータを検索
Set tmp = Sheets("別シート").Columns(1).Find(a, , xlWhole)
If tmp Is Nothing Then
'***** 見つからない場合
Sheets("検索用シート").Range("A2") = "一致するデータはありません"
Else
'***** 見つかった場合
firstAddress = tmp.Address
y = 2
'***** 他にもあるか探してあれば記載
Do
Sheets("検索用シート").Range("A" & y) = tmp
Sheets("検索用シート").Range("B" & y) = tmp.Offset(0, 1)
Set tmp = Sheets("別シート").Columns(1).FindNext(tmp)
y = y + 1
Loop Until tmp.Address = firstAddress
End If
End Sub

別シートにあるデータが例えばA列に上から
東京・東京都・大阪・大阪府・・・・
とはいっていて、東京を検索したら東京・東京都を持ってくるというイメージですが、合ってます？

お礼

ありがとうございます
早速試みたのですが
別シートに　テキストデータのリストを載せて
検索用シート　のＡ１にキーワードを入れて
マクロを実行したのですが「一致するデータはありません」
となってしまいました。何が原因でしょうか？

回答No.1

mu2011

次の方法は如何でしょうか。
（１）別シートの対象範囲を選択し、データ→フィルタ→オートフィルタ
（２）検索列の▽押下し、キーワードを選択
（３）編集→ジャンプ→セル選択→可視セルを選択
（４）表示された範囲を選択し、右クリック→コピー
（５）コピー先シートのセル上で右クリック→形式を選択して貼り付けで「値のみ」で貼り付け