excel関数で勤務時間の計算をしたいと思っています。

質問

excel関数で勤務時間の計算をしたいと思っています。
添付の画像の通り、実際の勤務実績を入力すると、右側の色つき部に早朝、早出や残業時間などを自動で表示できないかと考えています。

何とかなりませんでしょうか？

回答No.6

有田 賢治（@ARIKEN43）

hanaoyajiさん 今日は！前回の続きです。
■Excel実用編：勤務時間の計算例(関数)「Excel2002」
http://www.eurus.dti.ne.jp/~yoneyama/Excel/nyumon/kinmu/index.html
概要：はじめに　勤務時間計算の条件
一つのセルでいろんな条件を付けて計算しようとすると、長い数式になり修正が非常に大変になります。
このページでは、なるべく平易な分かりやすい数式となるように、各時間帯別に勤務時間を計算する方法で説明しています。
(作業列をちょっと多めに使用しています)
☆勤務時間計算の条件設定
1.通常勤務の時間帯　：　 8:00～17:00（休憩時間　12:00～13:00）
2.残業時間帯　　　　：　17:00～22:00
3.深夜残業時間帯　　：　22:00～30:00 (翌朝 6:00）
4.出勤時間は30分単位で切り上げ、ただし、8:00以前の出勤はすべて8:00出勤とします。
5.退勤時間は30分単位で切り捨てとします。
6.勤務時間の計算は30分単位で切り捨てとします。
計算表のスタイル
5.各勤務時間帯の時刻を6,7行目に入力します：各勤務時間帯の計算式を同一のものにするためです。
目障りならこれらの行を非表示にすればOKです。
6.計算用の出勤時間と退勤時間をF列とG列に表示することにします。
7.数式を簡潔にして、見やすくするためです。目障りならこれらの列も非表示にすればOKです。
8.24時以降は25:00，30:00の入力としています。セルの表示形式を『[h]:mm』とします。
☆計算表のスタイル設定例を参考にして下さい。
☆時間の切り上げと切り捨て　　　　　　
ここでは、まず以下の計算の条件を処理します。
(4) 出勤時間は30分単位で切り上げ、ただし、8:00以前の出勤はすべて8:00出勤とします。
(5) 退勤時間は30分単位で切り捨てとします。
(6) 勤務時間の計算は30分単位で切り捨てとします。
(4-1)出勤時間を30分単位で切り上げるには、CEILING関数を使います。
F8セル：=CEILING(D8,"0:30")　とします。
F8セル：=7:30　と30分単位で切り上げができました。しかし、8:00からの計算とするには(4-2)の処理が必要になります。
(4-2)IF関数またはMAX関数を使って、開始時刻は8:00とする条件を加えます。
8:00以前の出勤はすべて8:00出勤とし、8:00以降の出勤時間は30分単位で切り上げます。
F8セル：=IF(D8<=TIME(8,0,0),TIME(8,0,0),CEILING(D8,"0:30"))
または、最小値を8：00とすれば良いので、
F8セル：=MAX("8:00",CEILING(D8,"0:30"))
とすることもできます。
(4-3)ただし、D8セルが未入力の時も計算結果は8:00となるので、IF関数で未入力時の処理を加えます。
=IF(COUNT(D8)=1,MAX("8:00",CEILING(D8,"0:30")),"")　とします。
(5-1)退勤時間を30分単位で切り捨てるには、FLOOR関数を使います。
G8セル：=FLOOR(E8,"0:30")　とします。
(5-2)ただし、E8セルが未入力の時も計算結果は0:00となるので、IF関数で未入力時の処理を加えます。
=IF(COUNT(E8)=1,FLOOR(E8,"0:30"),"")　とします。
以降は設定が、図解されて居ますので良く読んで作成して下さい。
これで勤務時間が理解できると思います。

回答No.5

有田 賢治（@ARIKEN43）

hanaoyajiさん 今日は！前回の続きです。
11.E6セルが空欄の場合に空白処理を付け加えます。　　　　
=IF(E6=""，””，・・・)/=IF(E6=""，””，FLOOR(TIMEVALUE(TEXT(E6,"h:mm"),$F$3))
(注)F列の表示形式は、[時刻]の[13:30]とします。標準のままでは、シリアル値が表示されます。
12.時間の切り捨て/切り上げ/四捨五入は、CEILING関数/FLOOR関数/MROUND関数を使います。
(注)D3:E4　および　D9:D12　の表示形式は［時刻］の［13：30］としてあります。
13.MROUND関数が使用できず、エラー値 #NAME? が返される場合は、分析ツール アドインを組み込む必要があります。設定は下記の手順で行います。
14.[ツール] メニューの [アドイン] をクリックし、[アドイン] の一覧の [分析ツール] ボックスを選択し、[OK]ボタンをクリックします。必要に応じて、セットアップ プログラムで表示される手順に従って操作します。
☆支給額の計算：15.基本的には[支払い時間]*24*[時給額]なので[支払い時間]はシリアル値0.3958）なので、24をかけて9.5(時間)に直します。
16.日々の支払額はROUND関数を使って小数点以下を四捨五入しています。
平日の計算式　：=ROUND(F6*24*$H$1,0)
17.土日祝の計算式：=ROUND(F6*24*$H$2,0) これは、月単位の合計を出した時に小数点以下の誤差を防ぐためです。[注]小数点以下の値があると、合計時に誤差を生じます。
18.時給が[平日]と[土日祝]で異なる場合、IF関数で[時給額]の参照セルを変えるようにします。
条件は曜日欄のB6セルが「月、火、水、木、金」であれば、「平日」の時給額、そうでなければ「土日祝」の時給額としています。
19.=IF(OR(B6="月",B6="火",B6="水",B6="木",B6="金")、平日の計算式,土日祝の計算式)
空白処理を加えます。
20.=IF(F6="","",IF(OR(B6="月",B6="火",B6="水",B6="木",B6="金")、平日の計算式を入力。
条件は曜日欄のB6セルが「月、火、水、木、金」であれば、「平日」の時給額、そうでなければ「土日祝」の時給額としています。=IF(OR(B6="月",B6="火",B6="水",B6="木",B6="金")平日の計算式,土日祝の計算式)空白処理を加えます。
21.=IF(F6="","",IF(OR(B6="月",B6="火",B6="水",B6="木",B6="金"]は、{F6*24*$H$1,0),ROUND(F6*24*$H$2,0)))、となります。
☆日付の表示：21.曜日欄B列には祝日名を入力しています。これは、時給金額を[土日祝]で変える時のIF関数の条件で月～金でなかったらとしている為で(正確には、月～金の文字でなかったら「祝日」でも良いです)実際には、これらを入力するのは面倒ですので、マクロで処理しています。
21.C3セルに年月を入れ、[カレンダー表示]ボタンをクリックすると[日]、[曜日]、[祝日名]が自動で表示されます。
22.マクロについては参考です。マクロでの1月の祝日の判定箇所です。
こんな感じで12か月分を記述しています(バージョンで異なる）
23.1月の祝日判定
Select Case 基準月
Case 1
　　If 基準年 >= 1949 Then
　　　　祝日(1) = 1
　　　　祝日名(1) = "元　旦"
　　　nd If
'成人の日　1999年までは１５日に固定
　　If 基準年 >= 1949 And 基準年 < 2000 Then
　　　　祝日(2) = 15
　　　　祝日名(2) = "成人の日"
　　　nd If

'成人の日　２０００年以降は第２月曜日
　　　If 基準年 >= 1949 And 基準年 >= 2000 Then
　　　　　count = 0
　　　i = 0
　　　For i = 0 To 20 Step 1
　　　　　　If Weekday(日付 + i) = 2 Then
　　　　　　　　　count = count + 1
　　　　　　　　If count = 2 Then
　　　　　　　　　　祝日(2) = Day(日付 + i)
　　　　　　　　　　祝日名(2) = "成人の日"

回答No.4

有田 賢治（@ARIKEN43）

hanaoyajiさん 今晩は！
>実際の勤務実績を入力すると、右側の色つき部に早朝、早出や残業時間などを自動で表示できないかと考>えています。
入社・退社時間をマクロで自動化は、可成り面倒です。
■Excel(エクセル)実用編：時給計算書の作成例（Excel2002）
http://www.eurus.dti.ne.jp/~yoneyama/Excel/jituyou/jikyu.htm#kanseirei
概要：このページではエクセルで求めた時間から時給を計算する方法を説明しています。
1.時刻/時間の基本的な計算方法は→時刻の計算
2.基本的な、勤務時間の計算例は→勤務時間の計算例
3.完成例 4.基本数値の入力 5.勤務時間の計算 6.支払時間の計算 7.支給額の計算 8.日付
時刻/時間の基本的な計算方法などは別ページで説明しています。
1.項では[サンプルダウンロード]の入手が出来ます。（無料です）
2.項では基本数値の入力：（1）氏名欄および基本数値欄【完成例】
3.B1セルに「氏名」と文字を入力:C1:D1セルは【セルを結合して中央揃え】ボタンで、セルを結合しています。
4.F1,G1,G2,G3セルはそれぞれ、[時給]/[平日]/[土日祝]/[分単位]の文字を入力しH1,H2セルは時給額を入力します。【桁区切りスタイル】ボタンで桁区切りを使っています。F3セルは時間計算単位を入力します。この例では30分刻みです。
5.セルの書式を『時刻』の『13:00』としています。
F3セルを選択し、メニューバーの【書式】→【セル】→【表示形式】タブで以下を選択します。
6.上記(2)と同様に、C3:C4セルセルを結合した後、セルの書式を【表示形式】は[日付]【種類】を[2001年3月]としています。
☆勤務時間の計算　　　　　　　　　
7.E6セルの計算式は、基本的には　[退社時間]-[出社時間] 退社時間が深夜の0時を過ぎた場合は、
（退社時間が出社時間より小さいことで判定しています)には、『+1』を加えて、計算するようにします。
式：=IF(D6-C6<0，D6-C6+1，D6-C6)
(メモ)　時刻のシリアル値
8.24時間で「1」となるので、翌日の場合は+1とする必要があります。具体的には、21：00はシリアル値では0.875、3：00は0.125ですので、3：00-21：00＝0.125-0.875＝-0.75　(マイナス値)　となり、時間として計算できません。
また、[出社時間]と[退社時間]のどちらかが空欄のときは、計算をせずに[勤務時間]欄を空欄にします。
=IF(OR(C6="",D6=""),"",・・・・）または=IF(COUNT(C6:D6)<2,"",・・・・）この2つの式を合成して使っています。
(注)C,D,E列の表示形式は、[時刻]の[13:30]とします。標準のままでは、シリアル値が表示されます。
☆支払時間の計算：支払い時間を計算単位時間で丸めるには計算単位時間で切り下げるにはFLOOR関数を使います。=FLOOR(E6,$F$3) (=FLOOR(E6,”0：30”)と同じ)
☆支払時間の計算：支払い時間を計算単位時間で丸める：計算単位時間で切り下げるにはFLOOR関数を使います。=FLOOR(E6,$F$3) (=FLOOR(E6,”0：30”)と同じ)
9.小数誤差をさける→出社，退社時間をキーボードから入力する場合は問題ありませんが、オートフィルなどで連続値をコピーした時などに、小数点以下の計算誤差を生じる事があります。
10.その誤差を避けるため、TIMEVALUE関数を組み合わせています。=TIMEVALUE(TEXT(E6,"h:mm")に上記式のE6部分を置き換えます。=FLOOR(TIMEVALUE(TEXT(E6,"h:mm"),$F$3)とします。

以降は補足します。

回答No.3

KURUMITO

Ｅ4セルなどの時間は範囲を指定したセルですがお示しの0～7のように～の入った文字列になっているとします。
Ｅ5セルには次の式を入力してＩ5セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNT($B5:$C5)<>2,"",IF(MIN($C5,MID(E$4,FIND("～",E$4)+1,2)/24)<MAX($B5,LEFT(E$4,FIND("～",E$4)-1)/24),"0:00"*1,MIN($C5,MID(E$4,FIND("～",E$4)+1,2)/24)-MAX($B5,LEFT(E$4,FIND("～",E$4)-1)/24)))

なお、24：00を超えた深夜時間はないのですね。その場合にはＩ4セルを22～26などとして、Ｃ列のデータでは25:00などと入力すればよいでしょう。25：00と入力しても1：00のように表示される場合にはセルの書式設定からセルの表示形式でユーザー設定にし、種類の窓には　[h]:mm　のように入力します。

回答No.2

foomufoomu

訂正
前記の式だと時間の範囲内にまったく出勤していないと#####表示になってしまいます。
そこで、前の例の、7時から8時の間の勤務時間(F5セルの内容)は
=MAX(MIN(C5,"8:00")-MAX(B5,"7:00"),0)
としてください。

回答No.1

foomufoomu

エクセルの日時は1日(24時間)を１とした実数であらわされていて、大小比較や加減算が可能です。また式中に"8:30"などと書くと、前記ルールで日時を表す数として扱われます。
したがって、たとえば、7時から8時の間の勤務時間(G5セルの内容)は
=MIN(C5,"8:00")-MAX(B5,"7:00")
で、計算できます。