今回は、 EXCEL の VLOOKUP 関数を使って、簡易な名簿検索ツールを作ってみます。
会社で内線や携帯番号の検索、所属部署、役職などを検索することって有りますよね。せっかく、エクセルに表で入れているのに、表から人が探しているのではないですか？ それでは、勿体ない。EXCELの持ち腐れ。この記事を読んで 業務の効率化を進めてください
まとめの後に、この記事に関連する有益な情報記事へのリンクがあります。
こんな質問がありました。
名簿がExcelに入っているんですけど、
名前で検索して部署名と内線番号を簡単に表示
できるようになりませんか。
Excelには検索機能が付いていて
[Ctrl]+[F]で検索ができますよ。
その機能は知っているんですけど
画面のあちらこちらに移動するので
場所を探すのがちょっと大変なんです。
それに一日に何回も繰り返すので
そのやり方では時間が足りません。
私の場合は、VLOOKUP関数を使って、検索して一定の場所へ表示する、検索ツールを作成して、仕事で使っています。
簡単にできるし、入力場所も一定、表示する場所も一定なので、パフォーマンスは高いですよ。
それでは、始めます。
まずは、VLOOKUP関数の仕様から簡単に説明しておきます。
VLOOKUP 関数の仕様
VLOOKUP（検索値, 範囲, 列番号, 検索の型）
| 検索値 | 検索する文字列や値です。 今回の検索ツールでは、名前の文字列にあたります。 |
| 範囲 | データの範囲です。 検索値を検索する列と、検索の結果アウトプットしたい値が入っている列、を合わせた範囲を指定します。 |
| 列番号 | アウトプットしたい値の列は、範囲の中の何列目かを数値で指定します。 |
| 検索の型 | 完全一致（FALSE）か曖昧検索（TRUE）かを指定します。 省略可能ですが、私は、必ずFALSEを指定することとしています。 なぜか？についてはこの記事に詳しく説明していますので、良かったら参照してください。 曖昧検索の使い方は、得点からクラスに分類するようなときに使います |
VLOOKUP関数の列番号は、例えば、3列を範囲として選択している場合、左から1、2、3となります。
必ずしも、Aは1、Bは2ということではありませんので注意してください。
データを準備する。データベースとも言います。
実際にやってみましょう。
今回のケースでは、名前を入れたら完全一致でデータベースを検索し、部署名と内線番号を表示するため、名前・部署名・内線番号の表を作成します。
今回は、各部署を代表する5名を選抜して名簿を作りました。
これで、データベースの準備ができました。
VLOOKUP 関数で検索するフォームを作成する
次のような入力フォームを作ります。ブルーの枠に名前を入れると、その下の枠に部署名と内線番号が表示される仕組みです。
部署名を表示するC4へ、＝VLOOKUP(C2, $H$2:$J$999, 2, FALSE)と入力します。C2の値（文字列）をH2:J999の範囲の一番左側の列から検索し、2列目の値を返す。という意味です。したがって、I列の部署の名前が返されるというわけです。
範囲の指定で、$H$2:$J$999となっています。すべて絶対参照としています。原則、検索範囲などデータが入っている部分を指定する場合は絶対参照をおすすめします。今回$J$999と999までを範囲としているのは意味があります。どんどん下に追加していっても、式を変更しなくて良いようにしているのです。
同様に内線番号の部分にもVLOOKUP関数での検索を入力します。
名前を入れて動作を確認してみましょう。
山田みどりと入力してエンターを押してみます。部署名は総務、内線番号は203と表示されました。成功です。
VLOOKUP エラーの処置をする
でも、ちょっと待ってください。入力ミスした場合はどうなるでしょうか。”山田 みどり”と入力してみます。エラーが出てしまいます。
これは、名前を入力する前から出ていました。未入力やエラーの場合は、空白で表示するようにしてみます。そのためには、IFERROR関数を使います。
もしエラーの場合は、””（なにもなし）を表示する。ちょっと変な表現ですが、これで空白表示となります。先ほど入力したVLOOKUP関数の部分を修正してみます。
先ほどのVLOOKUP関数の前に「IFERROR(」を追加、後ろに「,””)」を追加します。
E4セルも同じようにIFERROR関数で囲みます。
先ほどのエラー表示が消ました。
これで、完成です。
あとは、データベース部分をカットして別のシートにコピーしても良いですし、表を追加しても構いません。入力と出力のフォームは変更しなくても大丈夫です。1000人を超えるときはVLOOKUP関数の範囲の指定を変更してください。
VLOOKUP 今回のまとめ
- データベースの検索は、VLOOKUP関数でできる。
- エラー表示を消すために、IFERROR関数を使う。
- 1000人を超えるときは、範囲の999を9999にすると使えます。
同姓同名への対応
今回は、結果の表示が1つだけですが、二人や三人の同姓同名が居たらどうするの？ってことになります。同姓同名が居ても大丈夫なように、結果を複数行で表示させる方法や、生年月日も同時に検索することで、一人に特定する方法について、以下の記事で紹介しています。
VLOOKUP で うまく 検索できない ときは
そして、やってみたけど うまく 検索できない というときに、まずチェックして欲しい、 よくある原因 について以下の記事で紹介しています。
空白 があって 一致するものが あるのに表示されない
今ある名簿は、空白があちらこちらに混在していて、データを修正しないと 使えなくて困った というときに、空白 が あっても 取り除いて 検索する方法や、 データベース を整える方法については、以下の記事を参照してみたください。
第三引数の 列番号 の指定を工夫する
列番号の指定に、数字を使わないことで、1つのセルに式を入力すれば、他のセルにはコピーでできるようになります。
列番号の指定を、ラベル名での検索を使うことで、探して数えることなく簡単にVLOOKUP関数の第三引数を指定します。
名簿検索システムの集大成
VLOOKUP関数の検索範囲を切り替える
是非、見てみてください。よろしくお願いします。
＜広告＞始まり—–
どうしても、なくせないのがFAXでの書類の受け渡し。相手がありますのでFAXでと言われると、メールでお願いしますとはなかなか言えないこともあります。そんなときに、インターネットFAXが代替してくれます。
FAX機の故障や寿命での取替え、インクカートリッジの交換など、保守にかかる手間は結構かかってしまいます。また、紙代・通信費は細かい費用かもしれませんが、固定的に掛かります。複合機であれば、結構な場所をとりますし電源、通信線も結構邪魔になります。 隙間には埃もたまりますので、掃除も大変で、放っておくとトラッキングによる火事も心配です。
といったように、FAXが有るだけで、こんなにも労力・費用・安全に対する負担があります。そうであれば、FAXでのやり取りは、電子化の流れでemailやクラウドなどの他の手段に変えていきたいところです。
これらの課題への対応に、インターネットFAXはいかがでしょうか。
スマホで受信して内容を確認し、スマホからFAXでの返信ができます。
電子帳簿保存法の電子取引データ保存が義務化されましたが、この法改正への対応策として活用できるのではないでしょうか。
＜広告＞終わり—–
コメント