Podsumowanie
Aby policzyć wiersze z logiką OR, możesz użyć formuły opartej na funkcji SUMPRODUCT. W przedstawionym przykładzie formuła w G6 to:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
gdzie grupa (B5: B15), kolor1 (C5: C15) i kolor2 (D5: D15) to nazwane zakresy.
Wyjaśnienie
Jednym z trudniejszych problemów w programie Excel jest zliczanie wierszy w zestawie danych za pomocą logiki „OR”. Istnieją dwa podstawowe scenariusze: (1) chcesz policzyć wiersze, w których wartość w kolumnie to „x” LUB „y” (2) chcesz policzyć wiersze, w których wartość „x” znajduje się w jednej kolumnie LUB w innej .
W tym przykładzie celem jest policzenie wierszy, w których grupa = „a” ORAZ Kolor1 LUB Kolor2 to „czerwony”. Oznacza to, że pracujemy ze scenariuszem 2 powyżej.
Z COUNTIFS
Na początku możesz sięgnąć po funkcję LICZ.WARUNKI, która natywnie obsługuje wiele kryteriów. Jednak funkcja LICZ.WARUNKI łączy warunki z logiką ORAZ, więc wszystkie kryteria muszą mieć wartość PRAWDA, aby zostały uwzględnione w zliczaniu:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
To sprawia, że COUNTIFS nie działa, chyba że używamy wielu wystąpień COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Tłumaczenie: policz wiersze, gdzie grupa to "a", a kolor1 to "czerwony" + policz wiersze, gdzie grupa to "a", a kolor2 to "czerwony" - policz wiersze, gdzie grupa to "a", a kolor1 to "czerwony", a kolor2 to " czerwony ”(aby uniknąć podwójnego liczenia).
To działa, ale widać, że jest to nieco skomplikowana i zbędna formuła.
Z logiką Boole'a
Lepszym rozwiązaniem jest użycie logiki Boole'a i przetworzenie wyniku za pomocą funkcji SUMPRODUCT. (Jeśli potrzebujesz elementarza z algebry Boole'a, ten film zawiera wprowadzenie). W pokazanym przykładzie wzór w G6 to:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
gdzie grupa (B5: B15), kolor1 (C5: C15) i kolor2 (D5: D15) to nazwane zakresy.
Pierwszą częścią problemu jest sprawdzenie dla group = "a", co robimy w ten sposób:
(group="a")
Ponieważ zakres B5: B15 zawiera 11 komórek, to wyrażenie zwraca tablicę 11 wartości PRAWDA i FAŁSZ w następujący sposób:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Każda wartość TRUE reprezentuje wiersz, w którym grupa jest „A”.
Następnie musimy sprawdzić wartość „czerwony” w kolumnie1 lub kolumna2. Robimy to za pomocą dwóch wyrażeń połączonych dodawaniem (+), ponieważ dodawanie odpowiada logice OR w algebrze Boole'a:
(color1="red")+(color2="red")
Excel automatycznie ocenia wartości PRAWDA i FAŁSZ jako 1 i 0 podczas dowolnej operacji matematycznej, więc wynik z powyższego wyrażenia to tablica taka:
(2;0;0;1;1;0;1;0;0;0;1)
Pierwsza liczba w tablicy to 2, ponieważ zarówno Kolor1, jak i Kolor2 są „czerwone” w pierwszym wierszu. Z powodów wyjaśnionych poniżej musimy ustrzec się przed tą sytuacją, sprawdzając wartości większe od zera:
((2;0;0;1;1;0;1;0;0;0;1))>0
Teraz znowu mamy tablicę wartości TRUE i FALSE:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Poniższa tabela podsumowuje, jak Excel ocenia logikę kolorów wyjaśnioną powyżej:
W tym momencie mamy wyniki z testowania Group = "a" w jednej tablicy:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Wyniki testów „czerwonego” w kolorze Color1 lub Color2 w innej tablicy:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Następnym krokiem jest połączenie tych dwóch tablic za pomocą logiki „AND”. Aby to zrobić, używamy mnożenia (*), ponieważ mnożenie odpowiada logice AND w algebrze Boole'a.
Po pomnożeniu dwóch tablic razem otrzymujemy pojedynczą tablicę 1 i 0, która jest dostarczana bezpośrednio do funkcji SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Funkcja SUMPRODUCT zwraca sumę liczb 2 jako wynik końcowy. To jest liczba wierszy, w których grupa = "a" ORAZ Kolor1 LUB Kolor2 to "czerwony".
Aby uniknąć podwójnego liczenia
Nie chcemy podwójnie liczyć wierszy, w których zarówno Kolor1, jak i Kolor2 są „czerwone”. Dlatego sprawdzamy wyniki z (color1 = "red") + (color2 = "red") pod kątem wartości większych niż zero w poniższym kodzie:
((color1="red")+(color2="red"))>0
Bez tego sprawdzenia 2 z pierwszego wiersza danych pojawiłyby się w końcowej tablicy i spowodowałyby, że formuła nieprawidłowo zwróciłaby 3 jako ostateczną liczbę.
Opcja FILTER
Jedną fajną rzeczą w logice boolowskiej jest to, że doskonale współpracuje z najnowszymi funkcjami programu Excel, takimi jak XLOOKUP i FILTER. Na przykład funkcja FILTER może używać dokładnie tej samej logiki opisanej powyżej, aby wyodrębnić pasujące wiersze:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Wynik funkcji FILTER to dwa wiersze spełniające kryteria, jak pokazano poniżej:
Jeśli chcesz dowiedzieć się więcej o tych nowych funkcjach, oferujemy przegląd i szkolenie wideo.
