Question

excel表格怎么计算有多少个数字1数字2,如图,怎么知道这一列有多少个2多少个7多少个1

Answer 1

在宏中添加以下自定义函数，然后在要显示统计结果的单元格里输入
=CalcNumbers(被统计的范围)
就可以了。

Public Function CalcNumbers(calcRange As Range)
Dim tmpCell As Range Dim newString As String
newString = ""
For Each tmpCell In calcRange
newString = newString & tmpCell.Value
Next

Dim resultString As String
resultString = ""
For i = 0 To 9
counts = Len(newString) - Len(VBA.Replace(newString, i, ""))
resultString = resultString & "[" & i & "]有[" & counts & "]个" & vbCrLf
Next
CalcNumbers = resultString
End Function

Answer 2

计算7的个数:
=sumproduct(len(m1:m100))-sumproduct(len(substitute(m1:m100,7,)))
同理,计算1的个数,就将公式中的7改成1就行.

Answer 3

打开菜单栏里的数据——自动筛选——按条件设置就行