休憩時間の変動を含むExcelでの作業時間計算

こんばんは、skireです。
Excelで作業時間を計算するための表を作ろうと思っています。
そこで少々行き詰まってしまいましたのでお知恵をお貸し下さい。

　　　　A　　　　　　B　　　　　　C　　　　　D　　　　　　　　E
　　開始時間　終了時間　　居残り　作業時間　居残り作業時間
１　　８：００　　　１６：００　　
２　１０：００　　　２２：００　　１：００
３　　９：００　　　１３：００

このD1以降のセルに休憩時間を差し引いた作業時間を自動で表示されるようにしたいです。
ですが、この休憩時間がちょっと面倒でして、
B1-A1が８：４５以上なら１時間
B1-A1が６：００より多く、８：４５未満の場合には４５分
B1-A1が６：００以下なら休憩はなし
という計算をします。
（２１：００より遅くなると居残り→居残り作業時間。
２１：００以降に作業をしに来た人に関しては今やると混乱しそうなので省いています。
いつかは挑戦したいのですが……）
自分で組んだ式は以下です。

D1=
IF(TEXT(B1-A1,"h:mm")>"8:44",TEXT(B1-A1,"h:mm")-"1:00",IF(TEXT(B1-A1,"h:mm")>"6:00",TEXT(B1-A1,"h:mm")-"0:45",TEXT(B1-A1,"h:mm")))-TEXT(E1,"h:mm")
E1=B4-"21:00"


この式で、
B1-A1が６：００より多く、８：４５未満の場合には４５分
B1-A1が６：００以下なら休憩はなし
の部分と居残り時間についてはうまくいくのですが、
８：４５以上の休憩だけがどうしてもうまくいきません。
１：００マイナスされていない数字が入力されてしまいます。
小さなミスだとは思うのですが、見つけることが出来ません。
よろしくお願いします。

No.3 ベストアンサー 回答者： umekihajime 回答日時： 2011/08/16 00:20

添付を参照願います。

No.11 回答者： kagakusuki 回答日時： 2011/08/16 13:28

　回答番号ANo.ANo.4です。

　居残り作業時間を求める関数も回答されている方もおられる様ですが、休憩開始時刻と休憩終了時刻が不明のままでは、21:00以前に全ての休憩を取り終えた人や、21:00以降に休憩を取った人、休憩の途中で21:00を迎えた人、等の違いが出て来る可能性があり、これらの違いを無視して一律に、終了時間だけを基にして居残り時間を算出したのでは、不平等になると思いますので注意なさって下さい。（私が居残り作業時間を求める関数を回答しなかったのも、そのためです）

この回答へのお礼

沢山のご回答をありがとうございます。
お返事、お礼が遅れてしまい申し訳ございません。

予想より沢山の方にお答えをいただけましたので、
誠に失礼ながらお礼をまとめさせていただきます。
休憩時間と作業時間の関係にまで言及して下さった方もありがとうございます。
これからのメンバーとのミーティングの時に話に出してみようと思います。

また、ベストアンサーには回答順で一番最初に意図した答えを出すことが出来た
umekihajimeさんの回答を選ばせていただきました。
他の方の回答も使わせていただき、よりよいものを作っていこうと思います。
本当にありがとうございます。

お礼日時：2011/08/21 13:26

No.10 回答者： bin-chan 回答日時： 2011/08/16 11:17

＃１です。

IF(TEXT(B1-A1,"h:mm")>"8:44",TEXT(B1-A1-"1:00","h:mm"),
では？

No.9 回答者： CoalTar 回答日時： 2011/08/16 09:58

＃7です。

余談ですがm(_ _)m

演算誤差とか小数点誤差対策として、時間計算においてtext関数を使うことは有効だと思います。
(今回の件に当てはまるかは別として)

サンプル１
="1:02"-"0:59"="0:03"*1
結果 FALSE

サンプル２
=TEXT("1:02"-"0:59","h:m")*1="0:03"*1
結果 TRUE

サンプル３
=-TEXT("0:59"-"1:02","[m]")=3
結果 TRUE

No.8 回答者： KURUMITO 回答日時： 2011/08/16 07:54

時間の計算では入力したデータがすべてシリアル値として認識されていることが必要です。

A1セルに8:00と入力し、そのセルを右クリックして「セルの書式設定」の「表示形式」から「標準」に切り替えることで0.33333・・（0.3333・・日)のように表示されますね。それがシリアル値なのです。時間の計算ではそのシリアル値が計算に使われるのです。
お示しの式ではTEXT関数が使われていますね。この関数を使うことでせっかくシリアル値として認識されている時間のデータを文字列に変えてしまうことになります。時間の表示を式の上で8：00と表示したい場合などにTEXT関数を使うことはありますが時間の計算でTEXT関数を使う必要は全くありません。文字列となっているデータを計算に使うことはできないからです。
ただ、文字列となっている例えば16:00から8:00を引くといった場合には自動的にパソコンが時刻と判断して引き算をしてはくれますが16:00>8:00といった比較は文字列ですので不可能です。
次のような式は時間の計算ではよく使われますのでぜひ理解できるようにしてほしいですね。
ご質問のD1セルへの入力の式は次のようにします。

=IF(COUNT(A1:B1)<>2,"",B1-A1-IF(B1-A1>="8:45"*1,"1:00",IF(B1-A1>"6:00"*1,"0:45",0)))

この式の意味はA1セルとB1セルが空の場合にはＤ1セルを空にしなさい。そうでない場合にはＢ1セルからＡ1セルの値を引く、そのほかにＢ1セルからＡ1セルの値を引いたときに（シリアル値の引き算になります）その値が8：45のシリアル値（＊1としているのは文字列である８:45をシリアル値に変換するためです）以上の場合には1:00（数値として1:00の数値はありません。文字列として"1:00"のように使っていますが先にも述べたように時間の計算で引き算や足し算の場合には文字列であっても自動的にシリアル値として理解されています。）を引きなさい、Ｂ1セルからＡ1セルの値を引いた値が6:00のシリアル値以上の場合には0:45を引きなさい、それ以下の場合には0を引きなさい、との意味になります。

また、E1セルへの入力の式は次のようにします。

=IF(B1>"21:00"*1,B1-"21:00","")

Ｂ1セルの値が21:00よりも小さい値のばあには引き算の結果がマイナスになり、時間の計算ではエラーが表示されることになります。

最後にＤ列やＥ列に表示されるのはいずれもシリアル値です。それらの数値を時間の表示にするためにはＤ列およびＥ列を選択した後で右クリックし「セルの書式設定」の「表示形式」タブから「時刻」で13:30などを選択すればよいでしょう。勿論、「ユーザー定義」でh:mmと入力しても同じことになりますね。

No.7 回答者： CoalTar 回答日時： 2011/08/16 05:47

混乱しそうなときは作業列を使って、順を追って行うとよいです。

>小さなミスだとは思うのですが、見つけることが出来ません。
問題点は
TEXT(B1-A1,"h:mm")>"8:44
文字列の大小比較になっていしまっています。

「数式から分単位で入力すると思われる」前提のもと、
休憩時間の列を設けて
=LOOKUP(-TEXT(A2-B2,"[m]"),{0,361,525},{0,"0:45","1:0"})*1
としてはいかがでしょうか？
もちろん、休憩時間と作業時間の関係を表した表を設けたほうがより良いと思います。

いちおう、簡単な説明
分換算([m])と小数点誤差の考慮(text関数)と数値化(-)、-TEXT(A2-B2,"[m]")
分による休憩時間の検索(LOOKUP)
数値化 *1

No.6 回答者： nayuta_lot 回答日時： 2011/08/16 01:00

こんにちは

時間の比較がうまくいってないような気がします。

Ｄ１に

=IF(B1-A1>=TIMEVALUE("8:45"),B1-A1-"1:00",IF(B1-A1>TIMEVALUE("6:00"),B1-A1-"0:45",B1-A1))-IF(E1<>"",E1,"0:00")

Ｅ１に

=IF(B1>TIMEVALUE("21:00"),B1-"21:00","")

と入力してみてください。

計算結果が意図してないものでしたら無視してください。

No.5 回答者： mshr1962 回答日時： 2011/08/16 00:50

D1=IF(COUNT(B1,A1)=2,B1-A1-LOOKUP(B1-A1,{"0:00","6:01","8:45"},{0,"0:45","1:00"}),"")

E1=IF(COUNT(B1)=1,MAX(B1,21/24)-21/24,"")

No.4 回答者： kagakusuki 回答日時： 2011/08/16 00:46

　まず、適当な場所に拘束時間と休憩時間の関係を表した表を作成して下さい。

　例えばSheet２の
A2セルに　　0:00
B2セルに　　0:00
A3セルに　　6:01
B3セルに　　0:45
A4セルに　　8:45
B4セルに　　1:00
と入力して下さい。
　次に、「作業時間を計算するための表」が存在ているSheetのD2セルに次の数式を入力して下さい。

=IF(COUNT($A2,$B2)=2,$B2-$B1-VLOOKUP($B2-$A2,Sheet2!$A:$B,2),"")

　次に、D2セルをコピーして、D3以下に貼り付けて下さい。

　これで作業時間が自動で表示されます。

　尚、D2セルに入力する数式を次の様に変えれば、拘束時間と休憩時間の関係を表した表を作成しなくても、同様の事が可能です。（6時間1分＝361分、8時間45分＝525分、1日＝1440分）

=IF(COUNT($A2,$B2)=2,$B2-$B1-LOOKUP(($B2-$A2)*1440,{0,361,525;0,"0:45","1:00"}),"")

No.2 回答者： tom04 回答日時： 2011/08/15 23:56

こんばんは！

＞E1=B4-"21:00"
は無視して・・・

1行目はタイトル行になっていて、2行目以降に数式を入れるとします。

D2セル（セルの表示形式はユーザー定義から　[h]:mm　としておきます）に
=IF(COUNTBLANK(A2:B2),"",IF(B2-A2<"6:00"*1,B2-A2-E2,IF(B2-A2<"8:45"*1,B2-A2-"0:45"*1-E2,B2-A2-"1:00"*1-E2)))
としてオートフィルで下へコピーではどうでしょうか？

的外れならごめんなさいね。m(_ _)m