Összegzés
VAGY logikájú sorok számlálásához használhatja a SUMPRODUCT függvényen alapuló képletet. A bemutatott példában a G6 képlete a következő:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
ahol Csoport (B5: B15), color1 (C5: C15), és COLOR2 (D5: D15) nevezzük tartományok.
Magyarázat
Az Excel egyik bonyolultabb problémája az, hogy "OR logikával" megszámolja az adatsor sorait. Két alapvető forgatókönyv létezik: (1) meg akarja számolni azokat a sorokat, ahol egy oszlop értéke "x" VAGY "y" (2) meg akarja számolni azokat a sorokat, ahol az "x" érték az egyik oszlopban VAGY egy másikban van .
Ebben a példában az a cél, hogy megszámoljuk azokat a sorokat, ahol a group = "a" ÉS a Color1 VAGY a Color2 "piros". Ez azt jelenti, hogy a fenti 2. forgatókönyvvel dolgozunk.
COUNTIFS-szel
Először elérheti a COUNTIFS függvényt, amely több kritériumot natív módon kezel. A COUNTIFS függvény azonban egyesíti a feltételeket az ÉS logikával, ezért minden feltételnek IGAZnak kell lennie ahhoz, hogy szerepeljen a számlálásban:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Ez a COUNTIFS működésképtelenné teszi, kivéve, ha a COUNTIFS több példányát használjuk:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Fordítás: számlálja azokat a sorokat, ahol a csoport "a" és a color1 "piros" + számlálja azokat a sorokat, ahol a csoport "a" és a color2 "piros" - számlálja azokat a sorokat, ahol a csoport "a", a color1 "piros" és a color2 " piros "(a kettős számlálás elkerülése érdekében).
Ez működik, de láthatja, hogy ez egy kissé bonyolult és felesleges képlet.
Logikai logikával
Jobb megoldás a logikai logika használata, és az eredmény feldolgozása a SUMPRODUCT függvénnyel. (Ha primerre van szüksége a logikai algebrán, akkor ez a videó egy bevezetőt tartalmaz.) A bemutatott példában a G6 képlete a következő:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
ahol Csoport (B5: B15), color1 (C5: C15), és COLOR2 (D5: D15) nevezzük tartományok.
A probléma első része a group = "a" tesztelése, amit így csinálunk:
(group="a")
Mivel a B5: B15 tartomány 11 cellát tartalmaz, ez a kifejezés 11 IGAZ és HAMIS érték tömböt ad vissza, így:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Minden IGAZ egy sort jelöl, ahol a csoport "A".
Ezután ellenőriznünk kell a "piros" értéket az 1. vagy a 2. oszlopban. Ezt két, összeadással (+) összekapcsolt kifejezéssel tesszük, mivel az összeadás megfelel az OR logikának a Boolean algebrában:
(color1="red")+(color2="red")
Az Excel minden matematikai művelet során automatikusan kiértékeli az IGAZ és HAMIS értékeket 1-ként és 0-ként, így a fenti kifejezés eredménye egy ilyen tömb:
(2;0;0;1;1;0;1;0;0;0;1)
A tömb első száma 2, mert a Szín1 és a Szín2 egyaránt "piros" az első sorban. Az alábbiakban ismertetett okokból nulla feletti értékek ellenőrzésével kell megvédenünk ezt a helyzetet:
((2;0;0;1;1;0;1;0;0;0;1))>0
Most megint van egy TRUE és FALSE értéktömb:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Az alábbi táblázat összefoglalja, hogyan értékeli az Excel a fentiekben kifejtett színlogikát:
Ezen a ponton eredményeink vannak a Group = "a" tesztelésével egy tömbben:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
A "piros" tesztelésének eredménye a Color1 vagy a Color2 más tömbben:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
A következő lépés az, hogy ezt a két tömböt összekapcsoljuk az "ÉS logikával". Ehhez szorzást (*) használunk, mivel a szorzás az logikai algebra AND logikájának felel meg.
A két tömb összeszorzása után egyetlen 1s és 0s tömböt kapunk, amelyet közvetlenül a SUMPRODUCT függvényhez juttatunk el:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
A SUMPRODUCT függvény a számok összegét (2) adja vissza végeredményként. Ez azoknak a soroknak a száma, ahol a group = "a" ÉS a Color1 VAGY a Color2 "piros".
A kettős számlálás elkerülése érdekében
Nem akarjuk megduplázni azokat a sorokat, ahol a Color1 és a Color2 egyaránt "piros". Ezért ellenőrizzük a (color1 = "red") + (color2 = "red") eredményeit nullánál nagyobb értékekre az alábbi kódban:
((color1="red")+(color2="red"))>0
Ezen ellenőrzés nélkül az adatok első sorának 2-je megjelenik az utolsó tömbben, és a képlet helytelenül adja vissza a 3-at végső számlálásként.
SZŰRŐ opció
A logikai logika egyik szép tulajdonsága, hogy tökéletesen működik az Excel legújabb funkcióival, például az XLOOKUP és a FILTER. Például a FILTER függvény pontosan ugyanazt a fentiekben ismertetett logikát használhatja az egyező sorok kibontásához:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
A FILTER eredménye a két sor, amely megfelel az alábbi kritériumoknak:
Ha többet szeretne megtudni ezekről az új funkciókról, áttekintést és videotanfolyamot tartunk.
