Генеричка формула
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Резиме
Да бисте бројали јединствене вредности са једним или више услова, можете да користите формулу засновану на УНИКУЕ и ФИЛТЕР. У приказаном примеру, формула у Х7 је:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
који враћа 3, јер у Б6 постоје три јединствена имена: Б15 повезана са пројектом Омега.
Напомена: ова формула захтева динамичке формуле низова, доступне само у програму Екцел 365. Са старијом верзијом програма Екцел можете користити сложеније алтернативне формуле.
Објашњење
У основи, ова формула користи функцију УНИКУЕ за издвајање јединствених вредности, а функција ФИЛТЕР примењује критеријуме.
Функционишући изнутра према унутра, функција ФИЛТЕР користи се за примену критеријума и издвајање само имена која су повезана са пројектом „Омега“:
FILTER(B6:B15,C6:C15=H6) // Omega names only
Резултат из ФИЛТЕР-а је низ попут овог:
("Jim";"Jim";"Carl";"Sue";"Carl")
Даље, функција УНИКУЕ се користи за уклањање дупликата:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
што резултира новим низом попут овог:
("Jim";"Carl";"Sue") // after UNIQUE
У овом тренутку имамо јединствену листу имена повезаних са Омегом, и само их морамо избројати. Из доле објашњених разлога то радимо са функцијом ЛЕН и функцијом СУМ. Да би ствари постале јасне, прво ћемо преписати формулу тако да укључује јединствену листу:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Функција ЛЕН добија дужину сваке ставке на листи и враћа низ дужина:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Затим проверавамо да ли су дужине веће од нуле:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
И употријебите двоструки негатив да присилите вредности ТРУЕ и ФАЛСЕ на 1с и 0с:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
На крају, збрајамо резултате помоћу функције СУМ:
=SUM((1;1;1)) // returns 3
Овај низ се испоручује директно функцији ЦОУНТА, која враћа коначни број:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Имајте на уму да се зато што проверавамо дужину сваке ставке коју враћа УНИКУЕ, празне или празне ћелије које испуњавају критеријуме занемарују. Ова формула је динамична и одмах ће се прерачунати ако се промене изворни подаци.
Броји јединствено са више критеријума
Да бисте бројали јединствене вредности на основу више критеријума, можете проширити логику „укључи“ унутар ФИЛТЕРА. На пример, за бројање јединствених имена за пројекат Омега само у јуну, користите:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Ово је пример употребе логичке логике за примену више од једног услова. Приступ је овде детаљније објашњен.
За више детаља погледајте овај видео за обуку: Како филтрирати помоћу више критеријума.
ТАЧКА
Могуће је написати једноставнију формулу која одговара функцији ЦОУНТА. Међутим, важно упозорење је да ће ЦОУНТА вратити 1 када нема одговарајућих вредности. То је зато што функција ФИЛТЕР враћа грешку када се ниједан податак не подудара са критеријумима, а функција ЦОУНТА на крају броји ову грешку. Основна формула ЦОУНТА изгледа овако:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Опет, ова формула ће вратити 1 када нема одговарајућих података. Такође ће садржати празне ћелије које испуњавају критеријуме. Формула заснована на ЛЕН и СУМ је боља опција.
Нема динамичких низова
Ако користите старију верзију Екцела без подршке за динамички низ, можете да користите сложенију формулу. За општију расправу о могућностима динамичког низа, погледајте: Алтернативе формулама динамичког низа.
