Technikum Klasa I 45 minut PP: II.3c | s. 344

Lekcja 42: Arkusz kalkulacyjny - tabele i wykresy przestawne

Tabele przestawne, grupowanie danych, wykresy przestawne, analiza danych

📋 Podstawa programowa: II.3c
analiza danycharkusz kalkulacyjnypivotpodsumowaniatabele przestawnewykresy
00:00
Wprowadzenie
5 min
00:05
Teoria
15 min
00:20
Cwiczenia
15 min
00:35
Podsumowanie
10 min
📚

Teoria

▼

Czym jest tabela przestawna?

Tabela przestawna (Pivot Table) to jedno z najpotezniejszych narzedzi arkusza kalkulacyjnego. Pozwala na szybkie podsumowanie, analize i prezentacje duzych zbiorow danych bez pisania formul. Tabela przestawna automatycznie grupuje, sumuje, liczy i agreguje dane wedlug wybranych kryteriow.

Przyklad: Masz tabele 10 000 transakcji sprzedazy. Tabela przestawna w kilka sekund pokaze ci laczna sprzedaz w podziale na produkty, regiony, miesiace - w dowolnej konfiguracji.

Budowa tabeli przestawnej

Tabela przestawna sklada sie z 4 obszarow, do ktorych przeciagamy pola (kolumny) z danych zrodlowych:

  • Wiersze (Rows) - kategorie wyswietlane w wierszach, np. nazwy produktow, nazwy miast
  • Kolumny (Columns) - kategorie wyswietlane w kolumnach, np. miesiace, lata, regiony
  • Wartosci (Values) - dane liczbowe do agregacji, np. SUMA sprzedazy, SREDNIA ceny, LICZBA transakcji
  • Filtry (Filters) - pola sluzace do filtrowania calej tabeli, np. rok, kategoria produktu
Kluczowa zasada: Dane zrodlowe musza byc w formie tabeli plaskiej - kazda kolumna to jedno pole, kazdy wiersz to jeden rekord. Nie moga byc puste wiersze ani kolumny wewnatrz danych!

Tworzenie tabeli przestawnej

  • Krok 1: Kliknij w dowolna komorke danych zrodlowych
  • Krok 2: Wstaw > Tabela przestawna
  • Krok 3: Wybierz lokalizacje (nowy arkusz lub istniejacy)
  • Krok 4: W panelu pol tabeli przestawnej przeciagnij pola do odpowiednich obszarow
  • Krok 5: Dostosuj funkcje agregujaca (SUMA, LICZBA, SREDNIA, MIN, MAX)

Grupowanie danych w tabeli przestawnej

Mozna grupowac dane na kilka sposobow:

  • Grupowanie dat - automatyczne grupowanie po miesiacach, kwartalach, latach (kliknij prawym na date > Grupuj)
  • Grupowanie liczb - podzial na zakresy, np. wiek 0-18, 19-35, 36-50 itd.
  • Grupowanie reczne - zaznacz elementy > Grupuj zaznaczenie (np. polacz kilka miast w regiony)

Funkcje agregujace

W obszarze Wartosci mozna uzywac roznych funkcji:

  • SUMA - laczna wartosc (domyslna dla liczb)
  • LICZBA - ile rekordow (domyslna dla tekstu)
  • SREDNIA - wartosc srednia
  • MIN / MAX - wartosc minimalna / maksymalna
  • ILOCZYN - iloczyn wartosci
  • ODCH.STAND - odchylenie standardowe

Zmiana funkcji: kliknij prawym na wartosc w tabeli > Podsumuj wartosci przez > wybierz funkcje.

Wykresy przestawne (Pivot Charts)

Wykres przestawny to wykres polaczony z tabela przestawna. Zmienia sie automatycznie gdy modyfikujemy tabele przestawna.

  • Tworzenie: zaznacz tabele przestawna > Wstaw > Wykres przestawny
  • Wykres ma wbudowane filtry - mozna klikac przyciski filtrow bezposrednio na wykresie
  • Zmiana ukladu tabeli przestawnej natychmiast aktualizuje wykres
  • Doskonaly do tworzenia interaktywnych dashboardow
Odswiezanie danych: Tabela przestawna NIE aktualizuje sie automatycznie gdy zmieniasz dane zrodlowe! Musisz kliknac prawym > Odswiez (lub Ctrl+Alt+F5 dla wszystkich tabel). Mozesz tez ustawic automatyczne odswiezanie przy otwarciu pliku.

Fragmentatory (Slicery) z tabelami przestawnymi

Fragmentatory to wizualne przyciski filtrowania, ktore mozna podpiac do tabel przestawnych:

  • Wstaw > Fragmentator - wybierz pola
  • Jeden fragmentator moze filtrowac wiele tabel i wykresow przestawnych naraz
  • Idealne do tworzenia interaktywnych raportow i dashboardow
✏️

Zadania

▼
Latwe

Zadanie 1: Pierwsza tabela przestawna

Utworz tabele sprzedazy z 30 wierszami zawierajaca kolumny: Data, Produkt (5 produktow), Kategoria (3 kategorie), Region (Polnoc/Poludnie/Wschod/Zachod), Kwota. Utworz tabele przestawna pokazujaca laczna sprzedaz kazdego produktu w kazdym regionie (produkty w wierszach, regiony w kolumnach, SUMA kwot jako wartosci).

Pokaz rozwiazanie 
1. Utworz tabele danych (A1:E31):
   Data | Produkt | Kategoria | Region | Kwota
   (wypelnij 30 wierszy roznymi danymi)

2. Kliknij w dowolna komorke danych
3. Wstaw > Tabela przestawna > Nowy arkusz > OK

4. Panel pol tabeli przestawnej:
   - Przeciagnij "Produkt" do obszaru WIERSZE
   - Przeciagnij "Region" do obszaru KOLUMNY
   - Przeciagnij "Kwota" do obszaru WARTOSCI
     (automatycznie ustawi sie SUMA)

5. Wynik: tabela krzyzowa
        Polnoc  Poludnie  Wschod  Zachod  Suma
   Prod A  1200    800      950    1100    4050
   Prod B   900   1100     1200     800    4000
   ...

6. Mozesz zmienic funkcje na SREDNIA:
   Kliknij prawym na liczbe > Podsumuj przez > Srednia
Srednie

Zadanie 2: Analiza danych z grupowaniem

Uzyj tabeli z Zadania 1. (a) Dodaj pole "Kategoria" jako filtr tabeli przestawnej. (b) Pogrupuj daty po miesiacach. (c) Zmien uklad tak, aby wiersze = miesiace, kolumny = produkty, wartosci = SUMA kwot. (d) Dodaj drugie pole wartosci: LICZBA transakcji. (e) Utworz wykres przestawny na podstawie tej tabeli.

Pokaz rozwiazanie 
1. Modyfikacja tabeli przestawnej:
   a) Przeciagnij "Kategoria" do obszaru FILTRY
      - Pojawi sie lista rozwijana nad tabela
      - Mozesz filtrowac po kategorii

   b) Grupowanie dat:
      - Kliknij prawym na dowolna date w tabeli
      - Grupuj... > zaznacz "Miesiace" > OK
      - Daty zostana pogrupowane po miesiacach

   c) Zmiana ukladu:
      - Usun "Produkt" z wierszy (przeciagnij poza)
      - Przeciagnij "Data" (miesiace) do WIERSZE
      - Przeciagnij "Produkt" do KOLUMNY
      - "Kwota" pozostaje w WARTOSCI

   d) Drugie pole wartosci:
      - Przeciagnij "Kwota" ponownie do WARTOSCI
      - Kliknij prawym > Podsumuj przez > Liczba
      - Teraz masz SUMA i LICZBA obok siebie

   e) Wykres przestawny:
      - Kliknij w tabele przestawna
      - Wstaw > Wykres przestawny
      - Wybierz typ: Kolumnowy grupowany
      - Wykres automatycznie pokaze dane z tabeli
      - Filtry na wykresie dzialaja interaktywnie
Trudne

Zadanie 3: Dashboard z tabelami przestawnymi

Utworz interaktywny dashboard: (a) Tabela przestawna 1 - sprzedaz wg regionow i kwartalow, (b) Tabela przestawna 2 - top 5 produktow wg wartosci, (c) Wykres przestawny kolumnowy do tabeli 1, (d) Wykres przestawny kolowy do tabeli 2, (e) Fragmentatory Regionu i Kategorii podpiete do obu tabel. Umiesc wszystko na jednym arkuszu "Dashboard".

Pokaz rozwiazanie 
1. Tabela przestawna 1 (sprzedaz regionalna):
   - Wiersze: Region
   - Kolumny: Data pogrupowana po kwartalach
   - Wartosci: SUMA(Kwota)
   - Lokalizacja: arkusz "Dashboard", komorka A1

2. Tabela przestawna 2 (top produkty):
   - Wiersze: Produkt
   - Wartosci: SUMA(Kwota), sortowanie malejace
   - Filtr wartosci: Top 5
     (kliknij strzalke przy Produkt > Filtry
     wartosci > 5 najwazniejszych)
   - Lokalizacja: arkusz "Dashboard", komorka A15

3. Wykresy przestawne:
   - Zaznacz Tabele 1 > Wstaw > Wykres przestawny
     > Kolumnowy > umiesc na "Dashboard"
   - Zaznacz Tabele 2 > Wstaw > Wykres przestawny
     > Kolowy > umiesc na "Dashboard"

4. Fragmentatory:
   - Kliknij Tabele 1 > Wstaw > Fragmentator
   - Zaznacz: Region, Kategoria > OK
   - Kliknij prawym na fragmentator > Polaczenia
     raportu > zaznacz OBIE tabele przestawne
   - Teraz filtrowanie dotyczy obu tabel i wykresow

5. Formatowanie:
   - Umiesc elementy rownomiernie na arkuszu
   - Ukryj linie siatki (Widok > odznacz Siatka)
   - Dodaj tytul "Dashboard sprzedazy"
🎥

Materialy wideo

▼
1.4.Jak przygotować dane do tabeli przestawnej? [Kurs Tabele przestawne Excel]
Bartosz Czapiewski SkuteczneRaporty
Pivot Table for Beginners - Excel
Karina Barańska
🎧

Podcasty

▼
✔️

Quiz - sprawdz sie!

▼
📜

Podstawa programowa

▼
← Lekcja 41: Wykresy i ich dobor Lekcja 43: Bazy danych - pojecia →