Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 7全般 : Excel 2013)
キーワード検索、あいまい検索
投稿日時: 18/09/18 18:21:27
投稿者: ピキヨ

初心者ですので、よろしくお願いします。
A列からL列、2000行のEXCELで作った取引先のデータベースがあります。
そこには、会社名、役職名、氏名、取引履歴などが記載されています。
誰でも簡単に検索できるように、メッセージボックスを表示させて、
キーワードをいくつか入力して検索すると、そのキーワードが入力されている
行全体の色(例えばマゼンダとか)が変わる、
あるいは新しいシートに該当する行が一覧になる。
こんな夢のようなマクロは作れるのでしょうか。
できれば、元に戻るマクロも合わせて教えていただけると助かります。
データベースを作ったものの、「検索できるようにしてほしい」という
上司の指示に困っています。
フィルターオプションを使ったほうがいいという方がいらっしゃると思いますが、上司から、まったくEXCELがわからない人でも検索できるようにしてほしいとの指示があるため、困っております。
フォームでボタンを作って、メッセージボックスを使えば、誰でもキーワードを入れるだけで検索できるので、そのようにしたいと思うのですが、いかがでしょうか。
皆様、よろしくお願いいたします。

回答
投稿日時: 18/09/18 19:52:19
投稿者: WinArrow
投稿者のウェブサイトに移動

ここの掲示板は、「コード作成依頼」を禁止しています。
↓参照
http://www.moug.net/faq/kiyaku.html
 
て行でもできるのでしたら、マクロの記録を
ご自分でトライしてみてから、わからないところを
質問してください
 
 
 

回答
投稿日時: 18/09/18 20:07:12
投稿者: WinArrow
投稿者のウェブサイトに移動

条件付き書式を使ってセルに色を設定する方法の例
 
 
セルA2:検索する文字列
 
セルA5〜A2000
例えば会社名
セル範囲に「会社名」という名前を定義します
 
会社名のセル範囲を選択して
「条件付き書式」
「新しいルール」
「数式〜」
 
条件式ボックスに
=INDEX(会社名,MATCH("*"&$A$2&"*",会社名,0),1)=A5
 
色はお望みで

回答
投稿日時: 18/09/18 22:33:35
投稿者: simple

「あいまい検索」とはどういうことを指しているのでしょうか。
単に、部分一致で抽出するということですか?
それとも、〜で始まるとか、〜で終わるとかそういったことですか?
それらの説明が必要ですね。
 
> キーワードをいくつか入力して検索すると、そのキーワードが入力されている
> 行全体の色(例えばマゼンダとか)が変わる、

ここも説明が必要です。
・1つの項目(列)について、複数の Or 条件があるのか
・それとも、会社名が○○で、氏名に山田が含まれるといった検索なのか
具体的に説明してください。
 
あいまい検索の種類が色々あるなら、フィルタオプションが適切ですね。
もちろん手作業でということではなく、それを自動実行するマクロでということですが。

投稿日時: 18/09/19 18:42:55
投稿者: ピキヨ

説明不足で失礼しました。
キーワード検索したいのは、部分一致でも抽出できるようにしたいと考えています。
例えば、「山田」で検索すると、
会社名の列では、「山田商事」、「山田工業」などが
氏名の列では「山田太郎」、「山田一郎」、「小山田五郎」などが
とにかくメッセージボックスに入力した文字列が含まれているものにヒットするようにしたいです。
そして、キーワードが含まれるセルに、マゼンダやイエローなど目立つ色を付けたいということです。
何とか完成させたいので、ご協力をお願いいたします。

投稿日時: 18/09/19 19:07:22
投稿者: ピキヨ

ここまでは、いろいろと試しながら作りました。
あとお願いしたいのは、データベースのシート("0.総括表")と検索のシート("検索トップ")が別々になっています。
このコードですと、("0.総括表")まで検索をかけてしまいます。
("検索トップ")のシートだけを検索するようにしたいです。
また、ヒットしたセルを次々に飛んでいきますが、
飛ぶ前に、いちいちメッセージボックスで、シート名とセル位置を返します。
これは、セルに色がつくようになれば、メッセージボックスを表示しなくてもいいと思っています。
ヒットしたセルに色がつき、次々に飛んでいけばいいと思います。
最後に、マクロを強制的に中断するマクロと、ついた色を一括で消すコードも必要です。
皆様、よろしくお願いいたします。
 
Sub 検索()
 s = InputBox("キーワードを入力してください。" & vbCrLf & _
          "OKを押すと、次々にキーワードに関連するセルにジャンプします。", "講師検索")
Dim sh As Worksheet
 
 For Each sh In ActiveWorkbook.Worksheets
 Set x = sh.Cells.Find(what:=s)
 If x Is Nothing Then GoTo p1
 MsgBox sh.Name & x.Address
 b = sh.Name & x.Address
 sh.Activate
 x.Activate
 '---
 Do
 Set y = sh.Cells.FindNext(after:=ActiveCell)
 If y Is Nothing Then GoTo p1
 If sh.Name & y.Address = b Then GoTo p1
 MsgBox sh.Name & y.Address
 y.Activate
 Loop
p1:
 Next
 
End Sub

回答
投稿日時: 18/09/19 20:17:54
投稿者: WinArrow
投稿者のウェブサイトに移動

>("検索トップ")のシートだけを検索するようにしたいです。
For Each の行とNextの行を削除
 
For の代わりに
Set Sh = Worksheets("検索トップ")
にすればよいです。
 
 
 
>ヒットしたセルに色がつき、次々に飛んでいけばいいと思います。
セルに色を付ければ良いです。
ヒント
Range("G1").Interior.Color = VbRed
 
ところでこの方法で色を設定するのは良いですが、
どこかのタイミングで消さなくてはいけませんね?
 
それより、条件付き書式を使うと、検索文字だけ入力すれば、消す必要はありません。
 

回答
投稿日時: 18/09/19 20:39:06
投稿者: simple

>キーワードをいくつか入力して検索すると、そのキーワードが入力されている
とありますが、複数のキーワードを同時指定することはないのですね。
 
それなら、「検索」の「すべて検索」が望むマクロとほぼ同等の機能を持っています。

・部分マッチですし、
・一覧性がありますし、
・クリックすることで、その行にジャンプすることが可能です。
一度試してみることを薦めます。
 
へたにマクロに頼るより、
ワークシートに操作要領を記入しておいて、
Excelに備わった機能を活用してもらうほうがスマートだと思います。
デフォルトの機能を使えないような人には、もっと学習を要望したらよいでしょう。
もうそういう時代です。

回答
投稿日時: 18/09/19 22:49:44
投稿者: WinArrow
投稿者のウェブサイトに移動

>誰でも簡単に検索できるように、メッセージボックスを表示させて、
  
「メッセージボードを表示させて」
この部分は、ともかくとして
  
検索して、そのデータをどのように使うのでしょうか?
その目的によっては、マクロを使わなくても、対応できると思います。
 検索キーとなる文字列を入力させるのに会社名の1部だけを指定する方法は、
 大量なデータを絞り込む方法としては有効な方法ですが、
それで取得したデータは、
お望みのデータではない可能性があります。
そうすると文字列を増やして再検索することになります。
 目的のデータにたどり着くまでに何回も検索することになりますが、
オペレータとしては、まどろっこく感じる人もいて、一発で目的のデータが検索できるようにするか/全く使わないかどちらかでしょう。
  
あいまい検索=マクロという方法論から入ると危険です。
せっかく作ったマクロも使われなくなる可能性があります。
  
検索キーを入力しなくても検索できる方法があります。
 検索したデータをどのように使うかで方法が変わってきます。
  
"あれば便利"というような、それこそあいまいな考えはやめて、
しっかりしたニーズを見極めてから方法を考えましょう。
 

回答
投稿日時: 18/09/20 19:33:09
投稿者: Suzu

こんばんは。
 
他の部分は既にみなさんが回答を寄せられていらっしゃいますので。。
 
マクロを強制的に中断する
 
の部分をば。。
処理に時間が掛かっているから、中断したいのでしょうが、
それは検索対象外のワークシートをループしているからではないでしょうか。
 
 
 
中断をさせる操作をユーザーにさせる為、実行中のマクロを中断させ、
操作待ちの状態を作る必要があります。
当然、待ち時間の分、処理終了までには時間を要する事となり、本末転倒になる恐れもあります。
 
それでも停止が必要であるなら。。
 
・フラグ変数を用意
・コードのループの条件に、フラグの内容を含める
   ループ内で DoEvents にて制御をユーザーに返す
・別ボタンを用意しフラグを変える。
 
の流れになります。
 
 
以下サンプル
 
ユーザーフォームに
ラベル : Label1
ボタン : btn_Start
ボタン : btn_Stop
 配置
 

Option Explicit

Dim flg As Boolean

Private Sub btn_Start_Click()
  Dim dtm As Date

  flg = False

  While flg = False
    If DateDiff("s", dtm, Now()) >= 1 Then
      dtm = Now()
      Me.Label1.Caption = Format(dtm, "hh:mm:ss")
    End If
    DoEvents
  Wend

End Sub

Private Sub btn_Stop_Click()
  flg = True
End Sub

回答
投稿日時: 18/09/22 12:27:12
投稿者: simple

私は、「すべて検索」は
>・部分マッチですし、
と書きましたが、少し誤解を生むかもしれないので補足します。
検索は部分マッチがデフォルトとなっているという意味でした。
 
つまり、「検索」メニューの「オプション」に
「セル内容が完全に同一であるものを検索する」というものがありますが、
これが

・部分マッチ(チェックが入っていない)
・完全マッチ(チェックが入っている場合)
の選択です。
Excelを開いた当初の段階(つまりデフォルト)では、チェックが入っていない部分マッチです。
しかし、一度、完全マッチの検索をしたあとは(部分マッチに変更するまでは)それが引き継がれます。
 
マクロでFindを使用するときにも、このことを念頭に置く必要があります。
もし、完全マッチの手作業検索をしたあとにマクロを実行すると
それは完全マッチにしかなりません。
マクロでは検索メニューが表示されないだけに、ユーザーは気づきにくいと思います。
 
間違いがない方法で、皆さんがよく使うのは、
引数に検索文字だけでなく、マッチ方法もその都度きちんと指定することです。
これはその他の引数についても同じことが言えます。
指定方法はヘルプを読んだり、マクロ記録をしてみてください。
 
以上、補足でした。

トピックに返信