Zhrnutie
Ak chcete počítať riadky pomocou logiky ALEBO, môžete použiť vzorec založený na funkcii SUMPRODUCT. V zobrazenom príklade je vzorec v G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
kde skupina (B5: B15), farba1 (C5: C15) a farba2 (D5: D15) sú pomenované rozsahmi.
Vysvetlenie
Jedným z zložitejších problémov v programe Excel je spočítať riadky v množine údajov s logikou OR. Existujú dva základné scenáre: (1) chcete spočítať riadky, ktorých hodnota v stĺpci je „x“ ALEBO „y“ (2), chcete spočítať riadky, kde hodnota „x“ existuje v jednom stĺpci ALEBO inom .
V tomto príklade je cieľom spočítať riadky, kde skupina = „a“ A Farba1 ALEBO Farba2 sú „červené“. To znamená, že pracujeme s vyššie uvedeným scenárom 2.
S COUNTIFS
Najskôr by ste mohli siahnuť po funkcii COUNTIFS, ktorá natívne spracováva viaceré kritériá. Funkcia COUNTIFS však spája podmienky s logikou AND, takže do počtu musia byť zahrnuté všetky kritériá TRUE:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Toto robí COUNTIFS nepoužiteľným, pokiaľ nepoužívame viac inštancií COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Preklad: počítať riadky, kde skupina je „a“ a farba1 je „červená“ + počítať riadky, kde skupina je „a“ a farba2 je „červená“ - počítať riadky, kde skupina je „a“ a farba1 je „červená“ a farba2 je „ červená “(aby sa zabránilo dvojitému započítaniu).
Toto funguje, ale môžete vidieť, že ide o trochu komplikovaný a nadbytočný vzorec.
S logickou logikou
Lepším riešením je použiť logickú logiku a výsledok spracovať pomocou funkcie SUMPRODUCT. (Ak potrebujete primer na booleovskú algebru, toto video predstavuje úvod.) V zobrazenom príklade je vzorec v G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
kde skupina (B5: B15), farba1 (C5: C15) a farba2 (D5: D15) sú pomenované rozsahmi.
Prvou časťou problému je testovanie skupiny = "a", ktorú robíme takto:
(group="a")
Pretože rozsah B5: B15 obsahuje 11 buniek, tento výraz vráti pole 11 TRUE a FALSE hodnôt, ako je tento:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Každá TRUE predstavuje riadok, kde je skupina „A“.
Ďalej musíme skontrolovať hodnotu „červená“ buď v stĺpci1, alebo v stĺpci2. Robíme to pomocou dvoch výrazov spojených sčítaním (+), pretože sčítanie zodpovedá logike OR v booleovskej algebre:
(color1="red")+(color2="red")
Excel počas akejkoľvek matematickej operácie automaticky vyhodnotí TRUE a FALSE hodnoty ako 1 s a 0 s, takže výsledkom z vyššie uvedeného výrazu je pole podobné tomuto:
(2;0;0;1;1;0;1;0;0;0;1)
Prvé číslo v poli je 2, pretože Color1 aj Color2 sú v prvom riadku „červené“. Z dôvodov vysvetlených nižšie sa musíme chrániť pred touto situáciou kontrolou hodnôt vyšších ako nula:
((2;0;0;1;1;0;1;0;0;0;1))>0
Teraz máme opäť pole hodnôt TRUE a FALSE:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
V nasledujúcej tabuľke je zhrnuté, ako program Excel vyhodnocuje logiku farieb vysvetlenú vyššie:
V tomto okamihu máme výsledky testovania skupiny = "a" v jednom poli:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
A výsledky testovania „červenej“ farby Color1 alebo Color2 v inom poli:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Ďalším krokom je spojenie týchto dvoch polí s logikou AND. Použijeme na to násobenie (*), pretože násobenie zodpovedá logike AND v booleovskej algebre.
Po vynásobení dvoch polí dohromady máme jediné pole 1 s a 0 s, ktoré sa doručí priamo do funkcie SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Funkcia SUMPRODUCT vráti súčet čísel 2 ako konečný výsledok. Toto je počet riadkov, kde skupina = "a" A Farba1 ALEBO Farba2 sú "červené".
Aby sa zabránilo dvojitému započítaniu
Nechceme zdvojnásobiť počet riadkov, kde sú Color1 aj Color2 „červené“. Preto v nižšie uvedenom kóde kontrolujeme výsledky z (color1 = "červená") + (color2 = "červená") na hodnoty väčšie ako nula:
((color1="red")+(color2="red"))>0
Bez tejto kontroly by sa 2 z prvého riadku v údajoch zobrazili v konečnom poli a spôsobili by, že vzorec nesprávne vráti 3 ako konečný počet.
Možnosť FILTER
Jedna príjemná vec na logickej logike je, že funguje perfektne s najnovšími funkciami programu Excel, ako sú XLOOKUP a FILTER. Napríklad funkcia FILTER môže na extrahovanie zhodných riadkov použiť presne tú istú logiku, ktorá je vysvetlená vyššie:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Výsledkom programu FILTER sú dva riadky, ktoré zodpovedajú kritériám, ako je uvedené nižšie:
Ak sa chcete dozvedieť viac informácií o týchto nových funkciách, máme prehľad a video školenie.
