Filtry w tabelach przestawnych nie są podobne do filtrów w tabelach lub danych, których używamy, w filtrach tabel przestawnych mamy dwie metody użycia filtrów, jedna polega na kliknięciu prawym przyciskiem myszy tabeli przestawnej i znajdziemy opcję filtra dla filtra tabeli przestawnej , inną metodą jest użycie opcji filtru dostępnych w polach tabeli przestawnej.
Jak filtrować w tabeli przestawnej?
Tabela przestawna jest przyjaznym dla użytkownika narzędziem arkusza kalkulacyjnego w programie Excel, które pozwala nam podsumowywać, grupować, wykonywać operacje matematyczne, takie jak SUMA, ŚREDNIA, LICZBA, itp. Na podstawie uporządkowanych danych przechowywanych w bazie danych. Oprócz operacji matematycznych, tabela przestawna ma jedną z najlepszych funkcji, czyli filtrowanie, które pozwala na wyodrębnienie zdefiniowanych wyników z naszych danych.
Przyjrzyjmy się wielu sposobom korzystania z filtru w tabeli przestawnej programu Excel: -
# 1 - Wbudowany filtr w tabeli przestawnej programu Excel
- Miejmy dane w jednym z arkuszy.
Powyższe dane składają się z 4 różnych kolumn z S.No, Flat no's, Carpet Area i SBA.
- Przejdź do zakładki wstawiania i wybierz tabelę przestawną, jak pokazano poniżej.
- Po kliknięciu tabeli przestawnej pojawia się okno „Utwórz tabelę przestawną”.
W tym oknie mamy możliwość wybrania tabeli lub zakresu do utworzenia tabeli przestawnej, lub też możemy skorzystać z zewnętrznego źródła danych.
Mamy również możliwość umieszczenia raportu tabeli przestawnej, czy to w tym samym arkuszu, czy w nowym arkuszu, co widać na powyższym obrazku.
- Pole tabeli przestawnej będzie dostępne na prawym końcu arkusza, jak poniżej.
- Możemy obserwować pole filtru, gdzie możemy przeciągnąć pola do filtrów, aby utworzyć filtr tabeli przestawnej. Przeciągnijmy pole Płaskie nie do Filtry i widzimy, że zostałby utworzony filtr dla Płaskich nie.
- Na tej podstawie możemy filtrować płaskie nie zgodnie z naszymi wymaganiami i jest to normalny sposób tworzenia filtru w tabeli przestawnej.
# 2 - Utwórz filtr do obszaru wartości tabeli przestawnej programu Excel
Ogólnie rzecz biorąc, kiedy bierzemy dane do obszarów wartości, nie będzie utworzony żaden filtr dla tych pól tabeli przestawnej. Widzimy to poniżej.
Możemy wyraźnie zauważyć, że nie ma opcji filtrowania dla obszarów wartości, tj. Sumy SBA i sumy powierzchni użytkowej. Ale faktycznie możemy go stworzyć, co pomaga nam w podejmowaniu różnych decyzji.
- Najpierw musimy zaznaczyć dowolną komórkę obok tabeli i kliknąć filtr w zakładce danych.
- Widzimy, że filtr dostaje się w obszarach wartości.
Ponieważ mamy filtry, możemy teraz wykonywać różne typy operacji z obszarów wartości, na przykład sortować je od największych do najmniejszych, aby poznać najlepszą sprzedaż / obszar / cokolwiek. Podobnie możemy sortować od najmniejszych do największych, sortować według kolorów, a nawet możemy wykonywać filtry liczbowe, takie jak <=, =,> i wiele innych. Odgrywa to ważną rolę w podejmowaniu decyzji w każdej organizacji.
# 3 - Wyświetl listę wielu elementów w filtrze tabeli przestawnej.
W powyższym przykładzie nauczyliśmy się tworzyć filtr w tabeli przestawnej. Przyjrzyjmy się teraz różnym sposobom wyświetlania listy.
3 najważniejsze sposoby wyświetlania listy wielu elementów w filtrze tabeli przestawnej to: -
- Korzystanie z fragmentatorów.
- Tworzenie listy komórek z kryteriami filtrowania.
- Lista wartości oddzielonych przecinkami.
Korzystanie z fragmentatorów
- Przygotujmy prostą tabelę przestawną z różnymi kolumnami, takimi jak Region, Miesiąc, Nr jednostki, Funkcja, Branża, Kategoria wiekowa.
- Najpierw utwórz tabelę przestawną, korzystając z podanych powyżej danych. Wybierz dane, a następnie przejdź do zakładki wstawiania i wybierz opcję tabeli przestawnej i utwórz tabelę przestawną.
- Na podstawie tego przykładu rozważymy funkcję w naszym filtrze i sprawdźmy, jak można ją wyświetlić za pomocą fragmentatorów i zmieniać zgodnie z naszym wyborem. Jest to proste, ponieważ po prostu zaznaczamy dowolną komórkę w tabeli przestawnej, a następnie przejdziemy do karty analizy na wstążce i wybierzemy fragmentator wstawiania.
- Następnie zamierzamy wstawić slicer z pola do naszego obszaru filtru, więc w tym przypadku „Funkcja” została umieszczona w naszym obszarze filtru, a następnie naciśnij OK, a to doda fragmentator do arkusza.
- Widzimy, że elementy wyróżnione we fragmentatorze to te, które są wyróżnione w naszych kryteriach filtru tabeli przestawnej w menu rozwijanym filtru.
To całkiem proste rozwiązanie, które wyświetla kryteria filtru. W ten sposób możemy łatwo odfiltrować wiele elementów i zobaczyć wynik różniący się w obszarach wartości. Z poniższego przykładu jasno wynika, że wybraliśmy funkcje, które są widoczne we fragmentatorze i możemy znaleźć liczbę kategorii wiekowych dla różnych branż (są to etykiety wierszy, które przeciągnęliśmy do pola etykiety wiersza), które są powiązane z tymi funkcjami, które znajdują się we fragmentatorze. Możemy zmienić funkcję zgodnie z naszymi wymaganiami i możemy zaobserwować, że wyniki różnią się w zależności od wybranych elementów.
Jeśli jednak masz tutaj dużo pozycji na liście i jest ona naprawdę długa, pozycje te mogą nie być wyświetlane poprawnie i być może będziesz musiał dużo przewijać, aby zobaczyć, które pozycje są zaznaczone, co prowadzi nas do zagnieżdżone rozwiązanie polegające na wyświetlaniu kryteriów filtrowania w komórkach.
Dlatego z pomocą przychodzi nam „Utwórz listę komórek z kryteriami filtrowania tabeli przestawnej”.
Utwórz listę komórek z kryteriami filtrowania tabeli przestawnej: -
Zamierzamy użyć połączonej tabeli przestawnej i zasadniczo użyjemy tutaj powyższego fragmentatora, aby połączyć ze sobą dwie tabele przestawne.
- Teraz stwórzmy zduplikowaną kopię istniejącej tabeli przestawnej i wklejmy ją do pustej komórki.
Więc teraz mamy zduplikowaną kopię naszej tabeli przestawnej i zamierzamy trochę zmodyfikować, aby pokazać to pole Funkcje w obszarze wierszy.
Aby to zrobić, musimy wybrać dowolną komórkę w naszej tabeli przestawnej tutaj i przejść do listy pól tabeli przestawnej i usunąć branżę z wierszy, usuwając Count of Age Category z obszaru wartości, a my zajmiemy się Funkcja, która jest w naszym obszarze filtrów do obszaru wierszy, więc teraz widzimy, że mamy listę naszych kryteriów filtrowania, jeśli spojrzymy tutaj, w menu rozwijanym filtrów, mamy listę elementów, które znajdują się we fragmentatorach i filtru funkcyjnego.
- Teraz mamy listę naszych kryteriów filtrowania tabeli przestawnej, a to działa, ponieważ obie te tabele przestawne są połączone fragmentatorem. Jeśli klikniemy prawym przyciskiem myszy w dowolnym miejscu fragmentatora &, aby zgłosić połączenia
- Połączenia tabel przestawnych, które otworzą menu pokazujące nam, że obie te tabele przestawne są połączone, gdy pola wyboru są zaznaczone.
Oznacza to, że za każdym razem, gdy jedna zmiana zostanie wprowadzona w pierwszym punkcie obrotu, zostanie automatycznie odzwierciedlona w drugiej.
Tabele można przenosić w dowolne miejsce; może być stosowany w dowolnych modelach finansowych; etykiety wierszy można również zmienić.
Lista wartości oddzielonych przecinkami w filtrze tabeli przestawnej programu Excel: -
Zatem trzeci sposób wyświetlania naszych kryteriów filtru tabeli przestawnej znajduje się w pojedynczej komórce z listą wartości oddzielonych przecinkami i możemy to zrobić za pomocą funkcji TEXTJOIN . Nadal potrzebujemy tabel, których używaliśmy wcześniej i właśnie użyliśmy formuły do utworzenia tego ciągu wartości i oddzielenia ich przecinkami.
To jest nowa formuła lub nowa funkcja, która została wprowadzona w programie Excel 2016 i nazywa się TEXTJOIN (jeśli nie ma 2016, możesz również użyć funkcji konkatenacji); łączenie tekstu znacznie ułatwia ten proces.
TEXTJOIN zasadniczo daje nam trzy różne argumenty
- Separator - może to być przecinek lub spacja
- Ignoruj puste - prawda lub fałsz, aby zignorować puste komórki lub nie
- Tekst - dodaj lub określ zakres komórek, w których znajdują się wartości, które chcemy połączyć
Wpiszmy TEXTJOIN - (separator - który w tym przypadku byłby „,” w tym przypadku TRUE (ponieważ powinniśmy zignorować puste komórki), K: K (podobnie jak lista wybranych elementów z filtru będzie dostępna w tej kolumnie), aby dołączyć do dowolnego wartość, a także zignoruj każdą pustą wartość)
- Teraz otrzymujemy listę wszystkich naszych kryteriów filtrowania tabeli przestawnej połączonych łańcuchem. Jest to więc w zasadzie lista wartości oddzielonych przecinkami.
- Gdybyśmy nie chcieli wyświetlać tych kryteriów filtrowania w formule, moglibyśmy ukryć komórkę. Po prostu wybierz komórkę i przejdź do zakładki opcji analizy; kliknij nagłówki pól, co spowoduje ukrycie komórki.
Więc teraz mamy listę wartości w ich kryteriach filtru tabeli przestawnej. Teraz, jeśli wprowadzimy zmiany w filtrze tabeli przestawnej, zostanie to odzwierciedlone we wszystkich metodach. Możemy użyć dowolnego z nich. Ale ostatecznie, w przypadku fragmentatora rozwiązań rozdzielanych przecinkami i lista jest wymagana. Jeśli nie chcesz wyświetlać tabel, możesz je ukryć.
O czym należy pamiętać o filtrze tabeli przestawnej programu Excel
- Filtrowanie tabeli przestawnej nie jest addytywne, ponieważ jeśli wybierzemy jedno kryterium i będziemy chcieli filtrować ponownie według innych kryteriów, to pierwsze zostanie odrzucone.
- Mamy specjalną funkcję w filtrze tabeli przestawnej, tj. „Pole wyszukiwania”, która pozwala nam ręcznie odznaczyć niektóre wyniki, których nie chcemy. Na przykład: jeśli mamy ogromną listę i są tam również spacje, to aby wybrać puste, możemy łatwo zostać wybrani, szukając pustego pola w polu wyszukiwania, zamiast przewijać w dół do końca.
- Nie powinniśmy wykluczać niektórych wyników z warunkiem w filtrze tabeli przestawnej, ale możemy to zrobić za pomocą „filtra etykiety”. Na przykład: jeśli chcemy wybrać dowolny produkt z określoną walutą, taką jak rupia, dolar itp., Możemy użyć filtru etykiety - „nie zawiera” i powinien podać warunek.
Możesz pobrać ten szablon filtru tabeli przestawnej programu Excel stąd - Szablon programu Excel Filtr tabeli przestawnej.
