業務に役立つExcelで検索エンジンVBAを5分で搭載。DL付。

シェアする

  • このエントリーをはてなブックマークに追加
  • Evernoteに保存Evernoteに保存
  • 0

業務に役立つExcelのデータベースファイリングに、VBAで作成した検索エンジンを5分で簡単に搭載出来ます。サンプルDL付です。

   はじめに

Excelでデータベース内の集計データを参照するようなファイル構成で、データ行数が、三桁四桁行くような膨大なデータになる場合、いちいちオートフィルタで絞ってとか、非常に面倒になるときがあるかと思います。

そんなとき、データに検索エンジンがあればな?と思われたことがないでしょうか?

そんなときに、お役に立てるのが、このVBA作成版の「検索エンジン」です。

オートフィルタがかかるファイルなら、どんなファイルにも簡単に設定出来ます。

大変適応力が高く、様々な事例にも応用が利く検索エンジンに仕上がっていると思います。

 検索エンジン搭載

   搭載条件

ここがポイントロゴ

  • オートフィルタがかかる、1セル1データのデータベースであれば、どんなファイルにも搭載可能です。
  • 起点となる列から右へ何列表示されるかを指定出来ます。例:B列から6列・G列までとかです。

   サンプルデータベース

(最後におまけDLのファイルのデータベースシートです)

検索エンジンデータベース

   検索エンジンのシート

まず、サンプル画像がこちら↓↓画像は全て拡大します。

検索エンジンシート

VBAで計算式が入る箇所は、3箇所

   CommandButton1

   CommandButton1内記述コードの説明

検索するシートを指定するコード行です。

検索する起点列を指定するコードです。(“b”) サンプルは、B列を検索する対象列に指定しています。

この行でも検索する起点列を指定するコードを書いています。

この行で、検索起点列から何列目までを表示させるかを指定しています。

7が指定されているので、B列から7列、つまりB列からH列までを表示させるという事です。

   CommandButton2

TextBox1つまり、検索窓に入力するワードをクリア(リセット)させるコードです。

   ListBox1

CommandButton1のコードと連動させています。検索結果をB列から7列のH列までを、ListBox1へ表示させますよ、というコードです。

   標準モジュール Module1

これは、必要なコードではありませんが、検索エンジンシートのスクロールを固定して、画面が動かないように、コード設定してあります。

A1:A1 で設定してあるように、VBAコントロール以外のセルポインタは、A1 しか置けません。

   使用方法

検索窓へ、検索ワードを入力。

画面例は、「あ」を入力。一番左の列「名称」が検索対象列となり、サンプルデータベースでは、B列が対象列に指定されていました。「あ」が含まれる全ての名称が表示されます。

検索エンジン検索例

この例では、「2」を検索ワードとして入力。「2」が含まれる全ての名称が表示されます。

検索エンジン検索例②

もちろんピンポイントのワードを入力すれば、その対象しか表示されません。

「あ210」をワードとして入力した例。

検索エンジン検索例③

様々なデータ運用に活用出来ます。下記は携帯番号データベースへの活用例。

検索エンジン活用例

この活用例の場合では、

へ変更させて4列表示にしてあります。

表示列を変更する場合は、ListBox1 のプロパティも修正します。

「ColumnWidths」が列と列の間隔です。

間隔を狭く=ポイントを少なくです。

リストボックス①

「Font」をクリックすると書式ウィンドウが開きます。

文字のポイントを調節しましょう。

リストボックス②

   ダウンロードファイル

ダウンロードExcelファイル  icon-arrow-circle-down

“Excelファイル検索エンジン雛形” をダウンロード Searchengine.xlsm – 1718 回のダウンロード – 47 KB

  • ご自身で、検索エンジンのシートを作成される方は、設置する各コントロールへ、VBAのコードをコピペして使用して下さい。
  • 一番手っ取り早いのは、ダウンロードして頂いたファイルのデータベースシートを差し替えてもらうのが、一瞬の作業で済みますし、それ以外のデータベースで使う場合もファイルごとコピペして、そのデータベースごとに差し替えて使って頂くのが楽かと思います。

次の記事 業務に役立つExcelで検索エンジンVBA応用!複数シート同時検索!へ!

業務に役立つExcel&VBA ファイルを連番で印刷。DL付。
業務に役立つExcel&VBA 今回は、印刷コントロールです。任意で設定した連番を付けて、連番枚数だけ印刷される大変便利なマクロをご紹介します。もちろん今回もダウンロ...

更に応用編 業務に役立つExcel&VBA!エンターでマクロの実行!

業務に役立つExcel&VBA!エンターでマクロの実行!
業務に役立つExcelのデータベースファイリングに、VBAで作成した検索エンジンを5分で簡単に搭載するのを以前記事にしました。今回は登録したマクロをコマンドボタンで...

   最後に

Excelに、[開発]タブを入れてない方は、忘れずに[開発]タブを入れておいて下さい。

このファイルが皆さまの業務にお役に立てれば何よりです。是非ご活用下さい。

以上、業務に役立つExcelで検索エンジンVBAを5分で搭載。DL付。でした。

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

スポンサーリンク

シェアする

  • このエントリーをはてなブックマークに追加
  • Evernoteに保存Evernoteに保存

フォローする


スポンサーリンク

コメント

  1. キタサン より:

    検索エンジンを利用させて頂いています。
    テキストボックスに入力後、エンターキーを押して検索ボタンを
    クリックしたと同様に検索させるにはどこを変更したらよろしいでしょうか?
    当方、VBA初心者でよろしくお願い致します。

    • なま たまご より:

      ご質問ありがとうございます。エンターキーを入力して入力結果を検索させる場合は、
      ①CommandButton1 内の記述を全て消去して下さい。
      ②以下の記述を、TextBox1 内へペーストして下さい。
      ③これで、検索結果はエンターを押した時点で表示されるようになります。値クリアは今まで通り、CommandButton2で使用出来ます。

      Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      If KeyCode = 13 Then ‘<- 加筆した箇所 13がエンターを押したときです。 Dim r As Range, ff As String, a(), n As Long, i As Long If Trim(Me.TextBox1.Value) = "" Then Exit Sub With Sheets("検索データベース") '<- 要変更(シート名) Set r = .Columns("b").Find(Me.TextBox1.Value, , , xlPart) '<- 修正(検索対象列) If Not r Is Nothing Then ff = r.Address Do ReDim Preserve a(25, n) For i = 0 To 25: a(i, n) = r.Offset(, i).Value: Next n = n + 1 Set r = .Columns("b").FindNext(r) '<- 修正(検索対象列) Loop Until ff = r.Address Else MsgBox "データなし": Exit Sub End If End With With Me.ListBox1 .Column = a .ColumnCount = 7 '<- 修正(列の表示数) End With  End if  '<- 加筆した箇所 に対応するEnd if End Sub 是非お試し下さい。

  2. ままびん より:

    こんにちは。

    蒸気の検索エンジンを使って、英語表現検索のマクロを作りたいと思っています。英和・和英両方で引ける&データベースの管理を容易にしたいため、複数のシートにデータを入れたい(検索されるデータ)のですが、複数シートにまたがって検索することはできますか?できるとしたら、どこをどのように変更したらいいのかご教授いただけませんか。

    初心者のため、行き詰っております。よろしくお願いします。

    • なま たまご より:

      ご質問ありがとうございます。
      何種類かの解決策がございますが、おそらくこれであろう認識での方法をお伝えさせて頂きます。
      ・検索する為の窓は 1つ
      ・検索する「検索ボタン」も 1つで複数シートを同時検索させる。
      ・検索した抽出結果のボックスは、1データシートに対して1つ。
       つまりデータベースが2つなら、抽出ボックスも2つ。データベースが3つなら、抽出ボックスも3つ。
      今回はデータベースが、和訳と英訳 2つあると仮定して解説します。

      ①抽出結果の、ListBox をもう一つ設置して下さい。ListBox1全体をコピペでも構いません。
      ②CommandButton1 のコードを全削除して頂き、以下のコードをペーストして下さい。

      Private Sub CommandButton1_Click()
      Call macro1
      Call macro2
      End Sub
      Sub macro1()
      MsgBox “和訳で検索します。”
      Dim r As Range, ff As String, a(), n As Long, i As Long
      If Trim(Me.TextBox1.Value) = “” Then Exit Sub
      With Sheets(“和訳データシート名”) ‘<- 要変更 Set r = .Columns("c").Find(Me.TextBox1.Value, , , xlPart) If Not r Is Nothing Then ff = r.Address Do ReDim Preserve a(24, n) For i = 0 To 24: a(i, n) = r.Offset(, i).Value: Next n = n + 1 Set r = .Columns("c").FindNext(r) Loop Until ff = r.Address '<- 修正 Else MsgBox "データなし": Exit Sub End If End With With Me.ListBox1 .Column = a .ColumnCount = 7 '<- 修正 End With End Sub Sub macro2() MsgBox "英訳で検索します。" Dim r As Range, ff As String, a(), n As Long, i As Long If Trim(Me.TextBox1.Value) = "" Then Exit Sub With Sheets("英訳データシート名") '<- 要変更 Set r = .Columns("c").Find(Me.TextBox1.Value, , , xlPart) If Not r Is Nothing Then ff = r.Address Do ReDim Preserve a(24, n) For i = 0 To 24: a(i, n) = r.Offset(, i).Value: Next n = n + 1 Set r = .Columns("c").FindNext(r) Loop Until ff = r.Address '<- 修正 Else MsgBox "データなし": Exit Sub End If End With With Me.ListBox2 .Column = a .ColumnCount = 7 '<- 修正 End With End Sub これで、一回の検索で、和訳英訳2つのデータベースか同時に検索が出来て、検索結果はそれぞれ対応した、ボックスに抽出されたかと思います。 一応検索は、c列を表示は、c列から7列になっていますので、お好みにご変更下さい。 また、今後とも「なまたまご情報局」を宜しくお願い致します。

      • ままびん より:

        早速のお返事ありがとうございました。

        やりたい処理ができるようになりました!次回は自分でも書けるよう勉強します。

        また、参考にさせていただきます。ありがとうございました。

        • なま たまご より:

          こちらこそお役に立てたなら大変嬉しいですが・・・
          申し訳ございません。コメントでコードを記載すると、行間が全て詰められてしまって、
          大変見づらくなってしまっていますね・・・
          今気が付きました。
          こちらのコメント返信の編集ではちゃんと行送りしてあるのですが、コメントに反映されると詰められてしまう仕様なんですね。
          工藤さん、ままぴんさんからのご質問への対応内容をまとめて、「検索エンジン応用編」として、近いうちに正式に記事にする事にします。
          その際、ままぴんさんへ回答していない他の種類の解決策も記事に致します。
          また、今後ともよろしくお願い致します。

          • ままびん より:

            なま たまごさん

            記事を書いていただけるとのこと、また読ませていただきます。

            ところで、教えていただいたマクロで、やりたいことができるようになったのですが、なにせ、単語や英文を検索するものなので、時々、折り返して2-3行になっているエントリーがあるのですが、長いものに関してはリストボックスで表示したときに、切れて、途中から表示されません。これを解決する手段はありますでしょうか。

            お手数ですが教えていただけると助かります。

          • なま たまご より:

            ままびん様
            前回のご質問内容が解決して何よりです。
            新たな、ご質問内容
            「折り返して2-3行になっているエントリーがあるのですが、長いものに関してはリストボックスで表示したときに、切れて、途中から表示されません。」
            の解決策ですが、
            ・検索表示列数を少なくして、Listbox プロパティ内 ColumnWidths を300なり400なり数字を大きくして頂ければ、表示文字数が多くなります。
            もちろん、表示文字数を多くするという事は、画面に表示出来る、列数も少なくなりますので、
            VBAコード内の
            With Me.ListBox1
            .Column = a
            .ColumnCount = 7 ‘<- 修正(列の表示数) End With End Sub で表示列数 7を5なり4なり表示列数を少なく調整して下さい。 もし、極力表示列を少なく出来ないのであれば、Listbox プロパティ内の、Fontで文字ポイントを少なくするしか手はないですが、 8ポイントがミニマムです。 なお、リストボックスの仕様上、列間隔は一定幅しか出来ません。 この列だけ広げて、この列だけ狭くというのは、同リストボックス内では出来ません。 以上を踏まえて、あとは、どこかで折り合いをつけながら調整して下さい。 それでは、今後とも「なまたまご情報局」を宜しくお願い致します。

  3. ぴん より:

    こんにちは。検索エンジンを使わせていただいております。
    おかげ様で仕事がスムーズになりました。
    本当にありがとうございます!!
    質問させてください。

    検索結果の表示をさせるときに、
    ・文字フォントを大きくする
    ・表示できる文字数を増やす
    (名称と品番の間隔をあければ解決するのかと思ったのですが、その方法がわかりませんでした。)
    ・複数検索された場合に、区切り線—みたいなものを入れることは可能なのでしょうか?

    たくさん質問をしてしまい申し訳ありません、
    また、初心者中の初心者のため教えていただければ幸いです。
    どうぞよろしくお願いいたします。

    • なま たまご より:

      ご質問ありがとうございます。
      紹介した検索エンジンをご活用下さり嬉しい限りです。
      それでは、下記質問。
      検索結果の表示をさせるときに、
      ・文字フォントを大きくする
      ・表示できる文字数を増やす
      これらは、VBAコードではなく、Listboxのプロパティで解決します。
      ・文字フォントは、プロパティ Font で色々変更出来ます。
      ・表示できる文字数は、フォントを小さくしたり、リストボックスでの列表示数で、調整して下さい。
      プロパティ内の、ColumnCount が表示させる列の数。ColumnWidths が列の幅です。
      リストボックス内に、行と行の区切り線は仕様上、無理なので、行間を開けたりとかで見やすくして下さい。
      プロパティ内 BackColor でボックス内全体の背景色を変更。BoderColor で外枠の線の色を変更。は出来ます。
      それでは、これからも「なまたまご情報局」を宜しくお願いします。

  4. ぴん より:

    なま たまご 様
    わかりやすく教えていただきありがとうございました。
    御蔭様で解決いたしました。
    御礼がおそくなりましたことお詫び申し上げます。
    今後ともよろしくお願いたします。

    • なま たまご より:

      ぴん様
      わざわざ、ご返信ありがとうございます。
      お役に立てて私も嬉しいです。
      今後とも「なまたまご情報局」をどうか宜しくお願い致します。

  5. ままびん より:

    なま たまご様

    ありがとうございました。記事も読ませていただきました。とてもわかりやすかったです!

    頼ってばかりで申し訳ないのですが、もう1点教えていただけますか。検索結果がリストボックスに表示された後、必要に応じて、その内容をコピペしたいと要望されました。リストボックス内の内容は今のところ、コピペできないのですが、この場合、選択した内容をクリップボードにコピーする、というようなコードを書く必要があるのでしょうか。それともほかの方法でできますか。

    よろしくおねがいします。

    • なま たまご より:

      ままびん様

      抽出されたリストボックスから任意に指定した行を、クリップボードにコピーの件。
      ご回答します。
      説明はかなり長くなる為、かいつまんで、Listbox1からの選択に絞ります。
      ①あらかじめ、Listbox1 プロパティ MultiSelect を0 single にして下さい。
      (任意で選択した1行のみが対象となりますから、複数行選択不可にします。)
      ②新たに クリップボードへコピー用の CommandButtonを設置下さい。
      (今回は、CommandButton8と過程して説明します。)
      ③CommandButton8 へ以下のコードを入れて下さい。
      Private Sub CommandButton8_Click()
      Dim lRow As Long, i As Long
      Dim ListNo As Long
      ListNo = ListBox1.ListIndex
      If ListNo < 0 Then MsgBox "任意の行を選択指定して下さい。" Exit Sub End If With Worksheets("検索エンジン") With .Range("B1") For i = 0 To 6 .Cells(2, i + 1).Value = ListBox1.List(ListNo, i) Next i End With End With Range("B2:H2").Select Selection.Copy End Sub ④抽出は7列だと仮定します。 このコードでは一旦、リストボックスからセル上へ抽出し直してからクリップボードへコピーしています。 For i = 0 To 6 これが7列指定です。 4列であれば 0 TO 3 にして下さい。 With .Range("B1") B1を起点とし .Cells(2, i + 1).Value = ListBox1.List(ListNo, i) ここで、何行目に抽出する指定をしています。 .Cells(2, i + 1)➡B2行から7列 .Cells(3, i + 1)➡B3行から7列 よって、B2行から7列なので、下記指定が Range("B2:H2").Select  となっています。 つまり B2セルからH2セルが抽出用セルにします。 使い方はいたって簡単で、抽出された Listbox1 から任意に行を指定して、CommandButton8 を押すだけ。 セルに転記され、かつクリップボードへコピーされます。 何度でも上書きされて転記コピーされるので便利です。 転記用セルは表示してもよし。非表示にして隠してもよし。お好みで。 以上になります。 お役に立てれば幸いです。

      • ままびん より:

        なまたまごさま

        お忙しい中本当にありがとうございます。

        リストボックスからコピーができるようになりました。ただ、私がちょこちょこいじったからなのか、MultiselectをSingleに変えると、

        Private Sub ListBox1_Click()

        Range(“a:a”).Find(What:=ListBox1.Value, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False).Activate

        End Sub

        のRange…~.Activateがデバッグで黄色くなってしまいます。

        Multiselectをmultiのままにしておけば、問題なく動いてはくれます(複数選択はできてしまいますが、コピーされるのは1つのみ)。

        どう直してよいかわからないのですが、とりあえず、やりたいことはできているのでそのままmultiの設定で使ってます。

        今までに変更を加えて出来上がったものは、そもそものものに加えて、
        1つの検索ボタンで複数シート検索ができるようにし、コピーボタンを追加して、リストボックスからコピーができるようにしました。また、英単語&例文データのようなものを検索するためのものなので、ひとつのリストボックスだと字数制限が厳しいので、もうひとつ追加して、
        検索ボタンをクリックすると、A列を検索し、Aから3つをリストボックス1、D列を検索し、Dから2つをリストボックス2に表示としました。すなわち、以下のような感じです。

        Aカラムー見出し語 
        Bカラムー訳語 
        Cカラムー備考

        の3つをリストボックス1に表示し、

        Dカラムー見出し語(もしくは例文訳)
        Eカラムー例文

        の二つをリストボックス2に表示するようにしました。

        ーー
        ここまで作れたのも本当になまたまご様のおかげです。ありがとうございます。

  6. ヒロキン より:

    はじめまして。検索エンジンを利用させて頂いております。
    検索結果が複数表示された際のスクロールバーを、右側ではなく左側に表示させることは可能でしょうか?

    • なま たまご より:

      ヒロシキ様
      せっかくご質問頂きましたが、スクロールバーを左へという発想が今まで無かったので、わかりかねます。
      お役に立てず申し訳ありません。

  7. なお より:

    はじめまして。検索エンジンを利用させて頂いております。例えばリストにはデータベース中のB列の1列のみリスト表示をさせていて、B列の名前が重複していた場合、リスト上には表示しないという事は可能でしょうか?

    • なま たまご より:

      ご返信遅れまして申し訳ありません。
      こちらのブログは完全に放置していて気が付くのが遅れましたm(__)m
      ご質問の内容ですが、いまいち何をされたいのが容量を得ません。
      B列を常時表示させておく?そして重複した場合は表示から削除。そういう事でしょうか?
      それであれば、VBAではなく関数だけで可能ではないでしょうか?
      以前、記事にしたこちら
      業務に役立つExcel重複データ最新のみを抽出する関数。
      http://rawegg.sakuraweb.com/excel-008/
      をご参考にしてみて下さい。
      宜しくお願いします。

  8. ヨシ より:

    こんにちは。検索エンジンダウンロードさせて頂き、便利に使わせてもらっています。

    表示列やフォントを変更する際、ListBox1のプロパディの変更・修正とありますが、この開き方?変更の仕方がわかりません。
    詳しく教えていただけないでしょうか。宜しくお願い致します。

    • なま たまご より:

      ご返信遅れまして申し訳ありません。
      こちらのブログは完全に放置していて気が付くのが遅れましたm(__)m
      そうですね。セルがA1以外、アクティブに出来ないコードが入ってしまっているので、そのコードを一時的に消さないと、
      listboxのプロパティを開けません。説明不足で申し訳ありませんでしたm(__)m
      では
      開発→コードの表示→標準モジュール→Module1
      の中に入っているコード。
      Sub AUTO_OPEN()
      Worksheets(“検索エンジン”).ScrollArea = “A1:A1”
      End Sub
      これは、ファイルを開いたらA1:A1しか選択出来ないようにするコードです。
      これを一度削除して下さい。
      削除後ファイルを開いたら、開発→デザインモードからlistboxのプロパティを開けるようになります。
      宜しくお願いします。

  9. ぺんぎん より:

    はじめまして。検索エンジンを活用させて頂いています。

    質問させて頂きたいのですが、
    サンプルですと検索起点から右の列をリストボックスへ表示させていますが、B~H列を表示させるのではなく、A~H列(検索にヒットしたセルの行全て)を表示させることは可能なのでしょうか?

    どうぞよろしくお願いします。

    • なま たまご より:

      ぺんぎん様 
      返信遅くなりまして、申し訳ございません。

      以下の3カ所をご自身のデーターベースに合わせて変更下されば問題なく解決します。

      1)Set r = .Columns(“A”).Find(Me.TextBox1.Value, , , xlPart) ‘<- 修正(検索対象列) BからAに。 2)Set r = .Columns("A").FindNext(r) '<- 修正(検索対象列) 同じくBからAに。 3).ColumnCount = 8 '<- 修正(列の表示数)7から8に。表示した列数。 表示数を増やせば、必然。LISTBOXプロパティでの調整も必要になってきます。 よろしくお願いします。

  10. なお より:

    お返事ありがとうございました。
    質問の仕方が悪く、申し訳ありません。
    検索した結果、もしもリストボックス上のリストに
    重複している内容があった場合、
    リストボックス上には重複しないように表示する
    というお問合せになります。
    よろしくお願い致します。

トップへ戻る