Üldine valem
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Kokkuvõte
Ühe või mitme tingimusega kordumatute väärtuste loendamiseks võite kasutada valikut, mis põhineb ainulaadsel ja filtril. Näidatud näites on H7 valem:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
mis tagastab 3, kuna B6-s on kolm ainulaadset nime: B15, mis on seotud Omega projektiga.
Märkus. Selle valemi jaoks on vaja dünaamilise massiivi valemeid, mis on saadaval ainult rakenduses Excel 365. Exceli vanema versiooni korral saate kasutada keerukamaid alternatiivseid valemeid.
Selgitus
Põhimõtteliselt kasutab see valem ainulaadsete väärtuste väljavõtmiseks funktsiooni UNIQUE ja funktsioon FILTER rakendab kriteeriume.
Seestpoolt töötades kasutatakse funktsiooni FILTER kriteeriumide rakendamiseks ja ainult nimega, mis on seotud projektiga "Omega":
FILTER(B6:B15,C6:C15=H6) // Omega names only
FILTERi tulemus on selline massiiv:
("Jim";"Jim";"Carl";"Sue";"Carl")
Järgmisena kasutatakse duplikaatide eemaldamiseks funktsiooni UNIQUE:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
mille tulemuseks on uus selline massiiv:
("Jim";"Carl";"Sue") // after UNIQUE
Siinkohal on meil Omega ainulaadne nimekiri nimedest ja peame lihtsalt need kokku lugema. Allpool selgitatud põhjustel teeme seda funktsiooniga LEN ja SUM. Asjade selgeks saamiseks kirjutame kõigepealt valemi ümber, et lisada ainulaadne loend:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Funktsioon LEN saab loendis iga üksuse pikkuse ja tagastab pikkuste massiivi:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Järgmisena kontrollime, kas pikkused on suuremad kui null:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
Ja kasutage topeltnegatiivi, et sundida TÕENE ja VALE väärtused väärtusele 1 ja 0:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Lõpuks liidame tulemused funktsiooniga SUM:
=SUM((1;1;1)) // returns 3
See massiiv edastatakse otse funktsioonile COUNTA, mis tagastab lõpliku loenduse:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Pange tähele, et kuna me kontrollime iga kordumatu pikkusega üksuse pikkust, siis eiratakse kriteeriumidele vastavaid tühje või tühje lahtreid. See valem on dünaamiline ja lähteandmete muutmise korral arvutab see kohe ümber.
Kordumatu loendamine mitme kriteeriumiga
Mitmel kriteeriumil põhinevate unikaalsete väärtuste lugemiseks võib laiendada loogikat "include" FILTER-is. Näiteks Omega projekti ainulaadsete nimede lugemiseks ainult juunis kasutage järgmist:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
See on näide boolean loogika kasutamisest mitme tingimuse rakendamiseks. Lähenemist on siin üksikasjalikumalt selgitatud.
Lisateavet leiate sellest koolitusvideost: Kuidas filtreerida mitme kriteeriumiga.
KUNST
Võimalik on kirjutada lihtsam valem, mis vastab funktsioonile COUNTA. Kuid oluline hoiatus on see, et COUNTA tagastab 1, kui vastavaid väärtusi pole. Seda seetõttu, et funktsioon FILTER tagastab vea, kui ükski andmestik ei vasta kriteeriumidele, ja funktsioon COUNTA loeb seda viga. COUNTA põhivalem näeb välja selline:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Jällegi tagastab see valem 1, kui vastavaid andmeid pole. See sisaldab ka tühje lahtrid, mis vastavad kriteeriumidele. Parem variant on LEN-il ja SUM-il põhinev valem.
Dünaamilisi massiive pole
Kui kasutate Exceli vanemat versiooni ilma dünaamilise massiivi toeta, võite kasutada keerukamat valemit. Dünaamiliste massiivide alternatiivide üldisema arutelu saamiseks vaadake: Alternatiivid dünaamiliste massiivide valemitele.
