Tabela przestawna Excel – 5 trików, które musisz znać

Tabela przestawna Excel – 5 trików, które musisz znać

Tabela przestawna Excela kryje w sobie wielką moc. Te 5 trików ułatwi Twoją pracę z danymi w Excelu i ułatwi Ci pokonywanie testów Excela z rozmowy o pracę.

1. Tabela przestawna Excela a lista unikatów

Jednym ze sposobów otrzymania unikatowej listy wyników są tabele przestawne Excela. Wystarczy skorzystać z tego narzędzia, kolejno zaznaczyć nagłówek kolumny, z której unikaty chcemy uzyskać i gotowe. Można wyniki skopiować i tabelę usunąć lub pozostawić — na wypadek, gdybyś musiał zmienić dane źródłowe.
Pamiętaj! Jeśli dokonasz zmiany w danych źródłowych, powinieneś odświeżyć tabele przestawne Excela.

Do czego między innymi mogą przydać Ci się listy unikatowe w raportowaniu?

  • jeśli dokonujesz analizy pod pewnymi kryteriami np. wartość sprzedaży dla poszczególnego regionu,
  • do tworzenia unikatowych list pracowników, produktów, kategorii i tak dalej,
  • do tworzenia listy wybieranych w komórkach (tzw. poprawność danych).

2. Wielokrotne przeciąganie kolumn w tabelach przestawnych Excela

Analiza w tabelach przestawnych Excela nie ogranicza nas do jednokrotnego użycia konkretnej kolumny. Jeśli chcemy popatrzeć na dane wartościowe w różnych ujęciach, warto dodać badaną kolumnę do naszego zestawienia więcej niż raz. Możemy zmienić sposób kalkulacji pola wartości tabeli przestawnej. W przykładzie zmieniłem sposób kalkulacji z SUMA na LICZBA, co spowodowało, że obok wyników sprzedażowych poszczególnych osób mieliśmy też informację, ile razy ich wynik występuje w tabeli źródłowej. To oczywiście nie wszystkie możliwości, jakie dają nam pola wartości tabel przestawnych Excela.

W czym może pomóc ta możliwość?

  • jeśli oprócz wartości zamówień jesteś też zainteresowany ich liczbą, może warto użyć możliwości z przykładu?
  • suma nie pokazuje wszystkiego — jeśli chcesz odpowiedzieć na pytanie „których zamówień / pracowników / wpisów mam najwięcej?” opcja zmiany kalkulacji pól wartości tabel przestawnych jest rozwiązaniem,
  • możesz do Twojego zestawienia dodać sumy narastające (bieżące) bez utraty widoku poszczególnych wartości (o tym więcej w dalszej części tego wpisu).

3. Strony filtru raportów a tabela przestawna Excel

Raport dla każdego z miast, regionów, działów czy czego tylko sobie zapragniesz? Ze stronami filtru raportów tabel przestawnych Excela to nic trudnego.

  • Dodaj kolumnę, po której chcesz kategoryzować swoje raporty, do pola FILTRY tabeli przestawnej.
  • Kliknij na tabelę przestawną.
  • Udaj się na kartę ANALIZA TABELI PRZESTAWNEJ.
  • Wybierz TABELA PRZESTAWNA, a następnie OPCJE.
  • Kliknij POKAŻ STRONY FILTRU RAPORTU…
  • Wybierz który filtr ma być tym, który kategoryzuje Twoje raporty (w przykładzie mamy jeden).
  • Zatwierdź i zwróć uwagę, że do Twojego pliku zostały automatycznie dodane karty raportów z tabelami przestawnymi Excela.

Czy w Twojej głowie już rodzi się pomysł na zastosowanie tej funkcjonalności tabel przestawnych Excela?

  • jeśli chcesz stworzyć raporty sprzedaży dla swoich dostawców, wystarczy, że przygotujesz jeden szablon i rozrzucisz go automatycznie filtrem strony raportu po filtrze DOSTAWCY,
  • możesz zrobić to samo po np. działach Twojej firmy albo rodzajach podpisanych umów czy regionach,
  • rozbicie Twoich raportów może zostać wykonane również po miesiącach, kwartałach czy latach — wszystko zależy od układu Twoich danych źródłowych.

4. „Pokaż wartości jako” czyli tajna broń tabel przestawnych Excela

I jest obiecane wcześniej POKAZYWANIE DANYCH JAKO. To mało znana i niedoceniania opcja tabeli przestawnych Excela. Dzięki niej możesz z łatwością stworzyć zestawienie pokazujące sumy narastające czy narastający udział konkretnych pozycji w całości. Prezentowany poniżej przykład to jedynie namiastka możliwości, jakie daje Ci opcja POKAŻ DANE JAKO. Do jej wachlarza należą też takie możliwości jak pokazywanie:

  • procentów sumy końcowej
  • procentów sumy z kolumny czy wiersza
  • procentów sumy kolumny czy wiersza nadrzędnego
  • procentów różnicy czy wartości różnicy między poszczególnymi wierszami w tabelach przestawnych

Jak widać, nie tylko formuły mają w sobie ukrytą moc. Do czego możesz użyć tej możliwości tabel przestawnych Excela?

  • sumy narastające w połączeniu z tabelami przestawnymi zaoszczędzą masę czasu – w końcu nie musisz już przeciągać formuły :). Wystarczy wkleić nowe dane źródłowe i odświeżyć tabelę przestawną,
  • POKAŻ DANE JAKO świetnie sprawdza się, jeśli rozbijasz dane po dacieMożesz w ten sposób łatwo śledzić przyrosty,
  • wyżej zaprezentowane narzędzie przyda Ci się też do analizy Parto,
  • to nie wszystkie możliwości, jakie daje opcja POKAŻ DANE JAKO. To bardzo złożona funkcjonalność Excela i więcej dowiesz się o niej w kursie Tabele Przestane Excela – OD ZERA DO MASTERA.

5. Możesz korzystać z dynamicznych wykresów przestawnych

Grupowanie to moim zdaniem jedna z najlepszych możliwości, jakie daje nam program Excel. W wersji 2016 i wyżej, jeśli użyjemy kolumny z prawidłowo sformatowaną datą, tabela przestawna dokona grupowania sama. W każdej wersji Excela od 2007 w górę można to też zrobić tak, jak na przykładzie. Grupować możemy:

  • daty
  • liczby
  • tworzyć własne grupy na podstawie wartości tekstowych

Jak wykorzystać grupowanie tabeli przestawnej Excela?

  • analiza miesięcy, kwartałów czy lat nie powinna teraz dla Ciebie stanowić problemu,
  • możesz tworzyć własne grupy np. kanały Internetu (social media, strona www, mailing) i kanały tradycyjne (telefon, FAX, spotkania) – mając tak stworzone grupy, możesz stwierdzić, która grupa kanałów lepiej u Ciebie działa,
  • możesz stworzyć przedziały cenowe, aby sprawdzić, w którym z nich jest najwięcej zamówień, albo stworzyć przedziały pensji, aby zobaczyć, w którym z nich pracuje najwięcej pracowników.

Podsumowanie

To, co zaprezentowałem powyżej to jedynie 5 trików tabel przestawnych Excela. To narzędzie ma olbrzymi potencjał i możliwości. Szkoda, że tak niewielu z nas go odkrywa. Znając dashboardy, możliwości odświeżenia tabel czy modele danych praca w Excelu będzie zajmować Ci jeszcze mniej czasu. Na koniec dnia pracodawca nie płaci Ci za robienie plików. Płaci Ci za dobre decyzje biznesowe i to te dobre decyzje będą Twoimi argumentami na rozmowie o podwyżce czy awansie.

Tabela przestawna Excel – i włosy jeżą się na ręce? 🙂 Poznaj pełen potencjał tabel przestawnych Excela. Odwiedź stronę kursu: Tabele przestawne Excel – OD ZERA DO MASTERA. Wyżej wymienione triki to tylko 5 z 50, które znajdziesz w kursie.

Umiesz już korzystać z wyżej wymienionych rozwiązań?

Michał Kowalczyk

Cała moja kariera zawodowa związana jest z cyframi i pracą z programem Excel. W każdej z firm, których pracowałem, byłem uznawany za specjalistę i „tego gościa od Excela”. Swoją wiedzą dzielę się z szerszym gronem przy pomocy Facebooka.