|
发表于 2014-5-13 19:48
|
显示全部楼层
本楼为最佳答案
- Sub Macro1()
- Dim arr, brr, crr, d, d2, i&, zf$, zf2$, zf3$
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- Range("b2:l60000").ClearContents
- arr = Sheet1.Range("a1").CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To 5)
- ReDim crr(1 To UBound(arr), 1 To 5)
- zf = "中断访问+业务成功+完成问卷/不同意+指定预约"
- zf2 = "完成问卷/不同意+业务成功"
- zf3 = "业务成功"
- For i = 2 To UBound(arr)
- x = Split(arr(i, 16))(0)
- If Not d.exists(x) Then
- s = s + 1
- d(x) = s
- brr(s, 1) = x
- brr(s, 2) = 1
- If InStr(zf, arr(i, 15)) Then brr(s, 3) = 1
- If InStr(zf2, arr(i, 15)) Then brr(s, 4) = 1
- If arr(i, 15) = zf3 Then brr(s, 5) = 1
- Else
- brr(d(x), 2) = brr(d(x), 2) + 1
- If InStr(zf, arr(i, 15)) Then brr(d(x), 3) = brr(d(x), 3) + 1
- If InStr(zf2, arr(i, 15)) Then brr(d(x), 4) = brr(d(x), 4) + 1
- If arr(i, 15) = zf3 Then brr(d(x), 5) = brr(d(x), 5) + 1
- End If
- If Not d2.exists(arr(i, 12)) Then
- s2 = s2 + 1
- d2(arr(i, 12)) = s2
- crr(s2, 1) = arr(i, 12)
- crr(s2, 2) = 1
- If InStr(zf, arr(i, 15)) Then crr(s2, 3) = 1
- If InStr(zf2, arr(i, 15)) Then crr(s2, 4) = 1
- If arr(i, 15) = zf3 Then crr(s2, 5) = 1
- Else
- crr(d2(arr(i, 12)), 2) = crr(d2(arr(i, 12)), 2) + 1
- If InStr(zf, arr(i, 15)) Then crr(d2(arr(i, 12)), 3) = crr(d2(arr(i, 12)), 3) + 1
- If InStr(zf2, arr(i, 15)) Then crr(d2(arr(i, 12)), 4) = crr(d2(arr(i, 12)), 4) + 1
- If arr(i, 15) = zf3 Then crr(d2(arr(i, 12)), 5) = crr(d2(arr(i, 12)), 5) + 1
- End If
- Next
- Range("b2").Resize(s, 5) = brr
- Range("h2").Resize(s2, 5) = crr
- Range("b2").Resize(s, 5).Sort [b2], Header:=xlGuess
- End Sub
复制代码 |
评分
-
查看全部评分
|