Obejrzyj wideo - Zaawansowany filtr programu Excel
Zaawansowany filtr Excela to jedna z najbardziej niedocenianych i niedostatecznie używanych funkcji, z jakimi się spotkałem.
Jeśli pracujesz z Excelem, jestem pewien, że używałeś (lub przynajmniej słyszałeś o zwykłym filtrze Excela). Szybko filtruje zestaw danych na podstawie selekcji, określonego tekstu, liczby lub innych podobnych kryteriów.
W tym przewodniku pokażę Ci kilka fajnych rzeczy, które możesz zrobić za pomocą zaawansowanego filtra Excela.
Ale najpierw… Co to jest zaawansowany filtr programu Excel?
Excel Advanced Filter - jak sama nazwa wskazuje - jest zaawansowaną wersją zwykłego filtra. Możesz tego użyć, gdy musisz użyć bardziej złożonych kryteriów do filtrowania zestawu danych.
Oto kilka różnic między zwykłym filtrem a filtrem zaawansowanym:
- Podczas gdy zwykły filtr danych filtruje istniejący zestaw danych, możesz użyć zaawansowanego filtra programu Excel, aby wyodrębnić zestaw danych również do innej lokalizacji.
- Zaawansowany filtr programu Excel umożliwia stosowanie złożonych kryteriów. Na przykład, jeśli masz dane sprzedaży, możesz filtrować dane według kryterium, w którym przedstawicielem handlowym jest Robert, a regionem jest Północ lub Południe (zobaczymy, jak to zrobić na przykładach). Wsparcie Office ma dobre wyjaśnienie na ten temat.
- Możesz użyć zaawansowanego filtra programu Excel, aby wyodrębnić unikalne rekordy z danych (więcej o tym za chwilę).
ZAAWANSOWANY FILTR EXCEL (przykłady)
Przyjrzyjmy się teraz przykładowi korzystania z filtru zaawansowanego w programie Excel.
Przykład 1 - Wyodrębnienie unikalnej listy
Możesz użyć zaawansowanego filtra Excel, aby szybko wyodrębnić unikalne rekordy z zestawu danych (lub innymi słowy usunąć duplikaty).
W programie Excel 2007 i nowszych wersjach istnieje możliwość usunięcia duplikatów z zestawu danych. Ale to zmienia twój istniejący zestaw danych. Aby zachować oryginalne dane w nienaruszonym stanie, musisz utworzyć kopię danych, a następnie użyć opcji Usuń duplikaty. Filtr Excel Advanced pozwoli Ci wybrać lokalizację, aby uzyskać unikalną listę.Zobaczmy, jak korzystać z zaawansowanych filtrów, aby uzyskać unikalną listę.
Załóżmy, że masz zbiór danych, jak pokazano poniżej:
Jak widać, w tym zestawie danych znajdują się zduplikowane rekordy (podświetlone na pomarańczowo). Może to być spowodowane błędem we wprowadzaniu danych lub wynikiem kompilacji danych.
W takim przypadku możesz użyć narzędzia Excel Advanced Filter, aby szybko uzyskać listę wszystkich unikalnych rekordów w innej lokalizacji (aby oryginalne dane pozostały nienaruszone).
Oto kroki, aby uzyskać wszystkie unikalne rekordy:
- Wybierz cały zestaw danych (łącznie z nagłówkami).
- Przejdź do zakładki Dane -> Sortuj i filtruj -> Zaawansowane. (Możesz również użyć skrótu klawiaturowego - Alt + A + Q). Spowoduje to otwarcie okna dialogowego Filtr zaawansowany.
- W oknie dialogowym Filtr zaawansowany użyj następujących szczegółów:
- Akcja: Wybierz opcję „Kopiuj do innej lokalizacji”. Umożliwi to określenie lokalizacji, w której można uzyskać listę unikalnych rekordów.
- Zakres listy: Upewnij się, że odnosi się do zbioru danych, z którego chcesz znaleźć unikalne rekordy. Upewnij się również, że w zestawie danych znajdują się nagłówki.
- Zakres kryteriów: Zostaw to puste.
- Kopiuj do: Podaj adres komórki, w której chcesz uzyskać listę unikalnych rekordów.
- Kopiuj tylko unikalne rekordy: Zaznacz tę opcję.
- Kliknij OK.
To natychmiast da ci listę wszystkich unikalnych rekordów.
Ostrożność: Jeśli korzystasz z Filtra zaawansowanego, aby uzyskać unikatową listę, upewnij się, że wybrałeś również nagłówek. Jeśli tego nie zrobisz, pierwsza komórka zostanie uznana za nagłówek.
Przykład 2 - korzystanie z kryteriów w zaawansowanym filtrze programu Excel
Uzyskiwanie unikalnych rekordów to jedna z wielu rzeczy, które możesz zrobić za pomocą zaawansowanego filtru Excel.
Jego główna użyteczność polega na tym, że pozwala na stosowanie złożonych kryteriów filtrowania danych.
Oto, co rozumiem przez złożone kryteria. Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz szybko uzyskać wszystkie rekordy, w których sprzedaż przekracza 5000, a regionem są Stany Zjednoczone.
Oto jak możesz użyć zaawansowanego filtra Excela do filtrowania rekordów na podstawie określonych kryteriów:
- Pierwszym krokiem podczas korzystania z zaawansowanego filtru Excel ze złożonymi kryteriami jest określenie kryteriów. Aby to zrobić, skopiuj nagłówki i wklej je gdzieś w arkuszu.
- Określ kryteria, według których chcesz filtrować dane. W tym przykładzie, ponieważ chcemy uzyskać wszystkie rekordy dla Stanów Zjednoczonych ze sprzedażą większą niż 5000, wpisz „US” w komórce poniżej Region i > 5000 w komórce poniżej Sprzedaż. Będzie to teraz używane jako dane wejściowe w Filtrze zaawansowanym w celu uzyskania przefiltrowanych danych (jak pokazano w następnych krokach).
- Wybierz cały zestaw danych (w tym nagłówki).
- Przejdź do zakładki Dane -> Sortuj i filtruj -> Zaawansowane. Spowoduje to otwarcie okna dialogowego Filtr zaawansowany.
- W oknie dialogowym Filtr zaawansowany użyj następujących szczegółów:
- Akcja: Wybierz opcję „Kopiuj do innej lokalizacji”. Umożliwi to określenie lokalizacji, w której można uzyskać listę unikalnych rekordów.
- Zakres listy: Upewnij się, że odnosi się do zbioru danych, z którego chcesz znaleźć unikalne rekordy. Upewnij się również, że w zestawie danych znajdują się nagłówki.
- Zakres kryteriów: Określ kryteria, które stworzyliśmy w powyższych krokach. W tym przykładzie byłoby to F1:I3.
- Kopiuj do: Podaj adres komórki, w której chcesz uzyskać listę unikalnych rekordów.
- Kopiuj tylko unikalne rekordy: Zaznacz tę opcję.
- Kliknij OK.
To natychmiast dałoby ci wszystkie rekordy, w których regionem jest USA, a sprzedaż przekracza 5000.
Powyższy przykład to przypadek, w którym filtrowanie odbywa się na podstawie dwóch kryteriów (USA i sprzedaż powyżej 5000).
Filtr Excel Advanced umożliwia tworzenie wielu różnych kombinacji kryteriów.
Oto kilka przykładów tworzenia tych filtrów.
Korzystanie z kryteriów ORAZ
Jeśli chcesz użyć kryteriów ORAZ, musisz je określić poniżej nagłówka.
Na przykład:
- Aby filtrować rekordy, gdy regionem jest USA, a przedstawicielem handlowym jest Joe.
- Aby filtrować rekordy, gdy region to Stany Zjednoczone ORAZ wartość sprzedaży jest większa niż 5000.
- Gdy regionem jest USA ORAZ sprzedaż jest rejestrowana po 31-03-2017.
Korzystanie z kryteriów OR
Jeśli chcesz użyć kryteriów OR, musisz określić kryteria w tej samej kolumnie.
Na przykład:
- Aby filtrować rekordy, gdy regionem są Stany Zjednoczone LUB regionem jest Azja.
- Aby filtrować rekordy, gdy przedstawicielem handlowym jest Bob LUB Marta.
Przykład 3 - używanie znaków WILDCARD w filtrze zaawansowanym w programie Excel
Zaawansowany filtr Excela pozwala również na użycie symboli wieloznacznych podczas konstruowania kryteriów.
W programie Excel są trzy znaki wieloznaczne:
- * (gwiazdka) - Reprezentuje dowolną liczbę znaków. Na przykład ex* może oznaczać excel, excels, example, expert itp.
- ? (znak zapytania) - Reprezentuje jedną postać. Na przykład Tr?mp może oznaczać Trump lub Tramp.
- ~ (tylda) - Służy do identyfikacji znaku wieloznacznego (~, *, ?) w tekście.
Zobaczmy teraz, jak możemy użyć tych symboli wieloznacznych do zaawansowanego filtrowania w programie Excel.
- Aby filtrować rekordy, w których nazwa przedstawiciela handlowego zaczyna się od J.
Zauważ, że * reprezentuje dowolną liczbę znaków. Więc każdy przedstawiciel o nazwie zaczynającej się od J będzie filtrowany według tych kryteriów.
Podobnie, możesz również użyć pozostałych dwóch symboli wieloznacznych.
Uwaga: jeśli korzystasz z Office 365, powinieneś sprawdzić funkcję FILTR. Może zrobić wiele rzeczy, które zaawansowany filtr może zrobić za pomocą prostej formuły.
NOTATKA:
- Pamiętaj, że nagłówki w kryteriach powinny być dokładnie takie same jak w zestawie danych.
- Filtrowania zaawansowanego nie można cofnąć podczas kopiowania do innych lokalizacji.
