|
Sub 提取数据2月数据() '提取数据
Sheet6.Range("a5:l10000").ClearContents
Dim arr, dic As Object, i, j
arr = Sheet3.Range("a1").CurrentRegion
Set dic = CreateObject("scripting.dictionary")
For j = 3 To 7 Step 4
yf = Sheet6.Cells(2, j)
ReDim brr(1 To UBound(arr), 1 To 3)
dic.RemoveAll
m = 0
For i = 3 To UBound(arr)
If VBA.Month(CDate(arr(i, 2))) & "月份" = yf Then
s = arr(i, 1)
If Not dic.exists(s) Then
m = m + 1
brr(m, 1) = s
brr(m, 2) = 1
brr(m, 3) = arr(i, 8)
dic(s) = m
Else
r = dic(s)
brr(r, 2) = brr(r, 2) + 1
brr(r, 3) = brr(r, 3) + arr(i, 8)
End If
End If
Next
Sheet6.Cells(5, j - 1).Resize(m, 3) = brr
Next
End Sub
Sub 新增客户()
arr = Sheet6.Range("b5:b" & Sheet6.Cells(Rows.Count, "b").End(xlUp).Row)
brr = Sheet6.Range("f5:f" & Sheet6.Cells(Rows.Count, "f").End(xlUp).Row)
ReDim crr(1 To (UBound(arr) + UBound(brr)), 1 To 1)
Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
dic(arr(i, 1)) = 1
Next
For j = 1 To UBound(brr)
If dic(brr(j, 1)) <> 1 Then
m = m + 1
crr(m, 1) = brr(j, 1)
End If
Next
Sheet6.Range("j5").Resize(m, 1) = crr
Call 流失客户
End Sub
Sub 流失客户()
brr = Sheet6.Range("b5:b" & Sheet6.Cells(Rows.Count, "b").End(xlUp).Row)
arr = Sheet6.Range("f5:f" & Sheet6.Cells(Rows.Count, "f").End(xlUp).Row)
ReDim crr(1 To (UBound(arr) + UBound(brr)), 1 To 1)
Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
dic(arr(i, 1)) = 1
Next
For j = 1 To UBound(brr)
If dic(brr(j, 1)) <> 1 Then
m = m + 1
crr(m, 1) = brr(j, 1)
End If
Next
Sheet6.Range("l5").Resize(m, 1) = crr
End Sub
|
|