今回は「あいまい検索」を極めてみたいと思います。
エクセルには標準で検索機能がありますが、それは 検索ワードが入力されている場所を「ここですよ」って指し示してくれるだけです。
そうじゃなくて、検索ワードに引っかかった項目(行)だけ「抽出」しかもヒットしたものは「複数抽出」したいってこと、ありませんか?(つまりトップ画像 ↑)
文字列の先頭だろうが 途中だろうが「あいまい検索」でキーワードをしっかりヒットさせちゃいましょう。
あいまい検索の仕組みと手順
シートは2つに分ける
今回のサンプルでは2つのワークシートを使います。
ひとつは「検索」シート。
検索ワードを入力するセル(セルD1)と、検索結果を表示するシートです。
▲「検索」シート
そして、もうひとつは「データ」シートです。
こちらは検索対象となるワークシートです。
▲「データ」シート
ポイント
表の左側に検索の仕組みづくりのための列を4列確保しておきます。
検索シートでの作業1
作業をしやすくするために「検索」シートのセルD1に「キーワード」という名前をつけておきます。
▲セルD1に「キーワード」を命名
※セルに名前をつける方法がわからない方は、過去記事をご確認ください。
さらに、セルD1に「神奈川」と入れておきます。(テスト用)
データシートでの作業
「データ」シートでの作業に移ります。
D列の数式
データシートの セルD2に数式を入力します。数式は;
つまり、E列からH列にかけて入力された値をすべてつなぐ数式です。
セルD2に数式を入力したら、その数式をセルD11までコピーします。
▲D列に数式が入った(わかりやすく列の幅をひろげています)
C列の数式
つぎにC列です。
セルC2には以下の数式が入ります;
IF関数 と COUNTIF関数が使われています。数式の意味は;
ってことです。
ポイント
数式中の「キーワード」を挟むように "*"(アスタリスク)が前後に入っているのは「ワイルドカード」と呼ばれるものです。つまり「キーワード(神奈川)の 前後の文字はなんでもOK」ということ。ここが、あいまい検索 のキモです。
(使い方を詳しく知りたい方は 過去記事 をご覧ください。)
セルD2の中にキーワード(=神奈川)が見つかったので、セルC2には「神奈川」と表示されました。
セルC2に数式を入力したら、その数式をセルC11までコピーします。
▲C列に数式が入り、キーワード「神奈川」を含む行にはキーワードが表示された
B列の数式
つぎにB列です。
セルB2には以下の数式が入ります;
この数式は、C列に「神奈川」と表示されている数をカウントします。
数式中でセル範囲をしめす $C$2:C2 の左側(先頭)だけが 絶対参照 になっているので注意しましょう。
数式をセルC11までコピーした状態か下の図です。
C列に2つ目の「神奈川」が見つかると、B列の数字は「2」に、3つ目の「神奈川」が見つかると「3」が表示されています。
A列の数式
A列に数式を入れていきますが、これはカンタン。
B列の値とC列の値をつなげるだけです。
つまり、セルA2に入る数式は;
▲こんな感じ
この数式をセルA11までコピーすると下図になります。
データ範囲に名前をつける
最後にデータの入ったセル範囲に名前をつけます。
名前をつけるセル範囲は、セルA2からセルH11まで。「データベース」と命名します。
▲セルA2からH11まで(赤枠)を選択して「データベース」と名前をつける
※セルに名前をつける方法がわからない方は、過去記事をご確認ください。
検索シートでの作業2
ふたたび「検索」シートに戻って作業をします。
セルB4に数式を入力します。
セルB4に入る数式は;
A列の数字とキーワードの組み合わせ(=1神奈川)をデータベース(「データ」シートのセルA2~H11)のA列から探させて、データベースの5列目を表示させます。
※ IFERROR関数 はエラーを表示させないためだけに入れているだけです。
▲セルB4には「山田」と表示された
数式をセルE2までコピーすると、すべてのセルに「山田」が表示されるので、セルC4からE4の数式を少し編集します。
数式の中で修正するのは「5」の部分。ここをそれぞれ、C列なら「6」、D列なら「7」、E列なら「8」にします。
「5」の部分だけ変更する。
正しく修正されると下図のようになります。
▲1件目が抽出された!
セルB4からE4までの数式を13行目までコピーすると、キーワードを含む3件が抽出されます。
▲ 複数抽出 に成功
テストを兼ねて、キーワードに「八戸」と入れてみると、、、
▲データベースに「八戸」を含む2件が抽出される
最後の仕上げに、セルA2に「検索に〇件 HIT」と表示させるための数式を入れます。
入る数式は;
検索結果に表示された件数をピックアップします。
▲セルA2に検索結果の件数が表示されて完成!
この仕組みを作るのに使用された関数は;(登場順)
IF関数
COUNTIF関数
IFERROR関数
VLOOKUP関数
COUNTA関数
COUNTBLANK関数
でした。最後の2つはおまけですけどね。
関連記事とのリンクも張っておきましたので、興味のある方はそちらもご覧ください。
以上で『エクセル「あいまい検索」で複数抽出する方法と必要な関数』は終了。
その他の関連記事と合わせて仕事や趣味に役立ててください。
関連記事・広告