Загальна формула
=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 комірки поспіль з’являться більше ніж один раз у відповідних стовпцях.
Параметр допоміжного стовпця "обдурює", об'єднуючи всі значення в рядку разом в одній комірці за допомогою об'єднання. Потім COUNTIF просто підраховує, скільки разів це об'єднане значення відображається у стовпці D.
Допоміжна колонка + конкатенація
Якщо ви не проти додати до своїх даних допоміжний стовпець, ви можете трохи спростити формулу умовного форматування. У допоміжному стовпці об’єднайте значення з усіх стовпців. Наприклад, додайте формулу в стовпець E, яка виглядає так:
=B4&C4&D4
Потім у правилі умовного форматування використовуйте таку формулу:
=COUNTIF($E$4:$E$11,$E4)>1
Це набагато простіше правило, і ви можете приховати допоміжний стовпець, якщо хочете.
Якщо у вас дійсно велика кількість стовпців, ви можете використовувати функцію TEXTJOIN (Excel 2016 365), щоб виконати конкатенацію, використовуючи діапазон:
=TEXTJOIN(",",TRUE,A1:Z1)
Потім ви можете використовувати COUNTIF, як зазначено вище.
ПІДГОТОВКА
Якщо ви використовуєте версію Excel до 2007 року, ви можете використовувати SUMPRODUCT так:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
