Загальна формула
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Резюме
Для підрахунку унікальних числових значень у діапазоні ви можете використовувати формулу на основі функцій FREQUENCY, SUM та IF. У наведеному прикладі номери співробітників відображаються в діапазоні B5: B14. Формула в G6 така:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
який повертає 2, оскільки в будівлі А. є 2 унікальні ідентифікатори співробітників
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter, якщо ви не використовуєте Excel 365.
Пояснення
Примітка: До Excel 365 у Excel не було спеціальної функції для підрахунку унікальних значень. Ця формула показує єдиний спосіб підрахунку унікальних значень, якщо вони є числовими. Якщо у вас є текстові значення або поєднання тексту та цифр, вам доведеться використовувати більш складну формулу.
Функція ЧАСТОТА ЧАСТОТИ Excel повертає розподіл частоти, що є підсумковою таблицею, що містить частоту числових значень, організованих у "біни". Ми використовуємо його тут як обхідний спосіб підрахунку унікальних числових значень. Для застосування критеріїв ми використовуємо функцію IF.
Працюючи зсередини назовні, ми спочатку фільтруємо значення за допомогою функції IF:
IF(C5:C14="A",B5:B14) // filter on building A
Результатом цієї операції є такий масив:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Зверніть увагу, що всі ідентифікатори в будівлі B тепер НЕПРАВДИВІ Цей масив доставляється безпосередньо до функції FREQUENCY як масив даних . Для bins_array ми надаємо самі ідентифікатори:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
У цій конфігурації FREQUENCY повертає масив нижче:
(4;0;0;0;2;0;0;0;0;0;0)
Результат трохи загадковий, але значення 905 з’являється чотири рази, а 773 два рази. Значення FALSE автоматично ігноруються.
FREQUENCY має спеціальну функцію, яка автоматично повертає нуль для будь-яких чисел, які вже з'явилися в масиві даних, саме тому значення, коли число зустрічається, дорівнюють нулю. Це особливість, яка дозволяє працювати такому підходу.
Далі кожне з цих значень перевіряється на значення більше нуля:
(4;0;0;0;2;0;0;0;0;0;0)>0
Результат - такий масив:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Кожне значення TRUE у списку представляє унікальне число у списку, і нам просто потрібно скласти значення TRUE за допомогою SUM. Однак SUM не буде складати логічні значення в масиві, тому нам потрібно спочатку примусити значення до 1 або нуля. Це робиться з подвійним негативним (-). Результат - масив лише з 1 або 0:
(1;0;0;0;1;0;0;0;0;0;0)
Нарешті, SUM додає ці значення і повертає загальну суму, яка в цьому випадку дорівнює 2.
Кілька критеріїв
Ви можете розширити формулу, щоб обробляти кілька таких критеріїв:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
