XLOOKUP 関数で作る名前からメールアドレスを検索するシステム

今回は VLOOKUP 関数や XLOOKUP 関数を使って作る名簿検索システムの四回目の投稿です。前回までは、部署名と内線番号を取り出すツールを作りました。今回は、同じ仕組みを使って、会社名とメールアドレスいわゆるメアドを検索するツールを作ります。

名前のところにグループ名、メールアドレスにその部署の人のメアドをカンマで区切って入力しておくと、クリックでその部署全員に送信するメールのアドレスができるので便利です。

では、今回の記事に入っていきます。

まずは、今回のシステムの概要です。

同姓同名の検索が可能
名字だけでの検索と、複数候補の表示
下の名前でも検索可能

同姓同名の検索
二人の山田さん検索
下の名前で検索
XLOOKUP 名簿検索システム処理の概要
COUNTIF 関数で検索文字が含まれる名前一致の列を追加
COUNTIF 関数で表示対象レコードの表示順の列を追加する
XLOOKUP 関数で名簿の処理用テーブルを検索
おまけ。検索結果が多くて溢れた場合の表示
XLOOKUP まとめ

同姓同名の検索

データベースを見ると、田中太郎さんは二人居ます。それぞれ営業部と設計部に所属しています。
二人とも表示されていますね。

二人の山田さん検索

山田さんだけど、下の名前が分からないというときは「山田」で検索すると該当する二人が表示されます。

下の名前で検索

いつも下の名前でしか呼んだことがないというときにも名前だけで検索できます。

それでは、その仕掛けを説明していきます。

XLOOKUP 名簿検索システム処理の概要

XLOOKUP 関数で検索をする前に、まず準備として次の2つの処理を行います。この２つの下準備が今回の肝ですから、しっかり理解してください。

データベースの各行（以下レコードと表示します）の情報が、検索セルに入力された文字を含むかどうかをチェックした情報を追加します。
検索文字が含まれる複数の対象レコードに、1から順に一連の番号を振ります。

そして次に、XLOOKUP 関数で検索し検索結果の表へ 抜き出して 表示させます。

COUNTIF 関数で検索文字が含まれる名前一致の列を追加

検索文字がそのレコ－ドの名前の列に含まれるかどうかについて、COUNTIF 関数を使って調べます。第二回のバージョンで使った SUBSTITUTE 関数のネストによる空白除去も使います。

データベースの名前（G3セル）の文字列に検索文字列（C2セル）が含まれているかを調べるために、 COUNTIF 関数を使っています。ここで COUNTIF 関数を使うのはワイルドカードを使うためです。 COUNTIF 関数の第二パラメータはちょっと複雑になってしまいましたが、 SUBSTITUTE 関数のかたまり（C2）を＊で囲んだ形となっています。

SUBSTITUTE 関数を除いてみると見やすいです。
COUNTIF(G3, “*” & $C$2 & “*”)となっていて、検索文字列の前と後に＊を付加しています。これで検索文字がデータベース文字列の最初や最後、途中のどこにあってもヒットとなるわけです。

=COUNTIF(G3,"*"&SUBSTITUTE(SUBSTITUTE($C$2,"　","")," ","")&"*")
----- SUBSTITUTE...を除くと
=COUNTIF(G3,"*"&$C$2&"*")

ここで、検索範囲は1つのセルだけですから、ヒットすれば1しなければ0となります。
上の例では、二人の田中太郎さんのレコードが1になっていて、他のレコードは0となっているのが分かると思います。

COUNTIF 関数で表示対象レコードの表示順の列を追加する

シリアル番号を1から振って行くには、どうすれば良いでしょうか。色々方法はあると思いますがExcel的に簡単な方法として「先頭のレコードから自分のレコードまでの1の数をカウントする」方法をとっています。

1行目のレコードでは先頭行から1行目までのカウント値。2行目のレコードでは先頭行から2行目までのカウント値と自分のレコードを含めて前に何個の1があるかを数えることでシリアル番号を作り出します。

名前検索が一致しない行はまず順序なしとします。一致するレコードでは自分のレコードを含めて上に1が何個あるかをカウントします。
例では、一行目の田中太郎さんは名前一致の1が1個なので1、6行目の田中太郎さんは名前の一致の1が自分を含めて2個なので2となります。

=IF(K3,COUNTIF($K$3:K3,1),"")   ･･･1行目
=IF(K3,COUNTIF($K$3:K4,1),"")   ･･･2行目
=IF(K3,COUNTIF($K$3:K5,1),"")   ･･･3行目
・・・とカウントする範囲が増えていくように設定します。
なお一行目をコピペするだけでできます。

XLOOKUP 関数で名簿の処理用テーブルを検索

VLOOKUP 関数は一番左で検索しその右の列を結果として返す関数でしたが、 XLOOKUP 関数はその制約がありません。検索の列が一番左でなくてもよく、返したい列も指定できる。これは、とっても便利です。今まで苦労してたのが嘘のように快適です。

作成した処理用テーブルを XLOOKUP 関数で検索して、名前、部署、メアドをC列D列E列に表示させます。今回は1～5までの表示としていますが、実際に作るときに必要に応じて下に伸ばせばその分表示可能となります。

XLOOKUP 検索 — 作成した処理テーブルをXLOOKUP関数で検索
部署名の列、メアドの列は同時に表示される

おまけ。検索結果が多くて溢れた場合の表示

こうして出来た表が冒頭で示した表です。
そしてもう一つ工夫を入れておきます。この結果表示は５件になっていますが、ちょうど５件なのか、それとももっと多いのかが分かりませんね。溢れたがどうか分からんじゃないか！というお叱りを受けることになりますから、溢れたことは表示させるようにします。

頻繁にあふれる場合は、増やして使ってください。

溢れたぞ！！というのは、処理用テーブルの表示順の列の最大値を MAX 関数で取得し、表示テーブルの最終番号よりも大きければ溢れたと判定して表示しています。
例では、名前に田の文字が含まれる人を検索したら6件がヒットしたため、5件まで表示させ「表示数オーバー」を表示しました。

XLOOKUP まとめ

これで、今回のバージョンは完成です。

VLOOKUP 関数に変えて XLOOKUP 関数を使うと、次のメリットがあります。

処理用テーブルに不要な列を追加しなくてすむ
IFERROR関数でエラー処理をする必要がなくなる XLOOKUP 関数に装備されている
結果が配列で返ってきてスピルで表示されるため、関数を書く列が一列ですむ

ほんと、高機能になり、楽になりました。

是非皆さんも使ってみてくださいね。

有り難うございました。