Как заменить повторяющиеся значения в excel

Помогла ли Вам статья?

Очень часто при работе c таблицами Excel возникают ситуации, в которых необходимо сравнить несколько списков, найти в них повторяющиеся значения и что-то с ними сделать.  Оптимальный способ поиска и обработки дубликатов должен быть выбран в зависимости от типа исходных данных и желаемого результата. Цель данной статьи — разобрать все возможные варианты обработки дубликатов в Excel в одной статье для того, чтобы читатель мог выбрать оптимальный вариант для любой ситуации.

Оглавление:

• Выделение
  • Поиск и выделение повторяющихся значений ячеек в одном списке — условное форматирование
  • Поиск и выделение повторяющихся значений ячеек в нескольких списках — условное форматирование
  • Поиск и выделение повторяющихся значений ячеек — макрос Excel-VBA
  • Поиск и выделение повторяющегося текста внутри ячеек — макрос Excel VBA
• Замена
  • Замена дублирующихся значений ячеек с помощью макроса Excel-VBA
  • Подстановка в другие таблицы
  • Функция ВПР (VLOOKUP)
  • Комбинация функций ИНДЕКС + ПОИСКПОЗ (INDEX+MATCH)+СЧЁТ()+ЕСЛИ()
• Подсчёт
  • Посчитать количество повторений в одном списке
  • Сравнение двух списков используя формулу подсчёта повторений
  • Подсчёт количества повторений значений в строках с помощью макросов Excel-VBA
  • Функция СЧЁТЕСЛИ (COUNTIF)
• Поиск
  • Поиск повторений значений в ячейках с помощью макроса Excel-VBA
  • Скрытие
  • Сортировка и фильтр
  • Скрытие строк с помощью макроса Excel-VBA
• Удаление
  • Данные -> удалить дубликаты
  • Умные таблицы. Форматировать как таблицу -> удалить дубликаты

Функции в каждом разделе описаны в порядке возрастания их сложности и трудоемкости использования.

Выделение

Поиск и выделение повторяющихся значений ячеек в одном списке — условное форматирование

1. Выделить все значения в списке 

2. Вкладка «Главная» -> Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения

3. Выбрать необходимый формат (в данном случае выбран красный шрифт на светло-красном фоне)

Результат:

Если применить данное условное форматирование ко всему столбцу A, то все новые дубликаты, добавленные после строки 10 также будут отформатированы по заданному правилу.

Поиск и выделение повторяющихся значений ячеек в нескольких списках — условное форматирование

Сначала необходимо выделить столбцы (диапазоны ячеек) с дубликатами. Далее необходимо проделать действия, описанные в предидушем разделе начиная с шага 2.

Недостаток данного способа выделения дубликатов — визуально не определить, продублированы ли значения внутри каждого из списков, или между списками. В данном примере «малина» дублируется внутри списка 1, а «банан» и «груша» выделены потому что они продублированы между списками.

Для того, чтобы дублирование внутри списков воспринималось отдельно от дублирования между списков можно повторно использовать другое условное форматирование для каждого из столбцов по отдельности.

1. Выделяем первый столбец:

2. Вкладка «Главная» -> Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения

3. Пользовательский формат

4. Выбираем, например, одинарное подчеркивание, жирный шрифт и фиолетовый цвет.

5. Повторяем операцию с шага 2 для столбца B и получаем:

Поиск и выделение повторяющихся значений ячеек — макрос Excel-VBA

Скачать пример в Excel

Sub search_highlight_duplicates()

Dim Arr(16, 1) As String 'сравниваем значения как текст
'массив двухмерный
'16 на 2
'элементы 1-16,0 содержат значения ячеек
'элементы 1-16,1 - является ли соответсвующее значение дубликатом


For i = 1 To 16
Arr(i, 0) = ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value 'записываем в массив
Next i


For k = 1 To UBound(Arr, 1)
CurrentItem = Arr(k, 0) 'достаём по одному элементы из массива 1-16,0
    For i = 1 To UBound(Arr, 1)
    If CurrentItem = Arr(i, 0) And i <> k Then Arr(i, 1) = "COPY"
    'сравниваем с другими элементами массива (за исключением себя самого)
    'для копий записываем в 1-16,0 "COPY"
    Next i
Next k


For i = 1 To UBound(Arr, 1)
    ThisWorkbook.Sheets("Sheet1").Cells(i, 2).Value = Arr(i, 1)
    'запишем результат обратно в таблицу в колонку 2
    'либо здесь можно прописать особенное форматирование для каждого элемента исходного массива
Next i

End Sub

То же самое, но через форматирование ячеек:

Скачать пример в Excel

Sub search_highlight_duplicates()

Dim Arr(16, 1) As String

For i = 1 To 16
Arr(i, 0) = ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value
Next i

For k = 1 To UBound(Arr, 1)
CurrentItem = Arr(k, 0)
    For i = 1 To UBound(Arr, 1)
    If CurrentItem = Arr(i, 0) And i <> k Then Arr(i, 1) = "COPY"
    Next i
Next k


For i = 1 To UBound(Arr, 1)
If Arr(i, 1) = "COPY" Then
    With ThisWorkbook.Sheets("Sheet1").Cells(i, 1)
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        With .Font
            .Color = -16776961
            .TintAndShade = 0
        End With
    End With
End If
    
Next i
End Sub

Поиск и выделение повторяющегося текста внутри ячеек — макрос Excel VBA

 Возможно существуют и другие более эффективные способы выделения копий слов внутри ячеек, но здесь будет описан только простейший способ, на который натолкнёт запись действий макрорекордером. Если мы напишем длинный текст в ячейке, включим запись макроса и отформатируем часть текста в ячейке, то получим примерно следующее: 

Таким образом, для выделения отдельных слов в ячейке нам нужно предварительно найти, где расположен нужный нам набор символов, а также его длину для каждого повторения.

Замена

Замена дублирующихся значений ячеек с помощью макроса Excel-VBA

Подстановка в другие таблицы

Функция ВПР (VLOOKUP)

Об использовании функции ВПР пошагово.

Комбинация функций ИНДЕКС + ПОИСКПОЗ (INDEX+MATCH)+СЧЁТ()+ЕСЛИ()

Если таблица, из которой мы хотим доставать значения для подстановки содержит дубликаты, то использование функции ВПР может не дать нужного результата, так как ВПР использует первое попавшееся совпадение и результат будет зависит от сортировки.

Подсчёт

Посчитать количество повторений в одном списке

Сравнение двух списков используя формулу подсчёта повторений

Подсчёт количества повторений значений в строках с помощью макросов Excel-VBA

Функция СЧЁТЕСЛИ (COUNTIF)

Поиск

Поиск повторений значений в ячейках с помощью макроса Excel-VBA

Скрытие

Сортировка и фильтр

Скрытие строк с помощью макроса Excel-VBA

Удаление

Данные -> удалить дубликаты

Умные таблицы. Форматировать как таблицу -> удалить дубликаты

Обычно, когда вы применяете команду «Удалить дубликаты» в Excel, она удаляет все повторяющиеся строки. Но иногда вы хотите, чтобы пустые ячейки заменили повторяющиеся значения, в этой ситуации команда «Удалить дубликаты» не будет работать. Эта статья поможет вам удалить дубликаты и заменить их пустыми ячейками в Excel.

Удалите дубликаты и замените пустыми ячейками с формулами
Удалите дубликаты и замените пустыми ячейками на Kutools for Excel

Удалите дубликаты и замените пустыми ячейками с формулами

Если в диапазоне есть повторяющиеся данные, следующие две формулы помогут вам заменить дубликаты пробелами в диапазоне. Пожалуйста, сделайте следующее:

1, Выберите пустую ячейку для вывода результата, например, ячейку D2, введите в нее формулу ниже и нажмите Enter . 

=IF(A2=»»,»»,IF(COUNTIF($A2:A15,A2)=1,A2,»»))

2. Выберите ячейку результата, а затем перетащите маркер заполнения через диапазон, в котором вы хотите применить эту формулу.
Затем вы можете увидеть, что все дубликаты (кроме первого) заменены пробелами. Смотрите скриншоты:

Функции: Если вам нужно заменить все повторяющиеся значения (включая первое) пустыми ячейками и сохранить только уникальные значения в диапазоне, следующая формула может вам помочь, и вы получите следующие результаты:

=IF(A2=»», «», IF(COUNTIF(A$2:A$15,A2)>1,»»,A2))

Примечание: Вы можете изменить ссылки на ячейки в двух приведенных выше формулах в зависимости от ваших потребностей.

Удалите дубликаты и замените пустыми ячейками на Kutools for Excel

Наблюдения и советы этой статьи мы подготовили на основании опыта команды Выберите повторяющиеся и уникальные ячейки полезности Kutools for Excel может помочь вам быстро выбрать все повторяющиеся ячейки в диапазоне, после чего вы можете только нажать клавишу Delete на клавиатуре, чтобы удалить их. 

1. Выберите диапазон, в котором вы хотите применить эту утилиту.

2. Нажмите Кутулс > Выберите > Выберите повторяющиеся и уникальные ячейки.

3. в Выберите повторяющиеся и уникальные ячейки диалоговое окно, выберите Дубликаты (кроме 1-го) or Все дубликаты (включая 1-й) как вам нужно, а затем щелкните OK кнопку.

4. Если вы выбрали Дубликаты (кроме 1-го) вариант, будут выбраны все повторяющиеся ячейки, кроме первой повторяющейся, и вы можете нажать кнопку Удалить ключ, чтобы удалить их. Смотрите скриншоты:

Если вы выбрали Все дубликаты (включая 1-й) вариант, все повторяющиеся ячейки (включая первый дубликат) будут выбраны сразу, затем вы можете нажать кнопку Удалить ключ, чтобы удалить их. Смотрите скриншоты:

  Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Статьи по теме:

• Найдите уникальные значения между двумя столбцами
• Найдите повторяющиеся значения в двух столбцах
• Отфильтровать уникальные записи из выбранного столбца