Excelで情報検索ツールを作成する

01-Excelの使い方
2023.08.18

情報を見つけるための鍵、それが『検索』です。デジタル時代において、新しい情報を見つけるためには、山のような情報の中から目的のものを速やかに見つけるスキルが重要です。『絞り込み』は、条件を使って情報をしぼり込む方法。そして、『抽出』は、必要な情報だけを取り出して整理する方法です。これらの基本的なテクニックをマスターすることで、データの宝庫を効果的に活用できるようになります。

本文では、これらの方法をシンプルに解説しています。さらに一覧表から情報を自動的に抜き出したリストを表示するツールの作成方法を紹介します。

パソコンは検索する道具

パソコンは、現代社会において重要な役割を果たすツールです。その中でも、パソコンの最も基本的な使い方は、情報を探し出すことです。私たちの日常生活において、インターネットを使って新しい情報を調べたり、データを整理したりすることは、パソコンの利用法の中で特に重要です。

情報を探し出すことは「検索」と呼びます。「検索」はインターネットでは検索エンジンを通じて行われます。この能力により、必要な情報を迅速に入手し、問題解決や判断の裏付けとすることができます。さらに、データ整理も同様に重要です。ファイルやフォルダを適切に名前付けたり、タグをつけたりすることで、保存したデータをすぐに見つけることができるようになります。

その一方で、パソコンはデータ処理と分析をする道具とも言われます。データ分析は「検索」の一環として位置付けられます。数値や文字データを整理し、必要な情報を探し出し、それらを集計することで今まで見えにくかった傾向や特徴が見つかるのです。

また、コミュニケーションと共有においても「検索「は欠かせません。膨大な量の電子メールの文書の中から必要な連絡事項が書いてあるメールだけを表示するといったことは、スムーズなコミュニケーションと円滑な業務遂行を支える手段です。

パソコンは「検索」能力により、情報の入手、整理、処理、共有という多岐にわたる業務を支える道具です。情報化社会において、この能力はますます重要性を増しており、効果的な活用が求められています。わかりやすい入力や日ごろからデータを整理することは、最終的に「検索」をするための準備なのです。

「検索」「絞り込み」「抽出」

パソコンの用語として一覧表があってその中からある値を見つけ出すことを「検索」と呼びます。Excelでも検索ができ、検索したら該当するセル1つが検索されます。しかし、一覧表管理として考える場合の検索は、ひとつのセルだけを指すのではなく、1行すべてが検索されたデータと考えます。

もし、検索したデータを削除してくださいという指示があったとき、そのセル1つを削除するとデータがずれてしまい、一覧表が崩れて使い物にならなくなるからです。

つまり検索して1つのセルを見つけたら、その後にそのセルの1行を選択する必要があります。

一覧表で検索する際には、1つの項目ごとではなく、複数の項目にわたって検索する必要もあります。例えば「取引先」が「A社」で、なおかつ「取引日」が「10月」のような場合です。一覧表管理として考えると、このような場合は「検索」ではなく「絞り込み」という操作を行います。

「絞り込み」を行うと、該当しない行は非表示になるので、該当する行すべてが一覧表として表示されます。あくまで非表示なので元の表が消えるわけではなく、再表示すれば元の表に戻すことができます。

「絞り込み」の場合は、その結果をどこかにコピーしておかなければ、絞り込んだ結果の一覧表を取っておくことができません。そこで「絞り込み」の結果を、絞り込みと同時に別の場所に作成することを「抽出」と呼びます。「抽出」の場合は、元の表が非表示にならないので、そのまままた別の絞り込みや抽出をすることができます。

Excelの絞り込みと検索

Excelでももちろん、「検索」「絞り込み」「抽出」をする機能があります。そのうち「絞り込み」「抽出」について考えてみましょう。

Excelには「絞り込み」「抽出」をする手段として3つの方法があります。それぞれの特徴を挙げます。

「オートフィルター」機能

「オートフィルター」は、「絞り込み」を行う機能で、元のデータ表内で特定の条件に合致しない行を非表示にするための機能です。条件に合致しないデータがある行が自動的に非表示にされ、残されたデータのみが表示されます。元の表のデータは変更されず、非表示にされた行だけが非表示にされます。

「詳細フィルター」機能

「詳細フィルター」は、オートフィルターに比べて有名ではないのですが、「絞り込み」または「抽出」を行う機能で、元のデータ表から特定の条件に基づいてデータを非表示にすることも、絞り込まれたデータを元の表とは別の場所に新しい一覧表として作成することもできる機能です。オートフィルターに比べて面倒なのは、どれを対象にするのかの条件、どの項目を抜き出すかを、セル範囲に設定する必要があります。

「FILTER」関数

上記の2つは機能なので、一回ごとに操作する必要があります。しかし、「FILTER」関数は計算式なので、一回、式をセル上に作成しておけば、いつの間にか結果が「抽出」されています。一番のメリットは操作し忘れがないということです。「抽出」なので元のデータはそのまま、関数を設定した個所に結果が表示されます。デメリットとしては、新しい関数ですのでExcel2019以前では利用できないことが挙げられます。

Excelでの自動化に向いているのはどの方法？

自動化と相性が良いのは明らかに「FILTER」関数です。一回設定すれば、あとは勝手に「抽出」されますし、「絞り込み」ではないので元の表を崩すこともありません。第一優先で「FILTER」関数を思い浮かべたいところです。

しかし、「FILTER」関数はExcel2019以前では使えません。Excel2019は2025年10月までの延長サポートがあり、それまでは利用することができます。それ以降はサポートがなくなるので、その時のインターネット環境やWindowsの状況との相性で不具合が出たとしても対応されないので、使い続けるのは非常に危険な状態になるので、使われなくなっていくと思いますが、それまでは「FILTER」関数しか使えないような仕組みにするのは早いかもしれません。

さて、そうなると「FILTER」関数の代替手段を考えなければならなくなります。その候補として、どの方法がよいでしょうか。

「詳細フィルター」機能とマクロ

「FILTER」関数は「抽出」機能を持っています。しかし、「抽出」は「詳細フィルター」機能の特長です。そのため、「詳細フィルター」機能が次の選択肢となります。ただし、残念ながら「詳細フィルター」は操作を一回一回行う必要がある「機能」なのです。

「詳細フィルター」機能を自動的に実行したい場合、その方法は実はマクロを利用することです。マクロは通常の機能では実現が難しい動作に対処する際に使用されます。つまり、マクロは普段の操作では行えないことに対応するために考えられるものです。

では、その自動化の前に、まず「詳細フィルター」の機能の操作方法について解説していきましょう。

次のような一覧表があります。テーブルになっていて「販売」という名前がついています。ここで大事なのはこのような一覧表の1行目には項目名が設定されていること、この項目名がとても大事です。

販売先がC社で仕入先がK販売のデータは、6行目、8行目15行目です。そのデータのみをG列に抽出したいと思います。

「詳細フィルター」機能には、元のデータの範囲はもちろん必要ですが、その他にどのデータを抽出するかを示す「検索条件範囲」、どの項目を抽出するかの「抽出範囲」をセルに作成する必要があります。

今回の場合は、「販売先」が「C社」、「仕入先」が「K販売」の2項目の検索条件が必要になります。抽出する項目は全項目とします。

まず検索条件が2項目なので、G1からH2のセル範囲に次のように入力します。項目名は元データにあるものをそのまま使ってください。

次に抽出項目が全項目なのでG3からのセル範囲に次のように入力します。元データの1行目をコピーしても構いません。抽出範囲も項目名は元データにあるものをそのまま使うのがポイントです。

これで前準備は終わりです。

ここで、データタブの「詳細設定」をクリックします。

そうすると次の「フィルターオプションの設定」が表示されます。すかさず「指定した範囲」をクリックします。これにより「絞り込み」ではなく「抽出」になるのです。

「リスト範囲」には元データ範囲、「検索条件範囲」、「抽出範囲」を指定します。すべて項目名を含めます。項目名を含めることにより、Excelがどの項目をどう見てどう抽出するのか判断できるのです。

OKボタンをクリックすると抽出されます。

この動作をマクロの記録で記録し、検索条件範囲の横にそのマクロを動かす図形を置いておけば、条件を変更してそのボタンをクリックするだけで抽出される仕組みにすることができます。

「詳細フィルター」機能をVBAで自動化

次に「詳細フィルター」機能を「FILTER」関数のように、特に抽出するという動作をせずとも、一連の動作の中で動作させるようにしたいと思います。どのタイミングで抽出の動作をすればいいでしょうか。

1つは、検索する条件を変えたときだと思います。検索の条件が変わるとき、つまり、セルの書き換えが行われた時、という動作です。

このタイミングはVBAを使えば、Excelがシート内のセルの内容が変わった時を検知し、その時に動作させることができます。これはシート見出しを右クリックして「コードの表示」をクリックすると設定することができます。

この画面は、VBAを操作するVBEの画面です。

この「(General)」と書いてあるところをクリックすると「Worksheet」をクリックすると、その右側が「SelectionChange」になります。下には「Private Sub Worksheet_SelectionChange(ByVal Target As Range)」が表示され、これは、このシートの選択しているセルが変わったら、というときに動き出すものを指しています。

今度はその「SelectionChange」をクリックし、「Change」を選択します。すると「Private Sub Worksheet_Change(ByVal Target As Range)」となり、これがセルの値が変わったら、になります。その下の「End Sub」までの間に抽出の動作を書き入れます。

「Private Sub Worksheet_SelectionChange(ByVal Target As Range)」から「End Sub」は不要なので消しましょう。

抽出の動作を日本語で整理すると、「もしも、変化したセルがセルG2またはセルH2だった場合は、元データを販売テーブルの範囲で検索条件範囲がG1からH2のセル範囲で抽出範囲がG4からK4のセル範囲で抽出する」という動作になります。

「もしも」で動作する内容は「If」文で「If」から「End If」の範囲に書きます。

変化したセルは「Target」で読み出すことができますが、そのセルが「G2」のような形で読み出すには「Target.Address」で読み出します。この場合は「$G$2」のような絶対参照で読み出されます。

抽出をするVBAは「元データのセル範囲.AdvancedFilter xlFilterCopy,検索条件範囲,抽出範囲」として、表現します。

範囲は「Range」で表し、「Range(“G1:H2”)」でG1からH2のセル範囲を表します。テーブル範囲も同じように表現出来て「Range(“販売”)」で販売テーブルのデータのみの範囲となり、項目名を含めたすべては「Range(“販売[#All]”)」で表現できます。

ここまでの情報をまとめると次のようなVBAになります。

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$G$2" Or Target.Address = "$H$2" Then
    Range("販売[#All]").AdvancedFilter xlFilterCopy, Range("G1:H2"), Range("G4:K4")
  End If
End Sub

このVBAのそれぞれの行は、次のような意味になります。

  1. シート内のセルの値が変化した時
  2. もしも変化したセルがG2または変化したセルがH2だったら次の動作をします。
  3. 販売テーブルの項目名が入った範囲を元データとして、G1からH2までのセル範囲を検索条件範囲、G4からK4のセル範囲を抽出範囲として抽出を行います。
  4. もしも変化したセルがG2または変化したセルがH2だったらの動作ここまで
  5. シート内のセルの値が変化した時の動作ここまで

これをVBEに記載したら、×ボタンでVBEを閉じれば完成です。

これで、見かけ上ですが、FILTER関数と同じような動作をさせることに成功しました。

まとめ

現代の情報社会において、パソコンは重要な検索のツールです。情報の中から必要なデータを引き出すテクニックが『抽出』です。FILTER関数を使えば、簡単にデータを選別できます。ただし、FILTER関数はExcelのバージョンによっては搭載されていないこともあります。そのような場合には、自動化された抽出の手段が求められます。ここで登場するのがVBAです。

パソコンは「検索」する道具です。FILTER関数または、VBAと詳細フィルターの機能を組み合わせることで、パソコンの情報の抽出能力を最大限に引き出すことができます。

今回は抽出を例に機能とVBAの組み合わせを紹介しましたが、このような何かの機能+VBAで思いもよらないとてつもない効率化をするためのツールを作成することができるのです。

タイトルとURLをコピーしました