由于我要处理大量的数据,内容是计算各时期不同公司的市盈率方差,但是要剔除其中小于零的市盈率(也就是市盈率小于零的公司在相应的时期不包括在内),我的具体代码是这样的:
Function varif(rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim p As Double
Dim q As Integer
Dim m As Double
MaxRows = rng.Rows.Count
MaxCols = rng.Columns.Count
p = 0
q = 0
For i = 1 To MaxRows
For j = 1 To MaxCols
If rng(i, j) > 0 Then
p = p + rng(i, j)
q = q + 1
End If
Next j
Next i
i = 1
j = 1
m = 0
For i = 1 To MaxRows
For j = 1 To MaxCols
If rng(i, j) > 0 Then
m = m + (rng(i, j) - p / q) * (rng(i, j) - p / q)
End If
Next j
Next i
varif = m / (q - 1)
End Function
现在的问题是,当我在一列中选择400多个数据的时候计算正常,但是超过了500左右的样子计算出来的就是空值!求高手指点!