Как найти и удалить дубликаты в Excel

Как в Эксель найти повторяющиеся значения?

Для примера я распределил фамилии прославленных футболистов российской эпохи в пару столбцов. Нарочно сделал повторы в столбиках (иллюстрации кликабельны).

Наша цель – найти повторы в столбцах Excel и выделить их цветом.

Действуем так:

Шаг №1. Выделяем весь диапазон.

Шаг №2. Кликаем на раздел «Условное форматирование» в главной вкладке.

Шаг №3. Наводим на пункт «Правила выделения ячеек» и в появившемся списке выбираем «Повторяющиеся значения».

Шаг №4. Возникнет окно. Вам нужно выбрать, хотите ли вы подсветить повторяющиеся или уникальные значения. Также можно установить цвета заливки и текста.

Нажмите «ОК», и вы обнаружите: одинаковые ячейки в двух столбиках теперь выделены! Как видите, это вопрос 30 секунд.

Описанный вариант — самый удобный для пользователей Эксель версий 2013 и 2016.

Видео

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

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

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

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

Использование формулы «СЧЕТЕСЛИ»

Например, предстоит найти похожие значения среди месяцев года. Выделите столбец и перейдите на «Главная» → «Стили» → «Условное форматирование» → «Создать правило» и используется формула. В открытом поле вводится «=СЧЕТЕСЛИ ($B$2:$B$9; B2) >1», т.е. указывается диапазон поиска, конкретное значение, которое повторяется больше, чем один раз. При этом выбирается заливка. Нажмите на «Формат» и укажите цвет.

Расширенный фильтр

Для того, чтобы найти дубликаты в Excel также можно воспользоваться расширенным фильтром (работает начиная с версии 2003). Вновь выделяем диапазон ячеек и в панели вкладок выбираем Данные -> Сортировка и фильтр -> Дополнительно:

Выбираем настройки фильтра, ставим флажок напротив Скопировать результат в другое место и Только уникальные записи:

Также вместо копирования результата в другое место можно выбрать опцию Фильтровать список на месте. В этом случае строки с дубликатами скроются и можно посмотреть какие конкретно данные являются дубликатами. Итоговый результат (слева — исходная таблица, справа — без дубликатов):

Как извлечь дубликаты из диапазона

Формулы, которые мы описывали выше, позволяют находить дубликаты в определенном столбце. Но часто речь идет о нескольких столбцах, то есть о диапазоне данных.

Рассмотрим это на примере числовой матрицы. К сожалению, с символьными значениями этот метод не работает.

При помощи формулы массива

{=ИНДЕКС(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11);СТРОКА($1:$100)); НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ(НАИМЕНЬШИЙ(ЕСЛИ( СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11);СТРОКА($1:$100)); НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));0)=СТРОКА($1:$100);СТРОКА($1:$100));»»);СТРОКА()-1))}

вы можете получить упорядоченный по возрастанию список дубликатов. Для этого введите это выражение в нужную ячейку и нажмите Ctrl+Alt+Enter.

Затем протащите маркер заполнения вниз на сколько это необходимо.

Чтобы убрать сообщения об ошибке, когда дублирующиеся значения закончатся, можно использовать функцию ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ИНДЕКС(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ( НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));0)=СТРОКА($1:$100);СТРОКА($1:$100));»»);СТРОКА()-1));»»)

Также обратите внимание, что приведенное выше выражение рассчитано на то, что оно будет записано во второй строке. Соответственно выше него будет одна пустая строка.

Поэтому если вам нужно разместить его, к примеру, в ячейке K4, то выражение СТРОКА()-1 в конце замените на СТРОКА()-3.

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

Смотрите также: “Пример использования функции ВПР в Эксель: пошаговая инструкция”

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

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

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

Как сравнить два столбца в Excel на совпадения

Представим, что наша таблица состоит из двух столбцов с данными. Нам нужно определить повторяющиеся значения в первом и втором столбцах. Для решения задачи нам помогут функции ЕСЛИ и СЧЁТЕСЛИ .

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.

Использование расширенного фильтра

Данный метод удаления дубликатов отличается простой реализации. Для его выполнения потребуется:

1. В разделе «Данные» возле кнопки «Фильтр» кликнуть по слову «Дополнительно». Откроется окно «Расширенный фильтр».

1. Поставить тумблер рядом со строкой «Скопировать результаты в другое место» и нажать на пиктограмму, расположенную около поля «Исходный диапазон».
2. Выделить мышкой диапазон ячеек, где требуется найти дубликаты. Окно выбора автоматически закроется.
3. Далее в строчке «Поместить результат в диапазон» также надо нажать ЛКМ по пиктограмме в конце и выделит любую ячейку вне таблицы. Это будет начальный элемент, в который вставится отредактированная табличка.

1. Установить галочку в строке «Только уникальные записи» и кликнуть «ОК». В итоге рядом с исходным массивом появится отредактированная таблица без дубликатов.

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

Обнаруживаем одинаковые ячейки при помощи встроенных фильтров Excel

Теперь рассмотрим, как можно обойтись без формул при поиске дубликатов в таблице. Быть может, кому-то этот метод покажется более удобным, нежели написание выражений Excel.

Организовав свои данные в виде таблицы, вы можете применять к ним различные фильтры. Фильтр в таблице вы можете установить по одному либо по нескольким столбцам. Давайте рассмотрим на примере.

В первую очередь советую отформатировать наши данные как «умную» таблицу. Напомню: Меню Главная – Форматировать как таблицу.

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

Вы можете убрать галочку с пункта «Выделить все», а затем отметить один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные значения. Так можно обнаружить дубликаты, если они есть. И все готово для их быстрого удаления.

Но при этом вы видите дубли только по отфильтрованному. Если данных много, то искать таким способом последовательного перебора будет несколько утомительно. Ведь слишком много раз нужно будет устанавливать и менять фильтр.

Формула проверки наличия дублей в диапазонах

Использование собственной формулы для проверки дубликатов в списке или диапазоне имеет ряд преимуществ, единственная задача — составление такой формулы. Но её я возьму на себя.

Внутри диапазона

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

Так выглядит на практике применение формулы:

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

А дело все в том, что формулу несложно видоизменить и улучшить.

Например, можно улучшить эффективность формулы, добавив в нее функцию СЖПРОБЕЛЫ — это позволит находить дубликаты, отличающиеся незаметными лишними пробелами:

Эта формула слегка отличается, так как проверяет встречаемость в диапазоне значения одной ячейки.

Если внести ее как правило отбора условного форматирования, она позволит выявлять неявные дубли. Ниже наглядная демонстрация:

Обратите внимание на пару моментов этой демонстрации:

• диапазон закреплен ($A$1:$B$4), а искомая ячейка — нет (A1).
• именно это позволяет условному форматированию находить все дубликаты в диапазоне

Как быстро удалить повторяющиеся значения в таблицах?

Часто такую процедуру проводят при работе с большим объем данных: ключевых слов, одинаковых вычислений и других манипуляций. Рассмотрим простой и быстрый метод ликвидации дублированных элементов, который подойдет тем пользователям, который впервые открыли файл пакета Microsoft.

1. Открываем уже готовую таблицу (не отформатированную), с которой будем работать. Выделяем весь табличный массив, а затем переходим во вкладку «Данные» и нажимаем кнопку «Удалить дубликаты».

1. Затем в открывшемся окне ставим галочку напротив записи «Мои данные содержат заголовки», ставим флажок напротив каждого столбца, где находятся повторы, после чего нажимаем кнопку «ОК».

Если повторяющиеся значения отсутствуют, вы узнаете об этом в сообщении, которым оповестит вас Excel. В противном случае, будет указано количество удаленных данных из таблицы.

Применение расширенного фильтра в Excel

Условное форматирование таблицы тоже считается полезным инструментом в плане удаления дублирующих значений. Основную работу будем выполнять во вкладке «Главная».

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

1. Затем в открывшемся меню нажимаем на пункт «Правила выделения ячеек», следом на кнопку «Повторяющиеся значения».

1. Следом открывается окно, в котором необходимо указать, какие значения будут отформатированы и каким цветом они будут выделяться на фоне всей таблицы. Затем подтверждаем свои действия нажатием на кнопку «ОК».

Полезно знать!

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

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

Теги