'38楼附件,分6个小问题来处理,修改起来也方便
'[s3]应该为3。另T列有3处不同,你这规则不唯一,按我的理解分3种规则来处理的
'[a:c]列为源数据,其实列数可以不限的,自己修改,,,
Option Explicit
Sub test()
Dim arr, brr, i, j, k, dic, cnt
Set dic = CreateObject("scripting.dictionary")
brr = Range("a2:c" & Cells(Rows.Count, "a").End(xlUp).Row + 1).Value
ReDim arr(UBound(brr, 1), 1 To UBound(brr, 1))
For j = 1 To UBound(brr, 2)
For i = 1 To UBound(brr, 1) - 1
If Not dic.exists(brr(i, j)) Then
cnt = cnt + 1: dic(brr(i, j)) = cnt
arr(0, cnt) = brr(i, j): arr(i, cnt) = brr(i, j)
End If
For k = 1 To UBound(brr, 2)
If brr(i, k) = arr(0, dic(brr(i, j))) Then
arr(i, dic(brr(i, j))) = brr(i, k)
Exit For
End If
Next
Next
Next
ReDim brr(1 To cnt, 1 To 1 + 6)
For j = 1 To cnt
brr(j, 1) = arr(0, j)
Call qu1(arr, brr, j) '最大间隔/天
Call qu2(arr, brr, j) '上次服用间隔/天
Call qu3(arr, brr, j) '当前间隔/天
Call qu4(arr, brr, j) '最大连续服用/天
Call qu5(arr, brr, j) '上次连续服用间隔/次
Call qu6(arr, brr, j) '当前连续服用间隔/次
Next
[o13].Resize(UBound(brr, 1), UBound(brr, 2)) = brr '对比用,自己修改输出位置
End Sub
Sub qu1(arr, brr, col)
Dim i, j, p, n
p = 1
For i = 1 To UBound(arr, 1) - 1
If Len(arr(i, col)) > 0 Or i = UBound(arr, 1) - 1 Then
If i = UBound(arr, 1) - 1 And Len(arr(i, col)) = 0 Then
If i - p + 1 > n And i - p + 1 > 1 Then n = i - p + 1
Else
If i - p > n And i - p > 1 Then n = i - p
For j = i + 1 To UBound(arr, 1) - 1
If Len(arr(j, col)) = 0 Then p = j: i = j: Exit For
Next
End If
End If
Next
brr(col, 2) = n
End Sub
Function qu2(arr, brr, col)
Dim i, j
For i = UBound(arr, 1) - 1 To 1 Step -1
If Len(arr(i, col)) Then
For j = i - 1 To 1 Step -1
If Len(arr(j, col)) Then
If i - j > 1 Then brr(col, 3) = i - j - 1
i = 1: Exit For
End If
Next
End If
Next
If Len(brr(col, 3)) = 0 Then brr(col, 3) = 0
End Function
Function qu3(arr, brr, col)
Dim i
For i = UBound(arr, 1) - 1 To 1 Step -1
If Len(arr(i, col)) Then brr(col, 4) = UBound(arr, 1) - i - 1: Exit For
Next
End Function
Function qu4(arr, brr, col)
Dim i, j, n
For i = 1 To UBound(arr, 1)
If Len(arr(i, col)) Then
For j = i + 1 To UBound(arr, 1) - 1
If Len(arr(j, col)) = 0 Then
If j - i > 1 And j - i > n Then n = j - i
i = j: Exit For
End If
Next
End If
Next
If n > 0 Then brr(col, 5) = n Else brr(col, 5) = 0
End Function
Function qu5(arr, brr, col)
Dim i, j, k, n
For i = UBound(arr, 1) - 1 To 2 Step -1
If Len(arr(i, col)) Then n = n + 1
If Len(arr(i, col)) > 0 And Len(arr(i - 1, col)) Then
n = 0
For j = i - 1 To 1 Step -1
If Len(arr(j + 1, col)) = 0 And Len(arr(j, col)) > 0 Then n = n + 1
If Len(arr(j, col)) = 0 Then
n = 0
For k = j - 1 To 2 Step -1
If Len(arr(k, col)) > 0 And Len(arr(k - 1, col)) > 0 Then: j = 0: i = 0: Exit For
If Len(arr(k, col)) Then n = n + 1
Next
If k = 1 Then
If Len(arr(k, col)) Then n = n + 1
i = 0: Exit For
End If
End If
Next
End If
Next
If i = 1 And Len(arr(1, col)) > 0 Then n = n + 1
brr(col, 6) = n
End Function
Function qu6(arr, brr, col)
Dim i, j, n
For i = UBound(arr, 1) - 1 To 2 Step -1
If Len(arr(i, col)) > 0 And Len(arr(i - 1, col)) > 0 Then Exit For
If Len(arr(i, col)) Then n = n + 1
Next
If i = 1 Then n = 0
If n > 0 Then brr(col, 7) = n Else brr(col, 7) = 0
End Function |