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

質問

こんばんは。いつも大変お世話になっています。

Ｅｘｃｅｌ２００３にて、あるシートの中から
対象の文字列があるかどうか検索をしました。
検索時に「すべて検索」にすると、
全結果が表示されますよね。
その内容をＥｘｃｅｌのシートに出力することは
できないでしょうか・・・。

どのセルの位置にその対象文字列がいてということを
報告書として作成したいのです。

イメージがうまくかけませんが、どなたかよい方法を
ご存知でしたら教えてください。
何か記述で足りないものがあれば、すぐに記述いたします。

回答No.2

zap35 · ベストアンサー

丸投げですか(^^;
以下のマクロを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

お礼

お礼が遅くなり申し訳ありませんでした。
上記をやってみたらできました！
思ったとおりの結果です。
すごいです。こうやるとできるんですね。
ただ、内容がたしかに理解できていません。
勉強します。
こういったツール（？とよぶのでしょうか）を作成できる
人になりたいと思っています。
本当にありがとうございました。

回答No.4

noname#52504

#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さんの方法で既に解決しているご様子ですから、
あくまで参考としてお考えください。

お礼

ありがとうございました。

関数がＩＦ，ＳＵＭ，ＡＶＲＡＧＥ，ＭＡＸ，ＭＩＮ、ＶＬＯＯＫＵＰ
くらいしかよくわかっていなくて。。。

上記式が何をしているのかがよくわかりません。。
ＬＡＲＧＥ関数はその対象範囲（ここではＡ１からＡＢ９９９でしょうか）
から大きい順の値をだす関数ですよね。それを1/のあとにいれ、
さらに。。えっと。。。

勉強します。。。
関数の組み合わせってむずかしいです。
どうやってみなさん手につけられていくのですか。。。

回答No.3

noname#52504

[すべて検索]の検索結果は、いかにも書き出せそうな感じがするのですが、
実際やってみると、どうも無理っぽいですね。

●Ａ案：検索結果全体のセル参照を[名前]で記録して数式で書き出す

　１．[すべて検索]で検索する（[検索方向]：行で検索すること）
　２．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で動作確認済）

以上ご参考まで。長乱文陳謝。

追記
どのような状況、目的での処理かわからないので断言はしませんけど、
なんとなく「オートフィルタ」で解決しそうな予感が…。

お礼

お礼が遅くなり申し訳ありませんでした。
Ａ案については関数がよくわからなくて、何をどうしているかが
わからず試せませんでした、申し訳ありません。
Ｂ案についてはためしてみたのですが、私のやり方が悪いのか
求めたい結果が得られませんでした。。。
（ためしてみたところ、全部のセル番地が別ブックに出力
されまして。。。）説明が下手で申し訳ありません。

オートフィルタ。。たしかにそうなんですが、データ量が
莫大なため、いちいちフィルタでみれなくて。。。
（何かよい方法があるのでしょうか。。。）
たとえば、Ａ列からＡＢ列までの中でＴＥＳＴと入力されているセルの
番地をしりたかったら、いちいちＡ列からＡＢ列までひとつずつ
オプションで「ＴＥＳＴ」でフィルタをかけていく方法しか
知らないのですが。。。。

回答No.1

imogasi

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

お礼

お礼が遅くなり申し訳ありませんでした。
そうですよね。
ＶＢＡ勉強しなきゃ、、、と思っています。
ご指摘ありがとうございました。