Как найти дубликаты записей в 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 немедленно выдает ошибку и рекомендации по её исправлению, которые я ввел раньше.

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

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

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

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

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

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

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

 

Также вас может заинтересовать:

 
Сайт создан: ООО "Эйфос". Информационные технологии

Наверх