こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

IF関数を使った勤務時間表

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

投稿日時 - 2005-04-30 00:48:24

QNo.1359730

暇なときに回答ください

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

すみません、m(_ _)m
#1は、#2の方の指摘通り間違いですね。
12:00~12:40
15:00~15:10
の部分が反対になっています。
AND(退勤時間>=休憩終了時間,休憩開始時間>=出勤時間)*(休憩終了時間-休憩開始時間)
でないといけません。
申し訳ございませんでした。

投稿日時 - 2005-04-30 13:34:07

お礼

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

投稿日時 - 2005-04-30 14:43:36

ANo.4

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

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


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

D列・・休憩1
E列・・休憩2
F列・・休憩3
用に用意します

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

これで準備okです

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

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

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

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

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

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

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

ここを変更するだけでOKです。

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

投稿日時 - 2005-04-30 12:59:59

お礼

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

投稿日時 - 2005-04-30 14:36:18

ANo.3

すみません、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 の時間を変更するだけで済みます。
→ 数式の変更が必要ありません)

投稿日時 - 2005-04-30 08:26:09

ANo.2

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

ちょっと確認ですが・・・
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)
--------------------------------
以下、必要分オートフィルでコピー

投稿日時 - 2005-04-30 06:04:30

お礼

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

投稿日時 - 2005-04-30 11:19:17

ANo.1

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"))
基本は、
退勤時間-出勤時間で
退勤時間>=各休憩時間>=出勤時間の時、休憩時間を引いています。

投稿日時 - 2005-04-30 01:05:18

お礼

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

投稿日時 - 2005-04-30 01:21:38

あなたにオススメの質問