SUBSTITUTE VLOOKUP を使って 名簿検索 の 名前に 空白 があっても 検索できる システム

今回は、名簿検索システムの２回目で、データベースや検索文字に空白が含まれるときの対応について、考えてみたいと思います。 空白 の削除には、 SUBSTITUTE 関数を使います。 空白は、全角と半角がありますから、 SUBSTITUTE 関数のネストで完全に除去します。　空白が除去できたら、あとはなにも気にせずに、心置きなく VLOOKUP 関数で検索すればいいだけです。

こんにちは、Akitamaです。
今回は、前回のEXCELエクセルを使った簡易な名簿検索ツールに改善を加えてみます。

前作ではVLOOKUP関数を使って空白も含めて完全に一致するものを検索するツールでした。
でも名前を入力するときはスペースで区切らないと気持ちが悪いという人も多いかもしれません。
検索する名前の前や後ろ、それに途中へも空白があっても大丈夫なように修正を加えます。

名前の入力セルに名字と名前の間に空白を挟んだ文字を入れてみました。前回までは検索が部署名と内線番号は空白表示となっていましたね。

前回の記事をまだ読んでいない方はこちらもまず見てみてくださいね。

このシステムは前提としてデータベースの名前には”空白は入れない”ルールとしています。それはデータを作るときに空白を入れるルールとすると、半角や全角そして2つ入ってしまったりとデータベースにばらつきが生じてしまう恐れがあるからです。こうなると検索はうまく出来ません。一番簡単なのは空白を全部取り除くことです。

なので今回の改善は、入力された文字列をSUBSTITUTE関数を使って全角と半角の空白文字を全て削除した文字列でVLOOKUP関数の検索を行うようにしました。

----- 変更前
=IFERROR(VLOOKUP(C2,$G$3:$I$1000,2,FALSE),"")
----- 変更後
=IFERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTE(C2,"　","")," ",""),$G$3:$I$1000,2,FALSE),"")

C2の文字列を2番目のSUBSTITUTE関数で全角の空白を除外した結果を1番目のSUBSTITUTE関数にそのまま渡し更に半角の空白を除外して、VLOOKUP関数に渡しています。
変更前の検索文字はC2となっているところを、2つのSUBSTITUTE関数のネストに変更していますね。

この変更で入力に「丸本　ゆう」と全角空白を間に入れても検索結果が表示されるようになりました。
前や後ろに空白を何個入れても大丈夫です。

そして、もう一つ改善を加えます。

次々に検索をしたいときにカーソルが毎回次のセルに移ると、戻す作業が必要です。毎回戻すという作業は超面倒なのでカーソルが動かないようにしてしまいます。

まずはC2を選択しセルの書式設定ダイアログで「保護」タブをクリックしロックのチェックを外します。その後メニューバーの「校閲」タブにある「シートの保護」をクリックするとシートの保護ダイアログが開きますので「ロックされたセル範囲の選択」のチェックを外してOKを押します。

ロックされていないセルしか選択が出来なくなりましたね。

この機能は入力するセルが複数有るときにも便利です。カーソルが他のところに行かずに次々に入力セルを移動しますので、じゃんじゃん入力が出来ますよ。ユーザー入力画面など入力以外は触って欲しくない時に活躍します。

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

ですが、フルネームを入力しないと検索できないのでは使い勝手がいまいちかもしれません。
「山田さん下の名前は何だったかな？」ってときに使えませんし、「山なんとかさん。ちょっとど忘れで」なんて時にも用をなしません。

そんな場面で使える名簿を次回は作ってみたいと思います。乞うご期待。

今回は以上です。