|
一直停发表中标记也可以,不过一直停发表是根据日期段筛选的结果,有些重复停发无续发的人员不能在这里体现
Sub AwDemo()
Dim i&, r&, j%, s%, x%, y%, DayM%, iStr$, DayStr$, begDay$, endDay$
Dim iDay As Date, rng As Range, arr, d As Object
Application.ScreenUpdating = False
Set d = CreateObject("Scripting.Dictionary")
begDay = InputBox("请输入开始日期,格式如:202001")
If StrPtr(begDay) = 0 Then Exit Sub
endDay = InputBox("请输入结束日期,格式如:202001")
If StrPtr(endDay) = 0 Then Exit Sub
With Sheets("数据")
.[a1:f83015].Sort .[a1], , .[c1], , Header:=xlYes
arr = .[a1].CurrentRegion
For i = 2 To UBound(arr)
If arr(i, 4) = "停发" Then s = -1 Else s = 1
iStr = arr(i, 1) & "|" & arr(i, 2)
d(iStr & "|" & "Num") = d(iStr & "|" & "Num") + 1
d(iStr & "|" & "Sum") = d(iStr & "|" & "Sum") + s
d(iStr) = d(iStr & "|" & "Num") & "|" & d(iStr & "|" & "Sum")
Next
ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2) + 1)
For j = 1 To UBound(arr, 2)
brr(1, j) = arr(1, j)
Next
brr(1, UBound(brr, 2)) = "停发年次"
r = 1
For i = 2 To UBound(arr)
iStr = arr(i, 1) & "|" & arr(i, 2)
x = Split(d(iStr), "|")(0): y = Split(d(iStr), "|")(1)
If arr(i, 3) >= begDay And arr(i, 3) <= endDay Then
If x = Abs(y) And y < 0 Then
r = r + 1
For j = 1 To UBound(arr, 2)
brr(r, j) = arr(i, j)
Next
DayStr = Left(brr(r, 3), 4) & "-" & Right(brr(r, 3), 2)
iDay = CDate(DayStr)
DayM = DateDiff("m", iDay, CDate(Left(endDay, 4) & "-" & Right(endDay, 2)))
If DayM > 0 Then brr(r, UBound(brr, 2)) = DayM \ 12 & "年" & DayM Mod 12 & "个月"
End If
End If
Next
End With
With Sheets("一直停发")
.Cells.Clear
If r > 0 Then
.[a1].Resize(r, UBound(brr, 2)) = brr
.[a1].CurrentRegion.Interior.ColorIndex = 0
arr = .[a1].CurrentRegion
For i = 2 To UBound(arr)
iStr = arr(i, 1) & "|" & arr(i, 2)
x = Split(d(iStr), "|")(0): y = Split(d(iStr), "|")(1)
If x = Abs(y) And x > 1 Then
If rng Is Nothing Then
Set rng = .Cells(i, 2)
Else
Set rng = Union(rng, .Cells(i, 2))
End If
End If
Next
If Not rng Is Nothing Then rng.Interior.Color = vbRed
End If
End With
Application.ScreenUpdating = True
End Sub
|
评分
-
1
查看全部评分
-
|