☰

Как удалить (или выделить) дубликаты строк в Excel, ТОЛЬКО ЕСЛИ их 4 или более экземпляров?

tag-icon • tag-icon microsoft-excel
Как удалить (или выделить) дубликаты строк в Excel, ТОЛЬКО ЕСЛИ их 4 или более экземпляров?

У меня есть документ Excel, в котором более 300 строк данных. Столбец A отсортирован по именам сотрудников, и мне нужно выделить только тех людей, которые имеютменьше 4вхождения в пределах листа.

Есть ли способ удалить все имена с 4 или более? Или выделить все имена с 4 или более?

Спасибо!

решение1

Допустим, мы начнем с:

введите описание изображения здесь

и запускаем этот короткий макрос:

Sub RowKiller101()
  Dim rKill As Range, r As Range, wf As WorksheetFunction
  Dim rBig As Range
  Set rBig = Intersect(Range("A:A"), ActiveSheet.UsedRange)
  Set rKill = Nothing
  Set wf = Application.WorksheetFunction

  For Each r In rBig
    If wf.CountIf(rBig, r.Value) > 3 Then
      If rKill Is Nothing Then
        Set rKill = r
      Else
        Set rKill = Union(rKill, r)
      End If
    End If
  Next r

  If rKill Is Nothing Then Exit Sub
rKill.EntireRow.Delete
End Sub

Мы получим:

введите описание изображения здесь

решение2

Для решения без использования VBA:

Введите формулу, как =countif($A:$A,$A1)в столбце B, и ваша таблица будет выглядеть примерно так:

    A    B  
1  Joe   3  
2  Joe   3  
3  Joe   3  
4  Amy   5  
5  Amy   5  
6  Amy   5  
7  Amy   5
8  Amy   5

Затем выберите A1, перейдите в Условное форматирование (пользовательское правило) и введите формулу =B1>=4. Затем примените правило к нужному вам объему в столбце A. Отсутствие привязки $означает, что правило будет перемещаться вместе с примененной ячейкой.

решение3

Студент Гэри: Решение VBA для удаления всех элементов, кроме <4.
Селвит: Формула решения для фильтрации только элементов <4.
Этот ответ: Решение условного форматирования для выделения только элементов <4.

(Вы также можете сослаться наСтатья о поддержке офисаоб использовании формул в условном форматировании, которое, как выясняется, использует COUNTIFв качестве примера.)

  1. Выберите диапазон данных (в примере я выбрал все столбцы)A:A
  2. На Homeленте нажмите Conditional Formattingи затемNew Rule...
  3. ВыбиратьUse a formula to determine which cells to format
  4. Введите формулу типа =COUNTIF($A:$A,$A1)<4
    A. Измените $A:$Aна любой диапазон, в котором вы ищете дубликаты. Например, это может быть $G$12:$G$48. $Важно, потому что это делает егоабсолютная ссылкакоторый не меняется от ячейки к ячейке.
    B. Измените $A1напервыйЯчейка в выбранном диапазоне. Обратите внимание, что здесь только одна $, потому что мы хотим, чтобы столбец был абсолютным. Мы хотим, чтобы номер строки ( 1в данном случае) был относительным к текущей ячейке.
    C. Результат таков, что условное форматирование для A1будет использовать формулу =COUNTIF($A:$A,$A1)<4, но для A2будет использовать формулу =COUNTIF($A:$A,$A2)<4. Вот что делает для вас управление абсолютными и относительными ссылками ( $или нет $).
  5. Нажмите Format...кнопку и выберите любое форматирование, которое вам нравится. В этом случае я выбрал выделение ячеек желтым фоном.
  6. Нажмите OK, чтобы закрыть окно условного форматирования и увидеть результаты.

Скриншот для шагов 1–5

Обратите внимание, что из-за того, как я это сделал, все пустые строки также выделены. Если это не работает для вас, есть несколько способов исправить это. Я рекомендую вариант 1, потому что это простой способ следить за своим списком по мере его роста.

  1. Измените формулу, которую вы используете в шаге 4, на=AND($A1<>"",COUNTIF($A:$A,$A1)<4)
  2. Вместо того чтобы выбирать все столбцы A:A, как я сделал на шаге 1, выберите только диапазон данных.
  3. После создания условного форматирования вернитесь в Conditional Formatting Rules Manager(Главная лента > Условное форматирование > Управление правилами...) и измените поле Applies toс =$A:$Aна что-то конкретное, например =$A$1:$A$19. Это тот же результат, что и при использовании варианта 2, но он позволяет вам изменить его постфактум.

Связанный контент