Какой инструмент позволяет выделить дубликаты значений
Перейти к содержимому

Какой инструмент позволяет выделить дубликаты значений

  • автор:

Фильтр уникальных значений или удаление повторяющихся значений

В Excel существует несколько способов фильтрации уникальных значений или удаления повторяющихся значений:

Фильтр

    Чтобы отфильтровать уникальные значения, щелкните Данные >Сортировка & Фильтр >Дополнительно.

Удаление дубликатов

Чтобы удалить повторяющиеся значения, щелкните Data > Data Tools>Remove Duplicates (Удалить дубликаты).

Сведения о фильтрации уникальных значений или удалении повторяющихся значений

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

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

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

Фильтрация уникальных значений

  1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
  2. Щелкните Данные > Дополнительно (в группе Фильтр & сортировки ).

Важно: Вставьте текст оповещения здесь.

Фильтр

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

  • Щелкните Фильтровать список на месте.

Чтобы скопировать результаты фильтра в другое расположение, выполните следующие действия:

Кнопка

  • Установите переключатель скопировать результат в другое место.
  • В поле Копировать введите ссылку на ячейку.
  • Кроме того, нажмите кнопку Свернуть диалоговое окно

Разверните

, чтобы временно скрыть всплывающее окно, выберите ячейку на листе и нажмите кнопку Развернуть

Уникальные значения из диапазона будут скопированы в новое расположение.

Удаление повторяющихся значений

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

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

Удаление двойников

  1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
  2. На вкладке Данные щелкните Удалить дубликаты (в группе Инструменты для работы с данными ).

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

Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. Если в этих столбцах найден дубликат, будет удалена вся строка, включая другие столбцы в таблице или диапазоне.

Проблемы при удалении дубликатов из контурных или промежуточных данных

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

Условное форматирование уникальных или повторяющихся значений

Примечание: Поля в области «Значения» отчета сводной таблицы нельзя условно форматировать по уникальным или повторяющимся значениям.

Быстрое форматирование

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

Важно: Вставьте текст оповещения здесь.

Быстрое форматирование

Расширенное форматирование

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

Важно: Вставьте текст оповещения здесь.

Управление правилами

Кнопка

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

    Разверните

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

    В Excel в Интернете можно удалить повторяющиеся значения.

    Удаление повторяющихся значений

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

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

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

    Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. При обнаружении дубликата в столбцах Column1 и Column2 будет удалена вся строка, включая данные из Столбца 3.

    Примечание: Если вы хотите вернуть данные, просто нажмите кнопку Отменить (или нажмите клавиши CTRL+Z на клавиатуре).

    Дополнительные сведения

    Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

    Макрос для выделения дубликатов разными цветами

    Выделение разными цветами заливки повторяющихся значений (дубликатов)

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

    Но иногда требуется, чтобы различные повторяющиеся значения были выделены РАЗНЫМИ ЦВЕТАМИ.
    В этом случае, без макросов не обойтись.

    Ниже приведён макрос, который как раз и решает эту задачу
    (достаточно выделить диапазон ячеек, запустить макрос, — и повторяющиеся непустые ячейки получат одинаковый цвет заливки)

    Sub ВыделитьДубликатыРазнымиЦветами() On Error Resume Next ' массив цветов, используемых для заливки ячеек-дубликатов Colors = Array(12900829, 15849925, 14408946, 14610923, 15986394, 14281213, 14277081, _ 9944516, 14994616, 12040422, 12379352, 15921906, 14336204, 15261367, 14281213) Dim coll As New Collection, dupes As New Collection, _ cols As New Collection, ra As Range, cell As Range, n& Err.Clear: Set ra = Intersect(Selection, ActiveSheet.UsedRange) If Err Then Exit Sub ra.Interior.ColorIndex = xlColorIndexNone: Application.ScreenUpdating = False For Each cell In ra.Cells ' запонимаем значение дубликатов в коллекции dupes Err.Clear: If Len(Trim(cell)) Then coll.Add CStr(cell.Value), CStr(cell.Value) If Err Then dupes.Add CStr(cell.Value), CStr(cell.Value) Next cell For i& = 1 To dupes.Count ' заполняем коллекцию cols цветами для разных дубликатов n = n Mod (UBound(Colors) + 1): cols.Add Colors(n), dupes(i): n = n + 1 Next For Each cell In ra.Cells ' окрашиваем ячейки, если для её значения назначен цвет cell.Interior.color = cols(CStr(cell.Value)) Next cell Application.ScreenUpdating = True End Sub
    • 84025 просмотров

    Удаление одинаковых значений в Google Таблицах

    Удаление одинаковых значений в Google Таблицах

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

    Применение функции очистки данных

    Разработчики Google Таблиц добавили интересную функцию, которая позволяет без лишних формул и фильтров очистить повторы в выделенном диапазоне ячеек. Это самый простой и быстрый метод, поэтому хотелось бы остановиться на нем в первую очередь.

    1. С зажатой левой кнопкой мыши выделите все ячейки, среди которых хотите найти повторы. Затем на верхней панели разверните меню «Данные». Выбор ячеек для удаления повторов в Google Таблицах
    2. В списке наведите курсор на «Очистка данных» и выберите пункт «Удалить повторы». Выбор функции удаления повторов в Google Таблицах
    3. В появившемся окне не нужно добавлять никаких изменений, поскольку вы изначально уже выделили необходимые для обработки ячейки. Просто нажмите кнопку «Удалить повторы». Настройка удаления повторов в Google Таблицах
    4. Уже через секунду вы увидите информацию о том, сколько повторяющихся строк найдено и сколько уникальных осталось. Можете вернуться к таблице и самостоятельно убедиться в успешности выполненной задачи. Успешное удаление повторов в Google Таблицах

    Комьюнити теперь в Телеграм
    Подпишитесь и будьте в курсе последних IT-новостей

    Использование специальной функции

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

    1. Вам достаточно будет выбрать пустой столбец, выделить в нем ячейку и ввести формулу =UNIQUE(A1:A10), где A1:A10 – диапазон ячеек со значениями для проверки. Создание функции для удаления повторов в Google Таблицах
    2. После нажатия по клавише Enter вы увидите, что формула уже вступила в силу и в каждой следующей строке столбца выведены исключительно уникальные значения из выбранного диапазона. Применение функции удаления повторов в Google Таблицах
    3. Старый столбец больше не нужен, поэтому можно щелкнуть по нему правой кнопкой мыши и из появившегося контекстного меню выбрать пункт «Удалить строки». Удаление оригинальных ячеек после поиска повторов в Google Таблицах

    Создание фильтра

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

    1. Выделите диапазон с ячейками, нажмите по нему правой кнопкой мыши и выберите пункт «Создать фильтр». Вызов инструмента фильтрации в Google Таблицах
    2. Нажмите по появившемуся значку фильтра и выберите подходящий для вас вариант сортировки. Настройка фильтрации для удаления повторов в Google Таблицах
    3. Вы увидите, как одинаковые значения теперь идут по порядку. Вам остается только найти ненужные и удалить их. Удаление повторов после фильтрации в Google Таблицах

    Установка дополнения

    Завершающий метод самый долгий по выполнению, поскольку требует некоторое время на подготовку. Он заключается в установке и применении специального дополнения для Google Таблиц, функциональность которого и строится вокруг уникальных и повторяющихся ячеек. Давайте разберемся с этим более детально.

    1. На верхней панели разверните меню «Расширения», наведите курсор на «Дополнения» и выберите пункт «Установить дополнения». Переход к поиску дополнений в Google Таблицах
    2. Через поиск в новом окне найдите дополнение с названием «Remove Duplicates». Поиск дополнения для удаления повторов в Google Таблицах
    3. На его странице нажмите кнопку «Установить» и подтвердите запуск инсталляции. Установка дополнения для удаления повторов в Google Таблицах
    4. Теперь дополнение запускается через то же меню «Расширения», а в его меню вам нужно выбрать метод работы. Я остановлюсь на первом, который подразумевает поиск одинаковых строк. Запуск дополнения для удаления повторов в Google Таблицах
    5. Выделите заранее диапазон или выберите его в окне дополнения. Выбор диапазона в дополнении для удаления повторов в Google Таблицах
    6. В следующем шаге уточните, хотите ли вы найти дубликаты или только уникальные значения. Выбор типа поиска для удаления повторов в Google Таблицах
    7. Задайте дополнительные параметры, если нужно игнорировать заголовок или пустые строки. Выбор дополнительных опций поиска повторов в Google Таблицах
    8. Далее выберите действие для дубликатов или уникальных ячеек. Их можно выделить цветом, переместить, удалить значения или удалить саму строку. Выбор действий для повторов в Google Таблицах
    9. Я выбрал вариант выделения цветом, чтобы найти все повторы. Далее их можно без проблем удалить или переместить, что уже зависит от требований пользователя. Выделение повторов цветом при использовании дополнения в Google Таблицах

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

    Читайте также в Комьюнити:

    • Поворот текста в Google Таблицах
    • Создание таблицы посещаемости в Google Таблицах
    • Как убрать сетку в Google Таблицах

    Exceltip

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

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

    Опубликовано 10.01.2014 Автор Ренат Лотфуллин

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

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

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

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

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

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

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

    Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Вам также могут быть интересны следующие статьи

    • Как отфильтровать список элементов из длинного-длинного списка
    • Группировка в сводных таблицах
    • Применение нескольких фильтров в сводной таблице
    • Работаем со срезами сводных таблиц
    • Checkbox в Google Таблицах и фишки их применения
    • Создание списка уникальных значений в Excel с помощью формул массивов
    • Расширенный фильтр в Excel с символами подстановки и уникальными значениями на VBA
    • Создание базы данных в Excel
    • Применение ролловер эффекта в отчетах Excel
    • Импорт данных с Excel книги, находящейся на веб-сайте
  • Добавить комментарий

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