Általános képlet
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Összegzés
Az egyedi értékek egy vagy több feltétellel történő számlálásához használhatja az UNIQUE és a FILTER alapú képletet. A bemutatott példában a H7 képlete a következő:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
amely 3-at ad vissza, mivel a B6-ban három egyedi név található: B15 az Omega projekthez társítva.
Megjegyzés: ehhez a képlethez dinamikus tömbképletek szükségesek, amelyek csak az Excel 365-ben érhetők el. Az Excel régebbi verziójával összetettebb alternatív képleteket is használhat.
Magyarázat
Lényegében ez a képlet az UNIQUE függvényt használja az egyedi értékek kinyerésére, a FILTER függvény pedig kritériumokat alkalmaz.
Belülről kifelé haladva a SZŰRŐ funkcióval kritériumokat alkalmaznak és csak az „Omega” projekthez társított neveket vonják ki:
FILTER(B6:B15,C6:C15=H6) // Omega names only
A FILTER eredménye egy ilyen tömb:
("Jim";"Jim";"Carl";"Sue";"Carl")
Ezután az UNIQUE függvényt használják a másolatok eltávolítására:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
ami egy ilyen új tömböt eredményez:
("Jim";"Carl";"Sue") // after UNIQUE
Ezen a ponton egyedi névlistánk van az Omega-hoz társítva, és csak meg kell számolnunk őket. Az alábbiakban ismertetett okokból ezt a LEN és a SUM funkcióval végezzük. A dolgok egyértelművé tétele érdekében először átírjuk a képletet az egyedi lista felvételére:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
A LEN függvény megkapja a lista minden elemének hosszát, és hosszúsági tömböt ad vissza:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Ezután ellenőrizzük, hogy a hosszúság nagyobb-e, mint nulla:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
És használjon dupla negatív értéket az IGAZ és HAMIS értékek 1-es és 0-asra kényszerítésére:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Végül összeadjuk az eredményeket a SUM függvénnyel:
=SUM((1;1;1)) // returns 3
Ez a tömb közvetlenül a COUNTA függvényhez érkezik, amely visszaadja a végső számot:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Vegye figyelembe, hogy mivel ellenőrizzük az UNIQUE által visszaadott elemek hosszát, a kritériumoknak megfelelő üres vagy üres cellákat nem veszünk figyelembe. Ez a képlet dinamikus, és azonnal kiszámítja, ha a forrásadatokat megváltoztatja.
Számoljon egyedit több kritériummal
Az egyedi értékek több kritérium alapján történő megszámolásához kiterjesztheti az "include" logikát a FILTER belsejében. Például az Omega projekt egyedi neveinek csak júniusban történő megszámolásához használja:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Ez egy példa logikai logika használatára egynél több feltétel alkalmazásához. A megközelítést itt részletesebben elmagyarázzuk.
További részletekért lásd ezt a képzési videót: Hogyan szűrhet több kritériummal.
COUNTA
Lehetőség van egyszerűbb képlet írására, amely a COUNTA függvényre válaszol. Fontos figyelmeztetés azonban, hogy a COUNTA 1-et ad vissza, ha nincsenek megfelelő értékek. A SZŰRŐ függvény hibát ad vissza, ha egyetlen adat sem felel meg a feltételeknek, és ezt a hibát végül a COUNTA függvény számolja. A COUNTA alapvető képlete a következőképpen néz ki:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Ez a képlet ismét 1-et ad vissza, ha nincs megfelelő adat. Ez magában foglalja a kritériumoknak megfelelő üres cellákat is. A LEN és SUM alapú képlet jobb megoldás.
Nincsenek dinamikus tömbök
Ha az Excel régebbi verzióját használja dinamikus tömb támogatás nélkül, használhat bonyolultabb képletet. A dinamikus tömb alternatívák általánosabb ismertetését lásd: Alternatívák a dinamikus tömb képletekhez.
