|
▼KAI さん:
こんばんは。
Sheet1のデータは、全て数値であるという条件です。
>以下のシート(Sheet1)の場合、大きい順に(Sheet2)のA列にB4.A3.D2…と書くことはできるのでしょうか??
>また、できるのならば、同得点の場合の処理もどうにかお願いします。
>
>
>(Sheet1) (Sheet2)
> A B C D E A B C
>1 100 123 321 566 129 1 B4
>2 578 654 156 846 345 ⇒ 2 A3
>3 875 659 800 124 651 3 D2
>4 758 986 425 154 445 4 C3
標準モジュールに
'====================================================
Sub test()
Dim ans As Variant
Dim rng As Range
Dim rr As Long
Dim r As Long, c As Long
Set rng = Worksheets("sheet1").Range("a1:e4")
ans = Evaluate("if(" & rng.Address(, , , True) & "<>""""," & _
"rank(" & rng.Address(, , , True) & "," & rng.Address(, , , True) & "))")
With Worksheets("sheet2")
.Range("a1:a" & rng.Count).Value = ""
For r = LBound(ans, 1) To UBound(ans, 1)
For c = LBound(ans, 2) To UBound(ans, 2)
rr = ans(r, c)
Do Until .Cells(rr, 1).Value = ""
rr = rr + 1
Loop
.Cells(rr, 1).Value = .Cells(r, c).Address
Next
Next
End With
End Sub
試してみて下さい
|
|