Общая формула
=COUNTIF(data,A1)>1
Резюме
Примечание. Excel содержит множество встроенных «предустановок» для выделения значений с условным форматированием, включая предустановку для выделения повторяющихся значений. Однако, если вам нужна большая гибкость, вы можете выделить дубликаты с помощью собственной формулы, как описано в этой статье.
Если вы хотите выделить ячейки, содержащие дубликаты в наборе данных, вы можете использовать простую формулу, которая возвращает ИСТИНА, когда значение появляется более одного раза.
Например, если вы хотите выделить дубликаты в диапазоне B4: G11, вы можете использовать эту формулу:
=COUNTIF($B$4:$G$11,B4)>1
Примечание. При условном форматировании важно, чтобы формула вводилась относительно «активной ячейки» в выделенном фрагменте, которым в данном случае считается B4.
Объяснение
СЧЁТЕСЛИ просто подсчитывает, сколько раз каждое значение появляется в диапазоне. Когда счетчик больше 1, формула возвращает ИСТИНА и запускает правило.
При использовании формулы для применения условного форматирования формула вычисляется относительно активной ячейки в выделенном фрагменте во время создания правила. В этом случае диапазон, который мы используем в COUNTIF, заблокирован абсолютным адресом, но B4 полностью относительный. Итак, правило оценивается для каждой ячейки в диапазоне, при этом B4 изменяется, а $ B $ 4: $ G $ 11 остается без изменений.
Переменное количество дубликатов + именованные диапазоны
Вместо того, чтобы жестко указывать число 1 в формуле, вы можете ссылаться на ячейку, чтобы количество дубликатов было переменной.
Вы можете расширить эту идею и упростить чтение формулы, используя именованные диапазоны. Например, если вы назовете G2 «дубликаты», а диапазон B4: G11 «данные», вы можете переписать формулу следующим образом:
=COUNTIF(data,B4)>=dups
Затем вы можете изменить значение в G2 на любое, что вам нравится, и правило условного форматирования немедленно отреагирует, выделив ячейку, содержащую значения, большие или равные числу, которое вы поместили в именованный диапазон «дупс».
