Tabele przestawne programu Excel - filtrowanie danych
Być może trzeba będzie przeprowadzić dogłębną analizę podzbioru danych tabeli przestawnej. Może to być spowodowane tym, że masz duże dane i musisz skupić się na mniejszej części danych lub niezależnie od rozmiaru danych, musisz skupić się na pewnych konkretnych danych. Dane w tabeli przestawnej można filtrować na podstawie podzbioru wartości co najmniej jednego pola. Można to zrobić na kilka sposobów:
- Filtrowanie przy użyciu fragmentatorów.
- Filtrowanie za pomocą filtrów raportów.
- Ręczne filtrowanie danych.
- Filtrowanie za pomocą filtrów etykiet.
- Filtrowanie za pomocą filtrów wartości.
- Filtrowanie za pomocą filtrów dat.
- Filtrowanie za pomocą filtra Top 10.
- Filtrowanie za pomocą osi czasu.
W następnym rozdziale nauczysz się filtrowania danych przy użyciu fragmentatorów. Zrozumiesz, jak korzystać z innych metod wymienionych powyżej w tym rozdziale.
Weź pod uwagę następującą tabelę przestawną, w której masz podsumowane dane dotyczące sprzedaży pod względem regionu, sprzedawcy i miesiąca.
Filtry raportów
Możesz przypisać filtr do jednego z pól, aby móc dynamicznie zmieniać tabelę przestawną na podstawie wartości tego pola.
Przeciągnij region z wierszy do filtrów w obszarach tabeli przestawnej.
Filtr z etykietą Region pojawia się nad tabelą przestawną (w przypadku, gdy nad tabelą przestawną nie ma pustych wierszy, tabela przestawna jest wypychana w dół, aby zrobić miejsce na filtr.
Zauważysz to
Wartości sprzedawcy pojawiają się w wierszach.
Wartości miesięcy pojawiają się w kolumnach.
Filtr regionu pojawia się na górze z domyślnym ustawieniem WSZYSTKIE.
Wartość podsumowująca to suma kwoty zamówienia.
Suma kwoty zamówienia według sprzedawcy pojawia się w kolumnie Suma całkowita.
Suma kwoty zamówienia miesięcznie pojawia się w wierszu Suma całkowita.
Kliknij strzałkę w polu po prawej stronie Regionu filtru.
Zostanie wyświetlona lista rozwijana z wartościami pola Region. Sprawdź pudełkoSelect Multiple Items.
Domyślnie wszystkie pola są zaznaczone. Odznacz pole (All). Wszystkie pola zostaną odznaczone.
Następnie zaznacz pola - Południe i Zachód i kliknij OK.
Dane dotyczące tylko regionów południowych i zachodnich zostaną podsumowane.
W komórce obok Regionu filtru wyświetla się (Wiele elementów), co oznacza, że wybrano więcej niż jeden element. Jednak ile elementów i / lub które elementy nie są znane z wyświetlanego raportu. W takim przypadku użycie fragmentatorów jest lepszą opcją do filtrowania.
Ręczne filtrowanie
Możesz również przefiltrować tabelę przestawną, wybierając wartości pola ręcznie. Możesz to zrobić, klikając strzałkę
Załóżmy, że chcesz analizować tylko dane z lutego. Musisz przefiltrować wartości według pola Miesiąc. Jak widać, Miesiąc jest częścią Etykiety kolumn.
Kliknij strzałkę
Jak widać, na liście rozwijanej znajduje się pole wyszukiwania, a pod polem znajduje się lista wartości wybranego pola, czyli Miesiąc. Pola wszystkich wartości są zaznaczone, pokazując, że wszystkie wartości tego pola zostały wybrane.
Odznacz pole (Zaznacz wszystko) u góry listy wartości.
Zaznacz pola wartości, które chcesz wyświetlić w tabeli przestawnej, w tym przypadku w lutym, i kliknij przycisk OK.
W tabeli przestawnej są wyświetlane tylko te wartości, które są związane z wybraną wartością pola Miesiąc - luty. Możesz zauważyć, że strzałka filtrowania zmienia się w ikonę,
Można zauważyć, że jest wyświetlany symbol wskazujący, że w polu Miesiąc zastosowano filtr ręczny.
Jeśli chcesz zmienić wartość wyboru filtra, wykonaj następujące czynności -
Kliknij
ikonę.
Zaznacz / odznacz pola wartości.
Jeśli wszystkie wartości pola nie są widoczne na liście, przeciągnij uchwyt w prawym dolnym rogu listy rozwijanej, aby je powiększyć. Alternatywnie, jeśli znasz wartość, wpisz ją w polu wyszukiwania.
Załóżmy, że chcesz zastosować inny filtr do powyższej przefiltrowanej tabeli przestawnej. Na przykład chcesz wyświetlić dane Waltersa i Chrisa za miesiąc luty. Musisz udoskonalić filtrowanie, dodając kolejny filtr dla pola Sprzedawca. Jak widać, Sprzedawca jest częścią Etykiety wierszy.
Kliknij strzałkę
w komórce Etykiety wierszy.
Zostanie wyświetlona lista wartości pola - Region. Dzieje się tak, ponieważ Region znajduje się na zewnętrznym poziomie sprzedawcy w kolejności zagnieżdżenia. Masz również dodatkową opcję - Wybierz pole. Kliknij pole Wybierz pole.
Kliknij Sprzedawca na liście rozwijanej. Zostanie wyświetlona lista wartości pola - Sprzedawca.
Odznacz (Zaznacz wszystko) i zaznacz Walters, Chris.
Kliknij OK.
W tabeli przestawnej są wyświetlane tylko te wartości, które są powiązane z wybraną wartością pola Miesiąc - luty i wartość pola Sprzedawca - Walters, Chris.
Strzałka filtrowania etykiet wierszy również zmienia się w ikonę,
Zostanie wyświetlone pole tekstowe wskazujące, że filtr ręczny został zastosowany w polach - Miesiąc i Sprzedawca.
W ten sposób można ręcznie filtrować tabelę przestawną na podstawie dowolnej liczby pól i dowolnej liczby wartości.
Filtrowanie według tekstu
Jeśli masz pola zawierające tekst, możesz filtrować tabelę przestawną według tekstu, pod warunkiem, że odpowiednia etykieta pola jest oparta na tekście. Weźmy na przykład pod uwagę następujące dane pracowników.
Dane zawierają szczegóły pracowników - identyfikator pracownika, tytuł, datę urodzenia, stan cywilny, płeć i datę zatrudnienia. Dodatkowo dane mają również poziom menedżera pracownika (poziomy 0 - 4).
Załóżmy, że musisz przeprowadzić analizę liczby pracowników podległych danemu pracownikowi według tytułu. Możesz utworzyć tabelę przestawną, jak podano poniżej.
Możesz chcieć wiedzieć, ilu pracowników ze słowem „Menedżer” w tytule ma pracowników podległych im. Ponieważ tytuł etykiety jest oparty na tekście, możesz zastosować filtr etykiety w polu Tytuł w następujący sposób -
Kliknij strzałkę
w komórce Etykiety wierszy.
Wybierz Tytuł w polu Wybierz pole z listy rozwijanej.
Kliknij Filtry etykiet.
Kliknij Zawiera na drugiej liście rozwijanej.
Zostanie wyświetlone okno dialogowe Filtr etykiet (tytuł). Wpisz Menedżer w polu obok Zawiera. Kliknij OK.
Tabela przestawna zostanie przefiltrowana do wartości tytułu zawierającej „Menedżer”.
Kliknij
ikonę.
Możesz zobaczyć, że
- Filtr etykiet zostanie zastosowany w polu - Tytuł i
- Co to jest zastosowany filtr etykiet.
Filtrowanie według wartości
Możesz chcieć poznać tytuły pracowników, którzy mają więcej niż 25 pracowników podległych im. W tym celu możesz zastosować filtr wartości w polu Tytuł w następujący sposób -
Kliknij strzałkę
w komórce Etykiety wierszy.
Wybierz Title w polu Wybierz pole z listy rozwijanej.
Kliknij Filtry wartości.
Wybierz Większe niż lub równe z drugiej listy rozwijanej.
Zostanie wyświetlone okno dialogowe Filtr wartości (tytuł). Wpisz 25 w prawym bocznym polu.
Tabela przestawna zostanie przefiltrowana, aby wyświetlić tytuły pracowników, którzy mają więcej niż 25 pracowników podległych im.
Filtrowanie według dat
Możesz chcieć wyświetlić dane wszystkich pracowników zatrudnionych w roku podatkowym 2015-15. Możesz użyć filtrów danych w tym samym celu -
Uwzględnij pole HireDate w tabeli przestawnej. Teraz nie potrzebujesz danych menedżera, więc usuń pole ManagerLevel z tabeli przestawnej.
Teraz, gdy masz pole Data w tabeli przestawnej, możesz użyć filtrów dat.
Kliknij strzałkę
w komórce Etykiety wierszy.
Wybierz HireDate w polu Wybierz pole z listy rozwijanej.
Kliknij opcję Filtry dat.
Seelct Between z drugiej listy rozwijanej.
Zostanie wyświetlone okno dialogowe Filtr daty (HireDate). Wpisz 4/1/2014 i 3/31/2015 w dwóch polach Data. Kliknij OK.
Tabeli przestawnej będą filtrowane, aby wyświetlić tylko te dane z HIREDATE między 1 st kwietnia 2014 i 31 st marca 2015 r.
Możesz pogrupować daty w kwartały w następujący sposób -
Kliknij prawym przyciskiem dowolną datę. PlikGrouping pojawi się okno dialogowe.
Wpisz 4/1/2014 w polu Rozpoczęcie o godz. Sprawdź pudełko.
Wpisz 31.03.2015 w polu Kończy się o. Sprawdź pudełko.
Kliknij Ćwiartki w polu poniżej By.
Daty zostaną pogrupowane w kwartały w tabeli przestawnej. Możesz sprawić, by tabela wyglądała na zwartą, przeciągając pole HireDate z obszaru ROWS do obszaru COLUMNS.
Będziesz mógł dowiedzieć się, ilu pracowników zostało zatrudnionych w danym roku podatkowym, kwartalnie.
Filtrowanie za pomocą filtru 10 najlepszych
Możesz użyć filtru 10 pierwszych, aby wyświetlić kilka pierwszych lub ostatnich kilku wartości pola w tabeli przestawnej.
Kliknij strzałkę
w komórce Etykiety wierszy.
Kliknij opcję Filtry wartości.
Kliknij Top 10 na drugiej liście rozwijanej.
Zostanie wyświetlone okno dialogowe Top 10 Filter (Title).
W pierwszym polu kliknij Góra (możesz również wybrać Dół).
W drugim polu wprowadź liczbę, powiedzmy 7.
W trzecim polu masz trzy opcje, według których możesz filtrować.
Kliknij opcję Elementy, aby filtrować według liczby elementów.
Kliknij Procent, aby filtrować według procentu.
Kliknij Suma, aby przefiltrować według sumy.
Gdy masz liczbę EmployeeID, kliknij opcję Elementy.
W czwartym polu kliknij pole Count of EmployeeID.
Kliknij OK.
W tabeli przestawnej zostanie wyświetlonych siedem pierwszych wartości według liczby EmployeeID.
Jak widać, największa liczba zatrudnionych w roku podatkowym to technicy produkcji, a przeważająca liczba zatrudnionych w pierwszym kwartale.
Filtrowanie za pomocą osi czasu
Jeśli tabela przestawna ma pole daty, możesz filtrować tabelę przestawną za pomocą osi czasu.
Utwórz tabelę przestawną na podstawie wcześniej użytych danych pracowników i dodaj dane do modelu danych w oknie dialogowym Tworzenie tabeli przestawnej.
Przeciągnij tytuł pola do obszaru WIERSZE.
Przeciągnij pole Identyfikator pracownika do obszaru ∑ WARTOŚCI i wybierz opcję Licznik do obliczenia.
Kliknij tabelę przestawną.
Kliknij kartę WSTAW.
Kliknij Oś czasu w grupie Filtry. Pojawi się okno dialogowe Wstaw osie czasu.
- Zaznacz pole HireDate.
- Kliknij OK. Oś czasu pojawi się w arkuszu.
- Narzędzia osi czasu pojawiają się na Wstążce.
Jak widać, Wszystkie okresy - w miesiącach są wyświetlane na osi czasu.
Kliknij strzałkę obok - MIESIĄCE.
Z listy rozwijanej wybierz KWARTAŁY. Widok osi czasu zmieni się na Wszystkie okresy - w kwartałach.
Kliknij I kw. 2014 r.
Przytrzymaj klawisz Shift i przeciągnij do 2014 Q4. Okres osi czasu został wybrany na okres od I do IV kwartału 2014 r.
Tabela przestawna jest filtrowana do tego okresu osi czasu.
Czyszczenie filtrów
Od czasu do czasu może być konieczne wyczyszczenie ustawionych filtrów, aby przełączyć się między różnymi kombinacjami i prognozami danych. Możesz to zrobić na kilka sposobów w następujący sposób -
Wyczyść wszystkie filtry w tabeli przestawnej
Możesz wyczyścić wszystkie filtry ustawione w tabeli przestawnej za jednym razem w następujący sposób -
- Kliknij kartę HOME na Wstążce.
- Kliknij Sortuj i filtruj w grupie Edycja.
- Wybierz Wyczyść z listy rozwijanej.
Czyszczenie etykiety, daty lub filtru wartości
Aby wyczyścić etykietę, datę lub filtr wartości, wykonaj następujące czynności -
Kliknij ikonę w obszarze Etykiety wierszy lub Etykiety kolumn.
Kliknij
<nazwa pola>, z którego chcesz wyczyścić filtr w polu Wybierz pole na liście rozwijanej.
Kliknij opcję Wyczyść filtr z <nazwa pliku>, która pojawia się na liście rozwijanej.
Kliknij OK. Konkretny filtr zostanie wyczyszczony.
