Exceｌ内での検索結果をシートに出力したい

Question

こんばんは。いつも大変お世話になっています。

Ｅｘｃｅｌ２００３にて、あるシートの中から
対象の文字列があるかどうか検索をしました。
検索時に「すべて検索」にすると、
全結果が表示されますよね。
その内容をＥｘｃｅｌのシートに出力することは
できないでしょうか・・・。

どのセルの位置にその対象文字列がいてということを
報告書として作成したいのです。

イメージがうまくかけませんが、どなたかよい方法を
ご存知でしたら教えてください。
何か記述で足りないものがあれば、すぐに記述いたします。

zap35 · Accepted Answer

丸投げですか(^^;
以下のマクロをALT＋F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい。マクロの実行はワークシート画面に戻ってALT＋F8でマクロ一覧を開き、マクロ名を選択して「実行」ボタンです。

勉強になりませんので解説や再修正はしません。もし修正が必要ならご自身でお願いします。

Sub Macro1()
Dim ret
Dim r As Range
Dim adr As String
Dim cnt As Long
Dim psw As Boolean
Dim mySht, adSht, ws As Worksheet
　　Set mySht = ActiveSheet
　　ret = Application.InputBox("検索文字列を入力してください")
　　If TypeName(ret) <> "Boolean" Then
　　　　With mySht.Cells
　　　　　　Set r = .Find(ret, LookIn:=xlValues, lookat:=xlPart)
　　　　　　If Not r Is Nothing Then
　　　　　　　　adr = r.Address
　　　　　　　　cnt = 1
　　　　　　　　For Each ws In Worksheets
　　　　　　　　　　If ws.Name = "検索結果" & ret Then
　　　　　　　　　　　　psw = True
　　　　　　　　　　　　Exit For
　　　　　　　　　　End If
　　　　　　　　Next ws
　　　　　　　　If psw Then
　　　　　　　　　　Set adSht = ws
　　　　　　　　　　adSht.Cells.ClearContents
　　　　　　　　Else
　　　　　　　　　　Set adSht = Worksheets.Add
　　　　　　　　　　adSht.Name = "検索結果" & ret
　　　　　　　　End If
　　　　　　　　adSht.Cells(cnt, 1).Value = r.Value
　　　　　　　　adSht.Cells(cnt, 2).Value = adr
　　　　　　　　Do
　　　　　　　　　　Set r = .FindNext(r)
　　　　　　　　　　If r.Address = adr Then
　　　　　　　　　　　　Exit Do
　　　　　　　　　　Else
　　　　　　　　　　　　cnt = cnt + 1
　　　　　　　　　　　　adSht.Cells(cnt, 1).Value = r.Value
　　　　　　　　　　　　adSht.Cells(cnt, 2).Value = r.Address
　　　　　　　　　　End If
　　　　　　　　Loop
　　　　　　End If
　　　　End With
　　End If
　　mySht.Activate
End Sub

deus_ex_machina · Answer

#3です。わかりにくかったようでスミマセン。Ａ案・Ｂ案ともにそうですが、　　>>検索は【エクセルの検索機能で】行う<< 　>>【検索結果を】すべて選択する<< のがポイントです。 Excelの検索機能で[すべて検索]を使うと、検索ダイアログに検索結果のリストが表示されますよね。その[結果リスト]の特定の行を選択すると、該当セルが選択されます。また[結果リスト]上でCtrl+Aを押すと、結果リストのすべての行が選択されます。つまり、検索でヒットしたセルがすべて選択されます。Ａ案もＢ案も、この[検索でヒットしたセルがすべて選択された状態]で行うことを想定しています。 ********************* 確かに、対象のセルが複数の列に散らばっている場合は、オートフィルタでは解決できませんね。失礼しました。（そういう状況やニーズ自体ちょっとイレギュラーな気もしますが）ただ、書式や数式を対象とした検索ではなく、単に値を対象として文字列を探すだけであれば、私なら次のような方向で考えます。 [シート１のA1:AB999の範囲で完全一致検索を行う場合] 適当なシートで、 A1：検索対象の文字、たとえば「ほにゃらら」を入力 B1：=INDEX(1/LARGE((Sheet1!$A$1:$AB$999=$A$1)/(ROW(Sheet1!$A$1:$AB$999)*1000+COLUMN(Sheet1!$A$1:$AB$999)),ROW()),) 　として下方にフィル C1：=ADDRESS(INT(B1/1000),MOD(B1,1000)) 　として下方にフィル ⇒値が「ほにゃらら」であるセルのセル番地がC列に順に表示される (Excel2003で動作確認済) まぁ、これはこれでかなり無理矢理ですし、 #2さんの方法で既に解決しているご様子ですから、あくまで参考としてお考えください。

deus_ex_machina · Answer

[すべて検索]の検索結果は、いかにも書き出せそうな感じがするのですが、
実際やってみると、どうも無理っぽいですね。

●Ａ案：検索結果全体のセル参照を[名前]で記録して数式で書き出す

　１．[すべて検索]で検索する（[検索方向]：行で検索すること）
　２．Ctrl＋Ａ　で検索結果を全て選択する
　３．挿入＞名前＞定義　で、適当な文字列(例えば"test")を入力してEnter

　以下、同じブックの任意のシートで…

　４．任意のセル：=CELL("address",test)
　　とすると、検索結果のセル番地が単一の文字列として返ります。

　あるいは…

　５．A1セル：1　とする
　６．A2セル：=IF(ROWS(INDEX(test,,,A1))=COUNTIF($A$1:A1,A1),A1+1,A1)
　　として下方にフィル
　７．B1セル：=COUNTIF($A$1:A1,A1)
　　として下方にフィル
　８．C1セル：=CELL("address",INDEX(test,B1,,A1))
　　として下方にフィル
　　※検索結果のセル番地が順に返ります。
　９．D1セル：=INDEX(test,B1,,A1)
　　として下方にフィル
　　※検索結果の値が順に返ります。
　（Excel2003で動作確認済)

　※セル参照全体の長さに制限があるので、
　　HITしたセルの数が多い場合には、その全てを取得することはできません。
　※セルに入力されている数式を取得することはできません、たぶん。

●Ｂ案：検索結果のセルを選択してマクロで書き出す

　Excelの検索機能にはさまざまなオプションがありますが、
　そのすべてをマクロでフォローするのは大変なので、
　検索自体は既存の機能を使い、結果を書き出すというアプローチで…。

　０．下記のマクロを設置する

　１．[すべて検索]で検索する
　２．Ctrl＋Ａ　で検索結果を全て選択する
　３．マクロを起動する

動作の概要
　選択しているすべてのセルについて、
　・セル番地
　・値
　・(数式が入っていれば）数式
　を新規ブックに書き出す。

'-------------↓　ココカラ　↓-------------------
Sub Sample()
　Set myCels = Selection
　i = 1
　With Application.Workbooks.Add.Worksheets(1)
　　For Each myCel In myCels
　　　.Cells(i, 1) = myCel.Address
　　　.Cells(i, 2) = myCel
　　　If myCel <> myCel.Formula Then
　　　　.Cells(i, 3) = "'" & myCel.Formula
　　　End If
　　　i = i + 1
　　Next myCel
　End With
End Sub
'-------------↑　ココマデ　↑-------------------
（Excel2003で動作確認済）

以上ご参考まで。長乱文陳謝。

追記
どのような状況、目的での処理かわからないので断言はしませんけど、
なんとなく「オートフィルタ」で解決しそうな予感が…。

imogasi · Answer

エクセルVBAの勉強をしないとできません。
一応１つしかないならマクロの記録（置換で採る？）をとれば、どんなコードか判りますが、次々と見つけて見つかったもの全体文字列（数字）を他シートのセルに順次表示していくのは、VBAの中級の技と思います。
勉強する気があれば、WEB検索で「エクセル　検索　VBA Find」で検索し関連記事をじっくり読んで、判らないところを絞って質問したら。

Exceｌ内での検索結果をシートに出力したい

丸投げですか(^^;

#3です。

[すべて検索]の検索結果は、いかにも書き出せそうな感じがするのですが、

エクセルVBAの勉強をしないとできません。

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング