エクセル初心者 時間ごとに残業手当が違う場合

質問

下図より
Ｃ：出社時間、D：退社時間　E：休憩時間　F：勤務時間　
G：残業時間(1)（17:00から15分単位で発生）
H：残業時間(2)(1)（18:00から15分単位で発生）があったとします。

現在、
Fの計算は=IF(D6="","",MIN(D6,"17:00")-MAX(C6,"8:45")-"1:00")
Gの計算は=IF(D6="","",IF(D6>"17:10"*1,D6-"17:00",0))
Hの計算は=IF(F6="","",IF(D6>="18:15"*1,D6-"18:00",0))

で作成してますが質問は
(1)Fの休憩時間１時間引く場合、"1:00"にせずに"F4"を引くとエラーになります
(2)Gの残業時間は17時から発生しHは18時から15分おきに発生するのですが式をひとつに
まとめて、「Dが17時を超えてたらGに表示、18時を超えていたらHに表示」にしたいです。
ちなみにGは時給\1600　Hは時給\2000となります。

　　　　　C 　　D E 　　 F 　G 　 H
4 火 8:45 17:45 1 7:15 0:45 0:00
5 水 8:45 12:15 1 2:30 0:00 0:00
6 木 8:45 18:15 1 #VALUE! 1:15 #VALUE!

勉強不足で申し訳ありません。

どうぞよろしくお願い致します。

回答No.2

kagakusuki

>「=2,」はどのような役割を果たしているのでしょうか。

　「COUNT(C6,D6)=2」の部分の事ですね。
　COUNT関数は、指定したセルやセル範囲の中に、「数値が入っているセル」が何個あるのかを数える関数です。
　「COUNT(C6,D6)」の場合、COUNT関数の中で指定されているセルはC6セルとD6セルの2つですから、「COUNT(C6,D6)=2」は「C6セルとD6セルの2つのセルの内、数値が入力されているセルの個数が2個であるのか否か」という事を判定する関数という事になります。
　前回の回答でも少し触れましたが、Excelでは時刻のデータはシリアル値という数値データの一種として扱われますから、C6セルとD6セルの2つのセルの2つとも時刻が入力されている場合には、IF関数によって勤務時間や残業時間の計算を行い、数値が入力されているセルが2つ未満の場合、即ち、2つのセルの内、1つでも時刻が入力されていないセルが存在している場合には、IF関数によって何も表示しない様にしている訳です。
　例えば、御質問文内で挙げられている

=IF(D6="","",MIN(D6,"17:00")-MAX(C6,"8:45")-"1:00")

という関数の場合、F6セルが空欄となるのはD6セルが空欄となっている場合だけですから、もし、遅刻した人が実際には9:30
に出社した場合であっても、D6セルに退社時刻だけを入力し、C6セルには出社時刻を入力せずにタイムカードを出しさえすれば、8:45に出社した場合と同じ勤務時間が表示される事になってしまいます。
　その様な事を防ぐためには、

=IF(OR(C6="",D6=""),"",MIN(D6,"17:00")-MAX(C6,"8:45")-"1:00")

等の様にすれば良いのですが、そうした場合であっても、C6セルかD6セルに時刻ではなく「ABC」とか「あいうえお」といった文字列データが入力された場合には、エラーとなってしまいます。
　その様な事を回避するために、

=IF(COUNT(C6,D6)=2,

としている訳です。
　尤も、この様にした場合でも、時刻データではない単なる数値や、日付けデータが入力された場合にも、計算処理へと進んでしまいますので、誤った結果が表示されてしまう恐れが残ってはいるのですが、その場合であっても少なくともエラー表示が出る事は無いため、単なる数値や日付が入力される事に対してまでは、対応策を採ってはおりません。

お礼

わかりやすい回答有難うございます。
適当に作成した他の資料も見直してみたいと思います。。
私も含めて周囲がエクセル音痴の文系集団なので助かりました。
多忙のところお時間頂きましてすみませんでした。

回答No.1

kagakusuki

>(1)Fの休憩時間１時間引く場合、"1:00"にせずに"F4"を引くとエラーになります

との事ですが、F列の勤務時間の値を求める際に、何故、同じ列上にあるセルであるF4セルの値を差し引いておられるのでしょうか？
　それに質問文に書かれているFの計算は

=IF(D6="","",MIN(D6,"17:00")-MAX(C6,"8:45")-"1:00")

という、F列の6行目に入力されている関数である様に思われるのにもかかわらず、何故、4行目のセルであるF4セルの値を差し引いておられるのでしょうか？
　もしかしますと、「"1:00"にせずに"F4"を引くと」という箇所は間違いで、本当は「"1:00"にせずに"E6"を引くと」なのではないでしょうか？
　もしそうだとしますと、F6～H6の各セルに入力する関数の一例は以下の様なものとなります。

【F6セルの関数】
　E6セルに入力される値が、必ず「1」とか「1.0」、「1.75」といった時間（hour）単位で入力されると決まっている場合には、次の様な関数となります。

=IF(COUNT(C6,D6)=2,MIN(D6,"17:00")-MAX(C6,"8:45")-TIME(E6,,),"")

　又、E6セルに入力される値が、「1.75」の様な時間（hour）単位で入力される事もあれば、「1:45」の様な「時間:分」の形式で入力される事もある、という様な場合には、次の様な関数となります。

=IF(COUNT(C6,D6)=2,MIN(D6,"17:00")-MAX(C6,"8:45")-IF(E6<1,E6,TIME(E6,,)),"")

【G6セルの関数】

=IF(COUNT(C6,D6)=2,IF(OR(D6<"17:15"*1,D6>="18:15"*1),"",TEXT(FLOOR(D6-"17:00"+0.000001,"0:15"),"h:m")*1),"")

【H6セルの関数】

=IF(COUNT(C6,D6)=2,IF(D6<"18:15"*1,"",TEXT(FLOOR(D6-"18:00"+0.000001,"0:15"),"h:m")*1),"")

　尚、上記のG6セルの関数とH6セルの関数に関しましては、15分未満の端数は切り捨てとなっております。（四捨五入では御座いません）
　F6セルの関数に関しましては、御質問文内には特にご要望がありませんでしたので、15分単位で値を丸める事は行っておりません。
　それと、D6セルの値から"17:00"や"18:00"を差し引いた後に、0.000001を足している理由は、Excelで時間を扱う際にはシリアル値という概念が使われていて、時間を表す際に「その時間を1日（24時間）で割った数値」として扱っているため、端数の切り捨てや切り上げを行う際に、千兆分の1前後の計算誤差によって正しい値が得られない場合があるので、その問題を回避するために、0.5秒未満の時間に相当する数値を加えています。

お礼

さっそくのご回答ありがとうございます。

ご指摘のとおり、F4ではなくE6の誤りです。大変失礼致しました。
計算式をkagakusuki様のとおり修正したところできました！
本当に助かりました。有難うございます。
無知でお恥ずかしいのですが「=2,」はどのような役割を果たしているのでしょうか。

エクセルができて本当にうらやましい限りです。