Поиск и удаление дубликатов в Excel: 5 методов

Большие таблицы Excel могут содержать повторяющиеся данные, что часто увеличивает объем информации и может вызывать ошибки в результате обработки данных с использованием формул и других инструментов. Это особенно важно, например, при работе с денежными и другими финансовыми данными.

В этой статье мы рассмотрим методы поиска и удаления повторяющихся (повторяющихся) данных, особенно строк в Excel.

Метод 1: удаление дублирующихся строк вручную

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

1. Полностью выделяет все ячейки таблицы с данными, например, зажав левую кнопку мыши.
2. На вкладке «Данные» в разделе инструментов «Работа с данными» найдите кнопку «Удалить дубликаты» и нажмите на нее.
3. Переходим к настройке параметров удаления дубликатов:
   • Если обрабатываемая таблица содержит заголовок, мы проверяем пункт «Мои данные содержат заголовки» — он должен быть выбран.
   • Ниже, в главном окне, перечислены названия столбцов, которые будут использоваться для поиска дубликатов. Система рассматривает случай, когда строки повторяют значения всех столбцов, выбранных в настройке, как совпадение. Удаление некоторых столбцов из сравнения увеличивает вероятность увеличения количества похожих строк.
   • Все внимательно проверяем и нажимаем ОК.
     
4. После этого программа Excel автоматически найдет и удалит все повторяющиеся строки.
5. По окончании процедуры на экране появится соответствующее сообщение с информацией о количестве найденных и удаленных дубликатов, а также о количестве оставшихся уникальных строк. Чтобы закрыть окно и выйти из этой функции, нажмите кнопку ОК.

Метод 2: удаление повторений при помощи “умной таблицы”

Другой способ удалить повторяющиеся строки — использовать интеллектуальную таблицу. Давайте рассмотрим алгоритм пошагово.

1. Во-первых, нам нужно выделить всю таблицу, как в первом шаге предыдущего раздела.
2. На вкладке «Главная» находим кнопку «Форматировать как таблицу» (раздел инструментов «Стили»). Щелкните стрелку в правом нижнем углу имени кнопки и выберите желаемую цветовую схему таблицы.
3. После выбора стиля откроется окно настроек, в котором вы указываете интервал создания умной таблицы. Поскольку ячейки были выбраны заранее, вам просто нужно убедиться, что в окне указаны правильные данные. Если нет, внесем исправления, убедитесь, что выбрана «Таблица с заголовками», и нажмите ОК. На этом процесс создания умной таблицы завершен.
   
4. Далее переходим к основной задаче — поиску сдвоенных строк в таблице. Из-за этого:
   • поместите курсор в произвольную ячейку таблицы;
   • перейти во вкладку «Конструктор» (если после создания «умной таблицы» переход не был осуществлен автоматически);
   • в разделе «Инструменты» нажмите кнопку «Удалить дубликаты“.
5. Следующие шаги точно такие же, как шаги по удалению повторяющихся строк, описанные в предыдущем методе.

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

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

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

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

Метод 4: условное форматирование

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

1. Выделяем все ячейки нашей таблицы.
2. На вкладке «Главная» нажмите кнопку «Условное форматирование», расположенную в разделе инструментов «Стили“.
3. Откроется список, в котором выбираем группу «Правила выбора ячеек» и внутри нее пункт «Повторяющиеся значения“.
4. Окно настроек форматирования оставьте без изменений. Единственный параметр, который вы можете изменить в зависимости от ваших цветовых предпочтений, — это цветовая схема, используемая для заливки выбранных линий. Когда будете готовы, нажмите кнопку ОК.
5. Теперь все повторяющиеся ячейки в таблице «подсвечиваются» и с ними можно работать: редактировать содержимое или удалять целые строки любым удобным способом.

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

Метод 5: формула для удаления повторяющихся строк

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

Общая формула для поиска пересечений в определенном столбце выглядит так:

= ЕСЛИОШИБКА (ИНДЕКС (адрес_столбца; ПОИСК (0, СЧЁТЕСЛИ (адрес_дубликата_столбца): дубликат_адрес_колонки) (абсолютный); адрес_столбца;) + SE (СЧЁТЕСЛИ (адрес_столбца); 0; 0;); СЧЁТЕСЛИ (адрес_столбца); 0;); column_address));»»)

Посмотрим, как с ним работать на примере нашей таблицы:

1. Добавьте новый столбец в конец специально разработанной таблицы для отображения повторяющихся (повторяющихся) значений).
2. В верхней ячейке нового столбца (не считая заголовка) введите формулу, которая для этого конкретного примера будет выглядеть, как показано ниже, и нажмите Enter:
   = ЕСЛИ ОШИБКА (ИНДЕКС (A2: A90; ПОИСК (0; СЧЁТЕСЛИ (E1: $ E $ 1; A2: A90) + ЕСЛИ (СЧЁТЕСЛИ (A2: A90; A2: A90)> 1, 0, 1); 0));»»).
3. Выбираем новый столбец для данных, удвоенных до конца, не касаясь заголовка. Далее действуем строго по инструкции:
   • ставим курсор в конец строки формулы (нужно убедиться, что это действительно конец строки, так как в некоторых случаях длинная формула не помещается в строку);
   • нажмите на клавиатуре сервисную клавишу F2;
   • затем нажмите комбинацию клавиш Ctrl + Shift + Enter.
4. Эти действия позволяют правильно заполнить формулу, содержащую ссылки на массивы, все ячейки в столбце. Проверяем результат.

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

Заключение

Excel предлагает несколько инструментов для поиска и удаления строк или ячеек с одинаковыми данными. Каждый из описанных методов специфичен и имеет свои ограничения. Мы, вероятно, назвали бы использование «умной таблицы» и функции «Удалить дубликаты» универсальным вариантом. В целом, для выполнения задачи необходимо руководствоваться как характеристиками структуры таблицы, так и преследуемыми целями и видением конечного результата.