ＩＦ関数を使った勤務時間表

質問

勤務時間表をエクセルで作成しました。
出勤時間　退勤時間　休憩時間
と入力し退勤時間－出勤時間－休憩時間で勤務時間を求めます。それぞれのセルに時間を入力して勤務時間を求めていましたが会社の管理により休憩時間がそれぞれの時間帯に定められています。そこで出勤時間と退勤時間だけの入力で、休憩時間の条件を定義すれば休憩時間は入力することなく求められるかと思いましたがＩＦ関数を使うと複雑で長くなりうまくできませんでした。”この関数に関する引数が多い”とのエラーメッセージが出てしまいます。ＶＬＯＯＫも考えてみましたがもの凄く長い条件になりそうで諦めました。休憩時間は以下の通りになります。
10：00～10：10
12：00～12：40
15：00～15：10
ちなみに私の通常勤務は8：30～16：30となります。
早退や遅刻などはたまになので難しい関数使うよりも
簡単に引くべき休憩時間は分かるんですが…。今後の勉強のためにもいい方法があればと思います。よろしくお願いします。

回答No.5

BLUEPIXY

すみません、ｍ（＿ ＿）ｍ
＃１は、＃２の方の指摘通り間違いですね。
12：00～12：40
15：00～15：10
の部分が反対になっています。
AND(退勤時間>=休憩終了時間,休憩開始時間>=出勤時間)*(休憩終了時間-休憩開始時間)
でないといけません。
申し訳ございませんでした。

お礼

いえいえ、謝らないで下さい。言われてみるとそうですね。私自身、頭が足りないもので単純な違いも分かりませんでした。複雑で分かりにくかったものをここまで解明な式に表してもらって感謝してます。少々の間違いはあってもこの式を即答してもらえて感動してます。

回答No.4

harukabcde

私が良く使う勤務表の技を紹介します

私の場合、残業計算に使っているのですが、
今回のような休憩にも応用できるので
紹介します

まず、
Ａ列・・出勤時刻
Ｂ列・・退勤時間
Ｃ列・・休憩時間
とし、
2行目に項目を入力
データは3行目から入力します

Ｄ列・・休憩１
Ｅ列・・休憩２
Ｆ列・・休憩３
用に用意します

D1に10:00と入力
D2に10:10と入力
E1に12:00と入力
E2に12:40と入力
F1に15:00と入力
F2に15:10と入力

これで準備ｏｋです

D3に
=IF(AND($A3<D$2,$B3>D$1),MIN($B3,D$2)-MAX($A3,D$1),0)
と入力してください

これは、
A3からB3（出勤時刻から退勤時間）の間に
D1からD2の時間が何分入っているかを計算しています
（実際にA3とB3に値を入れると判ります）
これが、出勤時刻に対する休憩１の時間です。

D3の数式を
D3:F33にコピーします
（$を意識して入れておりますのでD3をそのままコピペして構いません）

次に休憩時間の計算ですが、
C3に
=SUM(D3:F3)
と入力し
C33までコピーしてください

あとは
A3からB33まで1ヶ月入力すればＯＫです。

今後、休憩時刻が変更になった場合は、

D1に10:00と入力
D2に10:10と入力
E1に12:00と入力
E2に12:40と入力
F1に15:00と入力
F2に15:10と入力

ここを変更するだけでＯＫです。

余談ですが、本来の使い方である残業計算は、
休憩時刻を残業の時刻に変更するだけです。
G列を使用して、D1を複写するだけ。
C列とD列の間に残業の列を追加し、G列を参照するかたちにします。

お礼

自力で悩み考えややこしい式で結局完成しなかったものがこんな短い式で集約できてしまうんですね。素晴らしい！の一言です。MINとMAXのこの使い方は知りませんでした。私の場合、残業となるのは勤務時間を８時間を過ぎてからとなります。通常勤務では１６：３０に退社しますが１６：３０から１０分は休憩時間となるので残業となるのは１７：４０を過ぎてからとなります。１７：４０以降勤務するのは月に１回あるかないかペースなんですがね。(^^ゞ教えていただいた関数を使って残業の計算式も入れてみます。いろいろとありがとうございました。

回答No.3

AloneAgain

すみません、No.2です。

No.2の D2 に入れる数式を
↓に訂正してください。
---------------------------------------------
=IF(COUNT(B2:C2)<>2,"",(B2<=$J$1)*(C2>=$K$1)*($K$1-$J$1)+(B2<=$J$2)*(C2>=$K$2)*($K$2-$J$2)+(B2<=$J$3)*(C2>=$K$3)*($K$3-$J$3))
---------------------------------------------
（結果は同じですが、休憩時間の変更があった場合、
J1：K3 の時間を変更するだけで済みます。
→ 数式の変更が必要ありません）

回答No.2

AloneAgain

こんにちは、横から失礼します。

ちょっと確認ですが・・・
No.1の方の数式って合ってますか？
もしかしたらウチのおバカなEXCELが
おバカな答えを出しているだけかもしれませんが・・・
*
たとえば、9：00 － 15：00 の勤務の場合、
休憩時間は 50分ですから、実働時間は 5：10 ですよね？
これが No.1の方の式だと 5：00 になりませんか？

さらに 9：00 － 12：00 の場合、
休憩時間は 10分だけですから 2：50 のはずなのに
2：10 にまでけずられてしまいます(T_T)
*
No.1の方の数式をそのまま使うとすれば
↓が正しいのではないでしょうか？
--------------------------------
=C2-B2-(AND(C2>=TIMEVALUE("10:10:00"),TIMEVALUE("10:00:00")>=B2)*TIMEVALUE("00:10:00")+AND(C2>=TIMEVALUE("12:40:00"),TIMEVALUE("12:00:00")>=B2)*TIMEVALUE("00:40:00")+AND(C2>=TIMEVALUE("15:10:00"),TIMEVALUE("15:00:00")>=B2)*TIMEVALUE("00:10:00"))
--------------------------------
カンちがいでしたらすみません。
***
***
ついでに、どうでもいいことかもしれませんが・・
休憩時間のトータルを別のセルに出して
（退社時間）－（出社時間）－（休憩時間）
とした方が数式がすっきりすると思います。
*
たとえば規定の休憩時間を
J1：J3 に、それぞれ 10：00、12：00、15：00、
K1：K3 に、それぞれ 10：10、12：40、15：10 と入力しておいて
出社時間が B2、退社時間が C2、
休憩時間が D2、実働時間を E2 とすれば
（D列、E列も表示形式を「時刻」にしておいてください）

D2に↓
--------------------------------
=IF(COUNT(B2:C2)<>2,"",(C2>=$K$1)*(B2<=$J$1)*("0:10")+(C2>=$K$2)*(B2<=$J$2)*("0:40")+(C2>=$K$3)*(B2<=$J$3)*("0:10"))
--------------------------------
以下、必要分オートフィルでコピー

E2に↓
--------------------------------
=IF(COUNT(B2:D2)<>3,"",C2-B2-D2)
--------------------------------
以下、必要分オートフィルでコピー

お礼

No.1の方の式では私も正確には休憩時間は求められませんでした(T_T)労働時間も単純な引き算とは答えが違ってしまい何か部分的になおせば使えるかとやってはみましたがだめでした…。
教えていただいた式で
退社時間－出勤時間－休憩時間で求める労働時間と一致し休憩時間も正確に表示されました。ＰＣ初心者で関数歴半年のオバサンなもので初歩的なものにつまづいてしまい教えてもらってありがたいです。TIMEVALUEも知らなかったので勉強になりました。（時間表示をセルの書式設定から表示形式で時間の数値を標準に直して式に導入していました。）参考にさせていただきます。

回答No.1

BLUEPIXY

B2が出勤時間で
C2が退勤時間の時
以下の式で求められます。
=C2-B2-(AND(C2>=TIMEVALUE("10:10:00"),TIMEVALUE("10:00:00")>=B2)*TIMEVALUE("00:10:00")+AND(C2>=TIMEVALUE("12:00:00"),TIMEVALUE("12:40:00")>=B2)*TIMEVALUE("00:40:00")+AND(C2>=TIMEVALUE("15:00:00"),TIMEVALUE("15:10:00")>=B2)*TIMEVALUE("00:10:00"))
基本は、
退勤時間－出勤時間で
退勤時間＞＝各休憩時間＞＝出勤時間の時、休憩時間を引いています。

お礼

すごい！！早い回答をいただき驚きました。実はこの数日試行錯誤しながらも解決せず行きつけのＰＣ教室で先生に聞いたら先生が悩んだまま１時間過ぎ、先生の宿題になってしまいました。ＩＦ関数ではないんですね。この式をそのまま使わせていただきます。本当にありがとうございました。