Question

求EXCEL VBA程序：在 EXCECL的A1到A5里依次有数据1，3，9，7，6。要在A6里输出这些数据中除以4余1的

数据的个数。最好做成EXCEL自定义函数。

Answer 1

Public Function iMod4(Res, iRng As Range)
'VBA函数，iRng内，除以4余数为res的个数
Application.Volatile
If iRng.Cells.Count < 1 Then iMod4 = "#iRng": Exit Function
Dim c As Range, n
For Each c In iRng
If c Mod 4 = Res Then n = n + 1
Next
iMod4 = n
End Function
---------
使用公式：
=iMod4(1,a1:c5)
可以求其他的余数，比如求除4余数2的个数：
=iMod4(2,a1:c5)

Answer 2

Sub A()
Dim i As Integer, b As Integer
For i = 1 To 5
If Cells(i, 1).Value Mod 4 = 1 Then
b = b + 1
End If
Next
Cells(6, 1).Value = b
End Sub

追问

这没做成EXCEL自定义函数

追答

Function OK(ByVal TT As Range)
Dim i As Integer, b As Integer
For Each cell In TT
If cell.Value Mod 4 = 1 Then
b = b + 1
End If
Next
OK = b
End Function
把函数复制到模块里吧

Answer 3

=COUNTIF(mod((A10:E10),4),"=1")
不知道上面的函数为什么不行。
Function ss1(divi1 As Range) As Integer
Dim r1 As Range
ss1 = 0
For Each r1 In divi1
If r1.Value Mod 4 = 1 Then
ss1 = ss1 + 1
End If
Next r1
End Function

或者用 数组公式
=SUM(N(MOD((A10:E10),4)=1))