Excel tabele przestawne
Tabele przestawne w Excelu ułatwiają raportowanie. Dzięki nim możesz prezentować te same dane w różnych ujęciach i z różnymi rodzajami podsumowań.
Dlaczego warto znać tabele przestawne?
Tabela przestawna w Excelu umożliwia szybkie pofdsumowywanie dużych ilości danych. Jest interaktywna – dzięki temu możesz przedstawiać dane na wiele różnych sposobów i prezentować je z różnym poziomem szczegółowości.
Tabele przestawne:
- nie są trudne w użyciu – w najprostszej formie możesz podsumować dane, przeciągając je do różnych sekcji tabeli.
- umożliwiają szybką analizę danych – możesz pobrać duże ilości danych z różnych źródeł (MS Excel, MS SQL Server, MS Access), rozmieszczać je zgodnie ze swoimi potrzebami, etykietować, sortować i analizować w dowolny sposób.
- pozwalają na łatwe znalezienie wzorca danych – umożliwiają manipulowanie danymi w sposób, który pomoże znaleźć powtarzające się wzorce w danych.
- umożliwiają szybkie tworzenie raportów – pomagają tworzyć raporty w efektywny sposób, wystarczy przeciągnięcie danych do odpowiednich obszarów, aby zrobić podsumowanie.
Poniżej zobaczysz jak wstawić i obsługiwać tabelę przestawną. Znajdziesz też kilka wskazówek jak:
- nie stracić danych poprzez nadpisanie ich tabelą
- poradzić sobie, gdy np. „znikną” pola tabeli przestawnej
Excel tabele przestawne – sposoby prezentacji danych
Fragment danych źródłowych, których użyję do stworzenia raportu w formie tabeli przestawnej:
Forma przedstawienia informacji w tabeli przestawnej zależy od Ciebie. Poniżej trzy przykładowe sposoby prezentacji danych:
1. Podsumowanie danych per kraj a następnie per miesiąc w ramach każdego kraju.
2. Podsumowanie danych per miesiąc a następnie per kraj w ramach każdego miesiąca.
3. Podsumowanie danych per miesiąc i kraj.
Wstawianie tabeli przestawnej
Aby wstawić tabelę przestawną:
- kliknij w dowolnym miejscu na dane, które chcesz zaprezentować w tabeli. Wówczas Excel zaznaczy cały obszar danych, które do siebie przylegają.
- kliknij na Wstążce kartę Wstawianie a następnie polecenie Tabela przestawna.
Uwaga! W zaznaczonym obszarze danych nie powinno być pustych wierszy, pustych kolumn i brakujących nagłówków kolumn.
Opcja Zaznacz tabelę lub zakres w oknie dialogowym Tworzenie tabeli przestawnej umożliwia zaznaczenie zakresu danych, które mają stanowić źródło dla tabeli przestawnej. Jeśli przed wstawianiem tabeli klikniesz w dowolnym miejscu zakresu danych, podczas wstawiania tabeli, Excel automatycznie zaznaczy cały zakres przylegających do siebie komórek z danymi. Informacja o zaznaczonym zakresie podana jest w oknie Tabela/zakres.
Opcja Wybierz, gdzie chcesz umieścić raport w formie tabeli przestawnej – umożliwia wskazanie miejsca, w którym tabela przestawna zostanie wstawiona. Domyślnie Excel wstawia tabelę do nowego arkusza. Umieszcza go przed arkuszem z danymi zaznaczonymi jako źródło dla tabeli przestawnej.
Jeśli chcesz, aby tabela wstawiona została do wybranego przez Ciebie miejsca, w oknie Tworzenie tabeli przestawnej wskaż adres komórki, do której ma zostać wstawiona tabela np. tab_przest!$G$131. Wówczas początek tabeli przestawnej będzie w komórce G131.
Uwaga! Przed wstawieniem tabeli upewnij się, że jest na nią miejsce. Jeśli tabela przestawna będzie nachodziła na istniejące już w arkuszu dane, pojawi się poniższe ostrzeżenie.
Jeśli klikniesz przycisk OK, istniejące w arkuszu dane zostaną zastąpione obszarem tabeli przestawnej. Poniżej przykład takiej sytuacji.
Nawet jeśli uzupełnisz dane tabeli i jej rozmiar się zmniejszy nie odzyskasz wartości, które zostały nadpisane przez obszar tabeli przestawnej. Poniżej przykład.
Pola tabeli przestawnej
Po utworzeniu tabeli, kliknij na jej obszar. Wówczas pojawi się okno Pola tabeli przestawnej zawierające pola ze źródła danych, które zostały zaznaczone podczas wstawiania tabeli.
Uwaga! jeśli zamkniesz pola tabeli przestawnej, możesz je łatwo przywrócić. Aby to zrobić kliknij na tabelę przestawną prawym przyciskiem myszy. Pojawi się wówczas Menu podręczne zawierające najczęściej używane narzędzia tabeli przestawnej. Kliknij na pasku narzędzi polecenie Pokaż listę pól.
Pola możesz odpowiednio przeciągnąć między obszarami tabeli do:
- Filtra
- Kolumn
- Wierszy
- Wartości
Jeśli chcesz zmienić sposób prezentacji obszarów w polach tabeli przestawnej, kliknij narzędzia i wybierz odpowiadający Ci układ.
Karty kontekstowe tabeli przestawnej
Po utworzeniu tabeli przestawnej na Wstążce pojawiają się dwie karty kontekstowe:
- Projektowanie
- Analiza (Analiza tabeli przestawnej)
Zawierają one narzędzia do formatowania tabeli i dokonywania analizy przedstawionych w niej danych.
Karty są aktywne tylko wtedy, gdy aktywna jest tabela przestawna. Aby je wyświetlić, kliknij dowolne miejsce w tabeli przestawnej.
Tabela przestawna – narzędzia karty Projektowanie
Karta Projektowanie zawiera narzędzia, za pomocą których możesz sformatować wygląd tabeli, zmienić układ raportu i wstawić podsumowania.
Style tabeli przestawnej
1 – Style tabeli przestawnej – zawiera zestaw domyślnych stylów, za pomocą których możesz sformatować tabelę przestawną.
Po kliknięciu przycisku Więcej wyświetlisz zdefiniowane style tabel przestawnych.
Opcja Nowy styl tabeli przestawnej umożliwia stworzenie własnego stylu. Możesz go zapisać w stylach tabeli przestawnej i ustawić jako styl domyślny.
Aby użyć swojego stylu, klikamy przycisk Więcej. Nowe style widoczne są w części Niestandardowe.
Aby usunąć własny styl, klikamy styl prawym przyciskiem myszy i wybieramy opcję Usuń.
Opcja Wyczyść – za jej pomocą czyścimy styl z tabeli przestawnej.
2 – Opcje stylu tabeli przestawnej – grupa narzędzi, za pomocą której możesz sterować stylami tabeli np. zaznaczyć kolorem kolumny lub wiersze.
Opcje stylu tabeli przestawnej
Za pomocą opcji stylu możesz formatować następujące elementy tabeli:
- nagłówki wierszy – wyświetlenie specjalnego formatowania dla pierwszego wiersza tabeli
- nagłówki kolumn – wyświetlenie specjalnego formatowania dla pierwszej kolumny tabeli
- wiersze naprzemienne – odmienne formatowanie dla wierszy parzystych i nieparzystych
- kolumny naprzemienne – odmienne formatowanie dla kolumn parzystych i nieparzystych
Układ tabeli przestawnej
3 – Układ – grupa narzędzi, za pomocą której możesz sterować układem raportu np. wyświetlić raport w formie tabelarycznej, powtórzyć etykiety elementów, oddzielić grupy danych w tabeli pustym wierszem i wstawić podsumowania.
Wstaw pusty wiersz – za pomocą tej opcji poszczególne grupy danych w tabeli możesz oddzielić pustym wierszem. W tabelach zawierających dużą ilość danych, wstawienie pustego wiersza poprawi czytelność raportu.
Układ raportu – za pomocą tej opcji możesz zmieniać sposób wyświetlania raportu tabeli.
Forma kompaktowa – domyślna forma raportu. Służy do wyświetlania elementów pochodzących z różnych pól obszaru wierszy w jednej kolumnie. Elementy z różnych pól odróżniamy za pomocą wcięcia.
Etykiety wierszy:
- każda etykieta wiersza znajduje się w osobnym wierszu
- etykieta pola wiersza znajduje się zawsze powyżej etykiet pól wewnętrznych
- każda etykieta wiersza jest lekko wcięta względem pola nad nią
- pola etykiety wierszy nie mogą się powtarzać
Pola wierszy:
- wszystkie pola wierszy znajdują się w jednej kolumnie
- w kolumnie pola wiersza znajduje się ogólny nagłówek Etykiety wierszy
Sumy częściowe:
- mogą być wyświetlane u góry lub u dołu każdej grupy – ustawienie to dotyczy wszystkich pól Wierszy
- w polach kolumny, sumy częściowe są zawsze wyświetlane na dole
- etykieta Wiersza pozostaje na górze, nawet jeśli sumy częściowe są na dole
Forma konspektu – każde pole wiersza znajduje się w osobnej kolumnie. Układ konspektu jest przydatny, jeśli chcesz wyświetlić wszystkie nazwy pól jako etykiety nagłówków.
Etykiety wierszy:
- każda etykieta wiersza znajduje się w osobnym wierszu
- etykieta pola wiersza znajduje się zawsze powyżej etykiet pól wewnętrznych
- etykiety wierszy można powtarzać
Pola wierszy:
- każde pole wiersza znajduje się w oddzielnej kolumnie
- każde pole wiersza pokazuje jego nazwę w nagłówku kolumny
Sumy częściowe:
- mogą być wyświetlane u góry lub u dołu każdej grupy – ustawienie to dotyczy wszystkich pól wiersza
- w polach kolumny, sumy częściowe są zawsze wyświetlane na dole
- etykieta wiersza pozostaje na górze, nawet jeśli sumy częściowe są na dole
Forma tabelaryczna – każde pole wiersza znajduje się w osobnej kolumnie. Etykiety wierszy nie znajdują się w osobnym wierszu.
Etykiety wierszy:
- etykiety wierszy dla pól zewnętrznych znajdują się w tym samym wierszu, co pierwsza etykieta dla powiązanych pól wewnętrznych
- etykiety wierszy można powtarzać
Pola wierszy:
- każde pole wiersza znajduje się w osobnej kolumnie
- każde pole wiersza pokazuje jego nazwę w nagłówku kolumny
Sumy częściowe:
- sumy częściowe można wyświetlać tylko na dole każdej grupy – to ustawienie dotyczy wszystkich pól wiersza
- w polach kolumny, sumy częściowe są zawsze wyświetlane na dole
Forma tabelaryczna umożliwia zastosowanie dwóch pleceń:
- Powtórz wszystkie etykiety elementów – możesz powtórzyć etykiety wierszy dla wszystkich grup pól w tabeli przestawnej
- Nie powtarzaj etykiet elementów – poleceniem tym anulujesz powtarzanie etykiet wierszy dla wszystkich grup pól w tabeli przestawnej.
Podsumowania
W tabeli przestawnej możesz wstawić zarówno częściowe (dla każdej grupy), jak i końcowe podsumowanie danych.
Wstawianie sum częściowych:
Opcja Pokaż wszystkie sumy częściowe u góry grupy jest dostępna tylko w przypadku tabel przestawnych połączonych ze źródłem danych OLAP.
Wstawianie sum końcowych:
- na karcie Projektowanie rozwiń opcje dostępne przy ikonie Sumy końcowe
- wybierz opcję Włącz dla wierszy i kolumn
Podsumowania dla wierszy wyświetlają się pod warunkiem, że do obszaru kolumn przeciągniesz minimum jedną kolumnę. W przykładzie w obszarze kolumn znajduje się kolumna Miesiąc.
Narzędzia karty Analiza
Karta Analiza zawiera narzędzia umożliwiające zarządzanie danymi np. ich grupowanie, podsumowywanie, filtrowanie i analizę.
Grupa narzędzi Aktywne pole
Grupa ta zawiera narzędzia, za pomocą których możesz zarządzać opcjami tabeli przestawnej i jej polami. Rozwiń strzałkę przy ikonie Tabela przestawna. Zobaczysz wówczas następujące opcje:
1 – okienko, w którym możesz wpisać nazwę tabeli przestawnej
2– Opcje – za pomocą tego polecenia możesz wyświetlić Opcje tabeli przestawnej, w których dostosujesz tabelę do swoich potrzeb. Możesz np. ustawić jaka wartość ma być wyświetlana dla pustych pól.
3 – Opcja Pokaż strony filtru raportu działa, jeśli jakieś pole przeciągniesz do obszaru filtra. W naszym przykładzie do obszaru filtra przeciągnięte zostało pole Miesiąc.
Raport tabeli przestawnej został podzielony na kilka arkuszy według danych z pola, które zostało umieszczone w obszarze filtra tabeli. W naszym przykładzie każdy miesiąc został przeniesiony do oddzielnego arkusza.
4 – włączyć lub wyłączyć funkcję WeźDaneTabeli
5 – zobaczyć, jakie jest aktywne pole (aktywna komórka z tabeli przestawnej)
6 – wywołać okno dialogowe Ustawienia pola, w którym możesz zmienić nazwę pola zawierającego podsumowanie oraz sposób podsumowania danych np. z sumy na liczbę.
7 – zwijać i rozwijać grupy danych w tabeli przestawnej
Opcja Wstaw fragmentator umożliwia filtrowanie tabeli przestawnej za pomocą fragmentatora.
Oś czasu – jeśli w tabeli przestawnej posiadasz daty, możesz wstawić filtr w formie osi czasu. Aby to zrobić, kliknij polecenie Wstaw oś czasu. Pojawi się wówczas okno dialogowe – możesz w nim zaznaczyć pole, dla którego oś czasu ma zostać wstawiona.
Za pomocą osi czasu możesz filtrować np. miesiące, lata, kwartały. Jednostkę czasu, w jakiej mają być zaprezentowane dane, możesz wybrać na osi czasu.
Filtry w polach tabeli przestawnej
Każde z pól tabeli przestawnej możesz filtrować. Po rozwinięciu strzałki w dół znajdującej się po prawej stronie pola, pojawi się okno filtrowania, w którym możesz zaznaczyć interesujące Cię dane.
Grupa narzędzi Dane
Polecenie Odśwież – jeśli zmienisz dane wejściowe, na podstawie których przygotowałeś tabelę przestawną i chcesz zaktualizować tabelę – musisz ją odświeżyć. Tabela przestawna nie odświeża się automatycznie po zmienianie danych w źródle.
Tabelę możesz odświeżyć klikając na ikonę Odśwież w grupie narzędzi Dane lub klikając prawym przyciskiem myszy na tabeli przestawnej i wybierając z menu podręcznego polecenie Odśwież.
Jeśli chcesz zmienić źródło danych dla tabeli przestawnej, kliknij polecenie Zmień źródło danych w grupie narzędzi Dane.
Pojawi się wówczas okno dialogowe Zmienianie źródła danych tabeli przestawnej, w którym możesz wskazać nowe źródło danych.
Ikona Akcje zawiera zestaw narzędzi do czyszczenia tabeli, jej zaznaczania i przenoszenia.
Pole obliczeniowe w tabeli przestawnej
Aby wstawić pole obliczeniowe wybierz opcję Pola, elementy i zestawy i kliknij polecenie Pole obliczeniowe.
Pojawi się wówczas okno Wstawianie pola obliczeniowego. W oknie tym uzupełnij nazwę oraz wpisz formułę, za pomocą której wyliczysz potrzebne Ci informacje.
Tabela zawierająca pole obliczeniowe
Element obliczeniowy
Aby wstawić element obliczeniowy dane nie mogą być zgrupowane. Element obliczeniowy możesz wstawić jedynie do jednej z następujących sekcji tabeli przestawnej: Wiersze, Kolumny, Filtry. Nie możesz go wstawić w obszarze Wartości.
Przykładowa tabela przestawna, do której można wstawić element obliczeniowy.
Aby wstawić element obliczeniowy, kliknij na dowolne pole wierszy a później z grupy narzędzi Pola, elementy i zestawy wybierz polecenie Element obliczeniowy. W oknie dialogowym, które się pojawi w polu Nazwa wpisz nazwę elementu.
W polu Formuła wpisz funkcję, za pomocą której wykonasz obliczenia w elemencie obliczeniowym. W naszym przykładzie wyliczamy udział sprzedaży Polski w całości sprzedaży. Po wpisaniu funkcji, kliknij w oknie dialogowym przycisk Dodaj a następnie przycisk OK.
Wówczas do tabeli przestawnej dodany zostanie element obliczeniowy.
Na koniec kliknij prawym przyciskiem na wartość zero, kliknij opcję Formatuj komórki i w oknie dialogowym Formatowanie komórek w zakładce Liczby wybierz Kategorię Procentowe. W ten sposób zmienisz formatowanie komórek z ogólnych na procentowe.
Finalny wygląd tabeli przestawnej:
Grupa narzędzi Narzędzia i Pokazywanie
W grupie tej znajdziesz narzędzia, za pomocą których możesz wstawić wykres przestawny.
Aby wstawić wykres kliknij ikonę Wykres przestawny. Pojawi się wówczas okno dialogowe Wstawianie wykresu, w którym możesz wybrać odpowiedni dla Ciebie wykres.
Jeśli klikniesz ikonę Polecane tabele przestawne, wyświetlą się przykładowe tabele. Jeśli któraś z nich będzie dla Ciebie odpowiednia, kliknij w nią – wówczas wybrana przez Ciebie tabela zostanie wstawiona do arkusza.
Grupa narzędzi Pokazywanie umożliwia wyświetlenie bądź ukrycie w tabeli przestawnej:
- listy pól
- przycisków grup
- nagłówek pól
Jeśli zainteresował Cię temat tabel przestawnych i chcesz je przećwiczyć w praktyce, zajrzyj na nasz kurs z Excela, na którym rozwiązujemy ponad 100 praktycznych ćwiczeń.
Tomasz Majewski
Programista C#, Python, VBA
