【Excel関数】人事担当者必見　Excel関数はこう使う！ 第1回 VLOOKUP関数

 

組み合わせや使い方で効果絶大、Excel関数はこう使う！

今回ご紹介するのはこちら

 

 

VLOOKUP関数

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

 

 

Excelを使う方にはお馴染ではないでしょうか、VLOOKUP関数です。

私は、2つのデータを突合したり、データベースから必要なデータをひっぱってきたりしたい場合に使います。

社員名簿や給与データから必要なデータがほしい場合、非常に便利でよく使われる関数です。

 

 

動画で見たい方はこちら！

 

 

その1：4番目の引数 [検索方法] の活用法

9月分の保険料から厚生年金保険料の保険料率が変更されました。

日本年金機構でも、平成26年9月からの標準報酬月額表が、PDFとExcelの両方で公開されています。

例えばこの標準報酬月額表のような「～以上～未満」の形式も、VLOOKUP関数を用いて、給与から標準報酬月額や保険料額を求めることができるのです。

 

まずは標準報酬月額表のExcelをダウンロードします。

 

 

これがExcelの標準報酬月額表です。

今回は、試しに同じファイルの別のシートに、標準報酬月額を算出する数式を作成してみます。

 

 

 

準備として、1か所だけ手を加えます。

E10のセルが空白ですが、値として0を入力します。

今回使うVLOOKUP関数では、このE列を参照するのですが、この列の「～以上～未満」の「～以上」の部分が空白だとエラーが出てしまうため、このような処理が必要になります。

また今回は便宜上、シート名を「標準報酬月額表」に変更しています。

 

 

シートを1つ追加しました。

今回はB3のセルに給与額、隣のC3のセルに厚生年金保険料の折半額を表示させます。

 

 

C3のセルに以下の数式を入力しました。

=VLOOKUP(B3,標準報酬月額表’!$E$10:$I$39,5,1)

 

ポイントは、最後の「1」です。

VLOOKUP関数を使うときは、最後は省略するか、0を入力するか、FALSEを入力するかが多いのですが、省略、0、FALSEにすると「完全一致する値を検索」するモードになるのです。

一方、1やTRUEを入力すると「近似値を含めて検索」するモードになるため、標準報酬月額を算出するのに適している、という訳です。

 

 

ただし、VLOOKUP関数で検索する範囲の一番左の列（今回の場合はE列）が、昇順になっている必要があります。

昇順になっていなければ、正しい結果が計算できなくなります。

試しに、100,999／101,000／101,001の3つの値を入力してみました。

値が合致していますよね。

 

これで、4番目の引数 [検索方法] の活用法は終了です。

近似値検索を使うことで、VLOOKUP関数の活用できる幅が広がりましたね！

 

 

その2：MATCH関数との組み合わせ

MATCH関数とは、どのような関数でしょうか。

 

MATCH関数

MATCH(検査値, 検査範囲, [照合の種類])

 

使いどころが難しいMATCH関数ですが、私はVLOOKUP関数と組み合わせて使うことが多いです。

MATCH関数は、「[検査値]を[検査範囲]から探してきて、何番目にあったかを数値で結果を返す」関数になります。ポイントは「数値で結果を返す」ところです。

VLOOKUP関数では、[列番号]の部分に使うことで、VLOOKUPを多用することになってもオートフィルで計算式を入力することができるようになります。

 

例えば以下のような名簿や、給与データから必要な情報を抜き出したい場合、項目が大量にあると、それだけ列番号を変えなければならず、非常に手間になります。

 

 

この名簿を、例えば以下のフォーマットのように必要な情報のみ抜き出す際に、VLOOKUP関数とMATCH関数を組み合わせた計算式を使うと、非常にうまくいきます。

 

 

最初のシート名を「名簿」、2番目のシート名を「作業シート」として、MATCH関数を使わずにVLOOKUP関数のみで値を引っ張ると、

 

氏名の列は=VLOOKUP(作業シート!$A2,名簿!$1:$1048576,2,0)

入社日の列は=VLOOKUP(作業シート!$A2,名簿!$1:$1048576,6,0)

エリアの列は=VLOOKUP(作業シート!$A2,名簿!$1:$1048576,8,0)

 

のように、列番号を列ごとに変えていかなければならず、手間がかかります。

 

MATCH関数を使うと以下のようになります。

=VLOOKUP(作業シート!$A2,名簿!$1:$1048576,MATCH(B$1,名簿!$A$1:$O$1,0),0)

 

MATCH(B$1,名簿!$A$1:$O$1,0)の部分は、「作業シートの項目名と一致するのは、名簿の項目名の中で左から数えて何番目か」という意味になっています。

つまり、VLOOKUP関数の列番号を求める数え方とまったく同じになるのです。

 

 

後はこの式をそのまますべてのセルにコピーすれば、完了となります。

これができれば、項目が多くても、コピー＆ペーストで手間はほとんどかかりません。

 

私は特に、給与のデータを前月と比較するときにこれを使っています。

少し難しいですが、覚えると業務効率はぐっと高まります。

 

また、業務に必要なITスキルを鍛えるITリテラシー研修や、業務フローを整え見える化するなどの人事業務改善提案の事例のような、SRのさまざまな事例はこちらからご覧になることができます。