Pulpity nawigacyjne programu Excel - tabele przestawne

Jeśli masz dane w jednej tabeli programu Excel, możesz podsumować dane w wymagany sposób, korzystając z tabel przestawnych programu Excel. Tabela przestawna to niezwykle potężne narzędzie, którego można używać do krojenia danych. Możesz śledzić, 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.

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ę to Power PivotTable, który działa na swojej bazie danych znanej jako model danych. Dowiesz się o Power PivotTable i innych elektronarzędziach Excel, takich jak Power PivotChart i Power View Reports w innych rozdziałach.

Tabele przestawne, tabele Power Pivot, wykresy Power Pivot i raporty programu Power View przydają się do wyświetlania podsumowanych wyników z zestawów danych big data na pulpicie nawigacyjnym. Możesz zdobyć mistrzostwo w normalnej tabeli przestawnej, zanim zapuścisz się do elektronarzędzi.

Tworzenie 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.

Możesz zacząć od pustej tabeli przestawnej i skonstruować ją od podstaw lub skorzystać z polecenia Excel Zalecane tabele przestawne, aby wyświetlić podgląd możliwych dostosowanych tabel przestawnych dla swoich danych i wybrać taką, która odpowiada Twoim potrzebom. W obu przypadkach możesz modyfikować tabelę przestawną w locie, aby uzyskać wgląd w różne aspekty dostępnych 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.

Pojawi się okno dialogowe Utwórz tabelę przestawną.

Jak widać, w oknie dialogowym Tworzenie tabeli przestawnej w obszarze Wybierz dane, które chcesz przeanalizować, możesz wybrać tabelę lub zakres z bieżącego skoroszytu lub użyć zewnętrznego źródła danych. W związku z tym możesz wykonać te same kroki, aby utworzyć tabelę przestawną jako zakres lub tabelę.

Kliknij Wybierz tabelę lub zakres.
W polu Tabela / zakres wpisz nazwę zakresu - SalesData_Range.
Kliknij Nowy arkusz w obszarze Wybierz, gdzie chcesz umieścić raport w formie tabeli przestawnej.

Możesz również zauważyć, że możesz przeanalizować wiele tabel, dodając ten zakres danych do modelu danych. Model danych to baza danych dodatku Power Pivot programu Excel.

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

Jak widać, lista pól tabeli przestawnej 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.

Musisz wybrać pola tabeli przestawnej na podstawie danych, które chcesz wyświetlić. Umieszczając pola w odpowiednich obszarach, można uzyskać żądany układ danych. Na przykład, aby podsumować kwotę zamówienia według sprzedawcy za miesiące - styczeń, luty i marzec, możesz wykonać następujące czynności -

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 tabela przestawna jest gotowa. Możesz zmienić układ tabeli przestawnej, po prostu przeciągając pola przez obszary. Możesz zaznaczyć / odznaczyć pola na liście Pola tabeli przestawnej, aby wybrać dane, które chcesz wyświetlić.

Filtrowanie danych w tabeli przestawnej

Jeśli musisz skoncentrować się na podzbiorze danych tabeli przestawnej, możesz filtrować dane w tabeli przestawnej na podstawie podzbioru wartości co najmniej jednego pola. Na przykład w powyższym przykładzie można filtrować dane na podstawie pola Zakres, aby można było wyświetlić dane tylko dla wybranych regionów.

Istnieje kilka sposobów filtrowania danych w tabeli przestawnej -

Filtrowanie za pomocą filtrów raportów.
Filtrowanie przy użyciu fragmentatoró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.

Dowiesz się, jak korzystać z filtrów raportów w tej sekcji i fragmentatorów w następnej sekcji. Aby poznać inne opcje filtrowania, zapoznaj się z samouczkiem dotyczącym tabel przestawnych programu Excel.

Możesz przypisać filtr do jednego z pól, aby móc dynamicznie zmieniać tabelę przestawną na podstawie wartości tego pola.

Przeciągnij pole Region do obszaru FILTRY.
Przeciągnij pole Sprzedawca do obszaru WIERSZE.
Przeciągnij pole Miesiąc do obszaru KOLUMNY.
Przeciągnij pole Kwota zamówienia do obszaru ∑ WARTOŚCI.

Filtr z etykietą Region pojawia się nad tabelą przestawną (jeśli nie masz pustych wierszy nad tabelą przestawną, tabela przestawna jest wypychana w dół, aby zrobić miejsce na filtr).

Jak widać,

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 obszarze Filtr regionu.

Pojawi się lista rozwijana z wartościami pola Region.

Zaznacz pole Wybierz wiele elementów. Pola wyboru pojawią się dla wszystkich wartości. Domyślnie wszystkie pola są zaznaczone.
Odznacz pole (All). Wszystkie pola zostaną odznaczone.
Zaznacz pola - południe i zachód.

Kliknij przycisk OK. Dane dotyczące tylko regionów południowych i zachodnich zostaną podsumowane.

Jak widać, w komórce obok filtru regionu wyświetlany jest komunikat (Wiele elementów), co oznacza, że wybrano więcej niż jedną wartość. Ale ile wartości i / lub które wartości nie są znane z wyświetlanego raportu. W takim przypadku użycie fragmentatorów jest lepszą opcją do filtrowania.

Używanie fragmentatorów w tabeli przestawnej

Filtrowanie przy użyciu fragmentatorów ma wiele zalet -

Możesz mieć wiele filtrów, wybierając pola dla fragmentatorów.
Możesz wizualizować pola, do których zastosowano filtr (jeden fragmentator na pole).
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.
Możesz zwizualizować, jakie wartości pola są używane w filtrze (wybrane przyciski są podświetlone we fragmentatorze).
Możesz użyć wspólnego fragmentatora dla wielu tabel przestawnych i / lub wykresów przestawnych.
Możesz ukryć / odkryć krajalnicę.

Aby zrozumieć użycie fragmentatorów, rozważ poniższą tabelę przestawną.

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

Kliknij kartę ANALIZA w obszarze NARZĘDZIA PRZESTAWNE na Wstążce.
Kliknij Wstaw fragmentator w grupie Filtr.

Pojawi się okno dialogowe Wstaw fragmentatory. Zawiera wszystkie pola z Twoich danych.

Zaznacz pola Region i Miesiąc.

Kliknij przycisk 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.

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łudniowych i zachodnich oraz dla miesięcy lutego i marca.

Kliknij Południe w Region Slicer. W sekcji Slicer - Region podświetlona zostanie tylko południe.
Przytrzymaj wciśnięty klawisz Ctrl i kliknij West w Region Slicer.
Kliknij Luty we fragmentatorze miesiąca.
Przytrzymaj wciśnięty klawisz Ctrl i kliknij March w sekcji Month Slicer. Wybrane wartości we fragmentatorach są podświetlone. Tabela przestawna zostanie podsumowana dla wybranych wartości.

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