需求:
Excel的统计字符数
实现:
Excel中,开发工具-插入-模块-复制粘贴以下代码:
Sub 总字符数()
Arr = Range([A2], [A65536].End(xlUp))
Set d = CreateObject("Scripting.Dictionary")
For Each a In Arr
b = Split(a, ",")
For Each bb In b
d(bb) = ""
Next
Erase b
Next
Worksheets("Sheet1").Range("C2").Value = d.Count
End Sub
Sub 每格字符()
For Each Rng In [A2:A65536]
Rng.Offset(0, 1) = UBound(Split(Rng, ",")) + 1
If Rng.Offset(0, 1) = 0 Then Rng.Offset(0, 1) = ""
Next
End Sub
点击运行按钮,即可获取结果,如下:
注意:运行代码时,必须打开需要统计的Excel表。
如果名称有空,每格字符可以使用以下方式统计:
Sub 每格字符()
For Each Rng In [A2:A65536]
If Not IsEmpty(Rng) Then
Rng.Offset(0, 1) = UBound(Split(Rng, ",")) + 1
End If
Next
End Sub