我试图对一列求平均值,但前提是该值大于零。然后我希望它将信息放在该行的下一个空白单元格中。
下面的代码是一个简单的Average,但我希望它从上面的单元格中排除任何零值。
With Range("D2")
.End(xlDown)(2, 1) = _
"=AVERAGE(" & .Address & ":" & .End(xlDown).Address & ")"
End With
我试着用下面的代码把它当作单元格地址大于零。但它总是给我一个错误来调试?
With Range("D2")
.End(xlDown)(2, 1) = _
"=AVERAGEIF(" & .Address & ":" & .End(xlDown).Address & "," & Cell.Address & " > 0," & .Address & ":" & .End(xlDown).Address & ")"
End With
任何帮助都会很棒。
谢谢Al
您的公式语法错误。
你需要创建一个类似的公式
=AVERAGEIF(D2:Dxx, ">0")
所以使用这个
With Range("D2")
.End(xlDown)(2, 1) = _
"=AVERAGEIF(" & .Address & ":" & .End(xlDown).Address & ","">0"")"
End With