ブログ

2020/07/22/

【タイムカード集計をエクセルで】Excelでの勤怠管理の方法と注意点

勤怠管理をExcel(エクセル)で行いたい、と検討中の方へ。 

勤怠管理をExcelで始めたいけれど、そのためには何をすればいいのか分からない、と困っていませんか。この記事ではExcelでの勤怠管理の方法と注意点を、勤怠管理の全体像を踏まえて紹介します。

勤怠管理の3つのステップ

勤怠管理を広く捉えると記録、集計、給与計算の3つのステップに分けることができます。

① 記録

勤怠管理を始めるには、まずは勤務時間を記録することが必要です。

勤務時間の記録には様々な方法がありますが、厚生労働省が平成29年1月に策定したガイドライン(労働時間の適正な把握のために使用者が講ずべき措置に関するガイドライン)には、原則的な方法が二つ挙げられています。

  • 使用者(実際に労働時間の状況を管理する権限を委譲された者を含む)が直接確認
  • タイムカード、ICカード、パソコンの使用時間などによる客観的な記録

この他にも、Excelファイルなどを利用して時刻を手入力する、紙の出勤簿に時刻を書き入れる、という方法を取っている場合もありますが、この場合には、事前に従業員への周知が必要です。上述のガイドラインについて詳しく知りたい方は労働時間の適正な把握方法とは?「労働時間の適正な把握のために使用者が講ずべき措置に関するガイドライン」の解説をご覧ください。

② 集計

①で記録した出退勤時刻や休憩時間、休日・休暇などの情報をもとに、勤務時間数や勤務日数などを集計します。

集計すべき項目は勤務パターンや雇用形態によって異なりますが、具体的には、欠勤日数、深夜時間帯、残業時間、早出、遅刻早退、不足時間、時短時間などが挙げられます。  

③ 給与計算

②で集計したデータを利用して、給与に必要な項目の計算をします。

具体的には課税支給額、通勤手当、控除額(社会保険料)、源泉所得税などです。法改正により各種保険料率、税率、割増賃金の割増率に変更があった場合、自社の給与計算への影響がないか確認が必要です。

エクセルでの勤怠管理の例

先ほど紹介した勤怠管理の3つのステップのうち、どこでExcelを使うかによって、Excelによる勤怠管理の運用方法は異なります。インターネット上にはExcelでの勤怠管理の無料テンプレートが多数公開されていますので、「どこでExcelを使用するか」に着目すると有効に活用できるかと思います。ここでは代表的な例を4つ、具体例とともに紹介します。

運用例(1)集計のみ

概要

勤務時間などの集計のみにExcelを用いる

こんな方におすすめ

タイムカードや給与計算ソフトの利用を検討、または既に利用している事業者さん

運用方法

  1. 労働者はタイムカード等を用いて日々打刻する
  2. 月度の終わりに管理者がタイムカードを回収する
  3. タイムカードに基づいてを出退勤時刻などをExcelファイルに転記し、Excelファイル上で時間数の集計を行う
  4. 給与計算は、給与計算ソフトを用いるなどExcel外で行う
    ・給与計算ソフトに集計結果を手入力する
    ・給与計算ソフトに取り込める形式に、集計結果を加工したファイルを用意する、など

運用例(2)記録 + 集計

概要

日々の出退勤をExcelファイルに記録し、集計を行う

こんな方におすすめ

一人一台PCを使用でき、日々の勤怠の申請を自己申告で行っている事業者さん

運用方法

  1. 労働者が個々でExcelファイルを持ち、日々出退勤時刻を手入力する
  2. 労働者は自身で入力した勤怠データ(Excelファイル)を月の終わりに管理者に送る
  3. 管理者は入力されたデータを集計用のExcelファイルにペーストし、労働時間などを集計する
  4. 給与計算は、運用例(1)同様に給与計算ソフトなどExcel外で行う

運用例(3)集計+給与計算

概要

タイムカードなどの記録に基づいて集計と給与計算をExcelで行う

こんな方におすすめ

雇用形態がシンプルで、給与計算のパターンが少ない事業者さん

運用方法

  1. 労働者はタイムカードを用いて日々打刻する
  2. 月度の終わりに管理者がタイムカードを回収する
  3. 管理者は出退勤時刻などをExcelファイルへ手入力で転記して、労働時間の集計を行う
  4. 集計結果をもとに、給与計算を行う(同一シート内で自動集計する、給与計算用のシートまたはファイルを別で準備する、など)

運用例(4)記録+集計+給与計算

概要

記録から給与計算までExcelファイルのみで管理する

こんな方におすすめ

Excelだけで勤怠管理を一気通貫で行いたい事業者さん

運用方法

  1. 労働者が個々でExcelファイルを持ち、日々出退勤を手入力する
  2. 労働者は自身で入力した勤怠データ(Excelファイル)を月の終わりに管理者に送る
  3. 管理者は入力されたデータを集計用のExcelファイルにペーストし、労働時間などを集計する
  4. 集計結果をもとに、給与計算を行う(同一シート内で自動集計する、給与計算用のシートまたはファイルを別で準備する、など)

時間計算のワンポイントアドバイス

Excelによる勤怠管理の方法について、具体例とともに紹介しました。ここでは、Excelで時間の計算を行うときの注意点と勤怠管理に活用できる関数を簡単に紹介します。是非活用してみてください。より詳しく知りたい方は、ここで紹介するキーワードを参考に調べてみてください。

時間の計算を行うときの注意点

*シリアル値

Excel内部では日付や時刻をシリアル値で管理しています。具体的には、1900年1月1日を1として計算しています。同様に、1日(24:00)も1として計算しています。

試しに「1900/1/2」と「12:00」をそれぞれセルに入力し、セルの書式設定を「標準」に変更してみてください。1900/1/2と入力されたセルは2(1900/1/1から1日後)であり、12:00と入力されたセルは0.5(24:00の半分)と表示されます。

時間数の計算が上手くできない場合は、このシリアル値が原因かもしれません。(詳細は後述します。)

*24:00以上の時間数の表示

セルに23:59と24:00を入力すると、24:00は24:00:00と自動で変換されて表示されます。あるいは、時間数同士の合計が24:00以上になると、正しく表示されません。(例:24:00が0:00と表示されてしまう)そんなときはセルの書式設定を調整します。セルの書式設定を「ユーザー定義 [h]:mm」に変更すると、23:59以下と同様に24:00と表示できます。

*10進数と60進数の変換

Excel内部では時間数は前述のシリアル値で管理されているため、10進数との計算に用いるためには調整が必要です。具体的には24:00を1で管理しているため、10進数の”24”として計算するには24倍する必要があり、逆に10進数の1を”1:00”として計算するには1/24倍する必要があります。

下記に、10進数⇔60進数の変換時の注意をまとめました。

 60進数⇒10進数:24倍にしてセルの書式設定を「標準」に
 10進数⇒60進数:1/24倍にしてセルの書式設定を「ユーザー定義 [h]:mm」に

具体的には、時給 × 労働時間 を計算したい場合は時給 * 労働時間 * 24 という式を用います。60進数の労働時間を10進数に直して、時給と掛け合わせる必要があるからです。
※数式そのものには60進数(150:00など)は用いることができませんので、上記はその値の入ったセルを参照している想定です。

勤怠管理に活用できる関数

*TIME関数

時間、分、秒の3つを指定すると、時間数を返します。
予め決まっている時間数を表すときに、活用できます。
たとえば、休憩時間が1時間で固定の場合、TIME(1,0,0)と表すことができます。

Excelは計算式に60進数(1:00など)を直接用いることができません。したがって一日の労働時間を求めるには「=<終業時間のセル>-<始業時刻のセル>-<休憩時間のセル>」というように、休憩時間用のセルを用意して1:00を毎回入力しなければいけません。

そこで日の労働時間のセルに「=<終業時間のセル>-<始業時刻のセル>-TIME(1,0,0)」というように数式にTIME関数を組み込んでおけば、毎回1:00と入力する手間も行も省くことができます。

*HOUR関数、MINUTE関数

指定した時刻からそれぞれ、時間、分を返します。
60進数を10進数に変換するときに、活用できます。
24倍や1/24倍で悩む必要はありません。

9:30 ⇒ HOUR関数を使えば9、MINUTE関数を使えば30を取り出せます。

*FLOOR関数

データを指定した基準値で切り捨てます。
〇分単位のまるめ、をしたいときに活用できます。

先ほど紹介したTIME関数と組み合わせることがおすすめです。

例:10分まるめ =FLOOR(まるめ前の時間,TIME(0,10,0))

その他

*VLOOKUP関数

指定した範囲の先頭列を縦方向に検索し、検索条件に一致したデータを検索して取り出します。シフト記号を用いた勤怠管理を使用している場合に活用できます。

具体的には、勤怠管理シートとは別にシフト記号シートを用意し、勤務開始時間などを記入しておけば、「記号を入力すると、それに対応した時間数を表示する」ということができます。
時間計算を直接行っているわけではありませんが、運用上決まっている時間を表現する際に便利なので、併せて紹介しました。

Excelで勤怠管理するメリットと課題

Excelでの勤怠管理について紹介してきましたが、Excelで勤怠管理を行うメリットをここで整理します。Excelでの勤怠管理には、手作業のみでの勤怠管理と比較して、主に3つのメリットがあります。

メリット

  • 正確に計算でき、カスタマイズもしやすい
  • 計算を自動で行うため、時短に繋がる
  • 勤怠記録を電子化できるため、保管しやすく紙の保管が不要

Excelさえあれば、導入コストがかからない点も魅力的です。しかし、残念ながらExcelでの勤怠管理だけでは解決することが難しい課題もあります。

課題

  • 情報を手入力しなければならず、依然として手間が掛かる
  • 手入力時のミス、計算式のミスによって誤った集計結果になってしまう

もっと手軽に勤怠管理を行いたい方へ

Excelでの勤怠管理の方法は分かったけれど、「なんだか難しそう」、「手間やミスを減らして手軽に勤怠管理を行いたい」と感じた方もいらっしゃるかもしれません。そんな方には勤怠管理のシステム化がオススメです。

勤怠管理のシステム化には、Excelでの勤怠管理と比較してこんなメリットがあります。

  • 出退勤時刻を客観的な方法で記録でき、タイムカードも不要になる。
  • 手入力の手間が省け、ミスも防げる。

勤怠管理システムにも沢山の種類がありますが、数人~数十人程度の勤怠管理を行いたい方にはタブレット タイムレコーダーがおすすめです。

タブレット タイムレコーダーはiPadをタイムレコーダーにするアプリです。
タブレット タイムレコーダーには、下記の特徴があります。

  • iPadのタッチ操作で簡単打刻
  • 多くの集計項目はExcelで計算せずにアプリから集計値を直接出力
  • 出力した集計データは多くの給与計算ソフトとも連携可能
  • 初期費用のみで、月額費用は不要
  • ビデオメッセージ、打刻時メール送信など、通常のタイムカードにはない様々な機能付き

詳しくはタブレット タイムレコーダーのホームページをご覧ください

この他にもタイムレコーダーについて知りたい方は、タイムレコーダーの選び方をご覧ください。

おわりに

いかがでしたか。Excelでの勤怠管理を始めてみたい方は、この記事に載せた方法を参考に、是非一度試してみてください。「なんだか難しそう」、「試してみたけれど、なんだか思い通りに使いこなせない」「Excel勤怠管理では物足りない、もっと便利にしたい」そんな場合は、タブレット タイムレコーダーの購入も是非検討してみてください。