Excelについて質問です。 シート1の検索値例えば ＊ABC＊ をシート2.3.4から検索して、シ

Excelについて質問です。
シート1の検索値例えば　＊ABC＊　をシート2.3.4から検索して、シート1に検索値に該当する行を抽出したいです。
関数でも、VBAでも構いません。自分でも調べてみましたが、どうにも難しく、また、一つずつやって終わる量でもないので、知恵をお借りしたいです。よろしくお願いします。

質問者からの補足コメント

• シート１のA 1のセルに検索値を入力、
  A3から検索結果を表示させたいです。
  
  
  シート2.3.4にはA-E列にデータが入っています。
  それぞれのシートに20000行以上のデータが入っているので、一つにまとめるのも困難です。

    補足日時：2023/02/18 13:10

• 検索語句は1つのみです。
  それぞれのシートに重複するものがあるかもしれませんが、重複しているものも検索結果として出て欲しいです。

    補足日時：2023/02/18 13:12

• 何度も補足をすみません。
  こちらが検索画面シート1になります。

  
    補足日時：2023/02/18 13:53

• これが、データの入ってるシート2.3.4です。
  シート2.3.4には、同じデータもあれば、他のシートにはないデータもあります。
  シート2の中には、みかんのデータが2つあったりもします。
  
  これらをシート1の検索値を入力すると、該当するものがシート1に抽出されるようにしたいです。シート内での重複するデータ、シートごとでの重複するデータも全てシート1に抽出したいです。

    補足日時：2023/02/18 13:56

• よろしくお願いします

  
    補足日時：2023/02/18 13:56

No.5 回答者： goomania 回答日時： 2023/02/21 17:11

既に、マクロによる回答が複数ついていますが、ご質問者は関数による方法を対象外としておられないので、関数による方法を投稿します。

マクロによる方法の方がスマートで実行速度も速いと思うのですが、関数でもできるという意味での回答になります。
ご質問者のEXCELのバージョンが不明なので、2010以降で動作するローテクな数式なのでとても長いものになっています。「検索」シートのB2セルに検索値として入力された文字列を含むものをsheet2、sheet3、sheet4から探して「検索」シートのA5セル以下に表示するものとします。
添付画像の例では「みかん」という文字列を検索値としているため「みかん」だけでなく「なつみかん」も抽出されています。
また、データは各シート20000行以上とのことなので、とりあえず25000行までを抽出対象行としました。
「検索」シートのA5セルに以下の数式を記述し、右方向(E列まで)、下方向へオートフィル等でコピーしてください。

=IFERROR(IFERROR(IFERROR(INDEX(Sheet2!A$1:A$25000,AGGREGATE(15,6,ROW(Sheet2!$A$1:$A$25000)/(FIND($B$2,Sheet2!$B$1:$B$25000)>0),ROW(A1)))&"",INDEX(Sheet3!A$1:A$25000,AGGREGATE(15,6,ROW(Sheet3!$A$1:$A$25000)/(FIND($B$2,Sheet3!$B$1:$B$25000)>0),ROW(A1)-SUMPRODUCT((1-ISERROR(FIND($B$2,Sheet2!$B$1:$B$25000)))*1))))&"",INDEX(Sheet4!A$1:A$25000,AGGREGATE(15,6,ROW(Sheet4!$A$1:$A$25000)/(FIND($B$2,Sheet4!$B$1:$B$25000)>0),ROW(A1)-SUMPRODUCT((1-ISERROR(FIND($B$2,Sheet2!$B$1:$B$25000)))*1)-SUMPRODUCT((1-ISERROR(FIND($B$2,Sheet3!$B$1:$B$25000)))*1))))&"","")

No.4 回答者： fujillin 回答日時： 2023/02/18 15:01

こんにちは

単純に「検索値」に該当するデータを集めてくれば良さそうなので、こんな感じでしょうか？

ご質問文と図からでは、纏めるシートが「検索」なのか「Sheet1」なのかよくわかりません。
また、その他の不明点も勝手に解釈してありますので、適宜修正願います。

Sub Q_13357492()
Dim wrd As String, sh As Worksheet
Dim data, r As Long
Dim dst As Range

With Worksheets("検索") ' "Sheet1"なのかも？
Range(.Cells(5, 1), .Cells(Rows.Count, 5)).ClearContents
wrd = "*" & .Range("B2").Text & "*"
If Len(wrd) < 3 Then Exit Sub
Set dst = .Range("A5:E5")
End With

For Each sh In Worksheets(Array("Sheet2", "Sheet3", "Sheet4"))
r = Application.Max(sh.Cells(Rows.Count, 2).End(xlUp).Row, 2)
data = sh.Cells(1, 2).Resize(r).Value
For r = 1 To UBound(data)
If data(r, 1) Like wrd Then
dst.Value = sh.Cells(r, 1).Resize(, 5).Value
Set dst = dst.Offset(1)
End If
Next r
Next sh
End Sub

No.3 回答者： 初心者ですが何か。 回答日時： 2023/02/18 13:39

補足を見て。

結局
・探し物は単一のシートにしか存在しないのか、複数のシートに存在するのか
・１つのシート内で１つしかないのか、複数あるのか
・そしてあった場合どのように表示させたいのか

って何もないのですけど？

＞一つにまとめるのも困難です。

シート１に纏めたいってのと矛盾しますけど？

https://faq.nec-lavie.jp/qasearch/1007/app/servl …

ダミーデータでも良いですが、どうしたいのかを画面の画像を切り取って添付してみては如何でしょう？

No.2 回答者： 初心者ですが何か。 回答日時： 2023/02/18 09:53

・３つのシートそれぞれに検索語句が存在するとした場合、存在したシート名は不要？それともシート間での重複はない？

・１つのシート内に検索語句は１つのみ？それとも複数？

No.1 回答者： bymc 回答日時： 2023/02/18 00:52

詳細がもっと欲しいとこですが…

例えばシート1の検索値がA1で、～が含まれていたら、シート2,3,4のA列を検索して、最終的にシート1のB列に結果を表示するみたいな感じですが…

Sub test()
Dim str As String
Dim i, r, lastRow As Long

With Sheets("Sheet1")
str = "*" & .Range("A1") & "*"
.Columns(2).ClearContents
End With

For i = 2 To 4
With Sheets("Sheet" & i)
For r = 1 To .Cells(Rows.Count, 1).End(xlUp).row
If .Cells(r, 1) Like str Then
lastRow = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).row
Sheets("Sheet1").Cells(lastRow, 2).Offset(1) = .Cells(r, 1)
End If
Next
End With
Next

End Sub