INDEX関数とVLOOKUP関数を組み合わせて昇給額を算出!EXCEL VLOOKUP術[Vol.3]

2015/10/15
Pocket

Excelシート上の参考表から必要なデータを検索し、指定した箇所に表示させることのできるVlookup関数。人材のデータを管理する上で欠かせないメソッドです。Vlookup関数の活用術Vol.3となる今回はINDEX関数とVlookup関数を使って、人事考課と年齢を反映した昇給額を求める方法を説します。

INDEX関数とVlookup関数で返した値を掛け合わせて、昇給額を自動的に求めよう!

今回使用するのは、人事考課や年齢によって昇給額が変化するケースです。
例えば、人事考課が4つのレベルに分かれており、年齢によって減額率が変わるとしましょう。

人事考課のレベルは
レベル1 昇給額1500円
レベル2 昇給額1000円
レベル3 昇給額0円
レベル4 昇給額-1000円

減額率の年齢による区分は
29歳までが80%
30歳から39歳までが60%
40歳から49歳までが100%
50歳が40%

この場合、例えば人事考課のレベルが1、年齢が28歳の人の昇給額は、
1500円(基本の昇給額)×80%(減額率)=1200円(昇給額)

レベル2で、年齢が36歳の人の昇給額は
1000円(基本の昇給額)×60%(減額率)=600円(昇給額)

となるわけです。
しかし、いちいちデータを照らし合わせながら、減額率を計算するのは大変面倒。

INDEX関数とVlookup関数を使えば、下の表に、「年齢」、「人事考課」を入力するだけで効果テーブル、年齢テーブルのデータに沿って、「基本の昇給額」、「減額率」、「昇給額」が自動的に表示されるようにできます。

1

以下の例であれば、「年齢」のセルに20歳、「人事考課」のセルに2と記入すれば、「基本の昇給額」が1000円、「減額率」が0.8、「昇給額」が800円というデータが自動的に記入されます。

2

この場合、組み込まなければならない数式は、INDEX関数Vlookup関数、そしてアスタリスク「*」を使った掛け算の式です。

ステップ1、INDEX関数を組み込む

まず始めのステップとして必要なのは、考課テーブルからの検索。INDEX関数をC10のセルに組み込むことで、赤枠で囲った「人事考課」のセルに入力した時に、効果テーブルのデータに従って「基本の昇給額」の値が返るようにします。

3

数式の定義は以下の通り。
6

INDEX関数とは、一言で言うと、指定した行番号と列番号の交差するセルの値を返す関数です。
考課テーブルのセル「A3からB6」の範囲で、B10のセルに示された行番号(今回は2)と列番号2が交差するセルの値(今回は1000)を返します。

C10に組み込まれたこの式を、下まで一気にコピ&ペーストすればステップ1は完了です。

ステップ2、Vlookup関数を組み込む

「人事考課」の値に従って、「基本の昇給額」が表示されるようになりました。次のステップは、年齢テーブルからの検索です。Vlookup関数をD10のセルに組みこんで、赤枠内の「年齢」のセルに入力した時、年齢テーブルのデータに従って「減額率」が表示されるようにします。

9

この場合に必要な数式は、
「=VLOOKUP(A10,$D$3:$E$6,2,TRUE)」
「A10の値を、D3からE6のセル範囲(年齢テーブル)の2列目から検索して、返す」という意味の式です。(Vlookup関数の定義を詳しく知りたい方は、「意外に簡単!必ず効率がアップする人材管理に役立つExcel Vlookup術[vol.1]」をどうぞ)

ポイントは絶対セル番地を示す「$」のマークを必ずつけること、そして検索方法を「TRUE」と指定することです。「TRUE」と指定することによって、検索値であるA10の値と完全に一致する数が年齢テーブル上で見つからない場合、検索値を超えない範囲で、最大の値を返します。

例えば、表の年齢セルに20と入力した場合。年齢テーブルには20と書かれた値は無いため、20を超えない範囲で最大の値は1。1に対応する「減額率」80%が表示されるという訳です。

D10に組み込まれたこの式を、下まで一気にコピー&ペーストすれば、「年齢」に従って「減額率」が返されるようになりました。

ステップ3、アスタリスク「*」を使う

自動的に「基本の昇給率」と「減額率」が表示されるようになりました。しかし最終的に求めたいのは各社員の「昇給額」。これはE10のセルに「基本の昇給率」と「減額率」を掛け合わせる式を組み込むことで求められます。

必要な式は、「C10D10」。「」は掛け算を表しています。
以下の例のように、「基本の昇給額」が1000、「減額率」が0.8であった場合、これらが掛け合わされて、「昇給額」800円の値を得ることができます。

7

E10に組み込んだこの式を、下までコピペすればすべての行程は完了!

これで、人事考課と年齢を手入力すれば、すべての表が完成するようになりました。完成図は以下の通り。
8

複数の関数を活用して、業務を効率化!

今回は、Vlookup関数とINDEX関数、アスタリスク「*」を同時に活用する方法をご紹介しました。
数式を工夫して組み合わせれば、様々な業務がより早く、効率的に消化できるようになります。次回もお楽しみに。

マネたまご マネたまをフォローすれば最新記事をお届けします!
運営会社 | Copyright © kaonavi, inc. All Rights Reserved.