Bendroji formulė
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Santrauka
Norėdami suskaičiuoti unikalias reikšmes su viena ar daugiau sąlygų, galite naudoti formulę, pagrįstą UNIKALU ir FILTRU. Parodytame pavyzdyje formulė H7 yra:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
kuri grąžina 3, nes B6 yra trys unikalūs pavadinimai: B15, susieti su „Omega“ projektu.
Pastaba: šiai formulei reikalingos dinaminio masyvo formulės, prieinamos tik „Excel 365“. Su senesne „Excel“ versija galite naudoti sudėtingesnes alternatyvias formules.
Paaiškinimas
Iš esmės ši formulė naudoja funkciją UNIKALA, kad išgautų unikalias vertes, o funkcija FILTRAS taiko kriterijus.
Dirbant iš vidaus, funkcija FILTER naudojama norint taikyti kriterijus ir išskleisti tik tuos pavadinimus, kurie yra susieti su „Omega“ projektu:
FILTER(B6:B15,C6:C15=H6) // Omega names only
FILTER rezultatas yra toks masyvas:
("Jim";"Jim";"Carl";"Sue";"Carl")
Tada UNIQUE funkcija naudojama dublikatams pašalinti:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
kurio rezultatas yra toks masyvas:
("Jim";"Carl";"Sue") // after UNIQUE
Šiuo metu turime unikalų vardų, susietų su „Omega“, sąrašą, ir mes tiesiog turime juos suskaičiuoti. Dėl žemiau paaiškintų priežasčių tai darome naudodami funkciją LEN ir SUM. Kad viskas būtų aišku, pirmiausia perrašysime formulę, kad įtrauktumėte unikalų sąrašą:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Funkcija LEN gauna kiekvieno sąrašo elemento ilgį ir pateikia ilgių masyvą:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Tada mes patikriname, ar ilgiai yra didesni nei nulis:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
Jei naudojate TRUE ir FALSE reikšmes į 1s ir 0s, naudokite dvigubą neigiamą:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Galiausiai susumuojame rezultatus su funkcija SUM:
=SUM((1;1;1)) // returns 3
Šis masyvas pristatomas tiesiai į funkciją COUNTA, kuri pateikia galutinį skaičių:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Atminkite, kad tikrindami kiekvieno elemento, kurį grąžina UNIKALUS, ilgį, tušti arba tušti langeliai, atitinkantys kriterijus, nepaisomi. Ši formulė yra dinamiška ir bus nedelsiant perskaičiuota, jei bus pakeisti šaltinio duomenys.
Skaičiuokite unikalų pagal kelis kriterijus
Norėdami suskaičiuoti unikalias reikšmes pagal kelis kriterijus, galite išplėsti „įtraukti“ logiką FILTER viduje. Pavyzdžiui, jei norite suskaičiuoti unikalius „Omega“ projekto pavadinimus tik birželio mėnesį, naudokite:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Tai yra loginės logikos naudojimo kelioms sąlygoms taikyti pavyzdys. Metodas išsamiau paaiškintas čia.
Norėdami gauti daugiau informacijos, žiūrėkite šį mokymo vaizdo įrašą: Kaip filtruoti pagal kelis kriterijus.
COUNTA
Galima parašyti paprastesnę formulę, kuri atsako į funkciją COUNTA. Tačiau svarbus įspėjimas yra tas, kad COUNTA grąžins 1, kai nebus atitinkamų verčių. Taip yra todėl, kad funkcija FILTRAS pateikia klaidą, kai jokie duomenys neatitinka kriterijų, ir šią klaidą galiausiai skaičiuoja funkcija COUNTA. Pagrindinė COUNTA formulė atrodo taip:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Vėlgi, ši formulė grąžins 1, kai nebus atitinkamų duomenų. Taip pat bus tušti langeliai, atitinkantys kriterijus. Formulė, pagrįsta LEN ir SUM, yra geresnis pasirinkimas.
Nėra dinaminių masyvų
Jei naudojate senesnę „Excel“ versiją be dinaminio masyvo palaikymo, galite naudoti sudėtingesnę formulę. Norėdami išsamiau aptarti dinaminių masyvų alternatyvas, žr .: Dinaminių masyvų formulių alternatyvos.
