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