[[20210504170614]]　『サービス時間の重複を調べたい』（マロンドプロ）　ページの最後に飛ぶ

『サービス時間の重複を調べたい』（マロンドプロ）

はじめまして、お世話になります。

介護事業所で以下のようなデータのチェックをします。

ヘルパー名・日付・利用者・サービス内容・開始時間・終了時間
がABCDEFで並んだ行データが2000件あります。

同じ日に同じ利用者で開始時間と終了時間内に重複しているデータをG列に重複と表示させたいです。
開始時間と終了時間どちらが重なっても重複とさせたいのですが・・・

また、前の終了時間と次の開始時間が１０分無い場合もH列に不足と表示させたいです。

初心者で勉強中ですがわかりません。教えてください。すみません。。よろしくおねがします。

＜使用 Excel：Excel2007、使用 OS：Windows10 ＞

 １．利用者＞日付＞開始日　の順に並べ替える(下図参照）

 ２．下式を入力して、下にコピーする
   G2セル =IF(COUNTBLANK(E2:F2)=2,"",IF(F2<=E2,"入力ミス",IF(C3<>C2,"",IF(B3+E3<B2+F2,"重複",""))))
   H2セル =IF(COUNTBLANK(E2:F2)=2,"",IF(F2<=E2,"入力ミス",IF(C3<>C2,"",IF(AND(G2<>"重複",B3+E3<B2+F2+"0:9:59"),"不足",""))))

 <結果図>
 行 _____A_____ ____B____ ___C___ ______D______ ____E____ ____F____ ____G____ ____H____
  1 ヘルパー名  日付      利用者  サービス内容  開始時間  終了時間                     
  2              2021/5/4 Aさん                     8:00      9:00                    
  3              2021/5/4 Aさん                    10:00     12:00  重複               
  4              2021/5/4 Aさん                    11:59     13:00                     
  5              2021/5/4 Aさん                    13:10     13:26            不足     
  6              2021/5/4 Aさん                    13:30     14:00                     
  7              2021/5/4 B様                       8:00      9:55            不足     
  8              2021/5/4 B様                      10:00     12:00                     
  9              2021/5/4 B様                      12:10     13:00                     
 10                                                10:00      9:00  入力ミス  入力ミス

  ※同名異人がいる場合、何か区別出来るデータを見つける必要があります。
　　見つかったら、それと利用者を加味したデータを作って、別人扱いになるように工夫してください。

（半平太） 2021/05/04(火) 19:46

  ＞同じ日に同じ利用者で開始時間と終了時間内に重複しているデータをG列に重複と表示させたいです。

  「同じ利用者」で間違いないですね？

  ※「同じヘルパー名」ではないかと言う気がして、ちょっと不安

（半平太） 2021/05/04(火) 20:23

半平太さん
早速の回答ありがとうございます！

 はい！「同じ利用者」で間違いないです。

今からデータに載せてやってみます。

（マロンドプロ） 2021/05/04(火) 20:41

 重なってしまいましたが、そのまま載せます。
 (1)
 ヘルパー名      日付    利用者  サービス内容 開始時間  終了時間
             2021/5/4        A                    9:00    10:00
             2021/5/4        A                    9:30    12:00
             2021/5/4        A                   10:05    10:30
             2021/5/4        A                   12:05    13:00
 この場合、どのようなアウトプットになりますか？

 (2)
 ヘルパー名は関係しますか？
 上の4件は、すべて同じヘルパーとの前提でしたが、
 2番目が仮に別のヘルパーだったら、結果は変わってきますか？

 (3)
 日を跨ることはあるんですか？
 また、二日よりも多いことはありますか？

 # 前提の確認だけの参加になりますね。

（γ） 2021/05/04(火) 20:47

γ様
回答ありがとうございます。

(1)このようなアウトプットになります。

 ヘルパー名      日付    利用者  サービス内容 開始時間  終了時間　 判定　　不足
             2021/5/4        A                    9:00    10:00　　
             2021/5/4        A                    9:30    12:00　　重複　
             2021/5/4        A                   10:05    10:30    重複　　不足
             2021/5/4        A                   12:05    13:00　　　　　　不足

(2)

 ヘルパー名は関係しますか？・・・関係しません

(3)

 日を跨ることはあるんですか？
 また、二日よりも多いことはありますか？・・・可能性はあるんですが、今のところありません。

ありがとうございます。

（マロンドプロ） 2021/05/04(火) 21:03

ありがとうございました。
（γ） 2021/05/04(火) 21:15

＞ 2021/5/4 A 10:05 10:30 重複　　不足
はなぜ不足なんでしょうか。
9:30-12:00 のデータを削除する前提ならそうかもしれないが、
開始時刻を10:00に変更するなどの修正をするとすれば、
終了時刻は12:00まで伸びますよね。
すると不足でもないんじゃないですか？
　
なにか、このあたりのルール（考え方）を日本語で説明いただけませんか？

（γ） 2021/05/04(火) 21:43

γさま

9:30-12:00 のデータを削除ではないのに、間違えました。
おっしゃるとおりです。
不足はなく重複のみです。
すみません。。

ありがとうございます。
（マロンドプロ） 2021/05/04(火) 22:04

 おはようございます。
G/Wの暇つぶしに書いてみました。。。
データの在りようによって結果が変わるのでもう少しサンプルが欲しいところです。
が、出されてもご希望に沿えるかどうかわかりません。。(^^;

 ヘルパーさんが不問ということは複数いらっしゃるでしょうから。。。考え方ですね。。。ルール。。。
でも、これはトピ主さんにしか分かりませんのでご希望に沿っているかどうかわかりませんけど、折角書いたのでUpしておきます。
なお、何分にもずぶのど素人が片手間で書いたコードですのでお気に召さない箇所等は適当にアレンジしていただけると幸甚です。
では、、では、、、

     |[A]             |[B]     |[C]   |[D]         |[E]     |[F]     |[G] |[H] 
 [1] |ヘルパー名      |日付    |利用者|サービス内容|開始時間|終了時間|判定|不足
 [2] |マロンドプロ１  |2021/5/4|三郎  |介護        |9:00    |10:00   |    |    
 [3] |マロンドプロ２  |2021/5/4|三郎  |入浴        |9:30    |12:00   |重複|    
 [4] |マロンドプロ３  |2021/5/4|三郎  |料理        |11:55   |10:30   |重複|不足
 [5] |マロンドプロ４  |2021/5/4|三郎  |訪問        |12:05   |13:00   |    |    
 [6] |マロンドプロ５  |2021/5/5|三郎  |介護        |9:00    |10:00   |    |    
 [7] |マロンドプロ６  |2021/5/5|三郎  |入浴        |9:30    |12:10   |重複|    
 [8] |マロンドプロ７  |2021/5/5|三郎  |料理        |10:05   |10:30   |重複|    
 [9] |マロンドプロ８  |2021/5/5|三郎  |料理        |12:05   |13:00   |重複|    
 [10]|マロンドプロ９  |2021/5/4|一郎  |入浴        |8:00    |9:00    |    |    
 [11]|マロンドプロ１０|2021/5/4|一郎  |料理        |10:00   |12:00   |    |    
 [12]|マロンドプロ１１|2021/5/4|一郎  |訪問        |10:30   |14:00   |重複|    
 [13]|マロンドプロ１２|2021/5/4|一郎  |介護        |11:59   |13:00   |重複|    
 [14]|マロンドプロ１３|2021/5/4|一郎  |入浴        |13:10   |13:26   |重複|    
 [15]|マロンドプロ１４|2021/5/4|五郎  |料理        |8:00    |9:55    |    |    
 [16]|マロンドプロ１５|2021/5/4|五郎  |料理        |10:00   |12:00   |    |不足
 [17]|マロンドプロ１６|2021/5/4|五郎  |訪問        |10:30   |13:00   |重複|

 Option Explicit
Sub てすと()
Dim MyT As Range
Dim r As Variant
Dim x As Variant
Dim y As Variant
Dim z As Variant
Dim v As Variant
Dim w As Variant
Dim 開始 As Variant
Dim 終了 As Variant
Dim 位置 As Variant
Dim q() As Variant
Dim i As Long
Dim n As Long
Dim myflg As Boolean
With Range("A1").CurrentRegion
    Set MyT = .Resize(.Rows.Count, 6)
End With
v = MyT.Value
ReDim q(LBound(v, 1) To UBound(v, 1), 1 To 2)
q(1, 1) = "判定"
q(1, 2) = "不足"
ReDim r(1)
For i = LBound(r) To UBound(r)
    r(i) = MyT.Columns(i + 2).Address
Next
x = Application.Transpose(Evaluate(r(0) & "&" & r(1)))
For i = LBound(v, 1) + 1 To UBound(v, 1)
    z = Application.Match(x(i), w, 0)
    If IsError(z) Then
        If myflg = False Then
            ReDim y(0)
            ReDim w(0)
            myflg = True
        Else
            ReDim Preserve y(UBound(y) + 1)
            ReDim Preserve w(UBound(w) + 1)
        End If
        ReDim 開始(0)
        ReDim 終了(0)
        ReDim 位置(0)
        開始(0) = v(i, 5)
        終了(0) = v(i, 6)
        位置(0) = i
        y(UBound(y)) = Array(開始, 終了, 位置)
        w(UBound(w)) = x(i)
    Else
        開始 = y(z - 1)(0)
        終了 = y(z - 1)(1)
        位置 = y(z - 1)(2)
        ReDim Preserve 開始(UBound(開始) + 1)
        ReDim Preserve 終了(UBound(終了) + 1)
        ReDim Preserve 位置(UBound(位置) + 1)
        開始(UBound(開始)) = v(i, 5)
        終了(UBound(終了)) = v(i, 6)
        位置(UBound(位置)) = i
        y(z - 1) = Array(開始, 終了, 位置)
    End If
Next
For i = LBound(v, 1) + 1 To UBound(v, 1)
    z = Application.Match(x(i), w, 0)
    If Not IsError(z) Then
        開始 = y(z - 1)(0)
        終了 = y(z - 1)(1)
        位置 = y(z - 1)(2)
        For n = LBound(位置) To UBound(位置)
            If (x(i) = CLng(v(i, 2)) & v(i, 3)) Then
                If (開始(n) <> v(i, 5)) + (終了(n) <> v(i, 6)) Then
                    If (開始(n) < v(i, 5)) * (終了(n) > v(i, 5)) + (開始(n) < v(i, 6)) * (終了(n) > v(i, 6)) Then
                        If i > 位置(n) Then
                            q(i, 1) = "重複"
                        Else
                            q(位置(n), 1) = "重複"
                        End If
                    End If
                    If CDbl(CDate(Abs(Val(v(i - 1, 6)) - v(i, 5)))) < CDbl(TimeValue("00:09：59")) Then q(i, 2) = "不足"
'                    飛び越して不足も見る場合
'                    If CDbl(CDate(Abs(終了(n) - v(i, 5)))) < CDbl(TimeValue("00:09：59")) Then
'                        If i > 位置(n) Then
'                            q(i, 2) = "不足"
'                        Else
'                            q(位置(n), 2) = "不足"
'                        End If
'                    End If
                Else
                    If i > 位置(n) Then q(i, 1) = "重複"
                End If
            End If
        Next
    End If
Next
With Range("G1").Resize(UBound(q), UBound(q, 2))
    .Clear
    .Value = q
End With
Set MyT = Nothing
Erase r, x, y, v, w, 開始, 終了, 位置, q
End Sub
すみません。ディクショナリーをやめて多段配列にしました。m(__)m
よくよく考えたら全く同じというのもあるんじゃないかと。。ど素人ぶり発揮かもしれません。(^^;
（SoulMan） 2021/05/05(水) 11:34

 質問者さんからの反応が無いのが残念ですが、遅ればせですが、マクロを私も書いてみました。

 利用者 > 日付 > 開始時刻　の優先順位でソートしておきます。

 以下のマクロを実行すると、
   ・G列に 重複判定
   ・H列に 不足判定
 が記入されるはずです。

 Sub 重複_不足を調べる()
     Dim 日付    As Date
     Dim 利用者  As String
     Dim 開始時  As Date
     Dim 終了時  As Date
     Dim c日付   As Date     '現在(直前）の時点での日付
     Dim c利用者 As String   '同                   利用者
     Dim c終了時 As Date     '同                 　終了時刻
     Dim k       As Long

     '最初のデータだけ特別扱い---------
     c日付 = Cells(2, "B")
     c利用者 = Cells(2, "C")
     c終了時 = Cells(2, "F")

     For k = 3 To Cells(Rows.Count, "B").End(xlUp).Row
         'データ読み込み --------------
         日付 = Cells(k, "B")
         利用者 = Cells(k, "C")
         開始時 = Cells(k, "E")
         終了時 = Cells(k, "F")

         '判定 ------------------------
         If 利用者 <> c利用者 Then   '利用者が異なれば、情報を更新するだけ。重複も不足も発生せず            
             c利用者 = 利用者
             c日付 = 日付
             c終了時 = 終了時
         ElseIf c日付 <> 日付 Then   '利用者は同じだが、別の日付になったとき。
             c日付 = 日付
             c終了時 = 終了時
         Else                        '利用者及び日付が連続したとき。
                                     'それまでの最新の終了時刻と開始時刻を比較。
             If 開始時 < c終了時 Then
                 Cells(k, "G") = "重複"
             ElseIf 開始時 <= c終了時 + 10 Then
                 Cells(k, "H") = "不足"
             End If
             c終了時 = Application.Max(c終了時, 終了時)  '終了時刻を更新。
         End If
     Next
 End Sub

 ■不慣れのワークシート関数で対応するなら、こんな感じかな。
   ロジックは上記のマクロと同じです。

   A列からF列までは同様ですので、記載省略しました。

 　　　G列      H列         I列             J列             K列
 1行  重複判定  不足判定    利用者新規判定  日付新規判定    暫定終了時間
 2                                                          =F2
 3     （計算式は下記）
 4

 G2:J2 はブランク。

 G3:  =IF(OR(I3="新規",J3="新規"),"",IF(E3<K2,"重複",""))
 H3:  =IF(OR(I3="新規",J3="新規"),"",IF(AND(G3<>"重複",E3<K2+"00:10"),"不足","")) 
 I3:  =IF(C3<>C2,"新規","")
 J3:  =IF(B3<>B2,"新規","")
 K3:  =IF(OR(I3="新規",B3<>B2),F3,MAX(K2,F3))
 4行目以下は、3行目をコピーします。

 ワークシート式のほうがたぶんメンテナンスしやすいと思います。
 作業項目はもっと右端に移しても勿論問題ありません。
（γ） 2021/05/10(月) 06:42

コメント返信：

[ 一覧(最新更新順) ]

[[20210504170614]] 『サービス時間の重複を調べたい』（マロンドプロ） ページの最後に飛ぶ

[[20210504170614]]　『サービス時間の重複を調べたい』（マロンドプロ）　ページの最後に飛ぶ