Všeobecný vzorec
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Zhrnutie
Ak chcete počítať s viacerými kritériami, vrátane logiky pre NIE jednu z niekoľkých vecí, môžete použiť funkciu SUMPRODUCT spolu s funkciami MATCH a ISNA.
V zobrazenom príklade je vzorec v G8:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Kde „pohlavie“ je pomenovaný rozsah C4: C12 a „skupina“ je pomenovaný rozsah D4: D12.
Poznámka: MATCH a ISNA umožňujú ľahkému škálovaniu vzorca, aby zvládol viac vylúčení, pretože rozsah môžete ľahko rozšíriť tak, aby obsahoval ďalšie hodnoty „NIE“.
Vysvetlenie
Prvý výraz vo vnútri SUMPRODUCTS testuje hodnoty v stĺpci C, Pohlavie, oproti hodnote vo F4, „Muž“:
(gender=F4)
Výsledkom je pole TRUE FALSE hodnôt, ako je tento:
(PRAVDA; NEPRAVDA; PRAVDA; NEPRAVDA; PRAVDA; PRAVDA; NEPRAVDA; PRAVDA; NEPRAVDA)
Kde TRUE zodpovedá výrazu „muž“.
Druhý výraz vo vnútri SUMPRODUCTS testuje hodnoty v stĺpci D, skupina, oproti hodnotám v G4: G5, „A“ a „B“. Tento test sa spracováva s MATCH a ISNA takto:
ISNA(MATCH(group,G4:G5,0))
Funkcia MATCH sa používa na priradenie každej hodnoty v pomenovanom rozsahu „skupina“ k hodnotám v G4: G5, „A“ a „B“. Ak je zápas úspešný, MATCH vráti číslo. Ak MATCH zlyhá, MATCH vráti # N / A. Výsledkom je pole ako je toto:
(1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A)
Pretože # N / A hodnoty zodpovedajú „nie A alebo B“, ISNA sa používa na „obrátenie“ poľa k:
(FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE)
Teraz TRUE zodpovedá „nie A alebo B“.
Vo vnútri SUMPRODUCT sa výsledky dvoch polí vynásobia, čím sa vo vnútri SUMPRODUCT vytvorí jedno číselné pole:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
SUMPRODUCT potom vráti súčet 2, čo predstavuje „2 muži, ktorí nie sú v skupine A alebo B“.
