excel用vba计算1至4列中，统计出每行数据大于6的数字和值
作者&投稿：达史 （若有异议请与网页底部的电邮联系）
请高手帮忙在EXCEL中用VBA计算数字的个数。~
一：公式
个数：
=COUNTIF(A1:D500,">6")
总和：
=SUMIF(A1:D500,">6",A1:D500)
二：代码
Sub 求个数()
Dim N&
N = Application.WorksheetFunction.CountIf(Sheet1.Range("A1:D500"), ">6")
End Sub
Sub 求总和()
Dim N&
N = Application.WorksheetFunction.SumIf(Sheet1.Range("A1:D500"), ">6", Sheet1.Range("A1:D500"))
End Sub
此VBA功能为：将所有大于6的数一次放到了第5列，从第一行开始放。个数输出至F1
sub test()
dim i,j,k,l as integer
l=1
for i=1 to 4
for j=1 to 500
if Sheets("表名").cells(i,j)>6 then
Sheets("表名").cells(l,5)=Sheets("表名").cells(i,j)
l=l+1
k=k+1
end if
next j
next i
Sheets("表名").cells(1,6)=k
end sub
Sub 计数()
Dim x%, y%, i%,n
For x = 1 To 500
For y = 1 To 4
If Cells(x, y) > 6 Then i = i + 1: n = n + Cells(x, y)
Next y
Cells(x, 5) = i'第5列写入个数
Cells(x,6)=n'第6列写入和
i = 0:n=0
Next x
End Sub
Sub 求和()
For n = 1 To [d65535].End(xlUp).Row
Cells(n, 5).Select
Selection.FormulaArray = "=SUM(IF(RC[-4]:RC[-1]>6,RC[-4]:RC[-1]))"
Next
End Sub
样本文件见附件（两种方案，一种为自定义函数，一种是宏代码）
自定义函数用法如下：
代码如下：
Function 数字(ByVal rg As Range) As IntegerDim d As ObjectSet d = CreateObject("scripting.dictionary")For Each c In rg If Len(c) > 0 And VBA.IsNumeric(c) Then d(c.Value) = "" End IfNext数字 = d.CountEnd Function
你前面的语句没有问题，可以简化为
X = 6
For R = 2 To 20
If WorksheetFunction.Sum(Range(Cells(R, X), Cells(R, X + 4))) > 0 Then
Range(Cells(R, X + 5), Cells(R, X + 9)) = Range(Cells(R, X), Cells(R, X + 4)).Value
Else
Cells(R, X + 5) = WorksheetFunction.Sum(Range(Cells(R, X), Cells(R, X + 4)))
Range(Cells(R, X + 6), Cells(R, X + 9)) = 0
End If
Next
后面 For k = 5 To 9以后的语句有问题，变量J是干嘛的看不懂。
一：公式
个数：
=COUNTIF(A1:D500,">6")
总和：
=SUMIF(A1:D500,">6",A1:D500)
二：代码
Sub 求个数()
Dim N&
N = Application.WorksheetFunction.CountIf(Sheet1.Range("A1:D500"), ">6")
End Sub
Sub 求总和()
Dim N&
N = Application.WorksheetFunction.SumIf(Sheet1.Range("A1:D500"), ">6", Sheet1.Range("A1:D500"))
End Sub
此VBA功能为：将所有大于6的数一次放到了第5列，从第一行开始放。个数输出至F1
sub test()
dim i,j,k,l as integer
l=1
for i=1 to 4
for j=1 to 500
if Sheets("表名").cells(i,j)>6 then
Sheets("表名").cells(l,5)=Sheets("表名").cells(i,j)
l=l+1
k=k+1
end if
next j
next i
Sheets("表名").cells(1,6)=k
end sub
Sub 计数()
Dim x%, y%, i%,n
For x = 1 To 500
For y = 1 To 4
If Cells(x, y) > 6 Then i = i + 1: n = n + Cells(x, y)
Next y
Cells(x, 5) = i'第5列写入个数
Cells(x,6)=n'第6列写入和
i = 0:n=0
Next x
End Sub
Sub 求和()
For n = 1 To [d65535].End(xlUp).Row
Cells(n, 5).Select
Selection.FormulaArray = "=SUM(IF(RC[-4]:RC[-1]>6,RC[-4]:RC[-1]))"
Next
End Sub
《excel vba中编程实现:选择第二列到第四列 ! 用到2和4 不要用列标 谢谢...》
答：cells 的单元格表示方式是使用数字的 cells(1,1)和A1单元格是一致的
