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