Загальна формула
=SUM(--(FREQUENCY(data,data)>0))
Резюме
Для підрахунку унікальних числових значень у діапазоні ви можете використовувати формулу на основі функцій FREQUENCY та SUM. У наведеному прикладі номери співробітників відображаються в діапазоні B5: B14. Формула в F5:
=SUM(--(FREQUENCY(B5:B14,B5:B14)>0))
який повертає 4, оскільки в списку є 4 унікальних ідентифікатора співробітників.
Пояснення
Примітка: До Excel 365 у Excel не було спеціальної функції для підрахунку унікальних значень. Ця формула показує єдиний спосіб підрахунку унікальних значень, якщо вони є числовими. Якщо у вас є текстові значення або поєднання тексту та цифр, вам доведеться використовувати більш складну формулу.
Функція Excel FREQUENCY повертає розподіл частоти, що є підсумковою таблицею, що відображає частоту числових значень, організованих у "бункери". Ми використовуємо його тут як обхідний спосіб підрахунку унікальних числових значень.
Працюючи зсередини назовні, ми надаємо однаковий набір чисел як для масиву даних, так і для масиву bins для FREQUENCY:
FREQUENCY(B5:B14,B5:B14)
FREQUENCY повертає масив із підрахунком кожного числового значення в діапазоні:
(4;0;0;0;2;0;1;3;0;0;0)
Результат трохи загадковий, але значення 905 з’являється чотири рази, 773 з’являється двічі, 801 з’являється один раз і 963 з’являється тричі.
FREQUENCY має спеціальну функцію, яка автоматично повертає нуль для будь-яких чисел, які вже з'явилися в масиві даних, саме тому значення, коли число зустрічається, дорівнюють нулю.
Далі кожне з цих значень перевіряється на значення більше нуля:
(4;0;0;0;2;0;1;3;0;0;0)>0
Результат - такий масив:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE)
Кожна ІСТИНА представляє унікальний номер у списку. SUM ігнорує логічні значення за замовчуванням, тому ми примушуємо значення TRUE і FALSE до 1s і 0s з подвійним від'ємним (-), що дає:
=SUM((1;0;0;0;1;0;1;1;0;0;0))
Нарешті, SUM додає ці значення і повертає загальну суму, яка в цьому випадку дорівнює 4.
Примітка: ви також можете використовувати SUMPRODUCT для підсумовування елементів у масиві.
Використання COUNTIF замість FREQUENCY для підрахунку унікальних значень
Іншим способом підрахунку унікальних числових значень є використання COUNTIF замість FREQUENCY. Це простіша формула, але пам’ятайте, що використання COUNTIF для більших наборів даних для підрахунку унікальних значень може спричинити проблеми з продуктивністю. Формула ЧАСТОТА, хоч і є більш складною, обчислює набагато швидше.
