Из исходной таблицы с повторяющимися значениями отберем только те значения, которые имеют повторы. Теперь при добавлении новых значений в исходный список, новый список будет автоматически содержать только те значения, которые повторяются.
Пусть в столбце А имеется список с значениями, например список с названиями компаний. В некоторых ячейках исходного списка имеются повторы.
Задача
Создадим новый список, который содержит только те значения, которые повторяются. Дополнительное условие: при добавлении новых значений в исходный список, новый список должен автоматически включать только повторяющиеся значения.
Решение
Список значений, которые повторяются, создадим в столбце B с помощью . (см. файл примера ).
Введем в ячейку B5
:
=ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок;
ПОИСКПОЗ(0;СЧЁТЕСЛИ(B4:$B$4;ИсхСписок)+ ЕСЛИ(СЧЁТЕСЛИ(ИсхСписок;ИсхСписок)>1;0;1);0)
);"")
Вместо ENTER нужно нажать CTRL + SHIFT + ENTER .
Скопируйте формулу вниз с помощью (размерность списка значений имеющих повторы должна совпадать с размерностью исходного списка).
В файле примера также приведены перечни, содержащие неповторяющиеся значения и уникальные значения.
С помощью в исходном списке можно выделить повторяющиеся значения.
Тестируем
1. Добавьте в исходный список название новой компании (в ячейку А20 введите ООО Кристалл)
2. Список неповторяющихся значений (столбец С) автоматически будет обновлен, чтобы включить новое название
3. Добавьте в исходный список название новой компании еще раз (в ячейку А21 снова введите ООО Кристалл)
4. Список неповторяющихся значений автоматически будет обновлен, новое название будет исключено
Достаточно часто рабочие таблицы Excel содержат повторяющиеся записи, которые многократно дублируются. Но не всегда повторение свидетельствует об ошибке ввода данных. Иногда несколько раз повторяющиеся записи с одинаковыми значениями были сделаны намеренно. Тогда проблема может возникнуть при обработке, поиске данных или анализе в такой таблице. Чтобы облегчить себе работу с такими таблицами, рекомендуем автоматически объединить одинаковые строки в таблице Excel, выделив их цветом.
Как объединить одинаковые строки одним цветом?
Чтобы найти объединить и выделить одинаковые строки в Excel следует выполнить несколько шагов простых действий:
В результате выделились все строки, которые повторяются в таблице хотя-бы 1 раз.
Как выбрать строки по условию?
Форматирование для строки будет применено только в том случаи если формула возвращает значения ИСТИНА. Принцип действия формулы следующий:
Первая функция =СЦЕПИТЬ() складывает в один ряд все символы из только одной строки таблицы. При определении условия форматирования все ссылки указываем на первую строку таблицы.
Абсолютные и относительные адреса ссылок в аргументах функций позволяют нам распространять формулу на все строки таблицы.
Вторая функция =СЦЕПИТЬ() по очереди сложить значение ячеек со всех выделенных строк.
Обе выше описанные функции работают внутри функции =ЕСЛИ() где их результаты сравниваются между собой. Это значит, что в каждой ячейке выделенного диапазона наступает сравнение значений в текущей строке со значениями всех строк таблицы.
Как только при сравнении совпадают одинаковые значения (находятся две и более одинаковых строк) это приводит к суммированию с помощью функции =СУММ() числа 1 указанного во втором аргументе функции =ЕСЛИ(). Функция СУММ позволяет сложить одинаковые строки в Excel.
Если строка встречается в таблице только один раз, то функция =СУММ() вернет значение 1, а целая формула возвращает – ЛОЖЬ (ведь 1 не является больше чем 1).
Если строка встречается в таблице 2 и более раза формула будет возвращать значение ИСТИНА и для проверяемой строки присвоится новый формат, указанный пользователем в параметрах правила (заливка ячеек зеленым цветом).
Как найти и выделить дни недели в датах?
Допустим таблица содержит транзакции с датами их проведения. Необходимо найти одну из них, но неизвестны все детали. Известно только, что транзакция проведена во вторник или в среду. Чтобы облегчить себе поиск, выделим цветом все даты этих дней недели (вторник, среда). Для этого будем использовать условное форматирование.
Все транзакции, проводимые во вторник или в среду выделены цветом.
Проведение эффективной генеральной уборки на дисковом пространстве компьютера, наряду с использованием программ для автоматической очистки 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 каталогизирует по типам контента – документам, картинкам, видео, программам и т.п. По каждому типу отображается общий вес найденных файлов.
Как жаль, что жесткие диски не резиновые! Хотелось скачать пару-тройку фильмов на выходные да установить на ПК новую игру, но вот досада – свободное место почти закончилось. И освободить его нельзя, ведь на диске хранится только важное и нужное.
Выше нос! Вы можете увеличить объем свободного места на 5-50% без риска потерять ценную информацию. Как? Путем удаления идентичных копий документов, фотографий, аудиозаписей, видео и прочего добра, которого за год работы компьютера скапливается несколько сотен. Итак, как произвести поиск и удаление дубликатов файлов в Windows
К сожалению, встроенный в систему инструмент очистки дисков «не обучен» искать идентичные копии данных, и нам придется обратить свой взор на сторонние приложения. В сегодняшнем обзоре – 5 бесплатных утилит, которые делают эту работу быстро, четко и чисто.
Auslogics Duplicate File Finder
Одно только то, что , заявляет о некотором уровне качества и претендует на доверие пользователей. Программа поддерживает все выпуски Windows, регулярно обновляется, имеет русский язык и очень легко поддается освоению. Для поиска дубликатов она использует особый интеллектуальный алгоритм – сравнивает не только имена, но и внутреннее содержимое файлов, что гарантирует высокую точность результатов.
Функции и возможности Auslogics Duplicate File Finder
- Настройка фильтра по типу объектов: все либо только изображения, аудио, видео, архивы и приложения.
- Исключение из поиска файлов меньше и больше заданного размера.
- Исключение из критериев поиска названий, дат создания/изменения файлов и атрибутов «скрытый».
- Нахождение дубликатов с определенными именами или заданным фрагментом в имени.
- 3 способа удаления дублей: в корзину, безвозвратно и путем сохранения в архив.
- Восстановление данных из архива.
- Предпросмотр объектов перед удалением.
- Поддержка пользовательских списков исключения из сканирования файлов и папок.
- Откат системы на точку восстановления из интерфейса программы.
Использовать Auslogics Duplicate File Finder легко и удобно. К тому же можно не опасаться, что программа уничтожит какие-либо данные без вашего ведома и возможности восстановления (только если вы сами не выбрали опцию удаления мимо корзины). Впечатление портит лишь «ненавязчивое» предложение установить другие продукты разработчика, которое иногда появляется при запуске.
CloneSpy
Утилита предназначена для поиска дублированных файлов не только на жестком диске, но и на переносных накопителях. Она чуть сложнее в применении, чем Auslogics Duplicate File Finder, но более функциональна и способна работать без установки на компьютер – при запуске инсталлятора юзер может выбрать ее стандартный или портабельный вариант.
Функции и возможности CloneSpy
- Поддержка любых типов файлов.
- Поиск дубликатов по идентичному содержимому или одинаковому имени в совокупности с другим критерием – расширением, названием, размером.
- Нахождение объектов нулевой длины.
- Множество вариантов операций с копиями файлов: удаление вручную или автоматически (можно заранее выбрать, какой объект удалять – новый или старый), помещение в определенную папку, формирование списка дубликатов без удаления и прочее.
- Любые операции с найденными дубликатами (в том числе отложенные) при помощи пакетных командных файлов.
- Замена удаленных копий на ярлыки или жесткие ссылки.
- Вычисление контрольных сумм для возможности проверки объектов на идентичность.
- Поддержка пользовательских фильтров поиска.
CloneSpy функционирует быстро и бесперебойно, поддерживает все версии винды, регулярно получает обновления, но не имеет русского языка, что несколько затрудняет его освоение. Кроме того, некоторых пользователей отталкивает интерфейс, выполненный в стиле Windows 98.
Soft4Boost Dup File Finder
Разработчики утилиты позаботились не только о функциональности, но и о красоте своего продукта: к вашим услугам – 11 стильных вариантов оформления (скинов). Кроме того, приложение переведено на 9 языков, включая русский, и имеет интуитивно понятное управление.
Soft4Boost Dup File Finder приступает к делу сразу – при первом запуске на фоне главного окна открывается окошко мастера, который пошагово проведет вас через все этапы настройки. Запутаться и ошибиться не получится.
Функции и возможности Soft4Boost Dup File Finder
- Нахождение копий объектов по содержимому.
- Выбор категории поиска: среди всех файлов или только изображения, аудио, видео, архивы или приложения.
- Опция игнорирования имен объектов.
- Сканирование локальных и сетевых папок, а также съемных носителей (можно выбрать несколько).
- 2 способа удаления: в корзину и безвозвратно.
В отличие от многих «халявных» приложений, которые пытаются окупить свою бесплатность попутной установкой мусора или навязчивой демонстрацией рекламы, Soft4Boost Dup File Finder лишь скромно предлагает купить продукты того же разработчика оптом по 1399 рублей. Если они вам не нужны, просто не нажимайте на банер.
Как и остальные программы сегодняшнего обзора, Soft4Boost Dup File Finder поддерживает все версии Windows и регулярно обновляется.
AllDup
Утилита не столь интуитивна, как предыдущая, зато богата функциями. Не будь в ней русского языка, освоить все возможности за 5 минут смог бы не каждый. Хотя ничего сложного в этой программе нет. При первом запуске вместе с главным окном открывается небольшой подсказчик – окошко «Быстрый старт» с тремя кнопками: «Выбор исходной папки», «Выбор метода поиска» и «Начало поиска». Поочередное нажатие этих кнопок открывает области, где нужно что-либо выбрать или настроить.
Пользователи, владеющие английским, могут осваивать AllDup по материалам встроенной справки, которая открывается в нужном месте при нажатии на значки вопроса.
Функции и возможности AllDup
- Большой перечень критериев поиска: имя, расширение, размер, содержимое, даты создания и изменения, атрибуты файла, жесткие ссылки, похожие изображения. Поиск по сочетанию нескольких критериев.
- Сканирование содержимого архивов (нахождение отдельных дубликатов в архивах).
- Настраиваемый способ сравнения имен объектов: по полному совпадению, по начальным символам в имени, по заданному количеству совпадающих символов и т. д.
- Нахождение файлов с похожими именами.
- Сравнение содержимого файлов.
- Гибкие параметры поиска с возможностью выбора дополнительных действий до и после него.
- Ведение лога сканирования.
- Гибко настраиваемый фильтр игнорирования объектов.
- Создание жесткой ссылки или ярлыка на сохраненный файл на месте удаленного дубликата.
- Отсутствие ограничений на количество сканируемых каталогов.
- Сканирование съемных носителей.
- Предварительный просмотр найденных файлов с отображением различных параметров – на случай затруднения при принятии решения об удалении.
- Составление удобных списков результатов поиска. Экспорт списков в формат txt или csv.
- Сохранение результата сканирования. Позволяет закрывать программу, не дожидаясь окончания проверки. И после повторного запуска начинать не сначала, а с момента остановки.
- Создание и сохранение нескольких профилей настроек.
AllDup отличается от многих конкурентов высокой скоростью сканирования и полнотой обнаружения дублированных объектов. Кстати, благодаря функции поиска похожих файлов программу можно использовать для нахождения каких-либо данных на жестком диске без цели удаления.
Еще одна приятная особенность утилиты AllDup – это полное отсутствие рекламы. Вместо нее на верхнюю панель вынесена кнопка «Поддержать автора» любой приемлемой суммой, если вы захотите этого сами.
DiskBoss
Нельзя полностью отнести к классу программ для удаления дубликатов файлов. Это лишь одна из функций, помимо которой утилита умеет очень и очень многое. DiskBoss – это оптимизатор дискового пространства и усовершенствованный файловый менеджер. Он предназначен для анализа использования накопителей и управления всеми объектами, которые на них находятся.
Функции и возможности DiskBoss
- Исследование дискового пространства с отображением на круговой диаграмме, сколько места занимают те или иные объекты.
- Классификация, сортировка и группировка файлов и папок по множеству критериев, в том числе по правилам пользователя.
- Поиск и очистка дубликатов на рабочих станциях и серверах под управлением Windows. Гибкие критерии поиска.
- Несколько вариантов действий с найденными дубликатами – удаление, архивация, перенос в другое место и т. д.
- Замещение удаленных дубликатов ссылками.
- Сохранение отчетов сканирования и очистки дисков от дублированных данных в форматах html, excel, pdf, txt, csv и xml.
- Отображение результата поиска дубликатов на круговых диаграммах.
- Создание и выполнение автоматических сценариев по очистке дисков.
- Улучшенная система поиска данных на диске.
- Синхронизация данных в указанных каталогах.
- Удаление групп файлов на основе заданных критериев.
- Мониторинг изменений в файловой системе дисков в реальном времени.
- Анализ целостности и прочие операции с файлами.
- Многое другое.
Утилита DiskBoss будет хорошим выбором для тех, кому нужна полная картина содержимого и состояния дисков, а также возможность управлять всеми данными на них, используя ручные и автоматические методы. Приложение выпускается в бесплатной и нескольких платных версиях, причем первая содержит почти всё, что есть в платной версии pro, кроме нескольких инструментов автоматизации.
Пожалуй, единственный недостаток DiskBoss – отсутствие русского языка, но ради такого богатства функций с этим можно смириться. В остальном утилита очень хороша, а для некоторых задач – незаменима.
Конечно, это далеко не исчерпывающий список программ, предназначенных для удаления идентичных копий файлов. Их десятки, а может, и сотни. Однако этого скромного набора достаточно, чтобы удовлетворить потребности 90% пользователей Windows. Надеюсь, что что-то из них пригодится и вам.
Поиск и удаление дубликатов файлов в Windows с помощью пятерки бесплатных программ обновлено: Август 4, 2018 автором: Johnny Mnemonic
При работе с таблицей или базой данных с большим количеством информации возможна ситуация, когда некоторые строки повторяются. Это ещё больше увеличивает массив данных. К тому же, при наличии дубликатов возможен некорректный подсчет результатов в формулах. Давайте разберемся, как в программе Microsoft Excel отыскать и удалить повторяющиеся строки.
Найти и удалить значения таблицы, которые дублируются, возможно разными способами. В каждом из этих вариантов поиск и ликвидация дубликатов – это звенья одного процесса.
Способ 1: простое удаление повторяющихся строк
Проще всего удалить дубликаты – это воспользоваться специальной кнопкой на ленте, предназначенной для этих целей.
Способ 2: удаление дубликатов в «умной таблице»
Дубликаты можно удалить из диапазона ячеек, создав умную таблицу.
Этот способ является наиболее универсальным и функциональным из всех описанных в данной статье.
Способ 3: применение сортировки
Данный способ является не совсем удалением дубликатов, так как сортировка только скрывает повторные записи в таблице.
После этого, повторяющиеся записи будут скрыты. Но их показ можно в любой момент включить повторным нажатием на кнопку «Фильтр» .
Способ 4: условное форматирование
Найти повторяющиеся ячейки можно также при помощи условного форматирования таблицы. Правда, удалять их придется другим инструментом.
После этого произойдет выделение ячеек с повторяющимися значениями. Эти ячейки вы потом при желании сможете удалить вручную стандартным способом.
Внимание! Поиск дублей с применением условного форматирования производится не по строке в целом, а по каждой ячейке в частности, поэтому не для всех случаев он является подходящим.
Способ 5: применение формулы
Кроме того, найти дубликаты можно применив формулу с использованием сразу нескольких функций. С её помощью можно производить поиск дубликатов по конкретному столбцу. Общий вид данной формулы будет выглядеть следующим образом:
ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;; адрес_столбца;)>1;0;1);0));"")
После этих действий в столбце «Дубликаты» отобразятся повторяющиеся значения.
Но, данный способ все-таки слишком сложен для большинства пользователей. К тому же, он предполагает только поиск дублей, но не их удаление. Поэтому рекомендуется применять более простые и функциональные решения, описанные ранее.
Как видим, в Экселе есть множество инструментов предназначенных для поиска и удаления дублей. У каждого из них есть свои особенности. Например, условное форматирование предполагает поиск дублей только по каждой ячейке в отдельности. К тому же, не все инструменты могут не только искать, но и удалять повторяющиеся значения. Наиболее универсальный вариант – это создание «умной таблицы». При использовании данного способа можно максимально точно и удобно настроить поиск дубликатов. К тому же, их удаление происходит моментально.