Функция И в Excel

Функция И в Excel
На чтение
33 мин.
Просмотров
45
Дата обновления
31.10.2024

Как выделить повторяющиеся значения в Excel?

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

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

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

  • Как выделить дубликаты с помощью встроенного инструмента
  • Как выделить дубликаты без учета первого появления
  • Как показать дубликаты 3, 4 и выше?
  • Выделите повторяющиеся значения в диапазоне
  • Выберите всю строку на основе дубликатов в определенном столбце
  • Выделите повторяющиеся строки
  • Выделите последовательные повторяющиеся ячейки
  • Как выделить дубликаты в Excel специальным инструментом и без формул

Далее в этом руководстве вы найдете несколько способов выделения повторяющихся записей в зависимости от конкретной задачи. Эти методы работают во всех версиях Excel 2016, Excel 2013, Excel 2010 и более ранних версиях.

Как подсветить дубликаты, используя встроенный инструмент (с 1-м появлением)

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

  1. Выберите данные, которые хотите просмотреть. Это может быть столбец, строка или диапазон ячеек.
  2. На вкладке «Главная» выберите «Условное форматирование» > «Правила выделения ячеек» > «Повторяющиеся значения».

 

 

Откроется диалоговое окно со светло-красной заливкой и темно-красным текстом, выбранными по умолчанию. Чтобы применить форматирование по умолчанию, просто нажмите OK.

 

 

В дополнение к красной заливке и форматированию текста в раскрывающемся списке доступно несколько других предустановленных форматов. Чтобы покрасить повторяющиеся ячейки в другой цвет, нажмите «Пользовательский формат» (последний элемент в раскрывающемся списке) и выберите предпочтительный шрифт или цвет заливки.
Хорошо сейчас. Чтобы закодировать уникальные значения цветом, выберите «Уникальный» в поле слева.

И вот что у нас получилось в итоге:

 

 

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

При использовании этого инструмента помните о следующих двух вещах:

  • Работает только для одиночных ячеек. Чтобы отметить повторяющиеся строки, вы должны создать свои собственные правила.
  • Закрасьте повторяющиеся ячейки, включая их первые появления. Чтобы пометить все, кроме первых нескольких экземпляров, создайте правило условного форматирования на основе формулы из этого руководства по поиску дубликатов.

Как выделить повторы без 1-го вхождения

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

  1. Выберите «Условное форматирование» > «Новое правило» > «Использовать формулу», чтобы определить ячейки для форматирования .
  2. В поле «Формат значений», где эта формула возвращает ИСТИНА, введите:

=СЧЁТЕСЛИ($B$2:$B2,$B2)>1

Где B2 — верхняя ячейка выбранного диапазона.

  1. Нажмите кнопку «Формат», выберите нужный цвет заливки и/или шрифт.
  2. Наконец, нажмите OK, чтобы сохранить и применить то, что вы создали.

 

 

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

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

Как показать третий, четвертый и все последующие дубликаты?

Чтобы увидеть повторяющиеся значения из вхождения N, действуйте, как в предыдущем примере. Единственное отличие состоит в том, что вы заменяете > 1 в конце формулы на желаемое число. Например:

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

=СЧЁТЕСЛИ($B$2:$B2,$B2)>=3

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

=СЧЁТЕСЛИ($B$2:$B2,$B2)>=4

Чтобы выбрать только определенные вхождения, используйте оператор равенства (=). 

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

=СЧЁТЕСЛИ($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

В приведенной выше формуле первая функция СЧЁТЕСЛИ подсчитывает количество вхождений данного элемента в первом столбце, а вторая СЧЁТЕСЛИ делает то же самое для всех последующих. Затем вы складываете эти числа и проверяете, больше ли сумма 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. Вы можете находить, выделять, выбирать, копировать или перемещать повторяющиеся ячейки или целые повторяющиеся строки.

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

Duplicate Remover добавляет на ленту Excel три новых функции:

  • Быстрая дедупликация — быстро находите и выделяйте дубликаты в таблице за пару кликов.
  • Duplicate Remover — это пошаговый мастер с расширенными параметрами для выявления и выделения повторяющихся или уникальных значений в таблице.
  • Сравните два листа — найдите и выделите дубликаты, сравнив два столбца или два отдельных листа.

После установки Ultimate Suite for Excel вы найдете эти инструменты на вкладке «Данные» в Ablebits.

Как выделить дубликаты в Excel за пару кликов

Для этого примера я создал простую таблицу с тремя столбцами. И наша цель — выделить повторяющиеся строки, имеющие одинаковые значения во всех трех столбцах:

 

 

Вы не поверите, но желаемый результат можно получить всего в 2 клика мыши :)

  1. Выбрав любую ячейку таблицы, используйте кнопку Quick Dedupe на ленте Excel.
  2. Откроется диалоговое окно, в котором будут автоматически выбраны все столбцы для проверки на наличие дубликатов.

 

 

В правом нижнем углу из выпадающего списка выберите Выделить цветом. Теперь все, что вам нужно сделать, это нажать OK :) Готово!

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

 

 

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

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

Как выделить дубликаты в Excel с помощью расширенного пошагового мастера.

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

  1. Выберите любую ячейку таблицы, в которой вы хотите выделить дубликаты, и нажмите кнопку «Удалить дубликаты» на ленте. Запустится мастер, и будет выбрана вся таблица. Также на всякий случай плагин предложит создать резервную копию вашей таблицы. Если вам это не нужно, снимите этот флажок.

Убедитесь, что таблица выбрана правильно, и нажмите «Далее .

  1. Выберите один из следующих типов данных, которые вы хотите найти:
    • Дубликаты без первых вхождений повторяющихся записей.
    • Дублируется вместе с первым появлением.
    • Уникальные значения.
    • Одиночные записи и повторные первые появления.

В этом примере давайте найдем дубликаты + 1-е вхождение :

 

 

  1. Теперь выберите столбцы для проверки на наличие дубликатов. Поскольку мы хотим выделить целые повторяющиеся строки, я выбрал все 3 столбца.

 

 

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

  1. Наконец, выберите действие для дубликатов. У вас есть несколько вариантов: выбрать, удалить, скопировать, переместить дубликаты или добавить столбец состояния для выявления дубликатов .

Поскольку сегодня мы изучаем различные способы выделения дубликатов в Excel, наш выбор очевиден :) Поэтому выберите «Заполнить цветом» и выберите один из стандартных цветов или нажмите «Другие цвета» и выберите любой пользовательский цвет RGB.

 

 

Нажимаем кнопку Готово и наслаждаемся результатом :)

 

 

Вот как вы можете выделить дубликаты в Excel с помощью надстройки Duplicate Remover. Если вы хотите попробовать этот инструмент в своих электронных таблицах, вы можете загрузить полнофункциональную пробную версию Ultimate Suite, которая включает более 60 инструментов для экономии времени при работе в Excel. Будем очень признательны за ваши отзывы в комментариях!

 

0 Комментариев
Комментариев на модерации: 0
Оставьте комментарий