При работе с таблицей или базой данных с большим количеством информации возможна ситуация, когда некоторые строки повторяются. Это ещё больше увеличивает массив данных. К тому же, при наличии дубликатов возможен некорректный подсчет результатов в формулах. Давайте разберемся, как в программе Microsoft Excel отыскать и удалить повторяющиеся строки.

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

Способ 1: простое удаление повторяющихся строк

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

Способ 2: удаление дубликатов в «умной таблице»

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

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

Способ 3: применение сортировки

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

После этого, повторяющиеся записи будут скрыты. Но их показ можно в любой момент включить повторным нажатием на кнопку «Фильтр» .

Способ 4: условное форматирование

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

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

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

Способ 5: применение формулы

Кроме того, найти дубликаты можно применив формулу с использованием сразу нескольких функций. С её помощью можно производить поиск дубликатов по конкретному столбцу. Общий вид данной формулы будет выглядеть следующим образом:

ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;; адрес_столбца;)>1;0;1);0));"")

После этих действий в столбце «Дубликаты» отобразятся повторяющиеся значения.

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

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

Проведение эффективной генеральной уборки на дисковом пространстве компьютера, наряду с использованием программ для автоматической очистки Windows, требует еще выполнения ручной работы по удалению ненужных файлов и файлов-дубликатов. Для отслеживания ненужных файлов предназначены специальные программы – анализаторы дискового пространства, они помогают отфильтровать содержимое дисков компьютера по определенным критериям (в частности, по весу), чтобы пользователь мог принять решение об удалении файлов или их оставлении. Для поиска дубликатов файлов также существует специальный софт – либо в виде отдельных программ или небольших утилит, либо в составе комплексного ПО для чистки и оптимизации Windows. Ниже рассмотрим пятерку программ для поиска дубликатов файлов. В пятерку вошли полностью бесплатные программы с поддержкой русского языка.

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

1. AllDup

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

Далее во вкладке «Метод поиска» устанавливаются критерии поиска. Здесь поиск дубликатов можно конкретизировать и к предустановленному критерию поиска по именам файлов добавить расширение, размер, содержимое и пр.

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

Чтобы вернуться к анализу результатов поиска дубликатов, выполненных AllDup, позднее, но не ждать завершения сканирования, текущие результаты можно сохранить в файл формата программы или экспортировать в файлы TXT и CSV.

У программы AllDup есть портативная версия, не требующая установки в систему.

2. Duplicate Cleaner

Еще одна функциональная программа для гибко настраиваемого поиска дубликатов файлов – Duplicate Cleaner. Программа Duplicate Cleaner существует в двух редакциях – в платной Pro и бесплатной Free. Хоть последняя и ограничена недоступностью некоторых функций, ее возможностей будет достаточно для эффективного поиска дубликатов. Duplicate Cleaner Free позволяет задавать критерии поиска по именам, содержанию, размеру, дате создания файлов. Предусматриваются дополнительные критерии поиска по данным аудиофайлов, а также фильтрация по типам контента и расширениям файлов. Все эти моменты настраиваются в первой вкладке программы «Критерии поиска».

Во второй вкладке программы – «Сканировать путь» – выбирается область поиска.

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

Результаты поиска Duplicate Cleaner могут быть экспортированы в файл формата табличных данных CSV. Экспорт данных осуществляется как для всего перечня дубликатов, так и для только помеченных пользователем файлов. Из преимуществ Duplicate Cleaner – удобный интерфейс и продуманная организация.

3. DupeGuru

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

Результаты поиска дубликатов могут быть отсортированы по пути размещения и размеру файлов. Контекстное меню результатов поиска DupeGuru содержит лишь необходимые для работы с найденными дубликатами операции.

Результаты поиска сохраняются в файл программы или экспортируются в HTML.

DupeGuru – кроссплатформенная программа, но под Windows адаптированы только ее старые версии. Предлагаемый на официальном сайте инсталлятор программы для Windows 7 подойдет также для версий системы 8.1 и 10.

4. CCleaner

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

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

5. Glary Utilities 5

Комплексная программа для чистки и оптимизации Windows Glary Utilities 5 в числе арсенала содержит утилиту для поиска дубликатов файлов. Эту же утилиту при желании с официального сайта программы можно скачать отдельно, если не требуется всей мощи программного пакета.

Поисковик дубликатов в составе Glary Utilities 5 простенький, но удобный и настраиваемый. Сканирование дубликатов можно запустить сразу, выбрав лишь область поиска – разделы диска, съемные устройства или отдельные папки. А можно конкретизировать поиск, нажав кнопку «Опции».

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

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

Для удобства пользователя найденные дубликаты Glary Utilities 5 каталогизирует по типам контента – документам, картинкам, видео, программам и т.п. По каждому типу отображается общий вес найденных файлов.

Поиск дублей в Excel – это одна из самых распространенных задач для любого офисного сотрудника. Для ее решения существует несколько разных способов. Но как быстро как найти дубликаты в Excel и выделить их цветом? Для ответа на этот часто задаваемый вопрос рассмотрим конкретный пример.

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

Допустим мы занимаемся регистрацией заказов, поступающих на фирму через факс и e-mail. Может сложиться такая ситуация, что один и тот же заказ поступил двумя каналами входящей информации. Если зарегистрировать дважды один и тот же заказ, могут возникнуть определенные проблемы для фирмы. Ниже рассмотрим решение средствами условного форматирования.

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

Пример дневного журнала заказов на товары:

Чтобы проверить содержит ли журнал заказов возможные дубликаты, будем анализировать по наименованиям клиентов – столбец B:

Как видно на рисунке с условным форматированием нам удалось легко и быстро реализовать поиск дубликатов в Excel и обнаружить повторяющиеся данные ячеек для таблицы журнала заказов.

Пример функции СЧЁТЕСЛИ и выделение повторяющихся значений

Принцип действия формулы для поиска дубликатов условным форматированием – прост. Формула содержит функцию =СЧЁТЕСЛИ(). Эту функцию так же можно использовать при поиске одинаковых значений в диапазоне ячеек. В функции первым аргументом указан просматриваемый диапазон данных. Во втором аргументе мы указываем что мы ищем. Первый аргумент у нас имеет абсолютные ссылки, так как он должен быть неизменным. А второй аргумент наоборот, должен меняться на адрес каждой ячейки просматриваемого диапазона, потому имеет относительную ссылку.

Самые быстрые и простые способы: найти дубликаты в ячейках .

После функции идет оператор сравнения количества найденных значений в диапазоне с числом 1. То есть если больше чем одно значение, значит формула возвращает значение ИСТЕНА и к текущей ячейке применяется условное форматирование.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Смотрите также видеоверсию статьи .

Первая часть способа.

Рассмотрим пример нахождения повторяющихся значений.

Для нахождения повторяющихся значений необходимо выполнить следующий пошаговый алгоритм действий:

1. Выделить исходный диапазон (A1:E8)
2. Выполнить команду: вкладка Главная / группа Стили / Условное форматирование / Создать правило

1. В диалоговом окне выбрать: «Использовать формулу для определения форматируемых ячеек», при этом диалоговое окно немного изменит свой вид, далее следует ввести следующую формулу: =СЧЁТЕСЛИ($A$1:$E$8;A1)>1

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

1. После нажатия кнопки «ОК», можно сразу наблюдать результат выполнения операции.

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

Вторая часть способа.

Иногда возникает необходимость искать не повторяющиеся ячейки, а целые строки.

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

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

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

1. После нажатия «ОК», результат не заставит себя долго ждать.

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