Как подсветить дубликаты в эксель
Перейти к содержимому

Как подсветить дубликаты в эксель

  • автор:

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

В Excel существует несколько способов фильтрации уникальных значений или удаления повторяющихся значений:

Фильтр

    Чтобы отфильтровать уникальные значения, щелкните Данные >Сортировка & Фильтр >Дополнительно.

Удаление дубликатов

Чтобы удалить повторяющиеся значения, щелкните Data > Data Tools>Remove Duplicates (Удалить дубликаты).

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

Фильтрация уникальных значений и удаление повторяющихся значений — это две схожие задачи, так как цель состоит в том, чтобы представить список уникальных значений. Однако существует критическое различие: при фильтрации уникальных значений повторяющиеся значения скрыты только временно. Однако удаление повторяющихся значений означает, что вы окончательно удаляете повторяющиеся значения.

Повторяющееся значение — это значение, в котором все значения по крайней мере в одной строке идентичны всем значениям в другой строке. Сравнение повторяющихся значений зависит от того, что отображается в ячейке, а не от базового значения, хранящегося в ячейке. Например, если у вас одно и то же значение даты в разных ячейках, одна из которых имеет формат «08.03.2006», а вторая — «8 марта 2006 г.», значения будут уникальными.

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

Фильтрация уникальных значений

  1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
  2. Щелкните Данные > Дополнительно (в группе Фильтр & сортировки ).

Важно: Вставьте текст оповещения здесь.

Фильтр

Чтобы отфильтровать диапазон ячеек или таблицы на месте, выполните следующие действия:

  • Щелкните Фильтровать список на месте.

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

Кнопка

  • Установите переключатель скопировать результат в другое место.
  • В поле Копировать введите ссылку на ячейку.
  • Кроме того, нажмите кнопку Свернуть диалоговое окно

Разверните

, чтобы временно скрыть всплывающее окно, выберите ячейку на листе и нажмите кнопку Развернуть

Уникальные значения из диапазона будут скопированы в новое расположение.

Удаление повторяющихся значений

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

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

Удаление двойников

  1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
  2. На вкладке Данные щелкните Удалить дубликаты (в группе Инструменты для работы с данными ).

  • В разделе Столбцы выберите один или несколько столбцов.
  • Чтобы быстро выбрать все столбцы, нажмите кнопку Выбрать все.
  • Чтобы быстро очистить все столбцы, щелкните Отменить выбор всех. Если диапазон ячеек или таблицы содержит много столбцов и требуется выбрать только несколько столбцов, может оказаться проще нажать кнопку Отменить выбор всех, а затем в разделе Столбцы выберите эти столбцы.

Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. Если в этих столбцах найден дубликат, будет удалена вся строка, включая другие столбцы в таблице или диапазоне.

Проблемы при удалении дубликатов из контурных или промежуточных данных

Невозможно удалить повторяющиеся значения из контурных данных, которые выделены или имеют промежуточные итоги. Чтобы удалить дубликаты, необходимо удалить как контур, так и промежуточные итоги. Дополнительные сведения см. в разделах Структура списка данных на листе и Удаление промежуточных итогов.

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

Примечание: Поля в области «Значения» отчета сводной таблицы нельзя условно форматировать по уникальным или повторяющимся значениям.

Быстрое форматирование

  1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
  2. На вкладке Главная в группе Стиль щелкните небольшую стрелку для параметра Условное форматирование, а затем выберите пункт Правила выделения ячеек и выберите Повторяющиеся значения.

Важно: Вставьте текст оповещения здесь.

Быстрое форматирование

Расширенное форматирование

  1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
  2. На вкладке Главная в группе Стили щелкните стрелку условного форматирования, а затем щелкните Управление правилами , чтобы открыть всплывающее окно Диспетчер правил условного форматирования .

Важно: Вставьте текст оповещения здесь.

Управление правилами

Кнопка

  • Выполните одно из следующих действий:
    • Чтобы добавить условный формат, щелкните Создать правило , чтобы открыть всплывающее окно Новое правило форматирования .
    • Чтобы изменить условный формат, сначала убедитесь, что в разделе Показать правила форматирования для списка выбран соответствующий лист или таблица. При необходимости выберите другой диапазон ячеек, нажав кнопку Свернуть

    Разверните

    во всплывающем окне Применяется к временно скрыть его. Выберите новый диапазон ячеек на листе, а затем снова разверните всплывающее окно

    В Excel в Интернете можно удалить повторяющиеся значения.

    Удаление повторяющихся значений

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

    Важно: Вы всегда можете нажать кнопку Отменить, чтобы вернуть данные после удаления дубликатов. При этом рекомендуется скопировать исходный диапазон ячеек или таблицу на другой лист или книгу перед удалением повторяющихся значений.

    1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
    2. На вкладке Данные щелкните Удалить дубликаты .
    3. В диалоговом окне Удаление дубликатов снимите флажки столбцов, в которых не нужно удалять повторяющиеся значения.

    Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. При обнаружении дубликата в столбцах Column1 и Column2 будет удалена вся строка, включая данные из Столбца 3.

    Примечание: Если вы хотите вернуть данные, просто нажмите кнопку Отменить (или нажмите клавиши CTRL+Z на клавиатуре).

    Дополнительные сведения

    Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

    Поиск и удаление повторений

    В некоторых случаях повторяющиеся данные могут быть полезны, но иногда они усложняют понимание данных. Используйте условное форматирование для поиска и выделения повторяющихся данных. Это позволит вам просматривать повторения и удалять их по мере необходимости.

      Выберите ячейки, которые нужно проверить на наличие повторений.

    Примечание: В Excel не поддерживается выделение повторяющихся значений в области «Значения» отчета сводной таблицы.

    Удаление дубликатов 1

    Выберите Главная >условное форматирование >выделение правил ячеек >повторяющихся значений.

    Удаление дубликатов 2

    В поле рядом со значениями выберите форматирование, которое нужно применить к повторяющимся значениям, а затем нажмите кнопку ОК.

    Удаление повторяющихся значений

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

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

    Совет: Перед попыткой удаления повторений удалите все структуры и промежуточные итоги из своих данных.

    Удаление дубликатов 3

    Выберите Данные >Удалить дубликаты, а затем в разделе Столбцы проверка или снимите флажок столбцов, в которых нужно удалить дубликаты.

    Выделенные повторяющиеся значения

    Например, на данном листе в столбце «Январь» содержатся сведения о ценах, которые нужно сохранить.

    Удаление дубликатов 5

    Поэтому флажок Январь в поле Удаление дубликатов нужно снять.

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

    Макрос для выделения дубликатов разными цветами

    Выделение разными цветами заливки повторяющихся значений (дубликатов)

    Как известно, в последних версиях Excel легко выделить дубликаты цветом, — для этого есть специальная опция в «условном форматировании».
    Достаточно выделить диапазон, задать цвет заливки, — и все повторяющиеся (или, наоборот, уникальные) значения будут выделены.

    Но иногда требуется, чтобы различные повторяющиеся значения были выделены РАЗНЫМИ ЦВЕТАМИ.
    В этом случае, без макросов не обойтись.

    Ниже приведён макрос, который как раз и решает эту задачу
    (достаточно выделить диапазон ячеек, запустить макрос, — и повторяющиеся непустые ячейки получат одинаковый цвет заливки)

    Sub ВыделитьДубликатыРазнымиЦветами() On Error Resume Next ' массив цветов, используемых для заливки ячеек-дубликатов Colors = Array(12900829, 15849925, 14408946, 14610923, 15986394, 14281213, 14277081, _ 9944516, 14994616, 12040422, 12379352, 15921906, 14336204, 15261367, 14281213) Dim coll As New Collection, dupes As New Collection, _ cols As New Collection, ra As Range, cell As Range, n& Err.Clear: Set ra = Intersect(Selection, ActiveSheet.UsedRange) If Err Then Exit Sub ra.Interior.ColorIndex = xlColorIndexNone: Application.ScreenUpdating = False For Each cell In ra.Cells ' запонимаем значение дубликатов в коллекции dupes Err.Clear: If Len(Trim(cell)) Then coll.Add CStr(cell.Value), CStr(cell.Value) If Err Then dupes.Add CStr(cell.Value), CStr(cell.Value) Next cell For i& = 1 To dupes.Count ' заполняем коллекцию cols цветами для разных дубликатов n = n Mod (UBound(Colors) + 1): cols.Add Colors(n), dupes(i): n = n + 1 Next For Each cell In ra.Cells ' окрашиваем ячейки, если для её значения назначен цвет cell.Interior.color = cols(CStr(cell.Value)) Next cell Application.ScreenUpdating = True End Sub
    • 84683 просмотра

    Повторяющиеся значения в Excel: находим и выделяем быстро

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

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

    Ключевых моментов несколько:

    • Какие конкретно повторяющиеся значения — повторы слов в ячейках, сами повторяющиеся ячейки или повторяющиеся строки?
    • Если ячейки, то:
      • Какие ячейки мы готовы считать дубликатами — все кроме первой или включая ее?
      • Считаем ли дублями строки, отличающиеся только пробелами до/после слов или лишними пробелами между словами?
      • Где мы будем искать дубли — в одном столбце, в двух столбцах или в нескольких?
      • А может, нам нужно найти неявные дубли?

      Сначала рассмотрим простые примеры.

      Найти и выделить цветом дубликаты в Excel

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

      Найти инструмент можно на вкладке программы “Главная”:

      Условное форматирование - выделение повторяющихся значений на панели Excel

      Процедура интуитивно понятна:

      1. Выделяем диапазон, в котором хотим найти дубликаты.
      2. Вызываем процедуру.
      3. Выбираем форматирование для отобранных ячеек (есть предустановленные форматы или же можно задать свой вариант).

      Выбор цвета для повторяющихся ячеек

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

      Неочевидный результат работы условного форматирования с дублями внутри диапазона

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

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

      Но есть и другие решения. О них дальше.

      Формула проверки наличия дублей в диапазонах

      Использование собственной формулы для проверки дубликатов в списке или диапазоне имеет ряд преимуществ, единственная задача — составление такой формулы. Я решил её за вас 🙂

      Внутри диапазона

      Чтобы проверить, есть ли в диапазоне повторяющиеся значения, можно использовать такую формулу массива на основе функций СЧЁТЕСЛИ и СУММПРОИЗВ:

      =СУММПРОИЗВ(СЧЁТЕСЛИ(диапазон;тот-же-диапазон)-1)>0 

      Копировать

      Так выглядит на практике применение формулы:

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

      А дело все в том, что формулу несложно видоизменить и улучшить.

      Например, можно улучшить эффективность формулы, добавив в нее функцию СЖПРОБЕЛЫ .Это позволит находить дубликаты, отличающиеся незаметными лишними пробелами:

      =СУММПРОИЗВ(--(СЖПРОБЕЛЫ(ячейка)=СЖПРОБЕЛЫ(диапазон)))>1 

      Копировать

      Эта формула слегка отличается, так как проверяет встречаемость в диапазоне значения одной ячейки.

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

      Обратите внимание на один момент в этой демонстрации: диапазон закреплен ($A$1:$B$4), а искомая ячейка (A1) нет. Именно это позволяет условному форматированию находить все дубликаты в диапазоне.

      !SEMTools, поиск дублей внутри диапазона

      Когда-то я потратил немало времени, пользуясь перечисленными выше методами поиска повторяющихся значений. Все они мне не нравились. Причина была одна: это попросту медленно. Поэтому я решил сделать отдельные процедуры для поиска и удаления дубликатов в Excel в своей надстройке.

      Давайте покажу, как они работают.

      Найти дубли ячеек в столбце, кроме первого

      Процедура позволяет выделить все вторые, третьи и т.д. повторяющиеся значения в столбце.

      Найти в столбце дубли ячеек, включая первый

      Зачастую нужно найти в столбце все повторяющиеся ячейки, включая первую, для того, чтобы далее отфильтровать их все.

      Найти дубли в столбце без учета лишних пробелов

      Если мы считаем дубликатами фразы, отличающиеся количеством пробелов между словами или после, наша задача — сначала избавиться от лишних пробелов, и далее произвести тот же поиск дубликатов.

      Для первой операции есть отдельный инструмент «Удалить лишние пробелы»:

      Найти повторяющиеся значения в Excel и решить сотни других задач поможет надстройка !SEMTools.

      Скачайте прямо сейчас и убедитесь сами!

      • Удалить дубли без смещения строк;
      • Удалить неявные дубли;
      • Найти повторяющиеся слова в Excel;
      • Удалить повторяющиеся слова внутри ячеек.
  • Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *