Excelで勤務時間表をつくろう(2) 作成編

このページでは

前のページでまとめた仕様にもとづき、実際にExcelを使って勤務時間表を作成します。
なお、利用した関数については、次回に説明します。

ポイント

仕様を実現するための方法は、大抵の場合、複数考えられます。
このサンプルは、あくまでも一例です。
もっとうまい方法もありそうです。
カスタマイズする際に、いろいろ試してみましょう。

作成

ヘッダー部分

2016101702

年月

セル:B2、B3
入力項目、薄い黄色に着色。
西暦年と月を入力します。

基本時間・残業上限

セル:F2、F3
入力項目、薄い青色に着色。
基本時間と残業時間の上限を入力します。

合計時間

セル:I2
表示項目、グレーに着色。
作業時間の合計欄(セル:H37)を表示します。
セルの設定値:=H37

残業時間

セル:I3
表示項目、グレーに着色。
残業時間(セル:J37)と休日出勤(セル:K37)を合算して表示します。
セルの設定値:=J37+K37

セル:J3
表示項目、グレーに着色。
残業時間(セル:I3)が残業上限(セル:F3)より大きい値の場合、「残業時間の上限を越えました。」と表示します。
セルの設定値:=IF(I3>F3,”残業時間の上限を越えました。”,””)

明細部分

2016101703

2016101704

セル:B6~B36
表示項目、グレーに着色。
年と月に応じた日を表示します。
セルの設定値:1~28日(セル:B6~B33)は固定値を設定、29日~31日は以下の関数。
B34: =IF(DAY(DATE($C$2,$C$3,29))=29,29,””)
B35: =IF(DAY(DATE($C$2,$C$3,30))=30,30,””)
B36: =IF(DAY(DATE($C$2,$C$3,31))=31,31,””)

補足)
日は大の月、小の月があるため、月末日を意識した表示が必要です。
ここでは、1日~28日までは固定で表示し、29日~31日を関数で表示するようにします。
DATE関数でシリアル値を取得し、DAY関数でそのシリアル値の示す日を求めます。
存在する日ならば、日が取得出来ますが、存在しない日の場合、日が取得出来ません。
日が取得出来ない場合は、その日は存在しないので、空白にします。

曜日

セル:C6~C36
表示項目、グレーに着色。
年(C2)、月(C3)、日(B6~B36)に応じた曜日を表示します。
セルの設定値:=TEXT($C$2 & “/” & $C$3 & “/” & B6,”aaa”)
※B6の部分は、行に応じた値になります。(B6~B36)

補足)
TEXT関数の書式設定機能で、日付の値を曜日に変換しています。

セル:D6~D36
表示項目、薄い青色に着色。
曜日(C6~C36)が”土”または”日”の場合、休に1を表示します。
セルの設定値:=IF(OR(C6=”土”,C6=”日”),1,””)
※C6の部分は、行に応じた値になります。(C6~C36)

補足)
IF関数とOR関数で曜日の値が”土”または”日”を判定しています。

出勤、退勤、休憩

セル:E6~E36、F6~F36、G6~G36
入力項目、薄い黄色に着色。
出勤時刻、退勤時刻、休憩を入力します。
出勤していない日は、空白とします。

作業時間

セル:H6~H36
表示項目、グレーに着色。
出勤した日のみ、出勤時刻(E6~E36)と退勤時刻(F6~F36)の差分を算出した後、休憩時間(G6~G36)を引いて、作業時間とします。
セルの設定値:=IF(AND(E6<>“”,F6<>“”),F6-E6-G6,0)
※E6,F6,G6の部分は、行に応じた値になります。(E6~E36、F6~F36、G6~G36)

補足)
IF関数で出勤した日かどうか(出勤時刻も退勤時刻も空白かどうか)判定しています。

タイトルとURLをコピーしました