Обща формула
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Обобщение
За да преброите уникални текстови стойности в диапазон с критерии, можете да използвате формула на масив, базирана на функциите FREQUENCY и MATCH. В показания пример формулата в G6 е:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
което връща 3, тъй като по проект Omega са работили трима различни души.
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter.
Обяснение
Това е сложна формула, която използва ЧЕСТОТА за преброяване на числови стойности, получени с функцията MATCH. Работейки отвътре навън, функцията MATCH се използва, за да получи позицията на всяка стойност, която се появява в данните:
MATCH(B5:B11,B5:B11,0)
Резултатът от MATCH е масив като този:
(1;1;3;1;1;6;7)
Тъй като MATCH винаги връща позицията на първото съвпадение, стойностите, които се появяват повече от веднъж в данните, връщат една и съща позиция. Например, тъй като „Джим“ се появява 4 пъти в списъка, той се показва в този масив 4 пъти като числото 1.
Извън функцията MATCH, функцията IF се използва за прилагане на критерии, което в този случай включва тестване дали проектът е "омега" (от клетка G5):
IF(C5:C11=G5 // filter on "omega"
Функцията IF действа като филтър, като позволява на стойностите от MATCH да преминат само ако са свързани с "омега". Резултатът е масив като този:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Филтрираният масив се доставя директно на функцията FREQUENCY като аргумент data_array . След това функцията ROW се използва за изграждане на последователен списък с числа за всяка стойност в данните:
ROW(B3:B12)-ROW(B3)+1
Това създава масив като този:
(1;2;3;4;5;6;7;8;9;10)
което се превръща в аргумент bins_array във FILTER. Към този момент имаме:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY връща масив от числа, които показват броя на всяка стойност в масива от данни, организиран от bin. Когато числото вече е преброено, FREQUENCY ще върне нула. Резултатът от FREQUENCY е масив като този:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Забележка: FREQUENCY винаги връща масив с още един елемент от bins_array .
На този етап можем да пренапишем формулата по следния начин:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Проверяваме за стойности, по-големи от нула, което преобразува числата в TRUE или FALSE:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
След това използваме двойно отрицателно, за да принудим логическите стойности към 1s и 0s:
=SUM((1;0;0;0;0;1;1;0))
И накрая, функцията SUM връща 3 като краен резултат.
Забележка: това е формула на масив и трябва да се въведе с помощта на Control + Shift + Enter.
Обработка на празни клетки в диапазона
Ако някои клетки в диапазона са празни, ще трябва да коригирате формулата, за да предотвратите преминаването на празни клетки във функцията MATCH, което ще доведе до грешка. Можете да направите това, като добавите друга вложена IF функция, за да проверите за празни клетки:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
С два критерия
Ако имате два критерия, можете да разширите логиката на формулата, като добавите друг вложен IF:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Където c1 = критерии1, c2 = критерии2 и vals = стойностите варират.
С логическа логика
С булева логика можете да намалите вложените IF:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Това улеснява добавянето и управлението на допълнителни критерии.
