Excel（エクセル）活用コミュの勤怠管理表

こんばんわ。
過去スレも見てみましたが、ないようでしたので質問させていただきます。

夜勤を伴う現場の勤怠管理表を作成しているのですが、状況によっては22時以降の出勤であったり、退勤が早朝５時をまわっていたりすることもあります。

そうでなければ、22時以降の労働時間を算出するだけで良いのですが…

パターンとしては以下の４つです。

1.出勤が22時以前で、退勤が29時以前。
2.出勤が22時15分以降で、退勤が29時以前。
3.出勤が22時以前で、退勤が29時以降。
4.出勤が22時15分以降で、退勤が29時以降。
※出退勤は15分単位
※入力は『9:15』ではなく、『9.25』といった感じで0.25刻みで行っています。

22時15分以降の出勤や29時以降の退勤は頻繁にあるわけではないのですが、あらゆる事態に備えて完璧なものにしておきたいのです。

IF関数で作成しようと思ったのですが、いろんなパターンを考えているうちに頭がパンクしてしまいました

長くなりましたが、どなたか助けて下さいm(_ _)m

コメント(13)

最初
全て
最新の40件

[1] mixiユーザー 10月02日 21:54

IF関数で　何を　求めようとお思いになったのですか？
単に労働時間を算出するだけで良いのであれば、

出勤時刻　A1=21.5
退勤時刻　B1=29.75

なら

労働時間　C1=B1-A1

で 8.25 が求められます。IFは必要ありません。

[2] mixiユーザー 10月02日 22:00

> Hal (ねこ使い)さん
説明不足ですいません。
勤怠管理表から労務費までを算出したいのです。
深夜時間帯に入りますので、割増賃金が発生します。

その深夜時間帯にあたる労働時間を算出したいのです。

[3] mixiユーザー 10月02日 22:13

4つのパターンで割増賃金が違うということでしょうか？
であればAND関数で条件を指定してIF関数で分岐すればよいと思います
AND関数で条件指定するときは、比較演算子の使い分けをしっかりすれば出来ると思います

[4] mixiユーザー 10月02日 22:20

> マリ男さん

そうです。
例えば、６時間労働したとします。
18時に出勤して24時までの６時間。
22時30分に出勤して、28時30分の６時間。
23時30分に出勤して、29時30分までの６時間。

どれも同じ６時間ですが、割増賃金はそれぞれ変わってきますよね？

そこまで網羅出来ればと思っています。

[5] mixiユーザー 10月02日 22:23

頭がパンクする時は、最初から全部をいっぺんにやろうとせず、１パターンづつコツコツできる事を確認してからやった方が良いですよ。
１パターン目の時に割増賃金を計算する計算時間は"=b-a"ですが、"b=B1"でいいのか？　"a=A1"でいいのか？　違う時は、それぞれ幾つに変更したらいいのか？
1ステップずつ考えた方が良いと思いますよ。
　

[6] mixiユーザー 10月02日 22:36

IF関数をネストしていけば出来ますが、まず
パターン1を1行目
パターン2を2行目
という形で書いていって、それぞれ
=IF(条件,1,0)
などのように、条件を満たした場合 1 を返し、満たさなければ 0　を返すなどのような
式を作って、満たす数値・満たさない数値を検証し
結果が合っていれば、それをネストしていけばよいと思います

[7] mixiユーザー 10月03日 01:09

22時以前は割り増し（割増率ｘ％）
29時以降も割り増し（割増率ｙ％）

みたいな感じでしょうか。
だとしたら、総労働時間のうち22時以前の労働時間と、
29時以降の労働時間がそれぞれどれだけかが分かれば、
計算できそうですね。
1のコメントの通り入力されているとしたら、

D1=((22-A1)+((22-A1)^2)^0.5)*0.5

これで22時以前の労働時間 0.5 が求められます。

[8] mixiユーザー 10月03日 02:20

> Hal (ねこ使い)さん
29時以降は深夜時間帯ではないので、残業時間帯でない限り、割増は発生しません。

[10] mixiユーザー 10月03日 05:37

>2 その深夜時間帯にあたる労働時間を算出したいのです。

↓こういう事でしょうか？

　A　　　B　　C　　　D　　　　　E
1 出勤　退勤　～22時 22時～29時 29時～
2 21.00 28.00 1.00　 6.00 　　　0.00
3 21.00 30.00 1.00　 7.00 　　　1.00
4 23.00 28.00 0.00　 5.00 　　　0.00
5 23.00 30.00 0.00　 6.00 　　　1.00
6 18.00 21.00 3.00　 0.00 　　　0.00
7 30.00 33.00 0.00　 0.00 　　　3.00

C2 =IF(B2>=22,22,B2)-IF(A2>=22,22,A2)
D2 =IF(B2>=29,29,IF(B2>=22,B2,22))-IF(A2>=29,29,IF(A2>=22,A2,22))
E2 =IF(B2>=29,B2,29)-IF(A2>=29,A2,29)

(>9 と似てますが ^^;)

[11] mixiユーザー 10月03日 19:10

2>その深夜時間帯にあたる労働時間を算出したいのです。

突き詰めると、こうなりました。
=IF(B2<22,0,MIN(B2,29)-MAX(A2,22))

29時以降の出勤(無いか…)も考慮すると、
=IF(OR(A2>29,B2<22),0,MIN(B2,29)-MAX(A2,22))

[12] mixiユーザー 10月03日 21:22

なるほど、です ^^
コメント10の改良に使ってみました。

C2 =MIN(B2,22)-MIN(A2,22)
D2 =MIN(29,MAX(B2,22))-MIN(29,MAX(A2,22))
E2 =MAX(29,B2)-MAX(29,A2)

ログインすると、みんなのコメントがもっと見れるよ