Splošna formula
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Povzetek
Za štetje edinstvenih vrednosti z enim ali več pogoji lahko uporabite formulo, ki temelji na UNIQUE in FILTER. V prikazanem primeru je formula v H7:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
ki vrne 3, saj so v B6 tri edinstvena imena: B15, povezana s projektom Omega.
Opomba: ta formula zahteva dinamične formule matrike, ki so na voljo samo v Excelu 365. Pri starejši različici Excela lahko uporabite bolj zapletene nadomestne formule.
Pojasnilo
V osnovi ta formula uporablja funkcijo UNIQUE za pridobivanje unikatnih vrednosti, funkcija FILTER pa uporablja merila.
Funkcija FILTER, ki deluje od znotraj navzven, se uporablja za uporabo meril in pridobivanje samo imen, povezanih s projektom "Omega":
FILTER(B6:B15,C6:C15=H6) // Omega names only
Rezultat iz FILTER je matrika, kot je ta:
("Jim";"Jim";"Carl";"Sue";"Carl")
Nato se funkcija UNIQUE uporablja za odstranjevanje dvojnikov:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
kar ima za posledico novo matriko, kot je ta:
("Jim";"Carl";"Sue") // after UNIQUE
Na tej točki imamo edinstven seznam imen, povezanih z Omego, in jih moramo le prešteti. Iz spodnjih razlogov to storimo s funkcijo LEN in funkcijo SUM. Da bodo stvari jasne, bomo formulo najprej prepisali tako, da bo vključeval edinstven seznam:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Funkcija LEN dobi dolžino vsakega elementa na seznamu in vrne niz dolžin:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Nato preverimo, ali so dolžine večje od nič:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
In uporabite dvojni negativ, da prisilite vrednosti TRUE in FALSE na 1s in 0s:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Na koncu rezultate seštejemo s funkcijo SUM:
=SUM((1;1;1)) // returns 3
Ta matrika je dostavljena neposredno v funkcijo COUNTA, ki vrne končno štetje:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Upoštevajte, da ker preverjamo dolžino vsakega predmeta, ki ga vrne UNIQUE, so prazne ali prazne celice, ki ustrezajo merilom, prezrte. Ta formula je dinamična in bo takoj spremenjena, če se spremenijo izvorni podatki.
Štejte unikat z več merili
Če želite šteti unikatne vrednosti na podlagi več meril, lahko razširite logiko "vključi" v FILTER. Če želite na primer za projekt Omega šteti edinstvena imena samo junija, uporabite:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
To je primer uporabe logične logike za uporabo več kot enega pogoja. Pristop je tukaj podrobneje razložen.
Za več podrobnosti si oglejte ta video o usposabljanju: Kako filtrirati z več merili.
TOČKA
Možno je napisati enostavnejšo formulo, ki ustreza funkciji COUNTA. Vendar je pomembno opozorilo, da bo COUNTA vrnila 1, če ni ustreznih vrednosti. To je zato, ker funkcija FILTER vrne napako, ko se noben podatek ne ujema z merili, in ta napaka na koncu šteje funkcija COUNTA. Osnovna formula COUNTA je videti takole:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Tudi ta formula bo vrnila 1, če ni ustreznih podatkov. Vključeval bo tudi prazne celice, ki ustrezajo merilom. Formula, ki temelji na LEN in SUM, je boljša možnost.
Brez dinamičnih nizov
Če uporabljate starejšo različico Excela brez podpore za dinamična polja, lahko uporabite bolj zapleteno formulo. Za bolj splošno razpravo o možnostih dinamičnega polja glej: Alternative za dinamične matrične formule.
