0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро найти частичные дубликаты в столбце Excel

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel

повторяющиеся значения excel лого

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

1. Удаление повторяющихся значений в Excel (2007+)

Рассмотрим таблицу с тремя столбцами с дублирующимися записями, которые необходимо удалить. Выберите раздел таблицы, в котором необходимо удалить дубликаты данных. Можно выбрать один или несколько столбцов или всю таблицу. Нажмите кнопку Удалить дубликаты в группе Работа с данными на вкладке Данные.

удалить-дубликаты excel

Если каждый столбец таблицы имеет заголовок, установите маркер Мои данные содержат заголовки. Также установите маркеры рядом со столбцами, в которых вы хотите искать дубликаты.

удалить-дубликаты excel

Нажмите OK, диалоговое окно закроется, и строки, содержащие дубликаты, будут удалены.

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

2. Использование расширенного фильтра для удаления дубликатов

Выберите любую ячейку таблицы, перейдите на вкладку Данные в группе Сортировка и фильтр, а затем выберите Дополнительно.

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

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

Читайте так же:
Как в Excel показывать только положительные числа?

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

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

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

3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

повторяющиеся значения excel

В появившемся диалоговом окне Repeat Values выберите формат, в котором вы хотите получить дубликаты. По умолчанию я установил ярко-красный цвет заливки и темно-красный цвет текста. Примечание: В этом случае Excel сравнивает на уникальность не всю строку, а только ячейки столбца, поэтому если дублирующиеся значения находятся только в одном столбце, Excel отформатирует и их. В примере видно, как Excel наводнил некоторые ячейки третьего столбца именами, несмотря на то, что вся строка данной ячейки таблицы уникальна.

повторяющиеся значения excel

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

Используя уже известную нам таблицу с тремя столбцами, добавьте четвертый столбец, который называется Count, и заполните его единицами (1). Выделите всю таблицу, перейдите на вкладку Вставка в группе Таблицы, затем нажмите кнопку Сводная таблица.

сводная таблица excel

Создайте сводную таблицу. Поместите первые три столбца в поле Rows name, а столбец счетчика — в поле Values. В созданной перекрестной таблице записи со значением больше единицы будут дубликатами, само значение будет означать количество повторяющихся значений. Для наглядности можно отсортировать таблицу по столбцу счетчика, чтобы сгруппировать дубликаты.

3 способа как находить дубликаты в Excel

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

Читайте так же:
Как вернуть значение, если данное значение существует в определенном диапазоне в Excel?

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

Поиск дубликатов при помощи встроенных фильтров Excel

После организации данных в список к ним можно применять различные фильтры. В зависимости от имеющегося набора данных можно отфильтровать список по одному или нескольким столбцам. Поскольку я использую Office 2010, я просто выделяю верхнюю строку, где находятся заголовки, затем перехожу на вкладку Данные и нажимаю команду Фильтр. Рядом с каждым из заголовков появятся треугольные стрелки, направленные вниз (значки падения), как показано на рисунке ниже.

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

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

Поиск дубликатов в Excel

В результате фильтра Excel показывает только строки, содержащие элементы, которые я выбрал (т.е. людей с установленными на компьютере XP и XP Pro). Вы можете выбрать любую другую комбинацию данных и даже настроить фильтры сразу для нескольких столбцов.

Читайте так же:
Как автоматически вводить дату при вводе данных в столбец?

Поиск дубликатов в Excel

Расширенный фильтр для поиска дубликатов в Excel

На вкладке Данные, справа от команды Фильтр, есть кнопка для настройки фильтра: Продвинутый. Этот инструмент немного сложнее в использовании и требует некоторых настроек перед применением. Ваши данные должны быть организованы так, как описано выше, т.е. в виде базы данных.

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

Поиск дубликатов в Excel

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

Поиск дубликатов в Excel

На скриншоте ниже Excel выделил всю таблицу и ждет, пока мы укажем диапазон с вашими критериями. Выберите «Диапазон критериев» в диалоговом окне, затем щелкните и выделите мышью ячейки L1 и L2 (или любое другое место в таблице, где находится ваш критерий). Строки таблицы будут отображаться только в том случае, если в их столбце «Дом / Посетитель» есть значение H, остальные строки будут скрыты. Мы также обнаружили дублирование данных (по одному столбцу), показывая только домашние встречи:

Поиск дубликатов в Excel

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

Читайте так же:
Как вернуть первую / последнюю непустую ячейку в строке или столбце?

Встроенный инструмент для удаления дубликатов в Excel

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

Поиск дубликатов в Excel

Не забудьте выбрать столбец, в котором должны остаться только уникальные значения. Если данные не содержат заголовков, в диалоговом окне будут отображены столбец A, столбец B (столбец A, столбец B) и т.д.

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

Поиск дубликатов в Excel

Поиск дубликатов при помощи команды Найти

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

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

Кнопка Найти все может быть более полезной для поиска всех имеющихся у вас данных.

Поиск дубликатов в Excel

В заключение

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

  • Фильтр — идеально подходит, когда данные содержат несколько категорий, которые необходимо разделить, обобщить или удалить. Создание подразделов — лучшее использование расширенного фильтра.
  • Удаление дубликатов помогает минимизировать объем данных. Я использую это, когда мне нужно перечислить все уникальные значения в одном из столбцов, которые я затем использую для вертикального поиска с помощью функции FFT.
  • Я использую команду Найти только тогда, когда мне нужно найти небольшое количество значений, а инструмент Найти и заменить — когда я нахожу ошибки и хочу исправить их все сразу.
Читайте так же:
Как воспроизвести видеофайл с листа Excel?

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector