EXCELで出勤簿を作成する―不連続な勤務時間を記録する

出勤簿の通常のフォーマットでは一日につき一行しか欄がないため休憩を挟むなど就業時間が不連続な場合は記入が面倒になります。別に時間を記録しておいて終了後に合計してから記入しなければなりません。

EXCELであればこの問題を解決することができます。開始、終了時刻を入力するだけで就業時間の合計を自動的に行い出勤簿にまとめることができます。

次のような出勤簿をEXCELで作成したいと思います。完成したEXCELデータはこちらからダウンロードできます。

□タイムカード用シート

□出勤簿シート

・列は「日付」、「就業時間」、「就業時間範囲」がある

・就業時間は日毎の合計をタイムカード用シートから計算する。配列関数を利用する

・就業時間範囲はタイムカード用シートから「00:00-00:00，00:00-00:00…」の形で一行にする

使い方

①タイムカードシートに日付、開始時刻、終了時刻を入力する。

※［Ctrl］＋「：」で今日の日付、［Ctrl］＋「；」で現在の時刻を挿入できる。

②15分単位に変換された開始時刻と終了時刻が表示される

③開始時刻と終了時刻から就業時間が計算される

④出勤簿シートに日毎の合計就業時間が計算される

⑤最終行に月間の合計就業時間が計算される

各列の設定は次の通り。

〇タイムカードシート　A列「日付」

セルの表示形式はカスタムで yyyy”年”m”月”d”日”(aaa) とする。yyyyが4桁の西暦、mがゼロ埋めなしの月、ｄがゼロ埋めなしの日、aaaが漢字1字表記の曜日を表す。

〇タイムカードシート　B列　および　C列

入力用に空けておく。セルの表示形式は「時刻」に設定する。

日を跨ぐとそのままでは就業時間（G列）が正しく計算されないので例えばAM1時なら25時に書き換える。

〇タイムカードシート　D列「開始時刻（15分毎）」

=CEILING(B4,"0:15")

CEILING関数で15分単位の時刻に切り上げる。セルの表示形式は「時刻」に設定する。

〇タイムカードシート　E列「終了時刻（15分毎）」

=FLOOR(C2,"0:15")

FLOOR関数で15分単位の時刻に切り下げる。セルの表示形式は「時刻」に設定する。

〇タイムカードシート　F列「就業時間範囲」

=TEXT(D2,"hh:mm")&"-"&TEXT(E2,"hh:mm")

開始時刻と終了時刻をテキスト化して00:00-00:00の形式で表示する。セルの表示形式は「文字列」に設定する。

〇タイムカードシート　G列「就業時間」

=E2-D2

終了時刻と開始時刻の差を00:00の形式で時間として表示する。セルの表示形式を「時刻」にする。

〇タイムカードシート　H列「就業時間（h）」

=G2*24

G列の就業時間に24をかけることで数値に変換する。セルの表示形式を「数値」にする。例えばG列が00:15のとき0.25時間、00:30のとき0.5時間、00:45のとき0.75時間となる。

〇出勤簿シート　A列　「日付」

セルの表示形式をカスタムで d”日”(aaa) とする。年月が左上に表示されているので日、曜日のみとする。ただし表示形式に関係なく、実際のデータはタイムカードシートの日付列と同じ形式にする。比較を行うため、対応する日付が条件判定で等しいとみなされる必要がある。

〇出勤簿シート　C列　「就業時間」

=SUM(IF(タイムカード!A:A=A6,タイムカード!H:H,""))

A列と等しい日付をタイムカードシートから探して就業時間を合計する。

セルの確定の際、［Ctrl］＋［Shift］＋［Enter］を入力する。それによって式が配列数式となりIFの戻り値が範囲になる。IF関数で求めた範囲をSUM関数で合計する。

IF(条件, 真の時の戻り値, 偽の時の戻り値）

〇出勤簿シート　B列　「就業時間」

=TEXTJOIN(", ",TRUE,IF(タイムカード!A:A=A6,タイムカード!F:F,""))

セルの確定の際、［Ctrl］＋［Shift］＋［Enter］を入力する。

C列と同様に配列関数を使用してIF関数で範囲を求める。TEXTJOIN関数でその範囲を文字として結合して一行のテキストにする。

以上でEXCELの出勤簿ができました。