souhrn
Chcete-li počítat řádky pomocí logiky OR, můžete použít vzorec založený na funkci SUMPRODUCT. V zobrazeném příkladu je vzorec v G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
kde skupina (B5: B15), barva1 (C5: C15) a barva2 (D5: D15) jsou pojmenovány rozsahy.
Vysvětlení
Jedním ze složitějších problémů aplikace Excel je počítat řádky v sadě dat s logikou „NEBO logikou“. Existují dva základní scénáře: (1) chcete spočítat řádky, kde hodnota ve sloupci je „x“ NEBO „y“ (2) chcete počítat řádky, kde hodnota „x“ existuje v jednom sloupci NEBO jiném .
V tomto příkladu je cílem spočítat řádky, kde group = "a" AND Color1 NEBO Color2 jsou "červené". To znamená, že pracujeme se scénářem 2 výše.
S COUNTIFS
Nejprve můžete dosáhnout funkce COUNTIFS, která nativně zpracovává více kritérií. Funkce COUNTIFS však spojuje podmínky s logikou AND, takže všechna kritéria musí být TRUE, aby byla zahrnuta do počtu:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Díky tomu je COUNTIFS nepoužitelný, pokud nepoužíváme více instancí COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Překlad: spočítat řádky, kde skupina je „a“ a barva1 je „červená“ + počítat řádky, kde skupina je „a“ a barva2 je „červená“ - počítat řádky, kde skupina je „a“ a barva1 je „červená“ a barva2 je „ červená "(aby nedošlo k dvojímu započítání).
To funguje, ale můžete vidět, že se jedná o poněkud komplikovaný a nadbytečný vzorec.
S logickou logikou
Lepším řešením je použít logickou logiku a výsledek zpracovat pomocí funkce SUMPRODUCT. (Pokud potřebujete základní nátěr na booleovskou algebru, toto video poskytuje úvod.) V ukázaném příkladu je vzorec v G6:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
kde skupina (B5: B15), barva1 (C5: C15) a barva2 (D5: D15) jsou pojmenovány rozsahy.
První částí problému je otestovat skupinu = "a", kterou děláme takto:
(group="a")
Protože rozsah B5: B15 obsahuje 11 buněk, vrátí tento výraz pole 11 TRUE a FALSE hodnot, jako je tato:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Každá TRUE představuje řádek, kde je skupina „A“.
Dále musíme zkontrolovat hodnotu „červená“ buď ve sloupci1, nebo ve sloupci2. Děláme to se dvěma výrazy spojenými sčítáním (+), protože sčítání odpovídá logice OR v booleovské algebře:
(color1="red")+(color2="red")
Excel během jakékoli matematické operace automaticky vyhodnotí TRUE a FALSE hodnoty jako 1 s a 0 s, takže výsledkem výše uvedeného výrazu je pole jako toto:
(2;0;0;1;1;0;1;0;0;0;1)
První číslo v poli je 2, protože Color1 i Color2 jsou „červené“ v prvním řádku. Z níže vysvětlených důvodů se musíme proti této situaci chránit kontrolou hodnot větších než nula:
((2;0;0;1;1;0;1;0;0;0;1))>0
Nyní máme znovu řadu TRUE a FALSE hodnot:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Níže uvedená tabulka shrnuje, jak Excel vyhodnocuje barevnou logiku vysvětlenou výše:
V tomto okamžiku máme výsledky testování Group = "a" v jednom poli:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
A výsledky testování „červené“ v Color1 nebo Color2 v jiném poli:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Dalším krokem je sloučení těchto dvou polí s logikou AND. K tomu použijeme násobení (*), protože násobení odpovídá logice AND v booleovské algebře.
Po vynásobení dvou polí dohromady máme jediné pole 1 s a 0 s, které je doručeno přímo do funkce SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Funkce SUMPRODUCT vrací součet čísel 2 jako konečný výsledek. Toto je počet řádků, kde group = "a" AND Color1 NEBO Color2 jsou "červené".
Aby se zabránilo dvojímu započítání
Nechceme zdvojnásobit počet řádků, kde jsou Color1 i Color2 „červené“. Proto v níže uvedeném kódu kontrolujeme výsledky z (color1 = "red") + (color2 = "red") na hodnoty větší než nula:
((color1="red")+(color2="red"))>0
Bez této kontroly by se 2 z prvního řádku v datech zobrazily v konečném poli a způsobily, že vzorec nesprávně vrátí 3 jako konečný počet.
Možnost FILTER
Jedna hezká věc na logické logice je, že funguje perfektně s nejnovějšími funkcemi aplikace Excel, jako je XLOOKUP a FILTER. Například funkce FILTER může použít přesně stejnou logiku, jak je vysvětleno výše, k extrakci odpovídajících řádků:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Výsledkem FILTRU jsou dva řádky, které splňují kritéria, jak je vidět níže:
Pokud se chcete o těchto nových funkcích dozvědět více, máme přehled a video školení.
