Jeśli czytasz ten samouczek, istnieje duża szansa, że słyszałeś (lub nawet używałeś) tabeli przestawnej programu Excel. To jedna z najpotężniejszych funkcji programu Excel (nie żartuję).
Najlepsze w korzystaniu z tabeli przestawnej jest to, że nawet jeśli nic nie wiesz w programie Excel, nadal możesz robić z nią niesamowite rzeczy, mając bardzo podstawową wiedzę na ten temat.
Zacznijmy.
Kliknij tutaj aby pobrać przykładowe dane i postępować dalej.
Co to jest stół przestawny i dlaczego powinno Cię to obchodzić?
Tabela przestawna to narzędzie w programie Microsoft Excel, które pozwala szybko podsumować ogromne zbiory danych (za pomocą kilku kliknięć).
Nawet jeśli jesteś absolutnie nowy w świecie programu Excel, możesz z łatwością korzystać z tabeli przestawnej. Tworzenie raportów jest tak proste, jak przeciąganie i upuszczanie nagłówków wierszy/kolumn.
Załóżmy, że masz zbiór danych, jak pokazano poniżej:
To są dane sprzedaży składające się z ~1000 wierszy.
Zawiera dane sprzedaży według regionu, typu sprzedawcy i klienta.
Teraz twój szef może chcieć dowiedzieć się kilku rzeczy na podstawie tych danych:
- Jaka była całkowita sprzedaż w regionie Południe w 2016 roku?
- Jakich jest pięciu największych sprzedawców detalicznych pod względem sprzedaży?
- Jak wypadł The Home Depot w porównaniu z innymi sprzedawcami na Południu?
Możesz śmiało używać funkcji programu Excel, aby uzyskać odpowiedzi na te pytania, ale co, jeśli nagle twój szef wymyśli listę pięciu dodatkowych pytań.
Będziesz musiał wracać do danych i tworzyć nowe formuły za każdym razem, gdy nastąpi zmiana.
Tutaj przydają się tabele przestawne programu Excel.
W ciągu kilku sekund tabela przestawna odpowie na wszystkie te pytania (jak dowiesz się poniżej).
Ale prawdziwą korzyścią jest to, że może dostosować się do wybrednego, opartego na danych szefa, natychmiast odpowiadając na jego pytania.
To takie proste, że równie dobrze możesz poświęcić kilka minut i pokazać szefowi, jak to zrobić sam.
Mamy nadzieję, że teraz wiesz, dlaczego tabele przestawne są tak niesamowite. Przejdźmy dalej i stwórzmy tabelę przestawną przy użyciu zestawu danych (pokazanego powyżej).
Wstawianie tabeli przestawnej w programie Excel
Oto kroki, aby utworzyć tabelę przestawną przy użyciu danych pokazanych powyżej:
- Kliknij w dowolnym miejscu w zbiorze danych.
- Przejdź do Wstaw -> Tabele -> Tabela przestawna.
- W oknie dialogowym Utwórz tabelę przestawną domyślne opcje działają dobrze w większości przypadków. Oto kilka rzeczy do sprawdzenia:
- Stół/zakres: Jest wypełniany domyślnie na podstawie zestawu danych. Jeśli Twoje dane nie zawierają pustych wierszy/kolumn, program Excel automatycznie zidentyfikuje właściwy zakres. W razie potrzeby możesz to zmienić ręcznie.
- Jeśli chcesz utworzyć tabelę przestawną w określonej lokalizacji, w opcji „Wybierz, gdzie chcesz umieścić raport w formie tabeli przestawnej”, określ lokalizację. W przeciwnym razie tworzony jest nowy arkusz z tabelą przestawną.
- Kliknij OK.
Po kliknięciu przycisku OK zostanie utworzony nowy arkusz roboczy z tabelą przestawną.
Po utworzeniu tabeli przestawnej nie zobaczysz w niej żadnych danych. Wszystko, co zobaczysz, to nazwa tabeli przestawnej i jednowierszowa instrukcja po lewej stronie oraz pola tabeli przestawnej po prawej stronie.
Teraz, zanim przejdziemy do analizy danych za pomocą tej tabeli przestawnej, zrozummy, jakie są nakrętki i śruby, które tworzą tabelę przestawną programu Excel.
Nakrętki i śruby tabeli przestawnej Excel
Aby efektywnie korzystać z tabeli przestawnej, ważne jest, aby znać komponenty, które tworzą tabelę przestawną.
W tej sekcji dowiesz się o:
- Pamięć podręczna przestawna
- Obszar wartości
- Obszar rzędów
- Obszar kolumn
- Obszar filtrów
Pamięć podręczna
Gdy tylko utworzysz tabelę przestawną przy użyciu danych, coś się dzieje w zapleczu. Excel wykonuje migawkę danych i przechowuje je w swojej pamięci. Ta migawka nosi nazwę Pivot Cache.
Podczas tworzenia różnych widoków przy użyciu tabeli przestawnej program Excel nie wraca do źródła danych, a raczej używa pamięci podręcznej przestawnej do szybkiej analizy danych i przedstawienia podsumowania/wyników.
Powodem generowania pamięci podręcznej przestawnej jest optymalizacja działania tabeli przestawnej. Nawet jeśli masz tysiące wierszy danych, tabela przestawna bardzo szybko podsumowuje dane. Możesz przeciągać i upuszczać elementy w polach wierszy/kolumn/wartości/filtrów i natychmiast zaktualizuje wyniki.
Uwaga: Jedną z wad pamięci podręcznej przestawnej jest to, że zwiększa rozmiar skoroszytu. Ponieważ jest to replika danych źródłowych, podczas tworzenia tabeli przestawnej kopia tych danych jest przechowywana w pamięci podręcznej przestawnej.
Czytaj więcej: Co to jest pamięć podręczna Pivot i jak najlepiej z niej korzystać.
Obszar wartości
Obszar wartości zawiera obliczenia/wartości.
W oparciu o zestaw danych pokazany na początku samouczka, jeśli chcesz szybko obliczyć łączną sprzedaż według regionu w każdym miesiącu, możesz uzyskać tabelę przestawną, jak pokazano poniżej (zobaczymy, jak to utworzyć w dalszej części samouczka) .
Obszar podświetlony na pomarańczowo to obszar wartości.
W tym przykładzie ma całkowitą sprzedaż w każdym miesiącu dla czterech regionów.
Obszar rzędów
Nagłówki po lewej stronie obszaru Wartości tworzą obszar Wiersze.
W poniższym przykładzie obszar Wiersze zawiera regiony (podświetlone na czerwono):
Obszar kolumn
Nagłówki u góry obszaru Wartości tworzą obszar Kolumny.
W poniższym przykładzie obszar Kolumny zawiera miesiące (podświetlone na czerwono):
Obszar filtrów
Obszar filtrów to opcjonalny filtr, którego można użyć do dalszego drążenia zestawu danych.
Na przykład, jeśli chcesz zobaczyć tylko sprzedaż dla sprzedawców z wieloma liniami, możesz wybrać tę opcję z listy rozwijanej (podświetlonej na poniższym obrazku), a tabela przestawna zostanie zaktualizowana danymi tylko dla sprzedawców z wieloma liniami.
Analizowanie danych za pomocą tabeli przestawnej
Teraz spróbujmy odpowiedzieć na pytania, korzystając z utworzonej przez nas tabeli przestawnej.
Kliknij tutaj aby pobrać przykładowe dane i postępować dalej.
Aby analizować dane za pomocą tabeli przestawnej, musisz zdecydować, jak ma wyglądać podsumowanie danych w wyniku końcowym. Na przykład możesz chcieć wszystkie regiony po lewej stronie i całkowitą sprzedaż tuż obok. Mając to na uwadze, możesz po prostu przeciągnąć i upuścić odpowiednie pola w tabeli przestawnej.
W sekcji Pola tabeli przestawnej znajdują się pola i obszary (wyróżnione poniżej):
Pola są tworzone na podstawie danych zaplecza używanych w tabeli przestawnej. Sekcja Obszary to miejsce, w którym umieszczasz pola, a w zależności od miejsca, w którym znajduje się pole, Twoje dane są aktualizowane w tabeli przestawnej.
Jest to prosty mechanizm przeciągnij i upuść, w którym możesz po prostu przeciągnąć pole i umieścić je w jednym z czterech obszarów. Gdy to zrobisz, pojawi się w tabeli przestawnej w arkuszu.
Teraz spróbujmy odpowiedzieć na pytania Twojego przełożonego za pomocą tej tabeli przestawnej.
P1: Jaka była całkowita sprzedaż w regionie południowym?
Przeciągnij pole Region w obszarze Wiersze i pole Przychód w obszarze Wartości. Automatycznie zaktualizuje tabelę przestawną w arkuszu.
Zwróć uwagę, że gdy tylko upuścisz pole Przychód w obszarze Wartości, stanie się ono Sumą przychodu. Domyślnie Excel sumuje wszystkie wartości dla danego regionu i pokazuje sumę. Jeśli chcesz, możesz zmienić to na Liczbę, Średnia lub inne dane statystyczne. W tym przypadku potrzebna jest suma.
Odpowiedź na to pytanie to 21225800.
Q2 Jakich jest pięciu największych sprzedawców detalicznych pod względem sprzedaży?
Przeciągnij pole Klient w obszarze Wiersz i pole Przychód w obszarze wartości. W przypadku, gdy w sekcji obszaru znajdują się inne pola i chcesz je usunąć, po prostu wybierz je i przeciągnij z niego.
Otrzymasz tabelę przestawną, jak pokazano poniżej:
Zwróć uwagę, że domyślnie pozycje (w tym przypadku klienci) są sortowane w kolejności alfabetycznej.
Aby uzyskać pięciu najlepszych sprzedawców, możesz po prostu posortować tę listę i użyć pięciu najlepszych nazw klientów. Aby to zrobić:
- Kliknij prawym przyciskiem myszy dowolną komórkę w obszarze Wartości.
- Przejdź do Sortuj -> Sortuj od największego do najmniejszego.
To da ci posortowaną listę na podstawie całkowitej sprzedaży.
P3: Jak wypada The Home Depot w porównaniu z innymi sprzedawcami na Południu?
Możesz przeprowadzić wiele analiz dla tego pytania, ale tutaj spróbujmy porównać sprzedaż.
Przeciągnij pole regionu w obszarze Wiersze. Teraz przeciągnij pole Klient w obszarze Wiersze poniżej pola Region. Gdy to zrobisz, program Excel zrozumie, że chcesz kategoryzować dane najpierw według regionu, a następnie klientów w regionach. Będziesz mieć coś, jak pokazano poniżej:
Teraz przeciągnij pole Revenue w obszarze Values, a uzyskasz sprzedaż dla każdego klienta (jak również dla całego regionu).
Możesz posortować sprzedawców na podstawie wyników sprzedaży, wykonując poniższe czynności:
- Kliknij prawym przyciskiem myszy komórkę, która zawiera wartość sprzedaży dla dowolnego sprzedawcy.
- Przejdź do Sortuj -> Sortuj od największego do najmniejszego.
To natychmiast posortuje wszystkich sprzedawców według wartości sprzedaży.
Teraz możesz szybko przejrzeć region południowy i stwierdzić, że sprzedaż The Home Depot wyniosła 3004600 i wypadła lepiej niż czterech detalistów w regionie południowym.
Obecnie istnieje więcej niż jeden sposób na oskórowanie kota. Możesz również umieścić Region w obszarze Filtr, a następnie wybrać tylko Region Południe.
Kliknij tutaj aby pobrać przykładowe dane.
Mam nadzieję, że ten samouczek zawiera podstawowe omówienie tabel przestawnych programu Excel i pomoże w rozpoczęciu pracy.
Oto kilka samouczków dotyczących tabel przestawnych, które mogą Ci się spodobać:
- Przygotowywanie danych źródłowych do tabeli przestawnej.
- Jak zastosować formatowanie warunkowe w tabeli przestawnej w programie Excel.
- Jak grupować daty w tabelach przestawnych w programie Excel.
- Jak grupować liczby w tabeli przestawnej w programie Excel.
- Jak filtrować dane w tabeli przestawnej w programie Excel.
- Korzystanie z fragmentatorów w tabeli przestawnej programu Excel.
- Jak zastąpić puste komórki zerami w tabelach przestawnych programu Excel.
- Jak dodać i używać pól obliczeniowych tabeli przestawnej programu Excel.
- Jak odświeżyć tabelę przestawną w programie Excel.
