今回は VLOOKUP 関数を使って作る 名簿検索 システムの三回目の投稿です。
名前 で検索って意外と難しいですよね。名前って 苗字 と 名前 に分かれています。
そして、間に空白が入っているのが普通で、人によっては 空白 が 全角 だったり 半角 だったり、はたまた、検索の時は空白を入れなかったりと色んなケースがありますよね。
更に厄介なのは名簿データに、 同姓同名 が存在することです。
検索するときに、「フルネームで入力」となると、「下の名前知らないよ！」とか、その反対に「あの人苗字はなんだっけ？」とかなってしまい、面倒で使わなくなってしまう、ってことになってしまいます。
そうかといって、入力でよくある「姓」と「名」を別の枠で入力してもらう、というのも、入力の操作が煩雑になるので、氏名のどこでもいいから一部分を入力すると、その文字で名簿を部分検索してくれるように改善します。
また、検索でヒットした行が複数存在する場合でも表示できるように、検索結果を一覧で表示する秘策も組込みます。
以上の対応で、使い勝手がずいぶんと良くなり、ユーザーに使ってもらえるのでは？と目論んでいます。
今回のアップデートは、こんな感じの改善に対応したものにしました。
複数の関数を組み合わせて使いますので、ちょっと大変そうに思うかもしれませんが、一つ一つ噛み砕いて解説していますから、恐れていたよりは意外と簡単に理解できると思います。
ゆっくりで構いませんので、最後まで読んでくださいね。
では、ここら今回の本編に入ります。
第一回目は VLOOKUP 関数を使って名前で名簿のデータベースを検索しその所属部署と内線番号を表示するというものでした。
入力用のセルに氏名を（空白なしで）入力することで、名簿から氏名を検索し、結果を部署名と内線番号表示欄へ表示する、というものです。
使い方に制約はありますが、そこそこ使えるものでした。
そして第二回目では、第一回で作った検索システムの入力部分を改善しました。
名前に空白が含まれても検索ができるように、空白を削除してから検索する仕組みを追加しました。
そして、更に検索する度にカーソルを戻す作業が発生することに対応する方法も解説しました。
しかし、ここまでの対応をしても、名前で検索することの問題は色々残っていて、たとえば、同姓同名の人が居たらどうするかや、また下の名前が分からないときに検索できないなど、実用として使ってもらうにはもう少し工夫が必要でした。
そこで今回第三回目は、同姓同名の検索と名前の一部分を入力すらば検索できるように対応すべく、結果を一覧で表示するように修正をしました。
同姓同名の検索
データベースを見ると、田中太郎さんは二人居ます。それぞれ営業部と設計部に所属しています。
以前のバージョンだと最初に検索ヒットした営業部の人しか表示できませんでしたが、今回のバージョンでは、結果一覧に二人とも表示されています。
同姓同名も表示可能
二人の山田さん検索
「苗字は山田さんだと分かっているけど、下の名前が分からない」というときは「山田」だけを入力して検索すると、該当する二人が表示されます。
下の名前だけでも検索可能
「いつも下の名前でしか呼んでいるので、苗字を思い出せない」というときにも、名前だけ入力して検索できます。
今までのバージョンよりも、かなり使いやすくなったと思いませんか？便利に使ってもらえると思います。
それでは、その仕掛けを説明していきますね。
名簿検索 システム の処理概要
VLOOKUP 関数で検索実行する前に、2つの事前の処理をしておきます。
- データベースの各行（以下レコード）へあらかじめ情報を追加しておきます。追加する情報は、検索セルに入力された文字が氏名のデータ中に含まれるかどうかを表すものです。
- 検索文字が含まれるレコード（表示対象レコード）にシリアル番号を1から振ります。この列に番号が入っていれば、検索ヒットしていることを意味します。
情報を追加した後に、VLOOKUP関数で番号で検索し検索結果の表へ抜き出して表示させます。
データベースに検索文字列が含まれる行を表す列を追加
検索文字列がそのレコ－ドの名前の列に含まれるかを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となっているのが分かると思います。
表示対象レコードにシリアル番号を振る
シリアル番号を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行目 ・・・とカウントする範囲が増えていくように設定します。 なお一行目をコピペするだけでできます。
VLOOKUP 関数で 名簿検索 の処理用テーブルを検索
VLOOKUP 関数は検索範囲の一番左の列で検索し、同じレコードの右の列の何番目かを結果として返す関数ですから、作成した表示順の列の右側に名簿のコピー列を作成し処理用テーブルとします。
作成した処理用テーブルを VLOOKUP 関数で検索して、名前、部署、内線番号をC列D列E列に表示させます。
今回は結果の1～5までを表示するようにしていますが、下に伸ばせばその分何行でも表示可能となります。
同様に部署名の列、内線番号の列にも関数を設定する
おまけ。溢れた場合の表示
こうして出来た表が冒頭で示した表です。
そして、もう一つ検索結果が5つを超えて、溢れたときに、溢れたかどうかがぱっと見で、分からんじゃないか！ということになりますから、溢れたことだけ表示させるようにしておきます。
処理用テーブルの表示順の列の最大値を MAX関数で取得し、表示テーブルの最終番号よりも大きければ溢れたと判定して、「表示数オーバー」と表示させています。
例では、名前に田の文字が含まれる人を検索したら6件がヒットしたため、5件まで表示させ「表示数オーバー」を表示しました。
名簿検索 まとめ
これで、今回のバージョンは完成です。
ちょっとした仕組みを組み込んだだけで、数段に便利さが増し業務にも使えるレベルではないかと思います。
住所録などにも活用できますので、利用範囲は広いのではないかと思います。是非、あなたの表で流用してみてください。
以上です。有り難うございました。
コメント