楼主
liuguansky |
Q:如何返回两日期之间每个销售人员的有效率与退单率？
A:用如下代码可以实现：
- Sub justtest()
- Dim arr, i&, d, k&, t1&, t2&
- Dim d1, d2, arrt(), ar1, s1&, s2&, t
- t = Timer
- d1 = Range("b1").Value: d2 = Range("d1").Value
- If d1 > d2 Or d1 = "" Or d2 = "" Then MsgBox "请确认日期输入。": Exit Sub
- Set d = CreateObject("scripting.dictionary")
- With Sheets("数据源")
- k = .Cells(.Rows.Count, 1).End(3).Row - 1
- arr = .Cells(2, 1).Resize(k, 3).Value
- End With
- For i = 1 To k
- If arr(i, 3) <> "" And CDate(arr(i, 3)) >= d1 And CDate(arr(i, 3)) <= d2 Then
- If arr(i, 1) = "有效" Then
- t1 = 1: t2 = 0
- ElseIf arr(i, 1) = "退单" Then
- t1 = 0: t2 = 1
- End If
- If d.exists(arr(i, 2)) Then
- d(arr(i, 2)) = Array(d(arr(i, 2))(0) + t1, d(arr(i, 2))(1) + t2)
- Else: d.Add arr(i, 2), Array(t1, t2)
- End If
- End If
- Next i
- Range("a4:c" & Rows.Count).ClearContents
- If d.Count > 0 Then
- ReDim arrt(1 To d.Count, 1 To 3)
- ar1 = d.keys
- For i = 1 To d.Count
- arrt(i, 1) = ar1(i - 1)
- s1 = d(ar1(i - 1))(0): s2 = d(ar1(i - 1))(1)
- If s1 > 0 Then arrt(i, 2) = Format(s1 / (s1 + s2), "0.00%") & "(" & s1 & "/" & s1 + s2 & ")"
- If s2 > 0 Then arrt(i, 3) = Format(s2 / (s1 + s2), "0.00%") & "(" & s2 & "/" & s1 + s2 & ")"
- Next i
- Cells(3, 1).Resize(i - 1, 3) = arrt
- End If
- MsgBox "处理完成，用时" & Timer - t & "秒"
- Set d = Nothing
- End Sub
示例如下：
|