Niedawno napisałem artykuł o tym, jak używać funkcji podsumowujących w programie Excel do łatwego podsumowywania dużych ilości danych, ale ten artykuł uwzględniał wszystkie dane w arkuszu. Co zrobić, jeśli chcesz spojrzeć tylko na podzbiór danych i podsumować podzbiór danych?
W Excelu możesz tworzyć filtry dla kolumn, które ukrywają wiersze, które nie pasują do twojego filtra. Ponadto możesz również użyć specjalnych funkcji w programie Excel do podsumowania danych przy użyciu tylko przefiltrowanych danych.
Spis treści
- Twórz proste filtry w Excelu
- Twórz zaawansowane filtry w Excelu
- Podsumowanie przefiltrowanych danych
W tym artykule przeprowadzę Cię przez kroki tworzenia filtrów w programie Excel, a także korzystania z wbudowanych funkcji w celu podsumowania przefiltrowanych danych.
Twórz proste filtry w Excelu
W Excelu możesz tworzyć filtry proste i filtry złożone. Zacznijmy od prostych filtrów. Pracując z filtrami, zawsze powinieneś mieć jeden wiersz na górze, który jest używany do etykiet. Posiadanie tego wiersza nie jest wymagane, ale nieco ułatwia pracę z filtrami.
Powyżej mam fałszywe dane i chcę utworzyć filtr w kolumnie Miasto . W Excelu jest to naprawdę łatwe do zrobienia. Śmiało i kliknij kartę Dane na wstążce, a następnie kliknij przycisk Filtr . Nie musisz też wybierać danych na arkuszu ani klikać w pierwszym wierszu.
Kiedy klikniesz Filtruj, każda kolumna w pierwszym wierszu zostanie automatycznie dodana do małego rozwijanego przycisku 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.
U góry możesz szybko posortować wszystkie wiersze według wartości w kolumnie Miasto. Pamiętaj, że podczas sortowania danych zostanie przeniesiony cały wiersz, a nie tylko wartości w kolumnie Miasto. Dzięki temu Twoje dane pozostaną nienaruszone, tak jak wcześniej.
Dobrym pomysłem jest również dodanie na samym początku kolumny o nazwie ID i ponumerowanie jej od jednego do dowolnej liczby wierszy w arkuszu. W ten sposób zawsze możesz sortować według kolumny identyfikatora i odzyskać dane w tej samej kolejności, w jakiej były 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 filtrów. W moim przykładzie mam tylko trzy unikalne wartości w kolumnie Miasto i te trzy pojawiają się na liście.
Poszedłem dalej i odznaczyłem dwa miasta i zostawiłem zaznaczone jedno. Teraz mam tylko 8 wierszy danych, a reszta jest ukryta. Możesz łatwo stwierdzić, że patrzysz na przefiltrowane dane, jeśli sprawdzisz numery wierszy po lewej stronie. W zależności od tego, ile wierszy jest ukrytych, zobaczysz kilka dodatkowych poziomych linii, a kolor liczb będzie niebieski.
Załóżmy teraz, że chcę filtrować według drugiej kolumny, 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ę rozwijania w kolumnie C, a zobaczysz te same pola wyboru dla każdej unikalnej wartości w kolumnie. Jednak w tym przypadku chcemy kliknąć Filtry liczb , a następnie kliknąć Większy niż . Jak widać, jest też kilka innych opcji.
Pojawi się nowe okno dialogowe, w którym możesz wpisać wartość filtra. Możesz także dodać więcej niż jedno kryterium za pomocą funkcji AND lub OR. Możesz na przykład powiedzieć, że chcesz wierszy, w których wartość jest większa niż 2 i różna od 5.
Teraz mam tylko 5 wierszy danych: rodziny tylko z Nowego Orleanu i z 3 lub więcej członkami. Wystarczająco łatwe? Pamiętaj, że możesz łatwo wyczyścić filtr w kolumnie, klikając menu rozwijane, a następnie klikając łącze Wyczyść filtr z „Nazwy kolumny” .
To tyle w przypadku prostych filtrów w Excelu. Są bardzo łatwe w użyciu, a wyniki są dość proste. Przyjrzyjmy się teraz złożonym filtrom za pomocą okna dialogowego Filtry zaawansowane .
Twórz zaawansowane filtry w Excelu
Jeśli chcesz utworzyć bardziej zaawansowane filtry, musisz użyć okna dialogowego Filtr zaawansowany . Załóżmy na przykład, że chcę zobaczyć wszystkie rodziny mieszkające w Nowym Orleanie, które mają więcej niż 2 członków rodziny LUB wszystkie rodziny w Clarksville, które mają więcej niż 3 członków rodziny ORAZ tylko te, których adres e-mail kończy się na końcówce .EDU . Teraz nie możesz tego zrobić za pomocą prostego filtra.
Aby to zrobić, musimy trochę inaczej skonfigurować arkusz Excela. Śmiało, wstaw kilka wierszy nad zestawem danych i skopiuj etykiety nagłówków dokładnie do pierwszego wiersza, jak pokazano poniżej.
Oto jak działają zaawansowane filtry. Najpierw musisz wpisać kryteria w kolumnach u góry, a następnie kliknąć przycisk Zaawansowane w obszarze Sortuj i filtruj na karcie Dane .
Co dokładnie możemy wpisać w te komórki? OK, więc zacznijmy od naszego przykładu. Chcemy zobaczyć tylko dane z Nowego Orleanu lub Clarksville, więc wpiszmy je w komórkach E2 i E3.
Gdy wpisujesz wartości w różnych wierszach, oznacza to LUB. Teraz chcemy rodzin z Nowego Orleanu z więcej niż dwoma członkami i rodzin z 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 rodzin z końcowym adresem e-mail .EDU. Aby to zrobić, po prostu wpisz *.edu w D2 i D3. Symbol * oznacza dowolną liczbę znaków.
Gdy to zrobisz, kliknij dowolne miejsce w zbiorze danych, a następnie kliknij przycisk Zaawansowane . Pole Zakres listy automatycznie określi Twój zestaw danych, ponieważ kliknąłeś go przed kliknięciem przycisku Zaawansowane. Teraz kliknij mały przycisk po prawej stronie przycisku zakresu kryteriów .
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 zostać przefiltrowane!
Jak widać, teraz mam tylko 3 wyniki, które spełniają wszystkie te kryteria. Pamiętaj, że etykiety zakresu kryteriów muszą dokładnie pasować do etykiet zestawu danych, aby to zadziałało.
Możesz oczywiście tworzyć dużo bardziej skomplikowane zapytania za pomocą tej metody, więc baw się nią, aby uzyskać pożądane wyniki. Na koniec porozmawiajmy o stosowaniu funkcji sumowania do filtrowanych danych.
Podsumowanie przefiltrowanych danych
Załóżmy teraz, że chcę podsumować liczbę członków rodziny w moich przefiltrowanych danych. Jak mam to zrobić? Cóż, wyczyśćmy nasz filtr, klikając przycisk Wyczyść na wstążce. Nie martw się, bardzo łatwo jest ponownie zastosować filtr zaawansowany, po prostu klikając przycisk Zaawansowane i ponownie klikając OK.
Na dole naszego zestawu danych dodajmy komórkę o nazwie Suma , a następnie dodajmy funkcję sumującą, aby podsumować wszystkich członków rodziny. W moim przykładzie właśnie wpisałem =SUM(C7:C31) .
Więc jeśli spojrzę na wszystkie rodziny, mam łącznie 78 członków. Teraz przejdźmy dalej i ponownie zastosujmy nasz filtr zaawansowany i zobaczmy, co się stanie.
Ups! Zamiast pokazywać prawidłową liczbę, 11, nadal widzę, że suma wynosi 78! Dlaczego? Cóż, funkcja SUMA 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 zignorowania ukrytych wierszy.
Pierwsza to SUMA CZĘŚCIOWA . Zanim użyjemy którejkolwiek z tych funkcji specjalnych, będziesz chciał wyczyścić filtr, a następnie wpisać funkcję.
Po wyczyszczeniu filtra wpisz =SUMA.CZĘŚCIOWE(, a powinno pojawić się menu rozwijane z kilkoma opcjami. Korzystając z tej funkcji, najpierw wybierasz typ funkcji sumowania, której chcesz użyć, używając liczby.
W naszym przykładzie chcę użyć SUM , więc wpisałbym liczbę 9 lub po prostu kliknął ją z listy rozwijanej. Następnie wpisz przecinek i zaznacz zakres komórek.
Po naciśnięciu enter powinieneś zobaczyć, że wartość 78 jest taka sama jak poprzednio. Jeśli jednak ponownie zastosujesz filtr, zobaczymy 11!
Doskonały! Właśnie tego chcemy. Teraz możesz dostosować swoje filtry, a wartość zawsze będzie odzwierciedlać tylko aktualnie wyświetlane wiersze.
Drugą funkcją, która działa prawie dokładnie tak samo jak funkcja SUBTOTAL, jest AGGREGATE . Jedyna różnica polega na tym, że w funkcji AGGREGATE jest inny parametr, w którym musisz określić, że chcesz ignorować ukryte wiersze.
Pierwszym parametrem jest funkcja sumująca, której chcesz użyć, i podobnie jak w przypadku SUBTOTAL, 9 reprezentuje funkcję SUMA. Druga opcja to miejsce, w którym musisz wpisać 5, aby zignorować ukryte wiersze. Ostatni parametr jest taki sam i jest zakresem komórek.
Możesz również przeczytać mój artykuł na temat funkcji podsumowujących, aby dowiedzieć się, jak bardziej szczegółowo korzystać z funkcji AGGREGUJ i innych funkcji, takich jak TRYB, MEDIANA, ŚREDNIA 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, napisz komentarz. Cieszyć się!