Как найти дубликаты записей в MS Excel

Настоящая проблема для больших MS Excel таблиц — повторяющиеся данные или дублирующиеся строки. В обычных условиях, поиск «дублей» это сизифов труд. Просмотреть вручную даже несколько десятков строк — уже сложная задача, а если счет идет на тысячи?

К счастью, у MS Excel есть довольно эффективный способ борьбы с дубликатами строк — проверка данных. Однако, чтобы она заработала, сперва нам придется как следует подготовиться.

Подготовка таблицы MS Excel к поиску дублей

Чтобы искать дублирующиеся записи, нам нужна какая-то точка опоры. По какому принципу будет осуществляться отбор дублей? Поэтому первым делом, нужно определить «эталонный столбец» — именно по нему и будет осуществляться отбор дубликатов. Данные размещенные в нем должны быть уникальны и исключать возможность ошибки ввода.

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

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

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

Настраиваем проверку данных в MS Excel

Продолжаем наводить порядок. Выделяем все данные нашего эталонного столбца (конечно с запасом — мы ведь будем пополнять его!), чтобы ускорить этот процесс — выделите первую ячейку столбца, зажмите клавишу SHIFT и промотайте лист вниз до конца, а затем снова щелкните мышью, но уже в последнюю ячейку. Столбец выделен.

Выделяем эталонный столбец таблицы и запускаем проверку данных

Перейдите на вкладку «Данные«, и в группе «Работа с данными«, щелкните на инструмент «Проверка данных«.

В открывшемся окне, на вкладке «Параметры«, в выпадающем списке «Тип данных» выберите «Другой«, а в появившемся ниже поле «Формула«, введите:

=СЧЁТЕСЛИ($A:$A;A2)=1

или

=COUNTIF($A:$A,A2)=1

Естественно, в зависимости от буквы-обозначения вашего эталонного столбца, меняются и буквенные обозначения в параметрах функции ($A:$A;A2). По русски это звучало бы:

применить функцию СЧЁТЕСЛИ, которая сравнивает каждую вводимую в столбец А строчку с каждой уже там существующей ($A:$A), начиная со второй ячейки (А2), при этом одинаковое значение для каждой комбинации может появится только 1 раз (=1).

Формула для проверки данных в MS Excel

Перейдите на вкладку «Сообщение об ошибке» и введите текст, который будет выводится в случае несоблюдения описанного выше условия (поля «Заголовок» и «Сообщение). Поле «Вид» оставьте по-умолчанию, в положении «Останов«.

Настраиваем сообщение об ошибке, при вводе неверных данных

Вот и готово. Давайте проверим?

Отлично — при вводе слова «Ананас» ничего не происходит, но стоит мне ввести дубликат — слово «Груши», как MS Excel немедленно выдает ошибку и рекомендации по её исправлению, которые я ввел раньше.

MS Excel обработал ошибку ввода данных. Отлично.

А как же исключения?

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

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

Как видите, форма вывода ошибки изменилась — она как и ранее предупреждает об ошибке, однако нажатие кнопки «Да», позволит вам обойти написанное нами правило, и все-таки добавить наши груши в список повторно.