Загальна формула
=SUMPRODUCT((row1=ref1)*(row2=ref2)*(row3=ref3))>1
Резюме
Excel містить вбудований пресет для виділення повторюваних значень за умови умовного форматування, але він працює лише на рівні комірки. Якщо ви хочете знайти та виділити повторювані стовпці, вам потрібно буде скористатися власною формулою, як пояснено нижче.
Щоб виділити повторювані стовпці, можна використовувати формулу, засновану на функції SUMPRODUCT. У наведеному прикладі формула, яка використовується для виділення повторюваних стовпців:
=SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1
Пояснення
Цей підхід використовує SUMPRODUCT для підрахунку появи кожного значення в таблиці, один рядок за раз. Тільки коли одне і те ж значення з'являється в одному і тому ж місці у всіх трьох рядках, генерується підрахунок. Для комірки B4 формула вирішується так:
=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
Зверніть увагу, що посилання на рядки є абсолютно абсолютними, тоді як посилання на комірки є змішаними, і лише рядок заблоковано.
З допоміжним рядом
Якщо ви не проти додати допоміжний рядок до своїх даних, ви можете трохи спростити формулу умовного форматування. У допоміжному рядку об’єднайте всі значення в стовпці. Тоді ви можете використовувати COUNTIF у цьому одному рядку для підрахунку значень, які з’являються більше одного разу, і використовувати результат, щоб викликати умовне форматування у всьому стовпці.
