gpt-4-0314· openai
Suzhen/CodeChat
Переписать чтобы макрос делал ранг так же как сейчас, только по убывющей, к примеру там…
- 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:=xlYesEnd Sub
- 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 SubThis 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.