|
楼主 |
发表于 2014-12-6 16:31
|
显示全部楼层
写一段代码抛砖引玉,不相信公式能够解决的,代码会如此繁复
Sub Macro1()
Dim arr, d, i&, j%, zf$, z$
Set d = CreateObject("scripting.dictionary")
arr = Range("a1").CurrentRegion
For i = 2 To UBound(arr)
zf = arr(i, 1)
For j = 1 To Len(zf)
z = Mid(zf, j, 1)
d(z) = d(z) + 1
Next
Range("e1").Resize(1, d.Count) = d.keys
Range("e2").Resize(1, d.Count) = d.items
Range("e1").CurrentRegion.Sort Key1:=[e2], Order1:=xlDescending, Key2:=[e1], _
Order2:=xlAscending, Header:=xlGuess, Orientation:=xlLeftToRight
w = Range("e1").Resize(1, d.Count)
arr(i, 2) = Join(Application.Index(w, 1, 0), "")
Range("e1").CurrentRegion = ""
d.RemoveAll
Next
Range("b1").Resize(UBound(arr)) = Application.Index(arr, 0, 2)
End Sub
|
|