Check-moscow.ru

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

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

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

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

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

Выделите дубликаты или уникальные значения

Одним из удобных способов использования условного форматирования является быстрое выделение дублирующихся или уникальных значений. Благодаря встроенным правилам Excel облегчает обе задачи.

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

 165 кодов товаров с дубликатами

165 дублирующих кодов продуктов

Но с помощью условного форматирования можно просто выделить таблицу и заставить Excel выделить повторяющиеся значения с помощью встроенного правила условного форматирования:

Встроенное правило условного форматирования для пометки дубликатов

Встроенное условное правило формата для пометки дубликатов

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

Несколько щелчков мыши — и вот результат:

165 кодов товаров с четко выделенными дубликатами

165 товарных кодов с четко определенными дубликатами

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

Таблица имен

Многие имена… какие из них появляются только один раз?

Пытаетесь найти уникальные? С помощью встроенного правила условного форматирования вы можете найти все уникальные имена менее чем за 10 секунд:

Встроенное правило условного форматирования для пометки уникальных значений

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

Уникальные наименования четко обозначены

Читайте так же:
Как добавить ряд в диаграмму в Excel?

Трюки условного форматирования

Давайте усложним задачу, что если вы хотите найти все имена, которые встречаются не менее 5 раз? Давайте создадим формульное правило:

Формула для подсчета дубликатов

Формула для подсчета дубликатов,
которые появляются как минимум 5 раз

Вы можете легко отметить имена, которые встречаются как минимум 5 раз:

Имена, которые появляются как минимум 5 раз четко выделены

Имена, встречающиеся не менее 5 раз, четко обозначены.

Формула, которую я использую, с именованным диапазоном «имена»:

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

Для того, чтобы найти дубликаты в Excel также можно воспользоваться расширенным фильтром (работает начиная с версии 2003).
Вновь выделяем диапазон ячеек и в панели вкладок выбираем Данные ->Сортировка и фильтр ->Дополнительно:

Инструмент "Расширенный фильтр"

Выберите настройки фильтра, установите флажок Копировать результат в другое место и установите флажок Только уникальные записи.

Настройка параметров фильтра

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

Отформатированная таблица без дубликатов

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

Убедитесь, что активная ячейка находится в таблице, или выделите диапазон ячеек из таблицы.

Нажмите стрелку рядом с фильтром на вкладке Данные в разделе Сортировка и фильтрация и выберите Расширенный фильтр.

Выполните одно из следующих действий.

Необходимые меры

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

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

Копировать результаты фильтрации в другое место

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

Примечание: При копировании результатов фильтрации в другое место будут скопированы отдельные значения из выбранного диапазона. Исходные данные не будут изменены.

Установите флажок Только уникальные записи и нажмите OK.

Читайте так же:
Как добавить цвет в выпадающий список в Excel?

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

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

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

Создайте перекрестную таблицу. Столбцы с 1 по 3 находятся в поле Rows, а столбец со счетчиком — в Values. Записи перекрестной таблицы со значением больше единицы являются дубликатами; само значение — это количество раз, когда значение появлялось в перекрестной таблице. Для наглядности следует отсортировать таблицу по столбцу Счетчик, чтобы сгруппировать дубликаты.

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