Как выделить цветом повторяющиеся значения в Excel?
Как выделить повторяющиеся значения цветом в Excel?
Из этого туториала Вы узнаете, как просматривать дубликаты в Excel. Мы рассмотрим различные методы затенения повторяющихся ячеек, сплошных линий или последовательных повторов с использованием условного форматирования.
Ранее мы исследовали различные способы поиска дубликатов в Excel. Эти формулы, несомненно, очень полезны, но выделение повторяющихся записей определенным цветом может упростить анализ ваших данных.
Самый быстрый способ найти и выделить дубликаты в Excel - использовать условное форматирование. Самым большим преимуществом этого метода является то, что он не только показывает существующие совпадения, но и мгновенно обнаруживает и окрашивает новые повторы, когда вы вводите, редактируете или перезаписываете свои данные.
Позже в этом руководстве вы найдете несколько способов выделить повторяющиеся записи в зависимости от вашего конкретного бизнеса. Эти методы работают во всех версиях Excel 2016, Excel 2013, Excel 2010 и более ранних версиях.
Как подсветить дубликаты, используя встроенный инструмент (с 1-м появлением)
Во-первых, во всех версиях Excel есть шаблон для выделения повторяющихся ячеек. Чтобы использовать его в таблицах, выполните следующие действия:
- Выберите данные, которые хотите проверить. Это может быть столбец, строка или диапазон ячеек.
- На вкладке «Главная» выберите «Условное форматирование»> «Правила выделения ячеек»> «Повторяющиеся значения».
Откроется диалоговое окно со светло-красной заливкой и темно-красным текстом, выбранными по умолчанию. Чтобы применить формат по умолчанию, нажмите ОК.
Помимо красной заливки и форматирования текста, в раскрывающемся списке доступно множество других предопределенных форматов. Чтобы раскрасить повторяющиеся ячейки другим цветом, щелкните «Пользовательский формат» (последний элемент в раскрывающемся списке) и выберите нужный цвет заливки и / или шрифта.
Запрос. Чтобы обозначить уникальные значения цветом, выберите «Уникальный» в поле слева.
И вот что в итоге получилось:
Как видите, повторов было много. Применяя встроенное правило дублирования к двум или более столбцам, Excel не сравнивает содержащиеся в них значения, а просто выбирает все повторяющиеся экземпляры. Для работы со всей таблицей или для сравнения столбцов этот метод вряд ли подойдет вам. Но для поиска в столбце или строке этого достаточно.
При использовании этого инструмента помните о двух вещах:
- Это работает только для одиночных ячеек. Чтобы отмечать повторяющиеся строки, вам нужно создать свои собственные правила.
- Он рисует повторяющиеся клетки, включая их первые появления. Чтобы отметить все, кроме первого вхождения, создайте правило условного форматирования на основе формулы в этом руководстве для поиска дубликатов.
Как выделить повторы без 1-го вхождения
Чтобы отметить второе и все последующие повторяющиеся вхождения, выберите ячейки, которые нужно заполнить, и выполните следующие действия:
- Выберите «Условное форматирование»> «Новое правило»> «Использовать формулу», чтобы определить, какие ячейки нужно форматировать .
- В поле «Значения формата», где эта формула возвращает ИСТИНА, введите:
= СЧЁТЕСЛИ ($ B $ 2: $ B2; $ B2)> 1
Где B2 - верхняя ячейка выбранного диапазона.
- Нажмите кнопку «Форматировать» и выберите желаемый цвет заливки и / или шрифт.
- Наконец, нажмите ОК, чтобы сохранить и применить свое творение.
Если у вас нет большого опыта работы с условным форматированием Excel, вы найдете подробные инструкции о том, как создать правило на основе формулы в следующем руководстве: Как изменить цвет ячейки в зависимости от значения?
В результате повторяющиеся ячейки, за исключением первых экземпляров, будут выделены выбранным вами цветом.
Как показать третий, четвертый и все последующие дубликаты?
Чтобы увидеть повторяющиеся значения, начиная с n-го вхождения, действуйте, как в предыдущем примере. Единственное отличие состоит в том, что вы заменяете> 1 в конце формулы на необходимое число. Например:
Чтобы раскрасить третий и все последующие повторы, примените следующее:
= СЧЁТЕСЛИ ($ B $ 2: $ B2, $ B2)> = 3
Чтобы указать на четвертую и все последующие повторяющиеся записи, используйте выражение:
= СЧЁТЕСЛИ ($ B $ 2: $ B2, $ B2)> = 4
Чтобы выбрать только определенные вхождения, используйте оператор равенства (=).
Например, чтобы изменить фон только 2 экземпляров, вы воспользуетесь следующей формулой:
= СЧЁТЕСЛИ ($ B $ 2: $ B2, $ B2) = 2
Как выделить дубликаты в диапазоне
Если вы хотите проверить наличие повторяющихся данных в области данных, найдя все экземпляры одного и того же элемента, используйте одно из следующих решений.
Выделите дубликаты в таблице, включая 1-е вхождение.
Если первый экземпляр элемента, который появляется в наборе данных более одного раза, считается дубликатом, самый простой способ - использовать встроенное правило Excel для дубликатов, о котором мы говорили выше.
Или создайте собственное условное форматирование:
= СЧЁТЕСЛИ (диапазон; первая_ячейка)> 1
Например, чтобы закодировать совпадения в диапазоне A2: C8, сделайте следующее:
= СЧЁТЕСЛИ ($ A $ 2: $ C $ 8; A2)> 1
Обратите внимание на использование абсолютных ссылок на ячейки для диапазона ($ A $ 2: $ C $ 8) и относительных ссылок на ячейки для верхней ячейки (A2).
Выделите повторы в таблице без 1-го вхождения.
Решение для этого сценария намного сложнее. Неудивительно, что в Excel нет готового решения для этого :)
Чтобы выделить повторяющиеся значения в нескольких столбцах, игнорируя первое вхождение, вам нужно будет создать 2 правила со следующими формулами:
Правило 1. Относится к первому столбцу.
Здесь вы используете ту же формулу, которую мы использовали для выделения дубликатов без первого появления в столбце (подробные инструкции можно найти здесь).
В этом примере мы применяем условие для A2: A11:
= СЧЁТЕСЛИ ($ A $ 2: $ A2; A2)> 1
В результате повторяющиеся элементы без одного вхождения выделяются в крайнем левом столбце диапазона (в нашем случае таких элементов всего два):
Правило 2. Применимо ко всем следующим столбцам
Чтобы выделить дубликаты в оставшихся данных (B2: C11), используйте следующую формулу:
= СЧЁТЕСЛИ (A $ 2: $ A $ 11; B2) + СЧЁТЕСЛИ (B $ 2: B2; B2)> 1
В приведенной выше формуле первая функция СЧЁТЕСЛИ подсчитывает количество вхождений данного элемента в первом столбце, а вторая функция СЧЁТЕСЛИ делает то же самое для всех последующих. Затем вы складываете эти числа и проверяете, больше ли сумма единицы.
В результате будут найдены и выбраны все повторяющиеся элементы, кроме первого:
Выделение целых строк на основе повторяющихся значений в одной колонке.
Если ваша таблица содержит несколько столбцов, вы можете выделить целые строки на основе повторяющихся записей в одной из них.
Как вы уже знаете, встроенное правило дублирования Excel работает только на уровне ячейки. Но при использовании собственных формул нет проблем с растушевкой целых линий. Ключ состоит в том, чтобы выделить целые строки, а затем создать правило с одной из следующих формул:
- Чтобы выделить повторяющиеся строки, исключая первые вхождения:
= СЧЁТЕСЛИ ($ B $ 2: $ B2; $ B2)> 1
Выберите строки с одинаковыми названиями продуктов (столбец B)
- Чтобы выделить повторяющиеся строки, включая все вхождения:
= СЧЁТЕСЛИ ($ B $ 2: $ B $ 17, $ B2)> 1
Здесь B2 - первая ячейка, а B17 - последняя, которую вы хотите проверить на наличие дубликатов.
Как видите, разумное использование абсолютных и относительных ссылок имеет значение.
Как выделить дублирующиеся строки.
В предыдущем примере показано, как раскрашивать целые строки, когда повторяющиеся значения отображаются в определенном столбце. Но что, если вы хотите отображать строки с одинаковыми значениями в нескольких столбцах? Или как отличить от них точно такие же, у которых точно такие же значения?
Для этого используйте функцию СЧЁТЕСЛИ, которая позволяет сравнивать по разным критериям. Например, чтобы выделить строки с одинаковыми значениями в B и C, то есть найти заказы на один и тот же продукт от одного и того же клиента, используйте одну из следующих формул:
- Чтобы выделить совпадения, кроме первого :
= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2, $ B $ 2: $ B2, $ B2)> 1
Обратите внимание, что мы применяем форматирование ко всей таблице.
- Чтобы выделить все неуникальные:
= СЧЁТЕСЛИ ($ B $ 2: $ B $ 17; $ B2; $ C $ 2: $ C $ 17, $ C2)> 1
Как вы понимаете, приведенный выше пример предназначен только для демонстрационных целей. Когда вы выделяете повторяющиеся строки в реальных таблицах, вы, конечно, не ограничены сравнением значений всего в 2 столбцах. Функция СЧЁТЕСЛИ может обрабатывать до 127 пар диапазон / критерий.
Подсветка последовательных дубликатов ячеек.
Иногда может оказаться необходимым не выделять все дубликаты в таблице, а отображать только последовательные, то есть те, которые находятся рядом друг с другом.
Для этого выберите область данных (без заголовка) и создайте условное форматирование, используя одну из следующих формул:
- Чтобы выбрать два последовательных заказа от одного и того же менеджера:
= ИЛИ ($ G1 = $ G2; $ G2 = $ G3)
- Чтобы выделить второй заказ менеджера, если у него 2 заказа подряд:
= $ G1 = $ G2
Здесь мы показали выделение для последовательно повторяющихся текстов, но оно также будет работать для последовательно повторяющихся чисел и дат.
Если на вашем листе Excel могут быть пустые строки и вы не хотите, чтобы последовательные пустые ячейки выделялись цветом, внесите следующие улучшения в формулу:
- когда выбран только второй повтор
= И ($ G2 ""; $ G1 = $ G2)
- с обоими выбранными дублями:
= И ($ G2 "", ИЛИ ($ G1 = $ G2, $ G2 = $ G3))
Как видите, нет ничего сложного в выделении дубликатов цветом в Excel с использованием условного форматирования.
Как выделить дубликаты в Excel с помощью инструмента Duplicate Remover.
Эта надстройка включает множество опций для работы с повторяющимися записями в Excel. Он может находить, выбирать, выбирать, копировать или перемещать повторяющиеся ячейки или целые повторяющиеся строки.
Несмотря на название, программа может быстро выделять дубликаты разных цветов, не удаляя их.
Средство удаления дубликатов добавляет на ленту Excel три новых функции:
- Быстрая дедупликация (быстрое удаление дубликатов) - быстро находите и выбирайте дубликаты в таблице за пару кликов.
- Удаление дубликатов - пошаговый мастер с расширенными параметрами для выявления и выделения дубликатов или уникальных значений в таблице.
- Сравнить два листа - Найдите и выделите дубликаты, сравнив два отдельных столбца или таблицы.
После установки Ultimate Suite for Excel вы найдете эти инструменты на вкладке «Данные» в Ablebits.
Как выделить дубликаты в Excel за пару кликов
В этом примере я создал простую таблицу с тремя столбцами. И наша цель - выделить повторяющиеся строки, которые имеют одинаковое значение во всех трех столбцах:
Вы не поверите, но желаемый результат можно получить всего за 2 клика мыши :)
- Выделив любую ячейку таблицы, используйте кнопку «Быстрое исключение дубликатов» на ленте Excel.
- Откроется диалоговое окно, в котором все столбцы будут автоматически выбраны для поиска дубликатов.
В правом нижнем углу раскрывающегося списка выберите Выделить цветом. Теперь все, что вам нужно сделать, это нажать ОК :) Готово!
Совет. Если вы хотите искать повторяющиеся строки в одном или нескольких столбцах, а не во всех, снимите выделение со всех нерелевантных столбцов и оставьте выделенными только ключевые столбцы.
Результат будет примерно таким:
Как вы можете видеть на изображении выше, инструмент выбрал повторяющиеся линии без первых копий.
Если вы хотите выделить дубликаты, включая первые вхождения этих записей, или если вы хотите раскрасить уникальные записи вместо дубликатов, или если вам не нравится красный цвет по умолчанию, используйте мастер удаления дубликатов, который имеет все эти функции и многое другое.
Как выделить дубликаты в Excel с помощью расширенного пошагового мастера.
По сравнению с быстрым инструментом, который мы только что рассмотрели, для мастера удаления дубликатов требуется еще несколько щелчков мышью. Но это компенсируется рядом дополнительных опций. Посмотрим на это в действии:
- Выберите ячейку в таблице, в которой вы хотите выделить дубликаты, и нажмите кнопку «Удалить дубликаты» на ленте. Мастер запустится, и вся таблица будет выделена. Также на всякий случай надстройка предложит создать резервную копию таблицы. Если вам это не нужно, снимите этот флажок.
Убедитесь, что таблица верна, и нажмите Далее .
- Выберите один из следующих типов данных, которые вы хотите найти:
- Дублировать без первых повторений повторяющихся записей.
- Дублируйте вместе с первым экземпляром.
- Уникальные ценности.
- Уникальные записи и повторяющиеся первые вхождения.
В этом примере мы находим Дубликат + 1-е вхождение :
- Теперь выберите столбцы, чтобы проверить наличие дубликатов. Поскольку мы хотим выбрать полностью повторяющиеся строки, я выбрал все 3 столбца.
Кроме того, надстройка позволяет указать, есть ли у таблицы заголовки, пропускать ли пустые ячейки или игнорировать лишние пробелы. По умолчанию выбраны все параметры.
- Наконец, выберите действие, которое вы хотите выполнить с дубликатами. У вас есть несколько вариантов: выбрать, удалить, скопировать, переместить дубликаты или добавить столбец статуса для выявления дубликатов .
Поскольку сегодня мы изучаем различные способы выделения дубликатов в Excel, наш выбор очевиден :) Итак, выберите «Залить цветом» и выберите один из стандартных цветов или нажмите «Другие цвета» и выберите любой настраиваемый цвет RGB.
Нажимаем кнопку Finish и наслаждаемся результатом :)
Вот как вы можете выбрать дубликаты в Excel с помощью надстройки Duplicate Remover. Если вы хотите опробовать этот инструмент на своих таблицах, вы можете загрузить полнофункциональную пробную версию Ultimate Suite, которая включает более 60 инструментов Excel, чтобы сэкономить ваше время. Будем очень благодарны за отзывы в комментариях!
Похожие статьи
Ваш комментарий добавлен!
Он будет размещен после модерации
Курсы
.png)
.png)
