[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『遅刻・早退計算』（なやめ）
エクセルにてタイムカードの管理・活用をしたいのですが、遅刻の計算式を教えて下さい。
基本8：20〜17：00（7.5Ｈ勤務）
【遅刻】
8：21〜8：50＝-0.5
8：51〜9：20＝-1.0
9：21〜9：50＝-1.5
9：51〜10：30＝-2 (朝の出勤が8：20で10：00〜10分休憩です)
10：31〜11：00＝-2.5
11：01〜11：30＝-3.0
11：31〜12：50＝-3.5 (12：00〜50分まで昼休憩です)
12：51〜13：20＝-3.5/-0.5 と表示したい(合計は-4.0になりますが・・)
13：21〜13：50＝-3.5/-1.0
13：51〜14：20＝-3.5/-1.5
14：21〜14：50＝-3.5/-2.0
14：51〜15：30＝-3.5/-2.5 (15：00〜10分休憩です)
15：31〜16：00＝-3.5/-3.0
16：01〜16：30＝-3.5/-3.5
16：31〜17：00＝-3.5/-4.0
17：00以降の出勤は全て-3.5/-4.0となります。
＜ 使用 Excel：Excel2010、使用 OS：unknown ＞
> -3.5/-0.5 と表示したい(合計は-4.0になりますが・・)
よくわかりませんけど、後から集計とかしないんですかね？
どっちにしても、どこかに対応表を作って VLOOKUP で検索するのが一番簡単なような･･･
F G 1 2 8:21 -0.5 3 8:51 -1.0 4 9:21 -1.5 5 9:51 -2.0 6 10:31 -2.5 7 11:01 -3.0 8 11:31 -3.5 9 12:51 -3.5/-0.5 10 13:21 -3.5/-1.0 11 13:51 -3.5/-1.5 12 14:21 -3.5/-2.0 13 14:51 -3.5/-2.5 14 15:31 -3.5/-3.0 15 16:01 -3.5/-3.5 16 16:31 -3.5/-4.0 ↑ F2:G16 に作成
出勤時刻がA2セルだとして
=IFERROR(VLOOKUP(A2,$F$2:$G$16,2,TRUE),"")
とか。 （笑） 2016/06/08(水) 00:27
> VLOOKUPだと例えば8：21〜8：50は-0.5なので、8：21・8：22・8：23・・・-0.5の様に > 翌朝の8：20まで設定するとエクセルが重くなる
どういう意味なんですかね。 もしかして8:21〜1分刻みで表を作成すると思ってませんか？ 作成するのは例に挙げた F2:G16の表、その15行のみ VLOOKUPの「検索の型」はTRUE
これで出勤時刻が 8:21〜8:50 は「-0.5」になると思いますけど、試してみましたか？ > 同じ出勤時間でも退社時間によって遅刻-7.5になるパターンと、ならないパターンが出てきてしまいます。
こんなこと最初の質問に書いてありましたっけ？ > 朝5：00〜8：20までの勤務なら3.5時間勤務なので遅刻-7.5とするわけです。 > 朝5：00〜17：00までの勤務なら遅刻なしとなります。
これもよくわかりませんが、同じ出勤時刻でも 退社時刻が「8:20以内」だったら「-3.5/-4.0」にしたいってこと？
A B C 1 出勤 退勤 2 5:00 8:20 -3.5/-4.0 3 5:00 11:00 4 15:00 3:00 -3.5/-2.5 ↓ の式で上のC列のようになります（条件がわからないのでテキトー）
C2 =IF(COUNT(A2:B2)<2,"",IFERROR(VLOOKUP(A2+(A2<B2)*("8:20"-B2>=0),$F$2:$G$16,2,TRUE),""))
それでも VLOOKUP ではダメってことなら、これ以上の対応はできません。 悪しからず。 （笑） 2016/06/08(水) 11:33
解答ありがとうございます。説明不足と説明足らずにてご迷惑をお掛けしてすいません。TRUEの意味もしらなっかたので勉強になります。ご解答頂いたVLOOKUPでやってみます。ありがとうございました。
（なやめ） 2016/06/08(水) 13:53
A B 1 8:21 -0.5 2 8:50 -0.5 3 8:51 -1.0 4 9:20 -1.0 5 9:21 -1.5 6 9:50 -1.5 7 9:51 -2.0 8 10:30 -2.0 9 10:31 -2.5 10 11:00 -2.5 11 11:01 -3.0 12 11:30 -3.0 13 11:31 -3.5 14 12:50 -3.5 15 12:51 -3.5/-0.5 16 13:20 -3.5/-0.5 17 13:21 -3.5/-1.0 18 13:50 -3.5/-1.0 19 13:51 -3.5/-1.5 20 14:20 -3.5/-1.5 21 14:21 -3.5/-2.0 22 14:50 -3.5/-2.0 23 14:51 -3.5/-2.5 24 15:30 -3.5/-2.5 25 15:31 -3.5/-3.0 26 16:00 -3.5/-3.0 27 16:01 -3.5/-3.5 28 16:30 -3.5/-3.5 29 16:31 -3.5/-4.0 30 17:00 -3.5/-4.0
B1 =TEXT(MAX(-3.5,CEILING(TEXT(A1-IF(A1>="10:20"*1,"0:10")-"8:20","h:mm"),"0:30")*-24),";-0.0;")
&TEXT(MAX(-4,CEILING(TEXT(A1-IF(A1>="15:20"*1,"0:10")-"12:50","h:mm;!0"),"0:30")*-24),";!/-0.0;")
下へコピー。 （GobGob） 2016/06/08(水) 14:15
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.