Jak filtrować dane w programie Excel

Niedawno napisałem artykuł o tym, jak używać funkcji podsumowania w Excelu, aby łatwo podsumowywać duże ilości danych, ale ten artykuł uwzględniał wszystkie dane w arkuszu. Co jeśli chcesz tylko spojrzeć na podzbiór danych i podsumować podzbiór danych?

W programie Excel można tworzyć filtry na kolumnach, które będą ukrywać wiersze, które nie pasują do Twojego filtra. Ponadto można użyć specjalnych funkcji w programie Excel do podsumowania danych przy użyciu tylko filtrowanych danych.

W tym artykule przeprowadzę Cię przez kolejne kroki tworzenia filtrów w Excelu, a także za pomocą wbudowanych funkcji podsumowujących filtrowane dane.

Utwórz proste filtry w programie Excel

W programie Excel można tworzyć proste filtry i złożone filtry. Zacznijmy od prostych filtrów. Podczas pracy z filtrami zawsze powinieneś mieć jeden wiersz na górze, który jest używany do etykiet. Nie ma wymogu posiadania tego rzędu, ale ułatwia pracę z filtrami.

Powyżej mam pewne fałszywe dane i chcę utworzyć filtr na Miasto kolumna. W Excelu jest to naprawdę łatwe. Śmiało i kliknij Dane kartę na wstążce, a następnie kliknij Filtr przycisk. Nie musisz wybierać danych na arkuszu ani klikać w pierwszym wierszu.

Po kliknięciu Filtruj każda kolumna w pierwszym wierszu automatycznie doda mały przycisk rozwijany po prawej stronie.

Teraz idź dalej i kliknij strzałkę rozwijaną w kolumnie Miasto. Zobaczysz kilka różnych opcji, które wyjaśnię poniżej.

Na górze możesz szybko posortować wszystkie wiersze według wartości w kolumnie Miasto. Zauważ, że podczas sortowania danych przesunie cały wiersz, a nie tylko wartości w kolumnie Miasto. Zapewni to, że Twoje dane pozostaną nienaruszone tak jak wcześniej.

Warto też dodać kolumnę z przodu o nazwie ID i numerować ją od jednego do wielu wierszy w arkuszu. W ten sposób zawsze możesz sortować według kolumny ID i odzyskiwać dane w tej samej kolejności, w jakiej była pierwotnie, jeśli jest to dla Ciebie ważne.

Jak widać, wszystkie dane w arkuszu kalkulacyjnym są teraz sortowane na podstawie wartości w kolumnie Miasto. Jak dotąd żadne wiersze nie są ukryte. Teraz spójrzmy na pola wyboru na dole okna dialogowego filtra. W moim przykładzie mam tylko trzy unikalne wartości w kolumnie Miasto, a te trzy pojawiają się na liście.

Poszedłem do przodu i odznaczyłem dwa miasta i pozostawiłem jedno zaznaczone. Teraz mam tylko 8 wierszy pokazujących dane, a reszta jest ukryta. Możesz łatwo powiedzieć, że przeglądasz przefiltrowane dane, jeśli zaznaczysz numery wierszy z lewej strony. W zależności od tego, ile rzędów jest ukrytych, zobaczysz kilka dodatkowych linii poziomych, a kolor liczb będzie niebieski.

Powiedzmy, że chcę filtrować w drugiej kolumnie, aby jeszcze bardziej zmniejszyć liczbę wyników. W kolumnie C mam całkowitą liczbę członków w każdej rodzinie i chcę zobaczyć wyniki tylko dla rodzin z więcej niż dwoma członkami.

Śmiało i kliknij strzałkę rozwijaną w kolumnie C, a zobaczysz te same pola wyboru dla każdej unikalnej wartości w kolumnie. Jednak w tym przypadku chcemy kliknąć Filtry liczbowe a następnie kliknij Lepszy niż. Jak widać, jest też wiele innych opcji.

Pojawi się nowe okno dialogowe i tutaj możesz wpisać wartość filtru. Możesz także dodać więcej niż jedno kryterium za pomocą funkcji AND lub OR. Można powiedzieć, że chcesz wiersze, w których wartość jest większa niż 2, a nie równa 5, na przykład.

Teraz mam tylko 5 wierszy danych: rodziny tylko z Nowego Orleanu i 3 lub więcej członków. Wystarczająco łatwe? Pamiętaj, że możesz łatwo wyczyścić filtr w kolumnie, klikając listę rozwijaną, a następnie klikając Wyczyść filtr z „Nazwa kolumny” połączyć.

To wszystko na temat prostych filtrów w programie Excel. Są bardzo łatwe w użyciu, a wyniki są całkiem proste. Przyjrzyjmy się teraz złożonym filtrom za pomocą zaawansowane okno dialogowe filtrów.

Utwórz zaawansowane filtry w programie Excel

Jeśli chcesz tworzyć bardziej zaawansowane filtry, musisz użyć zaawansowane okno dialogowe filtrowania. Załóżmy na przykład, że chciałem zobaczyć wszystkie rodziny mieszkające w Nowym Orleanie z więcej niż 2 członkami w rodzinie LUB wszystkie rodziny w Clarksville z więcej niż 3 członkami w rodzinie I tylko te z .EDU kończący się adres e-mail. Teraz nie możesz tego zrobić za pomocą prostego filtra.

Aby to zrobić, musimy nieco inaczej skonfigurować arkusz Excela. Idź dalej i wstaw kilka wierszy nad zestawem danych i skopiuj etykiety nagłówków dokładnie w pierwszym wierszu, jak pokazano poniżej.

Oto jak działają zaawansowane filtry. Najpierw musisz wpisać kryteria w kolumny u góry, a następnie kliknąć zaawansowane przycisk pod Sortuj i filtruj na Dane patka.

Więc co dokładnie możemy wpisać do tych komórek? OK, zacznijmy od naszego przykładu. Chcemy tylko zobaczyć dane z Nowego Orleanu lub Clarksville, więc wpiszmy je w komórki E2 i E3.

Kiedy wpisujesz wartości w różnych wierszach, oznacza to OR. Teraz chcemy rodziny w Nowym Orleanie z więcej niż dwoma członkami i rodzinami Clarksville z więcej niż 3 członkami. Aby to zrobić, wpisz > 2 w C2 i > 3 w C3.

Ponieważ> 2 i Nowy Orlean znajdują się w tym samym wierszu, będzie to operator AND. To samo dotyczy wiersza 3 powyżej. Na koniec chcemy tylko rodziny z końcowym adresem e-mail .EDU. Aby to zrobić, po prostu wpisz * .edu do D2 i D3. Symbol * oznacza dowolną liczbę znaków.

Gdy to zrobisz, kliknij gdziekolwiek w swoim zbiorze danych, a następnie kliknij zaawansowane przycisk. The Lista RangPole automatycznie obliczy twój zestaw danych, ponieważ kliknąłeś w niego, zanim klikniesz przycisk Zaawansowane. Teraz kliknij mały mały przycisk po prawej stronie Zakres kryteriów przycisk.

Wybierz wszystko od A1 do E3, a następnie ponownie kliknij ten sam przycisk, aby wrócić do okna dialogowego Filtr zaawansowany. Kliknij OK, a Twoje dane powinny być teraz filtrowane!

Jak widać, teraz mam tylko 3 wyniki, które spełniają wszystkie te kryteria. Należy zauważyć, że etykiety dla zakresu kryteriów muszą dokładnie pasować do etykiet zestawu danych, aby działało.

Za pomocą tej metody możesz oczywiście tworzyć dużo bardziej skomplikowane zapytania, więc baw się nią, aby uzyskać pożądane wyniki. Na koniec porozmawiajmy o zastosowaniu funkcji sumowania do filtrowanych danych.

Podsumowanie filtrowanych danych

Powiedzmy, że chcę podsumować liczbę członków rodziny na moich przefiltrowanych danych, jak bym to zrobił? Cóż, wyczyśćmy nasz filtr, klikając Jasny przycisk na wstążce. Nie martw się, bardzo łatwo ponownie zastosować zaawansowany filtr, klikając przycisk Zaawansowane i ponownie klikając OK.

Na dole naszego zestawu danych dodajmy komórkę o nazwie Całkowity a następnie dodaj funkcję sumy, aby zsumować wszystkich członków rodziny. W moim przykładzie właśnie wpisałem = SUMA (C7: C31).

Więc jeśli spojrzę na wszystkie rodziny, mam łącznie 78 członków. Teraz przejdźmy dalej i zastosuj nasz zaawansowany filtr i zobacz, co się stanie.

Ups! Zamiast pokazywać poprawną liczbę 11, wciąż widzę, że suma wynosi 78! Dlaczego? Cóż, funkcja SUM nie ignoruje ukrytych wierszy, więc nadal wykonuje obliczenia przy użyciu wszystkich wierszy. Na szczęście istnieje kilka funkcji, których można użyć do ignorowania ukrytych wierszy.

Pierwszy to PODSUMOWANIE. Zanim użyjemy którejkolwiek z tych specjalnych funkcji, będziesz chciał wyczyścić filtr, a następnie wpisać funkcję.

Gdy filtr zostanie wyczyszczony, idź dalej i wpisz = SUBTOTAL ( i powinieneś zobaczyć listę rozwijaną z wieloma opcjami. Korzystając z tej funkcji, najpierw wybierasz rodzaj funkcji sumowania, której chcesz użyć za pomocą numeru.

W naszym przykładzie chcę użyć SUMA, więc wpisałbym numer 9 lub po prostu kliknął na niego z listy rozwijanej. Następnie wpisz przecinek i wybierz zakres komórek.

Kiedy naciśniesz enter, powinieneś zobaczyć wartość 78 jest taka sama jak poprzednio. Jeśli jednak ponownie zastosujesz filtr, zobaczymy 11!

Doskonały! To jest dokładnie to, czego chcemy. Teraz możesz dostosować filtry, a wartość zawsze będzie odzwierciedlać tylko wiersze, które są aktualnie wyświetlane.

Druga funkcja, która działa prawie dokładnie tak samo jak funkcja SUBTOTAL AGREGAT. Jedyną różnicą jest to, że w funkcji AGGREGATE istnieje inny parametr, w którym należy określić, że chcesz ignorować ukryte wiersze.

Pierwszym parametrem jest funkcja sumowania, której chcesz użyć, a podobnie jak w przypadku funkcji SUBTOTAL, 9 reprezentuje funkcję SUM. Druga opcja polega na wpisaniu 5, aby zignorować ukryte wiersze. Ostatni parametr jest taki sam i jest zakresem komórek.

Możesz także przeczytać mój artykuł na temat funkcji podsumowania, aby dowiedzieć się, jak bardziej szczegółowo korzystać z funkcji AGGREGATE i innych funkcji, takich jak MODE, MEDIAN, AVERAGE itp..

Mamy nadzieję, że ten artykuł stanowi dobry punkt wyjścia do tworzenia i używania filtrów w programie Excel. Jeśli masz jakieś pytania, możesz napisać komentarz. Cieszyć się!