Обща формула
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Обобщение
За да преброите уникални стойности с едно или повече условия, можете да използвате формула, базирана на UNIQUE и FILTER. В показания пример формулата в H7 е:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
което връща 3, тъй като в B6 има три уникални имена: B15, свързани с проекта Omega.
Забележка: тази формула изисква динамични формули за масиви, налични само в Excel 365. С по-стара версия на Excel можете да използвате по-сложни алтернативни формули.
Обяснение
В основата си тази формула използва функцията UNIQUE за извличане на уникални стойности, а функцията FILTER прилага критерии.
Работейки отвътре навън, функцията FILTER се използва за прилагане на критерии и извличане само на имена, които са свързани с проекта "Omega":
FILTER(B6:B15,C6:C15=H6) // Omega names only
Резултатът от FILTER е масив като този:
("Jim";"Jim";"Carl";"Sue";"Carl")
След това функцията UNIQUE се използва за премахване на дубликати:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
което води до нов масив като този:
("Jim";"Carl";"Sue") // after UNIQUE
На този етап имаме уникален списък с имена, свързани с Омега, и просто трябва да ги преброим. По причини, обяснени по-долу, правим това с функцията LEN и функцията SUM. За да направим нещата ясни, първо ще пренапишем формулата, за да включим уникалния списък:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Функцията LEN получава дължината на всеки елемент в списъка и връща масив от дължини:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
След това проверяваме дали дължините са по-големи от нула:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
И използвайте двойно отрицателно, за да принудите стойностите TRUE и FALSE до 1s и 0s:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
И накрая, сумираме резултатите с функцията SUM:
=SUM((1;1;1)) // returns 3
Този масив се доставя директно на функцията COUNTA, която връща окончателно отчитане:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Имайте предвид, че тъй като проверяваме дължината на всеки елемент, върнат от UNIQUE, празни или празни клетки, които отговарят на критериите, се игнорират. Тази формула е динамична и ще преизчисли незабавно, ако данните на източника се променят.
Брой уникални с множество критерии
За да преброите уникални стойности въз основа на множество критерии, можете да разширите логиката "включване" във FILTER. Например, за да преброите уникални имена за проекта Omega само през юни, използвайте:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Това е пример за използване на логическа логика за прилагане на повече от едно условие. Тук подходът е обяснен по-подробно.
За повече подробности вижте това видео за обучение: Как да филтрирате с множество критерии.
КОНТА
Възможно е да напишете по-проста формула, която отговаря на функцията COUNTA. Важна забележка обаче е, че COUNTA ще върне 1, когато няма съвпадащи стойности. Това е така, защото функцията FILTER връща грешка, когато няма данни, които отговарят на критериите и тази грешка в крайна сметка се отчита от функцията COUNTA. Основната формула COUNTA изглежда така:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Отново, тази формула ще върне 1, когато няма съвпадащи данни. Той също така ще включва празни клетки, които отговарят на критериите. Формулата, базирана на LEN и SUM, е по-добър вариант.
Няма динамични масиви
Ако използвате по-стара версия на Excel без поддръжка на динамичен масив, можете да използвате по-сложна формула. За по-общо обсъждане на алтернативите на динамичен масив вижте: Алтернативи на формулите на динамичен масив.
