[已解决]数值在一定范围对应值的次数

陈瑞云

数值在一定范围对应值的次数

最佳答案

月排行榜 / 总排行榜

心正意诚身修

2015-12-28 11:34

=SUMPRODUCT(($F$4:$F$31<=4)*($F$4:$F$31>=0)*($E$4:$E$31=H4))

跳转到最佳答案楼层

心正意诚身修

=SUMPRODUCT(($F$4:$F$31<=4)*($F$4:$F$31>=0)*($E$4:$E$31=H4))

晓米大大

我也想学

心正意诚身修

晓米大大 发表于 2015-12-28 11:36
我也想学

明年報班學習吧.

sry660

1. Sub 计算次数()
   
2. Dim arr, i&, n&, d, x, aa, brr, t$
   
3. arr = [e4:f31]
   
4. Set d = CreateObject("scripting.dictionary")
   
5. For i = 1 To UBound(arr)
   
6.     x = arr(i, 1)
   
7.     If arr(i, 2) >= 0 And arr(i, 2) < 4 Then
   
8.         d(x) = d(x) & x & ","
   
9.     End If
   
10. Next
    
11. aa = d.keys
    
12. ReDim brr(1 To d.Count, 1 To 2)
    
13. For n = 0 To UBound(aa)
    
14.     brr(n + 1, 1) = n
    
15.     t = d(Format(n, "0"))
    
16.     t = Left(t, Len(t) - 1)
    
17.     If InStr(t, ",") > 0 Then
    
18.         brr(n + 1, 2) = UBound(Split(t, ",")) + 1
    
19.     Else
    
20.         brr(n + 1, 2) = 1
    
21.     End If
    
22. Next
    
23. [h6:q7].ClearContents
    
24. [h6].Resize(UBound(brr, 2), UBound(brr, 1)) = Application.Transpose(brr)
    
25. End Sub

复制代码