エクセル「あいまい検索」で複数抽出する方法と必要な関数

今回は「あいまい検索」を極めてみたいと思います。

エクセルには標準で検索機能がありますが、それは 検索ワードが入力されている場所を「ここですよ」って指し示してくれるだけです。

そうじゃなくて、検索ワードに引っかかった項目(行)だけ「抽出」しかもヒットしたものは「複数抽出」したいってこと、ありませんか?(つまりトップ画像 ↑)
文字列の先頭だろうが 途中だろうが「あいまい検索」でキーワードをしっかりヒットさせちゃいましょう。



あいまい検索の仕組みと手順

シートは2つに分ける

今回のサンプルでは2つのワークシートを使います。

ひとつは「検索」シート。
検索ワードを入力するセル(セルD1)と、検索結果を表示するシートです。


▲「検索」シート

そして、もうひとつは「データ」シートです。
こちらは検索対象となるワークシートです。


▲「データ」シート

ポイント
表の左側に検索の仕組みづくりのための列を4列確保しておきます。

検索シートでの作業1

作業をしやすくするために「検索」シートのセルD1に「キーワード」という名前をつけておきます。


▲セルD1に「キーワード」を命名

セルに名前をつける方法がわからない方は、過去記事をご確認ください。

さらに、セルD1に「神奈川」と入れておきます。(テスト用)

データシートでの作業

「データ」シートでの作業に移ります。

D列の数式

データシートの セルD2に数式を入力します。数式は;

E2 & F2 & G2 & H2

つまり、E列からH列にかけて入力された値をすべてつなぐ数式です。

セルD2に数式を入力したら、その数式をセルD11までコピーします。


▲D列に数式が入った(わかりやすく列の幅をひろげています)

C列の数式

つぎにC列です。
セルC2には以下の数式が入ります;

=IF (COUNTIF (D2, "*" & キーワード & "*" ) >0, キーワード, "")

IF関数COUNTIF関数が使われています。数式の意味は;

D列の値の中に キーワード が1つ以上入っていたら キーワードの値 を表示してね

ってことです。

ポイント
数式中の「キーワード」を挟むように "*"(アスタリスク)が前後に入っているのは「ワイルドカード」と呼ばれるものです。つまり「キーワード(神奈川)の 前後の文字はなんでもOK」ということ。ここが、あいまい検索 のキモです。
(使い方を詳しく知りたい方は 過去記事 をご覧ください。)

セルD2の中にキーワード(=神奈川)が見つかったので、セルC2には「神奈川」と表示されました。

セルC2に数式を入力したら、その数式をセルC11までコピーします。


▲C列に数式が入り、キーワード「神奈川」を含む行にはキーワードが表示された

B列の数式

つぎにB列です。
セルB2には以下の数式が入ります;

=COUNTIF ( $C$2:C2, キーワード )

この数式は、C列に「神奈川」と表示されている数をカウントします。
数式中でセル範囲をしめす $C$2:C2 の左側(先頭)だけが 絶対参照 になっているので注意しましょう。

数式をセルC11までコピーした状態か下の図です。

C列に2つ目の「神奈川」が見つかると、B列の数字は「2」に、3つ目の「神奈川」が見つかると「3」が表示されています。

A列の数式

A列に数式を入れていきますが、これはカンタン。
B列の値とC列の値をつなげるだけです。

つまり、セルA2に入る数式は;

B2 & C2


▲こんな感じ

この数式をセルA11までコピーすると下図になります。

データ範囲に名前をつける

最後にデータの入ったセル範囲に名前をつけます。
名前をつけるセル範囲は、セルA2からセルH11まで。「データベース」と命名します。


▲セルA2からH11まで(赤枠)を選択して「データベース」と名前をつける

セルに名前をつける方法がわからない方は、過去記事をご確認ください。

検索シートでの作業2

ふたたび「検索」シートに戻って作業をします。

セルB4に数式を入力します。

セルB4に入る数式は;

=IFERROR ( VLOOKUP ($A4 & キーワード, データベース, 5, FALSE), "" )

A列の数字とキーワードの組み合わせ(=1神奈川)をデータベース(「データ」シートのセルA2~H11)のA列から探させて、データベースの5列目を表示させます。

IFERROR関数 はエラーを表示させないためだけに入れているだけです。


▲セルB4には「山田」と表示された

数式をセルE2までコピーすると、すべてのセルに「山田」が表示されるので、セルC4からE4の数式を少し編集します。

数式の中で修正するのは「5」の部分。ここをそれぞれ、C列なら「6」、D列なら「7」、E列なら「8」にします。

=IFERROR ( VLOOKUP ($A4 & キーワード, データベース, 5, FALSE), "" )

5」の部分だけ変更する。
正しく修正されると下図のようになります。


▲1件目が抽出された!

セルB4からE4までの数式を13行目までコピーすると、キーワードを含む3件が抽出されます。


複数抽出 に成功

テストを兼ねて、キーワードに「八戸」と入れてみると、、、


▲データベースに「八戸」を含む2件が抽出される

最後の仕上げに、セルA2に「検索に〇件 HIT」と表示させるための数式を入れます。
入る数式は;

="検索に " & COUNTA(B4:B13) - COUNTBLANK(B4:B13) & "件 HIT"

検索結果に表示された件数をピックアップします。


▲セルA2に検索結果の件数が表示されて完成!

この仕組みを作るのに使用された関数は;(登場順)

IF関数
COUNTIF関数
IFERROR関数
VLOOKUP関数
COUNTA関数
COUNTBLANK関数

でした。最後の2つはおまけですけどね。
関連記事とのリンクも張っておきましたので、興味のある方はそちらもご覧ください。

以上で『エクセルあいまい検索」で複数抽出する方法と必要な関数』は終了。
その他の関連記事と合わせて仕事や趣味に役立ててください。

関連記事






-関数, EXCEL&VBA
-, , , , ,

© 2021 h1r0-style.net