Большие электронные таблицы Excel могут содержать повторяющиеся данные, что часто увеличивает объем информации и может привести к ошибкам в результате обработки данных с помощью формул и других инструментов. Это особенно критично, например, при работе с денежными и финансовыми данными.
В этой статье мы рассмотрим способы поиска и удаления повторяющихся (дубликатов) данных, в частности, строк в Excel.
Метод 1: удаление дублирующихся строк вручную
Читайте также: «Как удалить верхние и нижние колонтитулы в Excel”
Первый способ максимально прост и заключается в удалении повторяющихся строк с помощью специального инструмента на ленте вкладки «Данные”.
- Полностью выделяем все ячейки таблицы с данными, используя, например, нажатую левую кнопку мыши.
- На вкладке «Данные» в разделе инструментов «Работа с данными» найдите кнопку «Удалить дубликаты» и нажмите ее.
- Заходим в настройки удаления дубликатов:
- Если обрабатываемая таблица содержит заголовок, отметьте пункт «Мои данные содержат заголовки», он должен быть отмечен.
- Далее в основном окне перечислены названия колонок, по которым будет производиться поиск дубликатов. Система считает совпадением, если в строках повторяются значения всех выбранных в конфигурации столбцов. Если вы удалите часть столбцов из сравнения, вы увеличите вероятность увеличения количества похожих строк.
- Внимательно все проверяем и нажимаем ОК.
- Кроме того, программа Excel автоматически найдет и удалит все повторяющиеся строки.
- По окончании процедуры на экране появится соответствующее сообщение с информацией о количестве найденных и удаленных дубликатов, а также о количестве оставшихся уникальных строк. Чтобы закрыть окно и завершить эту функцию, нажмите кнопку OK.
Метод 2: удаление повторений при помощи “умной таблицы”
Еще один способ удалить повторяющиеся строки — использовать смарт-таблицу. Давайте рассмотрим алгоритм шаг за шагом.
- Во-первых, нам нужно выделить всю таблицу, как и в первом шаге предыдущего раздела.
- Во вкладке «Главная» находим кнопку «Форматировать как таблицу» (раздел инструментов «Стили»). Щелкните стрелку вниз справа от названия кнопки и выберите нужную цветовую схему таблицы.
- После выбора стиля откроется окно настроек, в котором указан диапазон для создания «умной таблицы». Поскольку ячейки были выбраны заранее, вам просто нужно убедиться, что в поле введены правильные данные. Если это не так, вносим исправления, проверяем, что пункт «Таблица с заголовками» отмечен галочкой и нажимаем ОК. На этом процесс создания «умной таблицы» завершен.
- Далее приступаем к основной задаче — поиску двойных строк в таблице. За это:
- поместите курсор в произвольную ячейку таблицы;
- перейти на вкладку «Конструктор» (если после создания «умной таблицы» переход не произошел автоматически);
- в разделе «Инструменты» нажмите кнопку «Удалить дубликаты“.
- Следующие шаги точно такие же, как шаги по удалению повторяющихся строк, описанные в предыдущем методе.
Примечание: из всех методов, описанных в этой статье, это наиболее гибкий и универсальный, позволяющий комфортно работать с таблицами различной структуры и размера.
Метод 3: использование фильтра
Следующий метод физически не удаляет повторяющиеся строки, но позволяет установить режим отображения таблицы, чтобы скрыть их при просмотре.
- Как обычно, выделите все ячейки в таблице.
- На вкладке «Данные» в разделе инструментов «Сортировка и фильтрация» найдите кнопку «Фильтр» (иконка имеет вид воронки) и нажмите ее.
- После этого в строке с названиями столбцов таблицы появятся значки в виде перевернутого треугольника (это означает, что фильтр включен). Чтобы перейти к дополнительным настройкам, нажмите кнопку «Дополнительно», расположенную справа от кнопки «Фильтр“.
- В появившемся окне с дополнительными настройками:
- как и в предыдущем способе, проверяем адрес диапазона ячеек таблицы;
- поставить галочку «Только уникальные записи“;
- нажмите ОК.
- После этого все повторяющиеся данные больше не будут отображаться в таблице. Чтобы вернуться в стандартный режим, просто снова нажмите кнопку «Фильтр» на вкладке «Данные”.
Метод 4: условное форматирование
Читайте также: «Пример использования функции ВПР в Excel: пошаговая инструкция”
Условное форматирование — это гибкий и мощный инструмент, используемый для решения широкого круга задач в Excel. В данном примере мы будем использовать его для выделения двойных линий, после чего их можно удалить любым удобным способом.
- Выделите все ячейки нашей таблицы.
- На вкладке «Главная» нажмите кнопку «Условное форматирование», которая находится в разделе инструментов «Стили“.
- Откроется список, в котором выбираем группу «Правила выбора ячеек», а внутри нее пункт «Повторяющиеся значения“.
- Оставьте окно настроек формата без изменений. Единственный его параметр, который можно изменить в соответствии с вашими цветовыми предпочтениями, — это цветовая схема, используемая для заливки выбранных линий. Когда будете готовы, нажмите кнопку ОК.
- Теперь все повторяющиеся ячейки в таблице «выделены» и с ними можно работать — редактировать содержимое или удалять целые строки так, как вам удобнее.
Важно! Этот способ не такой универсальный, как описанные выше, так как выбирает все ячейки с одинаковыми значениями, а не только те, которым соответствует вся строка. Это видно на скриншоте выше, когда были выбраны нужные двойники по названиям регионов, но вместе с ними отмечены все ячейки с категориями регионов, т.к значения этих категорий повторяются.
Метод 5: формула для удаления повторяющихся строк
Этот последний способ достаточно сложен и мало кто его использует, так как он предполагает использование сложной формулы, объединяющей несколько простых функций. А чтобы настроить формулу для собственной таблицы данных, вам потребуются некоторый опыт и навыки работы с Excel.
Формула, позволяющая находить пересечения внутри определенного столбца, в общих чертах выглядит так:
=ЕСЛИ.ОШИБКА(ИНДЕКС(адрес_столбца,ПОИСКПОЗ(0,СЧЁТ.ЕСЛИ(дубликат_заголовка_столбца:дубликат_заголовка_столбца(абсолютный),адрес_столбца;)+ЕСЛИ(СЧЕТ.ЕСЛИ(адрес_столбца,адрес_столбца;)>1,0,1),0));»»)
Давайте посмотрим, как с ним работать на примере нашей таблицы:
- Мы добавили новый столбец внизу таблицы, специально предназначенный для отображения повторяющихся (дублирующихся) значений).
- В верхнюю ячейку нового столбца (не считая заголовка) вводим формулу, которая для данного конкретного примера будет выглядеть так, как показано ниже, и нажимаем Enter:
=ЕСЛИ.ОШИБКА(ИНДЕКС(A2:A90,ПОИСКПОЗ(0,СЧЁТЕСЛИ(E1:$E$1,A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90,A2:A90)>1,0,1),0));»»).
- Назначаем новый столбец для повторяющихся данных в конце, не трогая заголовок. Далее действуем строго по инструкции:
- поставьте курсор в конец строки формулы (нужно убедиться, что это действительно конец строки, так как в некоторых случаях длинная формула не помещается внутри одной строки);
- нажмите сервисную клавишу F2 на клавиатуре;
- затем нажмите комбинацию клавиш Ctrl+Shift+Enter.
- Эти действия позволяют корректно заполнить все ячейки столбца формулой, содержащей ссылки на массивы. Проверяем результат.
Как было сказано выше, этот метод сложен и функционально ограничен, так как не предполагает удаления найденных столбцов. Поэтому при прочих равных условиях рекомендуется использовать один из описанных выше способов, который является более логически понятным и зачастую более эффективным.
Заключение
Excel предлагает несколько инструментов для поиска и удаления строк или ячеек с одинаковыми данными. Каждый из описанных методов специфичен и имеет свои ограничения. К универсальному варианту можно отнести использование «умной таблицы» и функцию «Удалить дубликаты». В целом для выполнения задания необходимо руководствоваться как особенностями структуры таблицы, так и преследуемыми целями и видением конечного результата.