Большие электронные таблицы Excel могут содержать повторяющиеся данные, что часто увеличивает объем информации и может привести к ошибкам в результате обработки данных с помощью формул и других инструментов. Это особенно важно, например, при работе с денежными и другими финансовыми данными.
В этой статье мы рассмотрим методы поиска и удаления дублирующихся данных (дубликатов), в частности строк в Excel.
Метод 1: удаление дублирующихся строк вручную
См. также "Как удалить верхние и нижние колонтитулы в Excel".
Первый способ является самым простым и предполагает удаление дубликатов строк с помощью специального инструмента на ленте вкладки "Данные".
- Все ячейки данных в таблице выделяются полностью, например, если удерживать левую кнопку мыши.
- Найдите и нажмите кнопку "Удалить дубликаты" на вкладке "Данные" в разделе инструментов "Работа с данными".
- Перейдите к настройкам удаления дубликатов:
- Если обрабатываемая таблица содержит заголовки, проверьте опцию "Мои данные содержат заголовки" - она должна быть отмечена.
- Ниже, в главном окне, указаны названия столбцов, в которых будет производиться поиск дубликатов. Система считает его совпадением, если строки содержат дубликаты всех столбцов, выбранных в конфигурации. Удаление некоторых столбцов из сравнения увеличивает вероятность увеличения количества схожих строк.
- Внимательно проверьте все и нажмите OK.
- Затем Excel автоматически найдет и удалит все дублирующиеся строки.
- По завершении процедуры на экране появится соответствующее сообщение с указанием количества найденных и удаленных дубликатов и количества оставшихся уникальных строк. Нажмите OK, чтобы закрыть окно и завершить работу функции.
Метод 2: удаление повторений при помощи “умной таблицы”
Другой способ удаления дублирующихся строк - использование "умной таблицы". Давайте проанализируем алгоритм шаг за шагом.
- Сначала нам нужно выделить всю таблицу, как в первом шаге предыдущего раздела.
- На вкладке Главная найдите кнопку Формат как таблица (раздел инструментов Стили). Нажмите стрелку вниз справа от названия кнопки и выберите понравившуюся цветовую схему стола.
- После выбора стиля открывается окно настроек, в котором указывается диапазон для создания "умной таблицы". Поскольку ячейки были предварительно выбраны, необходимо просто убедиться, что данные в поле верны. Если это не так, внесите исправления, убедитесь, что выбран пункт 'Таблица с заголовками', и нажмите OK. На этом процесс создания Интеллектуальной таблицы завершен.
- Затем мы переходим к основной задаче - поиску двойных строк в таблице. Для этого:
- установите курсор на любую ячейку таблицы;
- переключитесь на вкладку Конструктор (если вы не переключились автоматически после создания Интеллектуальной таблицы);
- нажмите кнопку Удалить дубликаты в разделе Инструменты.
- Следующие шаги точно такие же, как описанные выше в методе удаления дубликатов строк.
Примечание: Из всех методов, описанных в данной статье, этот является наиболее гибким и универсальным, позволяющим комфортно работать с таблицами различной структуры и размера.
Метод 3: использование фильтра
Следующий метод не удаляет дубликаты строк физически, но позволяет установить режим отображения таблицы так, чтобы они были скрыты при ее просмотре.
- Как обычно, выделите все ячейки таблицы.
- На вкладке "Данные" в разделе инструментов "Сортировка и фильтр" найдите кнопку "Фильтр" (значок напоминает воронку) и нажмите ее.
- После этого в строке с названиями столбцов таблицы появятся перевернутые треугольники (это означает, что фильтр включен). Чтобы получить доступ к расширенным настройкам, нажмите кнопку "Дополнительно" справа от кнопки "Фильтр".
- В появившемся окне Дополнительные настройки:
- Как и в предыдущем методе, проверьте адрес диапазона ячеек таблицы;
- установите флажок "Только уникальные записи";
- Нажмите OK.
- После этого действия все дубликаты данных больше не будут отображаться в таблице. Чтобы вернуться в стандартный режим, просто снова нажмите кнопку Фильтр на вкладке Данные.
Метод 4: условное форматирование
См. также "Использование сквозной передачи в Excel: Пошаговое руководство".
Условное форматирование - это гибкий и мощный инструмент, используемый для решения многих задач в Excel. В этом примере мы будем использовать его для выделения дублирующихся строк, после чего вы сможете удалить их любым удобным для вас способом.
- Выделите все ячейки в нашей таблице.
- На вкладке Главная нажмите кнопку Условное форматирование, расположенную в разделе инструментов Стили.
- Открывается список, в котором выбираем группу 'Правила выбора ячеек' и в ней пункт 'Повторяющиеся значения'.
- Оставьте окно настроек форматирования без изменений. Единственный параметр, который можно изменить в соответствии с вашими цветовыми предпочтениями, - это цветовая схема, используемая для заливки выделенных линий. Нажмите OK, когда будете готовы.
- Теперь все повторяющиеся ячейки в таблице "подсвечены", и вы можете работать с ними - редактировать содержимое или удалить всю строку любым удобным способом.
Примечание: Этот метод не так универсален, как описанный выше, поскольку он выбирает все ячейки с одинаковыми значениями, а не только те, для которых совпадает весь ряд. Это видно на предыдущем снимке экрана, где нужные наложения по названию региона выделены, но все ячейки с категориями регионов также выделены, поскольку значения этих категорий повторяются.
Метод 5: формула для удаления повторяющихся строк
Последний метод довольно сложен, и мало кто им пользуется, поскольку он основан на сложной формуле, объединяющей несколько простых функций. А чтобы задать формулу для собственной таблицы данных, необходимо иметь некоторый опыт и навыки работы в Excel.
Формула, позволяющая искать пересечения в столбце, обычно выглядит следующим образом:
=ESLIOBER ERROR(INDEX(column_address;SEARCH(0;COUNT(address_head:address_head(absolute);column_address;)+ IF(COUNT(address_head;column_address;)>1;0;1);0);")))
Давайте посмотрим, как с ним работать на примере нашей таблицы:
- Добавим новый столбец в конец таблицы, специально для отображения повторяющихся (дублирующихся) значений.
- В верхней ячейке нового столбца (не включая заголовок) введите формулу, которая в данном примере будет выглядеть следующим образом, и нажмите Enter:
=IFERROR(INDEX(A2:A90;SEARCHPOS(0;COUNTSLICE(E1:$E$1;A2:A90)+IF(COUNTSLICE(A2:A90;A2:A90)>1;0;1);0);").
- В заключение выделим новый столбец для удвоенных данных, оставив заголовок без изменений. Далее действуем строго по инструкции:
- установите курсор в конец строки формулы (убедитесь, что это действительно конец строки, так как в некоторых случаях длинная формула не помещается на одной строке);
- нажмите F2 на клавиатуре;
- затем нажмите Ctrl+SHIFT+Enter.
- Эти действия позволяют правильно заполнить все ячейки в столбце формулой, содержащей ссылки на массив. Проверьте результат.
Как уже упоминалось выше, этот метод сложен и функционально ограничен, поскольку не предполагает удаления найденных столбцов. Поэтому при прочих равных условиях рекомендуется использовать один из ранее описанных методов, которые более логически понятны и зачастую более эффективны.
Заключение
Excel предлагает несколько инструментов для поиска и удаления строк или ячеек с одинаковыми данными. Каждый из описанных методов специфичен и имеет свои ограничения. Использование "Умной таблицы" и функции "Удаление дубликатов" мы бы отнесли к универсальным опциям. В целом, при выполнении задания следует руководствоваться как особенностями структуры таблицы, так и вашими целями и видением конечного результата.
