Общая формула
=СЧЕТЕСЛИМН(столбец_a;$A1; столбец_b;$B1; столбец_c;$C1)
=COUNTIFS(col_a,$A1,col_b,$B1,col_c,$C1)
Объяснение
Примечание: Excel содержит встроенные правила для выделения дублирующихся значений с условным форматированием, но работает только для ячейки. Если вы хотите выделить всю строку, то вам нужно использовать свою собственную формулу, как описано ниже.
Если вы хотите выделить повторяющиеся строки в несортированном наборе данных, и вы не хотите добавлять вспомогательный столбец, вы можете использовать формулу, которая использует функцию СЧЕТЕСЛИМН для подсчета дублированных значений в каждом столбце данных.
Например, если у вас есть значения в ячейках B4:D11 и вы хотите выделить целые повторяющиеся строки, вы можете использовать довольно большую формулу:
=СЧЕТЕСЛИМН($B$4:$B$11;$B4;$C$4:$C$11;$C4;$D$4:$D$11;$D4)>1
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Именованные диапазоны для более понятного синтаксиса
Причина, по которой приведенная выше формула настолько большая, состоит в том, что нам нужно полностью фиксировать каждый диапазон столбцов, а затем использовать смешанную ссылку для проверки каждой ячейки в каждом столбце. Если вы создадите именованные диапазоны для каждого столбца в данных: столбец_a, столбец_b и столбец_c, формула может быть написана с более понятным синтаксисом:
=СЧЕТЕСЛИМН(столбец_a;$B4;столбец_c;$C4;столбец_d;$D4)>1
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
С помощью вспомогательного столбца
Если вы можете добавить вспомогательный столбец для своих данных, вы можете немного упростить формулу условного форматирования. Во вспомогательном столбце объединяйте значения из всех столбцов. Например, добавьте формулу в столбец E, которая выглядит следующим образом:
=B4&C4&D4
=B4&C4&D4
Затем используйте следующую формулу в правиле условного форматирования:
=СЧЕТЕСЛИ($E$4:$E$11;$E4)>1
=COUNTIF($E$4:$E$11,$E4)>1
Это намного более простое правило, и вы можете скрыть вспомогательный столбец, если хотите.
Как работает формула
В формуле СЧЕТЕСЛИМН подсчитывает количество раз, когда каждое значение в ячейке появляется в своем «родительском» столбце. По определению каждое значение должно появляться как минимум один раз, поэтому, когда «количество>1», значение является дублирующимся. Ссылки фиксируются, поэтому формула вернет истина только тогда, когда все 3 ячейки в строке появляются более одного раза в соответствующих столбцах.
Опция вспомогательной колонки «хитрит», объединяя все значения в строке вместе в одной ячейке с помощью конкатенации. Тогда СЧЕТЕСЛИМН просто подсчитывает количество раз, когда это конкатенированное значение появляется в столбце D.
Другой способ
Если вы используете версию Excel до 2007 года, или если вы просто предпочитаете использовать СУММПРОИЗВ, потому что вам это нравится, вы также можете использовать эту формулу:
=СУММПРОИЗВ((столбец_b=$B4)*(столбец _c=$C4)*(столбец_d=$D4))>1
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
