Option Explicit
Const TM = 10 '跑10s随机数
Sub test()
Dim arr, brr, crr, i, j, t, sum, dt, n, a, b, c, d, e, f, cnt
arr = Range("b5:f" & Cells(Rows.Count, "b").End(xlUp).Row)
brr = [b2:f2]
Randomize
dt = Timer
ReDim sum(3 To UBound(arr, 2))
d = 1: e = 1: f = 1
Do
For i = 1 To UBound(arr, 1)
n = Int(Rnd * (UBound(arr, 1) - i + 1)) + i
For j = 1 To UBound(arr, 2)
t = arr(i, j): arr(i, j) = arr(n, j): arr(n, j) = t
If j > 2 Then sum(j) = sum(j) + arr(i, j)
Next
a = Abs(sum(3) - brr(1, 3)) / brr(1, 3)
b = Abs(sum(4) - brr(1, 4)) / brr(1, 4)
c = Abs(sum(5) - brr(1, 5)) / brr(1, 5)
If a < 0.05 And b < 0.05 And c < 0.05 Then
If a < d And b < e And c < f Then
d = a: e = b: f = c
cnt = i: crr = arr:
Debug.Print Timer - dt
End If
End If
Next
ReDim sum(3 To UBound(arr, 2))
DoEvents
Loop Until Timer - dt > TM
If cnt = 0 Then MsgBox "!": Exit Sub
With [l5]
.Resize(Rows.Count - 4, UBound(crr, 2)).ClearContents
.Resize(cnt, UBound(crr, 2)) = crr
End With
End Sub |