[[20210908075604]] 『指定された休憩時間中の業務が発生した場合の休憩』(こー) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『指定された休憩時間中の業務が発生した場合の休憩時間変更を自動化したいです。』(こー)

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


半平太さんありがとうございます。


その場合の結果は
b12:00〜12:45→13:00〜13:45
d17:15〜18:00→19:00〜19:45
e7:15〜7:30→8:15〜8:30
の結果が出てくるのが理想です。
理想というのは、どこの業務の時間帯がどこに変更されたかの処理が必要なので、変更前と変更後がこのように上がってきて欲しいのです。


日付変更後については、処理する様式は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


1.そのような解釈で結構です。
ただし、
例:業務z 翌朝 7:15〜8:24、
 業務z+1 8:30〜9:00
 このような場合は、変更後の休憩時間が、8:24〜8:30(7分)になり、部分取得という形になります。

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


なかなか私もそのケースは経験したことがありませんが、やはり、勤務時間の終了が休憩時間ということになってしまいます。ということで、その場合は業務終了を8:29以前、もしくは8:40に調整することになります。
また、特別なことがない限りは、8:40以降は新たな業務は発生しないことになっていますので、8:40以降は業務はないと考えて頂きたいです。
(こー) 2021/09/12(日) 14:07

 なんか頭が混乱しますね。
 取敢えず、以下の案でやってみてください。

 標準モジュールに後記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.