Szybki kontakt




Dlaczego warto znać tabele przestawne?

Tabela przestawna w Excelu umożliwia szybkie podsumowywanie 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. Bardziej skomplikowane zagadnienia, tak jak wszystko inne, wymagają ćwiczeń.
  • 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

Oprócz opisu funkcjonowania tabel przestawnych, przedstawiam rozwiązania dla problemów, na które natknęli się Uczestnicy naszych szkoleń na kursie MS Excel.

Sposoby prezentacji danych w tabeli przestawnej.

Fragment danych źródłowych, których użyję do stworzenia raportu w formie tabeli przestawnej:

Dane źródłowe do 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.

Tabela przestawna przykład 1

2. Podsumowanie danych per miesiąc a następnie per kraj w ramach każdego miesiąca.

Tabela przestawna przykład 2

3. Podsumowanie danych per miesiąc i kraj.

Tabela przestawna przykład 3

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.

Wstawianie tabeli przestawnej

Uwaga! W zaznaczonym obszarze danych nie powinno być pustych wierszy, pustych kolumn i brakujących nagłówków kolumn.

Tworzenie tabeli przestawnej

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.

Zakres danych do tabeli

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.

Lokalizacja tabeli przestawnej

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.

Lokalizacja tabeli przestawnej ostrzeżenie

Jeśli klikniesz przycisk OK, istniejące w arkuszu dane zostaną zastąpione obszarem tabeli przestawnej. Poniżej przykład takiej sytuacji.

Lokalizacja tabeli przestawnej przykład - skasowane dane

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.

Lokalizacja tabeli przestawnej skasowane dane przykład 2

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 zaznaczyłeś 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.

Lista pól tabeli przestawnej przykład 1

Pola możesz odpowiednio przeciągnąć między obszarami tabeli do:

  • Filtra
  • Kolumn
  • Wierszy
  • Wartości

Lista pól tabeli przestawnej przykład 2

Jeśli chcesz zmienić sposób prezentacji obszarów w polach tabeli przestawnej, kliknij narzędzia i wybierz odpowiadający Ci układ.

Ustawienia pól tabeli przestawnej

Karty tabeli przestawnej

Po utworzeniu tabeli przestawnej na Wstążce pojawiają się dwie karty kontekstowe:

  • Projektowanie
  • Analiza

Zawierają one narzędzia do formatowania tabeli i dokonywania analizy przedstawionych w niej danych.

Karty Analiza i Projektowanie

Karty są aktywne tylko wtedy, gdy aktywna jest tabela przestawna. Aby je wyświetlić, kliknij dowolne miejsce w tabeli przestawnej.

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

Style tabeli przestawnej

1 – Style tabeli przestawnej – zawiera zestaw domyślnych stylów, za pomocą których możesz sformatować tabelę przestawną.

Dodatkowe style tabeli przestawnej

Po kliknięciu przycisku Więcej wyświetlisz zdefiniowane style tabel przestawnych.

Dodatkowe style tabeli przestawnej przykład 2

Opcja Nowy styl tabeli przestawnej umożliwia Ci stworzenie własnego stylu. Możesz go zapisać w stylach tabeli przestawnej i ustawić jako styl domyślny.

Opcja Wyczyść – za jej pomocą usuwasz styl tabeli przestawnej.

2 – Opcje stylu tabeli przestawnej – grupa narzędzi, za pomocą której możesz sterować stylami tabeli np. zaznaczyć kolorem kolumny, pogrubić czcionkę w nagłówkach wierszy lub kolumn.

Tabela przestawna - wyłączone style

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

Tabela przestawna włączone style

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.

Tabela przestawna układ raportu

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.

Tabela przestawna - układ - pusty wiersz

Układ raportu – za pomocą tej opcji możesz zmieniać sposób wyświetlania raportu tabeli.

Tabela przestawna - opcje układu raportu

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.

Tabela przestawna forma kompaktowa

    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.

Tabela przestawna forma konspektu przykład

    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.

Tabela przestawna forma tabelaryczna

    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
  • Tabela przestawna - forma tabelaryczna - powtórzenie etykiet

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

Tabela przestawna - podsumowania

Wstawienie podsumowań umożliwiają polecenia:

  • Sumy częściowe
  • Sumy końcowe

Przykład sum częściowych

Sumy częściowe w tabeli przestawnej

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.

Przykład sum końcowych

Sumy końcowe w tabeli przestawnej

Opcja Włącz tylko dla wierszy działa pod warunkiem, że do obszaru kolumn przeciągniesz minimum jedno pole.

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:

Tabela przestawna - narzędzia Aktywne pole

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.

Opcje tabeli przestawnej

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

Filtr tabeli przestawnej

Po kliknięciu opcji Pokaż strony filtru raportu pojawia się poniższe okno dialogowe. Należy kliknąć w nim przycisk OK.

Tabela przestawna - strony filtru raportu

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 kraj został przeniesiony do oddzielnego arkusza.

Tabela przestawna - strony filtru raportu - przykład

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

Tabela przestawna - ustawienia pola wartości

Tabela przestawna - ustawienia pola wartości - przykład 2

7 – zwijać i rozwijać grupy danych w tabeli przestawnej

Tabela przestawna - grupy

8 – opcje Wyszczególnij i Uogólnij dostępne są dla kostek OLAP (rozszerzenie dwuwymiarowej tabeli arkusza kalkulacyjnego)

Grupa narzędzi Grupowanie

W grupie narzędzi Grupowanie możesz grupować i rozgrupowywać pola:

  • numeryczne
  • daty

Tabela przestawna - grupowanie

Poniżej tabela przestawna zawierająca sprzedaż zsumowaną per każdy dzień sprzedaży. Opcje grupowania umożliwiają pogrupowanie sprzedaży według np. miesiąca.

Tabela przestawna grupowanie przykład 1

Aby zgrupować dane dla każdego miesiąca, kliknij opcję Grupuj zaznaczenie lub Grupuj pole. Pojawi się wówczas okno Grupowanie.

Tabela przestawna - okno grupowanie

Pola PoczątekKoniec są uzupełniane automatycznie – jest to pierwsza i ostatnia data w zakresie danych, które były użyte do utworzenia tabeli przestawnej. Daty możesz zmienić według własnych potrzeb. Data końcowa (Koniec) musi być większa niż początkowa.

Jeśli zaznaczysz w oknie Miesiące i Lata, dane w tabeli przestawnej będą wyglądały następująco:

Tabela przestawna grupowanie - przykład 3

Grupa narzędzi Filtrowanie

W grupie narzędzi Filtrowanie znajdziesz narzędzia umożliwiające np. wstawienie fragmentatora (filtr) i osi czasu. Oś czasu możesz wstawić, jeśli tabela przestawna posiada dane typu data.

Tabela przestawna - fragmentator

Jeśli klikniesz opcję Wstaw fragmentator pojawi się okno dialogowe Wstawianie fragmentatorów. W oknie tym możesz wybrać kolumny, według których będziesz filtrował dane.

Tabela przestawna - wstawianie fragmentatora

Tabela przestawna fragmentator - przykład 1

Aby usunąć fragmentator lub zmienić jego rozmiar, klikasz na nim raz lewym przyciskiem myszy.

Tabela przestawna - fragmentator - przykład 2

Po pojawieniu się zaznaczenia, kliknij klawisz Delete aby usunąć fragmentator. Jeśli chcesz zmienić rozmiar, kliknij lewym przyciskiem myszy na krawędź fragmentatora i przeciągnij krawędź do takiego rozmiaru, jakiego oczekujesz.

Opcja Połączenia filtru – jeśli masz więcej niż jedną tabelę przestawną i w każdej z nich wstawisz fragmentatory, możesz użyć opcji Połączenia filtru, aby za pomocą fragmentatorów wszystkie tabele przestawne jednocześnie filtrować. Aby to zrobić, kliknij fragmentator prawym przyciskiem myszy i wybierz opcję Połączenia raportu.

Tabela przestawna - fragmentator - przykład 3

Pojawi się wówczas okno dialogowe, w którym możesz dołączyć do filtru kolejne raporty tabeli przestawnej.

Tabela przestawna - połączenie raportu

Opcja ta umożliwia filtrowanie wielu tabel przestawnych za pomocą fragmentatora.

Tabela przestawna - połączenia raportu przykład 1

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.

Tabela przestawna - wstawianie osi czasu

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.

Tabela przestawna - oś czasu przykład

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.

Tabela przestawna - filtry pól

Grupa narzędzi Dane i Akcje

Tabela przestawna narzędzia 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ż.

Tabela przestawna - odświeżanie

Jeśli chcesz zmienić źródło danych dla tabeli przestawnej, kliknij polecenie Zmień źródło danych w grupie narzędzi Dane.

Tabela przestawna - zmiana źródła danych

Pojawi się wówczas okno dialogowe Zmienianie źródła danych tabeli przestawnej, w którym możesz wskazać nowe źródło danych.

Tabela przestawna - zmiana źródła danych przykład

Ikona Akcje zawiera zestaw narzędzi do czyszczenia tabeli, jej zaznaczania i przenoszenia.

Tabela przestawna - Akcje

Grupa narzędzi Obliczenia

Grupa Obliczenia umożliwia wstawienie pola obliczeniowego i elementu obliczeniowego. Są to elementy, które pozwalają na wyliczenie w tabeli przestawnej dodatkowych informacji.

Pole obliczeniowe

Aby wstawić pole obliczeniowe wybierz opcję Pola, elementy i zestawy i kliknij polecenie Pole obliczeniowe.

Tabela przestawna - 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 przestawna - pole obliczeniowe - przykład

Tabela zawierająca pole obliczeniowe

Tabela przestawna - pole obliczeniowe - przykład 2

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.

Tabela przestawna - element obliczeniowy - przykład 1

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.

Tabela przestawna - element obliczeniowy - przykład 2

Wówczas do tabeli przestawnej dodany zostanie element obliczeniowy.

Tabela przestawna - element obliczeniowy - przykład 3

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.

Tabela przestawna - element obliczeniowy - przykład 3

Finalny wygląd tabeli przestawnej:

Tabela przestawna z elementem obliczeniowym

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. Wykresy przestawne przedstawiamy w poświęconym im artykule.

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

Tabela przestawna - pokazywanie przykład

Jeśli wstawiasz tabelę przestawną i jakaś opcja nie działa, napisz w komentarzu. Chętnie uzupełnimy artykuł lub po prostu odpowiemy na Twoje pytanie.

Dodaj komentarz

Twój adres email nie zostanie opublikowany.