Обща формула
(=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)
Забележете, че всички идентификатори в сграда Б вече са НЕВЯРНИ. Този масив се доставя директно на функцията FREQUENCY като data_array . За 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)))
