Kurs Excel: Zasady działania Tabeli Przestawnej
od Podstaw
6 godz. 10 min · Excel · Biznes i Automatyzacje
Michał KowalczykTrener i założyciel w Excellent Work - Skuteczna Nauka ExcelaCzego się nauczysz
- Zyska lepsze zrozumienie działania programu Excel.
- Zyska umiejętność pracy na dużych zestawach danych (filtrowanie, przekształcanie, raportowanie).
- Dowie się jak doszukiwać i łączyć potrzebne dane z innych plików i arkuszy.
- Pozna sposoby wyciągania z komórki istotnych fragmentów danych.
- Pozna podstawy analizy dużych zestawów danych z użyciem Tabel Przestawnych.
- Będzie umiał podsumować dane według kryteriów (np. sprzedaż w rozbiciu na kategorie).
- Będzie umiał zliczyć wystąpienia według kryteriów (np. liczba transakcji nieopłaconych).
- Nauczy się tworzyć proste algorytmy przypisujące wartości na podstawie spełnionych kryteriów.
Formatowanie i zarządzanie danymi
Efektywna praca w Excelu zaczyna się od płynnego poruszania się w arkuszach i tabelach. W tej części kursu skupimy się na wprowadzaniu danych, ich edycji i tworzeniu układów, które znacznie poprawią czytelność raportów. Nauczysz się też, jak sprawnie sortować i filtrować duże zestawy informacji, by w kilka chwil wyciągać z nich najważniejsze wnioski. Pokażę Ci również sztuczki przydatne na co dzień, dzięki którym oszczędzisz mnóstwo czasu.
Formuły i kalkulacje
Excel oferuje imponującą liczbę funkcji, które pozwalają na głęboką analizę dowolnych danych – od formuł tekstowych, przez przetwarzanie dat, po budowanie złożonych wyrażeń logicznych. W tej części kursu zobaczysz, jak łączyć różne funkcje w jedno, aby poszerzyć ich możliwości. Dowiesz się, jak tworzyć warunkowe formuły, które będą automatycznie reagować na zmiany w arkuszu. Dzięki temu aktualizacje danych w tabeli wyliczą się same, bez dodatkowej pracy z Twojej strony.
Praca z tabelami przestawnymi i wykresami
Nawet najlepsze dane nie mają większej wartości, jeśli nie umiesz ich właściwie zaprezentować. Dlatego w kolejnej sekcji nauczysz się tworzyć klarowne tabele przestawne i atrakcyjne wizualizacje. Wykorzystasz listy wyboru, by ograniczyć liczbę błędów przy wprowadzaniu danych, a następnie za pomocą wykresów pokażesz wyniki w jasny i przystępny sposób. Zyskasz praktyczne umiejętności tworzenia zestawień w oparciu o Tabele Przestawne.
Podsumowanie i praktyczne wskazówki
Na koniec kursu zebrałem najczęstsze błędy, na jakie trafiają nawet zaawansowani użytkownicy, i pokazałem, jak skutecznie ich unikać. Dostaniesz pakiet najlepszych praktyk, przydatnych skrótów klawiszowych oraz zestaw praktycznych rad, które pomogą Ci rozwiązać typowe problemy w Excelu. Do zobaczenia w kursie!
Dla kogo jest ten kurs?
Ten kurs powstał z myślą o osobach, które pracują już z Excelem oraz tych, które dopiero rozpoczynają przygodę z tym programem. Z kursu najwięcej wyniosą osoby, które:
Witam Cię w lekcji poświęconej tabelą przestawnym, bardzo przydatnym i
sprytnemu narzędziu do analizy danych.
Musimy wykonać tutaj kilka zadań przy użyciu tabel przestawnych.
Zanim jednak to zrobimy, warto przyglądnąć się naszym danym wsadowym.
A więc spójrzmy jakimi kolumnami dysponujemy.
Mamy imię i nazwisko, mamy płeć, mamy wiek, datę urodzenia, miasto,
województwo, a także pensję netto.
Istotną sprawą jest to, aby właśnie poznać kolumny, na których pracujemy, bo bardzo
przyda nam się to w momencie, gdy zaczniemy budować tabele przestawną.
Zanim zbudujemy na naszym obszarze tabele przestawną, gorąco zachęcam Cię do tego,
aby zawsze najpierw wybrać opcję Ctrl+T, czyli wstawienia zwykłej tabeli.
To się świetnie sprawdza w momencie, gdy będziemy kiedyś odświeżali dane
w naszej tabeli przestawnej.
Nasza tabela zwykła ma nagłówki.
Pamiętajmy o pustych wierszach. Ta nasza tabela.
Pustych wierszy nie ma.
Gdyby jakiś pusty wiersz się pojawił, warto byłoby go usunąć.
Dlaczego?
No dlatego, że gdyby tu pod spodem były dane po pustym wierszu.
No to niestety zaznaczenie wyglądałoby tak jak teraz i ten pusty wiersz
odciąłby nam dane poniżej.
Czyli nasza analiza byłaby błędna.
Także pamiętamy o pustych wierszach.
Przypomnę to jeszcze raz, bo była okazja już o tym powiedzieć.
Kliknijmy ok.
Właśnie na naszych danych została wstawiona taka normalna, zwykła tabela.
Ja osobiście nie jestem fanem tego formatowania, a więc będąc na tabeli
klikam na dowolną komórkę, która należy do tabeli.
Wybieram projekt tabeli.
No i tutaj mogę wybrać sobie takie predefiniowane formatowanie,
które mi się podoba. To wydaje się być całkiem ok.
Świetnie. I co dalej?
Z racji tego, że pracuję w tabeli teraz ta tabela ma kilka fajnych plusów.
Jednym z nich jest to, że gdy przeciągnę sobie w dół moją
tabelę, to u góry widzę zamiast konkretnych liter reprezentujących kolumny
nazwy, co jest bardzo przydatne, szczególnie gdy pracujemy
z dużymi tabelami.
Drugim takim bardzo fajnym plusem tabeli jest to, że gdybyśmy np.
Zdecydowali się wykonać jakieś obliczenia, wpisałem równa się i odwołałem się
strzałką w lewo to już nie widzę odwołania G10 tylko widzę odwołanie.
Pensja netto.
Symbol małpy oznacza odwołanie się do komórki na wysokości komórki, w której
jestem z konkretnej kolumny, czyli jestem na wysokości tej zaznaczonej komórki i
odwołuję się do tejże komórki w kolumnie pensja netto, o czym informuje
mnie druga część formuły.
Mogę pomnożyć sobie to np.
Razy 1.03. Załóżmy, że wszyscy dostają 3% podwyżkę i zatwierdzamy enterem.
Zwróć uwagę co się stało.
Nagłówek został tutaj automatycznie dodany, formatowanie zostało skopiowane, a
formuła przeciągnęła się sama do końca, co jest bardzo fajną rzeczą.
Dlatego zachęcam Cię też do tego, aby z tych tabeli zwykłych korzystać.
Cofnijmy sobie Ctrl+ dodanie tej naszej kolumny jeszcze raz Ctrl+F i jeszcze raz
Ctrl+C, aby pozbyć się również formuły i zbudujmy sobie teraz na naszym
obszarze tabele przestawną.
Udajmy się na kartę Wstawianie, a następnie mając zaznaczoną dowolną komórkę
tej naszej tabeli źródłowej wybierzmy Tabela przestawna.
Zrobiliśmy to po to, aby tabela przestawna od razu wiedziała na jakim
obszarze chcemy pracować.
No i faktycznie zostało to wykryte.
Jest to nasza tabela pierwsza.
To jest obszar, na którym pracujemy.
Możesz go rozpoznać po tych, powiedzmy kolokwialnie, biegających
mrówkach wokół naszego zakresu.
Zobacz, one sobie tutaj krążą wokół naszej tabeli.
Gdzie chcemy umieścić naszą tabelę?
To jest drugie pytanie.
A więc pierwszym jest to, co będziemy analizowali, a drugim to, gdzie
chcielibyśmy, aby taka analiza została wykonana.
My skorzystamy z opcji istniejącego arkusza i wybierzemy
jakąś konkretną komórkę.
Natomiast gdybyśmy wybrali opcję nowy arkusz, no to na dole pojawiłby się nowy
arkusz, w którym powstałaby nasza tabela przestawna i w tej tabeli
przestawnej dokonywalibyśmy analizy.
Ja skorzystam z nowego arkusza dlatego, że będzie nam po prostu się łatwiej
tłumaczyło tabele przestawne.
Zaznaczyłem komórkę i9 no i zrobiłem błąd. Dlaczego?
Dlatego, że byłem ustawiony kursorem w złym miejscu.
Zwracaj na to uwagę, a więc usuńmy sobie błąd.
Wróćmy do naszej tabeli i wciśniemy Ctrl+A.
Aby ją zaznaczyć, mamy właściwe źródło.
Przechodzimy teraz do istniejącego arkusza i teraz klikamy dopiero I9.
Klikamy OK.
No i naszym oczom ukazuje się tabela przestawna, a w zasadzie dopiero taki
schemat pusty szablon tabeli przestawnej.
Co w tej tabeli przestawnej się znajduje?
Tutaj widzimy kolejne nagłówki naszych kolumn.
A więc jeżeli pracujemy w tabeli przestawnej, nie robimy
tego na kontekście wiersza.
Robimy to na kontekście kolumny. Co to znaczy?
To znaczy, że jeżeli spojrzymy na naszą tabelę i wyobrazimy sobie, że ta źródłowa
tabela jest człowiekiem, to patrzymy na nią od góry.
Widzimy tylko głowę.
No i tą głową naszych danych są właśnie nagłówki.
Kliknijmy na dowolną komórkę naszej tabeli przestawnej, aby zobaczyć panel po prawej.
No i teraz w jaki sposób działa ta nasza tabela przestawna?
Aby było Ci łatwiej, narysuję taki bardzo prosty schemat
składający się z czterech elementów, bo właśnie tak będzie działała
nasza tabela przestawna.
Tymi elementami są filtry, które możesz znaleźć tutaj i one znajdują się tu.
Kolejno mamy kolumny.
Kolumny znajdują się tutaj.
No i mamy tutaj kolumny.
Następnie mamy wiersze.
Wiersze to ten obszar i na samym końcu mamy wartości.
Oznaczymy je jako Sigma.
Są to nasze wartości.
I teraz przeciągając kolejne kolumny z naszej tabeli do poszczególnych obszarów
tabeli przestawnej będzie wykonywała się analiza.
Czyli gdybyśmy np.
Chcieli sprawdzić ile w sumie w naszej organizacji płaci się której płci, to
przerzucilibyśmy płeć do wierszy, a pensję netto przerzucilibyśmy do wartości.
Zobaczmy, co się stanie, gdy wykonamy takie ćwiczenie.
A więc łapię płeć i przerzucam do wierszy.
Pojawia się k i m, bo faktycznie tylko takie dwie płcie znajdują
się w moim zestawieniu.
Czyli w tym momencie tabela przestawna wyciągnęła wartości unikatowe z płci.
Trochę jakbyśmy patrzyli teraz na naszą kolumnę przez pryzmat
tego, co widzimy w filtrze.
Kolejna sprawa to pensja netto.
Złapmy pensję netto i przerzucimy do wartości.
Co się teraz wydarzyło?
W tym przypadku zostały tutaj zsumowane wartości pensji dla osób z
płcią K i dla osób z płcią m.
W ten oto sposób widzimy wartości.
Pewnie nasunęłoby się też pytanie a ile było tych poszczególnych osób?
No to złapmy sobie pensję netto i wrzućmy jeszcze raz do wartości.
I teraz zadbajmy o to, żeby nie widzieć tutaj konkretnych wartości, a widzieć, ile
wystąpiło nam tutaj poszczególnych pozycji.
Jak możemy to zrobić? Zejdźmy sobie do wartości.
Kliknijmy sobie tę strzałkę i wybierzmy ustawienia pola wartości.
W tym miejscu możemy zadecydować, jaki rodzaj kalkulacji ma zostać
wykonany na naszej kolumnie.
Domyślnie dla kolumn liczbowych wykonywana jest suma.
My byśmy chcieli zliczyć wystąpienia.
A więc kliknijmy sobie na liczbę. Zobacz.
Możemy skorzystać tutaj ze średniej maximum minimum, iloczyn i
jeszcze kilku innych kalkulacji.
Możemy też zmienić nagłówek na przykład na Liczba osób.
Kliknijmy enter.
No i mamy informację, że pracuje tutaj 29 kobiet i 70 mężczyzn.
Być może to jest powód, dla którego różnica w pensji jest tak duża.
Ok, myślę, że podstawy tabel przestawnych są już trochę bardziej
dla Ciebie oczywiste.
Co gdybyśmy skorzystali jeszcze z kolumny?
Złapmy sobie miasto i przerzucimy do kolumny, jeżeli
przerzucimy miasto do kolumny.
Zwróć uwagę co się stanie.
Jeszcze nam się tutaj dodatkowo te dane rozsypały.
Spróbujmy je zrozumieć.
W naszym przypadku mamy tutaj kobiety, które pracują do tego miejsca.
W Krakowie tych kobiet jest 8 i zarabiają w sumie 24 tysiące 336 zł.
Mężczyzn jest 32 i zarabiają ok.
99 tysięcy.
I tak dalej, i tak dalej.
Mamy kolejne miasto i kolejne miasto.
Wyrzućmy, może miasto, przerzucimy może województwo.
Zobaczmy jak będzie to wyglądało w przypadku województwa.
Zaznaczyłem ptaszka.
To spowodowało, że województwo pojawiło się w wierszach.
Ale gdy przerzucę sobie województwo do kolumn, widzę podobne zestawienie.
Czyli mamy małopolskie, mazowieckie, pensja, a pod spodem małopolskie,
mazowieckie i liczba osób pracujących w poszczególnych województwach.
Gdybym zamienił miejscami wartości i wojwództwa.
No to zwróć uwagę.
Teraz mam mazowieckie, a tutaj mam informacje dla województwa mazowieckiego
odnośnie pensji netto i liczby osób.
I tutaj mam to samo dla województwa małopolskiego.
A na końcu razem, czyli razem.
Informacja o liczbie osób i informacja o tym ile jest tutaj kobiet
i ile jest tutaj mężczyzn.
Wyczyścimy naszą tabele przestawną.
Możemy powyciągać sobie te poszczególne pola gdzieś tutaj na bok, albo po prostu
odhaczyć ptaszkiem i to spowoduje, że tabela przestawna jest czysta.
Spróbujmy przejść przez kolejne zadania.
Oblicz ile jest osób płci męskiej.
No to możemy sobie wybrać płeć, a następnie płeć przerzucić do wartości.
Z tej tabeli jesteśmy w stanie wyciągnąć informacje.
Mamy 70 mężczyzn w naszej organizacji.
Oblicz sumę pensji netto w Krakowie.
Płeć przestaje nam być potrzebna, więc sobie ją odhaczamy.
Przerzucamy sobie miasto i przerzucamy sobie pensję netto.
Klikam sobie widziaczka.
Z racji tego, że pensja netto jest wartością liczbową, to trafia do wartości.
A miasto było wartością tekstową, dlatego, że tam były teksty
Kraków, Radom i tak dalej.
Trafiło to do wierszy.
Mamy informację, że w Krakowie mamy taką pensję netto?
Gdybyśmy chcieli być tacy bardzo precyzyjni, moglibyśmy
oczywiście skorzystać z filtra.
Filtrowanie w tabeli przestawnej ma miejsce tutaj.
A więc wybieram sobie Kraków. Klikam ok.
No i proszę, to jest dokładna informacja, która mnie interesuje.
Czyszczę filtr.
Działa to tak jak zwykły filtr i przechodzę sobie do kolejnego pytania.
Ile osób urodziło się przed 1990 rokiem?
Ok, teraz potrzebuję znowu zobaczyć osoby, A więc przerzucę sobie miasto do góry i
suma Z pensji netto wezmę sobie imię i nazwisko i przerzucę do wartości.
Mamy 99 pracowników i chcemy poznać osoby, które urodziły się przed 1990 rokiem.
A więc złapię datę urodzenia i przerzucę do wierszy.
Zwróć uwagę co tutaj się stało.
Wartości zostały zgrupowane w lata, czyli mamy tu poszczególne informacje
o latach, a potem kwartałach.
No i chciałbym się tutaj zafiltrować na osoby urodzone przed 1990 rokiem.
A więc rozwijam sobie etykiety wierszy, wybieram filtr dat, a następnie chciałbym
się filtrować na osoby przed i tutaj wybieram konkretną datę.
Mógłbym ją wybrać z kalendarza. To by trochę zajęło.
Przed 1990 rokiem, czyli przed pierwszym stycznia 1991 roku.
Kliknijmy zatem, ok.
No i zobaczmy, czy mamy tutaj wyfiltrowane osoby.
Tak, mamy tych osób 85 I to jest odpowiedź na nasze pytanie.
Możemy zdjąć filtr, wyczyścić tabele przestawną i podążyć do kolejnego zadania.
Oblicz średnią pensję w województwie małopolskim, A więc wybieram teraz
województwo, a następnie pensję netto.
No i z racji tego, że wiem już jak korzystać ze zmiany sposobu kalkulacji,
rozwijam, wybieram ustawienia pola wartości, wybieram średnia, klikam ok.
I to jest informacja o średniej pensji dla pracowników w województwie małopolskim.
I zadanie ostatnie oblicz ile zarabiają średnio mężczyźni po 40
roku życia w danej firmie.
I teraz na logikę pewnie moglibyśmy pomyśleć, że no dobra, wyrzucimy sobie
województwo, wrzucimy sobie tutaj wiek gdzieś na przykład do wierszy.
No i filtrujemy na osoby po 40 tce. No nie do końca.
Dlaczego? Nie do końca?
Dlatego, że średnia w tym momencie liczy się dla konkretnych osób
z konkretnym wiekiem.
A więc jeżeli chcemy uzyskać średnią policzoną w tym miejscu, to tutaj musi się
znaleźć pozycja mężczyzn tylko z przedziału 40+.
Jak możemy to zrobić?
Musimy wiek przerzucić do filtrów, a płeć do wierszy.
W tym przypadku interesują nas mężczyźni, więc zostawimy sobie tylko widoczną
metrykę mężczyzn, a w wieku musimy zaznaczyć wiele elementów i odznaczyć
osoby, które są poniżej 40 roku życia.
Możemy zrobić to przy pomocy spacji.
Czyli teraz wciskamy strzałka w dół, spacja, strzałka w dół,
spacja, strzałka w dół, spacja.
I w ten oto sposób możemy sobie szybko strzałką w dół i spacją odznaczyć
wszystkie osoby przed czterdziestką.
Kliknijmy sobie ok.
No i teraz dopiero ten wynik działa w sposób prawidłowy.
To jest wartość zarobków osób po 40 roku życia w naszej organizacji.
To tyle w temacie tabel przestawnych.
Zapraszam Cię do kolejnego materiału, którym są zadania do zobaczenia.