Bendroji formulė
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Santrauka
Norėdami suskaičiuoti pagal kelis kriterijus, įskaitant logiką, kad NĖRA vieno iš kelių dalykų, galite naudoti funkciją SUMPRODUCT kartu su funkcijomis MATCH ir ISNA.
Parodytame pavyzdyje formulė G8 yra:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Kur „lytis“ yra pavadintas diapazonas C4: C12, o „grupė“ - pavadintas diapazonas D4: D12.
Pastaba: „MATCH“ ir „ISNA“ leidžia formulę lengvai keisti, kad būtų galima atlikti daugiau išskyrimų, nes galite lengvai išplėsti diapazoną įtraukdami papildomas reikšmes „NOT“.
Paaiškinimas
Pirmoji išraiška SUMPRODUCTS viduje tikrina vertes C stulpelyje „Lytis“, palyginti su F4 reikšme „Vyras“:
(gender=F4)
Rezultatas yra tokių TRUE FALSE reikšmių masyvas:
(TIKRA; NETIESA; TIESA; NETIESA; TIESA; TIESA; NETIESA; TIESA; NETIESA)
Kur TIESA atitinka „Vyrą“.
Antroji išraiška SUMPRODUCTS viduje tikrina D stulpelio „Grupė“ reikšmes pagal G4 reikšmes: G5, „A“ ir „B“. Šis testas atliekamas su MATCH ir ISNA taip:
ISNA(MATCH(group,G4:G5,0))
Funkcija MATCH naudojama siekiant suderinti visas nurodyto diapazono „grupės“ reikšmes su G4 reikšmėmis: G5, „A“ ir „B“. Jei rungtynės pavyksta, MATCH grąžina skaičių. Jei MATCH nepavyksta, MATCH grąžina # N / A. Rezultatas yra toks masyvas:
(1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A)
Kadangi # N / A reikšmės atitinka „ne A ar B“, ISNA naudojama masyvui „pakeisti“ į:
(FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE)
Dabar TRUE atitinka „ne A ar B“.
SUMPRODUCT viduje du masyvo rezultatai padauginami kartu, o tai sukuria vieną skaitinę masyvą SUMPRODUCT viduje:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
Tada SUMPRODUCT pateikia sumą 2, nurodančią „2 patinus, nepriklausančius A ar B grupei“.
