Vispārēja formula
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Kopsavilkums
Lai uzskaitītu unikālas vērtības ar vienu vai vairākiem nosacījumiem, varat izmantot formulu, kuras pamatā ir UNIKĀLA un FILTRS. Parādītajā piemērā formula H7 ir:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
kas atgriež 3, jo B6 ir trīs unikāli nosaukumi: B15, kas saistīti ar Omega projektu.
Piezīme: šai formulai nepieciešamas dinamisko masīvu formulas, kas pieejamas tikai programmā Excel 365. Izmantojot vecāku Excel versiju, varat izmantot sarežģītākas alternatīvas formulas.
Paskaidrojums
Būtībā šī formula unikālo vērtību iegūšanai izmanto funkciju UNIQUE, un funkcija FILTER piemēro kritērijus.
Strādājot no iekšpuses, funkcija FILTER tiek izmantota, lai piemērotu kritērijus un iegūtu tikai nosaukumus, kas saistīti ar projektu "Omega":
FILTER(B6:B15,C6:C15=H6) // Omega names only
Rezultāts no FILTER ir šāds masīvs:
("Jim";"Jim";"Carl";"Sue";"Carl")
Pēc tam funkcija UNIQUE tiek izmantota, lai noņemtu dublikātus:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
kā rezultātā tiek izveidots jauns šāds masīvs:
("Jim";"Carl";"Sue") // after UNIQUE
Šajā brīdī mums ir unikāls vārdu saraksts, kas saistīts ar Omega, un mums tie vienkārši jāuzskaita. Tālāk izskaidroto iemeslu dēļ mēs to darām ar funkciju LEN un SUM. Lai viss būtu skaidrs, vispirms mēs pārrakstīsim formulu, iekļaujot unikālo sarakstu:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Funkcija LEN iegūst katra saraksta vienuma garumu un atgriež garumu masīvu:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Pēc tam mēs pārbaudām, vai garumi ir lielāki par nulli:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
Izmantojiet dubultnegatīvu, lai piespiestu TRUE un FALSE vērtības uz 1s un 0s:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Visbeidzot, mēs summējam rezultātus ar funkciju SUM:
=SUM((1;1;1)) // returns 3
Šis masīvs tiek piegādāts tieši funkcijai COUNTA, kas atgriež galīgo skaitli:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Ņemiet vērā, ka, tā kā mēs pārbaudām katra vienuma garumu, ko atgriež UNIQUE, tukšās vai tukšās šūnas, kas atbilst kritērijiem, tiek ignorētas. Šī formula ir dinamiska un nekavējoties pārrēķinās, ja tiek mainīti avota dati.
Skaits unikāls ar vairākiem kritērijiem
Lai uzskaitītu unikālas vērtības, pamatojoties uz vairākiem kritērijiem, varat paplašināt loģiku "iekļaut" FILTER iekšpusē. Piemēram, lai tikai jūnijā uzskaitītu unikālos projekta Omega nosaukumus, izmantojiet:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Šis ir Būla loģikas izmantošanas piemērs vairāku nosacījumu piemērošanai. Pieeja sīkāk paskaidrota šeit.
Lai iegūtu sīkāku informāciju, skatiet šo mācību video: Kā filtrēt pēc vairākiem kritērijiem.
KONTA
Ir iespējams uzrakstīt vienkāršāku formulu, kas atbild uz funkciju COUNTA. Tomēr svarīgs brīdinājums ir tas, ka COUNTA atgriezīs vērtību 1, ja nebūs atbilstošu vērtību. Tas ir tāpēc, ka funkcija FILTER atgriež kļūdu, ja neviens no datiem neatbilst kritērijiem, un šo kļūdu galu galā ieskaita funkcija COUNTA. COUNTA pamata formula izskatās šādi:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Atkal šī formula atgriezīs 1, ja nebūs atbilstošu datu. Tajā tiks iekļautas arī tukšas šūnas, kas atbilst kritērijiem. Labāka iespēja ir formula, kuras pamatā ir LEN un SUM.
Nav dinamisku masīvu
Ja izmantojat vecāku Excel versiju bez dinamiskā masīva atbalsta, varat izmantot sarežģītāku formulu. Plašāku dinamisko masīvu alternatīvu apskatu skatiet: Dinamisko masīvu formulu alternatīvas.
