エクセル勤務表で昼休憩を挟んだ時の休憩算出方法

エクセルで添付のような勤務表を作成しています。
労働時間（表中・小計）に応じた休憩時間は自動算出出来るようになったのですが、まだ課題がありまして…

それは、お昼休憩を挟んだ時の休憩時間についてです。
うちでは、お昼を挟むと、勤務時間に関係なく１時間の休憩になります。（お昼休憩）


例えば、９時～１８時だと、小計９時間・休憩１時間（８時間以上なので）・実労働（表中・合計）時間８時間となるので大丈夫なのですが。

９時～１５時の勤務だと、小計６時間・休憩３０分・実働５時間３０分になってしまいます。
これを、小計６時間・休憩１時間・実働５時間になるように計算させたいのです。


表中の各計算式は以下です。

C8→勤務開始を入力
D８→勤務終了を入力

D8の小計→=IF(D8<C8,1+D8-C8,D8-C8)
F8の休憩→=IF(E8<"4:00"*1,0,IF(E8<"6:00"*1,10,IF(E8<"7:00"*1,30,IF(E8<"8:00"*1,40,60))))/24/60

G8の合計→=E8-F8

です。

アドバイスよろしくお願いします。

No.1 ベストアンサー 回答者： siremono2496 回答日時： 2012/03/29 21:01

であれば、F列のIF関数の最初に次の条件を追加すればいけるのでは？

「業務開始時間が12時より前かつ、勤務終了時間が13時より後ろだったら、休憩時間は1.0hとする」

=IF(AND(C8<"12:00",D8>"13:00"),1,IF(E8<"4:00"*1,0,IF(E8<"6:00"*1,10,IF(E8<"7:00"*1,30,IF(E8<"8:00"*1,40,60))))/24/60)

この回答への補足

先ずはご回答ありがとうございます。

こ、これです！この条件をどう入力すれば良いのか、また「○時間」以上の「時：分」と「○時○分」の「時：分」が両立してくれるものか悩んでしまって行き詰っておりました。

私の探していたイメージぴったりの式で、どう条件設定をすれば良いか勉強になりました。

イメージぴったりという事で、この式でやってみてＯＫだったら質問を締め切ろうかとも思ったのですが、他の角度からのアプローチもあり、今後の参考・勉強のためにもご回答くださった皆様の式を試したいと思っております。


お礼が遅くなってしまいますが、今エクセルソフトが手元にないため、お礼とベストアンサーについては色々な検証が終わり、疑問が解決（課題クリア）しましたら改めて書かせて頂きますのでよろしくお願いします。

補足日時：2012/03/30 12:35

この回答へのお礼

エクセルの使用環境がありましたので検証してまいりました。

その結果…どうにもうまく行きませんでした（涙）
丸々コピーしたので打ちミスはないと思います。また、一応抜け？などが無いかも見たのですが分からず…

書式設定の変更などをすれば解決するでしょか？
具体的には、９時～１５時と打っても、今までと同じく３０分間の休憩のみとなってしまいます。


補足を使用してしまったのでお礼欄への記入で失礼いたします。
他に、方法やアドバイス等ありましたら、引き続きよろしくお願い致します。

siremonoさま、ヒントとなる回答、ありがとうございました！

お礼日時：2012/04/01 18:41

No.3 回答者： KURUMITO 回答日時： 2012/03/30 09:36

小計の計算の式から判断すると深夜12時を過ぎた時間は0：30のように入力するようになっていますが休憩時間の計算式が複雑になりますので24:30のように入力するとします。

そこでＦ8セルへは次の式を入力します。

=IF(MIN("13:00",D8)>MAX("12:00",C8),MIN("13:00",D8)-MAX("12:00",C8),0)+IF(D8>"13:00"*1,IF(D8-MAX("13:00",C8)<"4:00"*1,0,IF(D8-MAX("13:00",C8)<"6:00"*1,"0:10",IF(D8-MAX("13:00",C8)<"7:00"*1,"0:30",IF(D8-MAX("13:00",C8)<"8:00"*1,"0:40","1:00")))))

この式では午前中からの13時過ぎまで働いた場合には12時から13時まで休憩の時間を取っていますので、13時以降の時間について働いた時間を計算して休憩時間を計算するようにしています。なお、12時30分から勤務が始まっている場合には13時までは休憩時間にしています。
例えば12：30に勤務が始まって20：00に終了した場合には13時以降の勤務が7時間となることから40分の休憩と昼の時間の30分がプラスされて合計の休憩時間は1時間10分となります。

この回答への補足

先ずはご回答ありがとうございます。
えぇと、せっかく素晴らしい回答くださったのに…こちらの説明不足でちょっと実用的では無くなってしまいました（汗）

実は、この勤務表、夜勤の計算にも使用しております。
（夜勤は日勤でいうお昼休憩という概念はなく、一律１時間です。全日８時間以上なため。）

また以前は、翌９時などを３３時と打っており、この脳内変換作業がミスの元になったので、入力方法を現在の形にしたという経緯があります。（０時ちょっと過ぎくらいが多いなら２４時で対応出来たのですが、大抵は朝の時間帯が勤務終了時間なので…。タイムカードには９時と印字されるので、うっかりそのまま打つ…とか。）

式の解説を読むと「おぉ！」と声をあげるくらい取り入れたいと思った式なのですが、本当に申し訳ありません。


私事ですが、今エクセルソフトが手元にないため、お礼とベストアンサーについては検証が終わり、疑問が解決（課題クリア）しましたら改めて書かせて頂きますのでよろしくお願いします。

補足日時：2012/03/30 12:14

No.2 回答者： esupuresso 回答日時： 2012/03/29 21:44

取り敢えずＪ列とＫ列を使ってテーブルを作り、それをVLOOKUP関数で取り出すという方法を・・・

E8= IF(D8="","",(D8+IF(D8-C8<0,1,0))-C8)　←この式を下へコピー
F8= IF(E8="","",VLOOKUP(ROUNDUP(E8,3),$J$7:$K$11,2,1))←この式を下へコピー
G8= IF(E8="","",E8-F8)←この式を下へコピー

一応エラー処理してます　
サンプル画像を添付しておきます　<(_ _)>

この回答への補足

先ずはご回答ありがとうございます。

サンプル画像まで付けて頂きありがとうございます。
実は恥ずかしながらエラー表示が出ているのは知っていたのですが、一体どれが原因でエラーが出たかまでは未検証・未確認で、きちんと理解しておりませんでした。
今回、エラー処理して頂いてるとのことで、見比べて（…これで分かれば一発で理解してそうですが；；）前回のエラーの原因をきちんと確認・理解したいと思います。


私事ですが、今エクセルソフトが手元にないため、お礼とベストアンサーについては検証が終わり、疑問が解決（課題クリア）しましたら改めて書かせて頂きますのでよろしくお願いします。

補足日時：2012/03/30 12:22