[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『指定された休憩時間中の業務が発生した場合の休憩時間変更を自動化したいです。』(こー)
24時間勤務の仕事をしており、8:30〜翌8:40までが勤務時間です。
あらかじめ、
1.12:00〜13:00
2.17:15〜18:00
3.翌7:15〜翌7:30
と、これらの休憩時間が指定されていますが、この間に業務が発生した場合は業務終了から新たにその分の休憩時間をとることになっています。
業務というのが、1日に10件前後あり、1件につき1時間から2時間程度のものがあります。
a.8:50〜10:20
b.11:13〜12:45
c.13:50〜15:02
……
以上のように、1日の業務の開始から終了を入力したら、自動的に
休憩時間を業務時間外に指定してくれるようなシステムを作りたいです。
決まりとして、あらかじめ指定されてる休憩時間前には割り振ることができません。
とれていない休憩時間を一度にとれない場合は、割り振ってとる。
自分ではいくらやってもできないので、皆さまの知恵をお貸し下さい。
< 使用 Excel:Excel2016、使用 OS:Windows10 >
>業務終了から新たにその分の休憩時間をとることになっています。 この業務終了ってどのタイミングの話ですか? 一日の終わりなのか、1件終わる毎なのか
後者だとしたら、例に上がってる場合だと a. 8:50〜10:20 b. 11:13〜12:45 → 12:45〜13:45までが休憩時間 c. 13:50〜15:02 になってほしい認識で合っていますか? (*) 2021/09/08(水) 09:33
1.勤務データが下図のようだとした場合、希望する結果例はどうなりますか? 行 ___A___ __B__ __C__ 1 a 8:30 10:20 2 b 11:13 12:45 3 c 13:50 15:02 4 d 17:00 19:00 5 e(翌日) 7:15 8:15
2.翌日の時刻は、どう入力しているのですか? (例) 午前3時の場合、3:00 それとも 27:00 ?
(半平太) 2021/09/08(水) 10:13
1
その場合の結果は
b12:00〜12:45→13:00〜13:45
d17:15〜18:00→19:00〜19:45
e7:15〜7:30→8:15〜8:30
の結果が出てくるのが理想です。
理想というのは、どこの業務の時間帯がどこに変更されたかの処理が必要なので、変更前と変更後がこのように上がってきて欲しいのです。
2
日付変更後については、処理する様式はam3:00の場合はそのまま3:00で表記しています。
ただ、自分でやってみてこれがかなりネックだったので、27:00で処理して、様式にあげるときに3:00に直すように考えております。
(こー) 2021/09/08(水) 11:00
一件の業務が終わるごとになります。
*さんの認識の通りでございます。
(こー) 2021/09/08(水) 11:02
>理想というのは、どこの業務の時間帯がどこに変更されたかの処理が必要なので、 >変更前と変更後がこのように上がってきて欲しいのです。
具体的に図で示して頂けませんか?
そちらは、どうなればいいか分かり切っているんでしょうが、 こっちは分からないです。
(半平太) 2021/09/08(水) 11:25
変更前 変更後
12:00 12:45 13:00 13:45
17:15 18:00 19:00 19:45
7:15 7:30 8:15 8:30
以上になります。
(こー) 2021/09/08(水) 12:06
業務の例として
a.11:03〜12:45
b.13:11〜15:28
c.15:28〜16:15
d.16:20〜17:30
e.18:08〜19:56
f.20:00〜22:45
g.22:45〜0:10
h.7:09〜8:40
このような場合、必要な結果としては
変更前 変更後
12:00 12:45 13:00 13:11
16:15 16:20 18:00 18:08 19:56 20:00 0:10 0:27 17:15 17:30 0:27 0:42 7:15 7:30 未取得
以上のようになります。
説明させて頂くと、まずaの業務で12:00〜12:45の休憩時間45分を振り分けることになります。そこで、業務終了から振り分けていきますが、連続で業務が発生した場合は繰越になります。また、もともと指定されている次の休憩時間(17:15〜18:00)には振り分けることができないので、業務dと業務eの間の17:30〜18:00には振り分けることができません。
さらに、翌日の勤務時間が8:40までになりますので、勤務時間以内に取れない場合はどこにも振り分けず、"未取得"という形になります。
分かりづらくて申し訳ありません。不明な点があればご質問頂きたいです。
(こー) 2021/09/08(水) 12:30
>あらかじめ、 >1.12:00〜13:00 >2.17:15〜18:00 >3.翌7:15〜翌7:30 >と、これらの休憩時間が指定されていますが、
>このような場合、必要な結果としては >変更前 変更後 >12:00 12:45 13:00 13:11 > 16:15 16:20 > 18:00 18:08 > 19:56 20:00 > 0:10 0:27 >17:15 17:30 0:27 0:42 > 7:15 7:30 未取得
1.何故こうはならないのですか? ↓ 変更前 変更後 12:00 13:00 12:45 13:11 16:15 16:20 18:00 18:08 : :
>勤務時間以内に取れない場合はどこにも振り分けず、"未取得"という形になります。 2.一部が取れない場合(例:15分が規定で、7分は取れた)、どう言う表示になるんですか?
3.かなりややこしい仕様と思うんですが、ユーザー定義関数(VBA)でもいいですか?
(半平太) 2021/09/10(金) 15:12
1. あらかじめ12:00〜13:00に指定されている部分で休憩時間を取得した場合は、そこに指定はできないことになっています。
つまり、変更後の欄にはもともと指定されている休憩時間は入らないことになります。
2.私の会社のルールとして、休憩時間を最後にとって仕事を終えることはできないことになっていて、最後の休憩時間(7:15〜7:30)の変更は、業務終了の時間を調整して、全て取れるか全て取れないようにしております。
3.自分で作っていて、行き詰まっておりますので、どのような形でもありがたいです。
(こー) 2021/09/11(土) 22:51
>2.私の会社のルールとして、休憩時間を最後にとって仕事を終えることはできないことになっていて、 >最後の休憩時間(7:15〜7:30)の変更は、業務終了の時間を調整して、全て取れるか全て取れないようにしております。
最後の確認です。以下の解釈でいいですか?
1.部分取得は不可なので、1分でも不足したら「未取得」と表示する。
2.休憩時間が最後には近くとも、1分の余裕さえあれば可となる。
具体例:業務z、翌朝 7:15〜8:24、変更前 7:15〜7:30、変更後 8:24〜8:39
(半平太) 2021/09/11(土) 23:47
2.その通りです。
ありがとうございます。
(こー) 2021/09/12(日) 08:00
ちょっと分からないです。
>24時間勤務の仕事をしており、8:30〜翌8:40までが勤務時間です。 と言う前提だったと思うのですが、 業務z+1 8:30〜 9:00 などと8:40を越えることがあるんですね?
>このような場合は、変更後の休憩時間が、8:24〜8:30(7分)になり、部分取得という形になります。 原則、部分取得は不可と言うことだったと思うのですが、 なぜ部分取得ができるのですか?
「このような場合」とはどのような概念なのですか? 何が原則から外れていい理由なのか理解できないです。
あと、8:24〜8:30(7分)は、正味(6分)ですよね?
(半平太) 2021/09/12(日) 09:14
勤務時間の終了が休憩時間で終了ということは、制度上できないことになっています。
しかし、上げていただいた例の通り、8:24〜8:39まで休憩時間を取得するように業務を終了しても、8:30に新たな業務が発生した場合は8:30までの部分取得になり、残りの9分は業務終了後になるので未取得という形になります。
はい、6分です。すみません。
(こー) 2021/09/12(日) 10:51
>残りの9分は業務終了後になるので未取得という形になります。 部分取得なので未取得になるという事だったのですね。勘違いしてました。m(__)m
>残業が発生する場合もあります。 以下のケースは部分取得ではなくなり、可となりますか?(実際は滅多にないでしょうが)
業務 開始 終了 変更前 変更後 Z 7:15 8:30 7:15 7:30 8:30 8:45 ←本来の勤務時刻を越えて休憩を取れることになる? Z+ 8:50 9:20
(半平太) 2021/09/12(日) 13:57
なんか頭が混乱しますね。 取敢えず、以下の案でやってみてください。
標準モジュールに後記VBAをコピペする。
F1セルに下式を入力する。
=howTakenBreaks(B2:C13,D2:E4) ~~↑~~~ 少し広めにしておくと、メンテの手間がないです。
<結果図> 行 __A__ __B__ __C__ ___D___ __E__ ___F___ __G__ 1 業務 開始 終了 変更前 変更後 2 a 11:03 12:45 12:00 13:00 13:00 13:11 3 b 13:11 15:28 17:15 18:00 16:15 16:20 4 c 15:28 16:15 7:15 7:30 18:00 18:08 5 d 16:20 17:30 19:56 20:00 6 e 18:08 19:56 0:10 0:27 7 f 20:00 22:45 0:27 0:42 8 g 22:45 0:10 未取得 9 h 7:09 8:40
※数式は最上行に1つだけ入力してください。(「変更後」と表示されるべきセルの事です) 数式の下には何も入れないでください。(20行目まで空白のこと)
’コピペするVBA
Private Ret(1 To 19, 1 To 4)
Function howTakenBreaks(rngW As Range, rngB As Range) Const edMaxBreak As Long = 1958 Dim App As Application Dim rWork As Range Dim vWork Dim rBreak As Range Dim vBreak Dim i As Long, k As Long, cnt As Long Dim ZoneSt As Long, ZoneEd As Long Dim Tomrw As Long Dim Ocupy() Dim stMIN, edMAX Dim br As Long Dim continuousBreak As Long Dim continuousIdle As Long Dim prePos Dim kaisiFlag As Boolean Dim RWs As Long Dim NoneBreakMemo As String
Set App = Application Erase Ret
'関連データを取得 RWs = App.Round(App.CountA(rngW) / 2, 0) Set rWork = rngW(1, 1).Resize(RWs, 2)
If App.CountBlank(rWork) Then rngW.Parent.Evaluate "+PlusEval(" & Application.Caller.Address & ")" howTakenBreaks = "勤務データは上詰めにしてください" Exit Function End If
RWs = App.Round(App.CountA(rngB) / 2, 0) Set rBreak = rngB(1, 1).Resize(RWs, 2)
If App.CountBlank(rBreak) Then rngW.Parent.Evaluate "+PlusEval(" & Application.Caller.Address & ")" howTakenBreaks = "休憩データは上詰めにしてください" Exit Function End If
vWork = rWork.Value
'タイムゾーンを格納 Tomrw = 0 vWork(1, 1) = rWork(1, 1) vWork(1, 2) = rWork(1, 2)
For i = 2 To rWork.Rows.Count vWork(i, 1) = rWork(i, 1) vWork(i, 2) = rWork(i, 2)
Tomrw = IIf(Tomrw = 1, 1, IIf(vWork(i, 1) <= vWork(i - 1, 1), 1, 0)) '前行比小=翌日 vWork(i, 1) = rWork(i, 1) + Tomrw
Tomrw = IIf(Tomrw = 1, 1, IIf(vWork(i, 2) <= vWork(i - 1, 2), 1, 0)) vWork(i, 2) = rWork(i, 2) + Tomrw Next i
vBreak = rBreak.Value Tomrw = 0 vBreak(1, 1) = rBreak(1, 1) vBreak(1, 2) = rBreak(1, 2)
For i = 2 To rBreak.Rows.Count vBreak(i, 1) = rBreak(i, 1) vBreak(i, 2) = rBreak(i, 2)
Tomrw = IIf(Tomrw = 1, 1, IIf(vBreak(i, 1) <= vBreak(i - 1, 1), 1, 0)) '前行比小=翌日 vBreak(i, 1) = rBreak(i, 1) + Tomrw
Tomrw = IIf(Tomrw = 1, 1, IIf(vWork(i, 2) <= vWork(i - 1, 2), 1, 0)) vBreak(i, 2) = rBreak(i, 2) + Tomrw Next i
stMIN = App.Round(App.Min(vWork(1, 1), vBreak(1, 1)) * 1440, 0) edMAX = App.Round(App.Max(vWork(UBound(vWork), 2), vBreak(UBound(vBreak), 2), ["32:39"*1]) * 1440, 0) - 1
ReDim Ocupy(stMIN To edMAX, 1 To 2) '1分刻みの枠を格納。
'仕事(1000)と休憩(1,2,3)のデータをフラグで埋める(加算する) For i = 1 To UBound(vWork) ZoneSt = App.Round(vWork(i, 1) * 1440, 0) ZoneEd = App.Min(edMaxBreak, App.Round(vWork(i, 2) * 1440, 0) - 1) '終了時刻1分前までが対象
WorkFlag Ocupy, ZoneSt, ZoneEd, 1000 Next i
For i = 1 To UBound(vBreak) ZoneSt = App.Round(vBreak(i, 1) * 1440, 0) ZoneEd = App.Round(vBreak(i, 2) * 1440, 0) - 1 '終了時刻1分前までが対象
WorkFlag Ocupy, ZoneSt, ZoneEd, i Next i
continuousBreak = stMIN continuousIdle = continuousBreak + 1
'休憩の限界ZoneEdは8:38(1958)まで(休憩で業務終了できないので)
Dim satisfiedNum As Long
For br = 1 To UBound(vBreak) '休憩の序数(1,2,3) satisfiedNum = App.Round((vBreak(br, 2) - vBreak(br, 1)) * 1440, 0)
'代替取得できる休憩を検索 cnt = 0
For i = stMIN To edMaxBreak '翌日8:39迄 If cnt = satisfiedNum Then Exit For ElseIf Ocupy(i, 1) = br Then '予定通りに取れた cnt = cnt + 1
ElseIf Ocupy(i, 1) = 1000 + br Then '潰された休憩があったら
For k = App.Max(i + 1, continuousIdle) To edMaxBreak '非業務時間帯(Empty=Idle)を検索する
If Ocupy(k, 1) = "" Then Ocupy(k, 2) = br '代替休憩序数を入れる cnt = cnt + 1 continuousIdle = k + 1 '代替休憩を探る為の位置 Exit For End If Next k End If Next i
'取得すべき休憩分数に達しない場合は、休憩序数の書き込みを取り消す If cnt < satisfiedNum Then NoneBreakMemo = "," & br & "," '未取得メモに記入
For i = stMIN To edMaxBreak '翌日8:39迄 If Ocupy(i, 2) = br Then Ocupy(i, 2) = Empty '休憩序数を消去 End If Next i End If Next br
k = 1 For br = 1 To 3 '休憩の序数(1,2,3)別に算出 If InStr(NoneBreakMemo, "," & br & ",") = 0 Then cnt = 0 kaisiFlag = True
For i = stMIN To edMAX
If Ocupy(i, 2) = br Then 'ピュアな休憩だったら cnt = cnt + 1
If kaisiFlag Then Ret(k, 1) = vBreak(br, 1) Ret(k, 2) = vBreak(br, 2) Ret(k, 3) = Format(i / 1440, "h:nn") kaisiFlag = False
ElseIf prePos + 1 <> i Then '連続が切れた Ret(k, 4) = Format((prePos + 1) / 1440, "h:nn") k = k + 1 Ret(k, 3) = Format(i / 1440, "h:nn") kaisiFlag = False End If
prePos = i End If
Next i
If kaisiFlag = False Then Ret(k, 4) = Format((prePos + 1) / 1440, "h:nn") k = k + 1 End If Else Ret(k, 1) = vBreak(br, 1) Ret(k, 2) = vBreak(br, 2) Ret(k, 3) = "未取得" k = k + 1 End If Next br
'結果配列を書き出す rngW.Parent.Evaluate "+PlusEval(" & Application.Caller.Address & ")" howTakenBreaks = "変更前" End Function
Sub WorkFlag(ByRef ocpy, st, ed, BreakNo As Long) Dim i As Long For i = st To ed ocpy(i, 1) = ocpy(i, 1) + BreakNo Next i End Sub
Private Function PlusEval(BaseCell As Range) BaseCell.Offset(1).Resize(UBound(Ret), 4) = Ret BaseCell.Offset(, 2) = "変更後" End Function
(半平太) 2021/09/12(日) 19:30
本当に、説明が足りていなかったり、表現が拙いところがあるにも関わらず、このようなもの作って頂いて本当にありがとうございます!!
(こー) 2021/09/12(日) 20:17
変更前データに空行を挿入を忘れてしまった。
上記のコードを修正して置きましたので、再コピペしてください。
<シート 結果図> 行 __A__ __B__ __C__ ___D___ __E__ ___F___ __G__ ___H___ __I__ 1 業務 開始 終了 変更前 変更前 変更後 2 a 11:03 12:45 12:00 13:00 12:00 13:00 13:00 13:11 3 b 13:11 15:28 17:15 18:00 16:15 16:20 4 c 15:28 16:15 7:15 7:30 18:00 18:08 5 d 16:20 17:30 19:56 20:00 6 e 18:08 19:56 0:10 0:27 7 f 20:00 22:45 17:15 18:00 0:27 0:42 8 g 22:45 0:10 7:15 7:30 未取得 9 h 7:09 8:40
(半平太) 2021/09/12(日) 21:01
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.