Funkcja FILTR w programie Microsoft Excel jest jedną z najważniejszych funkcji, które należy opanować. Bez tego trudno będzie znaleźć potrzebne dane. Oto przyspieszony kurs korzystania z FILTRA w programie Excel.
Warto również pamiętać, że funkcja ta nie jest jedynym sposobem filtrowania danych w programie MS Excel. Do osiągnięcia tego samego celu służą narzędzia takie jak filtr automatyczny i filtr zaawansowany, z pewnymi ważnymi zastrzeżeniami, które omówimy w tym przewodniku.
Co to jest funkcja FILTR?
Funkcje lub formuły Excela to podstawa programu Excel, umożliwiająca wykonywanie takich czynności, jak znajdowanie średniej z dużego zbioru danych lub tworzenie wykresu krzywej Bella. Każda funkcja ma swoją składnię, którą zazwyczaj można sprawdzić, wpisując nazwę funkcji w programie Excel.
Funkcja Excel FILTER, jak sama nazwa wskazuje, służy do „filtrowania” wartości określonego zakresu według określonych warunków. Za pomocą tej funkcji wprowadza się zarówno zakres, jak i warunki, które mają zostać użyte, co czyni ją niezwykle konfigurowalną.
Dzięki właściwym parametrom możesz wyodrębnić z arkusza kalkulacyjnego dokładnie te informacje, których potrzebujesz, bez konieczności ręcznego przeglądania całości w celu znalezienia pasujących wpisów. A ponieważ dane wyjściowe znajdują się w komórce, możesz połączyć więcej funkcji w celu wykonania obliczeń lub wizualizacji wyników na wykresie.
Dlaczego funkcja FILTR jest preferowana zamiast funkcji FILTR Filtr zaawansowany?
Większość Początkujący Excel korzysta z wbudowanych narzędzi do filtrowania danych w programie Excel, zamiast próbować poznać składnię funkcji. Filtr automatyczny jest najłatwiejszy i umożliwia wykluczanie kolumn i ustawianie kryteriów filtrowania w interfejsie opartym na menu. Następnie dostępny jest filtr zaawansowany z możliwością zastosowania wielu kryteriów w celu wdrożenia złożonych schematów filtrowania.
Więc po co w ogóle zawracać sobie głowę funkcją FILTER?
Główną zaletą korzystania z funkcji programu Excel w porównaniu z ręcznym wykonywaniem jakichkolwiek operacji (za pomocą innego narzędzia programu Excel lub nawet dowolnego innego programu) jest to, że funkcje są dynamiczne. Filtr Auto lub Filtr Zaawansowany daje jednorazowe wyniki, które nie zmieniają się w przypadku modyfikacji danych źródłowych. Z drugiej strony funkcja FILTER odpowiednio aktualizuje swoje wyniki w przypadku zmiany danych.
Składnia funkcji FILTER
Składnia formuły FILTER jest dość prosta:
=FILTER(tablica, uwzględnienie, [jeśli_puste])
Tablica to prostokątny podzbiór arkusza kalkulacyjnego, wskazany przez określenie zakresu między lewą górną komórką a prawą dolną komórką. Na przykład A3:E10 to tablica składająca się z kolumn od A do E i wierszy od 3 do 10..
Następny parametr to po prostu kryteria, które należy zastosować, lub bardziej technicznie tablica logiczna. Wartość tę wprowadza się w formie wyrażenia oceniającego wartość zakresu komórek (zwykle kolumny), które zwraca wartość PRAWDA lub FAŁSZ. Na przykład A3:A10=”Pass” zwróci PRAWDA, gdy wartość komórki pasuje do podanego ciągu.
Na koniec możesz wprowadzić wartość zwracaną przez funkcję FILTER, gdy żaden wiersz nie spełnia warunków. Może to być prosty ciąg znaków, np. „Nie znaleziono żadnych rekordów”.
Powiązane: Chcesz sprawdzić, jak dokładnie przykładowe dane pasują do większego zbioru danych? Zapoznaj się z naszym przewodnikiem dotyczącym obliczanie błędu standardowego w programie Excel.
Korzystanie z funkcji FILTR
Teraz, gdy znamy składnię funkcji FILTER, przyjrzyjmy się, jak faktycznie używać FILTR w arkuszu kalkulacyjnym.
Przykładowe dane, których używamy w tej demonstracji, mają tablicę od A2 do F11 i przedstawiają wyniki z biologii dziesięciu uczniów wraz z rozkładem normalnym.
Napiszmy funkcję, która będzie filtrować wpisy na podstawie ich wyników z egzaminu (wymienionych w kolumnie D) i zwracać tylko te, które uzyskały wynik poniżej 30. Taka powinna być składnia:
=FILTER(A2:F11,D2:D11<30,”Nie znaleziono żadnych dopasowań”)
Ponieważ przefiltrowane wyniki stanowią podzbiór tablicy, użyj funkcji w komórce z wystarczającą ilością miejsca po niej. Zrobimy to poniżej oryginalnej tabeli:
I uzyskujemy oczekiwane rezultaty. Wszystkie wpisy z wynikiem poniżej 30 zostaną wybrane i wyświetlone w tym samym formacie tabeli.
Nie jesteś też ograniczony do jednego warunku. Użyj operatora AND (*), aby połączyć wiele wyrażeń w jeden parametr, tworząc bardziej złożony filtr.
Skonstruujmy funkcję, która zwraca wpisy mieszczące się w przedziale od 30 do 70 znaków. Oto składnia i wyniki:
=FILTER(A2:F11,(D2:D11>30)*(D2:D11<70),„Nie znaleziono żadnych dopasowań”)
W przypadku kryteriów niewyłącznych możesz także użyć operatora OR (+). Dopasowuje to filtr, nawet jeśli tylko jeden z uwzględnionych warunków ma wartość PRAWDA.
W poniższym wzorze używamy go do znajdowania wartości odstających poprzez filtrowanie wyników mniejszych niż 15 lub większych niż 70.
=FILTER(A2:F11,(D2:D11<30)+(D2:D11>70),”Nie znaleziono żadnych rekordów”)
Na koniec, zamiast używać pojedynczej wartości lub ciągu znaków do zwrócenia, gdy funkcja FILTER niczego nie znajdzie, możesz określić wartości dla każdej kolumny, aby mieć pewność, że dane wyjściowe zawsze będą miały spójny format..
Najpierw wypróbujmy warunek, o którym wiemy, że jest fałszywy, aby zobaczyć, jak wygląda domyślnie:
=FILTER(A2:F11,D2:D11>90,”Nie znaleziono żadnych dopasowań”)
Jak widać, wynik zawiera tylko jeden ciąg, co jest niezgodne z oczekiwanym formatem. Zwykle nie stanowi to problemu, chyba że chcesz wprowadzić wyniki (lub niektóre z nich wartości) do innej formuły.
Spróbujmy więc podać wartości domyślne w tym samym formacie, co wpis tablicy. Możemy to zrobić, podając wartości oddzielone przecinkami, ujęte w nawiasy klamrowe. W ten sposób:
=FILTER(A2:F11,D2:D11>90,{„Brak zapisu”, „Brak zapisu”, „Brak zapisu”, 0})
Dzięki temu wyniki są smaczniejsze, spójne z resztą formatu arkusza kalkulacyjnego.
Czy warto korzystać z funkcji FILTR?
Nawet jeśli do prowadzenia dokumentacji używasz wyłącznie programu MS Excel i nie masz zamiaru wykonywać żadnych wymyślnych obliczeń, funkcja FILTR jest jedną z niewielu, na którą warto zwrócić uwagę.
Ponieważ gdy skoroszyt osiągnie określony rozmiar, ręczne lokalizowanie danych może być uciążliwe. Chociaż narzędzia Filtr automatyczny i Filtr zaawansowany są przydatne, na dłuższą metę korzystanie z danej funkcji jest w rzeczywistości wygodniejsze, ponieważ wyniki aktualizują się same i można je sparować z innymi funkcjami.
.