Általános képlet
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Összegzés
A sok dolog egyikével sem egyenlő cellák számításához használhat egy MATCH, ISNA és SUMPRODUCT függvényeken alapuló képletet. A bemutatott példában az F5 cellában szereplő képlet a következő:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
ahol az "adatok" a B5: B16 elnevezett tartomány, az "kizárás" pedig a D5: D7 nevű tartomány.
Magyarázat
Először is, egy kis kontextus. Normális esetben, ha csak néhány dolog van, amelyet nem szeretne megszámolni, használhatja a COUNTIFS-t, mint ez:
=COUNTIFS(range,"apple",range,"orange")
De ez nem nagyon skálázódik, ha van egy listája sok mindenről, mert hozzá kell adnia egy további tartomány / kritérium párokat minden olyan dologhoz, amelyet nem szeretne megszámolni. Sokkal könnyebb lenne egy listát összeállítani és a kritériumok részeként hivatkozást adni erre a listára. Pontosan ezt teszi az ezen az oldalon található képlet.
Lényegében ez a képlet a MATCH függvény segítségével keresi meg az "a", "b" vagy "c" értékkel nem egyenlő cellákat ezzel a kifejezéssel:
MATCH(data,exclude,0)
Ne feledje, hogy a keresési érték és a keresési tömb "megfordítva" van a normál konfigurációval szemben - a megnevezett "adatok" tartomány összes értékét keresési értékként adjuk meg, és megadunk minden olyan értéket, amelyet ki akarunk zárni a megnevezett tartományban "kizár". Mivel a MATCH-nak egynél több keresési értéket adunk meg, ezért egy ilyen tömbben több eredményt kapunk:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
Lényegében a MATCH megadja számunkra az egyező értékek helyzetét, és az összes többi értékre # N / A értéket ad vissza.
A # N / A eredmények azok, amelyek érdekelnek minket, mivel az "a", "b" vagy "c" értékkel nem egyenlő értékeket képviselnek. Ennek megfelelően az ISNA-t arra használjuk, hogy ezeket az értékeket IGAZra, a számokat pedig HAMISRA kényszerítsük:
ISNA(MATCH(data,exclude,0)
Ezután kettős negatív segítségével a TRUE-t 1-re, a FALSE-t nullára kényszerítjük. A kapott tömb a SUMPRODUCT belsejében így néz ki:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Csak egy tömb feldolgozásával a SUMPRODUCT összegzi és visszaadja a végeredményt, 4.
Megjegyzés: A SUMPRODUCT használata SUM helyett elkerüli a vezérlő + shift + Enter használatát.
Gróf mínusz meccs
A több dolog egyikével sem egyenlő cellák számlálásának másik módja az összes érték megszámlálása és az egyezések levonása. Ezt egy ilyen képlettel teheti meg:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Itt a COUNTA visszaadja az összes nem üres cella számát. A COUNTIF függvény, ha a megnevezett tartományt "kizárja", három számot ad vissza, egyet a lista minden eleméhez. A SUMPRODUCT összeadja az összeget, és ez a szám kivonásra kerül az összes nem üres cella számából. A végeredmény azon cellák száma, amelyek nem felelnek meg az értékeknek a "kizárás" mezőben.
A literál típuslogikát tartalmaz
Az ezen az oldalon található képlet az "egyenlő" logikával számít. Ha meg kell számlálnia azokat a cellákat, amelyek nem tartalmaznak sok karaktersorozatot, ahol az azt jelenti, hogy egy sztring bárhol megjelenhet a cellában, akkor összetettebb képletre lesz szüksége.
