Загальна формула
=COUNTIF(data,A1)>1
Резюме
Примітка: Excel містить безліч вбудованих "попередньо встановлених налаштувань" для виділення значень за умови умовного форматування, включаючи пресет для виділення повторюваних значень. Однак, якщо ви хочете отримати більшу гнучкість, ви можете виділити дублікати за допомогою власної формули, як пояснено в цій статті.
Якщо ви хочете виділити комірки, що містять дублікати у наборі даних, ви можете використовувати просту формулу, яка повертає TRUE, коли значення з’являється більше одного разу.
Наприклад, якщо ви хочете виділити дублікати в діапазоні B4: G11, ви можете скористатися цією формулою:
=COUNTIF($B$4:$G$11,B4)>1
Примітка: при умовному форматуванні важливо, щоб формула була введена щодо "активної комірки" у виділенні, яке в цьому випадку вважається В4.
Пояснення
COUNTIF просто підраховує кількість разів, коли кожне значення відображається в діапазоні. Коли підрахунок більше 1, формула повертає TRUE і запускає правило.
Коли ви використовуєте формулу для застосування умовного форматування, формула обчислюється відносно активної комірки у виділенні під час створення правила. У цьому випадку діапазон, який ми використовуємо в COUNTIF, блокується абсолютною адресою, але B4 повністю відносний. Отже, правило обчислюється для кожної комірки в діапазоні, при цьому B4 змінюється і $ B $ 4: $ G $ 11 залишається незмінним.
Змінна кількість дублікатів + іменовані діапазони
Замість жорсткого кодування числа 1 у формулу, ви можете посилатися на клітинку, щоб зробити кількість дублікатів змінною.
Ви можете розширити цю ідею та полегшити читання формули, використовуючи іменовані діапазони. Наприклад, якщо ви назвали G2 "дупками", а діапазон B4: G11 "даними", ви можете переписати формулу так:
=COUNTIF(data,B4)>=dups
Потім ви можете змінити значення в G2 на будь-що, що вам подобається, і правило умовного форматування відповість миттєво, виділивши комірку, яка містить значення, більші або рівні числу, який ви вказали в іменованому діапазоні "дупи".
