Jak korzystać z zaawansowanego filtra Excela - Pełny przewodnik z przykładami
Zaawansowany filtr programu Excel to potężne narzędzie, które zapewnia elastyczność wykraczającą poza standardową funkcjonalność filtrowania, umożliwiając użytkownikom wydajne wykonywanie złożonych zadań filtrowania. W tym przewodniku szczegółowo omówiono funkcję zaawansowanych filtrów programu Excel, porównano ją ze zwykłymi filtrami, przedstawiono praktyczne przykłady i przedstawiono rozważania dotyczące optymalnego wykorzystania. Dzięki niemu możesz z pewnością opanować korzystanie z zaawansowanych filtrów.
- Wyodrębnij unikalną listę
- Filtruj w jednej kolumnie z wieloma kryteriami (dopasuj dowolne kryteria)
- Filtruj w wielu kolumnach z wieloma kryteriami
- Zaawansowany filtr z symbolem wieloznacznym
- Wyodrębnij tylko niektóre kolumny
Filtr zaawansowany a filtr normalny
Główna różnica pomiędzy normalnym filtrem programu Excel a filtrem zaawansowanym polega na ich złożoności i funkcjonalności. Podczas gdy filtr normalny oferuje proste filtrowanie w oparciu o kryteria jednokolumnowe w obrębie oryginalnego zbioru danych, filtr zaawansowany wykracza poza te ograniczenia poprzez:
- Umożliwienie stosowania wielu kryteriów w różnych kolumnach.
- Zapewnienie możliwości wyodrębnienia unikalnych wartości ze zbioru danych.
- Umożliwienie stosowania symboli wieloznacznych w celu bardziej elastycznego, częściowego dopasowania.
- Umożliwienie ekstrakcji przefiltrowanych danych do osobnej lokalizacji.
Przykłady zastosowania filtra zaawansowanego
W tej sekcji znajdziesz różne praktyczne przykłady pokazujące, jak używać zaawansowanych filtrów w Excelu, aby uzyskać różne efekty filtrowania.
Wyodrębnij unikalną listę
Zaawansowany filtr programu Excel może szybko wygenerować listę unikalnych wartości ze zbioru danych, co może być kłopotliwe w przypadku zwykłych filtrów. Jeśli masz listę transakcji sprzedaży ze zduplikowanymi wierszami i chcesz wyodrębnić listę unikalnych wierszy, funkcja Filtr zaawansowany w programie Excel może uprościć to zadanie. Aby to osiągnąć, postępuj zgodnie z poniższymi instrukcjami.
- Idź do Dane kartę, wybierz Zaawansowane Sortuj i filtruj grupa.
- W Zaawansowany filtr W oknie dialogowym należy skonfigurować w następujący sposób.
- W Działania sekcji, wybierz potrzebną opcję. Ponieważ chcę zlokalizować unikalną listę w innym miejscu, wybieram Skopiuj do innej lokalizacji opcja.
- Określ sekcję Zakres listy:
- Wyodrębnij unikalną wartość z jednej kolumny:
Wybierz kolumnę zawierającą wartości, z których chcesz wyodrębnić unikalne wpisy. Na przykład, aby w tym przypadku wyodrębnić unikalne nazwy klientów, wybierz A1:A11.
- Wyodrębnij unikalne wiersze na podstawie wielu kolumn:
Wybierz zakres obejmujący wszystkie kolumny, które rozważasz. W tym przypadku, ponieważ chcę wyodrębnić unikalne wiersze na podstawie nazw klientów, sprzedaży i regionu, wybieram cały zakres A1:C11.
- Wyodrębnij unikalną wartość z jednej kolumny:
- W Kopiuj do sekcji określ, gdzie chcesz wkleić unikalną listę.
- Sprawdź Tylko unikalne rekordy checkbox.
- Kliknij OK przycisk. Zobacz zrzut ekranu:
Wynik
Jak pokazano na zrzucie ekranu poniżej, z oryginalnego zakresu danych wyodrębniane są unikalne wiersze.
Filtruj w jednej kolumnie z wieloma kryteriami (dopasuj dowolne kryteria)
Filtrowanie danych w jednej kolumnie przy użyciu wielu kryteriów umożliwia wyświetlenie wierszy spełniających dowolny z określonych warunków. Może to być szczególnie przydatne, gdy pracujesz z dużymi zbiorami danych i musisz zawęzić informacje na podstawie kilku potencjalnych dopasowań. Oto, jak możesz to osiągnąć, korzystając z funkcji zaawansowanego filtrowania programu Excel:
Krok 1: Przygotuj oryginalne dane dotyczące zakresu listy
Upewnij się, że zbiór danych zakresu listy ma jasne nagłówki kolumn, ponieważ będą one ważne przy konfigurowaniu zakresu kryteriów. Jako przykład posłużę się poniższą tabelą wyników uczniów.
Krok 2: Konfiguracja zakresu kryteriów
- W zakresie powyżej lub poza zakresem listy utwórz zakres kryteriów. Aby nagłówki wpisywane w zakresie kryteriów musiały dokładnie odpowiadać nagłówkom w zakresie listy, aby działały poprawnie. Tutaj mój zakres kryteriów znajduje się powyżej zakresu listy.
- Pod nagłówkiem wypisz wszystkie kryteria, które chcesz spełnić. Każde kryterium powinno znajdować się w osobnej komórce, bezpośrednio pod poprzednim. Ta konfiguracja informuje program Excel, aby spełniał którekolwiek z tych kryteriów.
W tym przykładzie szukam studentów z wyniki większe niż 95 lub mniejsze niż 60 dzięki czemu mogę skutecznie filtrować zakres listy, aby uwzględnić zarówno uczniów z wysokimi, jak i niskimi wynikami. Dlatego każde kryterium wpisuję w oddzielnych wierszach pod nagłówkiem Wynik. Cały zakres kryteriów przedstawiono poniżej:
Krok 3: Zastosuj filtr zaawansowany
Teraz możesz zastosować filtr zaawansowany, aby wykonać zadanie w następujący sposób.
- Idź do Dane I wybierz opcję Zaawansowane Sortuj i filtruj grupa.
- W Zaawansowany filtr W oknie dialogowym należy skonfigurować w następujący sposób.
- W Działania sekcji, wybierz potrzebną opcję. Tutaj, ponieważ chcę zlokalizować przefiltrowany wynik w innym miejscu, wybieram Skopiuj do innej lokalizacji opcja.
- W Zakres listy sekcji wybierz cały zakres listy A7: D17.
- W Zakres kryteriów sekcji, wybierz cały zakres kryteriów A2: D4.
- W Kopiuj do w sekcji określ, gdzie chcesz wkleić przefiltrowany wynik (tutaj wybieram cell F8).
- Kliknij OK aby zastosować filtr. Zobacz zrzut ekranu:
Wynik
Następnie widać, że wyodrębniane są tylko wiersze, w których kolumna „Wynik” spełnia którekolwiek z kryteriów (>95 lub <60).
Pożegnaj ręczne ustawianie skomplikowanych zakresów kryteriów
Odblokuj moc filtrowania wielowarunkowego w programie Excel bez złożoności! Kutools dla programu Excel's Super filtr funkcja oferuje niezrównaną łatwość obsługi, której natywny filtr zaawansowany programu Excel po prostu nie może dorównać. Obsługuje następujące zaawansowane filtry za pomocą zaledwie kilku kliknięć:
- Filtruj według wielu kryteriów w jednej kolumnie
- Filtruj według wielu kryteriów w wielu kolumnach
- Filtruj dane według długości tekstu
- Filtruj dane na podstawie roku/miesiąca/tygodnia...
- Filtruj ciągi tekstowe według wielkości liter...
Dowiedz się jak Super filtr może zrewolucjonizować Twój przepływ pracy. Kliknij tutaj, aby pobrać bezpłatną 30-dniową wersję próbną Kutools dla programu Excel.
Kliknij tutaj, aby dowiedzieć się więcej i zobaczyć, jak korzystać z tej funkcji.
Filtruj w wielu kolumnach z wieloma kryteriami
Po omówieniu filtrowania wieloma kryteriami w jednej kolumnie, teraz skupimy się na filtrowaniu wielokolumnowym. W tej sekcji znajdziesz wskazówki dotyczące stosowania wielu kryteriów w różnych kolumnach przy użyciu logiki AND, OR i połączonej logiki AND/OR.
- Aby zastosować logikę AND, umieść kryteria w tym samym wierszu.
- Aby zastosować logikę OR, umieść kryteria w oddzielnych wierszach.
Z logiką AND (spełnia wszystkie kryteria)
Filtrowanie danych w wielu kolumnach przy użyciu wielu kryteriów przy użyciu logiki AND oznacza, że każdy wiersz musi spełniać wszystkie określone kryteria w różnych kolumnach, aby został wyświetlony. Oto jak to osiągnąć za pomocą zaawansowanego filtra programu Excel:
Krok 1: Przygotuj oryginalne dane dotyczące zakresu listy
Upewnij się, że zbiór danych zakresu listy ma jasne nagłówki kolumn, ponieważ będą one ważne przy konfigurowaniu zakresu kryteriów. Jako przykład posłużę się poniższą tabelą wyników uczniów.
Krok 2: Konfiguracja zakresu kryteriów
- Utwórz zakres kryteriów powyżej lub oddzielnie od zakresu listy, wpisując nagłówki dokładnie pasujące do zakresu listy. Tutaj mój zakres kryteriów znajduje się powyżej zakresu listy.
- W razie zamówieenia projektu ROLNICZE logika, wypisz wszystkie kryteria w tym samym wierszu pod odpowiadającymi im nagłówkami. Na przykład, jeśli chcę filtrować uczniów „klasy A” z wynikami powyżej 85, wówczas zakres kryteriów powinien być ustawiony w następujący sposób:
Krok 3: Zastosuj filtr zaawansowany
Teraz możesz zastosować filtr zaawansowany, aby wykonać zadanie w następujący sposób.
- Idź do Dane I wybierz opcję Zaawansowane Sortuj i filtruj grupa.
- W Zaawansowany filtr W oknie dialogowym należy skonfigurować w następujący sposób.
- W Działania sekcji, wybierz potrzebną opcję. Tutaj, ponieważ chcę zlokalizować przefiltrowany wynik w innym miejscu, wybieram Skopiuj do innej lokalizacji opcja.
- W Zakres listy sekcji wybierz cały zakres listy A7: D16.
- W Zakres kryteriów sekcji, wybierz cały zakres kryteriów A2: D3.
- W Kopiuj do w sekcji określ, gdzie chcesz wkleić przefiltrowany wynik (tutaj wybieram cell F6).
- Kliknij OK aby zastosować filtr. Zobacz zrzut ekranu:
Wynik
W rezultacie zostaną wyświetlone lub skopiowane tylko wiersze spełniające wszystkie kryteria w określonych kolumnach. W naszym przykładzie wyodrębnieni zostali tylko uczniowie z klasy A z wynikami powyżej 85.
Z logiką OR (dopasuj dowolne kryteria)
Aby filtrować dane w wielu kolumnach przy użyciu logiki OR (spełniającej dowolne kryteria) w filtrze zaawansowanym programu Excel, wykonaj następujące kroki:
Krok 1: Przygotuj oryginalne dane dotyczące zakresu listy
Upewnij się, że zbiór danych zakresu listy ma jasne nagłówki kolumn, ponieważ będą one ważne przy konfigurowaniu zakresu kryteriów. Jako przykład posłużę się poniższą tabelą wyników uczniów.
Krok 2: Konfiguracja zakresu kryteriów
- Utwórz zakres kryteriów powyżej lub oddzielnie od zakresu listy, wpisując nagłówki dokładnie pasujące do zakresu listy. Tutaj mój zakres kryteriów znajduje się powyżej zakresu listy.
- Stosując logikę OR, umieść każdy zestaw kryteriów dla tej samej kolumny w oddzielnych wierszach lub wypisz każde kryterium w oddzielnych wierszach pod odpowiadającym mu nagłówkiem. Na przykład, jeśli chcę filtrować według uczniów z wynikami wyższymi niż 90 lub ocenami F, zakres kryteriów powinien być ustawiony w następujący sposób:
Krok 3: Zastosuj filtr zaawansowany
Teraz możesz zastosować filtr zaawansowany, aby wykonać zadanie w następujący sposób.
- Idź do Dane I wybierz opcję Zaawansowane Sortuj i filtruj grupa.
- W Zaawansowany filtr W oknie dialogowym należy skonfigurować w następujący sposób.
- W Działania sekcji, wybierz potrzebną opcję. Tutaj, ponieważ chcę zlokalizować przefiltrowany wynik w innym miejscu, wybieram Skopiuj do innej lokalizacji opcja.
- W Zakres listy sekcji wybierz cały zakres listy A7: D17.
- W Zakres kryteriów sekcji, wybierz cały zakres kryteriów A2: D4.
- W Kopiuj do w sekcji określ, gdzie chcesz wkleić przefiltrowany wynik (tutaj wybieram cell F8).
- Kliknij OK aby zastosować filtr. Zobacz zrzut ekranu:
Wynik
Spowoduje to przefiltrowanie danych w oparciu o określone kryteria, spełniające dowolne wymienione kryteria. Jeśli wiersz spełnia którekolwiek z kryteriów we wskazanych kolumnach, zostanie uwzględniony w filtrowanych wynikach.
W tym przypadku filtr zwróci tylko uczniów, którzy uzyskali wynik wyższy niż 90 lub z oceną F.
Z logiką AND i OR
Aby filtrować dane w wielu kolumnach za pomocą kombinacji ROLNICZE jak również OR logiki przy użyciu zaawansowanego filtra programu Excel, możesz wykonać następujące kroki.
Krok 1: Przygotuj oryginalne dane dotyczące zakresu listy
Upewnij się, że zbiór danych zakresu listy ma jasne nagłówki kolumn, ponieważ będą one ważne przy konfigurowaniu zakresu kryteriów. Jako przykład posłużę się poniższą tabelą wyników uczniów.
Krok 2: Konfiguracja zakresu kryteriów
- Utwórz zakres kryteriów powyżej lub obok zakresu listy. Uwzględnij nagłówki kolumn dokładnie odpowiadające nagłówkom z zakresu listy. Tutaj mój zakres kryteriów znajduje się powyżej zakresu listy.
- Pod nagłówkami wprowadź kryteria, stosując kombinację logiki AND i OR.
- W razie zamówieenia projektu ROLNICZE logicznie, kryteria z różnych kolumn należy umieścić w tym samym wierszu.
- W razie zamówieenia projektu OR logicznie, kryteria należy umieścić w oddzielnych wierszach.
- W razie zamówieenia projektu połączona logika AND-OR, zorganizuj każdy zestaw warunków OR w oddzielnych blokach wierszy. W każdym bloku umieść kryteria ORAZ w tym samym wierszu.
Na przykład, aby odfiltrować uczniów w klasie A z wynikami większymi niż 90 lub w klasie B z oceną B, ustaw zakres kryteriów w następujący sposób:
Krok 3: Zastosuj filtr zaawansowany
Teraz możesz zastosować filtr zaawansowany, aby wykonać zadanie w następujący sposób.
- Idź do Dane I wybierz opcję Zaawansowane Sortuj i filtruj grupa.
- W Zaawansowany filtr W oknie dialogowym należy skonfigurować w następujący sposób.
- W Działania sekcji, wybierz potrzebną opcję. Tutaj, ponieważ chcę zlokalizować przefiltrowany wynik w innym miejscu, wybieram Skopiuj do innej lokalizacji opcja.
- W Zakres listy sekcji wybierz cały zakres listy A7: D17.
- W Zakres kryteriów sekcji, wybierz cały zakres kryteriów A2: D4.
- W Kopiuj do w sekcji określ, gdzie chcesz wkleić przefiltrowany wynik (tutaj wybieram cell F8).
- Kliknij OK aby zastosować filtr. Zobacz zrzut ekranu:
Wynik
Excel wyświetli tylko te wiersze, które spełniają złożoną kombinację kryteriów.
W tym przykładzie filtr zaawansowany zwróci tylko uczniów z wynikami większymi niż 90 w klasie A lub uczniów z oceną B w klasie B.
Zaawansowany filtr z symbolem wieloznacznym
Używanie symboli wieloznacznych w zaawansowanym filtrze programu Excel pozwala na bardziej elastyczne i wydajne wyszukiwanie danych. Symbole wieloznaczne to znaki specjalne reprezentujące jeden lub więcej znaków w ciągu, co ułatwia filtrowanie wzorców tekstowych. Oto szczegółowe instrukcje dotyczące korzystania z zaawansowanego filtra ze znakami wieloznacznymi w programie Excel.
Krok 1: Przygotuj oryginalne dane dotyczące zakresu listy
Upewnij się, że zbiór danych zakresu listy ma jasne nagłówki kolumn, ponieważ będą one ważne przy konfigurowaniu zakresu kryteriów. W tym przykładzie załóżmy, że masz listę imion, a niektóre z imion, których szukasz, mają określony wzorzec nazewnictwa.
Krok 2: Konfiguracja zakresu kryteriów
- Utwórz zakres kryteriów powyżej lub obok zakresu listy. Uwzględnij nagłówki kolumn dokładnie odpowiadające nagłówkom z zakresu listy. Tutaj mój zakres kryteriów znajduje się powyżej zakresu listy.
- Pod nagłówkiem wprowadź kryteria, używając symboli wieloznacznych.
- *: Reprezentuje dowolną liczbę znaków i może być użyte przed, po lub w ciągu ciągu.
- ?: Reprezentuje pojedynczy znak na określonej pozycji.
W tym przykładzie chcę filtrować nazwy zaczynające się od znaku „J”, więc wprowadzam J* pod nagłówkiem Nazwa zakresu kryteriów. Zobacz zrzut ekranu:
Krok 3: Zastosuj filtr zaawansowany
Teraz możesz zastosować filtr zaawansowany, aby filtrować wszystkie nazwy zaczynające się na znak J.
- Idź do Dane I wybierz opcję Zaawansowane Sortuj i filtruj grupa.
- W Zaawansowany filtr okno dialogowe, skonfiguruj w następujący sposób.
- W Działania sekcji, wybierz potrzebną opcję. Tutaj, ponieważ chcę zlokalizować przefiltrowany wynik w innym miejscu, wybieram Skopiuj do innej lokalizacji opcja.
- W Zakres listy sekcji wybierz cały zakres listy A6: B11.
- W Zakres kryteriów sekcji, wybierz cały zakres kryteriów A2: B3.
- W Kopiuj do w sekcji określ, gdzie chcesz wkleić przefiltrowany wynik (tutaj wybieram cell D7).
- Kliknij OK aby zastosować filtr. Zobacz zrzut ekranu:
Wynik
Filtr zaawansowany wyświetli tylko te wiersze z kolumny Nazwa, których nazwy zaczynają się na literę 'J', zgodnie ze wzorcem określonym przez symbol wieloznaczny w zakresie kryteriów.
Wyodrębnij tylko niektóre kolumny
Korzystanie z zaawansowanego filtra programu Excel w celu wyodrębnienia tylko niektórych kolumn jest szczególnie przydatne w przypadku analizy dużych zbiorów danych, w których wystarczy skupić się tylko na określonych informacjach.
Załóżmy, że Twój zbiór danych mieści się w zakresie A7:D17 i chcesz filtrować te dane na podstawie kryteriów określonych w B2:D4 i wyodrębniać tylko Imię, Wynik oraz Stopień kolumny. Oto jak to zrobić.
Krok 1: Określ kolumny do wyodrębnienia
Poniżej lub obok zbioru danych wpisz nagłówki kolumn, które chcesz wyodrębnić. Określa zakres „Kopiuj do”, w którym pojawią się przefiltrowane dane. W tym przykładzie wpisuję Imię, Wynik i Stopień nagłówki w zakresie F7:H7.
Krok 2: Zastosuj filtr zaawansowany
Teraz możesz zastosować filtr zaawansowany, aby filtrować tylko określone kolumny na podstawie określonych kryteriów.
- Idź do Dane I wybierz opcję Zaawansowane Sortuj i filtruj grupa.
- W Zaawansowany filtr okno dialogowe, skonfiguruj w następujący sposób.
- W Działania wybierz sekcję Skopiuj do innej lokalizacji opcja.
- W Zakres listy sekcji wybierz cały zakres listy A7: D17.
- W Zakres kryteriów sekcji, wybierz cały zakres kryteriów A2: D4.
- W Kopiuj do sekcję, wybierz zakres (F7:H7 w tym przypadku), w którym zapisałeś nagłówki kolumn, które chcesz wyodrębnić.
- Kliknij OK aby zastosować filtr. Zobacz zrzut ekranu:
Wynik
Widać, że wynik ekstrakcji obejmuje tylko określone kolumny.
Uwagi dotyczące filtra zaawansowanego
- Zakres kryteriów musi mieć nagłówki kolumn dokładnie takie same jak nagłówki w zakresie listy.
- Jeśli przefiltrowane wyniki zostaną skopiowane do innej lokalizacji, funkcja Cofnij (Control + Z) nie będzie dostępna.
- Stosując filtr zaawansowany w programie Excel, pamiętaj o uwzględnieniu nagłówków kolumn w zaznaczeniu. Pominięcie nagłówków może spowodować, że Excel błędnie potraktuje pierwszą komórkę zakresu jako nagłówek, co może skutkować nieprawidłowym filtrowaniem.
- Filtrowane wyniki nie są aktualizowane dynamicznie; ponownie zastosuj filtr zaawansowany, aby odświeżyć je po zmianie danych.
- W poniższej tabeli wymieniono operacje porównania liczb i dat, których można użyć w kryteriach filtra zaawansowanego.
Operator porównania Znaczenie = Równy > Lepszy niż < Mniej niż >= Większe bądź równe <= Mniejszy lub równy <> Nie równa się
Powiązane artykuły
Sprawdź, czy w arkuszu w programie Excel zastosowano filtr
Ten samouczek pomaga szybko sprawdzić, czy filtr został zastosowany do określonej kolumny lub do całego arkusza, bez konieczności ręcznego sprawdzania kolumny po kolumnie.
Wklej dane do przefiltrowanej listy, pomijając ukryte wiersze
W tym samouczku przedstawiono szczegółowe instrukcje wklejania danych do filtrowanych wierszy w programie Excel poprzez pomijanie ukrytych wierszy.
Skopiuj dane do innego arkusza za pomocą filtra zaawansowanego
W tym samouczku pokazano, jak używać zaawansowanych filtrów w programie Excel do kopiowania wyników filtra do innego arkusza.
Wyczyść filtry ze wszystkich arkuszy w aktywnym skoroszycie
Załóżmy, że utworzyłeś kilka filtrów w różnych arkuszach skoroszytu programu Excel i teraz chcesz wyczyścić je wszystkie na raz. Zwykle należy sprawdzać arkusz filtrów arkusz po arkuszu, a następnie usuwać je ręcznie. Jeśli chcesz wyczyścić filtry ze wszystkich arkuszy w aktywnym skoroszycie, wypróbuj metodę opisaną w tym artykule.
Spis treści
- Filtr zaawansowany a filtr normalny
- Przykłady zastosowania filtra zaawansowanego
- Wyodrębnij unikalną listę
- Filtruj w jednej kolumnie z wieloma kryteriami (dopasuj dowolne kryteria)
- Filtruj w wielu kolumnach z wieloma kryteriami
- Z logiką AND (spełnia wszystkie kryteria)
- Z logiką OR (dopasuj dowolne kryteria)
- Z logiką AND i OR
- Zaawansowany filtr z symbolem wieloznacznym
- Wyodrębnij tylko niektóre kolumny
- Uwagi dotyczące filtra zaawansowanego
- Powiązane artykuły
- Najlepsze narzędzia biurowe
- Komentarze
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!