Как в экселе найти одинаковые данные. Как найти дубликаты в Excel
Иногда в повседневной компьютерной деятельности возникает задача найти дубликаты файлов. Причин для этого может быть множество: нехватка места на жестком диске, попытки уменьшить энтропию в своих файлах, разобраться со сброшенными в разное время фотографиями с фотоаппарата и множество других нужных случаев.
В сети можно найти большое количество программ, которые позволяют искать дубликаты файлов. Но зачем искать какие-то программы, если шикарный инструмент для такой работы обычно всегда под рукой. И называется этот инструмент Total Commander (TC ).
В этой статье я покажу все методы на основе Total Commander версии 8.5 , в этой версии поиск дубликатов файлов стал очень богат функционально.
!!!Маленькое важное отступление. Что нужно понимать под словом дубликат файла? Два файла ИДЕНТИЧНЫ только тогда, когда они полностью совпадают побитно. Т.е. любая информация в компьютере представлена последовательностью нулей и единиц. Так вот, файлы совпадают только тогда, когда у них полностью совпадает последовательность нулей и единиц, из которых эти файлы состоят. Все разговоры о том, что можно сравнить два файла по какому-либо другому признаку, глубоко ошибочны.
В TC есть два, различающихся по своей сути, метода поиска дублирующихся файлов:
- Синхронизировать каталоги;
- Поиск дубликатов;
Их особенность и применение лучше всего показать на примерах.
1.Синхронизация каталогов.
Данный метод применяется тогда, когда у вас две сравниваемые папки имеют идентичную структуру. Это обычно бывает во многих случаях, вот несколько из них:
- Вы регулярно делали архив своей рабочей папки. Через какое-то время вам понадобилось выяснить, какие файлы были добавлены или изменены с момента создания архива. Вы распаковываете весь архив в отдельную папку. Структура папок в нем практически совпадает с рабочей. Вы проводите сравнение двух папок «исходной» и «восстановленной из архива» и легко получаете список всех измененных, добавленных или удаленных файлов. Пара несложных манипуляций — и вы удаляете из восстановленной папки все дубликаты файлов, которые есть в рабочей.
- Вы работаете в папке на сетевом диске и регулярно делаете копию к себе на локальный диск. Со временем ваша рабочая папка стала довольно большой и время, затрачиваемое на полное копирование, стало очень большим. Чтобы не копировать каждый раз всю папку, можно сначала провести сравнение с резервной и скопировать только те файлы, которые были изменены или добавлены, а также удалить в резервной папке файлы, которые были удалены из основной.
Когда вы войдете во вкус, почувствуете всю предоставленную мощь этого метода, вы сами сможете придумать тысячи ситуаций, когда метод синхронизации каталогов окажет вам огромную помощь в работе.
Итак, как же все происходит на практике. Приступаем.
Предположим у нас есть основная папка «Рабочая» , в которой лежат файлы, с которыми происходит работа. И есть папка «Архив» , в которой лежит старая копия папки «Рабочая» . Наша задача найти в обеих папках дублирующиеся файлы и удалить их из папки «Архив» .
Открываем TC. В правой и левой панелях открываем сравниваемые папки:
Нажимаем меню «Команды»-«Синхронизировать каталоги…»
Открывается окно сравнения каталогов
Далее нам необходимо установить параметры сравнения. Ставим галочки в параметры «с подкаталогами», «по содержимому», «игнорировать дату»
- «с подкаталогами» — будут сравниваться файлы во всех подкаталогах, указанных папок;
- «по содержимому» — вот ключевая опция, которая заставляет TC сравнивать файлы ПОБИТНО!!! В противном случае, файлы будут сравниваться по имени, размеру, дате;
- «игнорировать дату» — эта опция заставляет TC показывать различающиеся файлы, без попытки автоматического определения направления будущего копирования;
!!! Сравниваться будут файлы только с одинаковыми именами!!! Если файлы идентичны, но они имеют разное имя, то они не будут сравниваться!
Нажимаем кнопку «Сравнить».
В зависимости от объёма файлов, сравнение может идти очень долго, не пугайтесь. В конце концов сравнение закончится и в нижней строке состояния(на рисунке секция 1) отобразится результат:
Если кнопки в секции «Показывать» (на рисунке секция 2) нажаты, то вы увидите результат сравнения для каждого файла.
— эта кнопка включает отображение файлов, которые есть в левой панели, но которых нету в правой;
— эта кнопка включает отображение идентичных файлов;
— эта кнопка включает отображение различающихся файлов;
— эта кнопка включает отображение файлов, которые есть в правой панели, но которых нету в левой;
Если у вас, изначально, все кнопки отображения выключены, то результат сравнения можно оценить только по строке состояния (на рисунке выше секция 1), в данном случае мы видим что сравнились 11 файлов, из которых 8 файлов являются одинаковыми, 2 файла различаются, и еще в левой панели имеется файл, которого нет в правой панели.
Для выполнения нашего задания необходимо оставить отображение только идентичных(одинаковых) файлов, поэтому все остальные кнопки отображения выключаем
Теперь у нас остались только идентичные файлы, и мы спокойно можем удалить их в папке «Архив»
. Для этого выделяем все файлы. Проще всего это сделать нажатием универсальной комбинации CTRL+A
. Или сначала выделить мышкой первую строчку, потом нажать на клавиатуре клавишу SHIFT
и не отпуская ее выделить мышкой последнюю строчку. В результате у вас должно получиться вот так:
Заключительным шагом мы нажимаем правой клавишей мышки на любую строку и в открывшемся меню выбираем пункт «Удалить слева»
TC любезно переспрашивает нас о нашем желании,
и если мы нажимаем «ДА»
, то он удаляет все помеченные файлы в папке «Архив»
.
После этого, автоматически, происходит повторное сравнение двух папок. Если вам не нужно повторное сравнение, то процесс можно прервать, нажав на кнопку «Прервать»
или нажать клавишу ESC
на клавиатуре. Если повторное сравнение не было прервано, и мы включили все кнопки отображения, то мы увидим вот такое окно
Все. Поставленная задача выполнена. Все одинаковые файлы найдены и удалены в папке «Архив» .
Обучающее видео по теме
2.Поиск дубликатов.
Коренное отличие данного метода от метода синхронизации каталогов заключается в том, что TC игнорирует имена сравниваемых файлов. Фактически, он сравнивает каждый файл с каждым, и показывает нам идентичные файлы как бы они не назывались ! Такой поиск очень удобен, когда вы не знаете ни структуры папок, ни имен сравниваемых файлов. В любом случае, после поиска дубликатов, вы получите точный список идентичных файлов.
Поиск дубликатов я покажу на одной практической задаче, поиске дубликатов личных фотографий. Довольно часто вы сбрасываете в компьютер фотографии со своих цифровых гаджетов. Частенько ситуация запутывается, что-то сбрасывается по многу раз, что-то пропускается. Как быстро удалить файлы, сброшенные несколько раз? Очень просто!
Приступаем.
Предположим, вы всегда сбрасываете все свои фотографии в папку «ФОТО»
на диске D. После всех сбросов папка имеет примерно такой вид:
Как видим, некоторые файлы находятся в папках, названных по дате съемки, некоторые сброшены в корень папки «_Новые»
и «_Новые1»
Чтобы начать поиск дубликатов открываем в любой панели TC папку, в которой будем искать. В нашем случае это папка «ФОТО»
Далее нажимаем на клавиатуре комбинацию клавиш ALT+F7
или в меню выбираем «Команды»-«Поиск файлов»
Открывается окно стандартного поиска TC. Строку «Искать файлы:»
оставляем пустой, тогда будут сравниваться все файлы.
Затем переходим на закладку «Дополнительно»
и выставляем галочки «Поиск дубликатов:», «по размеру», «по содержимому»
и нажимаем «Начать поиск»
.
Поиск может идти ОЧЕНЬ долго, не надо этого пугаться, так как происходит огромное количество сравнений большого объёма файлов. При этом в строке состояния показывается процент выполнения
Когда поиск закончится, откроется окно результатов поиска, в котором нажимаем кнопку «Файлы на панель»
В окне поиска и в окне панели идентичные файлы собраны в секции, отделенные пунктирными линиям
В каждой секции отображается имя файла и полный путь к файлу. Имена ИДЕНТИЧНЫХ файлов могут быть абсолютно различными!
В данном случае видно, что одна и таже фотография записана ТРИ раза, причем два раза под одним именем(IMG_4187.JPG
) а третий раз эта фотография записана под совершенно другим именем(IMG_4187_13.JPG
).
Далее остается выделить ненужные одинаковые файлы и удалить их. Это можно делать вручную, выделяя каждый файл нажатием клавиши Ins . Но это долго и не эффективно. Есть более правильные и быстрые способы.
Итак, наша задача состоит в том, чтобы удалить повторяющиеся файлы в папках «_Новые»
и «_Новые1»
.
Для этого нажимаем на дополнительной клавиатуре, справа большую клавишу [+]
. Обычно этой клавишей в TC выделяются файлы по маске. Эту же операцию можно сделать через меню «Выделение»-«Выделить группу»
Рассмотрим, как найти повторяющиеся значения в
Excel
, как выделить одинаковые значения словами, знаками
, посчитать количество одинаковых значений
, узнаем формулу для поиска одинаковых значений в Excel
, т.д.
В Excel можно не только выделять и удалять дублирующие данные, но и работать с ними – посчитать дубли перед удалением, обозначить дубли словами, числами, знаками, найти повторяющиеся строки, состоящие из нескольких ячеек, т.д.
Рассмотрим, как выделить и обозначить дубли. У нас такая таблица, в которой рассмотрим варианты работы с дублями. Дублирующие данные подкрасили условным форматированием.
Есть два варианта выделять ячейки с одинаковыми данными. Первый вариант, когда выделяются все ячейки с одинаковыми данными. Например, как в таблице (ячейки А5 и А8). Второй вариант – выделяем вторую и следующие ячейки в одинаковыми данными. А первую ячейку не выделять (выделить только ячейку А8). Будем рассматривать оба варианта.
Первый способ.
Как выделить повторяющиеся значения в Excel.
Нам нужно в соседнем столбце напротив данных ячеек написать слово «Да», если есть дубль, у ячеек с уникальными данными написать слово «Нет».
В столбце А устанавливаем фильтр. Как установить и работать с фильтром, смотрите в статье «Фильтр в Excel ». В ячейке B2 пишем слово «Нет».
Нажимаем два раза левой мышкой на зеленый квадратик в правом нижнем углу ячейки (на картинке обведен красным цветом). Слово скопируется вниз по столбцу до последней заполненной ячейки таблицы.
Теперь в столбце A отфильтруем данные – «Фильтр по цвету ячейки». Можно по цвету шрифта, зависит от того, как выделены дубли в таблице.
В таблице остались две строки с дублями. В верхней ячейке отфильтрованного столбца B пишем слово «Да». Копируем по столбцу.
Возвращаем фильтром все строки в таблице. Получилось так.
Мы подсветили ячейки со словом «Да» условным форматированием. Вместо слов, можно поставить числа. Получится так.
Этот способ подходит, если данные в столбце A не меняются. Или, после изменения, повторить процедуру с обозначением дублей.
Второй способ.
Как выделить повторяющиеся ячейки в Excel.
Установим формулу в столбце D, чтобы автоматически писались слова. Формула такая. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;"Да";"Нет")
Копируем формулу по столбцу. Получится так.
Обратите внимание
, что такое выделение дублей, выделяет словом «Да» следующие повторы в ячейках, кроме первой ячейки.
Слова в этой формуле можно писать любые или числа, знаки. Например, в столбце E написали такую формулу. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;"Повторно";"Впервые")
В столбце F написали формулу. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;"+";"-") Получилось так.
Идея.
Можно в таблице использовать формулу из столбца E или F, чтобы при заполнении соседнего столбца было сразу видно, есть дубли в столбце или нет. Например, создаем список фамилий в столбце А. В столбце B установили формулу.
=ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;"+";"-") Если в столбце В стоит «+», значит такую фамилию уже написали.
Третий способ.
Посчитать количество одинаковых значений Excel.
Нам нужно не только выделить повторы, но и вести их подсчет, написать в ячейке их количество.
В ячейке G5 пишем такую формулу. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A$10;A5)>1;СЧЁТЕСЛИ(A$5:A5;A5);1) Копируем по столбцу. Получился счетчик повторов.
Изменим данные в столбце А для проверки. Получилось так.
Ещё один способ подсчета дублей описан в статье "Как удалить повторяющиеся значения в Excel ".
Как посчитать данные в ячейках с дублями, а, затем, удалить их, смотрите в статье «Как сложить и удалить ячейки с дублями в Excel» .
Четвертый способ.
Формула для поиска одинаковых значений в Excel.
Нам нужно выделить дубли формулой в условном форматировании. Выделяем ячейки. Вызываем диалоговое окно условного форматирования. Выбираем функцию «Использовать формулу для определения форматируемых ячеек».
В строке «Форматировать формулу для определения форматируемых ячеек» пишем такую формулу. =СЧЁТЕСЛИ($A:$A;A5)>1 Устанавливаем формат, если нужно выбрать другой цвет ячеек или шрифта.
Нажимаем «ОК». Все ячейки с повторяющимися данными окрасились.
Идея.
Можно в условном форматировании установить белый цвет заливки и шрифта. Получится так.
Первые ячейки остались видны, а последующие повторы не видны. При изменении данных в первом столбце меняются и пустые ячейки, в зависимости от того, где находятся дубли.
Пятый способ.
Как найти повторяющиеся строки в Excel.
Нужно сравнить и выделить данные по трем столбцам сразу. У нас такая таблица.
В столбцах A, B, C стоят фамилии, имена и отчества. Чтобы сравнить сразу по трем столбцам, нужно соединить данные трех столбцов в одной ячейке. В ячейке D15 пишем формулу, используя функцию «СЦЕПИТЬ» в Excel. =СЦЕПИТЬ(A15;" ";B15;" ";C15)
Про функцию «СЦЕПИТЬ» читайте
Если Вы работаете с большими количеством информации в Excel и регулярно добавляете ее, например, данные про учеников школы или сотрудников компании, то в таких таблицах могут появиться повторяющиеся значения, другими словами – дубликаты.
В данной статье мы рассмотрим, как найти, выделить, удалить и посчитать количество повторяющихся значений в Эксель.
Как найти и выделить
Найти и выделить дубликаты в документе можно, используя условное форматирование в Эксель . Выделите весь диапазон данных в нужной таблице. На вкладке «Главная» кликните на кнопочку «Условное форматирование» , выберите из меню «Правила выделения ячеек» – «Повторяющиеся значения» .
В следующем окне выберите из выпадающего списка «повторяющиеся» , и цвет для ячейки и текста, в который нужно закрасить найденные дубликаты. Затем нажмите «ОК» и программа выполнит поиск дубликатов.
В примере Excel выделил розовым всю одинаковую информацию. Как видите, данные сравниваются не построчно, а выделяются одинаковые ячейки в столбцах. Поэтому выделена ячейка «Саша В.» . Таких учеников может быть несколько, но с разными фамилиями.
Как посчитать
Если Вам нужно найти и посчитать количество повторяющихся значений в Excel, создадим для этого сводную таблицу Excel. Добавляем в исходную столбец «Код» и заполняем его «1» : ставим 1, 1 в первых двух ячейка, выделяем их и протягиваем вниз. Когда будут найдены дубликаты для строк, каждый раз значение в столбце «Код» будет увеличиваться на единицу.
Выделяем все вместе с заголовками, переходим на вкладку «Вставка» и нажимаем кнопочку «Сводная таблица» .
Чтобы более подробно узнать, как работать со сводными таблицами в Эксель , прочтите статью перейдя по ссылке.
В следующем окне уже указаны ячейки диапазона, маркером отмечаем «На новый лист» и нажимаем «ОК» .
Справой стороны перетаскиваем первые три заголовка в область «Названия строк» , а поле «Код» перетаскиваем в область «Значения» .
В результате получим сводную таблицу без дубликатов, а в поле «Код» будут стоять числа, соответствующие повторяющимся значениям в исходной таблице – сколько раз в ней повторялась данная строка.
Для удобства, выделим все значения в столбце «Сумма по полю Код» , и отсортируем их в порядке убывания.
Думаю теперь, Вы сможете найти, выделить, удалить и даже посчитать количество дубликатов в Excel для всех строк таблицы или только для выделенных столбцов.
Оценить статью:Распространенный вопрос как найти и удалить дубликаты в Excel. Предположим вы выгрузили месячный отчет из вашей учетной системы, но в итоге вам нужно понять какие контрагенты вообще взаимодействовали с компанией за этот период — оставить список контрагентов без повтарений. Как отобрать уникальные значения?
Можно ли удалить задвоеные, затроенные и так далее значения в Excel по нескольким столбцам?
Можно, причем очень просто. Для этого есть специальная функция. Предварительно выберите диапазон, где нужно удалять дубликаты. На ленте заходим Данные — Удалить дубликаты (смотрите картинку в начале статьи).
Выбираем первый столбец
При этом важно понимать, что если вы выберите только первый столбец, то все данные в не выбранных столбцах удаляться в случае неуникальности.
Очень удобно!
2. Как выделить все дубликаты в Excel?
Уже слышали про ? Да, здесь оно еще как поможет! Выделяете столбец в котором надо пометить дубликаты, выбираете в меню Главное — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения…
В открывшемся окне Повторяющиеся значения, выберите какие ячейки выделяем (уникальные или повторяющиеся), а так же формат выделения, либо из преложенных, либо создайте Пользовательский формат. Предустановлено форматом будет красная заливка и красный текст.
Нажимаете ОК, если не хотите изменять форматирование. Теперь все данные по выбранным условиям подкрасятся.
Отмечу, что инструмент применяется только для выбранного одного (!) столбца.
Кстати, если нужно увидеть уникальные, то в окне слева выберите — уникальные.
3. Уникальные значения при помощи сводных таблиц
Признаюсь честно, когда-то я не подозревал о существовании возможности «удалить дубликаты» и пользовался сводными таблицами. Как я это делал? Выделяете таблицу, в которых надо найти уникальные значения — Вставка —
Программа для поиска дубликатов файлов чаще всего необходима пользователям, хранящим на диске большое количество музыки, фото и документов.
И, хотя удалять такие лишние копии можно вручную, специализированные приложения способны сэкономить немало времени.
Особенно, если файлы расположены в разных папках или в .
Искать такие дубликаты можно с помощью универсального программного обеспечения или же рассчитанного на конкретный тип данных.
В первом случае повышается скорость поиска, во втором – увеличивается вероятность обнаружить все копии.
Содержание:Универсальные приложения
Универсальные приложения для поиска копий, в основном, работают по принципу сравнивания размеров файлов.
И, так как вероятность совпадения количества байтов у разных фото практически равна нулю, одинаковые значения считаются признаком дубликата.
Иногда алгоритм предусматривает проверку имён – тоже важный параметр для поиска, тем более что одинаковые данные в большинстве случаев совпадают и по названию.
Преимуществами программ являются возможность найти с их помощью файлы любого типа и сравнительно высокая скорость работы. Недостаток – меньшая точность обнаружения.
Так, например, ни одна из таких утилит не посчитает дубликатом одну и ту же , сохранённую с различным разрешением.
1. DupKiller
А среди её преимуществ можно отметить:
- простоту настройки;
- установку нескольких критериев поиска;
- возможность игнорировать некоторые файлы (с определённым размером или датой создания, а также системные или скрытые).
Важно: При обнаружении файлов с нулевым размером их не обязательно удалять. Иногда это может быть информация, созданная в другой операционной системе (например, Linux).
Рис. 4. Программа для оптимизации системы CCleaner может искать и дубликаты файлов.
5. AllDup
Среди преимуществ ещё одной программы, AllDup , можно отметить поддержку любой современной операционной системы Windows – от XP до 10-й.
При этом поиск ведётся и внутри скрытых папок, и даже в архивах.
Хотя сравнение информации по умолчанию происходит по названиям файлов, поэтому настройки желательно сразу же изменить.
Зато в процессе поиска каждый найдённый дубликат можно просмотреть, не закрывая приложение.
А при обнаружении копии её можно не только удалить, но и переименовать или перенести в другое место.
К дополнительным преимуществам приложения относится и полностью бесплатная работа в течение любого периода времени.
Кроме того, производитель выпускает ещё и портативную версию для того чтобы искать копии на тех компьютерах, на которых запрещена установка постороннего ПО (например, на рабочем ПК).
Рис. 5. Поиск файлов с помощью portable-версии AllDup.
6. DupeGuru
Ещё одним полезным приложением, проводящим поиск дубликатов с любым расширением, является DupeGuru .
Её единственный недостаток – отсутствие новых версий для Windows (при этом обновления для и MacOS появляются регулярно).
Впрочем, даже сравнительно устаревшая утилита для неплохо справляется со своими задачами и при работе в более новых ОС.
С её помощью легко обнаруживаются даже системные файлы, а меню интуитивно понятное и русскоязычное.
Рис. 6. Обнаружение копий с помощью утилиты DupeGuru.
Примечательно, что, кроме обычного универсального варианта, компания-производитель создала утилиту для поиска файлов определённого типа.
Существует отдельная версия для изображений и ещё одна для музыки.
И, при необходимости очистить свой компьютер не только от документов и системных файлов (которые, кстати, требуется удалять очень осторожно – иногда стоит даже оставить «лишнюю» копию, чем нарушить работоспособность системы), стоит скачать и эти приложения.
7. Duplicate Cleaner Free
Утилита для обнаружения копий любого файла Duplicate Cleaner Free отличается следующими особенностями:
- фильтрации данных по расширению;
- русским языком интерфейса;
- возможность бесплатного использования;
- высокой скоростью работы.
К её недостаткам относят небольшие ограничения при поиске изображений (для этого рекомендуется приобрести платную версию) и не совсем точный перевод отдельных элементов меню .
Тем не менее, благодаря своей эффективности и удобству использования, приложение пользуется определённой популярностью.
Рис. 7. Поиск дубликатов с помощью утилиты Duplicate Cleaner Free.
Поиск дубликатов аудио файлов
Если результаты поиска дубликатов не устраивают пользователя, можно рассмотреть вариант, предназначенный для определённых файлов. Например, для , скопившейся на диске.
Такая необходимость часто возникает при загрузке сразу нескольких альбомов и сборников одного и того же исполнителя – нередко в разных папках оказываются одинаковые треки.
Они могут иметь похожие размеры и отличаться, по большому счёту, только названиями. Специально для этого существуют утилиты для поиска похожих мелодий.
8. Music Duplicate Remover
Среди особенностей программы Music Duplicate Remover – сравнительно быстрый поиск и неплохая эффективность.
Фактически, это приложение как бы «прослушивает» композицию и проводит её сравнение с другими аудио файлами.
При этом, естественно, время её работы больше, чем у универсальных утилит.
Однако количество проверяемых программой данных, как правило, в десятки раз меньше, поэтому средняя продолжительность проверки редко превышает пару часов.
Рис. 8. Обнаружение копий музыки и аудио файлов по альбомам.
9. Audio Comparer
При этом анализ фото тоже длится дольше по сравнению с поиском файлов любого расширения, но результат оправдывает себя.
Изображения определяются даже в том случае, когда на диске находятся несколько дубликатов одной картинки, но с разным разрешением и, соответственно, размером.
Кроме того, для повышения эффективности проверяются файлы с любыми графическими расширениями – от до.png.
Рис. 11. Поиск картинок с помощью ещё одной версии DupeGuru.
12. ImageDupeless
При этом она распространяется бесплатно и имеет русский интерфейс. А производитель периодически выпускает к ней обновления, повышая эффективность поиска изображений.
Рис. 12. Стильный интерфейс приложения ImageDupeless.
13. Image Comparer
Преимуществами приложения Image Comparer , кроме простого интерфейса, можно назвать наличие пошагового мастера, позволяющего научиться быстрому и результативному поиску изображений.
Эта особенность отличает утилиту от большинства остальных, для работы с которыми придётся читать файлы справки, не всегда правильно переведённые (а иногда и вообще предоставляемые только на английском).
Фактически, приложение является ещё одной версией Audio Comparer, и тоже распространяется по «условно бесплатной» лицензии – то есть за определённые функции пользователю придётся заплатить.
Рис. 13. Приложение Image Comparer – неплохой способ найти дубликаты картинок.