[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『エクセルで勤務表作成』（もこもこ）
Excel2003 WindowsXPを使用してます。
エクセルで勤務表を作成してるのですが、過去ログ見てもなかなか条件一致がなく 以前質問しまして自分なりに考えましたがなかなか完成しなかったのでまた質問 させていただきました。 夜勤者の深夜時間や労働時間がうまく出なくて困ってます。 4つ勤務体制があるのですが 1直 ＝8：30～17：15 休憩時間12：00～13：00 2直 ＝17：00～1：45 休憩時間20：30～21：30 3直 ＝1：00～9：00 休憩時間5：00～5：45 変2直＝19：00～3：45 休憩時間22：30～23：30 深夜時間が22：00～5：00です。 15分単位で計算します。 変2直のみ休憩時間が深夜時間で1時間差引になります。 3直は45分休憩になります。
勤務時間を始業時間がE列 終業時間がH列に入力してます。
｢2直の場合｣①17：00～1：45の勤務の場合 Y列 Z列 AB列 AD列 AF列 AH列 通常勤務時間 早出残業時間 深夜時間 遅刻早退 休出時間 休出（法定） 4.00 0.00 3.75
｢3直の場合｣②1：00～10：00の勤務の場合（1時間残業） Y列 Z列 AB列 AD列 AF列 AH列 通常勤務時間 早出残業時間 深夜時間 遅刻早退 休出時間 休出（法定） 4.25 1.00 4.00
夜中の1：00は25：00と入力ではなく1：00と入力します。
(IF(COUNT(E13:H13)=2,MAX(0,MIN(INT(E13)+"29:00",H13)-MAX(INT(E13) +"22:00",E13)) +MAX(0,MIN(INT(E13)+"5:00",H13)-E13),""))*24 と深夜時間のAB列に入れてみましたが結局0と反応してしまいます。
どなたか分かる方アドバイスお願いします。
前回のご質問と少し違いますね。
＞ 15分単位で計算します。 これは、「時刻の切上げ・切捨て」ですか、 それとも「時間の切上げ・切捨て」ですか？
時刻の場合、人間が手入力の際に調整するのですか、 それとも、データは端数のままで、今回の数式の中で調整するのですか？
＞変2直のみ休憩時間が深夜時間で1時間差引になります。 これは問答無用で１時間引いていいと云うことですか？ 休憩前や、休憩中に急用が生じて帰っちゃったりする事はないですか？ そう云うことがあった時、どんな時刻データが入力されるのですか？
(半平太) 2011/08/08 11:38
時刻の切り上げ切り捨てで通常勤務時間などは時間に変換なんですけど・・・・
＞時刻の場合、人間が手入力の際に調整するのですか、 ＞それとも、データは端数のままで、今回の数式の中で調整するのですか？ 時刻を担当が15分単位で切り捨て入力していきます。
深夜時間に1時間差引でもいいんですが、 ＞休憩前や、休憩中に急用が生じて帰っちゃったりする事はないですか？ ありえます。 2直ですと17：00から1：45なんですが17：00～22：00と22：00～1：45に分かれまして 17：00～22：00は出勤せずに22：00～1：45のみ出勤したりその逆もありえます。
3直ですと1：00から9：00ですが1：00～5：00と5：00～9：00に分かれまして 1：00～5：00は出勤せずに5：00～9：00のみ出勤したりその逆もあり得ます。
変2直ですと19：00から3：45ですが19：00～22：00と22：00～3：45に分かれまして 19：00～22：00は出勤せずに22：00～3：45のみ出勤したりその逆のありえます。 前半出勤のや後半出勤のみの場合を半日年休といいます。
そのほかに2直ですと1：45が終了時間ですが0：00に早退したり17：00出勤ですが19：00に出勤して 遅刻したりなどはあり得ます。 早退は稀ですが遅刻はよくあります。 休憩中の中抜けなどはないに等しいです。 残業は基本労働時間が7.75時間を超えた場合のみ残業が付く形になります。
計算式を簡単にするため、AJ:AN列に以下の数式を入力するものとします。
(1) AJ7セル =IF(AN7="","",CHOOSE(SUMPRODUCT((K7:M7="○")*{1,2,3})+1,12,20.5,22.5,5)/24)
(2) AK7セル =IF(AN7="","",AJ7+"1:00"-"0:15"*(M7="○"))
(3) AL7セル =IF(AN7="","",(H7+(H7<E7)-E7)*24-AM7)
(4) AM7セル =IF(AN7="","",(MAX(0,MIN(H7+(H7<E7),AK7)-MAX(E7,AJ7)))*24)
(5) AN7セル =IF(COUNT(E7,H7)<2,"",7.75-(M7="○")*0.5)
次に本来の数式を入力します。
(1) Y7セル =IF(AN7="","",AL7-AB7) (2) Z7セル =IF(AN7="","",MAX(0,AL7-AN7)) (3) AB7セル =IF(AN7="","",IF(H7,(MAX(0,MIN(H7+(H7<E7),"5:00")-E7)+MAX(0,MIN(H7+(H7<E7),"29:00")-MAX(E7,"22:00")))*24-(L7="○")*AM7,0))
<結果図> 行 __E__ _F_ _G_ __H__ _I_ _J_ _K_ __L__ _M_ _ __Y__ __Z__ _AA_ __AB__ _AC_ _AI_ _ AJ _ __AK__ __AL__ __AM__ __AN__ 6 始業 終業 2直 変2直 3直 通常 早残 深夜 休始 休終 実働 実休 既定 7 17:00 1:45 ○ 4.00 0.00 3.75 20:30 21:30 7.75 1.00 7.75 8 1:00 10:00 ○ 4.25 1.00 4.00 5:00 5:45 8.25 0.75 7.25 9 22:00 4:00 ○ 0.00 0.00 5.00 22:30 23:30 5.00 1.00 7.75 10 8:30 17:15 7.75 0.00 0.00 12:00 13:00 7.75 1.00 7.75 11 19:00 3:45 ○ 3.00 0.00 4.75 22:30 23:30 7.75 1.00 7.75 12 1:00 9:00 ○ 3.25 0.00 4.00 5:00 5:45 7.25 0.75 7.25
(半平太) 2011/08/08 16:23
半平太さん完璧に出来ました。ありがとうございました。
私の頭は混乱状態でしたがすっきりしました。
分からないところがありまして
AJ7セル =IF(AN7="","",CHOOSE(SUMPRODUCT((K7:M7="○")*{1,2,3})+1,12,20.5,22.5,5)/24)
↑ ↑
この1,2,3の意味と↑
1,12,20.5,22.5,5の始めの1は何でしょうか？
＞ AJ7セル =IF(AN7="","",CHOOSE(SUMPRODUCT((K7:M7="○")*{1,2,3})+1,12,20.5,22.5,5)/24)
＞ ↑ ↑
＞ この1,2,3の意味と↑
＞ 1,12,20.5,22.5,5の始めの1は何でしょうか？
AJ列は各シフトの休憩の開始時刻を求めるものです。
シフトの判定はK,L,M列を見るのですが、どう云う訳か「１直」用の列が在りません。
※ 在るなら、それはJ列のハズで、=CHOOSE(MATCH("○",J7:M7),12,20.5,22.5,5)/24 で簡単に済むのですけどね。
そこで、まず「○」がある位置を探ります。
K,L,Mをそれぞれ1,2,3番目と考え、該当がなければ0番目と認識することにします。
その式が SUMPRODUCT((K7:M7="○")*{1,2,3}) です。
その式で「変2直」のケースに当てはめると、こんな計算過程になります。
(K7:M7="○")*{1,2,3} ⇒ {FALSE,TRUE,FALSE}*{1,2,3}) ⇒ {0,2,0}
その結果を SUMPRODUCT({0,2,0}) で合計すれば、結果は2となり、
2(番目、つまり変2直) に○が入っていることが分かります。
もし、○が皆無なら、{FALSE,FALSE,FALSE}*{1,2,3}) ⇒ {0,0,0} で合計は0になります。
以上で「○」が何番目にあるかは分かったのですが、それをどう対応する開始時刻に換えるかですね。 一つの方法がCHOOSEを使う案です。他にはINDEX関数なんかが考えられます。
CHOOSEは1番目から指定しますので、0番目なんかがあったら迷惑です。 そこで1を足し、0,1,2,3→1,2,3,4番目に変える為に「+1」加算したのです。
＞ 1,12,20.5,22.5,5の始めの1は何でしょうか？ つまり、12の前に1があるのではなく。Sumpruductの結果に加算する1です。そこまでがChooseの第一引数です。 Chooseの第2引数は12です。つまり(0番目→加算後1番目)の開始時刻が12時って云うことです。
(半平太) 2011/08/09 15:44
<追記> 考えたら、4種類しかないので、普通にIFの入れ子の数式にした方が簡明でした。 =IF(AN7="","",IF(K7="○",20.5,IF(L7="○",22.5,IF(M7="○",5,12)))/24)
返事が遅くなりまして。。。 完璧に出来ました。 かなり高度な技ですね。まだまだ私も勉強不足です。
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.