用VBA肯定能做,但是公式岂不是更方便??
第一步:建立一个序号数组并名称引用
其中名称输入Number,引用位置:
={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100}
第二步:输入公式
假设A3单元格有数据“A A C C C 休 休 A C C1 M 休 A A C1 C 休”,那么在B3单元格输入以下公式可计算其中字母的个数,排除汉字和数字:
{=COUNT(IF(CODE(MID(A3,Number,1))>=65,IF(CODE(MID(A3,Number,1))<=122,CODE(MID(A3,Number,1)),""),""))}
注意这个为数组公式,输入时不要输入大括号,输完后按“Ctrl+Shift+回车”形成数组公式。
附件为现成的公式~
第一步:建立一个序号数组并名称引用
在名称管理器里新建一个名称
其中名称输入Number,引用位置:
={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100}
第二步:输入公式
假设A3单元格有数据“A A C C C 休 休 A C C1 M 休 A A C1 C 休”,那么在B3单元格输入以下公式可计算其中字母的个数,排除汉字和数字:
{=COUNT(IF(CODE(MID(A3,Number,1))>=65,IF(CODE(MID(A3,Number,1))<=122,CODE(MID(A3,Number,1)),""),""))}
注意这个为数组公式,输入时不要输入大括号,输完后按“Ctrl+Shift+回车”形成数组公式。
继续追问可以发送个附件,有现成的公式,刚才在电脑上没法修改,现在是用的手机
我要是想统计所有的字母总和呢,就是A,C,C1,M等所有总和?
追答如果非字母的只有一个"休'字的话,可以运用公式=counta(a:a)-countif(a:a,"休")
或者简化一下=countif(a:a,"休")
是的呢
追答ALT+F11快捷键,在EXCEL文件名上点右键,点插入模块,输入以下代码:
Function GetENum(yStr As String)
Dim I As Long
Dim J As Long
Dim K As Long
Dim N As Long
N = 0
J = Len(yStr)
For I = 1 To J
K = Asc(Mid(yStr, I, 1))
If (K > 64 And K 96 And K < 123) Then
N = N + 1
End If
Next
GetENum = N
End Function
完成后在需要统计的列旁边插入一列,比如文件在A列,插入的是B列,则在B2输入=GetENum(A2),完成后向下拖拉复制就可以了
我不太看得懂呢~
追答假定原始数据在A列,结果在B列:
Sub try()
Dim a$, i As Integer, zh As Integer
Dim rng As Range
For Each rng In Range("A1:A" & Range("A65536").End(xlUp).Row)
a$ = rng.Value
zh = 0
For i = 65 To 90
zh = zh + Len(a$) - Len(Replace(a$, Chr(i), ""))
Next i
For i = 97 To 122
zh = zh + Len(a$) - Len(Replace(a$, Chr(i), ""))
Next i
rng.Offset(, 1) = zh
Next
End Sub
Sub try()
Dim a$, i As Integer, zh As Integer
Dim rng As Range
For Each rng In Range("A1:A" & Range("A65536").End(xlUp).Row)
a$ = rng.Value
zh = 0
For i = 1 To Len(a$)
If Mid(a$, i, 1) Like "[A-Za-z]" Then
zh = zh + 1
End If
Next i
rng.Offset(, 1) = zh
Next
End Sub