Excelにはとても便利な関数 vlookup があります。
この関数は、表から行ごとにセルを検索し、指定した文字や数値の行を探し出す関数です。
たとえば、社員番号・名前・生年月日の表から、社員番号で名前を検索することができます。
では、実際に社員一覧表から社員番号で名前を検索してみます。
■ 検索する表の準備
例として、次のような社員一覧表をエクセルシートに用意しました。
B6からD13のセルに社員番号・氏名・生年月日を入力しました。
■ VLOOKUP関数を入力
B3のセルに検索したい社員番号を入力することでC3のセルに検索結果として氏名表示させようと思います。
C3のセルに VLOOKUP関数を入力してみます。
=VLOOKUP(B3,B6:D13,2)
VLOOKUP関数には引数が4つあります。
引数 | 内容 | 今回の設定値 |
1 | 検索する値 | 社員番号の入ったセル B3 を指定 |
2 | 検索範囲 | 社員番号・氏名・生年月日の範囲 B6:D13 を指定(表のタイトルは範囲に含めない) |
3 | 答え(関数の戻り値)として返す列 | 氏名を表示させたいので、検索範囲の2番目の列、ということで 2 を指定 |
4 | 検索方法 | (後ほど説明します) |
C3のセルに社員番号 A002 の氏名「きさらぎ」が表示されました。
■ 正しく動作するかを確認
社員番号 B010 を検索してみましょう。 B3のセルに B010 と入力してみます。
C3のセルに社員番号 B010 の氏名「やよい」が表示されました。
■ 他の社員番号でも確認
社員番号 A034 を検索してみましょう。 B3のセルに A034 と入力してみます。
あれ???社員番号 A034 の氏名は「みなづき」のはずが、「きさらぎ」と表示されています。
VLOOKUP関数で検索する列は昇順に並んでいなければいけません!!!!
つまり、検索する列=社員番号が昇順に並んでいないために、正しく検索できないのです。
■ 社員番号一覧を並び替え
では、この表を社員番号順に並び替えてみましょう。
並び替える範囲(B5:D13)を選択
「データ」タブをクリック
「並び替え」をクリック
「先頭行をデータの見出しとして使用する」をチェック
「優先されるキー」に社員番号を選択
「OK」をクリック
社員番号一覧表が社員番号順に並び変わり、社員番号 A034 の氏名「みなづき」が表示されました。
■ 存在しない社員番号では?
社員番号 D001 を検索してみましょう。 B3のセルに D001 と入力してみます。
社員番号一覧表には D001 の社員番号はありません。
VLOOKUP関数には、四番目の引数があります。この引数は「検索方法」を指定します。
省略するか、TRUE を指定すると「近似一致」、FALSE を指定すると「完全一致」になります。
■ 検索方法を「完全一致」に変更
社員番号の近いものを検索しても意味が無いので「完全一致」の検索方法に変えてみます。
=VLOOKUP(B3,B6:D13,2,FALSE)
C3セルに #N/A と表示されました。これは、「該当なし」といった意味のエクセルのエラーメッセージです。
■ 検索エラーの表示をわかりやすく
これではわかりにくいので、社員番号が無いときは「登録されていません」といった表示に変更してみます。
IF関数と組み合わせて VLOOKUP関数が #N/Aエラーの時は「登録されていません」と表示、そうでないときは、検索結果の氏名を表示するように変更します。
=IF(ISNA(VLOOKUP(B3,B6:D13,2,FALSE)),”登録されていません”,VLOOKUP(B3,B6:D13,2,FALSE))
これでわかりやすくなりました。
■ 再度動作確認
社員番号 B013 を検索してみましょう。 B3のセルに B013 と入力してみます。
あれれ???社員番号 B013 の氏名は「うづき」のはずが、「登録されていません」と表示されています。
■ なぜ??
VLOOKUP関数の完全一致で検索しています。
B10セルの内容を見てみます。
社員番号 B013 の後に「スペース」が入力されていたみたいです。
見た目では気が付きません。注意しましょう。
■ 社員テーブルの訂正
社員番号を正しく入力しなおします。
氏名が正しく「うづき」と表示されました。
■ おわりに
いかがでしたでしょうか。
VLOOKUP関数はとても便利な関数です。
ちなみに、D3セルに社員の生年月日を表示する場合は、次の様に関数を入力します。
=IF(ISNA(VLOOKUP(B3,B6:D13,2,FALSE)),””,VLOOKUP(B3,B6:D13,3,FALSE))
エラーの時のメッセージは社員名で表示しているので、生年月日のセルでは表示していません。
いろいろ試しながら使ってみてください。