エクセルにて検索

質問

エクセルで作ったデータの中からデータを検索して該当するものを全て
抽出する関数を教えて下さい。

回答No.6

ham_kamo · ベストアンサー

No.1 & 4です。先ほどのマクロ、ちょっと修正しました。

表がＡ列からＦ列まである場合、１列あけてH1に検索条件、H3以降に検索結果が出る、というように表の大きさにかかわらず、表の右側に１列空行を開けて、１行目に検索文字列を書けば、その列の３行目以降に検索結果が出るようにしてみました。マクロを貼り付けても修正は必要ありません。

Sub SearchData()
　　Dim RngData As Range
　　Dim RngSearch As Range
　　Dim StrSearch As String
　　Dim RngResult As Range
　　Dim r0 As Range, r As Range
　　Dim i As Integer

　　With ActiveSheet
　　　　Set RngData = .Range("A1").CurrentRegion
　　　　Set RngSearch = .Cells(1, RngData.Columns.Count + 2)
　　　　Set RngResult = RngSearch.Offset(2, 0)
　　　　StrSearch = RngSearch.Value
　　　　.Columns(RngResult.Column).ClearContents
　　　　RngSearch.Value = StrSearch
　　End With

　　Set r = RngData.Find(StrSearch, , xlValues, xlPart)
　　If r Is Nothing Then
　　　　Exit Sub
　　End If
　　RngResult.Offset(0, 0).Value = r.Value

　　Set r0 = r
　　i = 1
　　Do
　　　　Set r = RngData.FindNext(r)
　　　　If r = r0 Then Exit Do
　　　　RngResult.Offset(i, 0).Value = r.Value
　　　　i = i + 1
　　Loop
End Sub

回答No.5

maron--5

maronです
>、”山”の文字をセルに文字を入力して検索出来るように出来ないでしょうか？
◆「山」を、D1　に入力したとします
★D1に入力されている文字が含まれているものを、抽出
=IF(ROW(A1)>COUNTIF($A$1:$A$5,"*"&$D$1&"*"),"",INDEX($A$1:$A$5,SMALL(INDEX(SUBSTITUTE(N(ISNUMBER(FIND($D$1,$A$1:$A$5))),0,10^5)*ROW($A$1:$A$5),),ROW(A1))))

★D1に入力されている文字が最後に付くものを、抽出
=IF(ROW(A1)>COUNTIF($A$1:$A$5,"*"&$D$1),"",INDEX($A$1:$A$5,SMALL(INDEX(SUBSTITUTE(N(RIGHT($A$1:$A$5)=$D$1),0,10^5)*ROW($A$1:$A$5),),ROW(A1))))

回答No.4

ham_kamo

No.1です。補足拝見しました。

関数で、というのはちょっと難しいですね。私は関数の複雑な使い方は苦手なので、うまくやる方法はあるかもしれませんが私には思いつきませんでした。特定の値を検索したい場合には、関数でやるよりNo.2さんの書かれているようにフィルタを使うのが一般的です。

ですが、
> 抽出：条件に一致するセルを表の外に一覧表示で。

と書かれているので、そのようなマクロを組んでみました。
表の大きさがどれくらいかわからないので、とりあえずH1に検索文字列を、I列に検索結果一覧を出すようにしてみました。
Alt+F11でVBAの画面を立ち上げて、以下のマクロを貼り付けてください。その際、"H1"という部分と"I1"という部分は適切なセル番地に書き換える必要があります。
貼り付けた後、Alt+F8でこのマクロを実行してみてください。

Sub SearchData()
　　Dim RngData As Range
　　Dim RngSearch As Range
　　Dim StrSearch As String
　　Dim RngResult As Range
　　Dim r0 As Range, r As Range
　　Dim i As Integer

　　With ActiveSheet
　　　　Set RngData = .Range("A1").CurrentRegion
　　　　Set RngSearch = .Range("H1") '検索文字列を記入するセル
　　　　Set RngResult = .Range("I1") '検索結果が表示される先頭のセル
　　　　StrSearch = RngSearch.Value
　　　　.Columns(RngResult.Column).ClearContents
　　End With

　　Set r = RngData.Find(StrSearch, , xlValues, xlPart)
　　If r Is Nothing Then
　　　　Exit Sub
　　End If
　　RngResult.Offset(0, 0).Value = r.Value

　　Set r0 = r
　　i = 1
　　Do
　　　　Set r = RngData.FindNext(r)
　　　　If r = r0 Then Exit Do
　　　　RngResult.Offset(i, 0).Value = r.Value
　　　　i = i + 1
　　Loop
End Sub

回答No.3

maron--5

◆例えば、Ａ列のデータで、
★「山」が含まれているものを、Ｂ列に抽出
★「山」が最後に付くものを、Ｃ列に抽出

　　　Ａ　　　　　Ｂ　　　　　Ｃ
1　富士山　　富士山　　富士山
2　鯛　　　　　山頂　　　 六甲山
3　山頂　　　 六甲山
4　桜
5　六甲山

B1=IF(ROW(A1)>COUNTIF($A$1:$A$5,"*山*"),"",INDEX($A$1:$A$5,SMALL(INDEX(SUBSTITUTE(N(ISNUMBER(FIND("山",$A$1:$A$5))),0,10^5)*ROW($A$1:$A$5),),ROW(A1))))

C1=IF(ROW(A1)>COUNTIF($A$1:$A$5,"*山"),"",INDEX($A$1:$A$5,SMALL(INDEX(SUBSTITUTE(N(RIGHT($A$1:$A$5)="山"),0,10^5)*ROW($A$1:$A$5),),ROW(A1))))

◆こんなことでしょうか

補足

ありがとうごいます。さらに補足ですが、”山”の文字を
セルに文字を入力して検索出来るように出来ないでしょうか？
宜しくお願い致します。

回答No.2

Wendy02

こんにはち。

関数というのは、一般的に、１(数式)対１(解)　なのです。(一般的にというのは、配列数式を使うと、複数の解が中に入るのですが、出口は一つしかありません)　しかし、配列数式というのは、やはり特殊な使い方です。

基本的には、フィルタ・オプションというものを使います。(オートフィルタではありません。以前、区別が付かなかった人がいます。)

データ－フィルタオプションの設定
リスト範囲
検索条件範囲

を入れて、OK をクリックして出します。

回答No.1

ham_kamo

ちょっと質問が抽象的なので、補足をお願いします。

・検索条件はなんでしょうか。特定の値に一致するセル？それとも
　セルの一部に特定の文字列が含まれているセル？

・抽出はどこにどういう形で行われるのでしょうか。条件に一致する
　セルを表の外、または別のシートに一覧で書き出すのか（それだと
　完全一致の検索をしたら、同じ内容のセルが並ぶだけですが）、
　それとも検索で該当したセルのアドレス一覧が表示されるのか、
　該当セルに色がつくのか。

とりあえずその辺りの情報を具体的に補足願います。

補足

さっそくありがとうございます。
検索条件：セルの一部に特定の文字列が含まれているセル。
抽出：条件に一致するセルを表の外に一覧表示で。

宜しくお願い致します。