Tabele przestawne programu Excel - szybki przewodnik

Tabela przestawna to niezwykle potężne narzędzie, którego można używać do krojenia danych. Możesz śledzić i analizować setki tysięcy punktów danych za pomocą zwartej tabeli, którą można dynamicznie zmieniać, aby umożliwić znalezienie różnych perspektyw danych. Jest to proste w użyciu narzędzie, ale potężne.

Główne cechy tabeli przestawnej są następujące:

Tworzenie tabeli przestawnej jest niezwykle proste i szybkie
Umożliwienie natychmiastowego przetwarzania danych poprzez proste przeciąganie pól, sortowanie i filtrowanie oraz różne obliczenia dotyczące danych.
Uzyskanie odpowiedniej reprezentacji danych, gdy zdobędziesz wgląd w nie.
Możliwość tworzenia raportów w locie.
Tworzenie wielu raportów z tej samej tabeli przestawnej w ciągu kilku sekund.
Dostarczanie interaktywnych raportów do synchronizacji z publicznością.

W tym samouczku szczegółowo poznasz funkcje tabeli przestawnej wraz z przykładami. Zanim ukończysz ten samouczek, będziesz mieć wystarczającą wiedzę na temat funkcji tabeli przestawnej, dzięki której możesz rozpocząć eksplorowanie, analizowanie i raportowanie danych w oparciu o wymagania.

Tworzenie tabeli przestawnej

Możesz utworzyć tabelę przestawną z zakresu danych lub tabeli programu Excel. Jeśli wiesz, czego szukasz, możesz zacząć od pustej tabeli przestawnej, aby podać szczegóły. Możesz także skorzystać z zalecanych tabel przestawnych programu Excel, które mogą dać ci orientację w układach tabeli przestawnej, które najlepiej nadają się do podsumowania danych.

Dowiesz się, jak utworzyć tabelę przestawną z zakresu danych lub tabeli programu Excel w rozdziale - Tworzenie tabeli przestawnej na podstawie tabeli lub zakresu.

Program Excel zapewnia skuteczniejszy sposób tworzenia tabeli przestawnej na podstawie wielu tabel, różnych źródeł danych i zewnętrznych źródeł danych. Nazywa się PowerPivot, który działa na swojej bazie danych znanej jako model danych. Nauczysz się tych elektronarzędzi programu Excel w innych samouczkach w tej bibliotece samouczków.

Musisz najpierw dowiedzieć się o normalnej tabeli przestawnej, jak wyjaśniono w tym samouczku, zanim zaczniesz korzystać z elektronarzędzi.

Układ tabeli przestawnej - pola i obszary

Układ tabeli przestawnej zależy po prostu od tego, jakie pola zostały wybrane dla raportu i jak je uporządkowałeś w obszarach. Wyboru i uporządkowania można dokonać, po prostu przeciągając pola. Podczas przeciągania pól układ tabeli przestawnej zachowuje zmiany i dzieje się to w ciągu kilku sekund.

Dowiesz się o polach i obszarach tabeli przestawnej w rozdziałach - Pola tabeli przestawnej i obszary tabeli przestawnej.

Eksploracja danych za pomocą tabeli przestawnej

Podstawowym celem korzystania z tabeli przestawnej jest zwykle badanie danych w celu wyodrębnienia istotnych i wymaganych informacji. Masz kilka opcji, aby to zrobić, które obejmują sortowanie, filtrowanie, zagnieżdżanie, zwijanie i rozwijanie, grupowanie i rozgrupowywanie itp.

Omówienie tych opcji znajdziesz w rozdziale - Eksploracja danych za pomocą tabeli przestawnej.

Podsumowanie wartości

Po zestawieniu danych wymaganych przez różne techniki eksploracji następnym krokiem, który chciałbyś wykonać, jest podsumowanie danych. Excel udostępnia różne typy obliczeń, które można zastosować w zależności od przydatności i wymagań. Możesz także przełączać się między różnymi typami obliczeń i przeglądać wyniki w ciągu kilku sekund.

Dowiesz się, jak zastosować typy obliczeń w tabeli przestawnej w rozdziale - Podsumowanie wartości według różnych typów obliczeń.

Aktualizowanie tabeli przestawnej

Po zbadaniu danych i podsumowaniu ich nie musisz powtarzać ćwiczenia, jeśli i kiedy dane źródłowe zostaną zaktualizowane. Możesz odświeżyć tabelę przestawną, aby odzwierciedlała zmiany w danych źródłowych.

Dowiesz się o różnych sposobach odświeżania danych w rozdziale - Aktualizacja tabeli przestawnej.

Raporty w formie tabeli przestawnej

Po zbadaniu i podsumowaniu danych za pomocą tabeli przestawnej przedstawisz je jako raport. Raporty w formie tabeli przestawnej mają charakter interaktywny, a specjalność polega na tym, że nawet osoba niezaznajomiona z programem Excel może z nich korzystać intuicyjnie. Ze względu na swój nieodłączny dynamiczny charakter pozwolą Ci szybko zmienić perspektywę raportu, aby pokazać wymagany poziom szczegółowości lub skupić się na konkretnych elementach, którymi interesuje się publiczność.

Ponadto, możesz zorganizować raport w formie tabeli przestawnej dla samodzielnej prezentacji lub jako integralna część szerokiego raportu, zależnie od przypadku. Dowiesz się kilku raportów z tabel przestawnych w rozdziale - Raporty w formie tabeli przestawnej.

Możesz utworzyć tabelę przestawną z zakresu danych lub z tabeli programu Excel. W obu przypadkach pierwszy wiersz danych powinien zawierać nagłówki kolumn.

Jeśli masz pewność, jakie pola mają zostać uwzględnione w tabeli przestawnej i jaki układ chcesz mieć, możesz rozpocząć od pustej tabeli przestawnej i utworzyć tabelę przestawną.

Jeśli nie masz pewności, który układ tabeli przestawnej najlepiej pasuje do Twoich danych, możesz skorzystać z polecenia Zalecane tabele przestawne programu Excel, aby wyświetlić tabele przestawne dostosowane do Twoich danych i wybrać tę, którą lubisz.

Tworzenie tabeli przestawnej z zakresu danych

Weź pod uwagę następujący zakres danych, który zawiera dane dotyczące sprzedaży dla każdego sprzedawcy, w każdym regionie oraz w styczniu, lutym i marcu -

Aby utworzyć tabelę przestawną z tego zakresu danych, wykonaj następujące czynności -

Upewnij się, że pierwszy wiersz zawiera nagłówki. Potrzebujesz nagłówków, ponieważ będą to nazwy pól w tabeli przestawnej.
Nazwij zakres danych jako SalesData_Range.
Kliknij zakres danych - SalesData_Range.
Kliknij kartę WSTAW na Wstążce.

Kliknij opcję Tabela przestawna w grupie Tabele. PlikCreate PivotTable pojawi się okno dialogowe.

W oknie dialogowym Utwórz tabelę przestawną w obszarze Choose the data that you want to analyze, możesz wybrać tabelę lub zakres z bieżącego skoroszytu lub użyć zewnętrznego źródła danych.

Podczas tworzenia tabeli przestawnej na podstawie zakresu danych, w oknie dialogowym wybierz następujące opcje:

Wybierz Select a table or range.
W polu Tabela / zakres wpisz nazwę zakresu - SalesData_Range.
Wybierz opcję Nowy arkusz w obszarze Wybierz miejsce, w którym chcesz umieścić raport w formie tabeli przestawnej i kliknij przycisk OK.

Możesz przeanalizować wiele tabel, dodając ten zakres danych do modelu danych. Możesz dowiedzieć się, jak analizować wiele tabel, korzystać z modelu danych i jak używać zewnętrznego źródła danych do tworzenia tabeli przestawnej w samouczku dotyczącym programu Excel PowerPivot.

Nowy arkusz zostanie wstawiony do skoroszytu. Nowy arkusz zawiera pustą tabelę przestawną. Nadaj arkuszowi nazwę - zakres-tabela przestawna.

Jak widać, plik PivotTable Fieldslista pojawia się po prawej stronie arkusza i zawiera nazwy nagłówków kolumn w zakresie danych. Dalej na Wstążce pojawiają się narzędzia tabeli przestawnej - ANALIZA i PROJEKT.

Dodawanie pól do tabeli przestawnej

Zrozumiesz szczegółowo pola i obszary tabeli przestawnej w dalszych rozdziałach tego samouczka. Na razie obserwuj kroki, aby dodać pola do tabeli przestawnej.

Załóżmy, że chcesz podsumować kwotę zamówienia według sprzedawcy za miesiące styczeń, luty i marzec. Możesz to zrobić w kilku prostych krokach w następujący sposób -

Kliknij pole Sprzedawca na liście Pola tabeli przestawnej i przeciągnij je do obszaru WIERSZE.
Kliknij pole Miesiąc na liście Pola tabeli przestawnej i przeciągnij je również do obszaru WIERSZE.
Kliknij Kwotę zamówienia i przeciągnij ją do obszaru ∑ WARTOŚCI.

Twoja pierwsza tabela przestawna jest gotowa, jak pokazano poniżej

Zwróć uwagę, że w tabeli przestawnej pojawiają się dwie kolumny, jedna zawiera wybrane etykiety wierszy, tj. Sprzedawca i miesiąc, a druga zawiera sumę kwoty zamówienia. Oprócz miesięcznej sumy kwoty zamówienia dla każdego sprzedawcy otrzymasz również sumy pośrednie reprezentujące całkowitą sprzedaż dokonaną przez tę osobę. Jeśli przewiniesz arkusz w dół, ostatni wiersz zostanie wyświetlony jako Suma całkowita reprezentująca całkowitą sprzedaż.

Dowiesz się więcej o tworzeniu tabel przestawnych zgodnie z potrzebami w miarę postępów w tym samouczku.

Tworzenie tabeli przestawnej z tabeli

Rozważ następującą tabelę programu Excel, która zawiera te same dane dotyczące sprzedaży, co w poprzedniej sekcji -

Tabela programu Excel z natury będzie miała nazwę, a kolumny będą miały nagłówki, co jest wymagane do utworzenia tabeli przestawnej. Załóżmy, że nazwa tabeli to SalesData_Table.

Aby utworzyć tabelę przestawną z tej tabeli programu Excel, wykonaj następujące czynności -

Kliknij tabelę - SalesData_Table.
Kliknij kartę WSTAW na Wstążce.
Kliknij opcję Tabela przestawna w grupie Tabele. PlikCreate PivotTable pojawi się okno dialogowe.

Kliknij Wybierz tabelę lub zakres.
W polu Tabela / zakres wpisz nazwę tabeli - SalesData_Table.
Wybierz Nowy arkusz roboczy pod Choose where you want the PivotTable report to be placed. Kliknij OK.

Nowy arkusz zostanie wstawiony do skoroszytu. Nowy arkusz zawiera pustą tabelę przestawną. Nazwij arkusz - tabela-tabela przestawna. Arkusz roboczy - tabela-tabela przestawna wygląda podobnie do tego, który masz w przypadku zakresu danych we wcześniejszej sekcji.

Możesz dodawać pola do tabeli przestawnej, jak pokazano w sekcji - Dodawanie pól do tabeli przestawnej, wcześniej w tym rozdziale.

Tworzenie tabeli przestawnej z zalecanymi tabelami przestawnymi

Jeśli nie jesteś zaznajomiony z tabelami przestawnymi programu Excel lub jeśli nie wiesz, które pola dałyby miarodajny raport, możesz użyć polecenia Zalecane tabele przestawne w programie Excel. Zalecane tabele przestawne zawierają wszystkie możliwe raporty z danymi wraz z powiązanym układem. Innymi słowy, wyświetlane opcje będą tabelami przestawnymi dostosowanymi do Twoich danych.

Aby utworzyć tabelę przestawną z tabeli SalesData-Table programu Excel przy użyciu zalecanych tabel przestawnych, wykonaj następujące czynności -

Kliknij tabelę SalesData-Table.
Kliknij kartę WSTAW.
Kliknij Zalecane tabele przestawne w grupie Tabele. Zostanie wyświetlone okno dialogowe Zalecane tabele przestawne.

W oknie dialogowym Zalecane tabele przestawne zostaną wyświetlone możliwe dostosowane tabele przestawne, które odpowiadają Twoim danym.

Kliknij każdą z opcji tabeli przestawnej, aby wyświetlić podgląd po prawej stronie.
Kliknij opcję Tabela przestawna - Suma kwoty zamówienia według sprzedawcy i miesiąca, a następnie kliknij przycisk OK.

Pojawi się podgląd po prawej stronie.

Wybrana tabela przestawna pojawi się w nowym arkuszu w skoroszycie.

Jak widać, zostały wybrane pola tabeli przestawnej - Sprzedawca, Region, Kwota zamówienia i Miesiąc. Spośród nich Region i Sprzedawca znajdują się w obszarze WIERSZE, Miesiąc w obszarze KOLUMNY, a Suma kwoty zamówienia w obszarze ∑ WARTOŚCI.

W tabeli przestawnej podsumowano dane według regionu, sprzedawcy i miesiąca. Sumy częściowe są wyświetlane dla każdego regionu, każdego sprzedawcy i każdego miesiąca.

Pola tabeli przestawnej to okienko zadań skojarzone z tabelą przestawną. Okienko zadań Pola tabeli przestawnej składa się z pól i obszarów. Domyślnie okienko zadań jest wyświetlane po prawej stronie okna, a pola są wyświetlane nad obszarami.

Pola reprezentują kolumny w danych - zakres lub tabela programu Excel i będą zawierać pola wyboru. Wybrane pola zostaną wyświetlone w raporcie. Obszary reprezentują układ raportu i obliczenia zawarte w raporcie.

W dolnej części Okienka zadań znajduje się opcja - Odrocz aktualizację układu z przyciskiem AKTUALIZUJ obok.

Domyślnie nie jest to zaznaczone, a wszelkie zmiany dokonane w zaznaczeniu pól lub w opcjach układu są natychmiast odzwierciedlane w tabeli przestawnej.
Jeśli wybierzesz tę opcję, zmiany dokonane w wyborze nie zostaną zaktualizowane, dopóki nie klikniesz pliku UPDATE przycisk.

W tym rozdziale poznasz szczegóły dotyczące pól. W następnym rozdziale poznasz szczegóły dotyczące obszarów.

Okienko zadań pól tabeli przestawnej

Okienko zadań Pola tabeli przestawnej można znaleźć w arkuszu, w którym znajduje się tabela przestawna. Aby wyświetlić okienko zadań Pola tabeli przestawnej, kliknij tabelę przestawną. W przypadku, gdy okienko zadań Pola tabeli przestawnej nie jest wyświetlane, sprawdź na Wstążce:

Kliknij kartę ANALIZA w obszarze NARZĘDZIA PRZESTAWNE na Wstążce.
Sprawdź, czy lista pól jest zaznaczona (tj. Podświetlona) w grupie Pokaż.
Jeśli lista pól nie jest zaznaczona, kliknij ją.

Okienko zadań Pola tabeli przestawnej zostanie wyświetlone po prawej stronie okna z tytułem - Pola tabeli przestawnej.

Przenoszenie okienka zadań pól tabeli przestawnej

Po prawej stronie tytułu Pola tabeli przestawnej w okienku zadań tabeli przestawnej znajduje się przycisk

. To reprezentuje opcje okienka zadań. Kliknij przycisk

. Opcje Okienka zadań - Przenieś, Rozmiar i Zamknij pojawiają się na liście rozwijanej.

Okienko zadań tabel przestawnych można przenieść w dowolne miejsce w oknie w następujący sposób -

Kliknij Przenieś na liście rozwijanej.

Na Okienku zadań pojawia się przycisk.
Kliknij

ikonę i przeciągnij panel do miejsca, w którym chcesz go umieścić. Okienko zadań można umieścić obok tabeli przestawnej, jak podano poniżej.

Okienko zadań można umieścić po lewej stronie okna, jak podano poniżej.

Zmiana rozmiaru okienka zadań pól tabeli przestawnej

Możesz zmienić rozmiar okienka zadań tabel przestawnych - tj. Zwiększyć / zmniejszyć długość i / lub szerokość okienka zadań w następujący sposób -

Kliknij Opcje okienka zadań -

czyli po prawej stronie tytułu - Pola tabeli przestawnej.
Kliknij Rozmiar na liście rozwijanej.
Użyj symbolu ⇔ aby zwiększyć / zmniejszyć szerokość okienka zadań.
Użyj symbolu ⇕ aby zwiększyć / zmniejszyć szerokość okienka zadań.

W obszarze ∑ WARTOŚCI, aby Suma kwoty zamówienia była widoczna w całości, możesz zmienić rozmiar Okienka zadań, jak podano poniżej.

Pola tabeli przestawnej

Lista pól tabeli przestawnej zawiera wszystkie tabele skojarzone ze skoroszytem oraz odpowiadające im pola. Wystarczy wybrać pola z listy pól tabeli przestawnej, aby utworzyć tabelę przestawną.

Tabele i odpowiadające im pola z polami wyboru odzwierciedlają dane z tabeli przestawnej. Ponieważ możesz losowo zaznaczać / odznaczać pola, możesz szybko zmienić tabelę przestawną, podświetlając podsumowane dane, które chcesz zgłosić lub przedstawić.

Jak można zauważyć, jeśli istnieje tylko jedna tabela, nazwa tabeli nie będzie wyświetlana na liście Pola tabeli przestawnej. Tylko pola zostaną wyświetlone z polami wyboru.

Nad listą pól znajduje się akcja Wybierz pola do dodania do raportu. Po prawej stronie znajdziesz przycisk -

reprezentujący Narzędzia.

Kliknij przycisk Narzędzia.

Na liście rozwijanej znajdziesz następujące -

Pięć różnych opcji układu dla pól i obszarów.
Dwie opcje kolejności sortowania pól na liście Pola -
- Sortuj od A do Z.
- Sortuj w kolejności źródła danych.

Jak widać na powyższej liście Pola, kolejność sortowania jest domyślnie - tj. W kolejności źródła danych. Oznacza to, że jest to kolejność, w jakiej pojawiają się kolumny w tabeli danych.

Zwykle możesz zachować domyślną kolejność. Jednak czasami możesz napotkać wiele pól w tabeli i możesz ich nie znać. W takim przypadku możesz posortować pola w kolejności alfabetycznej, klikając - Sortuj od A do Z na rozwijanej liście Narzędzia. Następnie lista pól tabeli przestawnej wygląda następująco:

Obszary tabeli przestawnej są częścią okienka zadań Pola tabeli przestawnej. Rozmieszczając wybrane pola w obszarach, można uzyskać różne układy tabeli przestawnej. Ponieważ możesz po prostu przeciągać pola między obszarami, możesz szybko przełączać się między różnymi układami, podsumowując dane w dowolny sposób.

Dowiedziałeś się już o okienku zadań Pola tabeli przestawnej we wcześniejszym rozdziale dotyczącym pól tabeli przestawnej w tym samouczku. W tym rozdziale poznasz obszary tabeli przestawnej.

Dostępne są cztery obszary tabeli przestawnej -

ROWS.
COLUMNS.
FILTERS.
∑ WARTOŚCI (odczytywane jako wartości podsumowujące).

Wiadomość - Drag fields between areas below pojawia się nad obszarami.

Dzięki obszarom tabeli przestawnej możesz wybrać -

Jakie pola mają być wyświetlane jako wiersze (obszar ROWS).
Jakie pola mają być wyświetlane jako kolumny (obszar KOLUMNY).
Jak podsumować swoje dane (obszar ∑ WARTOŚCI).
Filtry dla dowolnego pola (obszar FILTRY).

Możesz po prostu przeciągnąć pola przez te obszary i obserwować, jak zmienia się układ tabeli przestawnej.

WYDZIWIANIE

Jeśli zaznaczysz pola na listach Pola tabeli przestawnej, po prostu zaznaczając odpowiednie pola, wszystkie pola nieliczbowe zostaną automatycznie dodane do obszaru WIERSZE w wybranej przez Ciebie kolejności.

Opcjonalnie możesz przeciągnąć pole do obszaru WIERSZE. Pola umieszczone w obszarze ROWS są wyświetlane jako wiersze w tabeli przestawnej, a etykiety wierszy są wartościami wybranych pól.

Weźmy na przykład pod uwagę tabelę Dane sprzedaży.

Przeciągnij pole Sprzedawca do obszaru WIERSZE.
Przeciągnij pole Miesiąc do obszaru WIERSZE.

Twoja tabela przestawna pojawi się z jedną kolumną zawierającą etykiety wierszy - sprzedawca i miesiąc, a ostatnim wierszem jako sumą końcową, jak podano poniżej.

KOLUMNY

Możesz przeciągać pola do obszaru KOLUMNY.

Pola umieszczone w obszarze KOLUMNY są wyświetlane jako kolumny w tabeli przestawnej, a Etykiety kolumn są wartościami wybranych pól.

Przeciągnij pole Region do obszaru COLUMNS. Twoja tabela przestawna pojawi się z pierwszą kolumną zawierającą Etykiety wierszy - Sprzedawca i Miesiąc, następne cztery kolumny zawierające Etykiety kolumn - Region i ostatnią kolumnę Suma końcowa, jak podano poniżej.

Przeciągnij pole Miesiąc z WIERSZY do KOLUMN.
Przeciągnij pole Region z COLUMNS do ROWS. Twój układ tabeli przestawnej zmieni się, jak podano poniżej.

Jak widać, jest teraz tylko pięć kolumn - pierwsza kolumna z etykietami wierszy, trzy kolumny z etykietami kolumn i ostatnia kolumna z sumą końcową.

Liczba wierszy i kolumn jest oparta na liczbie wartości, które masz w tych polach.

∑ WARTOŚCI

Podstawowym zastosowaniem tabeli przestawnej jest podsumowywanie wartości. Dlatego umieszczając pola, w których chcesz podsumować dane∑ VALUES dotrzesz do tabeli zbiorczej.

Przeciągnij pole Kwota zamówienia do ∑ VALUES.
Przeciągnij pole Region powyżej pola Sprzedawca w obszarze WIERSZE. Ten krok polega na zmianie kolejności zagnieżdżenia. Zagnieżdżanie dowiesz się z rozdziału - Zagnieżdżanie w tabeli przestawnej w tym samouczku.

Jak można zauważyć, dane są podsumowywane według regionów, sprzedawców i miesięcznie. Masz sumy częściowe dla każdego regionu, w ujęciu miesięcznym. Masz również sumy całkowite miesięczne w wierszu Suma końcowa sumy całkowite regionu w kolumnie Suma całkowita.

FILTRY

Obszar Filtry służy do umieszczania filtrów w tabeli przestawnej. Załóżmy, że chcesz wyświetlić wyniki oddzielnie tylko dla wybranych regionów.

Przeciągnij pole Region z obszaru ROWS do obszaru FILTERS. Region filtru zostanie umieszczony nad tabelą przestawną. Jeśli nie masz pustych wierszy nad tabelą przestawną, tabela przestawna jest wypychana w dół, wstawiając wiersze powyżej tabeli przestawnej dla filtra.

Jak widać, (WSZYSTKO) pojawia się domyślnie w filtrze, a tabela przestawna wyświetla dane dla wszystkich wartości regionu.

Kliknij strzałkę po prawej stronie filtra.
Zaznacz pole - Wybierz wiele elementów.

Pola wyboru pojawią się dla wszystkich opcji na liście rozwijanej. Domyślnie wszystkie pola są zaznaczone.

Zaznacz pola - północ i południe.
Wyczyść pozostałe pola. Kliknij OK.

Tabela przestawna zostanie zmieniona, aby odzwierciedlić przefiltrowane dane.

Możesz zauważyć, że filtr wyświetla (Wiele pozycji). Dlatego gdy ktoś patrzy na tabelę przestawną, nie jest od razu oczywiste, jakie wartości są filtrowane.

Program Excel udostępnia inne narzędzie o nazwie Fragmentatory, które wydajniej obsługuje filtrowanie. Szczegółowe informacje na temat filtrowania danych w tabeli przestawnej znajdziesz w kolejnym rozdziale tego samouczka.

Tabela przestawna programu Excel umożliwia eksplorowanie i wyodrębnianie istotnych danych z tabeli programu Excel lub szeregu danych. Można to zrobić na kilka sposobów i możesz wybrać te, które najlepiej pasują do Twoich danych. Co więcej, podczas eksploracji danych możesz natychmiast wyświetlać różne kombinacje, zmieniając wybory w celu wybrania wartości danych.

Za pomocą tabeli przestawnej możesz wykonać następujące czynności -

Sortuj dane.
Filtruj dane.
Zagnieżdżaj pola tabeli przestawnej.
Rozwiń i zwiń pola.
Grupuj i rozgrupuj wartości pól.

Sortowanie i filtrowanie danych

Dane w tabeli przestawnej można sortować w kolejności rosnącej lub malejącej według wartości pól. Możesz również sortować według sum pośrednich od największych do najmniejszych lub od najmniejszych do największych wartości. Możesz także ustawić opcje sortowania. Dowiesz się tego szczegółowo w rozdziale - Sortowanie danych w tabeli przestawnej w tym samouczku.

Możesz filtrować dane w tabeli przestawnej, aby skupić się na określonych danych. W tabeli przestawnej dostępnych jest kilka opcji filtrowania, których nauczysz się w rozdziale - Filtrowanie danych w tabeli przestawnej w tym samouczku. Możesz użyć fragmentatorów do filtrowania, czego nauczysz się w rozdziale - Filtrowanie przy użyciu fragmentatorów w tym samouczku.

Zagnieżdżanie, rozwijanie i zwijanie pól

Możesz zagnieżdżać pola w tabeli przestawnej, aby wyświetlić hierarchię, jeśli ma to znaczenie dla danych. Dowiesz się tego z rozdziału - Zagnieżdżanie w tabeli przestawnej w tym samouczku.

Gdy masz zagnieżdżone pola w tabeli przestawnej, możesz rozwijać i zwijać wartości tych pól. Dowiesz się tego w rozdziale - Eksploracja danych za pomocą narzędzi tabeli przestawnej w tym samouczku.

Grupowanie i rozgrupowywanie wartości pól

Możesz grupować i rozgrupowywać określone wartości pola w tabeli przestawnej. Dowiesz się tego z rozdziału - Eksploracja danych za pomocą narzędzi tabeli przestawnej w tym samouczku.

Możesz sortować dane w tabeli przestawnej, aby łatwo było znaleźć elementy, które chcesz przeanalizować. Możesz sortować dane od najniższych do najwyższych wartości lub od najwyższych do najniższych wartości lub w dowolnej innej wybranej przez siebie kolejności.

Weź pod uwagę następującą tabelę przestawną, w której masz podsumowane dane sprzedaży z podziałem na regiony, sprzedawców i miesiące.

Sortowanie według pól

Możesz sortować dane w powyższej tabeli przestawnej według pól znajdujących się w wierszach lub kolumnach - region, sprzedawca i miesiąc.

Aby posortować tabelę przestawną z polem Sprzedawca, wykonaj następujące czynności -

Kliknij strzałkę

w obszarze Etykiety wierszy.
Wybierz pozycję Sprzedawca w polu Wybierz pole z listy rozwijanej.

Wyświetlane są następujące opcje sortowania -

Sortuj od A do Z.
Sortuj od Z do A.
Więcej opcji sortowania.

Ponadto pole Sprzedawca jest domyślnie sortowane w kolejności rosnącej. KliknijSort Z to A. Pole Sprzedawca zostanie posortowane w kolejności malejącej.

W ten sam sposób możesz posortować pole w kolumnie - Miesiąc, klikając strzałkę

w etykietach kolumn.

Sortowanie według sum pośrednich

Załóżmy, że chcesz posortować tabelę przestawną na podstawie łącznej kwoty zamówienia - od najwyższej do najniższej w każdym regionie. Oznacza to, że chcesz posortować tabelę przestawną według sum pośrednich.

Jak widać, nie ma strzałki

oznaczającej sumy częściowe. Nadal możesz sortować tabelę przestawną według sum pośrednich w następujący sposób -

Kliknij prawym przyciskiem myszy sumę częściową dowolnego sprzedawcy w kolumnie Suma całkowita.
Wybierz Sort z listy rozwijanej.
Pojawi się kolejna lista rozwijana z opcjami sortowania - Sortuj od najmniejszego do największego, Sortuj od największego do najmniejszego i Więcej opcji sortowania. Wybierz Sortuj od największych do najmniejszych.

Sumy częściowe w kolumnie Suma całkowita są sortowane od wartości najwyższych do najniższych w każdym regionie.

Podobnie, jeśli chcesz posortować tabelę przestawną według regionu sum częściowych, wykonaj następujące czynności -

Kliknij prawym przyciskiem myszy sumę częściową dowolnego regionu w kolumnie Suma całkowita.
Kliknij Sortuj na liście rozwijanej.
Kliknij Sortuj od największego do najmniejszego na drugiej liście rozwijanej. Tabela przestawna zostanie posortowana według sum częściowych według regionu.

Jak widać, Południe ma najwyższą kwotę zamówienia, a Północ najniższą.

Możesz również posortować tabelę przestawną na podstawie łącznej kwoty według miesiąca w następujący sposób -

Kliknij prawym przyciskiem myszy dowolną sumę częściową w wierszu Suma całkowita.
Wybierz Sortuj z listy rozwijanej.
Wybierz Sortuj od największego do najmniejszego z drugiej listy rozwijanej.

Tabela przestawna zostanie posortowana według łącznej kwoty miesięcznie.

Możesz zauważyć, że luty ma najwyższą kwotę zamówienia, a marzec najniższą.

Więcej opcji sortowania

Załóżmy, że chcesz posortować tabelę przestawną według łącznej kwoty według regionu w styczniu.

Kliknij strzałkę

w obszarze Etykiety wierszy.
Wybierz Więcej opcji sortowania z listy rozwijanej. PlikSort (Region) pojawi się okno dialogowe.

Jak można zauważyć, w obszarze Podsumowanie bieżąca kolejność sortowania jest podana jako Region sortowania w porządku rosnącym. Rosnąco (od A do Z) według jest zaznaczone w obszarze Opcje sortowania. W polu poniżejRegion jest wyświetlany.

Kliknij pole zawierające Region.
Kliknij opcję Suma kwoty zamówienia.

Kliknij More Optionsprzycisk. PlikMore Sort Options (Region) pojawi się okno dialogowe.

Jak widać, w sekcji Sortuj według wybrano opcję Suma całkowita. W sekcji Podsumowanie bieżąca kolejność sortowania jest podana jakoSort Region by Sum of Order Amount w porządku rosnącym.

Kliknij Values in selected column: w sekcji Sortuj według.
W polu poniżej wpisz B5.

Jak można zauważyć, w obszarze Podsumowanie aktualna kolejność sortowania jest następująca -

Sortuj region według Sum of Order Amountw porządku rosnącym z użyciem wartości w tej kolumnie: styczeń. Kliknij OK.
Pojawi się okno dialogowe Sort (Region). Wybierz Malejąco (od Z do A) według: w obszarze Opcje sortowania.

W sekcji Podsumowanie bieżąca kolejność sortowania jest podana w następujący sposób -

Sortuj Region według sumy kwoty zamówienia w porządku malejącym, używając wartości w tej kolumnie: Styczeń. Kliknij OK. Tabela przestawna zostanie posortowana według regionu przy użyciu wartości w styczniu.

Jak widać, w styczniu Zachód ma najwyższą kwotę zamówienia, a Północ najniższą.

Ręczne sortowanie danych

W tabeli przestawnej dane są sortowane automatycznie według wybranej opcji sortowania. Nazywa się to automatycznym sortowaniem.

Umieść kursor na strzałce

w obszarze Etykiety wierszy lub Etykiety kolumn.

Pojawi się Autosortowanie, pokazując bieżącą kolejność sortowania dla każdego pola w tabeli przestawnej. Teraz przypuśćmy, że chcesz posortować pole Region w kolejności - wschód, zachód, północ i południe. Możesz to zrobić ręcznie w następujący sposób -

Kliknij strzałkę

w obszarze Etykiety wierszy.
Wybierz Region w polu Wybierz pole z listy rozwijanej.
Kliknij More Sort Options. Pojawi się okno dialogowe Sort (Region).
Wybierz opcję Ręcznie (możesz przeciągać elementy, aby zmienić ich kolejność).
Kliknij OK.

W sekcji Podsumowanie bieżąca kolejność sortowania jest podana jako Przeciągnij elementy pola Region, aby wyświetlić je w dowolnej kolejności.

Kliknij wschód i przeciągnij go na górę. Podczas przeciągania w kierunku wschodnim w całym wierszu przesuwa się poziomy zielony pasek.

Powtarzaj przeciąganie z innymi elementami pola Region, aż uzyskasz wymagany układ.

Możesz zaobserwować następujące -

Elementy zagnieżdżonego pola - Sprzedawca również są przenoszone wraz z odpowiednim elementem pola Region. Ponadto wartości w innych kolumnach również odpowiednio się zmieniły.
Jeśli

umieścisz kursor na strzałce w obszarze Etykiety wierszy lub Etykiety kolumn, pojawi się Autosortowanie pokazujące bieżącą kolejność sortowania pól Sprzedawca i Tylko miesiąc. Ponieważ pole Region zostało posortowane ręcznie, nie pojawi się ono w autosortowaniu.

Note- Nie można użyć tego ręcznego przeciągania elementów pola, które znajduje się w obszarze ∑ WARTOŚCI listy Pola tabeli przestawnej. Dlatego nie można przeciągać wartości Suma kwoty zamówienia w tej tabeli przestawnej.

Ustawianie opcji sortowania

W poprzedniej sekcji dowiedziałeś się, jak ustawić opcję sortowania dla pola na ręczne. Masz więcej opcji sortowania, które możesz ustawić w następujący sposób -

Kliknij strzałkę

w obszarze Etykiety wierszy.
Wybierz Region w polu Wybierz pole.
Kliknij Więcej opcji sortowania. Pojawi się okno dialogowe Sort (Region).
Kliknij przycisk Więcej opcji.

Zostanie wyświetlone okno dialogowe Więcej opcji sortowania (region). W tym oknie dialogowym można ustawić więcej opcji sortowania.

W sekcji Autosortowanie możesz zaznaczyć lub odznaczyć pole - Sortuj automatycznie po każdej aktualizacji raportu, aby zezwolić lub zatrzymać automatyczne sortowanie po każdej aktualizacji danych tabeli przestawnej.

Odznacz pole - Sort automatically za każdym razem, gdy raport jest aktualizowany.

Teraz dostępna staje się opcja sortowania według pierwszego klucza. Możesz użyć tej opcji, aby wybrać niestandardową kolejność, której chcesz użyć.

Kliknij pole poniżej kolejności sortowania według pierwszego klucza.

Jak można zauważyć, na liście rozwijanej znajdują się niestandardowe listy dni tygodnia i miesięcy roku. Możesz użyć dowolnego z nich lub możesz użyć własnej listy niestandardowej, takiej jak wysoka, średnia, niska lub lista rozmiarów S, M, L, XL, które nie są w kolejności alfabetycznej.

Możesz tworzyć własne listy na karcie PLIK na Wstążce. PLIK → Opcje. W oknie dialogowym Opcje programu Excel kliknij zaawansowane i przejdź do opcji Ogólne. Przycisk Edytuj listy niestandardowe znajduje się obok opcji Utwórz listy do użycia w sekwencjach sortowania i wypełniania.

Zwróć uwagę, że niestandardowa kolejność sortowania listy nie jest zachowywana podczas aktualizowania (odświeżania) danych w tabeli przestawnej.

W sekcji Sortuj według możesz kliknąć Grand Total or Valuesw wybranych kolumnach, aby posortować według tych wartości. Ta opcja nie jest dostępna, jeśli ustawisz sortowanie na Ręczne.

Punkty do rozważenia podczas sortowania tabel przestawnych

Podczas sortowania danych w tabeli przestawnej pamiętaj o następujących kwestiach -

Dane ze spacjami wiodącymi wpłyną na wyniki sortowania. Usuń wszystkie wiodące spacje przed posortowaniem danych.
Nie można sortować wpisów tekstowych uwzględniających wielkość liter.
Nie można sortować danych według określonego formatu, takiego jak kolor komórki lub czcionki.
Nie można sortować danych według wskaźników formatowania warunkowego, takich jak zestawy ikon.

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 ALL.
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ę

w komórce Etykiety wierszy lub Etykiety kolumn.

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ą Etykiet kolumn.

Kliknij strzałkę

w komórce Etykiety kolumn.

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ę,

aby wskazać, że filtr został zastosowany. Umieść kursor na

ikonie.

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ę,

aby wskazać, że zastosowano filtr. Umieść kursor na

ikonie na etykietach wierszy lub etykietach kolumn.

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

jest wyświetlany, wskazując:

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 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 dotyczy techników produkcji, a większość z nich jest 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ć, na osi czasu wyświetlane są wszystkie okresy - w miesiącach.

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

Może być konieczne od czasu do czasu 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.

Korzystanie z co najmniej jednego fragmentatora to szybki i skuteczny sposób filtrowania danych. Fragmentatory można wstawić dla każdego pola, które chcesz filtrować. Fragmentator będzie miał przyciski oznaczające wartości pola, które reprezentuje. Możesz kliknąć przyciski fragmentatora, aby zaznaczyć / odznaczyć wartości w polu.

Fragmentatory pozostają widoczne w tabeli przestawnej, dzięki czemu zawsze wiesz, które pola są używane do filtrowania i jakie wartości w tych polach są wyświetlane lub ukrywane w filtrowanej tabeli przestawnej.

Aby zrozumieć użycie fragmentatorów, rozważ przykład danych sprzedaży według regionu, miesiąca i sprzedawcy. Załóżmy, że masz następującą tabelę przestawną z tymi danymi.

Wstawianie fragmentatorów

Załóżmy, że chcesz filtrować tę tabelę przestawną na podstawie pól - Region i Miesiąc.

Kliknij ANALIZUJ w sekcji NARZĘDZIA PRZESTAWNE na Wstążce.
Kliknij Wstaw fragmentator w grupie Filtr. Zostanie wyświetlone okno dialogowe Wstaw fragmentatory. Zawiera wszystkie pola z Twojej tabeli danych.
Zaznacz pola Region i Miesiąc.
Kliknij OK.

Fragmentatory dla każdego z wybranych pól są wyświetlane ze wszystkimi wartościami wybranymi domyślnie. Na Wstążce pojawiają się narzędzia fragmentatora, które umożliwiają pracę z ustawieniami, wyglądem i stylem fragmentatora.

Filtrowanie za pomocą fragmentatorów

Jak widać, każdy fragmentator ma wszystkie wartości pola, które reprezentuje, a wartości są wyświetlane jako przyciski. Domyślnie wszystkie wartości pola są zaznaczone, a zatem wszystkie przyciski są podświetlone.

Załóżmy, że chcesz wyświetlić tabelę przestawną tylko dla regionów południe i zachód oraz dla miesięcy luty i marzec.

Kliknij South w Slicer for Region. W sekcji Slicer - Region podświetlona zostanie tylko południe.
Przytrzymaj wciśnięty klawisz Ctrl i kliknij Zachód we fragmentatorze dla regionu.
Kliknij Luty we fragmentatorze miesiąca.
Przytrzymaj klawisz Ctrl i kliknij March w Slicer for Month.

Wybrane elementy we fragmentatorach są podświetlone. Zostanie wyświetlona tabela przestawna z podsumowanymi wartościami dla wybranych elementów.

Aby dodać / usunąć wartości pola z filtru, przytrzymaj klawisz Ctrl i kliknij te przyciski we fragmentatorze pola.

Czyszczenie filtra we fragmentatorze

Aby wyczyścić filtr we fragmentatorze, kliknij

w prawym górnym rogu fragmentatora.

Usuwanie fragmentatora

Załóżmy, że chcesz usunąć fragmentator dla pola Region.

Kliknij prawym przyciskiem myszy Slicer - Region.
Kliknij Usuń „Region” na liście rozwijanej.

Narzędzia do krajalnic

Po wstawieniu fragmentatora narzędzia fragmentatora pojawią się na Wstążce z kartą OPCJE. Aby wyświetlić narzędzia fragmentatora, kliknij fragmentator.

Jak widać, na karcie Narzędzia fragmentatora - OPCJA masz kilka opcji zmiany wyglądu i sposobu działania fragmentatora, które obejmują -

Podpis fragmentatora
Ustawienia fragmentatora
Zgłoś połączenia
Okienko wyboru

Podpis fragmentatora

Pole Tytuł fragmentatora można znaleźć w grupie Fragmentator. Podpis fragmentatora to nagłówek wyświetlany na fragmentatorze. Domyślnie,Slicer Caption to nazwa pola, które reprezentuje.

Kliknij fragmentator dla regionu.
Kliknij kartę OPCJE na Wstążce.

Grupa fragmentatora na Wstążce w polu Tytuł fragmentatora region jest wyświetlany jako nagłówek fragmentatora. Jest to nazwa pola, dla którego wstawiany jest fragmentator. Możesz zmienić podpis fragmentatora w następujący sposób -

Kliknij pole Tytuł fragmentatora w grupie Fragmentator na Wstążce.
Usuń region. Pole jest wyczyszczone.
Wpisz lokalizację w polu i naciśnij Enter. Podpis fragmentatora zmieni się na lokalizację i to samo zostanie odzwierciedlone jako nagłówek we fragmentatorze.

Note- Zmieniłeś tylko podpis fragmentatora, czyli nagłówek. Nazwa pola, które reprezentuje fragmentator - Region pozostaje bez zmian.

Ustawienia fragmentatora

Możesz użyć ustawień fragmentatora, aby zmienić nazwę fragmentatora, zmienić podpis fragmentatora, wybrać, czy wyświetlać nagłówek fragmentatora, czy nie, oraz ustawić opcje sortowania i filtrowania dla elementów -

Kliknij fragmentator - Lokalizacja.
Kliknij kartę OPCJE na Wstążce. Ustawienia fragmentatora można znaleźć w grupie fragmentator na Wstążce. Możesz również znaleźć ustawienia fragmentatora na liście rozwijanej po kliknięciu fragmentatora prawym przyciskiem myszy.
Kliknij Ustawienia fragmentatora. Zostanie wyświetlone okno dialogowe Ustawienia fragmentatora.

Jak widać, dla krajalnicy ustalono:

Nazwa źródła.
Nazwa do użycia w formułach.

Możesz zmienić następujące elementy dla krajalnicy -

Name.
Nagłówek - podpis.
Wyświetl nagłówek.
Opcje sortowania i filtrowania dla elementów wyświetlanych we fragmentatorze.

Zgłoś połączenia

Możesz podłączyć różne tabele przestawne do fragmentatora, pod warunkiem, że zachodzi jedna z poniższych sytuacji -

Tabele przestawne są tworzone przy użyciu tych samych danych.
Jedna tabela przestawna została skopiowana i wklejona jako dodatkowa tabela przestawna.
Wiele tabel przestawnych jest tworzonych na oddzielnych arkuszach za pomocą opcji Pokaż strony filtrów raportów.

Rozważmy następujące tabele przestawne utworzone na podstawie tych samych danych -

Nazwij górną tabelę przestawną jako góra tabeli przestawnej, a dolną jako tabelę przestawną na dole.
Kliknij górną tabelę przestawną.
Wstaw fragmentator dla pola Region.
Wybierz wschód i północ na krajarce.

Zwróć uwagę, że filtrowanie jest stosowane tylko do górnej tabeli przestawnej, a nie do dolnej tabeli przestawnej. Możesz użyć tego samego fragmentatora dla obu tabel przestawnych, łącząc go z dolną tabelą przestawną również w następujący sposób -

Kliknij fragmentator - Region. Na Wstążce pojawią się narzędzia SLICER TOOLS.
Kliknij kartę OPCJE na Wstążce.

Połączenia raportów znajdziesz w grupie Fragmentator na Wstążce. Połączenia raportów można również znaleźć na liście rozwijanej po kliknięciu fragmentatora prawym przyciskiem myszy.

Kliknij Report Connections w grupie Fragmentator.

Plik Report Connectionspojawi się okno dialogowe. Pole Góra tabeli przestawnej jest zaznaczone, a inne pola nie są zaznaczone. Zaznacz pole PivotTable-Bottom również i kliknij OK.

Dolna tabela przestawna zostanie przefiltrowana do wybranych elementów - wschodu i północy.

Stało się to możliwe, ponieważ obie tabele przestawne są teraz połączone z fragmentatorem. Jeśli wprowadzisz zmiany w zaznaczeniach we fragmentatorze, to samo filtrowanie pojawi się w obu tabelach przestawnych.

Okienko wyboru

Możesz włączać i wyłączać wyświetlanie fragmentatorów w arkuszu za pomocą okienka zaznaczenia.

Kliknij fragmentator - Lokalizacja.
Kliknij kartę OPCJE na Wstążce.
Kliknij okienko zaznaczenia w grupie Rozmieść na Wstążce. Okienko zaznaczenia pojawi się po prawej stronie okna.

Jak widać, nazwy wszystkich fragmentatorów są wymienione w okienku zaznaczenia. Po prawej stronie nazw można znaleźć symbol widoczności -

wskazujący, że fragmentator jest widoczny w arkuszu.

Kliknij

symbol miesiąca. Symbol

zmieni się w symbol

, wskazując, że fragmentator jest ukryty (niewidoczny).

Jak widać, fragmentator - Miesiąc nie jest wyświetlany w arkuszu. Pamiętaj jednak, że nie usunąłeś fragmentatora na miesiąc, ale właśnie go ukryłeś.

Kliknij

symbol miesiąca.
Symbol

zmieni się w symbol

, wskazując, że fragmentator jest teraz widoczny.

Po włączeniu / wyłączeniu widoczności fragmentatora wybór elementów w tym fragmentatorze do filtrowania pozostaje niezmieniony. Możesz także zmienić kolejność fragmentatorów w okienku zaznaczenia, przeciągając je w górę / w dół.

Jeśli masz więcej niż jedno pole w dowolnym obszarze tabeli przestawnej, układ tabeli przestawnej zależy od kolejności umieszczania pól w tym obszarze. Nazywa się to porządkiem zagnieżdżania.

Jeśli wiesz, jaka jest struktura Twoich danych, możesz umieścić pola w wymaganej kolejności. Jeśli nie masz pewności co do struktury danych, możesz zmienić kolejność pól, co spowoduje natychmiastową zmianę układu tabeli przestawnej.

W tym rozdziale zrozumiesz kolejność zagnieżdżania pól oraz sposób zmiany kolejności zagnieżdżania.

Zagnieżdżanie kolejności pól

Rozważ przykład danych sprzedaży, w którym umieściłeś pola w następującej kolejności -

Jak widać, w obszarze wierszy znajdują się dwa pola - sprzedawca i region w tej kolejności. Taka kolejność pól nazywa się kolejnością zagnieżdżania, tj. Najpierw Sprzedawca, a następnie Region.

W tabeli przestawnej wartości w wierszach będą wyświetlane w tej kolejności, jak podano poniżej.

Jak widać, wartości drugiego pola w kolejności zagnieżdżenia są osadzone pod każdą z wartości pierwszego pola.

W Twoich danych każdy sprzedawca jest powiązany tylko z jednym regionem, podczas gdy większość regionów jest powiązana z więcej niż jednym sprzedawcą. Dlatego istnieje możliwość, że jeśli odwrócisz kolejność zagnieżdżenia, Twoja tabela przestawna będzie wyglądać bardziej sensownie.

Zmiana kolejności zagnieżdżania

Aby zmienić kolejność zagnieżdżania pól w obszarze, po prostu kliknij pole i przeciągnij je w żądane miejsce.

Kliknij pole Sprzedawca w obszarze WIERSZE i przeciągnij je poniżej pola Region. W ten sposób zmieniłeś kolejność zagnieżdżania na - Najpierw region, a następnie Sprzedawca, w następujący sposób -

Wynikowa tabela przestawna będzie taka, jak podano poniżej -

Widać wyraźnie, że Układ z kolejnością zagnieżdżenia - Region, a następnie Sprzedawca daje lepszy i zwarty raport niż ten z kolejnością zagnieżdżania - Sprzedawca, a następnie Region.

W przypadku, gdy sprzedawca reprezentuje więcej niż jeden obszar i musisz podsumować sprzedaż przez sprzedawcę, to poprzedni układ byłby lepszą opcją.

W arkuszu zawierającym tabelę przestawną wstążka będzie zawierać narzędzia tabeli przestawnej z zakładkami ANALIZA i PROJEKT. Karta ANALIZA zawiera kilka poleceń, które pozwolą Ci eksplorować dane w tabeli przestawnej. Polecenia na karcie PROJEKT będą przydatne do tworzenia struktury tabeli przestawnej z różnymi opcjami raportu i stylami.

W tym rozdziale poznasz polecenia ANALIZA. Polecenia DESIGN poznasz w rozdziale - Raporty estetyczne z tabelami przestawnymi.

Polecenia ANALIZA

Polecenia na karcie Wstążka ANALIZA obejmują:

Rozszerzanie i zwijanie pola.
Grupowanie i rozgrupowywanie wartości pól.
Ustawienia aktywnego pola.
Opcje tabeli przestawnej.

Rozszerzanie i zwijanie pola

Jeśli masz zagnieżdżone pola w tabeli przestawnej, możesz rozwinąć i zwinąć pojedynczy element lub rozwinąć i zwinąć wszystkie elementy aktywnego pola.

Rozważ następującą tabelę przestawną, w której masz pole Sprzedawca zagnieżdżone w polu Region.

Kliknij

symbol po lewej stronie Wschodu. Element na wschód od pola Region zostanie zwinięty.

Jak widać, pozostałe elementy - północ, południe i zachód od pola Region nie są zwinięte. Jeśli chcesz zwinąć którykolwiek z nich, powtórz kroki, które wykonałeś dla Wschodu.

Kliknij

symbol po lewej stronie Wschodu. Element na wschód od pola Region zostanie rozwinięty.

Jeśli chcesz zwinąć wszystkie elementy pola naraz, wykonaj następujące czynności -

Kliknij dowolny element pola - Region.
Kliknij kartę ANALIZA na Wstążce.
Kliknij Zwiń pole w grupie Aktywne pole.

Wszystkie elementy pola Region zostaną zwinięte.

Jeśli chcesz jednocześnie rozwinąć wszystkie elementy pola, wykonaj następujące czynności -

Kliknij dowolny element pola - Region.
Kliknij kartę ANALIZA na Wstążce.
Kliknij opcję Rozwiń pole w grupie Aktywne pole.

Wszystkie elementy pola Region zostaną rozwinięte.

Grupowanie i rozgrupowywanie wartości pól

Możesz grupować i rozgrupowywać wartości pól, aby zdefiniować własne grupowanie. Na przykład możesz chcieć poznać dane łączące regiony wschodni i północny.

W tabeli przestawnej wybierz elementy Wschód i Północ w polu Region wraz z zagnieżdżonymi elementami pola Sprzedawca.
Kliknij kartę ANALIZA na Wstążce.
Kliknij opcję Wybór grupy w grupie - Grupa.

Elementy - Wschód i Północ zostaną zgrupowane pod nazwą Grupa1. Ponadto tworzone jest nowe południe, pod którym zagnieżdżone jest południe, i nowy zachód, pod którym zagnieżdżony jest zachód.

Możesz również zauważyć, że nowe pole - Region2 jest dodawane do listy Pola tabeli przestawnej, która pojawia się w obszarze WIERSZE.

Wybierz elementy Południe i Zachód pola Region2 w tabeli przestawnej wraz z zagnieżdżonymi elementami pola Region i Sprzedawca.
Kliknij kartę ANALIZA na Wstążce.
Kliknij opcję Wybór grupy w grupie - Grupa.

Elementy - na południe i zachód od pola Region zostaną zgrupowane pod nazwą Grupa2.

Aby rozgrupować grupę, wykonaj następujące czynności -

Kliknij nazwę grupy.
Kliknij kartę ANALIZA.
Kliknij Rozgrupuj w grupie - Grupa.

Grupowanie według pola daty

Weź pod uwagę następującą tabelę przestawną, w której masz dane pracowników podsumowane według Count of EmployeeID, według pracowników i według tytułu.

Załóżmy, że chcesz pogrupować te dane według pola HireDate, które jest polem Data, na lata i kwartały.

Kliknij pozycję Data w tabeli przestawnej.
Kliknij kartę ANALIZA na Wstążce.
Kliknij Pole grupy w grupie - Grupa.

Pojawi się okno dialogowe Grupowanie.

Ustaw daty dla - Rozpoczęcie o i Zakończenie o.
Wybierz kwartały i lata w polu pod pozycją Według. Aby zaznaczyć / odznaczyć wiele elementów, trzymaj wciśnięty klawisz Ctrl.
Kliknij OK.

Wartości pola HireDate zostaną pogrupowane w kwartały zagnieżdżone w latach.

Jeśli chcesz rozgrupować to grupowanie, możesz zrobić to, jak pokazano wcześniej, klikając Ungroup w grupie - Group on the Ribbon.

Ustawienia pola wartości aktywnej

Możesz ustawić opcje pola, klikając wartość tego pola. Rozważmy przykład danych sprzedażowych, których użyliśmy wcześniej w tym rozdziale.

Załóżmy, że chcesz ustawić opcje dla pola Region.

Kliknij na wschód. Na Wstążce, w grupie Aktywne pole, w polu Aktywne pole, zostanie wyświetlony region.
Kliknij Field Settings. Zostanie wyświetlone okno dialogowe Ustawienia pola.

Możesz ustawić swoje preferencje dla pola - Region.

Opcje tabeli przestawnej

Możesz ustawić opcje tabeli przestawnej zgodnie z własnymi preferencjami.

Kliknij tabelę przestawną.
Kliknij kartę ANALIZA.
Kliknij Opcje w grupie tabeli przestawnej.

Plik PivotTable Optionspojawi się okno dialogowe. Możesz ustawić swoje preferencje w oknie dialogowym.

Możesz podsumować tabelę przestawną, umieszczając pole w obszarze ∑ WARTOŚCI w okienku zadań Pola tabeli przestawnej. Domyślnie Excel przyjmuje podsumowanie jako sumę wartości pola w obszarze ∑ WARTOŚCI. Masz jednak inne typy obliczeń, takie jak Count, Average, Max, Min itp.

W tym rozdziale dowiesz się, jak ustawić typ obliczeń na podstawie tego, jak chcesz podsumować dane w tabeli przestawnej.

Suma

Weź pod uwagę następującą tabelę przestawną, w której masz podsumowane dane dotyczące sprzedaży w ujęciu regionalnym, handlowym i miesięcznym.

Jak można zauważyć, po przeciągnięciu pola Kwota zamówienia do obszaru ∑ WARTOŚCI jest ono wyświetlane jako Suma kwoty zamówienia, co oznacza, że obliczenie jest traktowane jako Suma. W tabeli przestawnej, w lewym górnym rogu, wyświetlana jest suma kwoty zamówienia. Ponadto kolumny Suma całkowita i Suma całkowita są wyświetlane dla sum pośrednich odpowiednio w wierszach i kolumnach.

Ustawienia pola wartości

Dzięki ustawieniom pola wartości możesz ustawić typ obliczeń w tabeli przestawnej. Możesz także zdecydować, w jaki sposób chcesz wyświetlać swoje wartości.

Kliknij Suma kwoty zamówienia w obszarze ∑ WARTOŚCI.
Wybierz Ustawienia pola wartości z listy rozwijanej.

Zostanie wyświetlone okno dialogowe Ustawienia pola wartości.

Nazwa źródła to pole, a Nazwa niestandardowa to Suma pola. Typ obliczenia to Suma. KliknijShow Values As patka.

W polu Pokaż wartości jako, No Calculationjest wyświetlany. KliknijShow Values Aspudełko. Możesz znaleźć kilka sposobów wyświetlania łącznych wartości.

% sumy całkowitej

Możesz wyświetlić wartości w tabeli przestawnej jako% sumy całkowitej.

W polu Nazwa niestandardowa wpisz% sumy całkowitej.
Kliknij pole Pokaż wartości jako.
Kliknij% sumy całkowitej na liście rozwijanej. Kliknij OK.

Tabela przestawna podsumowuje wartości jako% sumy całkowitej.

Jak widać, Suma kwoty zamówienia w lewym górnym rogu tabeli przestawnej oraz w obszarze ∑ WARTOŚCI w okienku Pola tabeli przestawnej zostaje zmieniona na nową nazwę niestandardową -% sumy całkowitej.

Kliknij nagłówek kolumny Suma całkowita.
Wpisz% sumy całkowitej na pasku formuły. Zarówno nagłówki kolumny, jak i wiersza zmienią się na% sumy całkowitej.

% sumy kolumny

Załóżmy, że chcesz podsumować wartości jako% sumy każdego miesiąca.

Kliknij Suma kwoty zamówienia w obszarze ∑ WARTOŚCI.
Wybierz Ustawienia pola wartości z listy rozwijanej. Zostanie wyświetlone okno dialogowe Ustawienia pola wartości.
W polu Nazwa niestandardowa wpisz% sumy miesiąca.
Kliknij pole Pokaż wartości jako.
Wybierz% sumy kolumny z listy rozwijanej.
Kliknij OK.

Tabela przestawna podsumowuje wartości jako% sumy kolumny. W kolumnach Miesiąc znajdziesz wartości jako% sumy określonego miesiąca.

Kliknij nagłówek kolumny Suma całkowita.
Wpisz% sumy w kolumnie na pasku formuły. Zarówno nagłówki kolumny, jak i wiersza zmienią się na% sumy kolumn.

% sumy wierszy

Możesz podsumować wartości jako% sum regionu i% sumy sprzedawcy, wybierając opcję% sumy wierszy w polu Pokaż wartości jako w oknie dialogowym Ustawienia pola wartości.

Liczyć

Załóżmy, że chcesz podsumować wartości według liczby kont według regionu, sprzedawcy i miesiąca.

Odznacz kwotę zamówienia.
Przeciągnij konto do obszaru ∑ WARTOŚCI. Suma kont zostanie wyświetlona w obszarze ∑ WARTOŚCI.
Kliknij Suma konta.
Wybierz Ustawienia pola wartości z listy rozwijanej. Zostanie wyświetlone okno dialogowe Ustawienia pola wartości.
W polu Podsumuj wartość pole według wybierz opcję Liczba. Nazwa niestandardowa zmieni się na Count of Account.
Kliknij OK.

Liczba kont zostanie wyświetlona, jak pokazano poniżej -

Średni

Załóżmy, że chcesz podsumować tabelę przestawną według średnich wartości zamówienia w regionie, sprzedawcy i miesiąca.

Odznacz konto.
Przeciągnij kwotę zamówienia do obszaru ∑ WARTOŚCI. Suma kwoty zamówienia zostanie wyświetlona w obszarze ∑ WARTOŚCI.
Kliknij Suma kwoty zamówienia.
Kliknij Ustawienia pola wartości na liście rozwijanej. Zostanie wyświetlone okno dialogowe Ustawienia pola wartości.
W polu Podsumuj wartość, kliknij opcję Średnia. Nazwa niestandardowa zmieni się na Średnia kwoty zamówienia.
Kliknij OK.

Średnia zostanie wyświetlona, jak pokazano poniżej -

Musisz ustawić format liczb wartości w tabeli przestawnej, aby była bardziej reprezentacyjna.

Kliknij opcję Średnia kwoty zamówienia w obszarze ∑ WARTOŚCI.
Kliknij Ustawienia pola wartości na liście rozwijanej. Zostanie wyświetlone okno dialogowe Ustawienia pola wartości.
Kliknij przycisk Format liczb.

Zostanie wyświetlone okno dialogowe Formatowanie komórek.

Kliknij Numer w kategorii.
Wpisz 2 w polu Miejsca dziesiętne i kliknij OK.

Wartości tabeli przestawnej zostaną sformatowane jako liczby z dwoma miejscami po przecinku.

Kliknij nagłówek kolumny Suma całkowita.
Wpisz Średnia kwota zamówienia na pasku formuły. Zarówno nagłówki kolumny, jak i wiersza zmienią się na średnią kwotę zamówienia.

Maks

Załóżmy, że chcesz podsumować tabelę przestawną według maksymalnych wartości Kwoty zamówienia z podziałem na regiony, sprzedawców i miesiące.

Kliknij Suma kwoty zamówienia.
Wybierz Ustawienia pola wartości z listy rozwijanej. Zostanie wyświetlone okno dialogowe Ustawienia pola wartości.
W polu Podsumuj wartość pole według kliknij opcję Maks. Nazwa niestandardowa zmieni się na maksymalną kwotę zamówienia.

W tabeli przestawnej zostaną wyświetlone wartości maksymalne według regionu, sprzedawcy i miesiąca.

Kliknij nagłówek kolumny Suma całkowita.
Wpisz maksymalną kwotę zamówienia na pasku formuły. Zarówno nagłówki kolumny, jak i wiersza zmienią się na maksymalną kwotę zamówienia.

Min

Załóżmy, że chcesz podsumować tabelę przestawną według minimalnych wartości kwoty zamówienia według regionu, sprzedawcy i miesiąca.

Kliknij Suma kwoty zamówienia.
Kliknij Ustawienia pola wartości na liście rozwijanej. Zostanie wyświetlone okno dialogowe Ustawienia pola wartości.
W polu Podsumuj wartość według pola kliknij Min. Nazwa niestandardowa zmieni się na Min. Kwoty zamówienia.

W tabeli przestawnej zostaną wyświetlone wartości minimalne według regionu, sprzedawcy i miesiąca.

Kliknij nagłówek kolumny Suma całkowita.
Wpisz Minimalną kwotę zamówienia na pasku formuły. Zarówno nagłówki kolumny, jak i wiersza zmienią się na minimalną kwotę zamówienia.

Dowiedziałeś się, jak podsumowywać dane za pomocą tabeli przestawnej. Dane, na których jest oparta tabela przestawna, mogą być aktualizowane okresowo lub w przypadku wystąpienia zdarzenia. Ponadto może być wymagana zmiana układu tabeli przestawnej dla różnych raportów.

W tym rozdziale poznasz różne sposoby aktualizowania układu i / lub odświeżania danych w tabeli przestawnej.

Aktualizowanie układu tabeli przestawnej

Możesz zdecydować, czy tabela przestawna ma być aktualizowana za każdym razem, gdy wprowadzasz zmiany w układzie, czy też ma być aktualizowana przez oddzielny wyzwalacz.

Jak się dowiedziałeś wcześniej, w okienku zadań Pola tabeli przestawnej w dolnej części znajduje się pole wyboru Odrocz aktualizację układu. Domyślnie nie jest zaznaczone, co oznacza, że układ tabeli przestawnej jest aktualizowany zaraz po wprowadzeniu zmian w obszarach tabeli przestawnej.

Sprawdź opcję - Defer Layout Update.

Przycisk UPDATE obok niego zostanie włączony. Jeśli dokonasz jakichkolwiek zmian w obszarach tabeli przestawnej, zmiany zostaną odzwierciedlone dopiero po kliknięciu przycisku AKTUALIZUJ.

Odświeżanie danych tabeli przestawnej

Gdy dane tabeli przestawnej zostaną zmienione w źródle, to samo można odzwierciedlić w tabeli przestawnej, odświeżając ją.

Kliknij tabelę przestawną.
Kliknij kartę ANALIZA na Wstążce.
Kliknij Odśwież w grupie Dane.

Istnieją różne opcje odświeżania danych na liście rozwijanej -

Refresh - Aby uzyskać najnowsze dane ze źródła podłączonego do aktywnej komórki.
Refresh All - Aby uzyskać najnowsze dane, odświeżając wszystkie źródła w skoroszycie.
Connection Properties - Aby ustawić właściwości odświeżania połączeń skoroszytu.

Zmiana danych źródłowych tabeli przestawnej

Możesz zmienić zakres danych źródłowych tabeli przestawnej. Na przykład można rozszerzyć dane źródłowe, aby obejmowały większą liczbę wierszy danych.

Jeśli jednak dane źródłowe zostały znacznie zmienione, na przykład mając więcej lub mniej kolumn, rozważ utworzenie nowej tabeli przestawnej.

Kliknij tabelę przestawną. PIVOTTABLE TOOLS pojawiają się na Wstążce.
Kliknij kartę ANALIZA.
Kliknij opcję Zmień źródło danych w grupie Dane.

Wybierz opcję Zmień źródło danych z listy rozwijanej.

Zostanie wyświetlone okno dialogowe Zmień źródło danych tabeli przestawnej, a bieżące źródło danych zostanie podświetlone.

Wybierz tabelę lub zakres, który chcesz uwzględnić w polu Tabela / zakres w obszarze Wybierz tabelę lub zakres. Kliknij OK.

Źródło danych tabeli przestawnej zostanie zmienione na wybraną tabelę / zakres danych.

Zmiana na zewnętrzne źródło danych

Jeśli chcesz zmienić źródło danych dla tabeli przestawnej, które jest zewnętrzne, najlepiej będzie utworzyć nową tabelę przestawną. Jeśli jednak lokalizacja zewnętrznego źródła danych zostanie zmieniona, na przykład nazwa bazy danych programu SQL Server jest taka sama, ale została przeniesiona na inny serwer lub baza danych programu Access została przeniesiona do innego udziału sieciowego, można zmienić Twoje obecne połączenie danych odzwierciedla to samo.

Kliknij tabelę przestawną.
Kliknij kartę ANALIZA na Wstążce.
Kliknij Change Data Sourcew grupie Dane. PlikChange PivotTable Data Source pojawi się okno dialogowe.
Kliknij Choose Connection przycisk.

Plik Existing Connections pojawi się okno dialogowe.

Wybierz wszystkie połączenia w polu Pokaż. Zostaną wyświetlone wszystkie połączenia w skoroszycie.
Kliknij Browse for More przycisk.

Plik Select Data Source pojawi się okno.

Kliknij przycisk Nowe źródło.
Przejdź przez kroki Kreatora połączenia danych.

Jeśli źródło danych znajduje się w innym skoroszycie programu Excel, wykonaj następujące czynności -

Kliknij pole Nazwa pliku.
Wybierz nazwę pliku skoroszytu.

Usuwanie tabeli przestawnej

Możesz usunąć tabelę przestawną w następujący sposób -

Kliknij tabelę przestawną.
Kliknij kartę ANALIZA na Wstążce.
Kliknij Wybierz w grupie Akcje.

Wybierz Entire PivotTablez listy rozwijanej. Zostanie wybrana cała tabela przestawna.

Naciśnij klawisz Delete. Tabela przestawna zostanie usunięta.

Jeśli tabela przestawna znajduje się w oddzielnym arkuszu, możesz również usunąć tabelę przestawną, usuwając cały arkusz.

Kliknij prawym przyciskiem myszy kartę arkusza i wybierz Delete z listy rozwijanej.

Cały arkusz wraz z tabelą przestawną zostanie usunięty.

Głównym zastosowaniem tabeli przestawnej jest raportowanie. Po utworzeniu tabeli przestawnej, zbadaniu danych poprzez rozmieszczenie i zmianę układu pól w jej wierszach i kolumnach, będziesz gotowy do prezentowania danych szerokiemu gronu odbiorców. Dzięki filtrom, różnym podsumowaniom, skupiając się na określonych danych, będziesz w stanie wygenerować kilka wymaganych raportów na podstawie jednej tabeli przestawnej.

Ponieważ raport w formie tabeli przestawnej jest interaktywny, podczas prezentacji można szybko wprowadzić niezbędne zmiany, aby wyróżnić określone wyniki, takie jak trendy danych, podsumowania danych itp. Możesz także udostępnić odbiorcom wizualne wskazówki, takie jak filtry raportów, fragmentatory, oś czasu, wykresy przestawne itp., Aby mogli wizualizować żądane szczegóły.

W tym rozdziale poznasz różne sposoby tworzenia atrakcyjnych raportów w formie tabeli przestawnej za pomocą wizualnych wskazówek, które umożliwiają szybką eksplorację danych.

Hierarchie

Dowiedziałeś się, jak zagnieżdżać pola w celu utworzenia hierarchii w rozdziale - Zagnieżdżanie w tabeli przestawnej w tym samouczku. Dowiedziałeś się również, jak grupować / rozgrupowywać dane w tabeli przestawnej w rozdziale - Korzystanie z narzędzi tabeli przestawnej. Podamy kilka przykładów, aby pokazać, jak tworzyć interaktywne raporty w formie tabeli przestawnej z hierarchiami.

Jeśli masz wbudowaną strukturę dla pól w swoich danych, na przykład rok-kwartał-miesiąc, zagnieżdżanie pól w celu utworzenia hierarchii umożliwi szybkie rozwijanie / zwijanie pól w celu wyświetlenia podsumowanych wartości na wymaganym poziomie.

Na przykład załóżmy, że masz dane sprzedaży za rok obrotowy 2015-16 dla regionów - Wschodu, Północy, Południa i Zachodu, jak podano poniżej.

Utwórz tabelę przestawną, jak pokazano poniżej.

Jak widać, jest to kompleksowy sposób raportowania danych przy użyciu zagnieżdżonych pól jako hierarchii. Jeśli chcesz wyświetlić wyniki tylko na poziomie kwartałów, możesz szybko zwinąć pole kwartał.

Załóżmy, że w danych masz pole Data, jak pokazano poniżej.

W takim przypadku możesz pogrupować dane według pola Data w następujący sposób -

Utwórz tabelę przestawną.

Jak widać, ta tabela przestawna nie jest wygodna do wyróżniania istotnych danych.

Pogrupuj pole tabeli przestawnej według daty. (Grupowanie nauczyłeś się w rozdziale - Eksploracja danych za pomocą narzędzi tabeli przestawnej w tym samouczku).
Umieść pole Sprzedawca w obszarze Filtry.
Przefiltruj etykiety kolumn do regionu wschodniego.

Filtr raportów

Załóżmy, że potrzebujesz raportu dla każdego sprzedawcy osobno. Możesz to zrobić w następujący sposób -

Upewnij się, że masz pole Sprzedawca w obszarze Filtry.
Kliknij tabelę przestawną.
Kliknij kartę ANALIZA na Wstążce.
Kliknij strzałkę obok opcji w grupie tabeli przestawnej.
Wybierz Pokaż strony filtrów raportów z listy rozwijanej.

Plik Show Report Filter Pagespojawi się okno dialogowe. Wybierz pole Sprzedawca i kliknij OK.

Tworzony jest oddzielny arkusz dla każdej wartości pola Sprzedawca z przefiltrowaną tabelą przestawną do tej wartości.

Arkusz zostanie nazwany na podstawie wartości pola, które jest widoczne na zakładce arkusza.

Krajalnice

Inną zaawansowaną funkcją, którą masz w tabelach przestawnych, jest fragmentator, którego można użyć do wizualnego filtrowania pól.

Kliknij tabelę przestawną.
Kliknij kartę ANALIZA.
Kliknij opcję Wstaw fragmentator w grupie Filtr.
Kliknij Order Date, Quarters and Yearsw oknie dialogowym Wstaw fragmentatory. Zostaną utworzone trzy fragmentatory - data zamówienia, kwartały i rok.
Dostosuj rozmiary fragmentatorów, dodając więcej kolumn dla przycisków na fragmentatorach.
Utwórz fragmentatory dla pól Sprzedawca i Region.
Wybierz style fragmentatora, aby pola dat były zgrupowane w jednym kolorze, a pozostałe dwa pola miały różne kolory.
Odznacz linie siatki.

Jak widać, masz nie tylko interaktywny raport, ale także atrakcyjny, łatwo zrozumiały.

Oś czasu w tabeli przestawnej

Jeśli masz pole Data w tabeli przestawnej, wstawienie osi czasu jest również opcją tworzenia estetycznego raportu.

Utwórz tabelę przestawną ze sprzedawcą w obszarze ROWS i Region w obszarze COLUMNS.
Wstaw oś czasu dla pola Data zamówienia.
Przefiltruj oś czasu, aby wyświetlić dane z 5 miesięcy, od listopada 2015 r. Do marca 2016 r.

PROJEKT Polecenia

Plik PIVOTTABLE TOOLS - DESIGN polecenia na Wstążce zapewniają opcje formatowania tabeli przestawnej, w tym następujące -

Layout
Opcje stylu tabeli przestawnej
Style tabeli przestawnej

Układ

Możesz mieć układ tabeli przestawnej na podstawie swoich preferencji dla następujących -

Subtotals
Sumy całkowite
Układ raportu
Puste wiersze

PivotTable Layout – Subtotals

Masz możliwość wyświetlenia Subtotalsalbo nie. Domyślnie sumy częściowe są wyświetlane u góry grupy.

Jak widać podświetlona grupa - Wschód, sumy pośrednie znajdują się na górze grupy. Możesz zmienić położenie sum pośrednich w następujący sposób -

Kliknij tabelę przestawną.
Kliknij kartę PROJEKT na Wstążce.
Kliknij opcję Sumy częściowe w grupie Opcje układu.
Kliknij opcję Pokaż wszystkie podsumy na dole grupy.

Sumy częściowe pojawią się teraz na dole każdej grupy.

Jeśli nie musisz zgłaszać sum pośrednich, możesz wybrać opcję - Nie pokazuj sum pośrednich.

Sumy całkowite

Możesz wybrać, czy chcesz wyświetlać sumy całkowite, czy nie. Masz cztery możliwe kombinacje -

Wyłączone dla wierszy i kolumn
Włączone dla wierszy i kolumn
Włączone tylko dla wierszy
Włączone tylko dla kolumn

Domyślnie jest to druga kombinacja - włączona dla wierszy i kolumn.

Układ raportu

Możesz wybrać jeden z kilku układów raportów, najlepiej pasujący do Twoich danych.

Kompaktowa forma.
Formularz konspektu.
Formie tabelarycznej.

Możesz także zdecydować, czy powtórzyć wszystkie etykiety pozycji, czy nie, w przypadku wielu wystąpień.

Domyślny układ raportu to forma kompaktowa, którą znasz.

Compact Form

Forma kompaktowa optymalizuje tabelę przestawną pod kątem czytelności. Pozostałe dwa formularze również wyświetlają nagłówki pól.

Kliknij Show w formie konspektu.

Kliknij Show w formie tabelarycznej.

Rozważmy następujący układ tabeli przestawnej, w którym pole Miesiąc jest zagnieżdżone w polu Region -

Jak widać, etykiety miesiąca są powtarzane i jest to ustawienie domyślne.

Kliknij opcję Nie powtarzaj etykiet elementów. Etykiety miesiąca zostaną wyświetlone tylko raz, a tabela przestawna wygląda na przejrzystą.

Puste wiersze

Aby bardziej wyróżnić raport w formie tabeli przestawnej, możesz wstawić pusty wiersz po każdym elemencie. Możesz usunąć te puste wiersze w dowolnym momencie.

Kliknij Insert Blank Line after Each Item.

Opcje stylu tabeli przestawnej

Dostępne są następujące opcje stylu tabeli przestawnej -

Nagłówki wierszy
Nagłówki kolumn
Wiersze naprzemienne
Kolumny naprzemienne

Domyślnie zaznaczone są pola dla nagłówków wierszy i nagłówków kolumn. Te opcje służą do wyświetlania specjalnego formatowania odpowiednio dla pierwszego wiersza i pierwszej kolumny. Sprawdź pudełkoBanded Rows.

Zaznacz pole Kolumny naprzemienne.

Style tabeli przestawnej

Możesz wybrać kilka stylów tabeli przestawnej. Wybierz ten, który pasuje do Twojego raportu. Na przykład, jeśli wybierzesz Pivot Style Dark 5, otrzymasz następujący styl dla tabeli przestawnej.

Formatowanie warunkowe w tabeli przestawnej

W komórkach tabeli przestawnej można ustawić formatowanie warunkowe według wartości.

PivotCharts

Wykresy przestawne dodają wizualny nacisk do raportów w formie tabeli przestawnej. Możesz wstawić wykres przestawny powiązany z danymi tabeli przestawnej w następujący sposób -