Podsumowanie
Aby filtrować i wyodrębniać dane w oparciu o wiele złożonych kryteriów, można użyć funkcji FILTER z łańcuchem wyrażeń korzystających z logiki boolowskiej. W pokazanym przykładzie formuła w G5 to:
=FILTER(B5:E16,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4))
Ta formuła zwraca dane, gdzie:
konto zaczyna się od „x” ORAZ region to „wschód”, a miesiącem NIE jest kwiecień.
Wyjaśnienie
W tym przykładzie musimy skonstruować logikę filtrującą dane tak, aby zawierała:
konto zaczyna się od „x” ORAZ region to „wschód”, a miesiącem NIE jest kwiecień.
Logika filtrowania tej formuły (argument włączania) jest tworzona przez połączenie trzech wyrażeń, które używają logiki boolowskiej na tablicach w danych. Pierwsze wyrażenie używa funkcji LEFT, aby sprawdzić, czy konto zaczyna się od „x”:
LEFT(B5:B16)="x" // account begins with "x"
Wynikiem jest tablica wartości TRUE FALSE, jak ta:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
Drugie wyrażenie sprawdza, czy Region ma wartość „wschód” z operatorem równości (=):
C5:C16="east" // region is east
Wynik jest kolejną tablicą:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
Trzecie wyrażenie używa funkcji MIESIĄC z funkcją NIE, aby sprawdzić, czy miesiącem nie jest kwiecień:
NOT(MONTH(D5:D16)=4) // month is not april
co daje:
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Zauważ, że funkcja NOT odwraca wynik z wyrażenia MIESIĄC.
Wszystkie trzy tablice są mnożone razem. Operacja matematyczna wymusza przekształcenie wartości PRAWDA i FAŁSZ na 1 i 0, więc w tym momencie możemy wizualizować argument include w następujący sposób:
(1;0;1;1;1;0;0;0;1;1;0;1)* (0;0;1;1;1;0;1;0;0;1;0;1)* (0;0;0;1;1;1;1;1;1;1;1;1)
Mnożenie boolowskie odpowiada funkcji logicznej AND, więc ostatecznym wynikiem jest pojedyncza tablica, taka jak ta:
(0;0;0;1;1;0;0;0;0;1;0;1)
Funkcja FILTER używa tej tablicy do filtrowania danych i zwraca cztery wiersze, które odpowiadają jedynkom w tablicy.
Kryteria rozszerzające
Wyrażenia używane do tworzenia argumentu włączania w filtrze można w razie potrzeby rozszerzyć, aby obsługiwały jeszcze bardziej złożone filtry. Na przykład, aby dalej filtrować dane w celu uwzględnienia tylko wierszy, w których kwota> 10000, możesz użyć następującej formuły:
=FILTER(B5:E16,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4)*(E5:E16>10000))
