|
用函数简单一点:
C3=IF(ABS(COUNTIF(A3:A1001,">="&LARGE(A3:A1001,INT(COUNT(A3:A1001)*0.9)))-INT(COUNT(A3:A1001)*0.9))>ABS(COUNTIF(A3:A1001,">="&LARGE(A3:A1001,INT(COUNT(A3:A1001)*0.9)-1))-INT(COUNT(A3:A1001)*0.9)),LARGE(A3:A1001,INT(COUNT(A3:A1001)*0.9)-1),LARGE(A3:A1001,INT(COUNT(A3:A1001)*0.9)))
如一定要用VBA :
Sub Macro1()
Dim X, Y, Z
Range("C3") = _
"=LARGE(RC[-2]:R[998]C[-2],INT(COUNT(RC[-2]:R[998]C[-2])*0.9))"
Range("C4") = "=COUNTIF(R[-1]C[-2]:R[997]C[-2],"">=""&R[-1]C)"
X = Range("C4")
Range("C3") = _
"=LARGE(RC[-2]:R[998]C[-2],INT(COUNT(RC[-2]:R[998]C[-2])*0.9)-1)"
Y = Range("C4")
Range("C3") = "=INT(COUNT(RC[-2]:R[998]C[-2])*0.9)"
Z = Range("C3")
If Z - Y > X - Z Then
Range("C3") = _
"=LARGE(RC[-2]:R[998]C[-2],INT(COUNT(RC[-2]:R[998]C[-2])*0.9))"
Else
Range("C3") = _
"=LARGE(RC[-2]:R[998]C[-2],INT(COUNT(RC[-2]:R[998]C[-2])*0.9))"
End If
Range("C3:C4").Value = Range("C3:C4").Value
End Sub |
|