Excel勤怠表の作り方

こんにちは、朱夏です。

今回は、メンバー一人一人が入力する個人用のファイルを作っていきます。
個人用の入力ファイルに求める機能は下記の予定です。

・ベースの入力フォーマットは崩さない
・勤怠表シートをつける
・１日１シートで工数を入力
・自動入力、自動計算を導入
・意味のある配色

それでは、行ってみましょう。

勤怠表シート

まずは、別々で入力していた勤怠表を統合します。
見た目はこんな感じです。

よくある横型の勤怠入力形式ですね。
詰め込んだ機能を紹介していきます。

前提条件

そこまで大げさなものではないのですが、作成の前提として、ファイル名を決めておきます。

以下の様なファイル名で作成します。
ファイル名：【テンプレート】個人用工数入力ファイル(名前)YYYYMM.xlsx

実際に使用する際は、テンプレートをコピーして下記例の様にリネームして使います。
　例：個人用工数入力ファイル(朱夏太郎)202306

表題[勤怠　YYYY年MM月分]

最初に実装した機能は、勤怠表シートの表題部分の自動入力です。
ここは、ファイル名にあるYYYYMMの部分を取得して、表題として表示させるものです。
勤怠表のC1:F1の部分に表示されています。

入力している関数は以下の様なものです。
分解してみていきましょう。

=VALUE(TEXT(MID(CELL(“filename”,$A$1),FIND(“.”,CELL(“filename”,$A$1))-6,6)&”01″,”0000!/00!/00″))

VALUE関数

VALUE関数は入力されている文字列として入力されている数字を数値に変換する関数です。
書き方は、＝VALUE(文字列)となります。
今回の場合、VALUE(～)内の全てが文字列を表していることになります。

TEXT関数

TEXT関数は数値を文字列へ変換と、書式の変更を行う関数です。
書き方は、＝TEXT(値,表示形式)となります。
今回の記述の中では、“0000!/00!/00”の部分が表示形式の部分になります。

MID関数

MID関数は、対象の文字列の中から、開始位置と文字数で指定した部分だけ表示させる関数です。
書き方は、＝MID(文字列,開始位置,文字数)となります。

CELL関数

CELL関数は、指定したセルに関する様々な情報を取得することができる関数です。
書き方は、=CELL(検査の種類,[参照])となります。
今回は”filename”（フルパス+ファイル名+シート名）の情報を取得しています。
参照は任意として扱われておりますが、指定しておかないと読み込みごとに変化してしまうので、任意で$A$1を指定しています。

FIND関数

FIND関数は、指定した文字列が、他の文字列の中から最初に出てきた場所の文字数を返す関数です。
書き方は、=FIND(検索文字列,対象,[開始位置])となります。
今回は、ファイル名の拡張子に使われる”.”を検索文字列に指定しました。

全体の説明

CELL関数でファイル名（フルパス）を取得して、MID関数でファイル名の中からYYYYMMの部分を取得します。
FIND関数を使ってファイル名の拡張子前のドット”.”から-6文字目を開始位置として6文字分を取得、後ろに”01″を付与したものを取得します。
これでYYYYMM01（例では20230601）がTEXT関数で文字列として取得できました。

ここで、TEXT関数のもう一つの機能「表示形式」を反映させています。
“0000!/00!/00″は、YYYY/MM/01というように指定位置にスラッシュを入れる表示形式です。
VALUE関数で作成したテキストを数値に変換したら、対象月の1日の日付を取得できました。

最後に、セルの書式から表示形式を変更して整えます。
ユーザー定義で「yyyy”年”m”月分”」を作成して適用します。

これでシートのタイトル部分が完成です。

勤怠表

では、このシートの本体である勤怠表の部分を作っていきます。

日付

まずは日付部分（2行目）から。
日付部分の書式設定はユーザー定義でd（日付のみ）を指定します。

C2には最初に設定したC1を参照する関数を入力しています。

こんな感じ。
お隣のD2にはC1に+1した日付を入力します。

で、C2セルの右下を引っ張って日付を31日目のAG2まで引っ張ります。

曜日

曜日（3行目）は、2行目の日付を参照して、TEXT関数で表示形式を変更して曜日に変換します。

こんな感じ。
因みに、曜日の表示形式はいくつかあります。

=TEXT(2023/7/1,”aaa”)　⇒　月
=TEXT(2023/7/1,”aaaa”)　⇒　月曜日
=TEXT(2023/7/1,”ddd”)　⇒　Mon
=TEXT(2023/7/1,”dddd”)　⇒　Monday

あとは日付と同様右に引っ張って反映させたら完成。

出勤、退勤

出勤（4行目）と退勤（5行目）の行は毎日の出勤時間、退勤時間を入力してもらう欄なので枠だけ作っておきます。

勤務時間

勤務時間の行は、退勤時間から出勤時間を引いた時間が表示されます。
エラー表示回避のために、4行目、5行目のセルが空欄だったら何も表示しない様に関数を組みました。

関数は以下を使用。

=IFもし(OR(C4=””,C5=””C4が空欄かC5が空欄だったら),“”空欄,TIME(HOUR(C5)-1,MINUTE(C5),SECOND(C5))そうでなければC5の「時間：分：秒」ひく–TIME(HOUR(C4),MINUTE(C4),SECOND(C4))C4の「時間：分：秒」したものを表示)

これも一か月分右へ引っ張ります。

残業

残業（7行目）は、1日の勤務時間が8時間を超えたら残業として加算されていきます。
残業時間が0の時は空欄になります。

関数にするとこんな感じ。

=IF(C6=””,””,IF(C6>=TIME(8,0,0),C6-TIME(8,0,0),TIME(0,0,0)))

同じく一か月分右へ引っ張っておきます。

合計欄

一か月分の勤務時間と残業時間の合計を表示する欄を作りました。
この部分はSUM関数で足し算しているだけなので割愛。

条件付き書式の設定

条件付き書式で設定したものは以下の4つです。

土曜日は水色にする
=C$3=”土”
日曜日はピンク色にする
=C$3=”日”
来月の日付はグレーアウト
=MONTH($C$1)<MONTH(AE$2)
勤怠の残業時間と工数の残業時間が一致しない場合は赤色にする
=C7<>INDIRECT(“工数シート”&TEXT(C2,”yyyymmdd”)&”!D24″)

設定するとこんな感じになります。

新しい関数を紹介しておきます。

INDIRECT関数

INDIRECT関数は、参照先のセルを指定するための関数です。
条件によって参照先が変動するときなどによく使う関数です。

今回は、対象列の日付を元に、参照先の工数入力用のシート名「工数シートYYYYMMDDこの部分」を指定するのに使用しています。

=C7C7と<>一致しないINDIREC参照先はT(“工数シート工数管理シートの後ろに“&TEXT(C2,”yyyymmdd”日付（C2）をYYYYMMDD形式でつけて)&“!D24”後ろに!D24をつける)

最後に

見出し部分や自動で入力される部分に色を付けて、入力が必要な部分と差別化します。
これで、白色の部分に出勤、退勤だけ入れれば勤務時間、残業時間が自動で表示され、月の合計も自動で出力されます。
ファイル名の入力ルールさえ入力すれば、表題、日付、曜日は自動で月ごとに表示してくれます。

まとめ

勤怠表の部分が完成しました。
色々組み合わせていますが、Excelの中では割と基本的な機能だけで作成できたのではないでしょうか。

次回は、実際の作業工数を入力するためのシートを作成していきたいと思います。

それでは、次回もよろしくお願いします。