[[20130205220106]]　『勤務表』（さつき）　ページの最後に飛ぶ

『勤務表』（さつき）

 給与計算の表をエクセルで作りたいです。

 出社時間と休憩時間と退社時間を入力すると
 所定時間と時間外残業100/100時間外残業125/100が出て
 遅刻、早退早退時間も出るようにしたいです。

 勤務シフトは、5種類あり
 ☆Ａ9：00～17：15休憩1時間○所定時間7時間15分
 ☆Ｂ8：45～17：00休憩1時間○所定時間7時間15分
 ☆Ｃ12：00～20：15休憩1時間○所定時間7時間15分
 ☆Ｄ9：00～13：15休憩時間0○4時間15分
 ☆Ｅ16：00～20：15休憩時間0○所定時間4時間15分です。

 8時間以内が時間内残業で8時間以上が時間外残業で
 集計欄は3箇所あり
 所定時間、時間内残業、時間外残業と分けて集計でき
 平日勤務は区分1で土日祝日は区分2にして
 合計集計のときにそれぞれ区分１と２になるようにできないでしょうか？

 一人の人がシフトが毎日変わることもあるので大変です。どなたか教えて下さい。

 人数が500人くらいいるので今は、手書きのタイムシートをチェックしている状態ですが、
 月末には全て集計して本社に報告しなくてはいけません。
 最悪の場合は電卓で計算して入力しようを思っていますが人数が多いので間違えそうです。

 エクセルに詳しい人に助けて欲しく投稿しました。よろしくお願いします。

 ☆例Ａシフト　出社9：00休憩1時間退社17：15○所定7：15○時間内残業0○時間外残業0

 ☆出社9：00休憩時間1時間退社18：30○所定7：15○時間内残業0：45○時間外残業0：30

 質問が見辛かったので改行入れさせてもらいましたよ。

 では今から検討。

 (GobGob)

 いきなり疑問。

 入力欄が「出社」「退社」「休憩時間」のみ。

 シフトの判断はどうするの？

 9:00出社 17:15退社の人の「シフト」がAなのかBなのか？

 どう判断するの？ (Bなら遅刻だよね？)

 シフトは出社時間で決まるのなら計算で出来るだろうけど
 会社が指示してるのならシフト入力欄ないと計算では判断できないね。

 (GobGob)

 あと質問。

 たとえば、遅刻してきて残業した場合。

 所定時間 6:15（1:00遅刻） 残業時間（規定退社より後に退社した時間）が 1:45の場合

 ① 所定 6:15 時間内残業 0:45 時間外残業 1:00 遅刻 1:00 （遅刻したので所定時間が少ない）
 ② 所定 7:15 時間内残業 0:45 遅刻 1:00 (遅刻したけど所定時間分働いている）

 ①と②、どっちで処理するの？

 ※早い話が「所定時間は必ず7:15としてみるのか？」   ってことっす。

 (GobGob)

 あてずっぽ

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	日付	シフト	区分	出社	退社	休憩	時間内	残業A	残業B	遅刻	早退			出社	退社		休日
2	2月1日	A	区分1	9:00	17:15	1:00	7:15	0:00	0:00	0:00	0:00		A	9:00	17:15		1月1日
3	2月2日	B	区分2	8:45	17:00	1:00	7:15	0:00	0:00	0:00	0:00		B	8:45	17:00		1月14日
4	2月3日	C	区分2	12:00	20:15	1:00	7:15	0:00	0:00	0:00	0:00		C	12:00	20:15		2月11日
5	2月4日	D	区分1	9:00	13:15		4:15	0:00	0:00	0:00	0:00		D	9:00	13:15		3月20日
6	2月5日	E	区分1	16:00	20:15		4:15	0:00	0:00	0:00	0:00		E	16:00	20:15		4月29日
7	2月6日	A	区分1	8:45	20:00	1:00	7:15	0:45	2:15	0:00	0:00						5月6日
8	2月7日	B	区分1	7:15	23:20	1:00	7:15	0:45	7:05	0:00	0:00						7月15日
9	2月8日	C	区分1	12:00	21:15	1:00	7:15	0:45	0:15	0:00	0:00						9月16日
10	2月9日		休日														9月23日
11	2月10日	E	区分2	15:30	23:00		4:15	0:45	2:30	0:00	0:00						10月14日
12	2月11日	A	区分2	10:00	20:00	1:00	6:15	0:45	2:00	1:00	0:00						11月4日
13	2月12日	B	区分1	7:30	15:00	1:00	5:15	0:45	0:30	0:00	2:00						12月23日
14

 ・A列日付、B列にシフト、D/E/Fにそれぞれ出社/退社/休憩時間を入力。
 ・M:O列に例題のようなシフト表を作成。
 ・Q列に祝日リストを作成

 C2 =IF(B2="","休日","区分"&IF(OR(WEEKDAY(A2)={1,7},COUNTIF(Q:Q,A2)),2,1))
 下へコピー。

 G2 =IF(B2="","",MIN(VLOOKUP(B2,M:O,3,FALSE),E2+(D2>E2))-MAX(VLOOKUP(B2,M:N,2,FALSE),D2)-F2)
 H2 =IF(G2="","",MIN("0:45",MAX(0,E2-VLOOKUP(B2,M:O,3,FALSE))+MAX(0,VLOOKUP(B2,M:N,2,FALSE)-D2)))
 I2 =IF(G2="","",MAX(0,E2-VLOOKUP(B2,M:O,3,FALSE))+MAX(0,VLOOKUP(B2,M:N,2,FALSE)-D2)-H2)
 J2 =IF(G2="","",MAX(0,D2-VLOOKUP(B2,M:N,2,FALSE)))
 K2 =IF(G2="","",MAX(0,VLOOKUP(B2,M:O,3,FALSE)-(E2+(D2>E2))))

 G2:K2 下へコピー。

 ※演算誤差考慮なし。検証たぶん不足ｗ

 (GobGob)

 余談。
[[20130127120111]]　『給与計算』（ミャー）

 質問よく似てるんだけど・・・

 (GobGob)

GobGob様へ　さつきより

早速の回答ありがとうございます。
ミャーは、私の妹です。姉妹で総務で働いています。
いつもわかりにくい質問ですみません(~_~;)
シフトの入力欄は必要ですね、気がつきませんでした。遅刻してきて残業した場合は②です。所定7：15が基本なので。。。。
毎日、タイムシートを回収し判を押すのですが、タイムシート記入がわかりにくいようで多くの人が、Ａシフトで9：00～18：30休憩1時間だと所定時間8：00時間外残業100/100が
0：30と記入してきます。修正は、所定7：15時間外100/100は、0：45時間外100/125は0：30と赤ペンで直します。入力より前にその作業が大変です。入社時に説明はしているのですが。。。。遅刻と早退は１５分単位で残業は５分単位なのでそれも修正が大変です。
所定時間も残業100/100も時給は同じなのに分けて記入するので面倒です。頑張って作ってみますね！ありがとうございました。

 ＞遅刻してきて残業した場合は②です。所定7：15が基本なので。。。。

 なら、オイラの回答はダメっすね。

 あと②に関しての補足質問。
 遅刻して残業した場合、総時間が7:15に収まれば 遅刻はつかないの？

 (GobGob)

 ②に対応。

 条件整理

 遅刻、早退発生時で残業がある場合は所定時間を下回る場合のみ
 下回った時間を「遅刻・早退」とする。

 ※例表のレイアウトは変化なし
 ※C2数式は変化なし

 G2 =IF(B2="","",MIN(IF(OR(B2={"D","E"}),"4:15","7:15"),E2-D2-F2))
 H2 =IF(B2="","",MIN("0:45",E2-D2-F2-G2))
 I2 =IF(B2="","",E2-D2-F2-G2-H2)
 J2 =IF(B2="","",IF(AND(IF(OR(B2={"D","E"}),"4:15","7:15")*1>G2,VLOOKUP(B2,M:O,2,FALSE)<D2),IF(OR(B2={"D","E"}),"4:15","7:15")-G2,0))
 K2 =IF(B2="","",IF(AND(IF(OR(B2={"D","E"}),"4:15","7:15")*1>G2,VLOOKUP(B2,M:O,3,FALSE)>E2),IF(OR(B2={"D","E"}),"4:15","7:15")-G2,0))

 G2:K2 下へコピー。

 ※区分ごと計算はC列を元にSUMIFすればOK。
 ※演算誤差考慮なしっす
 ※0:00越え退社 考慮なし

 (GobGob)

 見逃していた

 ・遅刻/早退は15分単位でみる
 ・残業は5分単位でみる

 なんだね。。。。

 たとえば Aシフトの人が

 出社 9:03
 退社 17:10
 休憩 1:00
 の場合、所定時間 7:03分になるけど
 遅刻、早退は15分単位なんで

 結果は

 所定時間 7:15
 遅刻・早退 なし

 になるの？

 (GobGob)

 レイアウトを変えてみたよ。遅刻/早退は1セル化。

	A	B	C	D	E	F	G	H	I	J	K	L
1	日付	シフト	区分	出社	退社	休憩	時間内	残業A	残業B	遅/早		休日
2	2月1日	A	区分1	9:00	17:10	1:00	7:15	0:00	0:00	0:00		1月1日
3	2月2日	B	区分2	8:45	17:00	1:00	7:15	0:00	0:00	0:00		1月14日
4	2月3日	C	区分2	12:00	20:15	1:00	7:15	0:00	0:00	0:00		2月11日
5	2月4日	D	区分1	9:00	13:15		4:15	0:00	0:00	0:00		3月20日
6	2月5日	E	区分1	16:00	20:15		4:15	0:00	0:00	0:00		4月29日
7	2月6日	A	区分1	8:45	20:00	1:00	7:15	0:45	2:15	0:00		5月6日
8	2月7日	B	区分1	7:15	20:20	1:00	7:15	0:45	4:15	0:00		7月15日
9	2月8日	C	区分1	12:00	21:15	1:00	7:15	0:45	0:10	0:00		9月16日
10	2月9日		休日									9月23日
11	2月10日	E	区分2	15:30	23:00		4:15	0:45	2:30	0:00		10月14日
12	2月11日	A	区分2	10:00	20:00	1:00	7:15	0:45	1:00	0:00		11月4日
13	2月12日	B	区分1	7:30	15:00	1:00	6:30	0:00	0:00	0:45		12月23日
14	2月13日	E	区分1	16:00	23:00		4:15	0:45	2:00	0:00		
15	2月14日	A	区分1	11:00	17:59	1:00	6:00	0:00	0:00	1:15		
16	2月15日	D	区分1	7:00	10:15		3:15	0:00	0:00	1:00		
17	2月15日	A	区分1	9:03	17:10	1:00	7:15	0:00	0:00	0:00		
18	2月16日	A	区分2	9:20	17:10	1:00	7:00	0:00	0:00	0:15		
19	2月17日		休日									
20

 L列に祝日を設定。（所定時間で判断するため、シフト表はなし）

 C2 =IF(B2="","休日","区分"&IF(OR(WEEKDAY(A2)={1,7},COUNTIF(L:L,A2)),2,1))
 下へコピー。

 G2 =IF(B2="","",MIN(IF(OR(B2={"D","E"}),"4:15","7:15"),CEILING(E2,"0:15")-FLOOR(D2,"0:15")-F2)) 
 H2 =IF(B2="","",MIN("0:45",FLOOR(MAX(0,CEILING(E2,"0:15")-FLOOR(D2,"0:15")-F2-G2),"0:05")))
 I2 =IF(B2="","",FLOOR(MAX(0,CEILING(E2,"0:15")-FLOOR(D2,"0:15")-F2-G2-H2),"0:05"))
 J2 =IF(B2="","",IF(OR(B2={"D","E"}),"4:15","7:15")*1-G2)

 G2:J2 下へコピー。

 ※残業5分 、遅/早 15分単位
 ※所定時間 15分単位
 ※0:00以降退社考慮なし

 検証不足なので、いっぱいテストして判断してちょ。

 (GobGob)

GobGob様へ　☆さつきより

いつも、すぐに答えていただきありがとうございます。

Ａシフトで○出社9：03○休憩1時間○退社17：10の時は、○出社9：15休憩1時間○退社17：00になるので
所定時間は、6：45になります。
タイムシートには、はじめから遅刻、早退は、１５分単位で記入してもらい残業も５分単位で記入してもらっているので半端な時間は記入してありません。
ただ、所定時間と普通残業と割増残業を分けて記入するのが、なかなか理解してもらえなく毎日タイムシート修正して赤ペンだらけの人もいますが、一度付箋で説明すると次回からは、正しく記入してくれるので助かります。
入力以前にタイムシートのチェック作業にすごーく時間がかかります。
タイムカードがあればまだ、出社しているか判断できますが壁の出勤簿に出社時に○を書いてもらうのですが○付け忘れや他の人に○したりして何人出社しているか走って確認するのが大変です。無断欠勤や遅刻や早退の人も多く、座席も毎日午前と午後に移動して階数も３箇所に分かれていて、確認作業に時間がかかり、退職者も多く、まだ、入力することができません。GobGob様のやり方で作ってみましたが正しく時間が計算されていました、平日と休日の集計を出す計算式は入れてませんが頑張って作ってみます。毎日600枚くらいのタイムシートをチェックして時間しか記入していない人の所定や残業時間を頭で計算しなくても時間を入力すると答えがでるので、嬉しいです(*^_^*)ありがとうございました。

 ＞Ａシフトで○出社9：03○休憩1時間○退社17：10の時は、○出社9：15休憩1時間○退社17：00になるので 
 ＞所定時間は、6：45になります。

	A	B	C	D	E	F	G	H	I	J	K	L
1	日付	シフト	区分	出社	退社	休憩	時間内	残業A	残業B	遅/早		休日
2	2月1日	A	区分1	9:00	17:10	1:00	7:00	0:00	0:00	0:15		1月1日
3	2月2日	B	区分2	8:45	17:00	1:00	7:15	0:00	0:00	0:00		1月14日
4	2月3日	C	区分2	12:00	20:15	1:00	7:15	0:00	0:00	0:00		2月11日
5	2月4日	D	区分1	9:00	13:15		4:15	0:00	0:00	0:00		3月20日
6	2月5日	E	区分1	16:00	20:15		4:15	0:00	0:00	0:00		4月29日
7	2月6日	A	区分1	8:45	20:00	1:00	7:15	0:45	2:15	0:00		5月6日
8	2月7日	B	区分1	7:15	20:20	1:00	7:15	0:45	4:00	0:00		7月15日
9	2月8日	C	区分1	12:00	21:15	1:00	7:15	0:45	0:10	0:00		9月16日
10	2月9日		休日									9月23日
11	2月10日	E	区分2	15:30	23:00		4:15	0:45	2:30	0:00		10月14日
12	2月11日	A	区分2	10:00	20:00	1:00	7:15	0:45	1:00	0:00		11月4日
13	2月12日	B	区分1	7:30	15:00	1:00	6:30	0:00	0:00	0:45		12月23日
14	2月13日	E	区分1	16:00	23:00		4:15	0:45	2:00	0:00		
15	2月14日	A	区分1	11:00	17:59	1:00	5:45	0:00	0:00	1:30		
16	2月15日	D	区分1	7:00	10:15		3:15	0:00	0:00	1:00		
17	2月15日	A	区分1	9:03	17:10	1:00	6:45	0:00	0:00	0:30		
18	2月16日	A	区分2	9:20	17:10	1:00	6:30	0:00	0:00	0:45		
19	2月17日		休日									
20												
 C2 そのまま

 G2 =IF(B2="","",MIN(IF(OR(B2={"D","E"}),"4:15","7:15"),FLOOR(E2,"0:15")-CEILING(D2,"0:15")-F2))
 H2 =IF(B2="","",MIN("0:45",FLOOR(MAX(0,FLOOR(E2,"0:15")-CEILING(D2,"0:15")-F2-G2),"0:05")))
 I2 =IF(B2="","",FLOOR(MAX(0,FLOOR(E2,"0:15")-CEILING(D2,"0:15")-F2-G2-H2),"0:05"))
 J2 =IF(B2="","",IF(OR(B2={"D","E"}),"4:15","7:15")*1-G2)

 ＞平日と休日の集計を出す計算式は入れてませんが頑張って作ってみます

 C列を活用して =SUMIF(C:C,"区分1",G:G) 見たいな感じにすると集計できるよん。

 (GobGob)

 ところで、シフトD、Eの人は休憩なしだけど
 残業等で時間が増した場合、休憩しなくていいの？

 拘束時間以上なら休憩1:00付く とか 
 拘束時間 8:15以上で休憩1:00で 拘束時間 10:15以上でさらに休憩1:00、
 とか、ルールあったりしないのかな？

 (GobGob）

GobGob様へ　　　☆さつきより

基本的にＤＥシフトは残業がありません。
短時間勤務の人も１０分休憩あります。勤務時間に含まれます。7：１５分の人は、昼休憩の他に午前と午後に１０分ずつ休憩があります。日々、色々なシフトが追加されるので横にシフト表を作り、シフトを足していく方法が良いと思います。面倒なのは、勤務変更してシフトの交換やペアの人を見つけ欠勤の交代などをされるとＰＣのシフト表を修正し貼り出しの表も赤ペンで修正しなければいけないのが大変です。今日も、昨日はシフト休だったのに他の人が遅刻の遅と記入してしまい。苦情を言いに来ました、が、タイムシートをきちんを書いていればＯＫですと言ったら納得してくれました。毎日残業で気が付くと夜になっていて、眠るとすぐ朝になります。でも。。。。やっと、三連休なので、ストレス解消にキックボクシングの練習とスポーツクラブでダンスのレッスンをして来ます。休み明けにはシフト担当の主査が来るみたいで楽しみです。
GobGob様も楽しい週末をお過ごしくださいね(~o~)

GobGob様へ　助けて下さい　　　さつきより

勤怠集計表に新しいシフトができて8：30～17：30で所定８時間と8：30～17：00で所定7時間30分です。
今までの計算式にプラスして活用できますか？自分で修正してみたけどエラーがでてダメでした。
初めから作り直さないといけませんか？

 N/O列追加

	･･･	N	O
1		シフト	所定
2		A	7:15
3		B	7:15
4		C	7:15
5		D	4:15
6		E	4:15
7		F	8:00
8		G	7:30
9			
10

 G2 =IF(B2="","",MIN(VLOOKUP(B2,$N:$Q,2,FALSE),CEILING(E2,"0:15")-FLOOR(D2,"0:15")-F2))

 J2 =IF(B2="","",VLOOKUP(B2,$N:$Q,2,FALSE)-G2)

 に変更。

 (GobGob)

コメント返信：

[ 一覧(最新更新順) ]

[[20130205220106]] 『勤務表』（さつき） ページの最後に飛ぶ

[[20130205220106]]　『勤務表』（さつき）　ページの最後に飛ぶ