2016/01/15 公開

VLOOKUP、COUNTIF、MATCH関数を使って勤務管理を効率化!EXCEL VLOOKUP術[Vol.5]

Pocket

Excelシート上の参考表から必要なデータを検索し、指定した箇所に表示させることのできるVlookup関数。大変便利な関数ですが、参照表内に同じ検索値が複数ある場合に、最初に一致した検索値しか引用できないという弱点があります。 Vol.4では、Vlookup関数COUNTIF関数を組み合わせることで、同一の検索値が複数ある場合に任意のデータを抽出する方法をご紹介しました。

今回は、そのメソッドを用いながら、社員の勤務状態を検索する実践編をお送りします。勤怠管理は、人事にとって重要かつ、骨の折れる仕事。特に従業員の人数が多くなれば、誰がいつどのような勤務状況だったのか、その都度データを見て確認するのことは大変手間です。そこでこの度は、社員の勤務状況を日付けごとに一発で検索する方法をご紹介します。

COUNTIF、Vlookup、MATCH関数を使って、勤怠表を作ろう!

今回は、使うのは以下のような表。
1

左の勤務表は、日付順に社員の遅刻・欠席・早退を記録したものです。
このデータを元に、右の表の上で、日付けごとに「社員番号」「名前」「勤務状態」が表示されるようにしたい場合に、Vlookup関数を挿入しただけでは、上手くいきません。

例えば、右の表に12/10(木)と記入した時、「溝田優里」「山田真一」「山村和彦」3人のデータを表示させたくても、実際には「溝田優里」のデータしか返されないのです。

これは、Vlookup関数の特性上、同じ値の検索値が複数ある場合に、検索範囲の一番最初のデータが引用されてしまうため。12/10(木)を検索値とするデータは3つ存在しており、そのうち一番最初にヒットする値である「溝田優里」のみが表示されてしまいます。

では、勤務表に従って、右の表が埋まるようにするためには、どのような処理をすれば良いのか。ステップとしては、以下の3つを踏みます。
(1)COUNTIF関数を使って、重複しているVlookup関数の検索値を唯一無二に加工。
(2)H6のセルにVlookup関数を挿入。
(3)MATCH関数を使って、Vlookupの列番号を列ごとに変えなくても済むように処理する。

では早速手順スタート!

ステップ1、COUNTIF関数を使って重複データに対応

まずは、A列の「日付け」の横に、それぞれ固有のキーワードを記入して、重複するデータを固有化していきます。

以下の図のように、勤務表の左側に作業列を加えましょう。
2

まずは、A4のセルに=COUNTIF($B$4:$B4,$B4)という数式を入力し、A15までコピーします。
ポイントは、検索範囲を指定する「$B$4:$B4」のコロン以前の部分に、「$」マークをつけること。

これによって、A列には「日付けの個数」、すなわちB列の日付けが、その列で何番目に出てきたかという数を示します。(詳しくは、VLOOKUP関数で同一の検索値が複数ある場合のデータ抽出術!EXCEL VLOOKUP術[Vol.4]前編をご覧ください。)

3

さて、次は「Key」という作業列のセルに、AND関数を使ってそれぞれ「日付けの個数+日付」を入力し、重複の無いデータを作っていきます。

まずは、A4のセルに挿入した=COUNTIF($B$4:$B4,$B4)の式に「&$B4」という数式を付けたし、15列目までコピーします。これによってB列の「日付け」と、その日付けがB列で何番目出てきたかを示す「日付けの個数」を組み合わせた値が、A列に表示されるようになりました。

4

さて、これでVlookup関数を使うための下準備はOKです。

ステップ2、H6のセルにVlookup関数の式を入力

右の表のH6のセルに=VLOOKUP($G6&$I$3,$A$4:$E$15,3,0)という数式を入力します。
5★

VLOOKUP関数の検索値を示す「$G6&$I$3」の部分は、「No.」すなわち「日付けの個数」を示すG6のセルと、「日付」を記入するI3のセルを[&]で結合しています。そうすることで、先ほどA列に追加した作業列の値を検索値とすることができるようになりました。

「$G6」の部分は「6」の前に「$」マークを付けず、相対参照にすることがポイントです。これによって、式を下方向にコピーしていくと「$G7」「$G8」・・・と数字が変化していきます。

式の定義は以下の通り。

7

このVlookupの式をH列の下のセルへコピー&ペーストしていけば、「日付け」を入力しただけで、「社員番号」が表示されるようになります。
6★

とはいえ、右の表で表示させたいデータは「社員番号」だけでなく、「名前」、「勤務状態」を含む社員のデータでした。

次のステップとしては、式を縦方向だけでなく横方向にもコピー&ペーストすれば良いのですが、Vlookupの数式は右方向にコピペする際に、列番号を(「名前」なら「4」、「勤務状態」なら「5」)手動で変えていく必要があるという欠点があります。

そこで役に立つのがMATCH関数です。

ステップ3、MATCH関数を使って列番号を可変にする

最後は、Vlookup関数の数式にMATCH関数を組み入れます。

H6に入力した数式=VLOOKUP($G6&$I$3,$A$4:$E$15,3,0)における列番号の部分をMATCH関数に変え、=VLOOKUP($G6&$I$3,$A$4:$E$15,MATCH(H$5,$A$3:$E$3,0),0)としましょう。

この数式を、右の表の各セルにコピペしていけば、日付けを入力しただけで、「社員番号」「名前」「勤務状態」が表示されていきます。例えば、12/10(木)と記入すれば、その日に遅刻・欠席・早退した「溝田優里」「山田真一」「山村和彦」のデータが、一括で抽出されるのです。
10★

ちなみに、MATCH関数とは、目当てのデータが参考表の何列目にあるか数値でフィードバックしてくれるというもの。これをVlookup関数の列番号の部分に組み入れると、自動的に欲しいデータのある列を探し出して結果を表示してくれます。(詳しくは、MATCH関数とVLOOKUP関数の合わせ技!人材管理に役立つEXCEL VLOOKUP術[vol.2]を参照)

MATCH関数の部分を抜き出すと、定義は以下のようになります。
9

Vlookup、MATCH、COUNTIFをおさらいしよう!

組み合わせ次第で、様々な作業を可能にする関数。EXCELのスキルUPシリーズでは、Vlookupと共にMATCH、COUNIFなどの関数とその活用法を紹介して参りました。これらの関数は、覚えておけばEXCELライフをより良きものにしてくれること間違いありません。

5回目となるVOL5は、その集大成として関数をおさらいする機会とさせて頂きました。日々の作業の効率化に役立てば幸いです。

運営会社 | Copyright © kaonavi, inc. All Rights Reserved.