VBA - 获取数组中第 n 个最大值的索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28468718/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA - Get index of nth largest value in an array
提问by doovers
I want to find the index of the nth largest value in an array. I can do the following but it runs into trouble when 2 values are equal.
我想找到数组中第 n 个最大值的索引。我可以执行以下操作，但是当 2 个值相等时会遇到麻烦。
fltArr(0)=31
fltArr(1)=15
fltArr(2)=31
fltArr(3)=52
For i = 0 To UBound(fltArr)
If fltArr(i) = Application.WorksheetFunction.Large(fltArr, n) Then
result = i
End If
Next
n=1 ---> 3
n=2 ---> 2 (but I want this to be 0)
n=3 ---> 2
n=4 ---> 1
n=1 ---> 3
n=2 ---> 2（但我希望这是 0）
n=3 ---> 2
n=4 ---> 1
采纳答案by brettdj
Uses a second array to quickly get what you want without looping through each element for every value of n
使用第二个数组快速获得您想要的内容，而无需为每个值循环遍历每个元素 n
Sub test()
Dim fltArr(0 To 3)
Dim X
Dim n As Long
Dim lngPos As Long
fltArr(0) = 31
fltArr(1) = 15
fltArr(2) = 31
fltArr(3) = 52
X = fltArr
For n = 1 To 4
lngPos = Application.WorksheetFunction.Match(Application.Large(X, n), X, 0) - 1
Debug.Print lngPos
X(lngPos) = Application.Max(X)
Next
End Sub
回答by El Scripto
Edit:
编辑：
Public Sub RunLarge()
Dim n%, i%, result%, count%
Dim fltArr(3) As Integer
Dim iLarge As Integer
fltArr(0) = 31:
fltArr(1) = 15:
fltArr(2) = 31:
fltArr(3) = 52
n = 1
Debug.Print " n", "iLarge", "result"
While n <= 4
count% = n - 1
iLarge = Application.WorksheetFunction.Large(fltArr, n)
For i = 0 To UBound(fltArr)
If fltArr(i) = iLarge Then
result = i
count% = count% - 1
If count% <= 0 Then Exit For
End If
Next
Debug.Print n, iLarge, result
n = n + 1
Wend
End Sub
result:
结果：
n iLarge result
1 52 3
2 31 0
3 31 2
4 15 1
回答by Simon
It's a bit "dirty" but seeing as you're in Excel...
它有点“脏”，但看到你在 Excel 中......
' Create a sheet with codename wsTemp...
For i = 0 To UBound(fltArr)
wsTemp.cells(i,1) = i
wsTemp.cells(i,2) = fltArr(i)
Next
with wsTemp
.range(.cells(1,1),.cells(i,2)).sort(wsTemp.cells(1,2),xlDescending)
end with
Result = wsTemp.cells(n,1)
Then you could also expand the sort to "sort by value then by index" if you wanted to control the "which of two equal 2nds should i choose" thing...
然后，如果您想控制“我应该选择两个相等的 2nds 中的哪一个”的话，您还可以将排序扩展为“按值排序，然后按索引排序”...
回答by Dan Donoghue
Perhaps this:
也许这个：
Public Sub RunLarge()
Dim fltArr() As Variant, X As Long
fltArr = Array(31, 15, 31, 52) 'Create the array
For X = 1 To 4 'Loop the number of large values you want to index
For i = LBound(fltArr) To UBound(fltArr) 'Loop the array
If fltArr(i) = Application.WorksheetFunction.Large(fltArr, 1) Then 'Find first instance of largest value
result = i
fltArr(i) = -9999 'Change the value in the array to -9999
Exit For
End If
Next
Debug.Print result
Next
End Sub
As it finds the first instance of the large number it replaces it with -9999 so on the next sweep it will pick the next instance of it.
当它找到大数的第一个实例时，它用 -9999 替换它，因此在下一次扫描时它将选择它的下一个实例。
回答by Ans
Here's code for finding the nth largest item in collection. All you need to do is to write a function that would return it's index.
这是用于查找集合中第 n 大项的代码。您需要做的就是编写一个函数来返回它的索引。
Sub testColl()
Dim tempColl As Collection
Set tempColl = New Collection
tempColl.Add 57
tempColl.Add 10
tempColl.Add 15
tempColl.Add 100
tempColl.Add 8
Debug.Print largestNumber(tempColl, 2) 'prints 57
End Sub
and the function itself, the easiest I could come up with.
和函数本身，我能想到的最简单的。
Function largestNumber(inputColl As Collection, indexMax As Long)
Dim element As Variant
Dim result As Double
result = 0
Dim i As Long
Dim previousMax As Double
For i = 1 To indexMax
For Each element In inputColl
If i > 1 And element > result And element < previousMax Then
result = element
ElseIf i = 1 And element > result Then
result = element
End If
Next
previousMax = result
result = 0
Next
largestNumber = previousMax
End Function
