请高手帮忙在EXCEL中用VBA计算数字的个数。 EXCEL中怎样用VBA计算单元格中数字的和值?
作者&投稿:钞寇 (若有异议请与网页底部的电邮联系)
EXCEL中请用VBA统计数字个数并提取数。~
这个首先按ALT+F11可以进入VBA开发界面
然后你可以设置一个实时更新程序,当B:G发生改变时自动执行
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim j As Integer
On Error GoTo ERRORHANDLER
If Target.Column >= 2 And Target.Column <= 7 Then '如果第二列到第七列的值发生变化的话(B到G单元格)
i = 1
j = 0
Do While Me.Cells(i, 2).Value <> "" '当B列不为空时就一直执行
If Me.Cells(i, 2).Value >= 0 And Me.Cells(i, 2).Value <= 9 Then
j = j + 1
Else
End If
If Me.Cells(i, 3).Value >= 0 And Me.Cells(i, 3).Value <= 9 And Me.Cells(i, 3).Value <> Me.Cells(i, 2).Value Then
j = j + 1
End If
If Me.Cells(i, 4).Value >= 0 And Me.Cells(i, 4).Value <= 9 And Me.Cells(i, 4).Value <> Me.Cells(i, 2).Value And Me.Cells(i, 4).Value <> Me.Cells(i, 3).Value Then
j = j + 1
End If
If Me.Cells(i, 5).Value >= 0 And Me.Cells(i, 5).Value <= 9 And Me.Cells(i, 5).Value <> Me.Cells(i, 2).Value And Me.Cells(i, 5).Value <> Me.Cells(i, 3).Value And Me.Cells(i, 5).Value <> Me.Cells(i, 4).Value Then
j = j + 1
End If
If Me.Cells(i, 6).Value >= 0 And Me.Cells(i, 6).Value <= 9 And Me.Cells(i, 6).Value <> Me.Cells(i, 2).Value And Me.Cells(i, 6).Value <> Me.Cells(i, 3).Value And Me.Cells(i, 6).Value <> Me.Cells(i, 4).Value And Me.Cells(i, 6).Value <> Me.Cells(i, 5).Value Then
j = j + 1
End If
If Me.Cells(i, 7).Value >= 0 And Me.Cells(i, 7).Value <= 9 And Me.Cells(i, 7).Value <> Me.Cells(i, 2).Value And Me.Cells(i, 7).Value <> Me.Cells(i, 3).Value And Me.Cells(i, 7).Value <> Me.Cells(i, 4).Value And Me.Cells(i, 7).Value <> Me.Cells(i, 5).Value And Me.Cells(i, 7).Value <> Me.Cells(i, 6).Value Then
j = j + 1
End If
Me.Cells(i, 8).Value = j '将最后计算的个数赋值给H列
Loop
'最后将计算出的值赋给第二行第一列即B1单元格
End If
ERRORHANDLER:
End Sub
做好了,继续加分啊
'从B列第一行开始逐个统计,结果显示在C列
Sub tj()
For r = 1 To Range("B65536").End(xlUp).Row
Dim a
a = Cells(r, 2)
Dim n
n = Len(a)
Dim x As Integer
For i = 1 To n
x = x + Mid(a, i, 1)
Next
Cells(r, 3) = x
x = 0
Next
End Sub
样本文件见附件(两种方案,一种为自定义函数,一种是宏代码)
自定义函数用法如下:
代码如下:
Function 数字(ByVal rg As Range) As Integer
Dim d As Object
Set d = CreateObject("scripting.dictionary")
For Each c In rg
If Len(c) > 0 And VBA.IsNumeric(c) Then
d(c.Value) = ""
End If
Next
数字 = d.Count
End Function
这个首先按ALT+F11可以进入VBA开发界面
然后你可以设置一个实时更新程序,当B:G发生改变时自动执行
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim j As Integer
On Error GoTo ERRORHANDLER
If Target.Column >= 2 And Target.Column <= 7 Then '如果第二列到第七列的值发生变化的话(B到G单元格)
i = 1
j = 0
Do While Me.Cells(i, 2).Value <> "" '当B列不为空时就一直执行
If Me.Cells(i, 2).Value >= 0 And Me.Cells(i, 2).Value <= 9 Then
j = j + 1
Else
End If
If Me.Cells(i, 3).Value >= 0 And Me.Cells(i, 3).Value <= 9 And Me.Cells(i, 3).Value <> Me.Cells(i, 2).Value Then
j = j + 1
End If
If Me.Cells(i, 4).Value >= 0 And Me.Cells(i, 4).Value <= 9 And Me.Cells(i, 4).Value <> Me.Cells(i, 2).Value And Me.Cells(i, 4).Value <> Me.Cells(i, 3).Value Then
j = j + 1
End If
If Me.Cells(i, 5).Value >= 0 And Me.Cells(i, 5).Value <= 9 And Me.Cells(i, 5).Value <> Me.Cells(i, 2).Value And Me.Cells(i, 5).Value <> Me.Cells(i, 3).Value And Me.Cells(i, 5).Value <> Me.Cells(i, 4).Value Then
j = j + 1
End If
If Me.Cells(i, 6).Value >= 0 And Me.Cells(i, 6).Value <= 9 And Me.Cells(i, 6).Value <> Me.Cells(i, 2).Value And Me.Cells(i, 6).Value <> Me.Cells(i, 3).Value And Me.Cells(i, 6).Value <> Me.Cells(i, 4).Value And Me.Cells(i, 6).Value <> Me.Cells(i, 5).Value Then
j = j + 1
End If
If Me.Cells(i, 7).Value >= 0 And Me.Cells(i, 7).Value <= 9 And Me.Cells(i, 7).Value <> Me.Cells(i, 2).Value And Me.Cells(i, 7).Value <> Me.Cells(i, 3).Value And Me.Cells(i, 7).Value <> Me.Cells(i, 4).Value And Me.Cells(i, 7).Value <> Me.Cells(i, 5).Value And Me.Cells(i, 7).Value <> Me.Cells(i, 6).Value Then
j = j + 1
End If
Me.Cells(i, 8).Value = j '将最后计算的个数赋值给H列
Loop
'最后将计算出的值赋给第二行第一列即B1单元格
End If
ERRORHANDLER:
End Sub
Sub 统计不重复个数()
Dim d As Object
Set d = CreateObject("scripting.dictionary")
For i = 1 To [b65536].End(3).Row
For j = 2 To 7
d(Cells(i, j).Value) = ""
Next j
Cells(i, 8) = d.Count
d.RemoveAll
Next i
End Sub