[已解决]如何用VBA计算每个组别的不重复客户数？

左奕释然

如何用VBA计算不同分组的“不重复”客户数？求指导啊，不尽感激

最佳答案

月排行榜 / 总排行榜

橘子红

2016-5-11 15:36

1. Sub x()
   
2. Dim a, d, d1, x%, b, c(), s%
   
3. Set d = CreateObject("scripting.dictionary")
   
4. Set d1 = CreateObject("scripting.dictionary")
   
5. a = Range("a1").CurrentRegion
   
6. For x = 2 To UBound(a)
   
7.     d(a(x, 2)) = d(a(x, 2))
   
8. Next
   
9. b = d.keys
   
10. ReDim c(1 To d.Count, 1 To 2)
    
11.   For x = 1 To d.Count
    
12.      c(x, 1) = b(x - 1)
    
13.      For s = 2 To UBound(a)
    
14.          If a(s, 2) = c(x, 1) Then d1(b(x - 1) & a(s, 1)) = ""
    
15.      Next
    
16.      c(x, 2) = d1.Count: d1.RemoveAll
    
17. Next
    
18. [e1].Resize(d.Count, 2) = c
    
19. End Sub
    

复制代码

跳转到最佳答案楼层

橘子红

1. Sub x()
   
2. Dim a, d, d1, x%, b, c(), s%
   
3. Set d = CreateObject("scripting.dictionary")
   
4. Set d1 = CreateObject("scripting.dictionary")
   
5. a = Range("a1").CurrentRegion
   
6. For x = 2 To UBound(a)
   
7.     d(a(x, 2)) = d(a(x, 2))
   
8. Next
   
9. b = d.keys
   
10. ReDim c(1 To d.Count, 1 To 2)
    
11.   For x = 1 To d.Count
    
12.      c(x, 1) = b(x - 1)
    
13.      For s = 2 To UBound(a)
    
14.          If a(s, 2) = c(x, 1) Then d1(b(x - 1) & a(s, 1)) = ""
    
15.      Next
    
16.      c(x, 2) = d1.Count: d1.RemoveAll
    
17. Next
    
18. [e1].Resize(d.Count, 2) = c
    
19. End Sub
    

复制代码

左奕释然

橘子红 发表于 2016-5-11 15:36

谢谢大神！！！！受教了呀！{:1112:}