如何删除同一列下单元格内容相同映射不同值下最大值除外的映射单元格的内容，如图所示，CD列变成GH列

Question

如题所述

Answer 1

选数据B2:C16

运行宏

Sub 保留最大值()

Dim myrs As Range, r As Long, i As Long, m1, m2, mm1, mm2, m

Set myrs = Selection

r = myrs.Rows.Count

For i = 1 To r - 1

    m1 = myrs.Cells(i, 1).Value

    m2 = myrs.Cells(i + 1, 1).Value

    mm1 = myrs.Cells(i, 2).Value

    mm2 = myrs.Cells(i + 1, 2).Value

    If m2 = m1 + 1 Then

       If mm1 = mm2 Then

          myrs.Cells(i, 2).Value = ""

          m = mm1

       ElseIf mm1 < mm2 Then

          myrs.Cells(i, 2).Value = ""

          m = mm1

       Else

          myrs.Cells(i + 1, 2).Value = ""

       End If

    ElseIf m2 = m1 Then

       If mm1 = mm2 Then

          myrs.Cells(i, 2).Value = ""

          m = mm1

       ElseIf mm1 < mm2 Then

          myrs.Cells(i, 2).Value = ""

          m = mm1

       Else

          myrs.Cells(i + 1, 2).Value = ""

       End If

    End If

Next i

End Sub

操作过程参考下面

Excel怎样批量自动删除行（空格）

Answer 2

通过分列等方法将D列文本型数值转换为数值

在H2中输入或复制粘贴下列公式

=IF(D2=MAXIFS(D$2:D$16,C$2:C$16,G2),D2,"")

下拉填充