勤怠管理表をエクセルで作るには？マクロ・関数や無料テンプレートを紹介

勤怠管理をエクセルでおこなうには、マクロや関数を活用すると手間を削減できます。エクセル勤怠管理表は、残業時間への手当や就業時間の計算などにも活用できますが、注意点も存在します。本記事では、エクセルを用いた勤怠管理に必要なマクロや関数での計算方法と、運用上の注意点などを解説します。

1.エクセルで勤怠管理をする方法

勤怠管理で必要な工程は、記録・集計・計算の3つの段階に分けられます。その際、特に意識しなければいけないのが、2019年4月の労働安全衛生法改正です。

同法の改正により、勤怠管理では使用者が従業員の労働時間を客観的方法による把握が義務付けられました。

この点を踏まえながら、エクセルで勤怠管理をするときはどのような点に気を付ければよいか解説します。

1-1.従業員の出退勤時刻を記録する

打刻では、労働時間の適正な把握が必要であるため、従業員が自己申告でエクセルシートに出退勤時刻を入力することは認められていません。

原則として、以下いずれかの方法が求められます。

• 使用者が自ら始業・終業の時刻を確認し記録する
• タイムカードなど客観的方法により記録する

勤怠管理のすべてをエクセルでおこなう場合、使用者や管理者が直接労働時間を確認した上で記録しなければいけません。
なお、やむを得ず自己申告制とするときは、定期的な実態調査などが必要となります。

［注1］労働時間の適正な把握のために｜厚生労働省

1-2.管理者が勤怠情報を集計する

入力された情報を元に、管理者は労働時間や残業時間、深夜労働、休日労働などに分けて集計をおこないます。

集計の際は、時間外労働の上限規制に抵触していないか、割増賃金が必要な時間帯は正しく分類されているかなど、複数項目の確認が必要です。

さらに、労働時間以外にも、有給休暇の取得状況なども確認しなければいけません。

なお、労働時間の記録や賃金に関する書類は5年間（当分の間は3年間）の保存が義務付けられているため、エクセルデータを破損しないように十分注意しましょう。

1-3.給与支払いに必要な情報を計算する

集計した情報を元に、従業員ごとに給与計算をおこないます。エクセルによる勤怠管理表を用いる場合には、エクセルに関数やマクロを入れておくと、入力データを元に自動で必要な計算をおこなえるため活用しましょう。

2.エクセルで勤怠管理をおこなうなら関数・マクロを活用すべき

エクセルで勤怠管理をおこなうなら、計算をおこなう機能を事前に組み込んでおきましょう。関数やマクロなど、必要な機能を解説します。

2-1.エクセルでの勤怠管理に役立つ関数

エクセルの関数とは、定型計算を自動でおこなう数式のことです。

セルに「=関数名（引数1,引数2,…）」と入力すると所定の計算を自動で行います。勤怠管理では以下の関数を利用します。

■四則演算
エクセルでは、数式バーに「=A-B」などと入力するだけで四則運算ができます。
労働時間を求める際に利用します。

■SUM関数
合計を計算する関数で、「=SUM（A1：A5）」のように入力すると、セルAの1から5までの合計を表示します。合計勤務時間の計算などで利用します。

■COUNTA関数
数値が入っているセルの個数を数える関数で「=COUNTA（A1：A5）」のように入力します。
出勤日数の計算などに利用できます。

これらの関数を組み合わせることで、勤怠管理を効率化できます。

2-2.エクセルでの勤怠管理に役立つマクロ

複数の作業を自動化するプログラムのことで、「VBA」というMicrosoft Office用のプログラミング言語を使って作成します。

マクロの活用により、従業員の名前や基礎賃金などを記載した元データから、必要な内容を別のエクセルシートに転記するなどが可能となります。

他にも、書類の作成やデータチェック、データ集計などさまざまな機能が利用できます。

3.エクセル勤怠管理表の作り方

ここからは、エクセルで勤怠管理表を作成する方法を解説します。関数を組むことは、一見難しいように感じる方も多いと思いますが、仕組みを理解することで勤怠管理表のみならず他の業務にも活用しやすいです。是非参考にしてみてください。

3-1.勤務時間を計算する関数

勤務時間とは、出勤してから退勤するまでの時間であり、かつ休憩時間を抜いた時間にする必要があります。
そのため、計算式にすると「出勤時刻ー退勤時刻ー休憩時間」となります。

例えばSUM関数をもちいたエクセル表を作成する場合には、まず1行目のA列に「日付」、B列に「出勤時間」、C列に「退勤時間」、D列に「休憩時間」、E列に「実労働時間」を記入し、項目をつくります。

次に、2行目のB～Dに時間を記入していきます。
そしてE2セル（E列の2行目）に「＝C2－B2－D2」と入力し、エンターキーを押します。

すると2行目に記入した勤務日の実労働時間が自動計算され、E2セルに表示されます。
翌日以降においても、同じ計算式で算出が可能なため、E2セルをコピーすることで反映が可能です。

3-2.1か月の勤務時間を集計するSUM関数

1ヵ月分の勤務時間の集計にもSUM関数を活用することで算出が可能です。
E列の実労働時間に関して、月の初日がE2セル、末日をE30セルに表示されており、合計勤務時間をE31セルに設定するとします。

まず、E31セルを選択してエクセルの機能などが表示される上段のタブから「数式」を選択し、「オートSUM」を選択します。
次にオートSUMにある「合計」を選択すると、E2からE30までが範囲として選択されるため、エンターキーを押すと1ヵ月の合計勤務時間が算出されます。

3-3.残業時間を集計するIF関数

残業時間の集計は、残業代を算出するためにも重要です。IF関数を組むと簡単に集計が可能です。

所定労働時間を8時間と設定している企業の場合、先程のシートを用いると下記の公式で算出が可能です。
「=IF(C2-B2>TIME(8,0,0),C2-B2-TIME(8,0,0),””””)」

3-4.セルの表示形式を「時刻」に変更するには

SUM関数の合計時間を算出した際に、セルが思うように反映されない場合、正しい表示形式に設定できていない可能性があります。

表示形式を変更するには、対象セルを右クリックし、「セルの書式設定」を選択します。表示形式は、分類から「ユーザー定義」を選び、種類に「［h］:ｍｍ」と入力します。
すると、時間形式で表示することが可能となります。

3-5.勤務時間データから給与計算をするには

勤務時間の合計が算出できた後は、エクセルで給与計算をおこなうことも可能です。
給与の算出には時給が必要なため、例えばD33セルに入力したとします。

先程のエクセルシートのE列の33に給与額を出したいときには、E33セルを選択して下記の計算式を入力します。
「=E32*D33」
ちなみに、月給制で「時給」を計算する必要がある場合は、下記の公式でだすことができます。
「時給（1時間あたりの基礎賃金）＝月給÷月平均所定労働時間」

月平均所定労働時間を算出するには、下記の公式にあてはめます。
「月平均所定労働時間＝（1年の暦日数－年間休日日数）×1日の所定労働時間÷12ヵ月」

時給（1時間あたりの基礎賃金）の計算方法をより詳しく理解したい方は、こちらの記事をご確認ください。

給与額は、勤務時間と単位を変更する必要があるためE33セルを右クリックし、「通貨」という表示形式を選択する必要があります。

4.エクセルで勤怠管理表を作成するときに役立つ無料テンプレート

エクセルを使って勤怠管理表を自作するのが難しいときは、無料のテンプレートを活用するのがおすすめです。ここでは、使いやすい無料テンプレートをピックアップして紹介しますので、チェックしておきましょう。

Officeテンプレート

「Officeテンプレート」は、Microsoftが提供しているテンプレートです。シンプルなデザインであることが大きな特徴で、公式サイトからダウンロードすれば誰でも簡単に使えます。勤怠管理や給与計算に役立つエクセルテンプレートも多く提供されているため、公式サイトで検索してみましょう。

みんなのExcelテンプレート

「みんなのExcelテンプレート」は、業務に役立つ多数のテンプレートが手に入るWebサイトです。勤怠管理やシフト管理など、事務作業を効率化するためのさまざまなテンプレートが提供されているため、有効活用しましょう。

bizocean

「bizocean」は、2.8万点以上のビジネス向けテンプレートを無料でダウンロードできるサービスです。勤務時間を算出するだけのシンプルなタイプから、給与計算までできる複雑なタイプまで多くのテンプレートがあるため、自社の状況に合わせて利用しましょう。

5.勤怠管理でエクセルを利用するメリット

ここまで勤怠管理表をエクセルで作成する方法について解説してきました。エクセルで関数やマクロを組み込むことは難しい反面、メリットも存在します。
ここからは、改めて勤怠管理でエクセルを用いることのメリットをおさらいします。

5-1.コストが低く自由度が高い

エクセルで基本的な四則演算や関数などを組むこと自体は、無料でおこなうことが可能です。そのため、どうしても初期費用がかけられない場合の勤怠管理にはよいでしょう。

またエクセルに詳しい社員がサポートをしてくれる場合は、エラーで悩むことが少なく、また使い勝手のいい表を作成できることから、メリットが享受しやすいといえるでしょう。

5-2.一度関数を組めば労働時間を自動計算できる

エクセルのもう一つのメリットとして、関数を一度組んでしまえば、その後計算する手間がかからないことが挙げられるでしょう。

出勤時間・退勤時間・休憩時間を入力するだけで、自動で労働時間を算出できることから、計算ミスの発生やチェック業務に割いていた時間も省くことができます。

6.勤怠管理でエクセルを利用する際の課題

勤怠管理はエクセルでもできるものの、有効活用できる企業は限られるでしょう。さらに、法改正の際は手作業でデータの変更が必要になり、管理の手間もかかってしまいます。

6-1.就業規則が複雑な企業では使いづらい

従業員数が少なく勤務時間も一定など、就業規則が単純な企業では、エクセルで勤怠管理をおこなう事も可能でしょう。

しかし、従業員数が多く、テレワークやフレックス制など、複数の勤務形態があるような企業では、エクセルのみで勤怠管理をおこなうのには限界があります。

間違い防止のためにも、手間をかけずに勤怠管理できるシステムの導入が求められます。

6-2.打刻はエクセル以外の方法が必要

始業・就業時間を使用者が自ら確認できないなら、客観的に記録する方法が必要ですが、エクセルのみで勤怠管理をおこなうのは現実的ではありません。

法令に則った方法にするならば、タイムカードや勤怠管理システムなどを導入した方がよいでしょう。

6-3.管理に手間がかかる

割増賃金率の変更や社会保険料の法改正があれば、その都度、エクセルの関数やデータを変更しなければいけません。

変更内容を間違えたり、失念したりすれば、残業代未払いなどの問題も発生するため、細心の注意が必要です。
また、データが破損すれば復元は難しく、時間がかかる点もデメリットです。

6-4.法律に即さない管理である可能性

法改正にともない厚生労働省は労働時間を適正に把握するためのガイドラインを平成29年1月20日に公表しました。ガイドラインによると、エクセルなどやスプレッドシートなどの表計算ソフトによる勤怠管理は、自己申告制に該当することが明らかになっています。

そのためやむを得ずに自己申告制で勤怠管理をおこなう場合には、毎日の出退勤時間を使用者が確認・把握する必要が生じることとなりました。また昨今では、管理監督者の労働時間においても把握の対象となるなど、法律変更に伴い管理内容や方法が変更しかねません。

エクセルの勤怠管理表を使う場合は、法改正の度に対応に工数が発生するほか、未対応の場合は法律違反となるため注意が必要です。

7.エクセルの勤怠管理が適しているケース

エクセルを使った勤怠管理にはさまざまな課題があるものの、次のようなケースでは適しているでしょう。

7-1.規模が小さい企業の場合

従業員が20〜30人程度の小さな企業の場合は、エクセルによる勤怠管理でも問題ないでしょう。手作業による入力や別の申請書の作成が必要であったとしても、従業員数が少なければ大きな負担にはなりません。無料のテンプレートなどを活用しつつ、規模が大きくなったときに勤怠管理システムを導入するのもよい方法です。

7-2.エクセルを使いこなせる従業員がいる場合

基本操作や関数をある程度は理解している従業員がいなければ、エクセルによる勤怠管理はできません。テンプレートを活用する場合でも、自社の状況に合わせてカスタマイズする必要はあります。必要に応じてデータや関数を修正する必要があるため、エクセルが不得意な人ばかりの場合は別の方法を検討したほうがよいでしょう。

7-3.時給制のアルバイトスタッフを管理する場合

時給制のアルバイトスタッフを管理する場合も、エクセルを使った勤怠管理が適しているでしょう。時給制の場合は、勤務時間と時給の数値を掛け合わせるだけで計算できるからです。ただし、人数が多くなると管理が難しくなるため注意しましょう。

8.エクセル以外で勤怠管理する方法

以上のように、エクセルで勤怠管理をおこなうなら、打刻はタイムカードでおこない、集計はエクセルでおこなうなど、管理が複雑になる恐れがあります。

エクセルを使わず低コストで勤怠管理する方法としては、紙の勤務簿などもあるものの、管理が煩雑になりやすく現実的な方法とはいえません。

そこで、エクセル以上に勤怠管理業務を効率化したいなら、勤怠管理システムの導入がおすすめです。

8-1.勤怠管理システムを利用するメリット

勤怠管理システムでは、指紋認証のようになりすまし防止機能があるため、客観的で正確な打刻方法として認められます。

パソコンやスマートフォンなどさまざまなデバイスから打刻できるので、テレワークや直行直帰時など、柔軟な働き方にも対応できます。

打刻後の労働時間は自動で深夜や休日のように、時間帯別に集計されます。集計されたデータは給与計算ソフトなどと連携すれば、賃金もすべて自動計算が可能です。

さらに、クラウド型なら法改正時も自動でアップデートされるので、管理者が一つひとつデータを訂正する必要もありません。

導入を検討する際は、利用料のみを確認するのではなく、複数社のサービスから比較するとよいでしょう。また、有料版ではトライアル期間を設けているものも多いため、自社に合ったものか試してから導入するのも方法です。

9.エクセルで勤怠管理をするなら他の方法と併用し客観性を維持しよう

勤怠管理の中でも、打刻は客観的に見て正確であることが求められます。エクセルのみで正確性を確保するのは難しいため、タイムカードなどと併用して使う方がよいでしょう。

もし、複数の方法を併用するのが面倒に感じるなら、勤怠管理システムを活用してみましょう。

客観的で正確な打刻や労働時間の自動集計、外部連携による給与計算など、必要な機能が一通りそろっているため、バックオフィス業務の効率化にも役立ちます。