Ostatnio napisałem artykuł na temat jak korzystać z funkcji podsumowania w programie Excel, aby z łatwością podsumować duże ilości danych, ale w tym artykule uwzględniono wszystkie dane z arkusza roboczego. Co jeśli chcesz spojrzeć na podzbiór danych i podsumować podzbiór danych?
W programie Excel możesz tworzyć filtry w kolumnach, które będą ukrywać wiersze, które nie są zgodne z filtrem. Ponadto można użyć specjalnych funkcji w programie Excel do podsumowania danych przy użyciu tylko filtrowanych danych.
W tym artykule omówię czynności tworzenia filtrów w programie Excel i korzystania z wbudowanych funkcji funkcje podsumowujące filtrowane dane.
Twórz proste filtry w Excelu
W Excelu możesz tworzyć proste filtry i złożone filtry. Zacznijmy od prostych filtrów. Podczas pracy z filtrami zawsze powinieneś mieć jeden wiersz u góry, który jest używany do etykiet. Nie ma wymogu posiadania tego wiersza, ale ułatwia to pracę z filtrami.
Powyżej mam fałszywe dane, a ja chcesz utworzyć filtr w kolumnie Miasto. W Excelu jest to naprawdę łatwe. Śmiało i kliknij kartę Danena wstążce, a następnie kliknij przycisk Filtruj. Nie musisz wybierać danych na arkuszu lub klikać w pierwszym wierszu.
Po kliknięciu przycisku Filtruj, każda kolumna w pierwszym wierszu automatycznie zostanie dodany mały przycisk rozwijania po prawej stronie.
Teraz kliknij strzałkę menu rozwijanego Kolumna miasta. Zobaczysz kilka różnych opcji, które wyjaśnię poniżej.
U góry możesz szybko sortować wszystkie wiersze według wartości w kolumnie Miasto. Zwróć uwagę, że podczas sortowania danych przeniesie cały wiersz, a nie tylko wartości w kolumnie Miasto. Zapewni to, że twoje dane pozostaną nienaruszone dokładnie tak, jak było wcześniej.
Warto również dodać kolumnę na samym początku, która nazywa się ID i numerować ją od jednego do wielu wierszy, które masz w swoim arkusz roboczy. W ten sposób zawsze możesz sortować według kolumny ID i odzyskać dane w takiej samej kolejności, w jakiej pierwotnie je wydano.
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 u dołu okna dialogowego filtra. W moim przykładzie mam tylko trzy niepowtarzalne wartości w kolumnie Miasto, a te trzy pokazują się na liście.
Poszedłem dalej i odznaczono dwa miasta i jeden lewy sprawdzony. Teraz mam tylko 8 wierszy danych, a pozostałe są ukryte. Możesz łatwo powiedzieć, że patrzysz na przefiltrowane dane, jeśli sprawdzasz numery rzędów po lewej stronie. W zależności od tego, ile wierszy jest ukrytych, zobaczysz kilka dodatkowych poziomych linii, a kolor liczb będzie niebieski.
Teraz 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.
Dalej kliknij strzałkę w kolumnie C, a zobaczysz te same pola wyboru dla każdej unikatowej wartości w kolumnie. Jednak w tym przypadku chcemy kliknąć Filtry liczbowe, a następnie kliknąć Większe niż. Jak widzisz, jest jeszcze kilka innych opcji.
Pojawi się nowe okno dialogowe i tutaj możesz wpisać wartość dla filtra. Możesz również dodać więcej niż jedno kryterium za pomocą funkcji AND lub OR. Możesz powiedzieć, że potrzebujesz wierszy, których wartość jest większa niż 2, a nie równa się 5.
Teraz jestem 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 menu, a następnie klikając link Wyczyść filtr od "nazwa kolumny".
A więc chodzi o proste filtry w Excelu. Są bardzo łatwe w użyciu, a wyniki są dość proste. Teraz spójrzmy na złożone filtry za pomocą okna dialogowego Zaawansowane.
Utwórz zaawansowane filtry w Excelu
Jeśli chcesz tworzyć bardziej zaawansowane filtry, musisz użyć okna dialogowego Zaawansowane. Na przykład, powiedzmy, że chciałem zobaczyć wszystkie rodziny mieszkające w Nowym Orleanie z więcej niż 2 członkami w rodzinie LUBwszystkie rodziny w Clarksville z więcej niż 3 członkami w rodzinie ORAZtylko te z .EDUkońcowym adresem e-mail. Teraz nie możesz tego zrobić za pomocą prostego filtru.
Aby to zrobić, musimy nieco zmienić arkusz Excela. Dalej wstaw kilka wierszy nad zestawem danych i dokładnie skopiuj etykiety nagłówków do pierwszego wiersza, jak pokazano poniżej.
Teraz Oto jak działają filtry zaawansowane. Najpierw należy wpisać kryteria w kolumny u góry, a następnie kliknąć przycisk Zaawansowanew obszarze Sortuj i amp; Filtrujna karcie Dane.
Co dokładnie możemy wpisać w tych komórkach? OK, zacznijmy od naszego przykładu. Chcemy tylko zobaczyć dane z Nowego Orleanu lub Clarksville, więc wpiszmy je w komórkach E2 i E3.
Gdy wpisujesz wartości w różnych wiersze, to znaczy OR. Teraz chcemy rodziny w Nowym Orleanie z więcej niż dwoma członkami i rodziny Clarksville z więcej niż 3 członkami. Aby to zrobić, wpisz & gt; 2w C2 i >3w C3.
Ponieważ>2 i Nowy Orlean znajdują się w tym samym wierszu, będzie to operator AND. To samo dotyczy wiersza 3 powyżej. Wreszcie, chcemy tylko rodziny z końcowym adresem e-mail .EDU. Aby to zrobić, po prostu wpisz *. Eduw D2 i D3. Symbol * oznacza dowolną liczbę znaków.
Po wykonaniu tej czynności kliknij dowolne miejsce zestawu danych, a następnie kliknij przycisk Zaawansowane Przycisk. Pole Lista dzwonkówe automatycznie wykryje zestaw danych od momentu kliknięcia w nim przed kliknięciem przycisku Zaawansowane. Teraz kliknij mały mały przycisk po prawej stronie przycisku Kryteria.
Wybierz wszystko od A1 do E3, a następnie kliknij ponownie ten sam przycisk, aby powrócić do okna dialogowego Filtr zaawansowany. Kliknij OK, a twoje dane powinny być teraz filtrowane!
Jak widać, teraz mam tylko 3 wyniki, które pasują do wszystkich tych kryteriów. Zauważ, że etykiety dla zakresu kryteriów muszą pasować dokładnie do etykiet dla zestawu danych, aby to działało.
Oczywiście możesz tworzyć o wiele bardziej skomplikowane zapytania za pomocą tej metody, więc baw się z nim aby uzyskać pożądane wyniki. Na koniec porozmawiajmy o zastosowaniu funkcji sumowania do filtrowanych danych.
Podsumowywanie filtrowanych danych
Powiedzmy teraz, że chcę podsumować liczbę członków rodziny w moich przefiltrowanych danych, w jaki sposób miałeś to robić? Cóż, wyczyść nasz filtr, klikając przycisk Wyczyśćna wstążce. Nie martw się, bardzo łatwo jest ponownie zastosować zaawansowany filtr, klikając przycisk Zaawansowane i ponownie klikając OK.
na dole naszego zestawu danych, dodajmy komórkę o nazwie Total, a następnie dodaj funkcję sumującą, aby podsumować całkowitą liczbę członków rodziny. W moim przykładzie po prostu wpisałem = SUMA (C7: C31).
Więc jeśli spojrzę na wszystkie rodzin, mam łącznie 78 członków. Teraz zastosuj filtr zaawansowany i zobacz, co się stanie.
Ups! Zamiast pokazywać prawidłową liczbę, 11, nadal widzę, że całkowita jest 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óre można zignorować ukryte wiersze.
Pierwszym z nich jest SUBTOTAL. Zanim użyjemy któregoś z tych specjalnych funkcji, należy wyczyścić filtr, a następnie wpisać funkcję.
Po wyczyszczeniu filtru, napisz = SUBTOTAL (i powinieneś zobaczyć rozwijane okno z kilkoma opcjami.Za pomocą tej funkcji najpierw wybierz typ funkcji sumowania, której chcesz użyć, używając numeru.
W naszym przykładzie chcę użyć SUMA, więc wpiszę numer 9 lub po prostu kliknij na nim z listy rozwijanej, a następnie wpisz przecinek i wybierz zakres komórek.
Kiedy naciśniesz enter, powinieneś zobaczyć wartość 78 jest taka sama jak poprzednio, jednak jeśli ponownie zastosujesz filtr, zobaczymy 11!
Doskonale! Dokładnie tego chcemy. Teraz możesz dostosować filtry, a wartość zawsze będzie odzwierciedlać tylko te wiersze, które są aktualnie wyświetlane.
druga funkcja, która działa dokładnie tak samo, jak funkcja SUBTOTAL, to AGREGATOWANIE . Różnica polega na tym, że istnieje inny parametr w funkcji AGGREGATE, w której musisz określić, że chcesz ignorować ukryte wiersze.
Pierwszy parametr jest funkcją sumowania, z której chcesz korzystać i tak jak w przypadku SUBTOTAL, 9 oznacza 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 także przeczytać mój artykuł na temat funkcji podsumowania, aby dowiedzieć się, jak użyj funkcji AGGREGATE i inne funkcje, takie jak MODE, MEDIAN, AVERAGE, itd. bardziej szczegółowo.
Mam nadzieję, że Artykuł stanowi dobry punkt wyjścia do tworzenia i używania filtrów w programie Excel. Jeśli masz jakieś pytania, napisz komentarz. Ciesz się!