Загальна формула
=SUMPRODUCT(COUNTIF(data,data)-1)>0
Резюме
Чи містить діапазон повторювані значення? Якщо ви хочете перевірити діапазон (або список) на наявність дублікатів, ви можете зробити це за допомогою формули, яка використовує COUNTIF разом із SUMPRODUCT.
У цьому прикладі є список імен у діапазоні B3: B11. Якщо ви хочете протестувати цей список, щоб перевірити, чи є дублікати імен, ви можете використовувати:
=SUMPRODUCT(COUNTIF(B3:B11,B3:B11)-1)>0
Пояснення
Працюючи зсередини, COUNTIF спочатку отримує підрахунок кожного значення в B3: B11 у діапазоні B3: B11. Оскільки ми надаємо діапазон (масив) комірок для критеріїв, COUNTIF повертає масив підрахунків як результат. У наведеному прикладі цей масив виглядає так:
(1; 2; 1; 1; 1; 1; 1; 2; 1)
Далі 1 віднімається, що дає масив, подібний до цього:
(0; 1; 0; 0; 0; 0; 0; 1; 0)
Зверніть увагу, що кожне 1 у масиві (тобто елементи, що з’являються лише один раз) було перетворено на нуль.
Потім SUMPRODUCT додає елементи цього масиву і повертає результат, який у цьому випадку є числом 2, яке потім перевіряється на значення> 0.
Кожного разу, коли список містить дублікати, в масиві буде принаймні два одиниці, підсумовані SUMPRODUCT, тому кінцевий результат TRUE означає, що список містить дублікати.
Обробка порожніх клітин
Порожні клітинки в діапазоні змусять формулу вище генерувати неправильні результати. Щоб відфільтрувати порожні або порожні клітинки, можна скористатися наступною альтернативою:
=SUMPRODUCT((COUNTIF(list,list)-1)*(list""))>0
Тут ми використовуємо список "" логічних виразів, щоб примусити всі значення, пов'язані з порожніми клітинками, до нуля.
