エクセルの社員名簿作成を自動入力で効率化|検索&転記もVLOOKUPで一発処理

エクセルの社員名簿作成を自動入力で効率化|検索&転記もVLOOKUPで一発処理

エクセルの社員名簿の運用管理をラクにしたい!

社員名簿を作るとき
■ 自動転記でラクしたい
■ 入力ミスや表記揺れを無くしたい
■ 変更点を一括更新したい
社員名簿を検索するとき
■ 膨大な社員数でも簡単に取り出したい
■ 変更点をリアルタイムに更新したい
■ 名簿をサーバーにおいて共有したい

この記事ではエクセルの社員名簿管理をVLOOKUP関数を用いて効率化する方法を解説します。
VLOOKUPのメリットや注意点もあわせて参考にしてください。

本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルも参照可能です。
≫ PDFファイル参照ページはこちら

お急ぎの方は、下記から該当箇所へ移動してください。

【社員名簿を作る】

解説内容詳細へ
自動転記の仕組みを理解したい2-1.
基本的な数式を作りたい2-2.
転記する項目を増やしたい2-3.
別ファイルから転記したい2-4.
部門数を増やしたい2-5.
作成時にVLOOKUPを使う
メリットを知りたい
3章
作成時にVLOOKUPを使う
注意点を知りたい
4章

【社員名簿を検索する】

解説内容  詳細へ 
検索する仕組みを理解したい5-1.
基本的な数式を作りたい5-2.
別ファイルを検索する数式を作りたい5-3.
数式をカスタマイズしたい5-4.
検索でVLOOKUPを使う
メリットを知りたい
6章
検索でVLOOKUPを使う
注意点を知りたい
7章

この記事を読めば、社員名簿作成時の単純入力作業を大幅に削減できます。完成後も社内で共有し有効活用してください。
そうすればよりクリエイティブな仕事に時間をまわせるようになります。時短にもつながり、残業を減らして早く帰れることでしょう。

VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事も参照ください。
≫ 超図解!VLOOKUP|基礎から応用まで完璧マスター&便利技20選

マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft|Officeのサポート~VLOOKUP関数

1. VLOOKUPの基礎知識と処理のイメージ

VLOOKUPとは、指定した範囲から特定の値を探し出し、紐づくデータを返す関数です。

処理の流れをイメージすると下図のようになります。

エクセルVLOOKUPの使い方【社員名簿編】VLOOKUPの処理のイメージ
VLOOKUPの処理の流れのイメージ(画像はクリックで拡大)

≫ 解説図のPDFファイル

【VLOOKUPの処理のイメージ】
検索値を所定のセルに入力
検索値と一致するものを、検索範囲の最左列を上から順に探す
検索値と一致する値を発見!
その行の左から○列目を返す(○は式で指定)
検索結果として表示

VLOOKUPの数式は次のとおりです。

=VLOOKUP( 検索値 , 検索範囲 , 列番号 , 検索方法 )

項目内容入力例
検索値データを探し出す手掛かりとなる値A1(セルを指定)
検索範囲探したいデータが入っている表A:D (列全体で指定)
A1:D4(長方形で指定)
列番号左から何列目を返すか2(数値で指定)
検索方法一致した場合のみ返すか、近似値でも返すかFALSE(完全一致)
TRUE(近似値)

各項目の基礎知識をさらに詳しく知りたい方はこちら
≫ VLOOKUPの使い方|3.VLOOKUPはどうやって使うの?|各項目の解説

マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft|Officeのサポート~VLOOKUP関数

2. エクセルの社員名簿をラクに作れるVLOOKUPの使い方

この章では、エクセルの社員名簿を作る際に、VLOOKUPの自動転記を使ってラクにする方法を解説します。お急ぎの方は下記チャートより、目的に応じたリンクを辿ってください。

目的別チャート詳細へ
自動転記の仕組みを理解したい2-1.へ
基本的な数式を作りたい2-2.へ
転記する項目を増やしたい2-3.へ
別ファイルから転記したい2-4.へ
部門数を増やしたい2-5.へ

2-1. 部門名が自動転記される仕組み

部門コードを手掛かりに部門名が自動転記される仕組みは、次のとおりです。

エクセルVLOOKUPの使い方【社員名簿編】部門名が自動転記される仕組み
部門名が自動転記される仕組み(画像はクリックで拡大)

≫ 解説図のPDFファイル

【VLOOKUPを用いた自動転記の流れ】
部門コードを所定の欄に入力する
部門マスタから同じ部門コードを探す
一致する部門コードを見つけたら対応する部門名を返す
所定の欄に部門名が自動転記される

部門名の一番上のセル「H2」の数式は次のとおりです。

【セル「H2」の数式】
=VLOOKUP(G2,A:B,2,FALSE)

G2部門コードを入力するセル
A:B部門マスタの範囲(※1)
22列目のデータを返せ
FALSEコードが一致する場合のみ返せ(※2)

(※1) A~B列の最下行まで検索範囲に含まれます。
指定方法には、長方形で囲む方法もあります。
≫ 範囲指定の方法について詳しく知りたい方はこちら

(※2) 「TRUE」にしてしまうと一致するコードがなくても、近い値を見つけて返してしまいます。部門名の転記間違いを防ぐためにも、「FALSE」を選びましょう。
≫ 「FALSE」「TRUE」の選び方について詳しく知りたい方はこちら

2-2. 基本的な数式の作り方

エクセルVLOOKUPの使い方【社員名簿編】部門名を自動転記する数式の作り方
部門コードを入力すると部門名が自動転記される数式の作り方(画像はクリックで拡大)

≫ 解説図のPDFファイル

この章では、社員名簿に部門名が自動転記される数式の作り方を解説します。

数式の作成手順
社員名簿のG列に部門コードを入れるとH列に部門名が自動転記される数式

社員名簿の部門名の一番上のセル「H2」に作成する数式は、次のとおりです。

部門名の一番上のセル「H2」
=VLOOKUP(G2,A:B,2,FALSE)

部門マスタと社員名簿が同じシートにあるシンプルなパターンです。
別シートや別ファイルにする場合はこちら ≫ 2-4.

(1) 数式を作成するセルを選択し関数作成用のツールを開く

数式の作成手順

数式を作成するセルを選択し、メニュー欄にある「fx」をクリックします。

(2) VLOOKUP関数を検索して呼び出す

数式の作成手順

「関数の挿入」ダイアログが開くので、「関数の検索」の欄に「VLOOKUP」と入力し、検索して呼び出します。

(3) 部門コードを入力する場所を指定する

数式の作成手順

「関数の引数」ダイアログが開くので、「検索値」の欄をクリックします。
参照先の部門コードのセルを選択します。
このとき「検索値」の欄に「G2」が入力されたことを確認してください。

(4) 部門マスタを指定する

数式の作成手順

「関数の引数」ダイアログの「範囲」の欄をクリックします。
部門マスタの列の番地A~Bをドラッグで範囲指定します。
このとき「範囲」の欄に「A:B」が入力されたことを確認してください。

(5) 「列番号」と「検索方法」を直接入力する

数式の作り方

「列番号」と「検索方法」を下記のとおり入力します。

列番号2
検索方法FALSE

以上です。

各項目の設定について詳しく知りたい方はこちら ≫ 2-1.

2-3. 自動転記する項目を追加する方法

エクセルVLOOKUPの使い方【社員名簿編】部門マスタの自動転記する項目を追加する方法
自動転記する項目を追加する方法(画像はクリックで拡大)

≫ 解説図のPDFファイル

自動転記する項目を新たに追加する方法を解説します。具体的には、部門マスタに「拠点」を追加し、社員名簿へも自動転記できるようにします。

数式のカスタマイズの仕方

ここで作成する数式は、次のとおりです。

部門名の一番上のセル「H2」
=VLOOKUP($G2,$A:$C,2,FALSE)

拠点名の一番上のセル「I2」
=VLOOKUP($G2,$A:$C,3,FALSE)

拠点の一番下のセル「I6」
=VLOOKUP($G6,$A:$C,3,FALSE)

(1) 検索値に「$」を付けて部門コードの指定を固定する

数式のカスタマイズの仕方

数式の1つ目の項目のうち、列にだけ「$」を付けて「$G2」にします。数式を右にコピーしても部門コードのあるG列を引き続き指定できるようにするためです。

(変更前)
=VLOOKUP(G2,A:B,2,FALSE)
↓↓↓
(変更後)
=VLOOKUP($G2,A:B,2,FALSE)

変更理由:「$」を付けることで隣へコピーしても変化しないようにするため

もし「$」がないと、隣にコピーしたときに「G2」→「H2」と変化してしまいます。これでは部門コードのあるセルを正しく指定できません。
一方、行番号の「2」には「$」を付けません。下にコピーしたときに「G3」「G4」・・・と変化していって欲しいからです。

変化する項目/しない項目

VLOOKUPでは数式を移動/コピーした際に、変化する項目としない項目があります。項目ごとに確認しながら進めましょう。

項目移動/コピーしたときの変化
検索値変化する(ただし$がある項目は変化しない)
検索範囲変化する(ただし$がある項目は変化しない)
列番号変化しない
検索方法変化しない

(2) 部門マスタの範囲指定を拡張し「$」を付ける

数式のカスタマイズの仕方

部門マスタの項目を増やしたので、範囲指定を「A:B」から「A:C」へと拡張します。

(変更前)
=VLOOKUP($G2,A:B,2,FALSE)
↓↓↓
(変更後)
=VLOOKUP($G2,A:C,2,FALSE)

変更理由:部門マスタの項目追加にあわせて検索範囲を拡張

さらに「$」を付けて「$A:$C」にします。数式をコピーしても引き続きA~C列を指定できるようにするためです。

(変更前)
=VLOOKUP($G2,A:C,2,FALSE)
↓↓↓
(変更後)
=VLOOKUP($G2,$A:$C,2,FALSE)

変更理由:「$」を付けることで隣へコピーしても変化しないようにするため

(3) 数式をコピーした後、列番号の指定を「拠点」のある列に変更する

数式のカスタマイズの仕方

部門名のセル「H2」にある数式を、隣の拠点のセル「I2」へコピーします。この操作では数式は変化しません。「検索値」と「検索範囲」の列に「$」を付けたからです。

同時に列番号の指定を「2」から「3」に変更します。これにより返す列を2列目の「部門名」から3列目の「拠点」へと変更できます。

(変更前)
=VLOOKUP($G2,$A:$C,2,FALSE)
↓↓↓
(変更後)
=VLOOKUP($G2,$A:$C,3,FALSE)

(4) 数式を最下行までコピーする

数式のカスタマイズの仕方

メンテした2つの数式を最下行までコピーします。このとき、検索値の「$G2」の数字部分だけが、「$G3」 「$G4」・・・「$G6」へと変化していることを確認しましょう。

以上が、部門マスタに新たに「拠点」を追加する方法です。

「$」は数式を移動/コピーしたときにできるだけ手を加えなくて済むようにするためのものです。「$」の有無は計算結果には直接影響しません。
「$」をうまく使えば変更箇所が少なくなり、ミスも減り、省力化できます。膨大な数をコピーする際に、「$」は必須アイテムです。

2-4. 別ファイルから自動転記する方法

エクセルVLOOKUPの使い方【社員名簿編】別ファイルを検索範囲にする場合の数式
別ファイルを検索範囲にする場合の数式(画像はクリックで拡大)

≫ 解説図のPDFファイル

別ファイルにある部門マスタからも自動転記できます。数式は次のとおりです。別ファイルを開いているか閉じているかで、見え方が異なるので注意してください。

【セル「H2」の数式】
部門マスタを開いているとき
=VLOOKUP(G2,[部門管理.xlsx]部門マスタ!A:B,2,FALSE)

部門マスタを閉じているとき)(※)
=VLOOKUP(G2,‘X:¥管理フォルダ¥[部門管理.xlsx]部門マスタ’!A:B,2,FALSE)

(※)部門マスタのファイルのプロパティ
ドライブ名 : X
フォルダ名 : 管理フォルダ
ファイル名 : 部門管理.xlsx
シート名  : 部門マスタ

記述方法について詳しくはこちら ≫ 参照ファイルの記述ルールと注意点

数式の作り方は下記のようになります。基本的な部分は、部門マスタが同じシートにある場合と変わりません。ファイルの取り扱い部分だけが異なります。

エクセルVLOOKUPの使い方【社員名簿編】別ファイルにある部門マスタから自動転記できる数式の作り方
別ファイルにある部門マスタから自動転記できる数式の作り方(画像はクリックで拡大)

≫ 解説図のPDFファイル

(1) 部門マスタのあるファイルを先に開く

別ファイルから転記する方法

部門マスタのあるファイルをあらかじめ開いておきましょう。
次にある関数作成用ツールを開いてしまうと、他のエクセルファイルを開く操作ができなくなるからです。

(2) 数式を作成するセルを選択し関数作成用ツールを開く

別ファイルから転記する方法

社員名簿内の数式を作成するセルを選択し、メニュー欄にある「fx」をクリックします。

(3) VLOOKUP関数を検索して呼び出す

別ファイルから転記する方法

「関数の挿入」ダイアログが開くので、「関数の検索」の欄に「VLOOKUP」と入力し、検索して呼び出します。

(4) 部門コードの入力場所を指定する

別ファイルから転記する方法

「関数の引数」ダイアログが開くので、「検索値」の欄をクリックします。
部門コードの入力場所となるセルを選択します。
このときダイアログの「検索値」の欄に「G2」が入力されたことを確認してください。

(5) 部門マスタを指定する

別ファイルから転記する方法

「関数の引数」ダイアログの「範囲」の欄をクリックします。
部門マスタのエクセルファイルの列の番地A~Bをドラッグで範囲指定します。

このとき「範囲」の欄に「[部門管理.xlsx]部門マスタ!A:B」が入力されたことを確認してください。

別シートを指定する際は、関係のないセルに触れないようにしましょう。うっかり触れると、そのセルまで数式に組み込まれてしまいます。

別シートを指定する際は、関係のないセルに触れないようにしましょう。うっかり触れると、そのセルまで数式に組み込まれてしまいます。

(6) 「列番号」と「検索方法」を直接入力する

別ファイルから転記する方法

「列番号」と「検索方法」を下記のとおり直接入力します。

列番号2
検索方法FALSE

各項目の設定について詳しく知りたい方はこちら ≫ 2-1.

以上です。

参照ファイルの記述ルールと注意点

数式に別シートや別ファイルを組み込む場合、次のように記述します。

シートの場所 数式での記述の仕方
別シートシート名の後に「!」を付ける
別ファイルファイル名を「 [ ] 」で囲む
別フォルダフォルダの区切りに「」を挿入
別ドライブドライブ名の後に「:」を付ける
閉じられた別ファイルドライブ名、フォルダ名、ファイル名までをひっくるめて「」で囲む

注意すべき点は、フォルダやドライブの情報は参照するファイルを閉じたときだけ現れることです。

数式を作成している間は部門マスタを開いているので、ドライブ名やフォルダ名は出ていません。部門マスタだけ先に閉じると出てきて、開くと再び消えてしまいます。

数式の表記ルール

ドライブ名やフォルダ名は数式を作る際には入力しないため、ファイルを閉じたときに突然出てくると戸惑うかもしれません。見た目が違いますが、両者は同じ数式です。

2-5. 部門マスタの件数を増やす方法

最下行までOK

部門マスタの件数を追加したい場合は、部門マスタに行を追加するだけでOKです。行間に挿入してもいいですし、末尾に追加しても構いません。

部門マスタの範囲指定を「A:B」のように列全体で指定しているので、行が増えても数式は変更ありません。

検索範囲の指定方法の違いについて、詳しく知りたい方はこちら
≫ VLOOKUPの使い方|検索範囲の指定方法には「セル指定」と「列指定」がある

VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事もあわせて参照ください。
≫ 超図解!VLOOKUPの使い方|基礎から応用まで完璧マスター&スゴ技20選

スキルに自信がついたら新しい世界に踏み出そう!|転職サイト登録なび
転職活動で成功するGmailの作り方(1人で複数アドレス作成もOK)|転職サイト登録なび

3. エクセルの社員名簿の作成でVLOOKUPを使うメリット

この章では、エクセルの社員名簿の作成にVLOOKUPを使うメリットを解説します。

  1. 同じ文字の繰り返し入力を減らせる
  2. 入力文字の表記を統一できる(表記揺れを防げる)
  3. 一括変更に漏れなく対応できる(社名変更など)
  4. データの情報共有ができる
  5. 作業の分担・効率化が図れる

3-1. 同じ文字の繰り返し入力を減らせる

VLOOKUPを使えば、同じ文字を繰り返し入力するのを減らせます。同じ部門名や拠点が何度出てきても、部門コードを入力するだけで部門マスタから自動転記できます。

3-2. 文字の表記を統一できる(表記揺れを防げる)

リストから転記するので、文字の表記を統一できます。いわゆる「表記揺れ」を防げるわけです。

参考に表記揺れの具体例を紹介します。

ABC(全角)ABC(半角)
システム(全角)システム(半角)
オフィス(カタカナ)OFFICE(英字)
アンド(カタカナ)&(全角記号) &(半角記号)
株式会社(漢字)(株) ㈱(略語)

3-3. 一括変更に漏れなく対応できる(組織変更など)

組織変更で部門名が変更になっても、漏れなく簡単に対応できます。
手作業の場合、該当データをすべて手で変更する必要があります。VLOOKUPを使っていれば、部門マスタをメンテするだけで、社員名簿の部門名を漏れなく一括更新できます。

変更前の古い名称が混在するといった事態を防げるわけです。

3-4. データの情報共有ができる

VLOOKUPは検索範囲としてネットワーク上の共有ファイルを指定することができます
組織変更の際に総務部が部門マスタを更新すれば、リアルタイムに情報共有できます

3-5. 作業の分担・効率化が図れる

社員名簿と部門マスタを分けることで、それぞれ別の人が同時にメンテできます。業務分担により効率化が図れるわけです。
ファイルが一つだと誰かが開いているときに「読み取り専用」になってしまい、編集できません。しかし別々のファイルであればその心配はありません。ネットワークでつながれていれば、拠点が離れていても分担可能です。

あなたを待っている会社がある
|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

4. エクセルの社員名簿の作成でVLOOKUPを使う際の注意点

社員名簿の作成でVLOOKUPを使う際、幾つか注意点があります。

  1. 部門マスタのフォーマットに一定のルールが必要
  2. 部門マスタに間違いがあると被害が大きくなる
  3. 部門マスタが壊れたら社員名簿も壊れる

4-1. 部門マスタのフォーマットに一定のルールが必要

部門マスタのフォーマットには、一定のルールが求められます。

【部門マスタのフォーマットの主なルール】

  • 部門コードは一番左の列に縦に並べること
  • 一行ごとに一組のデータであること
  • 部門コードに重複がないこと
  • 部門コードは昇順に並んでいること
  • 結合セルがないこと

特に部門コードは重複しないようにしましょう。なぜならVLOOKUPは同じコードが2つ以上あっても、一番上のデータしか返さないからです。

重複がないかを一括チェックする方法を知りたい方はこちら
≫ VLOOKUPの使い方|6-4.重複を「条件付き書式」で一括チェック

特にメンテの担当者を分けている場合は要注意です。
部門マスタに重複があってもエラーにはならないため、社員名簿の担当者は重複の存在に気付きにくいのです。発見が遅れてせっかくの業務分担による効率化が台無しになる恐れがあります。

VLOOKUPの検索範囲の表の作り方について、基本的なルールを知りたい方はこちら
≫ VLOOKUPの使い方|4-1.検索範囲となる表の作成上の基本ルール

4-2. 部門マスタに間違いがあると被害が大きくなる

部門マスタに間違いがあると、そのデータが転記されるすべてのファイルに間違いが伝播します。自動転記されるので間違いが何倍にも膨れ上がってしまうわけです。
また部門マスタが完成すると、以後は自動転記に頼ってしまい、細かな注意を払わなくなります。いっそう間違いに気付きにくくなります。

VLOOKUPは便利なだけに、ミスしたときの被害もその分大きくなります。部門マスタの作成には、特に注意を払いましょう。

4-3. 部門マスタが壊れたら社員名簿も壊れる

部門マスタが壊れたら、社員名簿も同時に壊れます。リアルタイムな機能がかえってあだとなるわけです。
原因として、部門マスタの一部あるいは全部を削除してしまったり、ファイルを別の場所に移動させてしまったり、などがあげられます。

入力済みのデータを固定させたい場合は、セルをコピー&「値貼り付け」して、数式を値に変えてしまいましょう。
ただし部門マスタとの連携はなくなり、更新情報が反映されなくなります。今後変更がないデータにだけ適用しましょう。

「値貼り付け」の仕方

該当セルをコピーした後、同じセルを指定したまま貼り付けします。その際「貼り付けオプション」で「形式を選択して貼り付け」から「値」を選択します。

転職して、私、成長したかな?
|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

5. エクセルの社員名簿からVLOOKUPを使ってデータを取り出す方法

この章では、エクセルで作成した社員名簿から、VLOOKUPを使って必要なデータを取り出す方法を解説します。

お急ぎの方は下記のチャートより、目的に応じたリンクを辿ってください。

目的別チャート詳細へ
検索する仕組みを理解したい5-1.へ
基本的な数式を作りたい5-2.へ
別ファイルを検索する数式を作りたい5-3.へ
数式をカスタマイズしたい5-4.へ

5-1. 社員名簿から社員番号を手掛かりにデータを取り出す仕組み

エクセルVLOOKUPの使い方【社員名簿編】社員番号を手掛かりに部門名を取り出す仕組み
社員番号を手掛かりに連絡先を取り出す仕組み (画像はクリックで拡大)

≫ 解説図のPDFファイル

上の図は、社員名簿から社員番号を手掛かりに検索し、連絡先を取り出す仕組みです。

連絡先を表示させるセル(I2)の数式は、次のとおりです。

=VLOOKUP(G2,$A:$D,4,FALSE)

G2 :社員番号はセルG2に入力する
$A:$D :社員名簿の場所はA列からD列で囲まれた範囲(※1)
4 :社員名簿の4列目のデータを返せ
FALSE :完全一致した場合のみデータを返せ(※2)

(※1)「$」をつけずに「A:D」とすることもできます。ただし、数式を移動/コピーすると同時にずれてしまいます。ミスを防ぐためにも「$」をつけておきましょう。
≫ 「$」について詳しく知りたい方はこちら

また、「$A$1:$D$4」のようにセル番地での範囲指定もできます。ただし、社員名簿の行数を増やすと、数式もあわせて変更する必要があります。
その点「$A:$D」のように列で指定しておけば、A~D列の最下行まで指定範囲に含まれるので、範囲の変更は不要になります。
≫ 範囲指定の仕方について詳しく知りたい方はこちら

(※2) 「FALSE」か「TRUE」かの二択になりますが、ここでは必ず「FALSE」を選びましょう。

「TRUE」にしてしまうと完全一致しなくても、近似値を見つけて返してしまいます。
社員検索においては、社員番号が異なれば他人です。完全一致した場合のみデータを返す「FALSE」を選択しましょう。
≫ 「FALSE」「TRUE」について詳しく知りたい方はこちら

5-2. 社員名簿と数式が同じシートにある場合の数式の作り方

エクセルVLOOKUPの使い方【社員名簿編】社員名簿と数式が同じシートにある場合の作り方
社員名簿と数式が同じシートにある場合の作り方(画像はクリックで拡大)

≫ 解説図のPDFファイル

上図は、VLOOKUPの使い方の手順です。数式と社員名簿が同じシート内にある場合を想定しています。

あらかじめ社員番号の入力欄にコードを入れておけば、数式をつくりながらチェックができて便利です。

数式の作り方をステップ順に確認して進めたい方はこちら
≫ 2-2.基本的な数式の作り方

5-3. 社員名簿と数式が別ファイルにある場合の数式の作り方

エクセルVLOOKUPの使い方【社員名簿編】社員名簿が別ファイルにある場合の数式の作り方
社員名簿が別ファイルにある場合の数式の作り方(画像はクリックで拡大)

≫ 解説図のPDFファイル

上図は、社員名簿が別ファイルにある場合の数式の作り方です。ステップ順に確認したい方はこちらを参照してください。
≫ 2-4.別ファイルから自動転記する方法

例えば下記の場所にある社員名簿を参照する数式は下記になります。

ドライブ名 : X
フォルダ名 : 社員管理フォルダ
ファイル名 : 社員名簿ファイル.xlsx
シート名  : 社員名簿

=VLOOKUP(A2,‘X:\社員管理フォルダ[社員名簿ファイル.xlsx]社員名簿’!$A:$D,3,FALSE)

別ファイルにある社員名簿を検索する場合の記述ルールについて詳しくはこちら
≫ 参照ファイルの記述ルールと注意点

5-4. VLOOKUPのカスタマイズの仕方

次に、カスタマイズの仕方を解説します。

エクセルVLOOKUPの使い方【社員名簿編】社員名簿の検索範囲を拡張する
社員名簿の検索範囲を拡張する方法(画像はクリックで拡大)

≫ 解説図のPDFファイル

上図は、検索する項目に変更を加えたときの数式の違いを解説したものです。必要に応じて自分なりにアレンジすることで、より理解が深まりイメージがわきやすくなります。

社員名簿に新たに「メールアドレス」を追加する方法

社員名簿に新たに「メールアドレス」を追加する方法を解説します。

【変更手順】
社員名簿の右側にメールアドレスの列(E列)を追加する
数式の検索範囲の指定を拡張する($A:$D→$A:$E)
返す列の番号を変更する(4→5)

変更後の数式は次のとおりです。

変更内容:検索範囲を拡張し、返す列番号を変更

(変更前)
=VLOOKUP(G2,$A:$D,4,FALSE)
↓↓↓
(変更後)
=VLOOKUP(G2,$A:$E,5,FALSE)

項目の追加をステップ順に確認して進めたい方はこちら
≫ 2-3.自動転記する項目を追加する方法

社員名簿の人数を増やす方法

社員名簿の人数を増やす場合は、社員名簿に行を追加するだけです。間に挿入しても、末尾に追加しても構いません。

その際、一番左の列に入力する社員番号は、既存の社員番号と重複してはいけません。その他の項目も表のフォーマットに合わせて入力します。

なお、社員名簿の範囲指定については、列全体を指定しているので、行が増えても数式は変更ありません。

変更内容:人数を増やす

(変更不要)
=VLOOKUP(G2,$A:$D,4,FALSE)

$A:$D:列全体指定なので行追加しても変更不要

検索範囲の指定方法の違いについて、詳しく知りたい方は下記を参照ください。
VLOOKUPの教科書|検索範囲の指定方法には「セル指定」と「列指定」がある

転職してやっと活躍できる場を見つけました
|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

6. エクセルの社員名簿の検索でVLOOKUPを使うメリット

エクセルの社員名簿の検索でVLOOKUPを使うメリットは、次のとおりです。

  1. 膨大な人数でも簡単に検索可能(max100万点!)
  2. 社員名簿を更新したら、検索結果もリアルタイムに反映される
  3. データの情報共有ができる

6-1. 社員名簿が膨大な人数でも簡単に検索可能

エクセルVLOOKUPの使い方【社員名簿編】100万行の社員名簿でも検索可能
100万行の社員名簿でも検索可能(画像はクリックで拡大)

≫ 解説図のPDFファイル

社員名簿に膨大な人数があっても、同じ式で簡単に検索できます。Max100万点まで可能です。

横方向は16000列

ちなみに横方向へはどのぐらいまで検索できるかというと、列の数は約16000まで可能です。

エクセルVLOOKUPの使い方【社員名簿編】列の数が1万超でも検索可能
列の数が1万超でも検索可能(画像はクリックで拡大)

≫ 解説図のPDFファイル

なお、列番号のアルファベットの増え方は下記のようになります。

A B C・・・X Y Z AA AB AC・・・ZX ZY ZZ AAA AAB AAC・・・XFB XFC XFD

6-2. 社員名簿を更新したら検索結果もリアルタイムに反映される

社員名簿を更新すると、検索結果もリアルタイムに反映されます。

社員の苗字が変更になったり部署が異動したりしても、社員名簿をメンテすればすぐに反映されます。手入力にありがちな「変更が漏れてて古い情報で印刷してしまった」といったミスを減らすことができるのです。

6-3. データの情報共有ができる

VLOOKUPは、社内ネットワーク上の共有ファイルを検索できます。

サーバーにある社員名簿を、各部署で共有できるわけです。

7. エクセルの社員名簿の検索でVLOOKUPを使う際の注意点

社員名簿の検索でVLOOKUPを使う際、幾つかの注意点があります。

  1. 検索値である社員番号より左側にあるデータは返せない
  2. 検索結果は同時に2つ以上返せない
  3. 社員番号が横方向に並ぶ表は検索できない

7-1. 必要なデータが社員番号より左側にあると返せない

社員名簿において、検索値である社員番号より左側にあるデータは返せません。社員番号は社員名簿の一番左の列に位置する必要があります。

列番号の指定をマイナスにして反対側のデータを返そうとしても、エラーになります。

社員名簿の作成について、基本的なルールを知りたい方はこちら
≫ VLOOKUPの使い方|4-1.検索範囲となる表の作成上の基本ルール

7-2. 検索結果は同時に2つ以上返せない

社員名簿の中に同じ社員番号があっても、1つしか返せません。一番上にあるものを処理したら、下に同じ社員番号があっても無視されます。
社員番号は重複しないようにしましょう。

社員番号に重複がないかチェックする方法はこちら
≫ VLOOKUPの使い方|6-4.重複を「条件付き書式」で一括チェック

7-3. 社員番号が横方向に並ぶ表は検索できない

社員名簿のフォーマットにおいて、社員番号が横方向に並んでいる表は検索できません。必ず、社員番号は縦に並んでいる必要があります。

VLOOKUPの先頭の「V」は、Vertical(垂直)の意味です。

転職サイトは複数登録してベストな会社を見つけたい|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

まとめ|VLOOKUPで入力作業や検索を大幅に省力化できる

このように、社員名簿の管理運用にVLOOKUPを使えば劇的に効率化できます。膨大な人数であっても、一発で必要なデータを取り出すことができます。

社員名簿を作るとき
自動転記で入力作業が効率化
入力ミスや表記揺れを減らせる
変更点があれば一括更新できる
社員名簿を検索するとき
膨大な人数でも簡単に取り出せる
変更点はリアルタイムに更新できる
名簿をサーバーにおいて情報共有できる

あわせてVLOOKUPのメリットや注意点も解説しました。

再確認したい方は、下記から該当箇所を辿ってください。

【社員名簿を作る】

解説内容詳細へ
自動転記の仕組みを理解したい2-1.へ
基本的な数式を作りたい2-2.へ
転記する項目を増やしたい2-3.へ
別ファイルから転記したい2-4.へ
人数を増やしたい2-5.へ
作成時にVLOOKUPを使う
メリットを知りたい
3章へ
作成時にVLOOKUPを使う
注意点を知りたい
4章へ

【社員名簿を検索する】

解説内容詳細へ
検索する仕組みを理解したい5-1.へ
基本的な数式を作りたい5-2.へ
数式をカスタマイズしたい5-3.へ
検索でVLOOKUPを使う
メリットを知りたい
6章へ
検索でVLOOKUPを使う
注意点を知りたい
7章へ

本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルも参照可能です。
≫ PDFファイル参照ページはこちら

VLOOKUPで社員名簿作成時の単純入力作業を大幅に削減できます。完成後も社内で共有して有効活用してください。
そうすればよりクリエイティブな仕事に時間をまわせるようになります。時短にもつながり、残業を減らして早く帰れるようになることでしょう。

VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事もあわせて参照ください。
≫ VLOOKUPの使い方|基礎から応用まで完璧マスター&便利技20選を超図解!

エクセルでお困りのことがありましたら、『エクセルの窓口』で検索してください。

エクセルの窓口検索

ブックマークお気に入りに登録していただけたら幸いです。

スキルに自信がついたら新しい世界へ飛び出そう!
転職サイト登録なび
転職サイト登録なび