Funkcja filtruj to prawdziwy game changer jeśli chodzi o pracę z danymi w programie MS Excel. Niestety jest ona dostępna tylko dla subskrybentów Office 365.
Jak działa funkcja filtruj?
Funkcja ta działa podobnie jak klasyczne filtrowanie w tabelach, jednakże kryteria po których chcemy filtrować możemy wpisać do komórek arkusza. Nie trzeba klikać za każdym razem w ikonkę filtra przy nagłówku tabeli.
Działanie funkcji jest podobne do działania X.WYSZUKAJ() oraz WYSZUKAJ.PIONOWO jednak jest jedna zasadnicza różnica- funkcja FILTRUJ() może wyświetlać wiele dopasowanych wyników.
Składnia funkcji
=FILTRUJ(B:D;E:E=J2;””)
=FILTRUJ(B:D [Zakres- kolumny które mają się wyświetlić];E:E=J2 [Kryteria- szuka wartości z komórki J2 w kolumnie E];”” [co ma być wynikiem w wypadku nieznalezienia pasujących wyników])
No bardzo fajnie, ale co dalej?
Dalej już jest tylko lepiej, ponieważ funkcję tą możemy łączyć z wieloma innymi, co czyni ją potężnym narzędziem.
Częściowe dopasowanie
W przykładzie ze składni funkcja szuka nam dopasowania dokładnego, a co jeśli chcemy tylko częściowe dopasowanie? Tutaj sprawa też jest prosta, choć wymaga odrobinę większego wysiłku. W tym przypadku użyjemy 2 funkcji: CZY.LICZBA() oraz ZNAJDŹ(). Ta pierwsza zwraca PRAWDA gdy wartość w nawiasie jest liczbą, a druga numer znaku w wyrazie od którego występuje dana fraza. Zastosowanie ich razem w funkcji FILTRUJ() znajduje się w poniższym przykładzie:
=FILTRUJ(E:E;CZY.LICZBA(ZNAJDŹ(J2;E:E));””)
Jak widać drugi argument to nie jest prosty warunek ze znakiem „=” lecz wypisane wyżej funkcje. Żeby zrozumieć zasadę działania trzeba prześledzić na jakiej zasadzie działa funkcja FILTRUJ(). Gdy funkcja w drugim argumencie (kryteria) znajdzie dopasowaną wartość w szukanej kolumnie, przypisuje tej komórce wartość PRAWDA. Tylko komórki z wartością prawda są wyświetlane.
Właśnie dlatego musimy użyć funkcji CZY.LICZBA() razem ze ZNAJDŹ(). Przyjrzyjmy się temu na przykładzie.
gdy szukamy np. „a” w wyrazie jabłko, to funkcja znajdź wygląda następująco: ZNAJDŹ(„a”;”jabłko”) i zwróci liczbę 2, gdyż „a” występuje na 2 miejscu w tym wyrazie. Wtedy funkcja CZY.LICZBA(ZNAJDŹ(„a”;”jabłko”)) zwróci wartość PRAWDA. Tak samo działa to dla komórek i zakresów. Funkcja ta każdej komórce z zakresu przypisuje wartość Prawda lub Fałsz i funkcja FILTRUJ() wie jakie wiersze ma wyświetlić.
Transponuj oraz Połącz teksty
Jak można było zauważyć funkcja filtruj może zająć sporo komórek w arkuszu i w dodatku, gdy funkcja będzie zmuszona wyświetlać wynik na komórce w której już jest jakaś inna wartość, nastąpi tzw. rozlanie i wyniki nie zostaną wyświetlone. Zdarza się to szczególnie wtedy, gdy mamy jakąś listę kryteriów i przeciągamy funkcję np. w dół żeby przefiltrować zakres po każdej z tych wartości. W takiej sytuacji gdy mamy więcej wyników niż 1, gdy będziemy przeciągać funkcję w dół, to w każdej zamiast wyników ujrzymy błąd #ROZLANIE. Na szczęście istnieją sposoby by dostać wyniki.
Transponuj
Jednym ze sposobów jest użycie funkcji transponuj
=TRANSPONUJ(FILTRUJ(E:E;CZY.LICZBA(ZNAJDŹ(J2;E:E));””))
Sprawdza się szczególnie wtedy gdy gdy jako zakres mamy 1 kolumnę. Wtedy wynik zamiast w pionie, jest wyświetlany w poziomie. W ten sposób unikniemy rozlania pod warunkiem że w kolejnych komórkach w tym samym wierszu nie mamy zapisanych wartości, gdyby jednak okazało się, że mamy wolną tylko 1 komórkę lub chcemy mieć wszystkie wyniki skompresowane w 1 komórce, z pomocą przychodzi funkcja POŁĄCZ.TEKSTY()
Połącz teksty
Jest to jedna z moich ulubionych funkcji do łączenia tekstów w komórkach gdyż pozwala pomijać puste komórki. jej składnia wygląda następująco
=POŁĄCZ.TEKSTY(ZNAK(10);PRAWDA;FILTRUJ(E:E;CZY.LICZBA(ZNAJDŹ(J2;E:E));””))
Pierwszy argument to znak, który ma łączyć teksty. Może być to np. spacja, w takim przypadku wystarczy wpisać ” ” lub myślnik „-„.W tym przypadku chcę, by wyniki były oddzielone enterem i mogę to uzyskać właśnie przy pomocy funkcji ZNAK(10), która zwraca znak ANSI po podanym numerze, 10 to enter. Kolejny argument pyta czy pomijać puste komórki, ja chcę je pominąć, więc piszę PRAWDA. Gdy nasz zakres to tylko jedna kolumna, wszystkie wyniki uzyskamy w jednej komórce. Wystarczy teraz włączyć zawijanie wierszy i już wszystko będzie klarownie widoczne.
Można też użyć funkcji POŁĄCZ.TEKSTY() gdy w zakresie mamy więcej niż 1 kolumnę
Filtrowanie po więcej niż 1 kolumnie i wielu kryteriach
Jest to jak najbardziej możliwe w funkcji FILTRUJ(). Łącznikami takimi są znak + (w funkcji oznaczający logiczne OR) oraz * (logiczne AND).
=FILTRUJ(E:G;(E:E=J2) + (E:E=J3) + (E:E=J4)) * (F:F>J5);””)
W powyższym przykładzie znajdujemy wartości równe J2, J3 i J4 (połączone + czyli logicznym OR) w zakresie kolumny E lub wartości większe od J5 w kolumnie F. Należy zwrócić szczególną uwagę na ułożenie nawiasów. Każde z kryteriów znajduje się w jednym i dodatkowo Całe wyrażenie LUB jest w nawiasie co oznacza, że ostatnie kryterium F:F>J5 będzie się odnosiło do każdego z osobna. Gdyby nawias okalający wszystkie kryteria LUB nie był obecny, wtedy warunek AND odnosiłby się tylko do ostatniego kryterium E:E=J4.
Dlaczego w wynikach nie ma Potato Chips? Ponieważ nie sprzedano nigdy więcej niż 58 sztuk
Jednym z kryteriów może być również omawiane wcześniej filtrowanie tylko po częściowym dopasowaniu (z użyciem CZY.LICZBA(ZNAJDŹ())).
W powyższym przykładzie należy zwrócić uwagę na nawiasy w kryteriach funkcji FILTRUJ().
Sanityzacja wyników
Może być tak, że kilka wyników z filtrowania może przynieść te same wartości. Wówczas można użyć funkcji UNIKATOWE(), która jak nazwa wskazuje wyświetla tylko unikatowe wartości.
=POŁĄCZ.TEKSTY(ZNAK(10);PRAWDA;UNIKATOWE(FILTRUJ(E:E;CZY.LICZBA(ZNAJDŹ(J2;E:E));””)))
Funkcję UNIKATOWE() zamieszczać należy zaraz przed funkcją filtruj. Bardzo dobrze sprawdza się ona razem z funkcją POŁĄCZ.TEKSTY()
Jest wiele możliwości
Kolejnym przykładem wykorzystania funkcji filtruj jest funkcja MAX(), która zwraca tylko najwyższą wartość z wybranych kolumn (w przypadku kolumn tekstowych zależy od pierwszej litery)
=MAX(FILTRUJ(F:F;(E:E=J2) + (E:E=J3) + (E:E=J4) * (F:F>J5) ;””))
Zwróć uwagę w powyższym przykładzie, że funkcja bierze maksymalną wartość tylko z wypisanych produktów (akurat tutaj Whole Wheat ma ogólnie najwyższą). Dodatkowo ostatni argument jest trochę bez sensu, ale bierze pod uwagę tylko ilości Potato Chips (a nie wszystkich artykułów, patrz nawiasy), które sprzedały się w ilości powyżej 58.
Man nadzieję, że po przeczytaniu tego wpisu przyjdzie Ci na myśl jak możesz wykorzystać potencjał funkcji FILTRUJ() w swojej pracy. Zachęcam Cię też do samodzielnego poszukiwania najbardziej przydatnej konfiguracji tej funkcji i próbowania łączenia jej z innymi. Do zobaczenia w następnym wpisie!
