エクセル小僧：1か月の作業時間を集計する [コンピューター]
昔は、Web画面に出社時刻と退社時刻を入力すれば、自動計算してくれたのですが。。。
そのシステムが使えなくなってしまったため、エクセルで計算することにしました。
・A2 - A32セル：その月の日付
A2セルに、「2013/5/1」の形式で、1日の日付を入力します。
A3セルに、「=A2+1」と入力します。
A3セルをコピーして、A4 - A32セルに貼り付けます。
A32セルが「=A31+1」となればOKです。
A2 - A32セルを選択して、「Ctrl+1」でセルの書式設定を開きます。
「日」のみ表示したいので、「ユーザー定義」を選択し、種類の欄に「d」と入力します。
A2 - A32セルに1, 2 - 31と日付が表示されれば、OKです。
・B2 - B32セル：曜日
B2セルに「=A2」と入力します。
「Ctrl+1」でセルの書式設定を開きます。
「ユーザー定義」を選択し、種類の欄に「aaa」と入力します。
B2セルをコピーして、B3 - B4セルに貼り付けます。
曜日が正しく表示されればOKです。
B2 - B32セルを選択して、「条件付き書式」を開きます。
「数式を使用して、書式設定するセルを決定」を選択。
書式欄に「=TEXT(A2,"aaa")="日"」と入力。
書式として、「塗りつぶし」の「ピンク」を選択。
「新規ルール」をクリック。
「数式を使用して、書式設定するセルを決定」を選択。
書式欄に「=TEXT(A2,"aaa")="土"」と入力。
書式として、「塗りつぶし」の「水色」を選択。
土曜日が水色、日曜日がピンクで塗りつぶされればOKです。
・C2 - C32セル：出社時刻
C2 - C32セルを選択して、「Ctrl+1」でセルの書式設定を開きます。
「ユーザー定義」を選択し、種類の欄に「h:mm」と入力します。
・D2 - D32セル：退社時刻
D2 - D32セルを選択して、「Ctrl+1」でセルの書式設定を開きます。
「ユーザー定義」を選択し、種類の欄に「h:mm」と入力します。
・E2 - E32セル：１日の作業時間[時間：分]
E2 - E32セルを選択して、「Ctrl+1」でセルの書式設定を開きます。
「ユーザー定義」を選択し、種類の欄に「h:mm」と入力します。
出社時刻「8:30」、退社時刻「17:15」で「7:45」と表示されればOKです。
と言いたいところですが、１日の作業時間はマクロで計算させるため、
確認はマクロ入力後にします。ちなみに、休憩時間は 12:00-13:00,
17:15-17:30, 18:30-19:00, 21:30-22:00です。
・F2 - F32セル：１日の作業時間[時間]
F2セルを選択し、「=E2*24」と入力します。
F2セルをコピーして、F3 - F32セルに貼り付けます。
F2 - F32セルを選択して、「Ctrl+1」でセルの書式設定を開きます。
「ユーザー定義」を選択し、種類の欄に「0.00」と入力します。
エクセルでは、24で割った値で時間計算しているらしく、24をかけて
標準の書式にしてやると、時間を10進表示できるようです。例えば、45分は、0.75時間になります。
・E33セル：１か月の作業時間[時間：分]
E33セルを選択し、「=SUM(E2:E32)」と入力します。
E33セルを選択して、「Ctrl+1」でセルの書式設定を開きます。
「ユーザー定義」を選択し、種類の欄に「[h]:mm」と入力します。
時間の書式を"[h]"としているのは、合計が24時間を超えてしまうからです。
・F33セル：１か月の作業時間[時間]
F32セルをコピーして、F33セルに貼り付けます。
次に、出社時刻(C列)、退社時刻(D列)のセルに変化があったら、１日の作業時間(E列)を計算するマクロを作ります。
「Alt+F11」でVBAを開きます。
該当シートのところに、次のように記述します。
そのシステムが使えなくなってしまったため、エクセルで計算することにしました。
・A2 - A32セル：その月の日付
A2セルに、「2013/5/1」の形式で、1日の日付を入力します。
A3セルに、「=A2+1」と入力します。
A3セルをコピーして、A4 - A32セルに貼り付けます。
A32セルが「=A31+1」となればOKです。
A2 - A32セルを選択して、「Ctrl+1」でセルの書式設定を開きます。
「日」のみ表示したいので、「ユーザー定義」を選択し、種類の欄に「d」と入力します。
A2 - A32セルに1, 2 - 31と日付が表示されれば、OKです。
・B2 - B32セル：曜日
B2セルに「=A2」と入力します。
「Ctrl+1」でセルの書式設定を開きます。
「ユーザー定義」を選択し、種類の欄に「aaa」と入力します。
B2セルをコピーして、B3 - B4セルに貼り付けます。
曜日が正しく表示されればOKです。
B2 - B32セルを選択して、「条件付き書式」を開きます。
「数式を使用して、書式設定するセルを決定」を選択。
書式欄に「=TEXT(A2,"aaa")="日"」と入力。
書式として、「塗りつぶし」の「ピンク」を選択。
「新規ルール」をクリック。
「数式を使用して、書式設定するセルを決定」を選択。
書式欄に「=TEXT(A2,"aaa")="土"」と入力。
書式として、「塗りつぶし」の「水色」を選択。
土曜日が水色、日曜日がピンクで塗りつぶされればOKです。
・C2 - C32セル：出社時刻
C2 - C32セルを選択して、「Ctrl+1」でセルの書式設定を開きます。
「ユーザー定義」を選択し、種類の欄に「h:mm」と入力します。
・D2 - D32セル：退社時刻
D2 - D32セルを選択して、「Ctrl+1」でセルの書式設定を開きます。
「ユーザー定義」を選択し、種類の欄に「h:mm」と入力します。
・E2 - E32セル：１日の作業時間[時間：分]
E2 - E32セルを選択して、「Ctrl+1」でセルの書式設定を開きます。
「ユーザー定義」を選択し、種類の欄に「h:mm」と入力します。
出社時刻「8:30」、退社時刻「17:15」で「7:45」と表示されればOKです。
と言いたいところですが、１日の作業時間はマクロで計算させるため、
確認はマクロ入力後にします。ちなみに、休憩時間は 12:00-13:00,
17:15-17:30, 18:30-19:00, 21:30-22:00です。
・F2 - F32セル：１日の作業時間[時間]
F2セルを選択し、「=E2*24」と入力します。
F2セルをコピーして、F3 - F32セルに貼り付けます。
F2 - F32セルを選択して、「Ctrl+1」でセルの書式設定を開きます。
「ユーザー定義」を選択し、種類の欄に「0.00」と入力します。
エクセルでは、24で割った値で時間計算しているらしく、24をかけて
標準の書式にしてやると、時間を10進表示できるようです。例えば、45分は、0.75時間になります。
・E33セル：１か月の作業時間[時間：分]
E33セルを選択し、「=SUM(E2:E32)」と入力します。
E33セルを選択して、「Ctrl+1」でセルの書式設定を開きます。
「ユーザー定義」を選択し、種類の欄に「[h]:mm」と入力します。
時間の書式を"[h]"としているのは、合計が24時間を超えてしまうからです。
・F33セル：１か月の作業時間[時間]
F32セルをコピーして、F33セルに貼り付けます。
次に、出社時刻(C列)、退社時刻(D列)のセルに変化があったら、１日の作業時間(E列)を計算するマクロを作ります。
「Alt+F11」でVBAを開きます。
該当シートのところに、次のように記述します。
| ' このマクロはシートに置くこと | |||||
| If Intersect(Target, Range("C2:D32")) Is Nothing Then | |||||
| ' 変化が無ければ抜ける | |||||
| Exit Sub | |||||
| Else | |||||
| ' 作業時間の計算 | |||||
| Dim Time1(2) As Date ' 時刻 | |||||
| Dim n As Integer ' 整数 | |||||
| Dim i As Integer ' 整数 | |||||
| n = Cells(Rows.Count, "A").End(xlUp).Row ' A列の最終日の行 | |||||
| For i = 2 To n | |||||
| Time1(0) = Cells(i, 3).Value ' 出社時刻 | |||||
| Time1(1) = Cells(i, 4).Value ' 退社時刻 | |||||
| ' 出社時刻>退社時刻だったら、作業時間に０を代入して終了 | |||||
| If Time1(0) > Time1(1) Then | |||||
| Cells(i, 5).Value = 0 | |||||
| Exit Sub | |||||
| End If | |||||
| Time1(2) = Time1(1) - Time1(0) | |||||
| ' 昼休み(12:00-13:00) | |||||
| If Time1(0) <= TimeSerial(12, 0, 0) And Time1(1) >= TimeSerial(13, 0, 0) Then | |||||
| Time1(2) = Time1(2) - TimeSerial(1, 0, 0) | |||||
| End If | |||||
| ' 休憩(17:15-17:30) | |||||
| If Time1(0) <= TimeSerial(17, 15, 0) And Time1(1) >= TimeSerial(17, 30, 0) Then | |||||
| Time1(2) = Time1(2) - TimeSerial(0, 15, 0) | |||||
| End If | |||||
| ' 休憩(18:30-19:00) | |||||
| If Time1(0) <= TimeSerial(18, 30, 0) And Time1(1) >= TimeSerial(19, 0, 0) Then | |||||
| Time1(2) = Time1(2) - TimeSerial(0, 30, 0) | |||||
| End If | |||||
| ' 休憩(21:30-22:00) | |||||
| If Time1(0) <= TimeSerial(21, 30, 0) And Time1(1) >= TimeSerial(22, 0, 0) Then | |||||
| Time1(2) = Time1(2) - TimeSerial(0, 30, 0) | |||||
| End If | |||||
| ' 作業時間の代入 | |||||
| Cells(i, 5).Value = Time1(2) | |||||
| ' 【注】 | |||||
| ' 作業時間合計のセルの表示形式を「[hh]:mm」にしておくこと。 | |||||
| ' [hh]としておくことで、24以上の時間を表示できる。 | |||||
| Next i | |||||
| End If | |||||
| End Sub | |||||
nice! ありがとうございます。
[__猫]
by cheese999 (2013-06-09 21:02)
F2 - F32セルの書式が間違っていました。訂正します。
誤：「0:00」
正：「0.00」
by cheese999 (2013-06-09 21:08)