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