'不会公式,特地查了一下这个公式使用帮助,大概明白是怎么回事了。输出到I列,为了比较用,,,
Option Explicit
Sub test()
Dim arr, i, j, k, p
arr = [a1].CurrentRegion.Offset(1).Resize(, 5)
For i = 1 To UBound(arr, 1) - 1
arr(i, 1) = LCase(arr(i, 1)): arr(i, 4) = 0: arr(i, 5) = i
Next
Call qsort(arr, 1, UBound(arr, 1) - 1, 1, UBound(arr, 2), 1)
For i = 1 To UBound(arr, 1) - 1
If arr(i, 1) <> arr(i + 1, 1) Then
For j = p + 1 To i
For k = p + 1 To i
If arr(k, 3) >= arr(j, 3) - 35 And arr(k, 3) <= arr(j, 3) - 7 Then arr(j, 4) = arr(j, 4) + arr(k, 2)
Next
Next
p = i
End If
Next
Call qsort(arr, 1, UBound(arr, 1) - 1, 1, UBound(arr, 2), 5)
[f2].Resize(UBound(arr, 1) - 1, UBound(arr, 2) - 1) = arr
End Sub
Sub qsort(arr, first, last, left, right, key)
Dim i As Long, j As Long, k As Long, x, t
i = first: j = last: x = arr((first + last) / 2, key)
While i <= j
While arr(i, key) < x: i = i + 1: Wend
While x < arr(j, key): j = j - 1: Wend
If i <= j Then
For k = left To right
t = arr(i, k): arr(i, k) = arr(j, k): arr(j, k) = t
Next
i = i + 1: j = j - 1
End If
Wend
If first < j Then qsort arr, first, j, left, right, key
If i < last Then qsort arr, i, last, left, right, key
End Sub |