|
- Sub 提取()
- Dim arr, i%, n&, m%, Z&, brr(1 To 1000, 1 To 1), CRR(), reg As Object, matches As Object, dic As Object
- arr = Range(Range("a1"), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))
- Set reg = CreateObject("vbscript.regexp")
- For i = 1 To UBound(arr)
- With reg
- .Global = True
- .Pattern = "[A-Z]?[\u4e00-\u9fa5]{2,}|\d+"
- Set matches = .Execute(arr(i, 1))
- For Each Match In matches
- n = n + 1
- brr(n, 1) = Match
- Next
- End With
- Next
- ReDim CRR(1 To n / 2, 1 To 2)
- For Z = 1 To n Step 2
- m = m + 1
- CRR(m, 1) = brr(Z, 1)
- CRR(m, 2) = brr(Z + 1, 1)
- Next
- Set dic = CreateObject("scripting.dictionary")
- For m = 1 To UBound(CRR)
- dic(CRR(m, 1)) = dic(CRR(m, 1)) + Val(CRR(m, 2))
- Next
- Range("h13").Resize(dic.Count, 1) = Application.Transpose(dic.keys)
- Range("i13").Resize(dic.Count, 1) = Application.Transpose(dic.items)
- End Sub
复制代码 我就是打个酱油,向烟花版主学习 |
|