Загальна формула
=SUMPRODUCT(1/COUNTIF(data,data))
Резюме
Щоб підрахувати кількість унікальних значень у діапазоні комірок, ви можете використовувати формулу на основі функцій COUNTIF і SUMPRODUCT. У прикладі шоу формула в F6 така:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Пояснення
Працюючи зсередини, COUNTIF налаштовано на значення в діапазоні B5: B14, використовуючи всі ці самі значення як критерії:
COUNTIF(B5:B14,B5:B14)
Оскільки ми надаємо 10 значень для критеріїв, ми отримуємо масив із 10 результатами, як це:
(3;3;3;2;2;3;3;3;2;2)
Кожне число представляє рахунок - "Джим" з'являється 3 рази, "Сью" з'являється 2 рази тощо.
Цей масив налаштований як дільник з чисельником 1. Після поділу отримуємо ще один масив:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Будь-які значення, що трапляються лише один раз у діапазоні, відображатимуться як 1s, але значення, що трапляються кілька разів, відображатимуться як дробові значення, що відповідають кратному. (тобто значення, яке з’являється в даних 4 рази, генерує 4 значення = 0,25).
Нарешті, функція SUMPRODUCT підсумовує всі значення в масиві і повертає результат.
Обробка порожніх клітин
Одним із способів обробки порожніх або порожніх комірок є коригування формули наступним чином:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Об’єднуючи порожній рядок ("") у дані, ми не даємо нулям потрапляти в масив, створений COUNTIF, коли в даних є порожні клітинки. Це важливо, оскільки нуль у дільнику спричинить формулу помилку # DIV / 0. Це працює, оскільки використання порожнього рядка ("") для критеріїв буде враховувати порожні клітинки.
Однак, хоча ця версія формули не видасть помилки # DIV / 0, якщо є порожні клітинки, вона включатиме пусті клітинки в підрахунок. Якщо ви хочете виключити порожні клітинки з підрахунку, використовуйте:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Це призводить до скасування кількості порожніх клітинок, роблячи нумератор нульовим для пов'язаних підрахунків.
Повільна продуктивність?
Це крута і елегантна формула, але вона обчислює набагато повільніше, ніж формули, які використовують ЧАСТОТУ для підрахунку унікальних значень. Для більших наборів даних вам може знадобитися перейти до формули на основі функції ЧАСТОТА. Ось формула для числових значень та одна для текстових значень.
