プロが教える店舗&オフィスのセキュリティ対策術

excel勤務表の複合条件での関数

先日同様の内容で質問した者ですが、どうしても上手くいかないので再質問です。

関数の条件は…

『拘束時間が8時間以上ならば、実労働時間には休憩時間1時間を差引いて表示。
残業時間には、7時間超過分を表示。
出勤or退勤が空白ならば実労働時間、残業時間は空白表示』

ここまでは以下の関数で実現出来ました。

『実労働時間』欄(N5)
IF(OR(N5<=$O$38,N5=""),"",N5-O38)

『残業時間』欄(O5)
IF(OR(L5="",M5=""),"",M5-L5-O37)

これに以下の条件を付け足す方法が上手く作れないのです。

『拘束時間が7時間〜6時間ならば実労働時間には休憩時間30分を差引いた時間を表示。
拘束時間が5時間以下ならば実労働時間は拘束時間そのままを表示』

出勤L5
退勤M5
実労働時間N5
残業時間O5
1時間休憩固定入力O37
30分休憩固定入力M37

関数作成のヒントを教えて下さい。

A 回答 (3件)

O38は記載がないので推測となりますが、


M37には30分(0:30:00)、O37には1時間(1:00:00)、O38には7時間(7:00:00)が、シリアル値で入っていると仮定しました。
ほかの時刻も全てシリアル値とし、
セルの書式は「h:mm」などとなっているとして考えています。

まず、N5にN5を参照する式は入力できないので
提示されている式を入力するセルはN5とO5逆ではないでしょうか?

仮に、逆に入力したとしても
『拘束時間が8時間以上ならば、実労働時間には休憩時間1時間を差引いて表示。
残業時間には、7時間超過分を表示。
出勤or退勤が空白ならば実労働時間、残業時間は空白表示』(これを条件Aとします)
ではなく、
『拘束時間にかかわらず、実労働時間は拘束時間から休憩時間1時間を差引いて表示。
残業時間には、7時間超過分を表示。
出勤or退勤が空白ならば実労働時間、残業時間は空白表示』
を表す式になっています。

『拘束時間が8時間以上ならば、実労働時間には休憩時間1時間を差引いて表示。』が考慮されていないので、
その分を追加すると条件Aは、
N5(実労働時間)に
=IF(OR(L5="",M5=""),"",IF(M5-L5>=8/24,M5-L5-$O$37,M5-L5))
O5(残業時間)に
=IF(OR(N5="",N5<=$O$38),"",N5-$O$38)
を入力すると表せます。
(時間は、24時間でシリアル値「1」となるので、
 8時間のシリアル値は「8/24」で求められます。)

条件を表した言葉のうち、式にできたところは線を引いて消してみるなどすると、どの条件が表せていないか分かりやすいかもしれません。
また、少し面倒ですがはじめはすべての条件を言葉で表す(例えば、条件Aは以下)ようにすると、
式にもしやすいかと思います。順番も条件式に合わせて変更しています。
『出勤or退勤が空白ならば実労働時間、残業時間は空白表示。
拘束時間が8時間以上ならば、実労働時間には拘束時間(退勤-出勤)から休憩時間1時間を差引いて表示。
拘束時間が8時間未満ならば、実労働時間には拘束時間をそのまま表示。
残業時間には、実労働時間が7時間超過分を表示』


次に、『拘束時間が7時間〜6時間ならば実労働時間には休憩時間30分を差引いた時間を表示。
拘束時間が5時間以下ならば実労働時間は拘束時間そのままを表示』
の条件を追加するとして、
拘束時間が5時間~6時間と7時間~8時間の場合の休憩時間は
記載がありませんがどうなるのでしょうか?

拘束時間5時間~6時間と7時間~8時間も休憩30分として、
すべてまとめた条件を以下とします。(これを条件Bとします)
『出勤or退勤が空白ならば実労働時間、残業時間は空白表示。
拘束時間が5時間以下ならば、実労働時間には拘束時間をそのまま表示。
拘束時間が8時間以上ならば、実労働時間には休憩時間1時間を差引いて表示。
拘束時間が5時間超8時間未満(5時間以下でも8時間以上でもない)ならば、実働時間には休憩時間30分を差引いて表示。
残業時間には、実労働時間が7時間超過分を表示』
※この場合、No.2さんのおっしゃるように拘束時間が6時間超8時間未満の間は労働基準法に違反します

条件Bは、
N5に
=IF(OR(L5="",M5=""),"",IF(M5-L5<=5/24,M5-L5,IF(M5-L5>=8/24,M5-L5-$O$37,M5-L5-$M$37)))
O5に
=IF(OR(N5="",N5<$O$38),"",N5-$O$38)
を入力すると、表せます。
    • good
    • 1
この回答へのお礼

詳しいご説明ありがとうございます。
7時間〜6時間の休憩時間は45分に修正する予定です。

お礼日時:2022/03/18 22:07

Excelとは関係ないんですが、労働基準法で休憩は最低でも労働時間が8時間超で1時間、6時間超8時間以下で45分、6時間以下は必要なしとなっています。

(拘束時間ではなく実労働時間)

多めに休憩を取らせる分には問題ありませんが(8時間労働で1時間とか)、例えば7時間拘束で休憩30分なら6時間30分の労働で30分しか休憩がないことになるので違法では?
    • good
    • 0
この回答へのお礼

ご指摘ありがとうございます。
会社に確認してみます。

お礼日時:2022/03/18 12:22

こんにちは



これじゃダメなのでしょうか?
https://oshiete.goo.ne.jp/qa/12853026.html
    • good
    • 0
この回答へのお礼

たびたびありがとうございます。
excel素人なのでご迷惑おかけしました。
色々と試してみます。

お礼日時:2022/03/18 12:13

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!