Jak filtrować dane w tabeli przestawnej w programie Excel

Istnieją różne sposoby filtrowania danych w tabeli przestawnej w programie Excel.

Podczas przechodzenia przez ten samouczek zobaczysz, że dostępne są różne opcje filtrowania danych w zależności od typu danych.

Rodzaje filtrów w tabeli przestawnej

Oto demonstracja typów filtrów dostępnych w tabeli przestawnej.

Spójrzmy na te filtry jeden po drugim:

• Filtr raportów: Ten filtr umożliwia drążenie do podzbioru ogólnego zestawu danych. Na przykład, jeśli masz dane o sprzedaży detalicznej, możesz analizować dane dla każdego regionu, wybierając jeden lub więcej regionów (tak, umożliwia to również wielokrotny wybór). Ten filtr tworzy się, przeciągając i upuszczając pole Tabela przestawna do obszaru Filtry.
• Filtr etykiet wierszy/kolumn: Filtry te umożliwiają filtrowanie odpowiednich danych na podstawie elementów pola (takich jak filtrowanie określonego elementu lub elementu zawierającego określony tekst) lub wartości (takich jak filtrowanie 10 najlepszych elementów według wartości lub elementów o wartości większej niż/mniej niż określona wartość).
  • Pole wyszukiwania: Dostęp do tego filtru można uzyskać w filtrze etykiet wierszy/kolumn, co umożliwia szybkie filtrowanie na podstawie wprowadzonego tekstu. Na przykład, jeśli chcesz mieć dane tylko dla Costco, po prostu wpisz Costco tutaj, a on przefiltruje je za Ciebie.
  • Pola wyboru: Pozwalają one wybrać określone pozycje z listy. Na przykład, jeśli chcesz ręcznie wybrać sprzedawców do analizy, możesz to zrobić tutaj. Możesz też selektywnie wykluczyć niektórych sprzedawców, odznaczając je.

Zauważ, że dla użytkownika dostępne są jeszcze dwa narzędzia do filtrowania: Fragmentatory i Osie czasu (które nie zostały omówione w tym samouczku).

Zobaczmy kilka praktycznych przykładów wykorzystania ich do filtrowania danych w tabeli przestawnej.

Przykłady użycia filtrów w tabeli przestawnej

W tej sekcji omówiono następujące przykłady:

• Filtruj 10 najlepszych pozycji według wartości/procentu/sumy.
• Filtruj przedmioty na podstawie wartości.
• Filtruj za pomocą filtra etykiet.
• Filtruj za pomocą pola wyszukiwania.

Filtruj 10 najlepszych pozycji w tabeli przestawnej

Możesz użyć 10 najlepszych opcji filtrowania w tabeli przestawnej, aby:

• Filtruj górne/dolne pozycje według wartości.
• Filtruj górne/dolne pozycje, które składają się na określony procent wartości.
• Filtruj górne/dolne pozycje, które składają się na określoną wartość.

Załóżmy, że masz tabelę przestawną, jak pokazano poniżej:

Zobaczmy, jak używać filtra Top 10 z tym zestawem danych.

Filtruj górne/dolne pozycje według wartości

Możesz użyć filtru Top 10, aby uzyskać listę 10 najlepszych sprzedawców na podstawie wartości sprzedaży.

Oto kroki, aby to zrobić:

• Przejdź do filtra etykiet wierszy -> Filtry wartości -> Top 10.
• W oknie dialogowym Top 10 Filter dostępne są cztery opcje, które należy określić:
  • Góra/Dół: W tym przypadku, ponieważ szukamy 10 najlepszych sprzedawców, wybierz Top.
  • Liczba elementów, które chcesz filtrować. W tym przypadku, ponieważ chcemy zdobyć 10 najlepszych przedmiotów, będzie to 10.
  • Trzecie pole to lista rozwijana z trzema opcjami: Pozycje, Procent i Suma. W tym przypadku, ponieważ chcemy mieć 10 najlepszych sprzedawców, wybierz Przedmioty.
  • Ostatnie pole zawiera wszystkie różne wartości wymienione w obszarze wartości. W tym przypadku, ponieważ mamy tylko sumę sprzedaży, pokazuje tylko „Sum of Sales”.

W ten sposób otrzymasz przefiltrowaną listę 10 sprzedawców na podstawie ich wartości sprzedaży.

Możesz użyć tego samego procesu, aby uzyskać 10 najniższych (lub dowolną inną liczbę) pozycji według wartości.

Filtruj górne/dolne pozycje, które składają się na określony procent wartości

Możesz użyć filtru 10 najlepszych, aby uzyskać listę 10 procent (lub dowolną inną liczbę, powiedzmy 20 procent, 50 procent itd.) elementów według wartości.

Załóżmy, że chcesz uzyskać listę sprzedawców detalicznych, którzy stanowią 25% całkowitej sprzedaży.

Oto kroki, aby to zrobić:

• Przejdź do filtra etykiet wierszy -> Filtry wartości -> Top 10.
• W oknie dialogowym Top 10 Filter dostępne są cztery opcje, które należy określić:
  • Góra/Dół: W tym przypadku, ponieważ szukamy najlepszych sprzedawców, którzy mają 25% całkowitej sprzedaży, wybierz Top.
  • W drugim polu musisz określić procent sprzedaży, jaki powinni uwzględniać najlepsi sprzedawcy. W tym przypadku, ponieważ chcemy uzyskać najlepszych detalistów, którzy realizują 25% sprzedaży, będzie to 25.
  • W trzecim polu wybierz Procent.
  • Ostatnie pole zawiera wszystkie różne wartości wymienione w obszarze wartości. W tym przypadku, ponieważ mamy tylko sumę sprzedaży, pokazuje tylko „Sum of Sales”.

W ten sposób otrzymasz przefiltrowaną listę sprzedawców, którzy stanowią 25% całkowitej sprzedaży.

Możesz użyć tego samego procesu, aby uzyskać sprzedawców detalicznych, którzy stanowią dolne 25% (lub dowolny inny procent) całkowitej sprzedaży.

Filtruj górne/dolne pozycje, które składają się na określoną wartość

Załóżmy, że chcesz poznać najlepszych sprzedawców detalicznych, których sprzedaż wynosi 20 milionów.

Możesz to zrobić za pomocą filtru Top 10 w tabeli przestawnej.

Aby to zrobić:

• Przejdź do filtra etykiet wierszy -> Filtry wartości -> Top 10.
• W oknie dialogowym Top 10 Filter dostępne są cztery opcje, które należy określić:
  • Góra/Dół: W tym przypadku, ponieważ szukamy najlepszych sprzedawców detalicznych, których łączna sprzedaż wynosi 20 milionów, wybierz Top.
  • W drugim polu musisz podać wartość, którą powinni uwzględniać najlepsi sprzedawcy. W tym przypadku, ponieważ chcemy pozyskać najlepszych sprzedawców, którzy osiągają 20 milionów sprzedaży, będzie to 20000000.
  • W trzecim polu wybierz Suma.
  • Ostatnie pole zawiera wszystkie różne wartości wymienione w obszarze wartości. W tym przypadku, ponieważ mamy tylko sumę sprzedaży, pokazuje tylko „Sum of Sales”.

Dzięki temu otrzymasz przefiltrowaną listę najlepszych sprzedawców, którzy stanowią 20 milionów całkowitej sprzedaży.

Filtruj przedmioty na podstawie wartości

Elementy można filtrować na podstawie wartości w kolumnach w obszarze wartości.

Załóżmy, że masz tabelę przestawną utworzoną na podstawie danych sprzedaży detalicznej, jak pokazano poniżej:

Możesz filtrować tę listę na podstawie wartości sprzedaży. Załóżmy na przykład, że chcesz uzyskać listę wszystkich sprzedawców detalicznych, których sprzedaż przekracza 3 miliony.

Oto kroki, aby to zrobić:

• Przejdź do filtra etykiet wierszy -> Filtry wartości -> Większe niż.
• W oknie dialogowym Filtr wartości:
  • Wybierz wartości, których chcesz użyć do filtrowania. W tym przypadku jest to Suma sprzedaży (jeśli masz więcej pozycji w obszarze wartości, lista rozwijana pokaże wszystko).
  • Wybierz warunek. Ponieważ chcemy, aby wszyscy detaliści ze sprzedażą przekraczali 3 miliony, wybierz „jest większa niż”.
  • Wpisz 3000000 w ostatnim polu.
• Kliknij OK.

Spowodowałoby to natychmiastowe przefiltrowanie listy i wyświetlenie tylko tych sprzedawców, których sprzedaż przekracza 3 miliony.

Podobnie istnieje wiele innych warunków, których możesz użyć, takich jak równy, nie równy, mniejszy niż, pomiędzy itd.

Filtruj dane za pomocą filtrów etykiet

Filtry etykiet przydają się, gdy masz ogromną listę i chcesz filtrować określone elementy na podstawie ich nazwy/tekstu.

Na przykład na liście sprzedawców mogę szybko przefiltrować wszystkie sklepy dolarowe, używając warunku „dolar” w nazwie.

Oto kroki, aby to zrobić:

• Przejdź do filtra etykiet wierszy -> Filtry etykiet -> Zawiera.
• W oknie dialogowym filtru etykiet:
  • Opcja „Zawiera” jest zaznaczona domyślnie (ponieważ wybraliśmy zawiera w poprzednim kroku). Możesz to zmienić tutaj, jeśli chcesz.
  • Wprowadź ciąg tekstowy, według którego chcesz filtrować listę. W tym przypadku jest to „dolar”.
• Kliknij OK.

Wraz z tekstem możesz także używać symboli wieloznacznych.

Należy pamiętać, że te filtry nie są addytywne. Jeśli więc wyszukasz termin „dolar”, otrzymasz listę wszystkich sklepów, w których występuje słowo „dolar”, ale jeśli ponownie użyjesz tego filtra, aby uzyskać listę z innym terminem, filtr zostanie odfiltrowany na podstawie nowego terminu.

Podobnie możesz użyć innych filtrów etykiet, takich jak zaczyna się od, kończy się na nie zawiera itp.

Filtruj dane za pomocą pola wyszukiwania

Filtrowanie listy za pomocą pola wyszukiwania bardzo przypomina opcję zawiera w filtrze etykiet.

Na przykład, jeśli musisz odfiltrować wszystkich sprzedawców, którzy mają w sobie nazwę „dolar”, po prostu wpisz dolar w polu wyszukiwania, a wyniki zostaną odfiltrowane.

Oto kroki:

• Kliknij listę rozwijaną Filtr etykiet, a następnie kliknij pole wyszukiwania, aby umieścić w nim kursor.
• Wprowadź wyszukiwane hasło, którym w tym przypadku jest „dolar”. Zauważysz, że lista zostanie przefiltrowana w polu wyszukiwania poniżej i możesz odznaczyć dowolnego sprzedawcę, którego chcesz wykluczyć.
• Kliknij OK.

To natychmiast odfiltruje wszystkich sprzedawców, którzy zawierają termin „dolar”.

W polu wyszukiwania możesz używać symboli wieloznacznych. Na przykład, jeśli chcesz uzyskać nazwy wszystkich sprzedawców, których nazwy zaczynają się na literę T, użyj ciągu wyszukiwania jako T* (T, po którym następuje gwiazdka). Ponieważ gwiazdka reprezentuje dowolną liczbę znaków, oznacza to, że nazwa może zawierać dowolną liczbę znaków po T.

Podobnie, jeśli chcesz uzyskać listę wszystkich sprzedawców detalicznych, które kończą się literą T, użyj wyszukiwanego terminu jako *T (gwiazdka, po której następuje T).

Jest kilka ważnych rzeczy, które należy wiedzieć o pasku wyszukiwania:

• Jeśli filtrujesz raz według jednego kryterium, a następnie filtrujesz ponownie według innego, pierwsze kryterium zostanie odrzucone i otrzymasz listę drugich kryteriów. Filtrowanie nie jest addytywne.
• Zaletą korzystania z pola wyszukiwania jest możliwość ręcznego usunięcia zaznaczenia niektórych wyników. Na przykład, jeśli masz ogromną listę firm finansowych i chcesz filtrować tylko banki, możesz wyszukać termin „bank”. Ale w przypadku, gdy wkradają się firmy, które nie są bankami, możesz po prostu odznaczyć to i trzymać z dala.
• Nie możesz wykluczyć konkretnych wyników. Na przykład, jeśli chcesz wykluczyć tylko tych sprzedawców, którzy zawierają w sobie dolary, nie można tego zrobić za pomocą pola wyszukiwania. Można to jednak zrobić za pomocą filtru etykiet z warunkiem „nie zawiera”.

Możesz również polubić następujące samouczki dotyczące tabel przestawnych:

• Tworzenie tabeli przestawnej w programie Excel - samouczek krok po kroku.
• Przygotowywanie danych źródłowych do tabeli przestawnej.
• Numery grupowe w tabeli przestawnej w programie Excel.
• Grupuj daty w tabelach przestawnych w programie Excel.
• Odśwież tabelę przestawną w programie Excel.
• Usuń tabelę przestawną w programie Excel.
• Jak dodać i używać pola obliczeniowego tabeli przestawnej programu Excel.
• Zastosuj formatowanie warunkowe w tabeli przestawnej w programie Excel.
• Pamięć podręczna przestawna w programie Excel - co to jest i jak najlepiej z niej korzystać?
• Zastąp puste komórki zerami w tabelach przestawnych programu Excel.
• Policz odrębne wartości w tabeli przestawnej