[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『勤務時間の集計について』（らすかる）
エクセルで会社の作業員の作業時間集計表を作り管理したいのですが、難しすぎて困っています。過去ログを検索していて気付いたのですが、通常の会社の勤務形態と条件が少し違うので、もしかすると私の会社の勤務形態ではエクセルで集計表は作れないのでしょうか？通常の勤務時間(定時)は 8:00～17:00 なのですが、残業時間の条件として、深夜残業となる22:00までに8時間以上勤務しなければ、残業時間になりません。出社時刻・休憩時間・退社時刻を入力するだけで、普通勤務時間・残業時間・深夜残業時間が計算されるものを作りたいのですが、可能でしょうか？ どなたか教えて下さい。
エクセルのバージョンはExcel2003,
OSのバージョンはWindowsXPです。
作業列(E～G)を使っています。 E3:G4セルに区切りとなる時刻を入力し、E5:G5セルにそれぞれの勤務時間を求めます。 E5セル=IF(OR(COUNT($B$5:$C$5)<2,$B5-E$4>=0,$C5-E$3<=0),"",--TEXT(MIN($C5,E$4)-MAX($B5,E$3),"h:mm")) G5セルまでコピー H5セル=MIN(SUM(E5:G5)-D5,"8:00") I5セル=IF(SUM(E5:F5)-D5-H5>0,SUM(E5:F5)-D5-H5,0) J5セル=SUM(E5:G5)-D5-SUM(H5:I5) こんな感じになりましたが、いかがでしょうか？ 細かい検証はしていませんので、不都合が有ると思いますが叩き台ということで・・・ (Hatch) B C D E F G H I J 2 出社時刻 退社時刻 休憩時間 普通 残業 深夜 普通勤務 残業勤務 深夜勤務 3 8:00 17:00 22:00 4 17:00 22:00 30:00 5 17:00 27:00 1:00 5:00 5:00 8:00 0:00 1:00
可能かどうかと聞かれれば可能だと思います。 ただ、勤怠管理に関しては細かい条件をきちんと提示していただく事が必要です。 時間は何分単位で切り上げとか、日をまたぐ勤務はあるとかなどなどです。 上記の文を見る限り、休憩時間も人や日によりまちまちなのかな～？と言う感じですが･･。 そうなるとまたややこしいかもです。 (gloomy)
gloomyさんの疑問どおり、日をまたぐ勤務もあり、休憩時間も人や日によりまちまちなのです。時間の切り上げに関しては、１分単位で(この表現が適切なのかしら？)実労働時間を集計したいのですが…。Hatchさんの方法で(数式・レイアウトをまるっとコピーさせて頂きました。)はりきってチャレンジしてみました。
8:00～17:00 の場合や、8:00～20:00、 8:00～25:00 などの場合や、休憩時間の人や日によりまちまちな所などは、すべてクリアできました。しかし、教えて頂いているのに大変恐縮なのですが、問題点がいくつか有りました。
私の条件の提示が、あまりにもおおざっぱだったのが原因ですね(T_T)スミマセンm(__)m
作業列B5:D5の 17:00～27:00、休憩 1:00(22:00～23:00に休憩したと仮定して)と入力した場合、結果は 普通勤務が5:00 深夜勤務が4:00 と、なってほしいのです。
休憩時間も 22:00以前か、22:00以降なのかを区別して入力しないと結果が違ってくることに気付きました。
また、早出(8:00前出勤)する場合があるのですが、作業現場がバラバラなので全員が早出するのではなく、何人かだけが早出する場合が多いのです。早出した分は普通残業として計算するのですが、例えば 7:00～16:00 休憩1:00 の場合は、8時間勤務なので 普通勤務8:00 のみとなります。このような場合はどうしたら良いですか？
細かい条件の提示は、考えれば考えるだけ難しくなってきて・・・上記の他にもきちんと提示すべき条件があるような気がするのですが．．．
今考えられるのはこれだけです。
みなさん、またアドバイスください(T_T)お願いしますm(__)m (らすかる)
E列に深夜休憩時間、F列に早出時間の作業列を挿入しました。 F5=IF(OR(COUNT($B$5:$C$5)<2,$B5-F$4>=0,$C5-F$3<=0),"",--TEXT(MIN($C5,F$4)-MAX($B5,F$3),"h:mm")) I5までコピー(ここまでは数式は前と同じ) J5=MIN(SUM(F5:H5)-D5,"8:00") K5=IF(SUM(F5:H5)-D5-J5>0,SUM(F5:H5)-D5-J5,0) L5=SUM(F5:I5)-(D5+E5)-SUM(J5:K5) こんな感じでしょうか? (Hatch) B C D E F G H I J K L 2 出社時刻 退社時刻 普通休憩 深夜休憩 早出 普通 残業 深夜 普通勤務 残業勤務 深夜勤務 3 6:00 8:00 17:00 22:00 4 8:00 17:00 22:00 30:00 5 8:00 25:00 1:00 1:00 9:00 5:00 3:00 8:00 5:00 2:00 6 17:00 27:00 1:00 5:00 5:00 5:00 0:00 4:00 7 7:00 16:00 1:00 1:00 8:00 8:00 0:00 0:00
例外条件と考えて、IF関数に条件を加えたらどうでしょう? J5=IF(AND(F5<>"",G5="",H5=""),0,MIN(SUM(F5:H5)-D5,"8:00")) K5=IF(AND(F5<>"",G5="",H5=""),F5,IF(SUM(F5:H5)-D5-J5>0,SUM(F5:H5)-D5-J5,0)) (Hatch)
>区切りとなる時刻の 8:00 をはさんでいるので無理ですか？ これは、8:00で区切って計算しているので、F,G列にでます。 後は、どのような条件で、普通、残業、深夜残業に振り分けるのかだけです。
>残業時間の条件として、深夜残業となる22:00までに8時間以上勤務しなければ、 >残業時間になりません。
>17:00～27:00、休憩 1:00(22:00～23:00に休憩したと仮定して)と入力した場合、 >結果は 普通勤務が5:00 深夜勤務が4:00 と、なってほしいのです。
>早出のみ 6:00～8:00 した場合、この２時間だけは８時間未満であっても、 >残業勤務で計算
今までの内容から・・・ 8:00～22:00間の勤務時間だけ、8時間以内は通常勤務、8時間を超える時間を残業時間とする。 22:00以降の勤務は、無条件で深夜残業時間とする。 出社時刻が8:00前の勤務は、無条件で残業時間とする。 ということでしょうか？ 他に条件がありませんか?
J5=MIN(SUM(G5:H5)-D5,"8:00") K5=SUM(F5:H5)-D5-J5 L5=SUM(F5:I5)-(D5+E5)-SUM(J5:K5) こんな感じでどうですか？ (Hatch)
これまでの条件の中で、1つ満たしていないものがありました。 >早出した分は普通残業として計算するのですが、例えば 7:00～16:00 休憩1:00 の場合は、 >8時間勤務なので普通勤務8:00 のみとなります。 この条件を加えて、以下のように修正しておきます。 J5=IF(COUNT(F5:G5,D5)=3,--TEXT(SUM(F5,G5)-D5,"h:mm"),--TEXT(MIN(SUM(G5:H5)-D5,"8:00"),"h:mm")) K5=--TEXT(SUM(F5:H5)-D5-J5,"h:mm")*1 L5=--TEXT(SUM(F5:I5)-(D5+E5)-SUM(J5:K5),"h:mm") 以下のような結果になります。 B C D E F G H I J K L 2 出社時刻 退社時刻 普通休憩 深夜休憩 早出 普通 残業 深夜 普通勤務 残業勤務 深夜勤務 3 6:00 8:00 17:00 22:00 4 8:00 17:00 22:00 30:00 5 8:00 17:00 1:00 9:00 8:00 0:00 0:00 6 8:00 20:00 9:00 3:00 8:00 4:00 0:00 7 8:00 25:00 1:00 1:00 9:00 5:00 3:00 8:00 5:00 2:00 8 17:00 27:00 1:00 5:00 5:00 5:00 0:00 4:00 9 6:00 8:00 2:00 0:00 2:00 0:00 10 6:00 9:00 2:00 1:00 1:00 2:00 0:00 11 7:00 10:00 1:00 2:00 2:00 1:00 0:00 12 7:00 16:00 1:00 1:00 8:00 8:00 0:00 0:00
＞正しく計算されないのですが・・・ 私の検証不足でしょう(^^;) 具体的に指摘いただければ確認したいと思います。 「--」は文字列の数字を数値(シリアル値)に変更しています。 「*1」や「VALUE関数」を使うのと同じですが入力が楽なためよく使われます。 (追加) こちら↓に分かりやすい説明がありました。 [[20040530061813]] 『「--」と「！」の意味?』(masabou5) (Hatch)
「--」の意味は↑の過去ログで勉強させて頂きました。まだまだ理解不足なので勉強中ですが・・・ ＞具体的に指摘いただければ確認したいと思います。 ですが、 8:00～16:00 休憩1:00 の時に Ｊ列は 7:00 でＯＫですがＫ列とＬ列が #VALUE!になってしまう 8:00～21:00 休憩1:00 の時に Ｊ列とＫ列は ＯＫですがＬ列が #VALUE!になってしまう 7:00～17:00 休憩1:00 の時に Ｊ列が 9:00 になってしまう 退社が17:00以降はＪ列が 9:00 になってしまうので、残業分（早出を含む）が1:00足りなくなってしまう 6:00～14:00 休憩1:00 の時に Ｊ列は 7:00 でＯＫですがＫ列とＬ列が #VALUE!になってしまう 6:00～16:00 休憩1:00 の時に Ｊ列が 9:00 になってしまい 退社が17:00以降はＪ列が 9:00 になってしまうので残業分（早出を含む）が1:00足りなくなってしまう 6:00～17:00 休憩1:00 の時に Ｊ列が 10:00 になってしまいＫ列とＬ列が #VALUE!になってしまう 6:00～18:00･･･････30:00 休憩1:00 まで Ｊ列が 10:00 になってまい退社が18時～20時の間は Ｌ列が #VALUE!になってしまう 具体的なものをあげてみましたが・・・・きっと今まで私が提示した条件が矛盾しているのが原因だと思います(T_T)Hatchさんを悩ませてしまって、本当に申し訳なく思っています・・・ Hatchさんが矛盾していると思う条件はどこですか?教えてください。（らすかる）
やっぱり検証不足だったようです。(というか、ほとんど検証はしていませんm(_ _)m） (1)小数誤差によるエラー(1秒を加えて計算するようにしました) (2)条件判定の不備(J5の式に8:00の上限を追加) が、あったようです。いや・・まだ他にもあるかも(^^;) 取りあえず・・・ J5=IF(COUNT(F5:G5,D5)=3,TEXT(MIN(SUM(F5,G5)-D5+"0:0:01","8:00"),"h:mm")*1,TEXT(MIN(SUM(G5:H5)-D5+"0:0:01","8:00"),"h:mm")*1) K5=TEXT(SUM(F5:H5)-D5-J5+"0:0:01","h:mm")*1 L5=TEXT(SUM(F5:I5)-(D5+E5)-SUM(J5:K5)+"0:0:01","h:mm")*1 これでどうでしょうか？ (Hatch) B C D E F G H I J K L 2 出社時刻 退社時刻 普通休憩 深夜休憩 早出 普通 残業 深夜 普通勤務 残業勤務 深夜勤務 3 6:00 8:00 17:00 22:00 4 8:00 17:00 22:00 30:00 5 8:00 16:00 1:00 8:00 7:00 0:00 0:00 6 8:00 21:00 1:00 9:00 4:00 8:00 4:00 0:00 7 7:00 17:00 1:00 1:00 9:00 8:00 1:00 0:00 8 6:00 14:00 1:00 2:00 6:00 7:00 0:00 0:00 9 6:00 16:00 1:00 2:00 8:00 8:00 1:00 0:00 10 6:00 17:00 1:00 2:00 9:00 8:00 2:00 0:00 11 6:00 22:00 1:00 2:00 9:00 5:00 8:00 7:00 0:00
時間の計算で小数誤差を生じる可能性があります。 そこで、小数を使わない計算に書き直してみます。(分単位の整数値に直してから計算します。) 前提条件：B～E列とF3:I4の時刻は手入力します。 F5=IF(OR(COUNT($B$5:$C$5)<2,$B5-F$4>=0,$C5-F$3<=0),0, TEXT((MIN(ROUND($C5*1440,0),ROUND(F$4*1440,0))-MAX(ROUND($B5*1440,0),ROUND(F$3*1440,0)))/1440,"h:mm")*1) I5セルまで右方向へコピーします。 J5=IF(AND(F5>0,G5>0,D5>0),TEXT(MIN(ROUND(F5*1440,0)+ROUND(G5*1440,0)-ROUND(D5*1440,0),480)/1440,"h:mm")*1, TEXT(MIN(ROUND(G5*1440,0)+ROUND(H5*1440,0)-ROUND(D5*1440,0),480)/1440,"h:mm")*1) K5=TEXT((ROUND(SUM(F5:H5)*1440,0)-ROUND(D5*1440,0)-ROUND(J5*1440,0))/1440,"h:mm")*1 L5=TEXT((ROUND(F5*1440,0)+ROUND(G5*1440,0)+ROUND(H5*1440,0)+ROUND(I5*1440,0)- ROUND(VALUE(D5)*1440,0)-ROUND(VALUE(E5)*1440,0)-ROUND(J5*1440,0)-ROUND(K5*1440,0))/1440,"h:mm")*1 # どの辺まで整数値にするか判断できなかったので、すべてを分単位での整数値に # 直してから計算するようにしています。 # これまた、検証はほとんどできていません(今までと結果は同じというのは確認済) (Hatch)
またまた、修正です。 F～Iの時間区分の計算値を時刻表示にしていましたが、これは分単位(整数値)のままでOK, そして後の計算にそのまま使えばいいので先ほどの式は下のように短くなります。 F5=IF(OR(COUNT($B$5:$C$5)<2,$B5-F$4>=0,$C5-F$3<=0),0, MIN(ROUND($C5*1440,0),ROUND(F$4*1440,0))-MAX(ROUND($B5*1440,0),ROUND(F$3*1440,0))) として、表示形式を「標準」でI5までコピー。 J5=IF(AND(F5>0,G5>0,D5>0),TEXT(MIN(F5+G5-ROUND(D5*1440,0),480)/1440,"h:mm")*1,TEXT(MIN(G5+H5-ROUND(D5*1440,0),480)/1440,"h:mm")*1) K5=TEXT((SUM(F5:H5)-ROUND(D5*1440,0)-ROUND(J5*1440,0))/1440,"h:mm")*1 L5=TEXT((SUM(F5:I5)-ROUND(D5*1440,0)-ROUND(E5*1440,0)-ROUND(J5*1440,0)-ROUND(K5*1440,0))/1440,"h:mm")*1 シートのデータ表示は下のように変わります。 B C D E F G H I J K L 2 出社時刻 退社時刻 普通休憩 深夜休憩 早出 普通 残業 深夜 普通勤務 残業勤務 深夜勤務 3 6:00 8:00 17:00 22:00 4 8:00 17:00 22:00 30:00 5 8:00 16:00 1:00 0 480 0 0 7:00 0:00 0:00 6 8:00 21:00 1:00 0 540 240 0 8:00 4:00 0:00 7 7:00 17:00 1:00 60 540 0 0 8:00 1:00 0:00 8 6:00 14:00 1:00 120 360 0 0 7:00 0:00 0:00 9 6:00 16:00 1:00 120 480 0 0 8:00 1:00 0:00 10 6:00 17:00 1:00 120 540 0 0 8:00 2:00 0:00 11 6:00 22:00 1:00 120 540 300 0 8:00 7:00 0:00 12 8:00 17:00 1:00 0 540 0 0 8:00 0:00 0:00 13 8:00 20:00 0 540 180 0 8:00 4:00 0:00 14 8:00 25:00 1:00 1:00 0 540 300 180 8:00 5:00 2:00 15 17:00 27:00 1:00 0 0 300 300 5:00 0:00 4:00 16 6:00 8:00 120 0 0 0 0:00 2:00 0:00 17 6:00 9:00 120 60 0 0 1:00 2:00 0:00 18 7:00 10:00 60 120 0 0 2:00 1:00 0:00 19 7:00 16:00 1:00 60 480 0 0 8:00 0:00 0:00
# 一晩寝たら気づいた・・・ちょっと遅い(^^;) (Hatch)
J5セルの数式の条件がいい加減だったようです。 （「IF(AND(F5>0,G5>0,D5>0),」←ここが適当でないということです。） さて、次の2つの場合で早出を残業とするのはどこを見て区別するのでしょうか? 早出を残業とする条件が分かりません。 (条件：A) >早出のみで ① 6:00～9:00 や、② 7:00～10:00 勤務した場合、 >① 普通勤務 1:00 残業勤務 2:00 ② 普通勤務 2:00 残業勤務 1:00 と計算する (条件：B) >①6:00～14:00 ②7:00～15:00 の時間帯勤務で休憩をとらなかった場合(８時間を超えていない場合) >普通勤務 8:00 とする。
>少数誤差が生じた場合、結果にどのような不具合が生じるのでしょうか・ 計算結果の時間数が増えたり減ったりすることがある→正しい計算結果が得られなことがある。 演算誤差については↓が参考になると思います。 http://pc21.nikkeibp.co.jp/special/gosa/
(Hatch)
Hatchさん、すみませんm(__)m 大きな間違いをしていました・・・私の最初の質問から間違っていたのです。本当に申し訳ありませんm(__)m 勤務時間集計表を作りたいのですが、その前に、現場人工集計表（げんばにんくしゅうけいひょう）とゆうものを作りたかったのです。 正確には、作らなければいけなかったのです。 どういったものかというと、単純に現場単位での作業時間集計なのですが・・・ 今までHatchさんにいろいろと考えて頂いたものは、１行で１日分の勤務時間を計算するもので、これは必ず必要なものなのですが、 その前に、 ○月○日（△曜日）誰がどこの現場で何時から何時まで勤務した（これが１行） これを基にデータ表というか一覧表を作り、その入力データから、現場単位の１ヶ月の集計表（詳細入り）、作業員別の１ヶ月の現場単位集計表（詳細入り） これが一目でわかるようなもの（シート別）を作り、最後に今まで考えて頂いた勤務時間集計表のようなものに勤務時間での集計をしたいのです。 私の会社（下請け業者）では、１日に１つの現場ではなく、幾つかの現場で作業する（１日に同じ人間が現場を移動して作業する）事がよくあるので、 １行に１日分ではなく、１作業員に対して１日分が何行かになる。という結果になにます。 その場合、１日の元請会社が数社になってしまうので、こちらの都合で現場を移動した場合に作業員自体は１日の合計が８時間以上勤務で残業分賃金がついても、 元請さんには残業分として請求できない事があります。 また、こちらは雪国なので、今時期は突発で『除雪』という仕事が入ります。 ほとんど早朝の 6:00から作業するのですが、その時は１日の勤務時間は関係なく 6:00～9:00 まで作業すれば 普通勤務 1:00 残業勤務 2:00 と計算して元請さんに請求できるわけです。 そして、除雪のような突発の仕事がなく、こちらの都合で元請Ａ社の現場から元請Ｂ社の現場に移動した場合 7:00～12:00 Ａ社の現場 普通勤務 5:00 13:00～18:00 Ｂ社の現場 普通勤務 5:00 のような結果になるのです。 突発で 17:00から仕事か入ったと時も、その日が８時間未満だったとしても17:00からの分は残業勤務分として請求できるわけです。 これを今まで（現在も）手で集計しているので、この日の早出は残業分で計算する・しないを任意にというか、そのケース･ケースでやっていたわけです。 だからHatchさんの >早出を残業とするのはどこを見て区別するのでしょうか? 早出を残業とする条件が分かりません。 という質問に、どう答えていいかわからず今までかかってしまいました。 長々と説明させて頂きましたが、とても複雑ですよね。申し訳ありません。 この説明で（まだ不十分だと思いますが）少しでもご理解頂けますでしょうか? どうか、見捨てずにアドバイスを下さい。お願いしますm(__)m （らすかる）
>『除雪』という仕事が入ります。 『除雪』の場合は8:00以前の勤務は残業として計上する。
>突発で 17:00から仕事か入ったと時も、その日が８時間未満だったとしても >17:00からの分は残業勤務分として請求できるわけです。 これは新たな条件のようです。『突発』なら17:00以降は残業として計上する。
これらを区別するセルが必要かと思います。 今までの表を下のように変更して、G列にその区別を入力することにします。 すると、下のような計算結果になりました。 L5=IF(OR(COUNT($H$5:$I$5)<2,$H5-L$4>=0,$I5-L$3<=0),0, MIN(ROUND($I5*1440,0),ROUND(L$4*1440,0))-MAX(ROUND($H5*1440,0),ROUND(L$3*1440,0))) O5までコピー P5=IF(G5="除雪",TEXT(MIN(M5-ROUND(J5*1440,0),480)/1440,"h:mm")*1, IF(G5="突発",TEXT((M5-ROUND(J5*1440,0))/1440,"h:mm")*1, TEXT(MIN(L5+M5+N5-ROUND(J5*1440,0),480)/1440,"h:mm")*1)) Q5=TEXT((SUM(L5:N5)-ROUND(J5*1440,0)-ROUND(P5*1440,0))/1440,"h:mm")*1 R5=TEXT((SUM(L5:O5)-ROUND(J5*1440,0)-ROUND(K5*1440,0)-ROUND(P5*1440,0)-ROUND(Q5*1440,0))/1440,"h:mm")*1
B C D E F G H I J K L M N O P Q R 2 月日 曜日 氏名 元請先 現場名 計算方法 出社時刻 退社時刻 普通休憩 深夜休憩 早出 普通 残業 深夜 普通勤務 残業勤務 深夜勤務 3 6:00 8:00 17:00 22:00 4 8:00 17:00 22:00 30:00 5 3月1日 火 氏名1 A社 あ 除雪 6:00 9:00 120 60 0 0 1:00 2:00 0:00 6 3月1日 火 氏名1 B社 か 10:00 15:00 1:00 0 300 0 0 4:00 0:00 0:00 7 3月1日 火 氏名1 C社 さ 突発 16:00 0:00 1:00 0 60 300 120 1:00 5:00 1:00 8 8:00 16:00 1:00 0 480 0 0 7:00 0:00 0:00 9 8:00 21:00 1:00 0 540 240 0 8:00 4:00 0:00 10 7:00 17:00 1:00 60 540 0 0 8:00 1:00 0:00 11 6:00 14:00 1:00 120 360 0 0 7:00 0:00 0:00 12 6:00 16:00 1:00 120 480 0 0 8:00 1:00 0:00 13 6:00 17:00 1:00 120 540 0 0 8:00 2:00 0:00 14 6:00 22:00 1:00 120 540 300 0 8:00 7:00 0:00 15 8:00 17:00 1:00 0 540 0 0 8:00 0:00 0:00 16 8:00 20:00 0 540 180 0 8:00 4:00 0:00 17 8:00 25:00 1:00 1:00 0 540 300 180 8:00 5:00 2:00 18 17:00 27:00 1:00 0 0 300 300 5:00 0:00 4:00 19 除雪 6:00 8:00 120 0 0 0 0:00 2:00 0:00 20 除雪 6:00 9:00 120 60 0 0 1:00 2:00 0:00 21 除雪 7:00 10:00 60 120 0 0 2:00 1:00 0:00 22 7:00 16:00 1:00 60 480 0 0 8:00 0:00 0:00 23 6:00 14:00 120 360 0 0 8:00 0:00 0:00 24 7:00 15:00 60 420 0 0 8:00 0:00 0:00
このようなデータを元にして、 >現場単位の１ヶ月の集計表（詳細入り）、作業員別の１ヶ月の現場単位集計表（詳細入り） を作成することになると思います。 これらの集計時に >こちらの都合で現場を移動した場合に作業員自体は１日の合計が８時間以上勤務で残業分賃金がついても、 >元請さんには残業分として請求できない事があります。 のような条件を加味すればよさそうな気がします。これは面倒そうな気がしますが・・・ (ぱっと見た感じ元請用と自社用の2通りの計算が必要？）
以上ですが、こちらではどのような勤務状態か推測もできませんので、検証はしておりません。 (Hatch)
Hatchさん、ありがとうございますm(__)m見捨てずに回答下さって本当に感謝してます。 考えて頂いた↑の数式と振分けでいけそうです。 ですが、休日出勤という条件を新たに追加したいのですが・・・（今さら・・の凡ミスで、お恥ずかしい＆申し訳ありませんm(__)m） 区別する条件としては、曜日が日曜日の場合だけ時間帯に関係なく作業時間を計算する。といった感じです。 C列に C5=IF(B5="","",TEXT(WEEKDAY(B5,1),"aaa"))下方向にコピー このような式を入れて、日付入力で曜日が表示されるようにしてみたのですが、不都合がありますか？ これからの作業に不都合でしたらご指導ください。 また、私の説明不足のせいなのですが、 >これは新たな条件のようです。『突発』なら17:00以降は残業として計上する。 この『突発』と前に挙げた『除雪』の条件は同じ（『突発』が6:00からあったり17:00以降に『除雪』の場合がある） なのでG列の計算方法に"除雪"や"突発"と区別しないで、 １種類で１文字(例えば、"特"でもいいです)を入れるだけで今のように計算されるようにできますか?
>のような条件を加味すればよさそうな気がします。これは面倒そうな気がしますが・・・ >(ぱっと見た感じ元請用と自社用の2通りの計算が必要？） >以上ですが、こちらではどのような勤務状態か推測もできませんので、検証はしておりません。
Hatchさんのご意見どおり２通りの計算が必要になると思います。たぶん(・_・;) 元請用と自社用での条件が違ってくるわけですから、そうですよね・・・ その場合、Hatchさんてきに、面倒な事になるのですか?それとも、入力作業が面倒という事ですか?
こちらで考えられるケースで検証した結果、これで休日出勤の振り分けとG列の計算方法の部分の修正が可能であれば、 元になるデータ入力として問題ないように思ったのですが・・・ 勤務状態の推測ができない事で問題があれば、ご説明させて頂きます。 ので、どうぞ宜しくお願いしますm(__)m （らすかる）
> C5=IF(B5="","",TEXT(WEEKDAY(B5,1),"aaa")) =IF(B5="","",TEXT(B5,"aaa")) で良いと思います。（WEEKDAY関数は不要。)
P5=IF(G5="特",TEXT(MIN(M5-ROUND(J5*1440,0),480)/1440,"h:mm")*1, TEXT(MIN(L5+M5+N5-ROUND(J5*1440,0),480)/1440,"h:mm")*1)) でどうでしょうか？ 「除雪」と「突発」が同じ計算でいいような気がして、 「突発」の条件を除いています。なお、「特」で区別するようにしています。 検証してみてください。
>曜日が日曜日の場合だけ時間帯に関係なく作業時間を計算する。 具体的にはどのような計算になるのでしょうか? 日曜日だったら、全て残業勤務とする？ どの勤務(普通,残業,深夜またはそれ以外)で計上するのですか?
また、祝日はどうするのですか？ それ以外に、休日と見なす日があるとかないとか・・・
# 後の集計方法やその時の計算は、後回し・・・ (Hatch)
Hatchさん、ご指導ありがとうございます。遅くなってしまい申し訳ありません。 曜日の表示をご指導頂いたとおり修正しました。 計算方法の区別も修正し検証した結果、問題なく計算できます。
休日出勤の件ですが、
>具体的にはどのような計算になるのでしょうか? >日曜日だったら、全て残業勤務とする？ >どの勤務(普通,残業,深夜またはそれ以外)で計上するのですか? 計算は、出社から退社までの合計（休憩を引いた）が求められれば良いです。 早出・残業、深夜などの時間帯の区別はありません。 休日出勤という列を設けて(Ｓ列が良いかと思うのですが)その列に計上されるようにしたいのですが・・・
>また、祝日はどうするのですか？ >それ以外に、休日と見なす日があるとかないとか・・・ 日曜日だけを休日と扱いますので、祝日などは無視してかまいません。 休日出勤＝日曜出勤です。 宜しくお願い致しますm(__)m （らすかる）
P～S列の数式に「IF(C5="日",・・・」を付けました。 P5=IF(C5="日",0,IF(G5="特",TEXT(MIN(M5-ROUND(J5*1440,0),480)/1440,"h:mm")*1, TEXT(MIN(L5+M5+N5-ROUND(J5*1440,0),480)/1440,"h:mm")*1)) Q5=IF(C5="日",0,TEXT((SUM(L5:N5)-ROUND(J5*1440,0)-ROUND(P5*1440,0))/1440,"h:mm")*1) R5=IF(C5="日",0,TEXT((SUM(L5:O5)-ROUND(J5*1440,0)-ROUND(K5*1440,0)-ROUND(P5*1440,0)-ROUND(Q5*1440,0))/1440,"h:mm")*1) S5=IF(C5="日",TEXT((SUM(L5:O5)-ROUND(J5*1440,0)-ROUND(K5*1440,0))/1440,"h:mm")*1,0) 以上でいかがでしょうか？ (Hatch)
Hatchさん、ありがとうございます。 休日出勤の条件もクリアできました。 とりあえず２週間分のデータ入力作業を試してみたのですが、作成していた表（まだ仮の状態ですが）より縦に長いものになりました。 表が足りなくなった、ということです。 ここにきて唐突な質問になりますが・・・ この長さは月によって、まちまちになるのものなんですけど、 １行目を入力したら、２行目が自動で作成される または、作成した表の最後の行を使うと自動で１行増える というような事は、マクロを使わなければ無利なのですか？ （らすかる）
ツール→オプション の 編集タブで 「リスト形式および数式を拡張する」にチェックを入れておけば データを入力したら、数式が自動で入力されませんか? 5～6行ほどはあらかじめ入力しておく必要があるようです。 # なお、この機能はExcel2002で確認しました。
例えば、L20～S20セルを選択して、フィルハンドルを下へドラッグすれば 計算式がコピーされるので、たいした手間ではないと思うのですが・・・ データの入力フォームを作って、マクロで計算させるとか、 使い勝手がいいようにマクロを作成するのもいいと思います。 (Hatch)
Hatchさん、長い間お返事も書かず申し訳ありません。 インターネットが使えない状況になってしまい・・・こんなに日があいてしまいました。 本当に申し訳ありません。 こちらの勝手な都合で恐縮なのですが、授業を再開して頂けないでしょうか・・・ （らすかる）
一件落着と思っていたのですが・・・ さて、質問は何でしょうか? (Hatch)
Hatchさん、ありがとうございますm(__)m嬉しいです(T_T)
Hatchさんの >「リスト形式および数式を拡張する」にチェックを入れておけば データを入力したら、数式が自動で入力されませんか? 試してみたら、自動で入力されました。この機能は知りませんでした・・・ありがとうございます。
質問は集計方法についてです。 まず、作業員別で集計をしようとオートフィルタという機能で試してみたのですが、 入力したデータ表を直接いじるのではなく、行単位で抽出されて、別のシートに転記される方法はありますか?
ご回答はゆっくりで構いません。 アドバイス下さい。（らすかる）
従業員数=シート数 となりそうですが、どのくらいのシート数が必要になりますか? それとも、必要なときに一人分のデータが抽出できればよいのでしょうか? (Hatch)
>従業員数=シート数 ということで、現在８シート必要ですが、この数は増減するものです。 必要なときに一人分のデータの抽出をしたい時もあると思うのですが、毎月、全員分の（作業員別で） データを抽出したいのです。 （らすかる）
フィルタオプションの設定を使ったデータ抽出です。 細かいところはいい加減です(^^;) 現場人工勤務表シートのボタンをクリックすると個人名でシートを追加して、 データを抽出&コピーします。 Sheet2は一人分のデータの抽出になっています。 サンプルは↓にNo.24 rasuka02.xls(約55KB)としてUpしました。 http://www.geocities.jp/hatch4700/index.html (Hatch) Sub Simei_get() Dim Simei(20) As String, myName As String Dim LastRow1 As Long, LastRow2 As Long, LastRow3 As Long Dim i As Long, j As Long Dim myFlg As Integer, myCount As Integer Dim shName As Object, NewWS As Worksheet '--------Check Name ------ LastRow1 = Sheets("現場人工勤務表").Range("B65536").End(xlUp).Row Simei(0) = Cells(5, 4).Value For i = 5 To LastRow1 For j = 0 To 20 If Simei(j) = Cells(i, 4).Value Then myFlg = 1: Exit For Next j If myFlg = 0 Then myCount = myCount + 1 Simei(myCount) = Cells(i, 4) End If myFlg = 0 Next i '--------NewSheet Add------ For i = 0 To myCount myFlg = 0 For Each shName In Worksheets If shName.Name = Simei(i) Then myFlg = 1 Exit For End If Next If myFlg = 0 Then Set NewWS = Worksheets.Add(after:=Worksheets(Worksheets.Count)) With NewWS .Name = Simei(i) End With End If Next i '--------Data Copy------ For i = 0 To myCount Sheets(Simei(i)).Select LastRow3 = Sheets(Simei(i)).Range("B65536").End(xlUp).Row Range("B1").Value = "氏名" Range("B2").Value = Simei(i)
If LastRow2 < 6 Then LastRow2 = 6
Sheets(Simei(i)).Range("B5:S" & LastRow2).ClearContents
Sheets("現場人工勤務表").Range("B2:S" & LastRow1).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B1:B2"), CopyToRange:=Range("B5"), Unique:=False
Next i
End Sub
Hatchさん、ありがとうございます。 まず、サンプルを使いながら練習してみます。 ので、もう少しお付き合い下さいm(__)m宜しくお願い致しますm(__)m （らすかる）
Hatchさん、すみません(T_T)とても嬉しく有難いサンプルだったのですが(T_T) Sheet2に氏名別でデータが抽出される時に（された後でもいいです(T_T)） さらに 日付・曜日・出社・退社・休憩・深休・普通勤務・残業勤務・深夜勤務・休日出勤が わかるように、一日分を一行に合計して表示する事はできますか？ 現場別でも同じように集計したいのですが・・・ 根本的に無理な発想でしょうか・・・・ また、Hatchさんのサンプルで説明させて頂きますが 現場人工勤務表シートで3月17日の下に 3月1日のデータを入力した場合 Sheet2には日付順で並べ替えられている、ということはできないのでしょうか・・・ Hatchさんに頼りっぱなしで本当にすいません(>_<) 少しづづですが、勉強しています。 どうか、Hatchさんの素晴らしい頭脳でお助け下さい。お願いしますm(__)m （らすかる）
出社・退社・休憩・深休を一日分を一行にまとめるのは形式的に無理ではないですか? 複数ある日だけ横に長くなって見にくいと思いますが・・・ (普通勤務・残業勤務・深夜勤務・休日出勤の合計なら横に広げなくても良いですが・・・)
集計はデータ→集計、並べ替えはデータ→並べ替えで対応できませんか? またVBAのコードはマクロの自動記録を利用して、どうにかなりませんか？
現場別の抽出は氏名の抽出と同じパターンでいけそうな気がしますので、 その部分を書き換えてみたらいかがでしょうか？
それらの途中で分からないことが出てきたら、また質問をされたらよいと思います。
(追加) Sheet2のVBAに並べ替え(日付の昇順)を付け加えると、下のようになりました。
マクロの自動記録で得られたコードのデータ範囲を書き換えています。
Sub Macro1()
Dim LastRow2 As Long
Dim LastRow1 As Long
LastRow1 = Sheets("現場人工勤務表").Range("B65536").End(xlUp).Row
LastRow2 = Sheets("Sheet2").Range("B65536").End(xlUp).Row
If LastRow2 < 6 Then LastRow2 = 6
Sheets("Sheet2").Range("B6:N" & LastRow2).ClearContents
Sheets("現場人工勤務表").Range("B2:S" & LastRow1).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B1:B2"), CopyToRange:=Range("B5:O5"), Unique:=False
'----並べ替え----
LastRow2 = Sheets("Sheet2").Range("B65536").End(xlUp).Row
Range("B5:O" & LastRow2).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortTextAsNumbers
End Sub
(Hatch)
Hatchさん、ご指導ありがとうございます。 データの並び替えできました。 Sheet2に抽出した後にですが。 （らすかる）
あっ、私がもたもた書いてる間にこんなに追加して頂いてたのですね。 ありがどうございますm(__)m驚きました(*_*)
>(普通勤務・残業勤務・深夜勤務・休日出勤の合計なら横に広げなくても良いですが・・・) これを参考に、明日からなんとか頑張ってみます。 現場別の集計も、コードの書き方から勉強して挑戦してみます。 わからなくなったら、ここに質問しにきます。 ので、その時はご指導・ご教授ください。Hatchさんの優しさにすっかり甘えてしまいました。 すみません・・・反省してますm(__)m （らすかる）
ももさん、まだ見て居られますかね？ 新規で御質問をなさるのが宜しいと思いますよ。 その際、休憩時間の話だけではなく、勤務時間全体に関してと 現在作っている式があるならそれも載せてみるのが良いと思います。
いくつかのサンプルデータと、それを使った場合の希望する結果も有ると アドバイスがもらえやすいかもしれませんね。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.