Niedawno napisałem artykuł o tym, jak korzystać z funkcji podsumowania w Excelu, 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żna 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 związane z tworzeniem filtrów w programie Excel i wykorzystaniem wbudowanych funkcji do podsumowania filtrowanych danych.
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 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 i chcę utworzyć filtr na Miasto kolumna. W Excelu jest to naprawdę łatwe. Śmiało i kliknij przycisk Dane kartę na wstążce, a następnie kliknij przycisk Filtr przycisk. Nie musisz wybierać danych na arkuszu lub kliknąć w pierwszym wierszu.
Po kliknięciu przycisku Filtruj każda kolumna w pierwszym wierszu automatycznie wyświetli mały przycisk rozwijania z prawej strony.
Teraz idź i kliknij strzałkę 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. 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ę z przodu, która nazywa się ID i numerować ją od jednego do wielu wierszy w arkuszu roboczym. 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, 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 u dołu okna dialogowego filtra. W moim przykładzie mam tylko trzy niepowtarzalne wartości w kolumnie Miasto, a te trzy pojawiają się na liście.
Poszedłem naprzód, odznaczając dwa miasta i zostawiłem jeden 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 załóżmy, że chcę filtrować na 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ę 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ć, istnieje również szereg innych opcji.
Pojawi się nowe okno dialogowe i tutaj możesz wpisać wartość filtra. Możesz również dodać więcej niż jedno kryterium za pomocą funkcji AND lub OR. Można powiedzieć, że chcesz mieć wiersze, których wartość jest większa niż 2, a nie równa się 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 menu, a następnie klikając Wyczyść filtr od "Nazwa kolumny" połączyć.
A więc chodzi o proste filtry w Excelu. Są bardzo łatwe w użyciu, a wyniki są dość proste. Teraz przyjrzyjmy się złożonym filtrom przy użyciu zaawansowane okno dialogowe filtrów.
Jeśli chcesz tworzyć bardziej zaawansowane filtry, musisz użyć zaawansowane okno dialogowe filtra. Na przykład, powiedzmy, że chcę zobaczyć wszystkie rodziny mieszkające w Nowym Orleanie z więcej niż 2 członkami w ich rodzinie LUB wszystkie rodziny w Clarksville z więcej niż 3 członkami w rodzinie I tylko te z .EDU kończący adres e-mail. Teraz nie możesz tego zrobić za pomocą prostego filtra.
Aby to zrobić, musimy nieco zmienić arkusz Excela. Śmiało i wstaw kilka rzędów nad zestawem danych i dokładnie skopiuj etykiety nagłówków do pierwszego rzędu, jak pokazano poniżej.
Oto, jak działają filtry zaawansowane. Najpierw wpisz swoje kryteria w kolumny u góry, a następnie kliknij przycisk zaawansowane przycisk pod Sortuj i filtruj na Dane patka.
Co dokładnie możemy wpisać w te komórki? 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.
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 rodziny Clarksville z więcej niż 3 członkami. Aby to zrobić, wpisz >2 w C2 i >3 w C3.
Ponieważ> 2 i Nowy Orlean są w tym samym rzędzie, 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 * .edu do obu 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. The Lista RangPole e automatycznie wykryje zestaw danych od momentu kliknięcia w niego przed kliknięciem przycisku Zaawansowane.Teraz kliknij mały mały przycisk po prawej stronie Zakres kryteriów przycisk.
Wybierz wszystko od A1 do E3, a następnie kliknij ponownie ten sam przycisk, aby powrócić do okna dialogowego Zaawansowany filtr. Kliknij OK, a twoje dane powinny być teraz filtrowane!
Jak widać, teraz mam tylko 3 wyniki, które pasują do wszystkich tych kryteriów. Należy zauważyć, że etykiety dla zakresu kryteriów muszą dokładnie odpowiadać etykietom zestawu danych, aby to działało.
Możesz oczywiście tworzyć znacznie bardziej skomplikowane zapytania za pomocą tej metody, więc baw się z nią, aby uzyskać pożądane wyniki. Na koniec porozmawiajmy o zastosowaniu funkcji sumowania do filtrowanych danych.
Teraz powiedzmy, że chcę podsumować liczbę członków rodziny na moich filtrowanych danych, w jaki sposób mam to zrobić? Cóż, wyczyść nasz filtr, klikając przycisk Jasny przycisk na wstążce. Nie martw się, bardzo łatwo jest ponownie zastosować zaawansowany filtr, klikając przycisk Zaawansowane i ponownie klikając OK.
U dołu naszego zestawu danych dodajmy komórkę o nazwie Całkowity a następnie dodaj funkcję sumującą, aby podsumować całkowitą liczbę członków rodziny. W moim przykładzie właśnie wpisałem = SUMA (C7: C31).
Więc jeśli patrzę na wszystkie rodziny, mam łącznie 78 członków. Teraz zastosuj nasz filtr zaawansowany i zobacz, co się stanie.
Whoops! 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, za pomocą których można zignorować ukryte wiersze.
Pierwszy jest SUBTOTAL. Zanim skorzystamy z którejś z tych funkcji specjalnych, należy wyczyścić filtr, a następnie wpisać funkcję.
Po wyczyszczeniu filtru, napisz i wpisz = SUBTOTAL ( powinieneś zobaczyć rozwijane okno z kilkoma opcjami. Korzystając z tej funkcji, najpierw wybierasz typ funkcji sumowania, której chcesz użyć, używając numeru.
W naszym przykładzie chcę użyć SUMA, więc wpisałem numer 9 lub po prostu kliknąłem na niego z rozwijanego menu. Następnie wpisz przecinek i wybierz zakres komórek.
Kiedy naciśniesz enter, powinieneś zobaczyć, że wartość 78 jest taka sama jak poprzednio. Jeśli jednak ponownie zastosujesz filtr, zobaczymy 11!
Doskonały! 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 działa dokładnie tak samo, jak funkcja SUBTOTAL AGREGAT. Jedyną różnicą jest to, że istnieje inny parametr w funkcji AGGREGATE, w której musisz określić, że chcesz ignorować ukryte wiersze.
Pierwszym parametrem jest funkcja sumowania, której chcesz użyć 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 bardziej szczegółowo wykorzystać funkcję AGREGOWANIA i inne funkcje, takie jak MODE, MEDIAN, AVERAGE.
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ę!
