[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『勤務表（終了時刻が休憩時間に被る場合の計算方法』（MA）
Excelで勤務表を作成しています。 社員には、タイムカードの開始時刻・終了時刻を入力してもらえば 総時間・休憩時間・実働時間が自動計算されるような表を以下の ように作成しています。
終了時刻が休憩時間帯に被った場合（C4の場合）、休憩時間帯は実働時間 としてカウントしないので、F4の実働時間が7:45になるようにしたいのですが、 方法が分かりません。
ご教示をお願い致します。
勤務表と式は以下の通りです。
A B C D E F
1 シフト 開始時刻 終了時刻 総時間 休憩時間 実働時間
2 8:50 8:43 17:37 8:45 1:00 7:45
3 8:50 8:20 27:30 18:40 2:45 15:55
4 8:50 8:38 17:41 8:50 1:00 7:50
5
6 休憩① 12:00 13:00 1:00
7 休憩② 17:35 17:50 0:15
8 休憩③ 18:30 19:00 0:30
9 休憩④ 21:30 22:00 0:30
10 休憩⑤ 26:15 26:45 0:30
11 休憩⑥ 32:15 32:30 0:15
A2は、IF(B2="","","8:50")というように開始打刻時間にかかわらずシフト開始 時刻から勤務時間を計算するために式を入れています。 また、場合によってはシフト開始時刻が8:30と変わる時もありますので、 その場合はA2に手入力でシフト開始時刻を入れるような運用をしています。
B2・C2はタイムカードの打刻時間を手入力する欄になります。
D2には、 IF(AND(A2="",B2<>""),"",IF(B2<A2,FLOOR(C2,TIME(0,5,0))-CEILING(A2,TIME(0,5,0)),FLOOR(C2,TIME(0,5,0))-CEILING(B2,TIME(0,5,0)))) という式を入れ、シフト時間（A2）よりも開始時刻（B2）が早かった場合は、 A2～C2までの総時間を計算し、シフト時間(A2）よりも開始時間（B2）が遅かった 場合は、B2～C2までの総時間を5分の丸め時間で計算するようにしています。
E2は、 SUMPRODUCT((B6:B11>=B2)*(C2:C11<=C2)*D6:D11) という式を入れ、勤務時間の内、休憩時間①～⑥を計算するようにしています。
F2には、D2-E2の総時間から休憩時間を引いた実働時間が計算されるようにしています。
長くなり申し訳ありませんが、ご教示宜しくお願い致します。
こんばんは。 以下のように考えてみました。 ・終了時刻（4行目の場合、FLOOR(17:41,TIME(0,5,0))=17:40）が、何回目の休憩開始時間以上か、をG列にセット。 （17:35<17:40なので2回目） ・このとき、17:35と17:40の差5分の休憩分（FLOOR(C2,TIME(0,5,0))-INDEX(B$6:B$11,G2)）を、E列に含める。 ・ところで、仮に終了時刻が2回目の休憩終了時間（17:50）以上の場合は、2回目の休憩時間（0:15）は、 SUMPRODUCTの中に含まれるので、上の差額を加えるのは、終了時刻<17:50の場合のみ。 なので、IF(FLOOR(C2,TIME(0,5,0))<INDEX(C$6:C$11,G2),～で分岐する。
※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [1] シフト 開始時刻 終了時刻 総時間 休憩時間 実働時間 終了 [2] 8:50 8:43 17:37 8:45 1:00 7:45 2 [3] 8:50 8:20 27:30 18:40 2:45 15:55 5 [4] 8:50 8:38 17:41 8:50 1:05 7:45 2 [5] _ _ 0 _ _ _ _ [6] 休憩1 12:00 13:00 1:00 _ _ _ [7] 休憩2 17:35 17:50 0:15 _ _ _ [8] 休憩3 18:30 19:00 0:30 _ _ _ [9] 休憩4 21:30 22:00 0:30 _ _ _ [10] 休憩5 26:15 26:45 0:30 _ _ _ [11] 休憩6 32:15 32:30 0:15 _ _ _
E2 =SUMPRODUCT((B$6:B$11>=B2)*(C$6:C$11<=C2)*D$6:D$11)
+IF(FLOOR(C2,TIME(0,5,0))<INDEX(C$6:C$11,G2),FLOOR(C2,TIME(0,5,0))-INDEX(B$6:B$11,G2),0) →E2:E4フィルコピー
G2 =MATCH(FLOOR(C2,TIME(0,5,0)),B$6:B$11,1) →G2:G4フィルコピー
（コタ）
『本題とは関係ないけれど、どうにも気になる二、三の事柄』
■その１ A2セルの数式 IF(B2="","","8:50")
"8:50" だけでは文字列だってことわかってますか？ "8:50"*1 などとしないとシリアル値にはなりませんよ。
B列とC列はシリアル値でしょう？ D2の数式で A2とB2を比較してますが 文字列とシリアル値なので、期待通りの結果にはなってない場合があるはずです。 ■その２ D2セルの数式 IF(AND(A2="",B2<>""),"",･･････
A2が空白で、かつB2が空白でない場合は "" にする、ですよね？ B2が空白でなければ A2に 8:50 をセットしてるんですから そんなケース（A2が空白で、B2が空白でない）はありえないと思うんですが･･？ ■その３ D2セルは、たぶん ↓ でも同じ結果になると思います（空白にする条件を除けば）
=IF(COUNT(B2:C2)<2,"",FLOOR(C2,"0:05")-CEILING(MAX(A2:B2),"0:05"))
ただし小数誤差のことは考えてません。
（sky）
> また、場合によってはシフト開始時刻が8:30と変わる時もありますので、 > その場合はA2に手入力でシフト開始時刻を入れるような運用をしています。
すみません。 ↑ を見落としてました（滝汗 だから上の ■その２ のところ、B2が空白でなくても A2が空白の場合もありうるんですかね？
行によってシフト時間が異なることがあるんですか？ それとも全行一斉に 8:30 とかになるってことですか？
全行同じシフト時間になるんだったら どこかにシフト時間を入力しておいて、セル参照すればいいのではないかと思います。
最後に提示した数式も一応訂正しておきます。
=IF(COUNT(B2:C2)<2,"",･･････ ↓ =IF(COUNT(A2:C2)<3,"",･･････
まあ本題とは関係ないことですから 軽くスルーして下さい。
（sky）
作業列（G列）を使わない方法を考えてみました。 （配列計算は増えますが、こちらのほうが分かりやすいかもです）
E2 =SUMPRODUCT((B$6:B$11>=B2)*(C$6:C$11<=C2)*D$6:D$11+(B$6:B$11<=C2)*(C$6:C$11>C2)*(FLOOR(C2,TIME(0,5,0))-B$6:B$11)) →E2:E4フィルコピー
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
・実際の終了時刻（4行目の場合、C4=17:41）が、休憩開始時間（B$6:B$11）以上かつ、休憩終了時間（C$6:C$11）未満のとき、
（17:35<17:41<17:50なので休憩2回目だけ両方TRUE）
計算上の終了時刻（FLOOR(17:41,TIME(0,5,0))=17:40）と休憩開始時間（B$7=17:35）の差を計算する。
（コタ）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.