Excel(エクセル)でシフト表や、労働時間の実績を管理したいと思ったことはないですか？
出勤時間と退勤時間をプルダウンリストから選択するだけで、勤務時間(労働時間)を「8:15」や「8.25」などの表示形式で表示させることができます。
勤務時間を数値で表示させることで、時給を掛けて基本給や残業代などの給料の計算をすることも可能です。
この記事では、「勤務時間を自動で計算する方法」を各ステップに沿って解説します。
勤務時間を自動で計算(完成イメージ)
作成手順を紹介する前に、今回の勤務時間の管理表の完成イメージを紹介します。
今回は、以下の管理表を作成する手順を紹介します。
「出勤時刻」と「退勤時刻」をプルダウンリストから選択すれば、残りの項目はすべて自動で計算される仕組みです。
各項目の説明
勤務日：勤務日を曜日付きで表示します。
出勤時刻/勤務時刻：プルダウンリストから時刻を選択します。15分、30分刻みでも設定可能です。
休憩時間(:形式)：出勤/退勤時刻から、自動で休憩時間を算出します。
勤務時間(:形式)：休憩時間を加味して、勤務時間を「8:45」という表示で自動計算します。
勤務時間(数値)：勤務時間を数値で「8.75」(時間)という表示で表します。時給にかければ、給料が計算できます。
使用するのは、①セルの表示形式の設定、②プルダウンの設定、③関数の挿入の３つだけです。
一度完成させてしまえば、毎月同じフォーマットを使い回しできて便利です。ぜひ作成してみてください。
勤務時間を自動で計算する方法
それでは、休憩時間も加味して勤務時間を自動で計算する方法を解説します。
ステップ１からステップ７に分けて、各見出しで手順を紹介します。長く感じるかもしれませんが、だれでも簡単に理解できるように作成しています。
それでは、どうぞご覧ください。
ステップ１．勤務管理表のフォーマットを作成
まずは、以下の勤怠管理表のフォーマットを作成します。
「勤務日」「出勤時刻」「退勤時刻」「休憩時間(:形式)」「勤務時間(:形式)」「勤務時間(数値)」列を作成し、罫線を引いて、体裁を整えてください。
ステップ２．セルの表示形式を変更
ステップ２では、各列の「セルの表示形式」を変更していきます。
各列ごとに、日付や時刻、時間など異なる表示形式でデータを入力するためです。
手順１．「勤務日」列の表示形式を変更
「2月3日」を「2月3日(火)」というように、日付を曜日付きの表示に変更します。
「勤務日」列を選択し、右クリックメニューから「セルの表示設定」画面を開きます。「表示形式」タブの「ユーザー定義」を選択し、以下のコードを貼り付けしてください。
m"月"d"日"(aaa)
手順２．「勤務時間」列などの表示形式を変更
続いて、「出勤時刻」「退勤時刻」「休憩時間(:形式)」「勤務時間(:形式)」列のセルの書式設定を変更します。
対象のセル範囲を選択し、右クリックメニューから「セルの表示設定」画面を開きます。「表示形式」タブの「時刻」メニューから「13:30」という種類を選択してください。
以上で、ステップ２の作業は完了です。
ステップ３．出勤/退勤時刻にドロップダウンを設定
ステップ３は、「出勤時刻」と「退勤時刻」をドロップダウンリスト(=プルダウンリスト)から選択できるように設定します。
手順１．プルダウンリストを作成
勤務管理表とは異なるワークシート(例：「プルダウンリスト」シート )に、「0:00」「0:15」と入力します。15分刻みではなく、30分刻みで勤怠を管理したい場合は、「0:00」「0:30」と入力してください。
手順２．プルダウンリストを完成
２つのセルを選択してカーソルを右下に合わせると、マウスのポインターが「＋」の形に変わります。下方向にドラッグして23:45(もしくは23:30)までのプルダウンリストを完成させます。
手順３．プルダウンリストを完成
勤務時間管理表のシートに戻ります。「出勤時刻」列の一番上のセル(C3)を選択し、「データ」タブの「データの入力規則」ボタンを押します。
手順４．データの入力規則を設定
「データの入力規則」画面が開きます。「入力値の種類」は「リスト」を選択し、「元の値」は、手順２で作成したドロップダウンリストのセル範囲を選択します。
手順５．ドロップダウンリストを確認
C3セルにドロップダウンリストが設定され、出勤時刻が選択できることを確認します。
手順５．ドロップダウンリストを確認
C3セル以外にもドロップダウンリストを設定するため、C3セルを「Ctrl＋C」キーでコピーして、反映させたいセルに「Ctrl＋V」キーで貼り付けします。
後は、ドロップダウンリストから出勤時刻や退勤時刻を選択すれば、ステップ３の作業は完成です。
ドロップダウンリストを作成する方法を詳しく知りたい方は、以下の記事も参考にしてください。
ステップ４．休憩時間(時刻形式)に計算式を挿入
ステップ４では、「休憩時間」列に数式を挿入して、「出勤/退勤時刻」から休憩時間を自動で計算します。
手順１．休憩時間を算出するIF関数を挿入
休憩時間は、「勤務時間が8時間を超えれば45分休憩、それ以外であれば15分休憩」という設定であれば、以下のIF関数を「休憩時間」列に貼り付けします。
=IF((D3-C3)>8/24,0.75,0.25)/24
休憩時間を８時間越えで「30分」と設定したい場合は、数式内の「0.75」を「0.5」に変更してください。(1時間が数値の１のため)
手順２．IF関数を下のセルにもコピペ
後は、下のセルにもIF関数を挿入すれば完成です。労働時間によって休憩時間は「0:45」か「0:15」が表示されます。
以上で、ステップ４の作業は完了です。
ステップ５．勤務時間(時刻形式)に計算式を挿入
ステップ５では、「勤務時間(時刻形式)」列に数式を挿入して、「出勤/退勤時刻」と「休憩時間」から労働時間を自動で計算します。
手順１．勤務時間を算出する数式を挿入
G3セルに、以下の数式を挿入します。0時を超えて次の日にまたいでも勤務時間が自動で計算できるように、IF関数を含めています。
=D3-C3+IF(D3<C3,1)-F3
手順２．「勤務時間」列の数式が完成
後は、下のセルにも、G3セルの数式をコピペすれば完成です。
以上で、ステップ５の作業は完了です。
ステップ６．勤務時間(数値)に計算式を挿入
ステップ６では、ステップ５で算出した「勤務時間(時刻形式)」を数値に変換します。
例えば、「8:15」という時刻の表示形式を「8.25」と数値に変換します。
手順１．勤務時間を算出する数式を挿入
ステップ５で算出した勤務時間(時刻形式)を数値に直すために、G3セルに24を掛けます。
=G3*24
手順２．「勤務時間」列の数式を完成
下のセルにも、G3セルの数式をコピペします。
以上で、勤務時間を自動で計算することができるフォーマットの完成です。
ステップ７．時給から給与を計算(※作業は任意)
数値で表した勤務時間を使って、基本給や残業代などの給料を計算することができます。
『8時間勤務以内であれば時給×労働時間の給料』、『8時間を超えた場合は、残業時間×時給×1.3倍の残業代』が支払われる条件で、給料を自動で計算します。
給与計算のポイント
１．8時間勤務以内(基本給) ⇒ 労働時間×時給
２．8時間勤務超えた分(残業代) ⇒ 労働時間×時給×1.3
手順１．基本給を計算
勤務時間が8時間以内であれば『労働時間×基本給』、8時間を超える場合は『8.0時間×基本給』となるようにIIF関数を挿入します。
=IF(H3<=8,I3*H3,8*I3)
手順２．残業代を計算
勤務時間が8時間以内であれば『残業代は0円』、8時間を超える場合は『残業時間×時給×1.3倍』となるようにIF関数を挿入します。
=IF(H3<=8,0,(H3-8)*I3*1.3)
IF関数は条件によって値を分岐させる関数です。IF関数の使い方が分からない方は、下の記事を参考にしてください。
(おまけ)時刻・時間の計算方法
今回は、出勤時刻と退勤時刻から勤務時間や給与を計算する方法を紹介しました。
作成のポイントは、セルの表示形式を時刻に変更することと、表示形式に合わせた計算式を挿入することです。
以下の記事で、時刻や時間の計算方法を詳しく紹介しているので、合わせて参考にしてください。