【解決方法】値に基づいて数値を 1 位、2 位などに格付けする方法

[ad_1]

hello everyone. 
I am trying to rate a group of numbers in a datagrid containing two columns. I want the cells of the Coulmn B to have values as 1st, 2nd, 3rd etc, based on the values of Column A.

For example:

Col A            Col B
80                    2
305                   1
23                    5
43                    4
31                    3

私が試したこと:

All I know is how to assign the result to the cells of Column B after its found

Dim iRow As Integer
        Dim cellA As Integer
        Dim cellB As Integer

        For iRow = 0 To Me.DataGridView1.RowCount - 1

            Me.DataGridView1.Rows(iRow).Cells(1).Value = cellA
        Next
        iRow = irow + 1

解決策 1
解決策 2
解決策 3

解決策 1

次のようなものは機能し、タイを正しくランク付けします。

VB.NET

Dim cumulativeRank As Integer = 1
For Each group As IGrouping(Of Object, DataGridViewRow) In DataGridView1.Rows.Cast(Of DataGridViewRow)().GroupBy(Function (r) r.Cells(0).Value).OrderByDescending(Function (g) g.Key)
    Dim rank As Integer = cumulativeRank
    For Each row As DataGridViewRow In group
        row.Cells(1).Value = rank
        cumulativeRank += 1
    Next
Next

タイを気にしない場合は、グループ化は必要ありません。

VB.NET

Dim rank As Integer = 1
For Each row As DataGridViewRow In DataGridView1.Rows.Cast(Of DataGridViewRow)().OrderByDescending(Function (r) r.Cells(0).Value)
    row.Cells(1).Value = rank
    rank += 1
Next

解決策 2

に加えてリチャード・ディーミング[^]の解決策と彼の解決策に対する私のコメント…

使用することをお勧めします辞書[^] 重複した値を持つ行がある場合のオブジェクト ColA

VB.NET

Dim dgv As DataGridView = Me.DataGridView1
Dim oDict As Dictionary(Of Integer, Integer) = dgv.Rows().Cast(Of DataGridViewRow) _
	.OrderByDescending(Function(r) r.Cells("ColA").Value) _
	.GroupBy(Function(r) r.Cells("ColA").Value) _
	.Select(Function(grp, index) New KeyValuePair(Of Integer, Integer)(grp.Key, index+1)) _
	.ToDictionary(Function(kvp) kvp.Key, Function(kvp) kvp.Value)
	
For Each dgr As DataGridViewRow In dgv.Rows
	dgr.Cells(1).Value = oDict(dgr.Cells(0).Value)
Next

出力例:

ご覧のとおり、305 は 1. ランク、80 – 2. などを取得します。

幸運を！

[EDIT]

私の答えに対するリチャードのコメントに関しては、小さな改善です（まだDictionaryオブジェクトを使用しています）：

VB.NET

Dim oDict As Dictionary(Of Integer, Tuple(Of Integer, Integer)) = dgv.Rows().Cast(Of DataGridViewRow) _
	.GroupBy(Function(r) r.Cells("ColA").Value) _
	.OrderByDescending(Function(g) g.Key) _
	.Select(Function(grp, index) New KeyValuePair(Of Integer, Tuple(Of Integer, Integer))(grp.Key, Tuple.Create(index+1, grp.Count-1))) _
	.ToDictionary(Function(kvp) kvp.Key, Function(kvp) kvp.Value)

For Each dgr As DataGridViewRow In dgv.Rows
	Dim cumulative As Integer = oDict.TakeWhile(Function(x) x.Key>dgr.Cells(0).Value).Sum(Function(x) x.Value.Item2)
	dgr.Cells(1).Value = oDict(dgr.Cells(0).Value).Item1  + cumulative
Next

結果：

解決策 3

    Dim oDict As Dictionary(Of Integer, Tuple(Of Integer, Integer)) = DataGridView1.Rows().Cast(Of DataGridViewRow).GroupBy(Function(r) r.Cells(10).Value).OrderByDescending(Function(g) g.Key) _
.Select(Function(grp, index) New KeyValuePair(Of Integer, Tuple(Of Integer, Integer))(grp.Key, Tuple.Create(index + 1, grp.Count - 1))) _
.ToDictionary(Function(kvp) kvp.Key, Function(kvp) kvp.Value)

    For Each dgr As DataGridViewRow In DataGridView1.Rows
        Dim cumulative As Integer = oDict.TakeWhile(Function(x) x.Key > dgr.Cells(10).Value).Sum(Function(x) x.Value.Item2)
        dgr.Cells(13).Value = oDict(dgr.Cells(10).Value).Item1 + cumulative
    Next

81.
Marks	Grade     Remark            Position
68.2	4	      High Average	    4
63.2	4	      High Average	    5
56.2	5	      Average	        6
39.5	8	      Lower	            10
57.5	5	      Average	        7
68.2	4	      High Average	    4
68.5	4	      High Average	    1
50.3	6	      Low Average	    8
68.5	4	      High Average	    1
43.7	7	      Low	            9
68.3	4	      High Average	    3
35.5	8	      Lower	            11

エラーが発生しました。「オブジェクトは double である必要があります」というメッセージが表示され続けます。

<pre>81.
the results should be

Marks	Grade     Remark            Position
68.2	4	      High Average	    4
63.2	4	      High Average	    5
56.2	5	      Average	        6
39.5	8	      Lower	            10
57.5	5	      Average	        7
68.2	4	      High Average	    4
68.5	4	      High Average	    1
50.3	6	      Low Average	    8
68.5	4	      High Average	    1
43.7	7	      Low	            9
68.3	4	      High Average	    3
35.5	8	      Lower	            11