Excel（エクセル）で勤怠管理をする方法！必要な関数、メリットや注意点を解説

Excel（エクセル）による勤怠管理は、初期費用の安さや豊富なテンプレート、カスタマイズのしやすさなどの理由から、多くの中小企業に選ばれています。

最近では働き方改革などの影響によりワークスタイルが変化し、勤怠管理システムの見直しが求められる企業も増えてきました。

今回は勤怠管理システムの導入や、勤怠管理の効率化をお考えの人事や労務のご担当者に向けて、身近なオフィスソフトである「Excel」を活用して勤怠管理をスムーズに行う方法を具体的に解説します。

Excel（エクセル）で勤怠管理を行うメリット

Excelで勤怠管理を行うメリットは、初期投資の低さやユーザー数の多さ、カスタマイズ性の高さにあります。

導入や運用のコストが低い

Excelで勤怠管理を行う場合、すでに会社のPCにExcelがインストールされていれば、追加費用不要ですぐに運用を開始できます。

そのため初期コストのかかる勤怠システムと比較すると、Excelはとても安価で挑戦しやすい勤怠管理方法です。

Excelを使える人が多い

Excelは多くの企業が利用しているソフトなので、すでに多くの社員が操作方法を知っているという点がメリットといえます。特別な指導が必要ないため、勤怠管理をする側にもされる側にも少ない負担で導入可能です。

項目やシートなどが自由にカスタマイズできる

Excelを使えば、関数やVBAを使用して自由に勤怠管理表を作成できます。
部署やチームごとにシートを分けて管理したり、会社の勤怠規則に応じて自由に項目を作成できるため、慣れれば、簡単に使いやすい勤怠管理表を作成することができるでしょう。

テンプレートを使用すれば簡単に導入できる

インターネットで検索すれば、簡単にExcelの勤怠管理表のテンプレートを入手することができます。
そのためゼロから開発する必要がなく、テンプレートの計算式や記録項目を少しカスタマイズすれば自社専用の勤怠管理表を作成可能です。

勤怠管理を効率化！Excelテンプレート

どの企業でも必要な勤怠管理。様々な働き方に対応できるExcelテンプレートがあれば、より楽に正確に勤怠管理をすることができます。

ダウンロードはこちら

勤怠管理の3ステップ

Excelで勤怠管理表の作成を行う前に、まずは勤怠管理における「記録、集計、給与計算」の3ステップを理解しておきましょう。
Excelを無駄なく機能的に設計するためにも、全体の勤怠管理の流れを把握しておくことが重要です。

STEP1：勤怠状況を記録する

ステップ1は「従業員の勤怠記録をとること」です。具体的には出勤・退勤時刻、残業、労働時間、有給取得日数などの勤怠状況を正確に記録します。
記録には「Excelの勤怠管理表に従業員が直接入力」する方法や「タイムカードを管理者側が転記する」などのパターンが考えられます。

なお、勤怠記録の付け方に関しては厚生労働省が策定したガイドラインがあるため、自社の運用方法の確認や従業員への周知は怠らないようにしましょう。

※厚生労働省のガイドラインに関しては、本記事後半の「Excelで勤怠管理をする際に注意するべき点」で詳しく解説しています。

STEP2：勤務時間の集計

ステップ2は「勤務時間の集計」です。ステップ1で記録した情報をもとに、従業員ごとの勤務日数や欠勤日数、遅刻早退の有無、残業時間数や深夜労働時間数などを集計します。
集計項目や計算式は従業員の雇用形態や勤務パターンなどで異なるため、自社に合った集計方法で行うことが大切です。

STEP3：集計したデータから給与計算

ステップ3は「集計した勤怠データに基づいた給与計算」です。
時給や月給計算以外にも、課税支給額、社会保険料等の控除額、源泉所得税、通勤等の手当などを算出しましょう。

Excel（エクセル）で勤怠管理表を作成する方法

実際にExcelで勤怠管理テンプレートを作成してみましょう。
ここでは例として、2023年6月分の勤怠管理テンプレートを作っていきます。

必要項目の洗い出し

まずは、必要項目を洗い出しましょう。
勤怠管理テンプレートに必要な基本的な項目は以下の通りです。

基本的な項目

• 年月日
• 曜日
• 営業日／休業日
• 開始時刻
• 終了時刻
• 休憩
• 1日の稼働時間
• 法定外稼働時間
• 深夜残業時間
• 稼働時間／法定外労働時間／深夜残業時間の合計
• 備考

あると良いもの

• 勤務時間（例：9:00～18:00）
• 勤怠（有給休暇／フレックスタイム／遅刻／早退など）
• 有給休暇消化日数
• 遅刻／早退回数

今回は、勤務時間が午前9時から午後6時までの企業を想定して記事を作成しています。また、祝日なども考慮していません。

貴社にあった勤怠管理作成等に関するご相談は、こちらよりお気軽にご連絡ください。

勤怠管理テンプレート作成手順

ここからは、以下よりダウンロードいただける、「勤怠管理表Excelテンプレート」の作成方法を説明します。

勤怠管理を効率化！Excelテンプレート

どの企業でも必要な勤怠管理。様々な働き方に対応できるExcelテンプレートがあれば、より楽に正確に勤怠管理をすることができます。

ダウンロードはこちら

STEP1：項目名を入力する

（1）洗い出した項目名をセルに入力
今回は、以下のように項目名を入力します。

（2）年月の表示形式を「2023年6月」と設定
【F3】セルを選択した状態で右クリック→《セルの書式設定》→《表示形式》→《ユーザー定義》で種類を「0"年"」と書き換え、《OK》を押します。同様の手順で【G3】セルの種類を「0"月"」と書き換えます。

以下のように「2023年6月」と表示されました。

表示形式のユーザー定義については、こちらの記事もご覧ください。
表示形式のユーザー定義ってどう使うの？日付や金額、カンマをわかりやすく表示！データ活用の基本

（3）【C6】セルに始業時刻、【E6】セルに終業時刻を入力
今回E列の「勤怠」については、手動で勤怠（遅刻や早退など）を入力することを想定しているため計算式は使用しません。
【C6】セルに「9:00」【E6】セルに「18:00」と入力します。勤務表の大枠が完成しました。

STEP2：B列に日付を表示させる

（1）【B9】セルに「=DATE(F3,G3,1)」と入力
【B9】セルに「2023/6/1」と表示されます。

（2）【B10】セルに「=B9+1」と入力
【B10】セルに「2023/6/2」と表示されます。

（3）「年/月/日」の表示を「日」のみにする
【B9】セルと【B10】セルを選択した状態で右クリック→《セルの書式設定》→《表示形式》→《ユーザー定義》を開き、種類を「d」と書き換えたら《OK》を押します。

【B10】セルを選択し直し、数式を【B38】セルまでコピーすれば日付の列が完成です。

日付の表示については、こちらの記事もご覧ください。
DATE関数で作る万年カレンダー！自作のカレンダーで効率的にスケジュール管理をしよう！

STEP3：TEXT関数で曜日を表示させる

勤怠管理を行う際には、営業日か休業日かを判断する必要があるため、日付だけでなく曜日も意識する必要があります。今回はTEXT関数を使って曜日を表示します。

TEXT関数の基本構文は以下の通りです。

（1）【C9】に「=TEXT(B9,"aaa")」と入力

【C9】セルに「木」と表示されます。【C9】セルを選択し、数式を【C38】セルまでコピーすれば曜日の列が完成します。

曜日の入力についてはこちらの記事もご覧ください。
【初心者向け】関数を使って日付や曜日の入力を効率的に！

STEP4：IF関数とOR関数を組み合わせて営業日か休業日かを判断する

ここでは、IF関数とOR関数を組み合わせて、【C列】が土か日だったら「休業日」、それ以外であれば「営業日」と表示されるように数式を作成します。

IF関数とOR関数の基本構文は下記の通りです。

（1）【D9】セルに「=IF(OR(C9="土",C9="日"),"休業日","営業日")」と入力

（2）【C9】セルと【D9】セルを選択し、数式を38行目までコピー

土曜日と日曜日に「休業日」が反映されます。

STEP5：休業日の行の色を変える

休業日であることを分かりやすくするために、Ｄ列が休業日であれば行ごとグレーになるように設定しましょう。

（1）書式設定を反映する範囲を選択
セル範囲【B9:L38】を選択します。

（2）条件付き書式を選択
《ホーム》タブから《スタイル》→《条件付き書式》→《新しいルール》をクリックします。

《ルールの種類を選択してください》から《数式を使用して、書式設定するセルを決定》を選択し、《次の数式を満たす場合に値を書式設定》に「=$D9="休業日"」と入力し、《書式》をクリックします。

続いて表示される画面で、タブ《塗りつぶし》をクリックし、塗りつぶしたい色を選択したら《OK》をクリックします。ここでは、例として「白、背景1、黒＋基本色15%」を選択しています。

プレビューが反映されていることを確認し、《OK》をクリックします。

休業日を行ごと指定した色に変更できました。

＜参考＞
以下のように、WEEKDAY関数を利用して休業日を行ごとグレーに変えることも可能です。

WEEKDAY関数の基本構文は下記の通りです。

B列のDATE関数の結果を利用します。「週の基準」は2を利用します。
関数の戻り値は以下の曜日として対応しています。

（1）条件付き書式で数式「=WEEKDAY($B9,2)>=6」を設定
【B9】セルから【L38】セルを選択した状態で右クリック→《条件付き書式》→《新しいルール》から、《数式を使用して、書式設定をするセルを設定》をクリックします。

（2）《次の値を満たす場合に値を書式設定》に、数式「=WEEKDAY($B9,2)>=6」を入力
今回の「週の基準」では土曜日と日曜日であるため6以上の値が休業日となります。

WEEKDAY関数を利用してセルに色を付ける方法については、下記の記事もご覧ください。
DATE関数で作る万年カレンダー！自作のカレンダーで効率的にスケジュール管理をしよう！

STEP6： 1日当たりの稼働時間を算出する

1日当たりの稼働時間は「終了時刻-開始時刻-休憩」で算出します。
【I9】セルに「=IF(OR(F9="",G9=""),"",G9-F9-H9)」と入力
IF関数とOR関数を組み合わせて、開始・終了のいずれかが空欄なら稼働時間が空欄になるよう設定します。

ここでは、仮に【F9】セルの開始時刻に「8:47」、【G9】セルの終了時刻に「22:18」、【H9】セルの休憩に「1:00」と入力しています。

STEP7：TIME関数で法定外労働の時間を算出する

勤怠管理には法定外労働の管理も必要です。法定労働時間は8時間ですので、稼働時間と8時間の差が法定外労働となります。ここから、法定外労働の時間の算出を行いましょう。

時間の計算にはTIME関数を使用します。
TIME関数の基本構文は以下の通りです。

法定労働時間をTIME関数で表すと「=TIME(8,0,0)」となります。

（1）【J9】セルに「=IFERROR(IF(I9-TIME(8,0,0)>0,I9-TIME(8,0,0),""),"")」と入力
※I列に値が反映されていない場合は計算ができないため#VALUE!エラーとなります。エラー回避のためIFERROR関数を使用します。

IFERROR関数については、こちらの記事をご覧ください。
Excel（エクセル）エラー表示の原因と対処法を解説！IFERROR関数で置き換える方法

（2）【K9】セルに「=IFERROR(IF(G9<=TIME(22,0,0),"",(G9-TIME(22,0,0))),"")」と入力
22時以降の深夜時間の労働時間も算出することができます。

ここまでできたら、【I9】セルから【K9】セルまでを選択し、数式を38行までコピーしましょう。

（3）表示形式の変更
正しく時間が表示されない場合は、【I9】セルから【K9】セルを選択した状態で右クリック→《セルの書式設定》→《表示形式》→《ユーザー定義》を開く→種類を「[h]:mm」と書き換え、《OK》を押す

STEP9：労働時間の合計を算出する

39行目に1か月の総稼働時間、法定外労働時間、深夜時間帯の労働時間の合計を計算します。ここからの画像は、開始時刻、終了時刻、休憩時間が入力されたものを使用しています。

（1）【I39】セルを選択した状態で、《数式》タブから《オートSUM》をクリック

（2）【I9】セルから【I38】セルを範囲選択する

（3）【I39】セルの数式を【J39】セルと【K39】セルにコピー

ここで注目したいのが【I39】セルの値です。23時間より大きい値は24で除算され、余剰が時となります。そのため【I39】セルの値が「6:13」となっています。23より大きい値であっても除算されないようにするため、表示形式を変更する必要があります。

【I39】セルから【K39】セルを選択した状態で右クリック→《セルの書式設定》→《表示形式》→《ユーザー定義》を開くと進み、種類を「[h]:mm」と書き換え、《OK》を押してください。

1か月の総稼働時間、法定外労働時間、深夜時間帯の労働時間の合計を正しく表示できました。

STEP9：休暇の取得日数を算出する

ここではE列にその日の勤怠状況を入力し、下図のように【J6】セルにその月の休暇取得日数を反映させます。今回は計算方法の一例をご紹介します。

（1）E列に勤怠（有給休暇／午前有給／午後有給／遅刻／早退／フレックスのいずれか）を入力
通常の勤務であれば空欄にします。
（2）【N8】セルに「休暇計算」を追加

（3）【N9】セルに「=IF(OR(E9="午前有給",E9="午後有給"),0.5,IF(E9="有給休暇",1,0))」と入力し、数式を【N38】セルまでコピー
午前有給・午後有給＝0.5日、有給休暇＝1日として扱うという意味の数式です。

値が反映されました。

（4）【J6】セルに「=SUM(N9:N38)」と入力
休暇取得日数の合計を算出します。

休暇取得日数が算出できました。

STEP10：遅刻、早退の回数をカウントする

遅刻、早退の回数はCOUNTIF関数で算出できます。

（1）【K6】セルに「=COUNTIF(E9:E38,"遅刻")」と入力

（2）【L6】セルに「=COUNTIF(E9:E38,"早退")」と入力

遅刻、早退の回数を算出できました。

COUNTIF関数については、こちらの記事をご覧ください。
知っておきたい！COUNTIF関数を活用した効率的なデータ分析

Excel（エクセル）で勤怠管理をする際に注意すべき点

Excelでは、関数やマクロで勤怠管理を一部自動化できるため、とても便利です。
しかしExcelで勤怠管理には、特有のリスクもあります。

ここではExcelで勤怠管理をする際の注意点を4つ解説しますので、導入前によく確認しておきましょう。

入力や転記・計算式のミスにより、誤った集計結果になってしまう

Excelで勤怠管理表を自作する場合は、ひとつミスが発生すると給与計算がすべて狂ってしまう危険性があります。

Excelの勤怠管理表では打刻や入力を人の手で行うため、どうしても入力忘れや転記ミスが発生しがちです。また、シフト制やフレックスタイム制などを導入している企業は、従業員ごとに出退勤時間が異なるため、Excelの計算式が複雑になります。

そのため関数のミスがないようにダブルチェック制度を取り入れる、従業員が誤って計算式を消さないようにセルにロックをかけるなど、事前に対策をするようにしましょう。

勤務実績の改ざんリスクがある

Excelの勤怠管理表は誰でも簡単に入力できる一方、タイムカードや勤怠管理システムと比較して勤怠情報を改ざんされるリスクが高いことを覚えておきましょう。

操作をすべて従業員任せにしてしまうと、サービス残業や遅刻をごまかすための不正申告などが発生しやすくなるため、あらかじめ防止策を検討しておくと安心です。

法改正に応じて、Excelの数式を見直す必要がある

Excelの関数で給与計算を自動化している場合、法改正に応じて給与の計算式を修正する必要があります。給与計算やコンプライアンスに問題が発生しないよう、Excelの勤怠管理表は適切にアップデートすることが大切です。

例えば勤怠管理に関わる法律は年に数回改定が行われるため、深夜残業や休日出勤の賃金割増率の改定、各種税率や保険料の変更がないかなど、常に情報収集をするようにしましょう。

出退勤の入力は、自己申告制の勤怠管理だけでは法的に認められない

厚生労働省は平成29年1月に「労働時間の適正な把握のために使用者が講ずべき措置に関するガイドライン」を策定しました。

このガイドラインの中では勤怠管理の原則として「使用者が自ら現認することにより確認すること」と「タイムカード、ICカード、パソコンの使用時間の記録等の客観的な記録を基礎として確認し適正に記録すること」の2つを挙げています。

Excelに従業員が直接入力する手法は「自己申告制」にあたるため、勤怠管理の客観性を証明することが難しいです。Excelでの勤怠管理方法は会社によって異なるため、自社がどのような対策を講じるべきかはガイドラインを熟読した上でよく検討するようにしましょう。

出典：厚生労働省「労働時間の適正な把握 のために使用者が講ずべき措置に関するガイドライン」

勤怠管理表作成のためのExcel（エクセル）テクニックを身につけるには

Excelでの勤怠管理表はテンプレートも多数公開されており、関数やマクロの知識があれば、コストをかけずに自由にカスタマイズできるという魅力があります。勤怠管理は同じ作業の繰り返しが多いので、VBAを使ってマクロを作成し作業を自動化していくことも可能です。

しかし、自社の就業規則や法律に合わせてスムーズに勤怠管理表の調整を行うには、基礎知識以上のマクロやVBAなどのExcelテクニックが必要になります。

これからExcelによる勤怠管理を検討している方や、勤怠管理を効率化したい方、Excelをもっと業務に活かしたいとお考えの方は、一度EXCEL女子のExcel研修をご活用ください。

勤怠管理表作成や給与自動計算システムはもちろん、通常業務にも役立つVBAや関数などの知識を実践的に身につけることができます。

Excel研修の詳細にご興味がございましたら、下記のお問い合わせフォームよりお気軽にご連絡ください。