|
本帖最后由 lovelfg 于 2017-8-17 16:48 编辑
如附件所示,A列内有这样的一系列数字
Q3 | 101 | 102 | 101/102 | 103 | 101/102/103 | 104 | 109/1011/1012 | 106 | 101/102/103 | 106 | 1011/1012 | 102/108 | 101/107 | 107 | 106 | 106 | 105/1011/1012 | 107 | 现在B列内列出所有元素,要统计出每种数字出现的次数,附件内有我尝试写的代码,但可惜完全是错的,求教高人指点一下
Sub test()
Dim i, j, k
Dim arr, n
Do While Range("B" & j) <> ""
For i = 2 To Range("A65535").End(xlUp).Row
arr = Split(Range("A" & i))
For Each n In arr
If Val(n) = Range("B" & j) Then
k = k + 1
End If
Next
Range("C" & j) = k
j = j + 1
Next
Loop
End Sub
- Sub tt()
- Dim i%, arr, brr, d, n%
- arr = Range("a1").CurrentRegion
- Set d = CreateObject("scripting.dictionary") '创建字典
- For i = 2 To UBound(arr)
- brr = Split(arr(i, 1), "/") '数据放到brr
- For n = 0 To UBound(brr)
- If Not d.Exists(brr(n)) Then '用字典判断是否重复
- d(brr(n)) = Array(brr(n), 1)
- Else
- d(brr(n)) = Array(brr(n), d(brr(n))(1) + 1)
- End If
- Next n, i
- Range("b2").Resize(d.Count, 2) = Application.Transpose(Application.Transpose(d.items))
- End Sub
复制代码
|
|