photo credit: Red Doors and Yellow Bricks via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
Googleスプレッドシートを使った勤怠管理集計システムを作るシリーズの第３回目となります。
前回はこちらの記事。
スプレッドシートの定番関数DATE,MOD,AND,OR,IFなどを使って日付と曜日の箇所を簡単かつクールに出力する方法についてお伝えしました。
今回の主役は、データを抽出する超定番関数VLOOKUPです。
GoogleスプレッドシートでVLOOKUP関数を使う基本に加えてVLOOKUP関数で複数条件による検索をする方法についてお伝えします。
またVLOOKUP関数でエラーになったときにエラー表示を空欄にするIFERROR関数の使い方にも触れます。
スプレッドシートのVLOOKUP関数の使い方
では、まず初級編ということで、VLOOKUP関数の基本的な使い方からです。
以下のように社員IDを入力することで、セルB2に氏名（姓と名、半角スペースを空ける）、セルB3に所属が自動で表示されるようにしたいと思います。
社員に関する元データは「社員マスタ」という別のシートに格納されています。
例の場合ですと、社員マスタのB列から「001」という社員IDを検索して、その部署と氏名を引っ張ってくれば良いということになります。
このような場合にVLOOKUP関数を使います。書き方としては
とします。
ちょっとややこしいですが、これにより指定された範囲の1列目から検索値を探し当てて、その探し当てた行の列番号にある値を返します。
例えば部署欄であるセルB3に社員IDをキーとして部署名を求めたい場合は
- 検索値：社員番号つまりB1セルの値
- 範囲：社員マスタのB列からF列（この場合、B列からC列でも可）
- 列番号：部署はC列、範囲がB列からなのでC列の列番号は2
- 検索方法：FALSE（完全一致）
ですから
と入力します。
同様に「勤怠管理」シートの氏名欄であるセルB2には
とすればOKです。姓と名をそれぞれVLOOKUP関数で引っ張ってきてアンパサンドで結合をしています。
打刻に関するデータの取得方法は？
次のお題ですが、以下の図の「勤怠管理」シートのF～H列に当たる区分、打刻（出社時間、退社時間）を埋めていきたいと思います。
よくあるパターンですと、このF～H列に
- 出社｜9:00｜18:00
- 出社｜9:15｜23:00
- 代休
- 有休
- 欠勤
- …
などと手入力でデータを入れていってしまいたいところですが、そこはグッとこらえて別に用意したデータ専用のシートから数式で呼び出すのをお勧めします。
例えばこのようなシートです。
データが1行ずつ格納されていて、A～F列はそれぞれ
打刻ID｜年月｜社員ID｜区分｜打刻（出社）｜打刻（退社）
で構成されています。
なぜ、わざわざデータを別シートに分けているのかについて先にお話をしておきます。
データは一か所にまとめておく
勤怠管理の過去のデータが年月×社員ごとにバラバラのシートになっていた場合、例えば
- 特定の社員の一年間の勤務時間の合計を知りたい
- 年月ごとのの社員全員の有休取得日数を計算したい
などといった通常以外の集計をしたい場合に、バラバラのシートからコピペなどでデータを集めてくる手間が発生します。
コピペを何回も繰り返さなくてはなりません。
しかし、もともとからデータを上記のような形式で保管しておけば、その目的ごとにフィルタや関数で簡単に抽出することができます。
データは一か所のシートに、1行1データの形式でまとめて蓄積しておきましょう。
ちなみに、この形にどのようにしてデータを集めてくるのか、というのはまた別の記事で紹介しますので、ひとまず今回はこの形式でデータが集まっている、ということにしておいてください。
複数の条件でVLOOKUP関数を使う
さて、話をもとに戻しましょう。
データシートから特定の日付かつ特定の社員番号の区分、打刻（出社）、打刻（退社）を引っ張ってきたいと思います。
なんとなくVLOOKUP関数を使えば良さそうですが、キーとなる検索値が「日付」と「社員ID」という複合の要素になっています。
このような複数の検索値でVLOOKUPをするためにはどうしたらよいでしょうか？
二つの要素を結合してIDを生成する
この場合、二つの検索値「日付」と「社員ID」を結合して「打刻ID」生成することで解決ができます。
「データ」シートのA列、打刻IDですが、例えばセルA2の計算式を見てみますと
このように
となっています。つまりB列の年月と、C列社員IDを結合したものなんですね。
日付は全然違うように見えますが、「2016/06/01」の表示形式を「数値」に変更すると「42522」という5桁の数値になります。
この結合した「打刻ID」をキーとしてVLOOKUPをすると
- 検索値：打刻IDつまり日付と社員IDを結合したもの
- 範囲：社員マスタのA列からF列
- 列番号：区分はD列、範囲がA列からなのでD列の列番号は4
- 検索方法：FALSE（完全一致）
ですからセルF3であれば
とします。
F列に上記数式をコピーします。
おや…「#N/A」というエラーが出てしまうセルがありますね。
IFERROR関数でエラーを非表示にする
VLOOKUP関数は該当の検索値が見つからない場合にはエラー「#N/A」を返してしまいます。
このままでは見苦しいので、エラーのときは非表示にする方法をお伝えします。
この場合は数式がエラーかどうかを判定してその際は指定した値を返すIFERROR関数を使います。
数式がエラーであればそのまま数式の結果を返しますが、エラーの場合は値を表示します。
今回の場合は、F3セルであれば
とすればOKですね。エラーの場合は空文字、つまり何も表示されません。
これをF列にコピーしますと
このようにVLOOKUPで検索値がない箇所は空欄となりました。
G列、H列も同様にしてVLOOKUPで値を引っ張ってくることができますので、ぜひトライしてみてください。
まとめ
GoogleスプレッドシートでVLOOKUP関数を使う方法についてお伝えしました。
最もシンプルなVLOOKUP関数の使い方に加えて、複数の条件を組み合わせたVLOOKUPの方法、また検索値が存在しなかったときにエラー表示をさせない方法についてお伝えしました。
次回は時刻を分単位で丸めて給与計算の元になる時刻を求める方法についてお伝えしていきます。
どうぞお楽しみに！
連載目次：Googleスプレッドシートで勤怠管理集計システムを作る
勤怠管理業務はたくさんのスタッフとのやり取りもありますし、集計もなかなか複雑です。本シリーズでは複数人や離れた場所での共同作業も得意なスプレッドシートを用いてクラウド上で勤怠管理システムを作る方法をお伝えしていきます。- Googleスプレッドシートでクラウド上の勤怠管理集計システムを作る
- 【スプレッドシート初心者向け】定番関数と表示形式で手入力しないで日付と曜日を出力
- GoogleスプレッドシートのVLOOKUP関数で複数条件による検索を行う方法
- 【スプレッドシートで給与計算】打刻時間を指定の分単位で切り上げ、切り捨てをする方法
- GoogleスプレッドシートでIF文を超絶駆使して通常・残業・深夜の就業時間を求める
- Googleスプレッドシートで時間内・時間外（残業）・休日・深夜の労働時間を計算する
- Googleスプレッドシートによる勤怠管理シートをCOUNTIFS関数と書式設定で仕上げる
