Dla zdecydowanej większości użytkowników Excela, gdy w głowie pojawia się słowo „filtrowanie danych”, tylko zwykły klasyczny filtr z zakładki Dane – Filtr (Dane — Filtr):
Taki filtr jest bez wątpienia znany i w większości przypadków wystarczy. Zdarzają się jednak sytuacje, w których konieczne jest filtrowanie według dużej liczby złożonych warunków w kilku kolumnach jednocześnie. Zwykły filtr tutaj nie jest zbyt wygodny i chcę czegoś mocniejszego. Takim narzędziem może być zaawansowany filtr, zwłaszcza z niewielkim „wykończeniem pilnikiem” (zgodnie z tradycją).
Podstawa
Aby rozpocząć, wstaw kilka pustych wierszy nad tabelą danych i skopiuj tam nagłówek tabeli – będzie to zakres z warunkami (zaznaczonymi na żółto dla jasności):
Między żółtymi komórkami a oryginalną tabelą musi znajdować się co najmniej jedna pusta linia.
To w żółtych komórkach należy wpisać kryteria (warunki), według których zostanie następnie wykonane filtrowanie. Na przykład, jeśli chcesz wybrać banany w moskiewskim „Auchan” w III kwartale, warunki będą wyglądać tak:
Aby filtrować, wybierz dowolną komórkę w zakresie z danymi źródłowymi, otwórz kartę Dane I kliknij do tego (Dane — Zaawansowane). W oknie, które się otworzy, zakres z danymi powinien już być wpisany automatycznie, a my będziemy musieli jedynie określić zakres warunków, czyli A1:I2:
Należy pamiętać, że zakres warunków nie może być przydzielony „z marginesem”, tj. nie można wybrać dodatkowych pustych żółtych linii, ponieważ pusta komórka w zakresie warunków jest odbierana przez Excel jako brak kryterium, a cała pusta wiersz jako żądanie wyświetlania wszystkich danych bez rozróżnienia.
Przełącznik Skopiuj wynik do innej lokalizacji pozwoli Ci przefiltrować listę nie w tym arkuszu (jak przy zwykłym filtrze), ale wyładować wybrane wiersze do innego zakresu, który następnie trzeba będzie określić w polu Umieść wynik w zakresie. W takim przypadku nie korzystamy z tej funkcji, wychodzimy Lista filtrów na miejscu i kliknij OK. Wybrane wiersze zostaną wyświetlone na arkuszu:
Dodawanie makra
„Cóż, gdzie jest tu wygoda?” pytasz i będziesz miał rację. Nie tylko musisz wpisać warunki do żółtych komórek rękami, ale także otworzyć okno dialogowe, wpisać tam zakresy, nacisnąć OK. Smutne, zgadzam się! Ale „wszystko się zmienia, gdy przychodzą ©” – makra!
Pracę z filtrem zaawansowanym można znacznie przyspieszyć i uprościć za pomocą prostego makra, które automatycznie uruchomi filtr zaawansowany po wprowadzeniu warunków, tj. zmianie dowolnej żółtej komórki. Kliknij prawym przyciskiem myszy zakładkę bieżącego arkusza i wybierz polecenie Tekst źródłowy (Kod źródłowy). W oknie, które się otworzy, skopiuj i wklej następujący kod:
Private Sub Worksheet_Change(ByVal Target As Range) Jeśli nie przecina(Target, Range("A2:I5")) to nic, to przy błędzie Wznów następny ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion Koniec Jeśli Koniec Sub Ta procedura zostanie uruchomiona automatycznie po zmianie dowolnej komórki w bieżącym arkuszu. Jeśli adres zmienionej komórki mieści się w zakresie żółtym (A2:I5), to makro usuwa wszystkie filtry (jeśli są) i ponownie stosuje filtr rozszerzony do tabeli danych źródłowych zaczynając od A7, czyli wszystko zostanie natychmiast przefiltrowane po wpisaniu kolejnego warunku:
Więc wszystko jest o wiele lepsze, prawda?
Wdrażanie złożonych zapytań
Teraz, gdy wszystko jest filtrowane w locie, możemy nieco głębiej zagłębić się w niuanse i zdemontować mechanizmy bardziej złożonych zapytań w filtrze zaawansowanym. Oprócz wprowadzania dokładnych dopasowań możesz użyć różnych symboli wieloznacznych (* i ?) oraz matematycznych znaków nierówności w różnych warunkach, aby zaimplementować wyszukiwanie przybliżone. Wielkość liter nie ma znaczenia. Dla jasności podsumowałem wszystkie możliwe opcje w tabeli:
| Kryterium | Wynik |
| gr* lub gr | wszystkie komórki zaczynające się od GrIe Grucho, Growoc jabłkowy, Grostatni rok itd. |
| = cebula | wszystkie komórki dokładnie i tylko ze słowem Łuk, czyli dopasowanie ścisłe |
| *liv* lub *liv | komórki zawierające Liv jak podkreślić, tj ОLivże, Livep, StosownieLiv itd. |
| =p*w | słowa zaczynające się od П i kończąc na В ie П drugimв, Пeterв itd. |
| jak | słowa zaczynające się od А i dalej zawierające СIe Аpelсin, Аnanaс, Asai itd. |
| =*s | słowa kończące się na С |
| =???? | wszystkie komórki z tekstem 4 znaków (litery lub cyfry ze spacjami) |
| =m????n | wszystkie komórki z tekstem 8 znaków zaczynających się od М i kończąc na НIe МAndariн, Мniepokójн itd. |
| =*nie | wszystkie słowa kończą się na А, gdzie jest czwarta litera od końca НIe Belkaнikа, Stosownieнozа itd. |
| >=e | wszystkie słowa zaczynające się od Э, Ю or Я |
| <>*o* | wszystkie słowa, które nie zawierają litery О |
| <>*wicz | wszystkie słowa oprócz tych kończących się na HIV (na przykład filtruj kobiety według drugiego imienia) |
| = | wszystkie puste komórki |
| <> | wszystkie niepuste komórki |
| > = 5000 | wszystkie komórki o wartości większej lub równej 5000 |
| 5 lub =5 | wszystkie komórki o wartości 5 |
| > = 3/18/2013 | wszystkie komórki z datą po 18 marca 2013 (włącznie) |
Subtelne punkty:
- Znak * oznacza dowolną liczbę dowolnych znaków, a ? – dowolna jedna postać.
- Logika przetwarzania zapytań tekstowych i liczbowych jest nieco inna. Na przykład komórka warunku o numerze 5 nie oznacza wyszukiwania wszystkich liczb zaczynających się od pięciu, ale komórka warunku z literą B jest równa B*, czyli będzie szukać dowolnego tekstu zaczynającego się na literę B.
- Jeśli zapytanie tekstowe nie zaczyna się od znaku =, możesz w myślach umieścić * na końcu.
- Daty należy wprowadzać w formacie amerykańskim miesiąc-dzień-rok i ułamek zwykły (nawet jeśli masz Excel i ustawienia regionalne).
Spójniki logiczne AND-LUB
Warunki zapisane w różnych komórkach, ale w tym samym wierszu, są uważane za połączone operatorem logicznym И (ORAZ):
Tych. banany filtracyjne dla mnie w trzecim kwartale, dokładnie w Moskwie i jednocześnie z Auchan.
Jeśli potrzebujesz połączyć warunki z operatorem logicznym OR (OR), wystarczy je wpisać w różnych wierszach. Na przykład, jeśli musimy znaleźć wszystkie zamówienia kierownika Voliny na moskiewskie brzoskwinie i wszystkie zamówienia na cebulę w trzecim kwartale w Samarze, to można to określić w następującym zakresie warunków:
Jeśli potrzebujesz nałożyć dwa lub więcej warunków na jedną kolumnę, możesz po prostu zduplikować nagłówek kolumny w zakresie kryteriów i wpisać pod nim drugi, trzeci itd. semestry. Na przykład możesz wybrać wszystkie transakcje od marca do maja:
Generalnie po „dokończeniu pliku” filtr zaawansowany okazuje się całkiem przyzwoitym narzędziem, w niektórych miejscach nie gorszym od klasycznego autofiltra.
- Superfiltr na makrach
- Czym są makra, gdzie i jak wstawiać kod makr w Visual Basic
- Inteligentne tabele w Microsoft Excel