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

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

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

Как найти и выделить

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

В следующем окне выберите из выпадающего списка «повторяющиеся» , и цвет для ячейки и текста, в который нужно закрасить найденные дубликаты. Затем нажмите «ОК» и программа выполнит поиск дубликатов.

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

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

Как удалить

Чтобы удалить дубликаты в Excel можно воспользоваться следующими способами. Выделяем заполненные ячейки, переходим на вкладку «Данные» и нажимаем кнопочку «Удалить дубликаты» .

В следующем окне ставим галочку в пункте «Мои данные содержат заголовки» , если Вы выделили таблицу вместе с заголовками. Дальше отметьте галочками столбцы, в которых нужно найти повторы, и нажмите «ОК» .

Появится диалоговое окно с информацией, сколько было найдено и удалено одинаковых данных.

Второй способ для удаления дубликатов – это использование фильтра. Выделяем нужные столбцы вместе с шапкой. Переходим на вкладку «Данные» и в группе «Сортировка и фильтр» нажимаем на кнопочку «Дополнительно» .

В следующем окне в поле «Исходный диапазон» уже указаны ячейки. Отмечаем маркером пункт «скопировать результат в другое место» и в поле «Поместить результат в диапазон» указываем адрес одной ячейки, которая будет левой верхней в новой таблице. Ставим галочку в поле «Только уникальные записи» и нажимаем «ОК» .

Будет создана новая таблица, в которой не будет строк с повторами информации.

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

Как посчитать

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

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

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

В следующем окне уже указаны ячейки диапазона, маркером отмечаем «На новый лист» и нажимаем «ОК» .

Справой стороны перетаскиваем первые три заголовка в область «Названия строк» , а поле «Код» перетаскиваем в область «Значения» .

В результате получим сводную таблицу без дубликатов, а в поле «Код» будут стоять числа, соответствующие повторяющимся значениям в исходной таблице – сколько раз в ней повторялась данная строка.

Для удобства, выделим все значения в столбце «Сумма по полю Код» , и отсортируем их в порядке убывания.

Думаю теперь, Вы сможете найти, выделить, удалить и даже посчитать количество дубликатов в Excel для всех строк таблицы или только для выделенных столбцов.

[expert_bq id=»1570″]Как уже было сказано выше, этот метод сложен и функционально ограничен, так как не предполагает удаления найденных столбцов. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Последний метод достаточно сложен, и им мало, кто пользуется, так как здесь предполагается использование сложной формулы, объединяющей в себе несколько простых функций. И чтобы настроить формулу для собственной таблицы с данными, нужен определенный опыт и навыки работы в Эксель.

Как удалить дубликаты в Excel, найти повторяющиеся строки, убрать или выделить цветом одинаковые значения в Эксель

1. Результатом будет выделение дублированных элементов в заданном формате. В будущем при изменении записей в столбце «B» (например добавление новых) заданная функция будет проверять эту запись по формуле каждый раз.

Поиск и удаление дубликатов (повторяющихся значений) в Excel

При работе в Excel мы часто сталкиваемся с наличием повторяющихся значений в таблицах. Рассмотрим основные методы поиска и удаления дубликатов в Excel.

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

Удаление дубликатов

В Excel (начиная с версии 2007) есть стандартная функция удаления дубликатов.
Для поиска дубликатов выделяем диапазон ячеек, в котором будем искать повторяющиеся значение, и в панели вкладок выбираем Данные -> Работа с данными -> Удалить дубликаты:

Указываем столбцы по которым ищем дубликаты, и если таблица имеет заголовки, то также ставим галочку в Мои данные содержат заголовки:

Нажимаем OK и в результате получаем отформатированную таблицу без повторяющихся значений.

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

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

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

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

Выделить дубликаты в Excel также можно с помощью условного форматирования (начиная с версии 2007).
Снова выделяем таблицу, в которой ищем дубликаты и переходим в панели вкладок на Главная -> Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения:

Выбираем условия форматирования (например, выделяем ячейки красной заливкой, чтобы показать дубликаты), в результате получаем таблицу следующего вида:

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

[expert_bq id=»1570″]Как уже было сказано выше, этот метод сложен и функционально ограничен, так как не предполагает удаления найденных столбцов. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Последний метод достаточно сложен, и им мало, кто пользуется, так как здесь предполагается использование сложной формулы, объединяющей в себе несколько простых функций. И чтобы настроить формулу для собственной таблицы с данными, нужен определенный опыт и навыки работы в Эксель.

Как выделить ячейку цветом в Excel – инструкция

1. Результатом будет выделение дублированных элементов в заданном формате. В будущем при изменении записей в столбце «B» (например добавление новых) заданная функция будет проверять эту запись по формуле каждый раз.