Общая формула
=СУММПРОИЗВ((ряд1=сслыка1)*(ряд2=сслыка2)*(ряд2=сслыка2))>1
=SUMPRODUCT((row1=ref1)*(row2=ref2)*(row2=ref2))>1
Объяснение
Excel содержит встроенный модуль для выделения повторяющихся значений с условным форматированием, но он работает только на уровне ячейки. Если вы хотите найти и выделить повторяющиеся столбцы, вам нужно будет использовать свою собственную формулу, как описано ниже.
Чтобы выделить повторяющиеся столбцы, вы можете использовать формулу, основанную на функции СУММПРОИЗВ. В приведенном примере формула используется для выделения повторяющихся столбцов:
=СУММПРОИЗВ(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1
=SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1
Как работает формула
Этот подход использует СУММПРОИЗВ для расчёта появления каждого значения в таблице по одной строке за раз. Только тогда, когда одно и то же значение отображается в одном месте во всех трех строках, генерируется счет. Для ячейки B4 формула решается следующим образом:
=СУММПРОИЗВ (($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1
=СУММПРОИЗВ (({1,1,1,1})*({1,0,1,0})*({1,0,1,0}))>1
=СУММПРОИЗВ ({1,0,1,0})>1
=2>1
=ИСТИНА
=SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1
=SUMPRODUCT(({1,1,1,1})*({1,0,1,0})*({1,0,1,0}))>1
=SUMPRODUCT({1,0,1,0})>1
=2>1
=TRUE
Обратите внимание, что ссылки на строки полностью абсолютны, а ссылки на ячейки смешаны, причем только строка зафиксирована.
С помощью вспомогательных строк
Если вам не будут мешать дополнительные вспомогательные строки в данных, вы можете немного упростить формулу условного форматирования. В вспомогательной строке объедините все значения в столбце. Затем вы можете использовать СЧЁТЕСЛИ в этой строке для подсчета значений, которые появляются более одного раза, и использовать результат для запуска условного форматирования во всем столбце.
