В Excel есть несколько способов фильтрации уникальных значений, а также удаления повторяющихся значений.
Сведения о фильтрации уникальных значений и удалении повторяющихся значений
Фильтрация уникальных значений и - это две похожие задачи, так как целью является предоставление списка уникальных значений. Тем не менее, при фильтрации уникальных значений вы должны временно скрыть повторяющиеся значения. Однако удаление повторяющихся значений означает безвозвратное удаление повторяющихся значений.
Повторяющееся значение - это одно из значений, для которого все значения по крайней мере одной строки идентичны всем значениям в другой строке. Сравнение повторяющихся значений зависит от того, что отображается в ячейке, а не на основе значения, хранящегося в ячейке. Например, если у вас есть одинаковое значение даты в разных ячейках, например "3/8/2006", а другое - "Мар 8, 2006", значения будут уникальными.
Проверка перед удалением дубликатов: Прежде чем удалять повторяющиеся значения, рекомендуется сначала попытаться отфильтровать (или условно отформатировать по - уникальные значения), чтобы подтвердить достижение ожидаемых результатов.
Фильтрация уникальных значений
Чтобы отфильтровать диапазон ячеек или таблицы по месту, выполните указанные ниже действия.
Нажмите кнопку Фильтровать список на месте .
Чтобы скопировать результаты фильтра в другое место, выполните указанные ниже действия.
Уникальные значения из диапазона будут скопированы в новое место.
Удаление повторяющихся значений
Так как вы удаляете данные безвозвратно, рекомендуется скопировать исходный диапазон ячеек или таблицы на другой лист или в другую книгу, прежде чем удалять повторяющиеся значения.
Выполните указанные ниже действия.
Проблемы, возникающие при удалении дубликатов из структурированных или промежуточных данных
Невозможно удалить повторяющиеся значения из структурированных данных структуры или с промежуточными итогами. Для удаления дубликатов необходимо удалить структуру и промежуточные итоги. Дополнительные сведения можно найти в разделе Структурирование списка данных на листе и Удаление промежуточных итогов .
Условное форматирование уникальных и повторяющихся значений
Примечание: Вы не можете условно форматировать поля в области значений отчета сводной таблицы с помощью уникальных или повторяющихся значений.
Быстрое форматирование
Выполните указанные ниже действия.
Расширенное форматирование
Выполните указанные ниже действия.
В Excel в Интернете вы можете удалить повторяющиеся значения.
Удаление повторяющихся значений
При удалении повторяющихся значений единственным эффектом будут значения в диапазоне ячеек или таблице. Другие значения, находящиеся за пределами диапазона ячеек или таблицы, не изменяются и не перемещаются. При удалении дубликатов сохраняется первое вхождение значения в списке, а также удаляются другие идентичные значения.
Важно: Вы всегда можете нажать кнопку отменить , чтобы вернуть данные после удаления дубликатов. С другой стороны, перед удалением повторяющихся значений рекомендуется скопировать исходный диапазон ячеек или таблицы на лист или в другую книгу.
Выполните указанные ниже действия.
Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
На вкладке данные нажмите кнопку Удалить дубликаты .
В диалоговом окне снимите флажок для столбцов, в которых не нужно удалять повторяющиеся значения.
Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не будут выделяться все столбцы. Например, если выбрать Столбец1 и Столбец2 (но не Столбец3), то "Key", использованный для поиска дубликатов, - это значение обоих Столбец1 & Столбец2. При обнаружении дубликата в Столбец1 и Столбец2 вся строка будет удалена, включая данные из Столбец3.
Наверняка у любого из нас на диске со временем скапливаются файлы-дубликаты. Файлы в «Загрузках», которые вы скачали несколько раз, одинаковые фотографии и музыкальные композиции, лежащие в таких недрах, что до них просто не доходят руки. Избавиться от всего этого можно и вручную, но куда быстрее за вас сработают специальные утилиты, ищущие одинаковые файлы.
Очень популярный «чистильщик», который, наверное, установлен у каждого. Да, он не только искать системный мусор и очищать историю и cookies браузеров, но и убирать дубликаты файлов.
Платформы: Windows, Mac.
Цена: бесплатно, 24,95 доллара за расширенную версию.
Программа ищет файлы как с одинаковыми или похожими названиями, так и с идентичным содержимым. Хорошо работает с музыкой и может отыскивать одинаковые музыкальные файлы, даже если у них разные теги. Кроме того, dupeGuru умеет сравнивать изображения, чтобы найти не только одинаковые, но и просто похожие фотографии.
Разрабатывается для Mac и Linux. Версия для Windows больше не поддерживается разработчиком, но её можно загрузить с официального сайта - она полностью работоспособна.
Платформы: Windows, Mac, Linux.
Продвинутое приложение для поиска файлов, которое, помимо прочего, может удалять дубликаты. SearchMyFiles обладает гибкими фильтрами, так что вы можете настроить поисковую выдачу так, как вам угодно.
Платформы: Windows.
Популярное приложение для Mac, которое ищет одинаковые или похожие файлы и показывает, в чём разница между ними. Копии в «Фото», в фонотеке iTunes - мимо Gemini 2 не пройдёт ничего. Разработчиками заявлен умный механизм поиска дубликатов, который запоминает, какие файлы вы оставляете, а что решаете удалить.
Платформы: Mac.
Хотя AllDup бесплатна, она умеет немало. Ищет, удаляет, копирует и перемещает дубликаты файлов, в том числе одинаковые аудиозаписи с разными тегами. Присутствует гибкая настройка поиска. С помощью встроенного просмотрщика можно изучить файлы и выбрать, что именно удалять.
Платформы: Windows.
Duplicate File Finder быстро и эффективно отыскивает файлы-копии. Предоставляет интересную возможность поиска дубликатов не только на жёстком диске, но и в локальной сети. Может работать с изображениями и музыкой, сравнивая как теги, так и содержимое. Функция предпросмотра поможет разобраться, что действительно удалить, а что оставить. К сожалению, в бесплатной версии довольно много опций недоступно.
Платформы: Windows.
Цена: бесплатно, 29,95 доллара за расширенную версию.
Универсальный файловый менеджер, который может сделать с вашими файлами что угодно. В том числе и найти файлы-дубликаты. Включить опцию поиска копий можно на вкладке с параметрами поиска, там же, где указываются другие атрибуты искомых файлов.
Платформы: Windows.
DupeGuru выглядит наиболее привлекательным вариантом. Он бесплатен, при этом предлагает все необходимые функции для избавления вашего диска от скопившегося барахла. Огорчает лишь тот факт, что разработка версии для Windows остановлена. Пользователям Windows, не желающим платить за коммерческие альтернативы, больше подойдёт AllDup. А CCleaner и Total Commander - это более универсальные и распространённые решения, которые, наверное, уже установлены у каждого.
Дубликаты данных в Excel могут приводить к множеству проблем при работе с данными. Не важно, импортируете ли вы данные из какой-либо базы данных, получаете их от коллеги или друзей. Чем больше данных в вашем файле, тем сложней найти и удалить дубликаты в Excel.
В этой статье мы подробно рассмотрим эффективные практики по поиску и удалению дубликатов.
Поиск и выделение дубликатов цветом в Excel
Дубликаты в таблицах могу встречаться в разных формах. Это могут быть повторяющиеся значения в одной колонке и в нескольких, а также в одной или нескольких строках.
Поиск и выделение дубликатов цветом в одном столбце в Эксель
Самый простой способ найти и выделить цветом дубликаты в Excel, это использовать условное форматирование.
Как это сделать:
- Выделим область с данными, в которой нам нужно найти дубликаты:
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”:
- Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены дублирующие значения. Нажмите кнопку “ОК”:
- После этого, в выделенной колонке, будут подсвечены цветом дубликаты:
Подсказка : не забудьте проверить данные вашей таблицы на наличие лишних пробелов. Для этого лучше использовать функцию TRIM (СЖПРОБЕЛЫ).
Поиск и выделение дубликатов цветом в нескольких столбцах в Эксель
Если вам нужно вычислить дубликаты в нескольких столбцах, то процесс по их вычислению такой же как в описанном выше примере. Единственное отличие, что для этого вам нужно выделить уже не одну колонку, а несколько:
- Выделите колонки с данными, в которых нужно найти дубликаты;
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены цветом дубликаты:
Поиск и выделение цветом дубликатов строк в Excel
Поиск дубликатов повторяющихся ячеек и целых строк с данными это разные понятия. Обратите внимание на две таблицы ниже:
В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали дубликаты ячеек, а справа мы нашли целые повторяющие строчки с данными.
Рассмотрим как найти дубликаты строк:
- Справа от таблицы с данными создадим вспомогательный столбец, в котором напротив каждой строки с данными проставим формулу, объединяющую все значения строки таблицы в одну ячейку:
=A2&B2&C2&D2
Во вспомогательной колонке вы увидите объединенные данные таблицы:
Теперь, для определения повторяющихся строк в таблице сделайте следующие шаги:
- Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены дублирующиеся строки:
На примере выше, мы выделили строки в созданной вспомогательной колонке.
Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?
Для этого давайте сделаем следующее:
- Также как и в примере выше создадим вспомогательный столбец, в каждой строке которого проставим следующую формулу:
=A2&B2&C2&D2
Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:
- Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
- Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:
- В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:
=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1
- Не забудьте задать формат найденных дублированных строк.
Эта формула проверяет диапазон данных во вспомогательной колонке и при наличии повторяющихся строк выделяет их цветом в таблице:
Как убрать дубликаты в Excel
Выше мы изучили как найти дубликаты и как их выделить цветом. Ниже вы узнаете как их удалить.
Как удалить дубликаты в одном столбце Эксель
Если ваши данные размещены в одном столбце и вы хотите удалить все дубликаты, то проделайте следующие действия:
- Выделите данные;
- Перейдите на Панели инструментов во вкладку “Данные” – > раздел “Работа с данными” -> “Удалить дубликаты”:
- В диалоговом окне “Удалить дубликаты” поставьте флажок напротив пункта “Мои данные содержат заголовки”, если в выделенном вами диапазоне столбца есть заголовок. Также, убедитесь, что в меню “Колонны” выделен необходимый вам столбец:
- Нажмите “ОК”
После этого система удалит все дубликаты в столбце, оставив только уникальные значения.
Совет. Обязательно делайте резервную копию ваших данных перед любыми операциями с удалением дубликатов. Можно также проводить удаление дубликатов на отдельных листах, во избежание случайного удаления данных.
Как удалить дубликаты в нескольких столбцах в Excel
Представим, что у нас есть данные продаж как в таблице ниже:
Желтым цветом выделены строки, в которых имена, регион и сумма продаж одинаковы, но даты разные. Скорее всего, это связано с ошибкой ввода дынных в таблице. Если нам требуется удалить дубликаты данных таблицы в отдельных колонках, то проделайте следующие действия:
- Выделите данные таблицы;
- Перейдите на Панели инструментов во вкладку “Данные” – > раздел “Работа с данными” -> “Удалить дубликаты”:
- В диалоговом окне “Удалить дубликаты” поставьте флажок напротив пункта “Мои данные содержат заголовки” если в выделенном вами диапазоне есть заголовки. Для того, чтобы удалить дубликаты во всех столбцах кроме даты, оставьте поле с датой пустым:
- Нажмите “ОК”
После этого система удалит строки с данными, в которых дублируются значения в колонках “Имя”, “Регион”, “Продажи”.
Как удалить дублирующие строки с данными в Эксель
Если вам нужно удалить целые строки дублирующиеся в таблице, проделайте следующие шаги:
- Выделите данные таблицы;
- Перейдите на Панели инструментов во вкладку “Данные” – > раздел “Работа с данными” -> “Удалить дубликаты”:
- В диалоговом окне “Удалить дубликаты” поставьте флажок напротив пункта “Мои данные содержат заголовки” если в выделенном вами диапазоне есть заголовки. Для того чтобы система сравнила внутри таблицы строки, важно убедиться что отмечены все столбцы в диалоговом окне:
- Нажмите “ОК”
Используйте вышеупомянутые методы для очистки данных и избавления от дубликатов.
В работе с данными периодически возникает необходимость извлечения уникальных записей. Для этого пользователю Excel доступно множество способов удаления повторяющихся значений и фильтрации с возможностью скрытия или отображения. Каждый метод можно применять в повседневной работе или при обработке сложных статистических данных. При этом специальных знаний не требуется, достаточно минимальных знаний табличного процессора Excel.
Как удалить дубликаты в Excel
Для быстрого решения этой задачи пользователю доступна стандартная функция «Удаление дубликатов ». В этом случае операцию можно провести только по выбранным полям.
Для того чтобы удалит дубликаты строк, необходимо:
Удаление дубликатов в умной таблице
В этом методе используется конструктор Excel (расширенный режим с дополнительными возможностями). Конструктор активируется при выделении любой ячейки табличной области. Как и в первом случае, после применения производится удаление дублированных значений.
Для тех, кто не знает: умная таблица – это вид форматирования, после использования которого все ячейки таблицы принимаю определенные свойства. При этом Excel рассматривает подобную таблицу, как единое целое, а не как набор ячеек и элементов.
Чтобы удалить повторяющиеся строки в такой таблице, следует:
Используем фильтрацию
С помощью фильтрации, можно скрыть дублированные данные, при этом значения не удаляются — их можно вернуть в любой момент.
Чтобы провести форматирование и найти повторяющиеся значения таким образом следует:
Условное форматирование
Этим способом можно воспользоваться для поиска одинаковых записей в ячейках. Для удобства можно настроить цвет выделения. Для реализации нужно применить предустановленное правило выделения — «Повторяющиеся значения… ».
Чтобы найти дубликаты в Excel, необходимо:
Использование формулы
С помощью встроенной функции «ПОИСКПОЗ » можно найти повторяющиеся элементы в настраиваемом диапазоне. «ПОИСКПОЗ» — возвращает относительную позицию в массиве элемента, соответствующего указанному значению с учетом указанного порядка.
Чтобы выделить повторяющиеся строки в Excel необходимо:
Пояснение: В формуле, задается функция «ПОИСКПОЗ» которая ищет повторы по столбцу «B», начиная с ячейки «B3».
Копирование уникальных строк в новое расположение
Для выполнения, потребуется:
Используем сводные таблицы
Для группировки дублированных элементов можно использовать сводную таблицу (более простое понятие — обобщенная).
Для работы этого способа потребуется:
Кроме этого можно ознакомиться с видео по удалению дублей в Excel.
Удаление дубликатов в Google таблицах
В Google доступен только поиск уникальных записей в ячейках (методов удаления дублей нет):
- используя сводные таблицы (подсчет дублированных значений);
- с помощью функции unique (вывод результата из массива);
- используя сторонние расширения ;
Пример использования функции «unique »:
Для вывода уникальных записей, требуется применить формулу «=
unique(диапазон проверки)
»:
В этой инструкции о нескольких бесплатных и простых способах найти дубликаты файлов на компьютере в Windows 10, 8 или 7 и удалить их при необходимости. Прежде всего речь пойдет о программах, которые позволяют выполнить поиск дубликатов файлов, но, если вас интересуют способы интереснее, в инструкции затронута и тема их поиска и удаления с помощью Windows PowerShell.
Для чего это может потребоваться? Почти у любого пользователя, который достаточно продолжительное время сохраняет архивы фотографий, видео, музыки и документов к себе на диски (не важно, внутренние или внешние хранилища), с огромной вероятностью «образуются» дубликаты одних и тех же файлов, занимающие лишнее место на HDD, SSD или другом накопителе.
Программа DupeGuru - еще одна отличная бесплатная программа для поиска дубликатов файлов на русском языке. К сожалению, разработчики с недавних пор перестали обновлять версию для Windows (но обновляют DupeGuru для MacOS и Ubuntu Linux), однако имеющаяся на официальном сайте http://hardcoded.net/dupeguru версия для Windows 7 (внизу страницы) прекрасно работает и в Windows 10.
Все что потребуется для использования программы - добавить папки для поиска дубликатов в список и запустить сканирование. По его завершении вы увидите список найденных дубликатов файлов, их расположение, размеры и «процент», на сколько этот файл совпадает с каким-либо другим файлом (по любым из этих значений можно отсортировать список).
Например, в моем случае одна из тестировавшихся недавно программ, как оказалось, скопировала свой файлы установки в папку Windows и оставила там (1, 2), забрав у меня драгоценные 200 с лишним Мб, этот же файл остался и в папке загрузок.
Как видно на скриншоте, отметка для выбора файлов есть только у одного из найденных образцов (и только его можно удалить) - при этом в моем случае логичнее удаление не из папки Windows (там, в теории, файл может быть нужен), а из папки загрузок. Если выбор нужно изменить, отметьте файлы, которые не нужно удалять и затем, в меню правого клика мыши - «Сделать выбранные эталоном», тогда отметка для выбора исчезнет у текущих файлов и появится у их дубликатов.
Думаю, с настройками и остальными пунктами меню DupeGuru у вас не составит труда разобраться: все они на русском и достаточно понятны. А сама программа ищет дубликаты быстро и надёжно (главное не удаляйте какие-либо системные файлы).
Duplicate Cleaner Free
Программа для поиска дубликатов файлов на компьютере Duplicate Cleaner Free - еще одно скорее хорошее, чем плохое решение, особенно для начинающих пользователей (на мой взгляд, этот вариант проще). Несмотря на то что сравнительно ненавязчиво предлагает приобрести Pro версию и ограничивает некоторые функции, в частности поиск только одинаковых фотографий и изображений (но при этом доступны фильтры по расширениям, что позволяет также искать только картинки, возможен поиск только одинаковой музыки).
Также, как и предыдущие программы, Duplicate Cleaner имеет русский язык интерфейса, но некоторые элементы, судя по всему, были переведены с использованием машинного перевода. Тем не менее, почти всё будет понятно и, как было упомянуто выше, работа с программой скорее всего будет очень простой для начинающего пользователя, которому потребовалось найти и удалить одинаковые файлы на компьютере.
Скачать бесплатно Duplicate Cleaner Free вы можете с официального сайта https://www.digitalvolcano.co.uk/dcdownloads.html
Как найти дубликаты файлов с помощью Windows PowerShell
При желании, можно обойтись без сторонних программ для поиска и удаления дубликатов файлов. Недавно я писал о том, в PowerShell и эту же функцию можно использовать для поиска одинаковых файлов на дисках или в папках.
При этом, можно найти множество различных реализаций скриптов Windows PowerShell, позволяющих найти дубликаты файлов, вот некоторые варианты (сам я не специалист по написанию таких программ):
Ниже на скриншоте - пример использования слегка модифицированного (чтобы он не удалял дубликаты файлов, а отображал их список) первого скрипта в папке изображений (где лежат две одинаковые картинки - те же, что нашла программа AllDup).
Если для вас создание скриптов PowerShell - привычное дело, то, думаю, в приведенных примерах вы сможете найти полезные подходы, которые помогут реализовать поиск дубликатов файлов необходимым именно вам способом или даже автоматизировать процесс.
Дополнительная информация
Помимо приведенных программ поиска дубликатов файлов, есть много других утилит такого рода, многие из них не бесплатны или ограничивают функции до регистрации. Также при написании этого обзора попались программы-пустышки (которые делают вид, что ищут дубликаты, а по факту лишь предлагают установить или купить «основной» продукт) от довольно-таки именитых разработчиков, которые у всех на слуху.
На мой взгляд, свободно распространяемые утилиты для поиска дубликатов, особенно первые две из данного обзора, более чем достаточны для любых действий по поиску одинаковых файлов, в том числе музыки, фотографий и картинок, документов.
Если же приведенные варианты вам показались не достаточными, при загрузке других найденных вами программ (да и перечисленных мною - тоже) будьте внимательны при установке (во избежание установки потенциально нежелательного ПО), а еще лучше - .