Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0314· openaiHugging Face Suzhen/CodeChat

Переписать чтобы макрос делал ранг так же как сейчас, только по убывющей, к примеру там…

  1. User

    Переписать чтобы макрос делал ранг так же как сейчас, только по убывющей, к примеру там где есть 62 должно быть 1, 61 должно быть -2 и так далле. Sub RankValues() Dim ws As Worksheet Dim lastRow As Long, i As Long Dim uniqueValues As Collection Dim value As Variant Dim rank As Long

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
    
       ' Delete first column A
    ws.Columns(1).Delete
    
    ' Insert new column A
    ws.Columns(1).Insert Shift:=xlToRight
    
    ' Find unique values in column E
    Set uniqueValues = New Collection
    On Error Resume Next
    For i = 2 To lastRow
        uniqueValues.Add ws.Cells(i, "E").value, CStr(ws.Cells(i, "E").value)
    Next i
    On Error GoTo 0
    
    ' Rank values in column R for each unique value in column E
    For Each value In uniqueValues
        rank = 1
        For i = 2 To lastRow
            If ws.Cells(i, "E").value = value Then
                ws.Cells(i, "A").value = rank
                rank = rank + 1
            End If
        Next i
    Next value
    
    ' Sort data by column E then by new column A (Rank)
     ws.Range("A1").CurrentRegion.Sort _
        key1:=ws.Range("E1"), order1:=xlAscending, _
        key2:=ws.Range("A1"), order2:=xlAscending, _
        Header:=xlYes
    

    End Sub

  2. Assistant
    Sub RankValuesDescending()
        Dim ws As Worksheet
        Dim lastRow As Long, i As Long
        Dim uniqueValues As Collection
        Dim value As Variant
        Dim rank As Long
        
        Set ws = ActiveSheet
        lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
        
        ' Delete first column A
        ws.Columns(1).Delete
        
        ' Insert new column A
        ws.Columns(1).Insert Shift:=xlToRight
        
        ' Find unique values in column E
        Set uniqueValues = New Collection
        On Error Resume Next
        For i = 2 To lastRow
            uniqueValues.Add ws.Cells(i, "E").Value, CStr(ws.Cells(i, "E").Value)
        Next i
        On Error GoTo 0
        
        ' Rank values in column A for each unique value in column E (descending)
        For Each value In uniqueValues
            rank = WorksheetFunction.Max(ws.Range("E2:E" & lastRow)) - value + 1
            For i = 2 To lastRow
                If ws.Cells(i, "E").Value = value Then
                    ws.Cells(i, "A").Value = rank
                End If
            Next i
        Next value
    
        ' Sort data by column E (descending) then by new column A (Rank)
        ws.Range("A1").CurrentRegion.Sort _
            Key1:=ws.Range("E1"), Order1:=xlDescending, _
            Key2:=ws.Range("A1"), Order2:=xlDescending, _
            Header:=xlYes
    End Sub
    

    This modified macro will rank the values in column E in descending order, so that 62 is ranked as 1, 61 is ranked as 2, etc. Additionally, the data is sorted by column E in descending order and then by the new ranking column A, also in descending order.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.