Jak filtrować w Excelu według kolumn. Zaawansowany filtr w Excelu i przykłady jego funkcji

Prawdopodobnie wszyscy użytkownicy, z którymi stale pracują Microsoft Excel są tego świadomi użyteczna funkcja ten program jako filtrowanie danych. Ale nie wszyscy zdają sobie sprawę, że istnieją również zaawansowane funkcje tego narzędzia. Przyjrzyjmy się, co potrafi zaawansowany filtr Microsoft Excel i jak z niego korzystać.

Nie wystarczy od razu uruchomić filtr zaawansowany - do tego musi być spełniony jeszcze jeden warunek. Następnie opiszemy kolejność działań, które należy podjąć.

Krok 1: Utwórz tabelę z kryteriami wyboru

Aby zainstalować zaawansowany filtr, musisz przede wszystkim stworzyć dodatkową tabelę z warunkami selekcji. Jej kapelusz jest dokładnie taki sam jak główny, który w rzeczywistości przefiltrujemy. Na przykład umieściliśmy dodatkowy stół nad głównym i pokolorowaliśmy jego komórki na pomarańczowo. Chociaż możesz umieścić go w dowolnym wolnym miejscu, a nawet na innym arkuszu.

Teraz w tabeli dodatkowej wpisujemy informacje, które będą musiały zostać odfiltrowane z tabeli głównej. W naszym konkretnym przypadku z listy wynagrodzeń wydanych pracownikom postanowiliśmy wybrać dane dotyczące głównego personelu męskiego na dzień 25.07.2016.

Krok 2: Uruchamianie filtra zaawansowanego

Dopiero po utworzeniu dodatkowej tabeli można przystąpić do uruchomienia filtra zaawansowanego.

Możemy zatem stwierdzić, że filtr zaawansowany zapewnia więcej opcji niż zwykłe filtrowanie danych. Należy jednak zauważyć, że praca z tym narzędziem jest nadal mniej wygodna niż ze standardowym filtrem.

Główną wadą standardu ( ) to brak wizualnej informacji o zastosowanym ten moment filtr: za każdym razem, gdy musisz przejść do menu filtrów, aby zapamiętać kryteria wyboru rekordów. Jest to szczególnie niewygodne, gdy stosuje się wiele kryteriów. Filtr zaawansowany nie ma tej wady - wszystkie kryteria umieszczone są w osobnej tabeli nad przefiltrowanymi rekordami.

Algorytm tworzenia zaawansowany filtr prosty:

• Utwórz tabelę, do której zostanie zastosowany filtr (tabela źródłowa);
• Tworzymy tabliczkę z kryteriami (z warunkami selekcji);
• uruchamiamy Zaawansowany filtr.

Wpuść w zakres A 7:C 83 znajduje się tabela początkowa z listą towarów zawierającą pola (kolumny) Produkt, Ilość I Cena £(patrz przykładowy plik). Tabela nie może zawierać pustych wierszy i kolumn, w przeciwnym razie Zaawansowany filtr(i normalne) nie będą działać poprawnie.

Zadanie 1 (rozpoczęcie...)

Ustaw filtr, aby wybrać wiersze zawierające wartości w nazwie Produktu początek od słowa Paznokcie. Ten warunek wyboru spełniają linie z towarami gwoździe 20 mm, Gwoździe 10 mm, Gwoździe 10 mm I Paznokcie.

ALE 1 :A2 A2 wskazać słowo Paznokcie.

Notatka: Struktura kryteriów y zaawansowany filtr jest jasno zdefiniowany i pokrywa się ze strukturą kryteriów dla funkcji BDSUMM() , COUNT() , itd.

Zwykle kryteria zaawansowany filtr są umieszczone nad stołem, do którego nałożony jest filtr, ale można je również umieścić z boku stołu. Unikaj umieszczania etykiety z kryteriami pod oryginalną tabelą, chociaż nie jest to zabronione, nie zawsze jest wygodne, ponieważ do oryginalnej tabeli można dodać nowe wiersze.

UWAGA!
Upewnij się, że między tabelą z wartościami warunków selekcji a oryginalną tabelą jest co najmniej jedna pusta linia (ułatwi to pracę z zaawansowany filtr).

Zaawansowany filtr:

• dzwonić Zaawansowany filtr();
• w terenie oryginalny zakres A 7:C 83 );
• w terenie Zakres warunków ALE1 :A2 .

W razie potrzeby możesz skopiować wybrane wiersze do innej tabeli, ustawiając przełącznik w pozycji Skopiuj wynik do innej lokalizacji. Ale nie zrobimy tego tutaj.

Naciśnij przycisk OK, a filtr zostanie zastosowany - w tabeli pozostaną tylko wiersze zawierające nazwę produktu w kolumnie gwoździe 20 mm, Gwoździe 10 mm, Gwoździe 50 mm I Paznokcie. Reszta linii zostanie ukryta.

Wybrane numery linii zostaną podświetlone na niebiesko.

Aby anulować akcję filtrowania, wybierz dowolną komórkę w tabeli i kliknij CTRL+SHIFT+L(tytuł zostanie zastosowany, a akcja) zaawansowany filtr zostanie anulowany) lub naciśnij przycisk menu Jasny ().

Zadanie 2 (dokładne dopasowanie)

Skonfigurujmy filtr, aby wybrać wiersze, które mają w kolumnie Produkt dokładnie zawiera słowo Paznokcie. Ten warunek wyboru spełniają tylko wiersze z towarami paznokcie I Paznokcie(nie liczy się). Wartości gwoździe 20 mm, Gwoździe 10 mm, Gwoździe 50 mm nie będą brane pod uwagę.

Umieścimy płytkę z warunkiem selekcji Umieścimy ją w zakresie B1:B2 . Tabliczka powinna również zawierać nazwę nagłówka kolumny, według której dokonywany będzie wybór. Jako kryterium w komórce B2 podaj formułę ="= Paznokcie”.

Teraz wszystko jest gotowe do pracy Zaawansowany filtr:

• wybierz dowolną komórkę tabeli (nie jest to konieczne, ale przyspieszy wypełnianie parametrów filtra);
• dzwonić zaawansowany filtr ( Dane/ Sortuj i filtruj/ Zaawansowane);
• w terenie oryginalny zakres upewnij się, że zakres komórek tabeli jest określony wraz z nagłówkami ( A 7:C 83 );
• w terenie Zakres warunków podać komórki zawierające etykietę z kryterium, tj. zakres B1:B2 .
• Kliknij OK

Zastosować Zaawansowany filtr przy tak prostych kryteriach nie ma to większego sensu, ponieważ radzić sobie z tymi zadaniami z łatwością. Autofiltr. Rozważmy bardziej złożone zadania filtrowania.

Jeśli określisz nie ="=Gwoździe" jako kryterium, ale po prostu Paznokcie, zostaną wyświetlone wszystkie rekordy zawierające nazwiska początek od słowa gwoździe ( Gwoździe 80mm, Paznokcie2). Aby wyświetlić linie produktów, zawierający na słowo paznokcie, na przykład, Nowe paznokcie, musisz określić ="=*Gwoździe" jako kryterium lub po prostu * Gwoździe gdzie* to i oznacza dowolny ciąg znaków.

Zadanie 3 (warunek OR dla jednej kolumny)

Skonfiguruj filtr, aby wybrać wiersze, których kolumna Produkt zawiera wartość zaczynającą się od słowa PaznokcieLUB Tapeta.

Kryteria wyboru w tym przypadku należy umieścić pod odpowiednim nagłówkiem kolumny ( Produkt) i powinny znajdować się jeden pod drugim w jednej kolumnie (patrz rysunek poniżej). Umieść tabliczkę z kryteriami w zakresie C1:C3 .

Okno z Zaawansowanymi opcjami filtrowania oraz tabela z przefiltrowanymi danymi będzie wyglądać tak.

Po kliknięciu OK zostaną wyświetlone wszystkie rekordy zawarte w kolumnie Produkt produkty Paznokcie LUB Tapeta.

Zadanie 4 (warunek ORAZ)

dokładnie zawierać w kolumnie Produkt produkty Paznokcie, a w kolumnie Ilość wartość >40. Kryteria wyboru w tym przypadku powinny być umieszczone pod odpowiednimi nagłówkami (Produkt i Ilość) i powinny znajdować się w tym samym wierszu. Warunki selekcji należy zapisać w specjalnym formacie: ="= Paznokcie” i =">40" . Umieścimy płytkę z warunkiem selekcji Umieścimy ją w zakresie E1:F2 .

Po naciśnięciu przycisku OK zostaną wyświetlone wszystkie rekordy zawarte w kolumnie Produkt produkty Paznokcie z liczbą >40.

RADA: Przy zmianie kryteriów wyboru lepiej za każdym razem stworzyć tabliczkę z kryteriami i po wywołaniu filtra zmień tylko link do nich.

Notatka: Gdybyś musiał wyczyścić opcje Filtra zaawansowanego ( Dane/ Sortuj i filtruj/ Wyczyść), następnie przed wywołaniem filtra zaznacz dowolną komórkę w tabeli - EXCEL automatycznie wstawi link do zakresu zajmowanego przez tabelę (jeśli w tabeli są puste wiersze, link nie zostanie wstawiony do całej tabeli, ale tylko do pierwszego pustego rzędu).

Zadanie 5 (warunek OR dla różnych kolumn)

Poprzednie problemy można było, w razie potrzeby, rozwiązać zwykłym . Tego samego problemu nie da się rozwiązać za pomocą konwencjonalnego filtra.

Wybierzmy tylko te wiersze tabeli, które dokładnie zawierać w kolumnie Produkt produkty Paznokcie, OR które są w kolumnie Ilość zawierać wartość >40. Kryteria wyboru w tym przypadku należy umieścić pod odpowiednimi nagłówkami (Pozycja i Ilość) i należy je umieścić na różne linie . Warunki selekcji muszą być zapisane w specjalnym formacie: ="> 40" i ="= Paznokcie”. Umieścimy płytkę z warunkiem selekcji Umieścimy ją w zakresie E4:F6 .

Po naciśnięciu przycisku OK zostaną wyświetlone rekordy zawarte w kolumnie Produkt produkty Paznokcie Wartość LUB >40 (dla dowolnego produktu).

Zadanie 6 (Warunki selekcji powstałe w wyniku zastosowania wzoru)

prawdziwa moc zaawansowany filtr objawia się, gdy formuły są używane jako warunki wyboru.

Istnieją dwie opcje określania warunków wyboru ciągu:

• bezpośrednio wprowadź wartości dla kryterium (patrz zadania powyżej);
• wygenerować kryterium na podstawie wyników realizacji formuły.

Rozważ kryteria podane przez formułę. Formuła określona jako kryterium wyboru musi zwracać PRAWDA lub FAŁSZ.

Na przykład wyświetlmy wiersze zawierające Produkt, który występuje w tabeli tylko 1 raz. Aby to zrobić, wpisz w komórce H2 formuła =LICZ.JEŻELI(Arkusz1!$A8$:$A83$,A8)=1, i w H1 zamiast nagłówka wprowadź tekst objaśniający, na przykład Wartości niezduplikowane. Odpowiedni Zaawansowany filtr, określając zakres warunków komórki H1:H2 .

Należy zauważyć, że zakres wyszukiwania wartości jest wprowadzany za pomocą , a kryterium w funkcji LICZ.JEŻELI() jest wprowadzane z odwołaniem względnym. Jest to konieczne, ponieważ podczas korzystania zaawansowany filtr EXCEL to zobaczy A8 jest odniesieniem względnym i będzie przesuwać się w dół kolumny Produkt o jeden rekord naraz i zwracać TRUE lub FALSE. Jeśli zwrócona zostanie wartość TRUE, zostanie wyświetlony odpowiedni wiersz tabeli. Jeśli zwracana jest wartość FALSE, wiersz nie zostanie wyświetlony po zastosowaniu filtru.

Przykłady innych formuł z przykładowego pliku:

• Wypisz wiersze z cenami większymi niż trzecia najwyższa cena w tabeli. =C8>DUŻY($C$8:$C$83;5) Ten przykład wyraźnie pokazuje zawiłość funkcji NAJWIĘKSZE(). Jeśli posortujesz kolumnę OD (ceny) otrzymujemy: 750; 700; 700 ; 700; 620, 620, 160, ... W kategoriach ludzkich „trzecia najwyższa cena” odpowiada 620, a w zrozumieniu funkcji GREAT() - 700 . W rezultacie nie zostaną wyświetlone 4 linie, ale tylko jedna (750);
• Wielkość liter w danych wyjściowych ciągu znaków =DOKŁADNIE("gwoździe", A8). Zostaną wyświetlone tylko te wiersze, w których będzie wyświetlany produkt. paznokcie wprowadzane za pomocą małych liter;
• Wyjście rzędów z ceną wyższą niż średnia =C8>ŚREDNIA(8$C$:$83C$);

UWAGA!
Wniosek zaawansowany filtr anuluje filtr zastosowany do tabeli ( Dane/ Sortuj i filtruj/ Filtruj).

Zadanie 7 (Warunki wyboru zawierają formuły i zwykłe kryteria)

Rozważ teraz inny stół od przykładowy plik na arkuszu Zadanie 7.

W kolumnie Produkt podana jest nazwa produktu, aw kolumnie Rodzaj produktu- w jego typie.

Zadaniem jest wyświetlanie produktów o cenie poniżej średniej dla danego typu produktu. Czyli mamy 3 kryteria: pierwsze kryterium określa Produkt, drugie jego Rodzaj, a trzecie kryterium (w postaci wzoru) określa cenę poniżej średniej.

Kryteria umieścimy w wierszach 6 i 7. Wprowadź wymagany produkt i typ produktu. Dla danego typu produktu obliczamy średnią i wyświetlamy ją dla przejrzystości w osobnej komórce F7. W zasadzie formułę można wprowadzić bezpośrednio do formuły kryterium w komórce C7.

Wyświetlone zostaną 2 z 4 produktów (z określonego typu produktu).

Problem 7.1. (Czy 2 wartości w tej samej linii są takie same?)

Znajduje się tam tabela, która pokazuje rok produkcji i rok zakupu samochodu.

Chcesz wyświetlić tylko te wiersze, w których rok wydania jest taki sam jak rok zakupu. Można to zrobić za pomocą podstawowego wzoru =B10=C10.

Problem 8 (Czy symbol jest liczbą?)

Załóżmy, że mamy stół z listą różne rodzaje paznokcie.

Wymagane jest filtrowanie tylko tych wierszy, których kolumna Produkt zawiera Gwoździe 1 cal, Gwoździe 2 cale itp. dobra Gwoździe nierdzewne, gwoździe chromowane itp. nie powinny być filtrowane.

Najprostszym sposobem na to jest ustawienie warunku jako filtru, że po słowie Nails powinna znajdować się liczba. Można to zrobić za pomocą wzoru =CZY.LICZBA(--ŚREDNIA(A11;LSTR($A$8)+2,1))

Formuła usuwa 1 znak z nazwy produktu po słowie Nails (ze spacjami). Jeżeli ten znak jest liczbą (cyfrą), to formuła zwraca TRUE i wyświetlany jest ciąg znaków, w Inaczej linia nie jest wyprowadzana. Kolumna F pokazuje, jak działa formuła, tj. można go przetestować przed uruchomieniem zaawansowany filtr.

Zadanie 9 (Wydaj wiersze NIE ZAWIERAJĄCE danego Towaru)

Wymagane jest filtrowanie tylko tych wierszy, które NIE zawierają w kolumnie Produkt: Gwoździe, deska, klej, tapeta.

Do tego będziesz musiał użyć prosta formuła =KONIEC(WYSZUKAJ.PIONOWO(A15;$A$8:$A$11,1,0))

Użyj Autofiltru lub wbudowanych operatorów porównania, takich jak „większe niż” i „top 10” w programie Excel, aby wyświetlić żądane dane i ukryć resztę. Po przefiltrowaniu danych w zakresie komórek lub tabeli możesz ponownie zastosować filtr, aby uzyskać aktualne wyniki, lub wyczyścić filtr, aby ponownie wyświetlić wszystkie dane.

Użyj filtrów, aby tymczasowo ukryć niektóre dane w tabeli i wyświetlić tylko te dane, które chcesz.

Filtrowanie zakresu danych

Filtrowanie danych w tabeli

Przefiltrowane dane pokazują tylko wiersze spełniające określony warunek i ukrywają wiersze, których nie chcesz wyświetlać. Po przefiltrowaniu danych możesz kopiować, wyszukiwać, modyfikować, formatować, wykresy i drukować podzbiór przefiltrowanych danych bez ich przenoszenia lub modyfikowania.

Możesz także filtrować według wielu kolumn. Filtry są addytywne, co oznacza, że ​​każdy dodatkowy filtr opiera się na bieżącym filtrze i dodatkowo redukuje podzbiór danych.

Notatka: Podczas korzystania z okna dialogowego Szukaj aby wyszukać filtrowane dane, przeszukiwane są tylko te dane, które pojawiają się na liście. Dane, które nie są wyświetlane, nie są przeszukiwane. Aby znaleźć wszystkie dane, wyczyść wszystkie filtry.

Dodatkowe informacje o filtrowaniu

Dwa rodzaje filtrów

Dzięki autofiltrowi możesz tworzyć dwa typy filtrów: według wartości listy lub według kryteriów. Każdy z tych typów filtrów wyklucza się wzajemnie dla każdego zakresu komórek lub tabeli kolumn. Na przykład możesz filtrować według listy liczb lub warunku, ale nie obu; Możesz filtrować według ikony lub filtra niestandardowego, ale nie obu.

Ponowne stosowanie filtra

Aby określić, czy zastosowano filtr, spójrz na ikonę w nagłówku kolumny.

Ponowne zastosowanie filtra daje różne wyniki z następujących powodów.

Dodano, zmieniono lub usunięto dane w zakresie komórek lub kolumnie tabeli.

wartości zwracane przez formułę uległy zmianie i arkusz został przeliczony.

Nie używaj mieszanych typów danych

Za osiągnięcia najlepsze wyniki Nie należy mieszać typów danych, takich jak tekst i liczba lub liczby i daty w tej samej kolumnie, ponieważ dla każdej kolumny dostępny jest tylko jeden typ polecenia filtrowania. Jeśli używana jest mieszanka typów danych, wyświetlane polecenie jest najczęściej wywoływanym typem danych. Na przykład, jeśli kolumna zawiera trzy wartości zapisane jako liczba i cztery zapisane jako tekst, polecenie zostanie wyświetlone filtry tekstowe .

Filtrowanie danych w tabeli

Gdy wprowadzasz dane do tabeli, kontrolki filtrowania są automatycznie dodawane do nagłówków jej kolumn.

Filtrowanie zakresu danych

Jeśli nie chcesz formatować swoich danych jako tabeli, możesz również zastosować filtry do zakresu danych.

Wybierz dane, które chcesz filtrować. Aby uzyskać najlepsze wyniki, kolumny powinny zawierać nagłówki.

Na karcie „ dane"naciśnij przycisk" Filtr".

Opcje filtrowania tabel i zakresów

Możesz zastosować filtr ogólny, wybierając Filtr lub niestandardowy filtr oparty na typie danych. Na przykład podczas filtrowania liczb wyświetlany jest element Filtry numeryczne, pozycja jest wyświetlana dla dat Filtry daty, a dla tekstu - Filtry tekstu. Stosując filtr ogólny, możesz wybrać wyświetlanie żądanych danych z listy istniejących, jak pokazano na rysunku:

Wybór opcji Filtry numeryczne możesz zastosować jeden z następujących filtrów niestandardowych.

Filtrowanie danych programu Excel pomaga szybko ustawić warunki dla wierszy, które chcesz wyświetlić, i ukryć pozostałe wiersze, które nie spełniają warunków.

Filtr jest ustawiony na nagłówki i podtytuły tabel; najważniejsze jest to, że komórki, na których zostanie zainstalowany filtr, nie są puste. Znajduje się w menu skoroszytu programu Excel na karcie Dane w sekcji Sortuj i filtruj:

Klikając w ikonę „Filtruj”, górne komórki zakresu zostaną zdefiniowane jako nagłówki i nie będą brały udziału w filtrowaniu. Nagłówki będą oznaczone . Kliknij go, aby zobaczyć opcje filtrowania:

Filtry w Excelu umożliwiają sortowanie. Pamiętaj, że jeśli nie zaznaczyłeś wszystkich kolumn tabeli, a tylko część i zastosujesz sortowanie, to dane zostaną utracone.

„Filtruj według koloru” pozwala wybrać wiersze w kolumnie, które mają określoną czcionkę lub kolor wypełnienia. Możesz wybrać tylko jeden kolor.

„Filtry tekstu” umożliwiają ustawienie pewnych warunków dla ciągów, takich jak: „równe”, „nie równe” i inne. Wybierając dowolny z tych elementów, pojawi się okno:

Możesz w nim ustawić następujące warunki:

• Warunki „równe” i „nierówne” nie wymagają wyjaśnienia, ponieważ wszystko jest z nimi bardzo jasne;
• większy niż, mniejszy niż, większy lub równy i mniejszy niż lub równy. Jak można porównać struny ze sobą? Aby to zrozumieć, pamiętaj, jak sortuje program Excel. Tych. im dalej ciąg znajduje się na liście sortowania, tym większa jest jego wartość. Następujące stwierdzenia są prawdziwe (poprawne): A<Б; АА>ALE; ALE<=Я; 5 яблок < апельсин.
• „zaczyna się od”, „nie zaczyna się od”, „kończy się na”, „nie kończy się na”, „zawiera” i „nie zawiera”. W zasadzie warunki mówią same za siebie i mogą przyjąć postać lub zestaw znaków jako wartości. Zwróć uwagę na wskazówkę w oknie znajdującym się poniżej wszystkich warunków (pojawią się wyjaśnienia).

W razie potrzeby można ustawić 2 warunki za pomocą logicznego „ORAZ” lub „LUB”.

Jeśli wybrano „ORAZ”, muszą być spełnione wszystkie warunki. Uważaj na warunki, które nie wykluczają znajomego, takie jak „<Значение И >Znaczenie”, ponieważ nic w tym samym momencie nie może być jednocześnie więcej i mniej niż ten sam wskaźnik.

W przypadku korzystania z opcji „LUB” musi być spełniony co najmniej jeden z określonych warunków.

Podpowiedź znajduje się na samym końcu okna Custom AutoFilter. Jego pierwsza część: "Znak zapytania ""?" oznacza dowolny znak...". Tych. przy ustalaniu warunków, w których nie jest możliwe dokładne określenie znaku w określonym miejscu ciągu, w jego miejsce należy wstawić znak „?”. Przykłady warunków:

• Rozpoczęcie od „?va” (zaczyna się od dowolnego znaku, po którym następuje „va”) zwróci wyniki: „Ivanov”, „Ivanova”, „quartz”, „svat” i inne ciągi pasujące do warunku;
• Równa się "???????" - zwróci w wyniku ciąg zawierający 7 dowolnych znaków.

Druga część podpowiedzi: „Znak „”*”” oznacza sekwencję dowolnych znaków”. Jeśli nie można określić w warunku, które znaki iw jakiej ilości powinny znajdować się w ciągu, zamiast tego należy wstawić „*”. Przykłady warunków:

• Kończy się na „o * t” (kończy się na „o”, po którym następuje dowolna sekwencja znaków, a następnie znak „t”) zwróci wynik: „pot”, „ciasto”, „obrót”, a nawet to - „rvnschuoooviunistvrunkt” .
• Równe "*" — zwróci ciąg, który zawiera co najmniej jeden znak.

Oprócz filtrów tekstowych istnieją „Filtry liczbowe”, które zasadniczo akceptują te same warunki co filtry tekstowe, ale mają też dodatkowe, związane tylko z liczbami:

• Powyżej średniej i poniżej średniej — zwraca wartości, które są odpowiednio powyżej i poniżej średniej. Średnia wartość jest obliczana ze wszystkich wartości liczbowych na liście kolumn;
• „Pierwsze 10…” – kliknięcie tego elementu powoduje wyświetlenie okna:

Tutaj możesz określić, które elementy mają być wyświetlane jako pierwszy z największych lub pierwszy z najmniejszych. Ponadto, ile elementów ma zostać wyświetlonych, jeśli element „elementy listy” jest zaznaczony w ostatnim polu. Jeśli wybrana jest pozycja „% liczby elementów”, druga wartość określa ten procent. Tych. jeśli na liście jest 10 wartości, zostanie wybrana najwyższa (lub najniższa) wartość. Jeśli na liście jest 1000 wartości, to pierwsze lub ostatnie 100.

Microsoft Excel to wszechobecne i przyjazne dla użytkownika narzędzie do arkuszy kalkulacyjnych. Szeroka funkcjonalność sprawia, że ​​program ten jest drugim po MS Word najpopularniejszym wśród wszystkich programów biurowych. Korzystają z niego ekonomiści, księgowi, naukowcy, studenci i przedstawiciele innych zawodów, którzy potrzebują przetwarzać dane matematyczne.

Jedną z najwygodniejszych funkcji tego programu jest filtrowanie danych. Zastanów się, jak skonfigurować i używać filtrów MS Excel.

Gdzie są filtry w Excelu - ich rodzaje

Wyszukiwanie filtrów w tym programie jest łatwe - musisz otworzyć menu główne lub po prostu przytrzymać klawisze Ctrl + Shift + L.

Jak ustawić filtr w Excelu

Podstawowe funkcje filtrowania w Excelu:

• filtruj po kolorze: pozwala sortować dane według czcionki lub koloru wypełnienia,
• filtry tekstowe w programie Excel: pozwalają ustawić pewne warunki dla wierszy, na przykład: mniejszy niż, większy niż, równy, nie równy i inne, a także ustawić warunki logiczne - i, lub,
• filtry numeryczne: sortuj według warunków liczbowych, takich jak poniżej średniej, top 10 i inne,
• ręczny: wybór może być dokonany zgodnie z samodzielnie wybranymi kryteriami.

Są łatwe w użyciu. Należy wybrać tabelę i wybrać w menu sekcję z filtrami, a następnie wyjaśnić, według jakiego kryterium dane będą przeszukiwane.

Jak korzystać z zaawansowanego filtra w Excelu - jak go dostosować

Standardowy filtr ma sporą wadę - aby zapamiętać, jakie kryteria wyboru zostały użyte, należy otworzyć menu. A tym bardziej powoduje to niedogodności, gdy ustawione jest więcej niż jedno kryterium. Z tego punktu widzenia wygodniejszy jest filtr zaawansowany, który jest wyświetlany jako osobna tabela nad danymi.

INSTRUKCJA WIDEO

Kolejność ustawiania:

1. Utwórz tabelę z danymi do dalszej pracy z nią. Nie powinien zawierać pustych wierszy.
2. Utwórz tabelę z warunkami selekcji.
3. Uruchom filtr zaawansowany.

Spójrzmy na przykładową konfigurację.
Mamy tabelę z kolumnami Produkt, Ilość i Cena.

Na przykład musisz posortować wiersze, których nazwy produktów zaczynają się od słowa „Gwoździe”. Warunek ten obejmuje kilka wierszy.

Tabela z warunkami zostanie umieszczona w komórkach A1:A2. Ważne jest, aby wskazać nazwę kolumny, w której nastąpi selekcja (komórka A1) oraz samo słowo do selekcji - Gwoździe (komórka A2).

Najwygodniej jest umieścić go nad danymi lub z boku. Nie jest to również zabronione pod nim, ale nie zawsze jest to wygodne, ponieważ od czasu do czasu może być konieczne dodanie dodatkowych linii. Wcięcie co najmniej jednego pustego wiersza między dwiema tabelami.

Następnie potrzebujesz:

1. wybierz dowolną z komórek
2. otwórz "Filtr zaawansowany" wzdłuż ścieżki: Dane - Sortuj i filtruj - Zaawansowane,
3. sprawdź co jest określone w polu "Początkowy zakres" - cała tabela z informacjami powinna tam trafić,
4. w „Zakresie warunków” należy ustawić wartości komórek z warunkiem selekcji, w tym przykładzie jest to zakres A1:A2.

Po kliknięciu przycisku „OK” zostaną wybrane niezbędne informacje, aw tabeli pojawią się tylko wiersze z żądanym słowem, w naszym przypadku jest to „Gwoździe”. Pozostałe numery linii zmienią kolor na niebieski. Aby anulować określony filtr, po prostu naciśnij klawisze CTRL+SHIFT+L.

Można również łatwo filtrować ciągi zawierające dokładnie słowo „Gwoździe” bez uwzględniania wielkości liter. W zakresie B1:B2 umieścimy kolumnę z nowym kryterium wyboru, nie zapominając o wskazaniu nagłówka kolumny, w której będzie wykonywany screening. W komórce B2 musisz wprowadzić następującą formułę = „= Gwoździe”.

• wybierz dowolną z komórek tabeli,
• otwórz "Filtr zaawansowany",
• sprawdź, czy cała tabela z danymi znajduje się w „Zakresie początkowym”,
• w „Zakresie warunków” wskazać B1:B2.

Po kliknięciu „OK” dane zostaną odfiltrowane.

To są najprostsze przykłady. praca z filtrami w Excelu. W wersji rozszerzonej wygodnie jest ustawić inne warunki selekcji, np. dropout z parametrem „LUB”, dropout z parametrem „Gwoździe” oraz wartość w kolumnie „Kwota” >40.

Jak filtrować w Excelu według kolumn

Informacje w tabeli można filtrować według kolumn - jednej lub kilku. Rozważmy przykład tabeli z kolumnami „Miasto”, „Miesiąc” i „Sprzedaż”.

Jeśli chcesz filtrować dane według kolumny z nazwami miast w kolejności alfabetycznej, musisz zaznaczyć dowolną komórkę w tej kolumnie, otworzyć „Sortuj” i „Filtruj” i wybrać opcję „T”. Dzięki temu informacja zostanie wyświetlona z uwzględnieniem pierwszej litery nazwy miasta.

Aby uzyskać informacje o odwrotnej zasadzie, musisz użyć parametru „YA”.

Konieczne jest przesiewanie informacji według miesięcy, a także miasto o dużym wolumenie sprzedaży powinno znajdować się wyżej w tabeli niż miasto o mniejszym wolumenie sprzedaży. Aby rozwiązać problem, musisz wybrać opcję „Sortuj” w „Sortuj i filtruj”. W oknie, które pojawi się z ustawieniami, określ "Sortuj według" - "Miesiąc".

Następnie musisz dodać drugi poziom sortowania. Aby to zrobić, wybierz w "Sortuj" - "Dodaj poziom" i określ kolumnę "Sprzedaż". W kolumnie ustawień „Zamówienie” wybierz „Malejąco”. Po kliknięciu „OK” dane zostaną wybrane zgodnie z określonymi parametrami.

INSTRUKCJA WIDEO

Dlaczego filtry mogą nie działać w programie Excel

Podczas pracy z narzędziem takim jak filtry użytkownicy często napotykają trudności. Zwykle są one związane z naruszeniem zasad korzystania z niektórych ustawień.

Problem z filtrowaniem według daty jest jednym z najczęstszych. Występuje po załadowaniu danych z System księgowy jako tablica. Podczas próby filtrowania wierszy według kolumny zawierającej daty porzucenie następuje nie według daty, ale według tekstu.

Rozwiązaniem problemu:

1. wybierz kolumnę z datami,
2. otwórz zakładkę Excel w menu głównym,
3. wybierz przycisk "Komórki", z rozwijanej listy wybierz opcję "Konwertuj tekst na datę".

Typowe błędy użytkownika podczas pracy z tym programem powinny również obejmować:

• brak nagłówków kolumn (bez nich filtrowanie, sortowanie nie będzie działać, a także cała linia inne ważne parametry)
• obecność pustych wierszy i kolumn w tabeli z danymi (to myli system sortowania, Excel postrzega informacje jako dwie różne niezależne tabele),
• umieszczenie kilku tabel na jednej stronie (wygodniej jest umieścić każdą tabelę na osobnym arkuszu),
• umieszczenie w kilku kolumnach danych tego samego typu,
• umieszczenie danych na kilku arkuszach, na przykład według miesięcy lub lat (ilość pracy można od razu pomnożyć przez liczbę arkuszy z informacjami).

Kolejnym krytycznym błędem, który nie pozwala w pełni wykorzystać możliwości Excela, jest użycie nielicencjonowanego produktu.

Nie można zagwarantować, że będzie działać poprawnie, a błędy będą pojawiać się cały czas. Jeśli zamierzasz użyć to narzędzie przetwarzając na bieżąco informacje matematyczne, kup pełną wersję programu.

Zaawansowany filtr w programie Excel zapewnia więcej opcji zarządzania danymi arkusze kalkulacyjne. Jest bardziej złożony w ustawieniach, ale znacznie skuteczniejszy w działaniu.

Za pomocą standardowego filtra użytkownik Microsoft Excel może rozwiązać daleko od wszystkich zadań. Nie ma wizualnego wyświetlania zastosowanych warunków filtrowania. Nie można zastosować więcej niż dwóch kryteriów wyboru. Nie można filtrować pod kątem zduplikowanych wartości, aby pozostawić tylko unikalne wpisy. A same kryteria są schematyczne i proste. Zaawansowana funkcjonalność filtrowania jest znacznie bogatsza. Przyjrzyjmy się bliżej jego możliwościom.

Jak zrobić zaawansowany filtr w programie Excel?

Zaawansowany filtr umożliwia filtrowanie danych według nieograniczonego zestawu warunków. Za pomocą narzędzia użytkownik może:

1. ustawić więcej niż dwa kryteria wyboru;
2. skopiuj wynik filtrowania do innego arkusza;
3. ustawić warunek o dowolnej złożoności za pomocą formuł;
4. wyodrębnij unikalne wartości.

Algorytm zastosowania zaawansowanego filtra jest prosty:

1. Tworzymy tabelę z danymi początkowymi lub otwieramy już istniejącą. Na przykład tak:
2. Utwórz tabelę warunków. Osobliwości: linia nagłówka całkowicie pokrywa się z „nagłówkiem” przefiltrowanej tabeli. Aby uniknąć błędów, skopiuj wiersz nagłówka w tabeli źródłowej i wklej go na tym samym arkuszu (bocznym, górnym, dolnym) lub innym arkuszu. Wprowadź kryteria wyboru do tabeli warunków.
3. Przejdź do zakładki "Dane" - "Sortuj i filtruj" - "Zaawansowane". Jeśli przefiltrowane informacje powinny być wyświetlane w innym arkuszu (NIE tam, gdzie znajduje się oryginalna tabela), to musisz uruchomić filtr zaawansowany z innego arkusza.
4. W otwartym oknie „Filtr zaawansowany” wybierz metodę przetwarzania informacji (na tym samym arkuszu lub na innym), ustaw zakres początkowy (tabela 1, przykład) i zakres warunków (tabela 2, warunki). Linie nagłówka muszą być zawarte w zakresach.
5. Aby zamknąć okno Filtr zaawansowany, kliknij OK. Widzimy wynik.

Górna tabela jest wynikiem filtrowania. Obok znajduje się dolna tabliczka z warunkami dla przejrzystości.

Jak korzystać z zaawansowanego filtra w programie Excel?

Aby anulować akcję filtru zaawansowanego, umieść kursor w dowolnym miejscu w tabeli i naciśnij kombinację klawiszy Ctrl + Shift + L lub "Dane" - "Sortuj i filtruj" - "Wyczyść".

Korzystając z narzędzia „Filtr zaawansowany”, znajdźmy informacje o wartościach, które zawierają słowo „Ustaw”.

Dodajmy kryteria do tabeli warunków. Na przykład te:

Program w tym przypadku wyszuka wszystkie informacje o produktach, które mają w nazwie słowo „Zestaw”.

Możesz użyć znaku „=”, aby wyszukać dokładną wartość. Dodajmy następujące kryteria do tabeli warunków:

Program Excel przyjmuje znak „=” jako sygnał, że użytkownik wprowadza teraz formułę. Aby program działał poprawnie, pasek formuły musi zawierać wpis w postaci: = „= Zestaw komórek regionu 6”.

Po użyciu „Filtru zaawansowanego”:

Teraz przefiltrujmy tabelę źródłową według warunku „LUB” dla różnych kolumn. Operator „LUB” znajduje się również w narzędziu „Autofiltr”. Ale tam można go użyć w jednej kolumnie.

W tabeli warunków przedstawiamy kryteria wyboru: \u003d „= Zestaw komórek regionu 6”. (w kolumnie „Nazwa”) i =

Uwaga: kryteria należy wpisać pod odpowiednimi nagłówkami w RÓŻNYCH wierszach.

Wynik selekcji:

Zaawansowany filtr pozwala na użycie formuł jako kryterium. Rozważ przykład.

Wybór linii z maksymalnym zadłużeniem: =MAX(Tabela1).

W ten sposób otrzymujemy wyniki jak po uruchomieniu wielu filtrów na tym samym arkuszu Excela.

Jak zrobić wiele filtrów w programie Excel?

Utwórzmy filtr według kilku wartości. Aby to zrobić, wprowadzamy jednocześnie kilka kryteriów wyboru danych do tabeli warunków:

Zastosuj narzędzie Filtr zaawansowany:

Teraz z tabeli z wybranymi danymi wyodrębniamy Nowa informacja wybrane według innych kryteriów. Na przykład tylko przesyłki na rok 2014.

Wprowadzamy nowe kryterium w tabeli warunków i stosujemy narzędzie filtrujące. Początkowy zakres to tabela z danymi wybranymi według poprzedniego kryterium. W ten sposób filtrujesz w wielu kolumnach.

Aby użyć wielu filtrów, możesz utworzyć wiele tabel warunków w nowych arkuszach. Sposób realizacji zależy od zadania postawionego przez użytkownika.

Jak filtrować w programie Excel według wierszy?

Nie w standardowy sposób. Program Microsoft Excel wybiera tylko dane w kolumnach. Dlatego należy szukać innych rozwiązań.

Oto przykłady zaawansowanych kryteriów ciągu filtra w programie Excel:

1. Konwertuj tabelę. Na przykład z trzech wierszy utwórz listę trzech kolumn i zastosuj filtrowanie do przekonwertowanej wersji.
2. Użyj formuł, aby wyświetlić dokładnie te dane w wierszu, którego potrzebujesz. Na przykład zrób jakiś wskaźnik jako listę rozwijaną. A w następnej komórce wprowadź formułę za pomocą funkcji JEŻELI. Po wybraniu określonej wartości z listy rozwijanej jej parametr pojawia się obok niej.

Aby podać przykład działania filtrowania według wierszy w programie Excel, utwórzmy tabelę:

Dla listy produktów utwórz listę rozwijaną:

Nad tabelą z danymi źródłowymi wstaw pusty wiersz. W komórkach wprowadzimy formułę, która pokaże, z jakich kolumn pochodzą informacje.

Obok rozwijanej listy wpisz w komórce następującą formułę: Jej zadaniem jest wybranie z tabeli tych wartości, które odpowiadają danemu produktowi

Pobierz przykłady filtrów zaawansowanych

Tak więc za pomocą narzędzia „Lista rozwijana” i wbudowanego Funkcje Excela wybiera dane w wierszach według określonego kryterium.

Filtrowanie danych w Excelu pozwoli wyświetlić w kolumnach tabeli informacje, które w danym momencie interesują użytkownika. Znacznie upraszcza proces pracy z dużymi stołami. Będziesz mógł kontrolować zarówno dane, które będą wyświetlane w kolumnie, jak i dane wykluczone z listy.

Jeśli utworzyłeś tabelę w programie Excel za pomocą zakładki "Wstaw" - "Tabela" lub "Strona główna" - "Formatuj jako tabelę", to w takiej tabeli filtr jest domyślnie włączony. Jest wyświetlany w postaci strzałki, która znajduje się w górnej komórce po prawej stronie.

Jeśli właśnie wypełniłeś komórki danymi, a następnie sformatowałeś je jako tabelę, filtr musi być włączony. Aby to zrobić, zaznacz cały zakres komórek tabeli, w tym nagłówki kolumn, ponieważ przycisk filtrowania znajduje się w górnej kolumnie, a jeśli wybierzesz kolumnę zaczynając od komórki z danymi, nie będzie ona dotyczyć przefiltrowanych danych tej kolumna. Następnie przejdź do zakładki „Dane” i kliknij przycisk „Filtruj”.

W przykładzie strzałka filtrowania znajduje się w nagłówkach i słusznie - wszystkie dane w kolumnie poniżej zostaną przefiltrowane.

Jeśli interesuje Cię pytanie, jak zrobić tabelę w Excelu, kliknij link i przeczytaj artykuł na ten temat.

Przyjrzyjmy się teraz, jak działa filtr w programie Excel. Użyjmy poniższej tabeli jako przykładu. Ma trzy kolumny: „Nazwa produktu”, „Kategoria” i „Cena”, zastosujemy do nich różne filtry.

Kliknij strzałkę w górnej komórce żądanej kolumny. Tutaj zobaczysz listę nie powtarzających się danych ze wszystkich komórek znajdujących się w ta kolumna. Obok każdej wartości pojawią się pola wyboru. Odznacz pola obok wartości, które chcesz wykluczyć z listy.

Na przykład zostawmy tylko owoce w „Kategorii”. Usuń zaznaczenie w polu „warzywa” i kliknij „OK”.

W przypadku tych kolumn tabeli, do których zastosowano filtr, w górnej komórce pojawi się odpowiednia ikona.

Jeśli chcesz usunąć filtr danych w programie Excel, kliknij ikonę filtra w komórce i wybierz z menu „Usuń filtr z (nazwa kolumny)”.

Jak zrobić filtr danych w programie Excel różne sposoby. Istnieją filtry tekstowe i liczbowe. Stosuje się je odpowiednio, jeśli komórki kolumny zawierają tekst lub liczby.

Zastosujmy „Filtr liczbowy” do kolumny „Cena”. Kliknij przycisk w górnej komórce i wybierz odpowiednią pozycję z menu. Z listy rozwijanej możesz wybrać warunek, który chcesz zastosować do danych kolumny. Na przykład wyświetlmy wszystkie produkty, których cena jest niższa niż „25”. Wybieramy „mniej”.

W odpowiednim polu wpisz Pożądana wartość. Do filtrowania danych można zastosować wiele warunków za pomocą logicznego AND i OR. W przypadku używania „ORAZ”, oba warunki muszą być spełnione, podczas korzystania z „LUB”, jeden z określonych warunków musi być spełniony. Na przykład możesz ustawić: "mniej" - "25" - "I" - "większe niż" - "55". Tym samym wykluczymy z tabeli produkty, których cena mieści się w przedziale od 25 do 55.

Tabela z filtrem w kolumnie „Cena” poniżej 25.

„Filtr tekstu” w przykładowej tabeli można zastosować do kolumny „Nazwa produktu”. Kliknij przycisk filtra w kolumnie i wybierz z menu element o tej samej nazwie. Na liście rozwijanej, która się otworzy, użyj na przykład „zaczyna się od”.

Zostawmy w tabeli produkty, które zaczynają się od „ka”. W kolejnym oknie w polu piszemy: "ka*". Kliknij OK".

„*” w słowie zastępuje ciąg znaków. Na przykład, jeśli ustawisz warunek "zawiera" - "s * l", słowa stół, krzesło, sokół i tak dalej pozostaną. "?" zastąpi dowolny znak. Na przykład „b? ton” - bochenek, pączek. Jeśli chcesz zostawić słowa składające się z 5 liter, napisz „?????”.

Filtruj według kolumny Nazwa produktu.

Filtr można dostosować według koloru tekstu lub koloru komórki.

Utwórzmy komórkę „Filtruj według koloru” dla kolumny „Nazwa produktu”. Klikamy na przycisk filtra i wybieramy z menu element o tej samej nazwie. Wybierzmy czerwony.

W tabeli pozostały tylko czerwone produkty.

Filtr kolorów tekstu dotyczy kolumny Kategoria. Zostaw tylko owoce. Ponownie wybierz czerwony.

Teraz w przykładowej tabeli wyświetlane są tylko czerwone owoce.

Jeśli chcesz, aby wszystkie komórki tabeli były widoczne, ale najpierw jest czerwona komórka, potem zielona, ​​niebieska itd., użyj sortowania w programie Excel. Klikając w link, możesz przeczytać artykuł na ten temat.

Filtry w programie Excel pomogą Ci pracować z dużymi tabelami. Rozważyliśmy główne punkty, jak zrobić filtr i jak z nim pracować. Odebrać niezbędne warunki i pozostaw interesujące dane w tabeli.