Co to jest filtr zaawansowany w programie Excel?
Filtr zaawansowany różni się od filtra automatycznego w programie Excel, funkcja ta nie przypomina przycisku, którego można użyć jednym kliknięciem myszy, aby użyć filtru zaawansowanego, najpierw musimy zdefiniować kryteria automatycznego filtrowania, a następnie kliknąć zakładkę Dane, a następnie w sekcji zaawansowanej dla filtru zaawansowanego, gdzie wypełnimy nasze kryteria dla danych.
Jak korzystać z zaawansowanego filtru w programie Excel? (Z przykładami)
Nauczmy się tego na kilku przykładach.
Możesz pobrać ten szablon programu Excel z filtrem zaawansowanym - szablon programu Excel z filtrem zaawansowanymPrzykład 1
Załóżmy, że mamy śledzenie danych do filtrowania na podstawie różnych kryteriów.
Musimy sprawdzić transakcję sprzedaży, której dokonali „Taran” i „Suresh”, a następnie możemy skorzystać z operatora OR, który wyświetla rekordy spełniające którykolwiek z warunków. Aby uzyskać wyniki, możemy wykonać kroki, aby zastosować te filtry w programie Excel.
- Krok 1: Aby zastosować zaawansowany filtr, musimy najpierw wybrać dowolną komórkę w zakresie danych
- Krok 2: następnie kliknij Danych Tab-> Sortowanie i filtrowanie grupa -> Zaawansowane komendy
- Krok 3: Gdy klikniemy „Zaawansowane” , otworzy się okno dialogowe „Filtr zaawansowany” z pytaniem o zakres listy do przefiltrowania, zakres kryteriów do zdefiniowania kryteriów i zakres wyodrębniania do skopiowania przefiltrowanych danych (w razie potrzeby).
- Krok 4: W przypadku kryteriów musimy skopiować nagłówki kolumn z górnego wiersza i zdefiniować kryteria poniżej nagłówka pola. Aby określić kryteria, możemy użyć operatora porównania, które są następujące:
- Krok 5: Ponieważ chcemy, aby wszystkie rekordy miały nazwę „Suresh” lub „Taran”. Zakres kryteriów wyglądałby jak poniżej:
W przypadku warunków „LUB”, w których chcemy wyświetlić rekordy spełniające którykolwiek z warunków, musimy określić kryteria w różnych wierszach.
W zaawansowanym filtrze są dwie akcje.
- Przefiltruj listę na miejscu : Ta opcja filtruje listę w pierwotnym miejscu, czyli w samym zakresie listy i po przeanalizowaniu możemy usunąć filtr za pomocą polecenia „Wyczyść” w grupie „Sortuj i filtruj” pod „Dane”
- Kopiuj do innej lokalizacji : Ta opcja kopiuje żądane dane zgodnie z kryteriami do określonego zakresu.
W zależności od potrzeb możemy skorzystać z dowolnej opcji, ale częściej będziemy korzystać z drugiej opcji.
Teraz po prostu musimy
- Otwórz okno dialogowe „Filtr zaawansowany”
- Określenie zakresu listy jako $ A $ 5: $ D $ 26, Zakres kryteriów jako $ A $ 1: $ D $ 3 i ` ` Kopiuj do '' jako $ F $ 5: $ I $ 26. Kliknij „OK” .
Widzimy, że wszystkie rekordy o nazwie „Suresh” lub „Taran” są odfiltrowywane i wyświetlane oddzielnie w innym zakresie komórek.
Przykład nr 2
Teraz chcemy uzyskać wszystkie transakcje sprzedaży w pierwszym kwartale i południowych Indiach. Zakres kryteriów jest następujący:
Ponieważ mamy tutaj warunek „AND”, czyli chcemy wyświetlić rekordy, w których oba warunki są spełnione, dlatego wspomnieliśmy o kryteriach pod nagłówkami obu kolumn w tym samym wierszu.
Teraz klikniemy polecenie „Zaawansowane” w grupie „Sortuj i filtruj” w zakładce „Dane” .
W oknie dialogowym „ Filtr zaawansowany ” wybierzemy „Kopiuj do innej lokalizacji”, a następnie zdefiniujemy A5: D26 jako zakres listy , A1: D2 jako zakres kryteriów i F5: I26 jako zakres „ Kopiuj do” .
Teraz wynik jest następujący:
Przykład nr 3
Teraz chcemy znaleźć sprzedaż w pierwszym kwartale lub w północnych Indiach.
Musimy określić zarówno kryteria w różnych wierszach, jak i w różnych kolumnach, ponieważ musimy wyświetlić dane, jeśli którykolwiek z warunków jest spełniony, a oba warunki są związane z różnymi kolumnami.
Kroki:
- Musisz otworzyć okno dialogowe „Filtr zaawansowany” .
- Określ zakres listy jako $ A $ 5: $ D $ 26
- Określ zakres kryteriów jako 1 $ A: 3 $ D
- Określ zakres „Kopiuj do” jako 5 $ F $: $ I 26 $
Wynik byłby następujący:
Przykład 4
Teraz chcemy znaleźć wszystkie sprzedaże Rs. 2000-4000 i Rs. 10000-13000.
Ponieważ mamy cztery warunki jako (Warunek 1 ORAZ Warunek 2) LUB (Warunek 3 ORAZ Warunek 4).
(> = 2000 I = 10000 I <= 13000)
Dlatego wspomnieliśmy o warunkach z „ AND” w tym samym wierszu i warunkami z „OR” w różnych wierszach.
Kroki:
- Aby otworzyć okno dialogowe „Filtr zaawansowany” , klikniemy „Zaawansowane” w grupie „Sortuj i filtruj” w sekcji „Dane”
- W oknie dialogowym „Filtr zaawansowany” określimy
- Zakres listy jako $ A $ 5: $ D $ 26
- Kryteria Zakres jako 1 $ A $: 3 $ D $
- Zakres „Kopiuj do” jako $ F $ 5: $ I $ 26
- Po kliknięciu „OK”. Rezultatem będzie:
Przykład nr 5
Teraz chcemy znaleźć sprzedaż Qtr 1 przez Sunny lub Qtr 3 przez Mukesh.
Ponieważ mamy AND i OR , oba typy relacji w warunkach, dlatego warunki określimy w zakresie kryteriów w różnych wierszach (OR) i różnych kolumnach (AND).
Kroki:
- Aby otworzyć okno dialogowe „Filtr zaawansowany” , klikniemy „Zaawansowane” w grupie „Sortuj i filtruj” w sekcji „Dane”
- W oknie dialogowym „Filtr zaawansowany” określimy
- Zakres listy jako $ A $ 5: $ D $ 26
- Kryteria Zakres jako 1 $ A $: 3 $ D $
- Zakres „Kopiuj do” jako $ F $ 5: $ I $ 26
- Po kliknięciu OK wynik będzie
Przykład # 6 - Używanie znaków WILDCARD
Teraz chcemy znaleźć wszystkie transakcje sprzedaży, które mają nazwę kończącą się na „esh” lub pierwsze słowo regionu kończące się na „st” i chcemy pobrać tylko nazwę, sprzedaż i region.
Tutaj * oznacza więcej niż jeden znak i
'?' oznacza tylko jeden znak.
Ponieważ chcemy, aby tylko niektóre kolumny nie były wszystkie, musimy określić etykiety kolumn w opcji Kopiuj do zakresu przed wdrożeniem filtru zaawansowanego.
Teraz nazwiemy polecenie.
Kroki:
- Aby otworzyć okno dialogowe „Filtr zaawansowany” , klikniemy „Zaawansowane” w grupie „Sortuj i filtruj” w sekcji „Dane”
- W oknie dialogowym „Filtr zaawansowany” określimy
- Zakres listy jako $ A $ 5: $ D $ 26
- Kryteria Zakres jako 1 $ A $: 3 $ D $
- Zakres „Kopiuj do” jako 5 $ F: 26 $ H
- Po kliknięciu „ OK”. Wynik byłby:
Przykład nr 7
Teraz chcemy przefiltrować pięć najlepszych sprzedaży (o dużej ilości).
Komórka formuły musi mieć wartość PRAWDA lub FAŁSZ . Ponieważ chcemy uzyskać największe 5 rekordów, dlatego użyliśmy DUŻEJ funkcji programu Excel i porównaliśmy wartość z kwotą sprzedaży .
Jak widać, nagłówek kolumny dla komórki formuły jest pusty. Możemy pozostawić to pole puste lub nadać nazwę, która nie pasuje do żadnego z nagłówków kolumny w zakresie danych.
Teraz określimy zakresy w oknie dialogowym „Filtr zaawansowany” . Kroki to:
- Aby otworzyć okno dialogowe „Filtr zaawansowany” , klikniemy „Zaawansowane” w grupie „Sortuj i filtruj” w sekcji „Dane”
- W oknie dialogowym „Filtr zaawansowany programu Excel” określimy
- Zakres listy jako $ A $ 5: $ D $ 26
- Zakres kryteriów jako 1 $ A: 2 $ E
- Zakres „Kopiuj do” jako $ F $ 5: $ I $ 26
- Po kliknięciu OK. Wynik byłby taki:
Rzeczy do zapamiętania
- Zakres, do którego ma zostać zastosowany, musi mieć unikalny nagłówek, ponieważ zduplikowane nagłówki powodują problem podczas uruchamiania zaawansowanego filtru.
- Pomiędzy zakresem listy a zakresem kryteriów powinien znajdować się co najmniej jeden pusty wiersz.
