Как настроить автоматическое выделение дубликатов в Excel

Эта статья покажет Вам, как избежать дубликатов в таблицах, используя встроенные инструменты Excel. Данное решение актуально для Excel 2013, 2010 и 2007. Его настройка займёт у Вас всего несколько минут.

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

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

Хотите ли Вы, чтобы данные автоматически проверялись на наличие дубликатов прямо в момент их ввода? Как только Вы введете какие-либо повторяющиеся значения и нажмете клавишу Enter, редактируемое поле сразу же выделится (например, поменяет фоновый цвет или цвет текста), таким образом извещая Вас, что в данном столбце уже есть совершенно идентичное значение (как это показано на скриншоте ниже). Продолжайте читать эту статью, чтобы узнать, как проделать это легко и быстро.

Как динамически выделять дубликаты?

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

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

Примечание: Некоторые столбцы таблицы (например, пол) наоборот должны содержать только повторения (мужской, женский, в некоторых странах есть еще 3-й пол), и этот набор значений определен. Вы можете вводить такие значения, выбирая их из выпадающего списка, что сэкономит время и поможет избежать опечаток (например, «женски» или «жеский» вместо «женский»). Вы хотите узнать, как это сделать? – читайте статьи Выпадающий список в Excel и Как создать связанные выпадающие списки в Excel.

Подсказка: Если Вам нужно автоматически сравнить введенные данные со столбцом из другой таблицы (например, телефонные номера черного списка), прочтите статью Как сравнить два столбца в Excel и выделить дубликаты.

1. Подготовьте таблицу

Если Ваш список – это обычный диапазон, то Вы не увидите вкладку Table Tools (Работа с таблицами) после нажатия на любую запись.

Во-первых, нужно создать полноценную таблицу: выберите любую ячейку с данными и нажмите Ctrl+T. Проверьте, что Excel корректно распознает заголовки и границы таблицы, когда увидите диалоговое окно Create Table (Создание таблицы). Убедитесь, что установлен флажок напротив опции My table has headers (Таблица с заголовками) и нажмите ОК.

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

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

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

3. Примените к столбцу условное форматирование

Откройте вкладку Home (Главная) и кликните по иконке Conditional formatting (Условное форматирование).

В появившемся меню выберите Highlight Cells Rules (Правила выделения ячеек) > Duplicates Values (Повторяющиеся значения).

4. Установите формат для ячеек с повторами

Выберите один из вариантов форматирования в списке Values with (Значения с) диалогового окна Duplicate Values (Повторяющиеся значения) и нажмите ОК, чтобы применить форматирование.

Кроме этого, в раскрывающемся списке Values with (Значения с) Вы можете выбрать пункт Custom format (Пользовательский формат) и настроить по своему желанию цвет и размер шрифта, заливку или границы. Например, жирный шрифт и ярко-желтая заливка.

5. Вот и всё!

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