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

Как в Excel найти и выделить повторяющиеся значения? Представляем несколько способов выделить повторяющиеся ячейки или целые строки.

Как подсветить дубликаты, используя встроенный инструмент (с 1-м появлением)

Для начала, во всех версиях Excel есть заготовка для выделения дублирующих ячеек. Чтобы использовать это её в своих таблицах, выполните следующие действия:

  1. Выберите данные, которые вы хотите проверить. Это может быть колонка, строка или диапазон ячеек.
  2. На вкладке « Главная » выберите « Условное форматирование» > «Правила выделения ячеек» > «Повторяющиеся значения».

Откроется диалоговое окно с форматом «Светло-красная заливка» и «Темно-красный текст», выбранными по умолчанию. Чтобы применить формат по умолчанию, просто нажмите ОК.

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

И вот что мы получили в итоге:

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

При использовании этого инструмента имейте в виду следующие две вещи:

  • Работает только для отдельных клеток. Чтобы отметить дублирующиеся строки, вам необходимо создать свои собственные правила.
  • Он закрашивает дубликаты ячеек, включая их первые появления. Чтобы отметить все из них, кроме первых экземпляров , создайте правило условного форматирования на основе формулы из этого руководства по нахождению дубликатов.

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

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

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

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

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

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

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

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

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

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

  3. Нажмите кнопку ОК.

Нужна дополнительная помощь?

Задача

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

Простейшие варианты условного форматирования

Для того, чтобы произвести форматирование определенной области ячеек, нужно выделить эту область (чаще всего столбец), и находясь во вкладке «Главная», кликнуть по кнопке «Условное форматирование», которая расположена на ленте в блоке инструментов «Стили».

После этого, открывается меню условного форматирования. Тут представляется три основных вида форматирования:

  • Гистограммы;
  • Цифровые шкалы;
  • Значки.

Типы условного форматирования в Microsoft Excel

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

Выбор гистограммы в Microsoft Excel

Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.

Гистограмма применена в Microsoft Excel

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

Использование цветовой шкалы в Microsoft Excel

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

Значки при условном форматировании в Microsoft Excel

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

Стрелки при условном форматировании в Microsoft Excel

Как выделить повторы без 1-го вхождения

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

  1. Выберите «Условное форматирование» > « Новое правило»> «Использовать формулу», чтобы определить ячейки для форматирования .
  2. В поле « Форматировать значения», где эта формула возвращает ИСТИНА , введите:

=СЧЕТЕСЛИ($B$2:$B2;$B2)>1

Где B2 – самая верхняя ячейка выбранного диапазона.

  1. Нажмите кнопку «Формат» выберите нужный цвет заливки и/или шрифта.
  2. Наконец, нажмите кнопку ОК, чтобы сохранить и применить созданное.

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

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

Метод 3: использование фильтра

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

  1. Как обычно, выделяем все ячейки таблицы.
  2. Во вкладке “Данные” в разделе инструментов “Сортировка и фильтр” ищем кнопку “Фильтр” (иконка напоминает воронку) и кликаем на нее.Включение фильтра в таблице Эксель
  3. После этого в строке с названиями столбцов таблицы появятся значки перевернутых треугольников (это значит, что фильтр включен). Чтобы перейти к расширенным настройкам, жмем кнопку “Дополнительно“, расположенную справа от кнопки “Фильтр“.Переход в расширенные настройки фильтра в Excel
  4. В появившемся окне с расширенными настройками:
    • как и в предыдущем способе, проверяем адрес диапазон ячеек таблицы;
    • отмечаем галочкой пункт “Только уникальные записи“;
    • жмем ОК.Расширенный фильтр в Эксель
  5. После этого все задвоенные данные перестанут отображаться в таблицей. Чтобы вернуться в стандартный режим, достаточно снова нажать на кнопку “Фильтр” во вкладке “Данные”.Включение и выключение фильтра в таблице Эксель

Подсветка повторяющихся значений по нескольким столбцам

Принцип подхода к решению данной проблемы – создание дополнительного столбца, который будет содержать значения нескольких столбцов, и уже к созданному столбцу применять способ описанный выше. Для заполнения такого столбца используйте формулу =СЦЕПИТЬ(Ячейка1столбца; Ячейка2столбца). У вас должно получиться что-то подобное:

Подсветка повторяющихся значений по нескольким столбцам

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

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

Вкладка «Вставка».

Пункт «Сводная таблица».

Как выделить повторяющиеся и одинаковые значения в Excel

В диалоговом окне выбрать размещение сводной таблицы на новом листе.

Как выделить повторяющиеся и одинаковые значения в Excel

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

Как выделить повторяющиеся и одинаковые значения в Excel

Получаемупорядоченный список уникальных строк.

Как выделить повторяющиеся ячейки в Excel

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

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...