vba不重复且不并列排名如何实现？

软件 2022-07-05

vba不重复且不并列排名如何实现？

获取B:R数据的排名，填入T:AJ，用VBA实现的代码如下：

补充一、本问题可以用公式解决，T3公式为：

=RANK(B3, $B3:$AJ3)

右拉、下拉即可。

补充二、上图VBA代码的文本如下：

Option Explicit

Sub 宏1()

Dim arr1, arr2, i&, j&, k&

arr1 = Range("b2:r20") '请根据需要修改20为结束行

ReDim arr2(1 To UBound(arr1), 1 To UBound(arr1, 2))

For i = 1 To UBound(arr1)

For j = 1 To UBound(arr1, 2)

'通过循环，统计第i行数据中大于arr1(i,j)的数据个数到arr2(i,j)

For k = 1 To UBound(arr1, 2)

If arr1(i, k) > arr1(i, j) Then arr2(i, j) = arr2(i, j) + 1

Next k

arr2(i, j) = arr2(i, j) + 1 '次数+1即为排名

Next j

Next i

Range("t2").Resize(UBound(arr2), UBound(arr2, 2)) = arr2

End Sub

A列是一组数据，对A列的每一个数据进行数字的排序，放到B列中，不允许有重复，用VBA，怎么实现？

做好了。。。 Option Explicit Public resCol As Collection Sub a() Set resCol = New Collection Dim l As Integer, irow As Long Dim tmp As String, t() As String, tt() As String Dim i As Integer, k As Integer, kk As Integer irow = ActiveSheet.Range("A65536").End(xlUp).Row kk = 1 For i = 1 To irow With ActiveSh