Tabela przestawna w Excelu

Tabela przestawna to jedno z najbardziej przydatnych narzędzi w Excelu. Ma bardzo dużo możliwości, będąc stosunkowo łatwą w użyciu. Może być użyteczna praktycznie w każdej branży i w dowolnym dziale w ramach przedsiębiorstwa. Tabela przestawna jest też narzędziem, o które kandydaci do pracy są najczęściej pytani podczas testów znajomości programu Excel. Czytaj poniżej do czego dokładnie takie tabele się przydają oraz jak ich używać.

Do czego służy tabela przestawna?

Najczęściej, podczas pracy w Excelu, spotykamy się z tabeli pełnymi różnych danych, zgadzasz się z tym? Nieważne czy te tabele są proste, czy skomplikowane – tabele przestawne ułatwiają ich analizowanie, wyszukiwanie w nich danych, porządkowanie danych, grupowanie ich i podsumowywanie znajdujących się tam informacji.

Tabele przestawna tworzysz właśnie z takiej zwykłej, dowolnej tabeli. W tej przestawnej, możesz wówczas w bardzo prosty sposób różnie prezentować (przestawiać) dane, tak, aby wszystko czego potrzebujesz dowiedzieć się z tej tabeli, było przejrzyste. Można wiec powiedzieć, że tabela przestawna jest niejako kopią tej tabeli zwykłej, ale bardziej inteligentną.

Można wyróżnić kilka najczęstszych zadań w Excelu, do których używa się tabel przestawnych:

Podsumowanie danych za pomocą tabeli przestawnej

Za pomocą tabeli przestawnej zaprezentujesz sumy podobnych do siebie elementów z tabeli źródłowej. Na przykładzie poniżej, podsumowana jest liczba pracowników w każdym z działów pewnego przedsiębiorstwa, a także całkowita liczba zatrudnionych w całej firmie.

Rysunek 1. Podsumowanie tabeli, pokazujące liczbę pracowników.

Analiza danych

Naturalnym celem używania tabeli przestawnej jest analizowanie danych. Gdy jest ich bardzo dużo w zwykłej tabeli, to ciężko wyciągać z nich wnioski. Tabela przestawna pozwala tak przedstawić te dane, aby móc wyciągnąć z nich potrzebne informacje. W przykładzie poniżej, zaprezentowane jaki jest udział kosztów wynagrodzeń na poszczególnych stanowiskach w całkowitej kwocie wynagrodzeń. Widać z ten sposób „na jakich pracowników” wydawana jest duża, a na jakich mała część pieniędzy.

Rysunek 2. Analiza udziału wynagrodzeń.

Filtrowanie i segregacja danych za pomocą tabeli przestawnej

Tabela przestawna pozwala zgrupować duże ilości danych w krótkim czasie, tak żeby „wyciągnąć” z nich te informacje, które są Ci potrzebne. Poniżej widać, jak pracownicy zostali pogrupowani według wielkości wynagrodzenia, w przedziałach co 1.000 zł. W tabeli wyświetlono ilu jest pracowników, których wynagrodzenie mieści się w podanym przedziale.

Rysunek 3. Ilość pracowników w podziale na grupy wielkości wynagrodzenia.

Rekonfiguracja danych

Podczas analizowania danych, często potrzeba spojrzeć na nie „pod innym kątem”. W tabeli przestawnej można za pomocą kilku kliknięć zmienić sposób ich prezentowania. Na zdjęciu poniżej możesz zobaczyć dwa razy tę samą tabelę przestawną, ale za każdym razem prezentuje inne informacje. Po lewej stronie jest średnie wynagrodzenie w poszczególnych działach firmy, natomiast po prawej jest średnie wynagrodzenie na wskazanych stanowiskach. W tabeli przestawnej, w ciągu kilku sekund, można zmienić prezentowane dane z jednych na drugie.

Rysunek 4. Dwie różne formy tej samej tabeli przestawnej.

Porównywanie danych w tabeli przestawnej

W tabeli przestawnej możesz porównywać różne kategorie danych ze sobą. W przykładzie poniżej porównuje się ilość mężczyzna i kobiet zatrudnionych na poszczególnych stanowiskach – przykładowo, jaki jest udział kobiet i mężczyzn, jeśli chodzi o stanowisko „Asystent”. Dane zostały zaprezentowane procentowo.

Rysunek 5. Porównanie ze sobą różnych kategorii danych.

Sprawdzanie poprawności danych

Tabela przestawna może ułatwić sprawdzenie, czy w danych nie ma błędów, np. czy nr PESEL, który powinien być unikatowy, nie pojawia się w tabeli wielokrotnie. W przykładzie poniżej, sprawdzane są właśnie PESELe, a następnie filtrowane są wartości większe niż 1, co oznacza że dany PESEL wystąpił więcej niż jeden raz.

Rysunek 6. Szukanie powtarzających się numerów PESEL w tabeli Excela.

Tworzenie raportów i wykresów

Tabela przestawna jest estetyczna i łatwa w użyciu, dzięki czemu może być gotowym raportem. Bardzo łatwo można dodać do niej wykres przestawny, czy choćby takie narzędzie jak fragmentator, które sprawiają, że arkusz Excela z tabelą przestawną jest bardzo miejscem, w którym można przedstawiać wszystkie ważne informacje.

Rysunek 7. Tabela przestawna i wykres przestawny w Excelu.

Jak wstawić tabelę przestawną w Excelu?

Po pierwsze, musisz mieć jakąś zwykłą tabelę, którą chcesz w jakiś sposób przeanalizować. Zaznaczasz tę tabele, przechodzisz do zakładki Wstawianie i klikasz przycisk Tabela przestawna.

Rysunek 8. W zakładce Wstawianie znajduje się przycisk do wstawiania tabeli przestawnej.

W oknie, które się pojawi, zazwyczaj nic nie musisz zmieniać, dlatego ze jedna z opcji pozwala zmienić wskazanie miejsca, gdzie znajduje się Twoja zwykłą (źródłowa) tabela, a Ty przecież już zaznaczyłeś/aś już właściwą tabelę. Druga z opcji pozwala wybrać miejsce gdzie będzie wstawiona tabela przestawna, ale na początku najlepiej wstawiać ja do zupełnie nowego arkusza Excela, i to jest zawsze domyślnie wybrana opcja.

Rysunek 9. Okno tworzenia tabeli, czyli zakres tabeli źródłowej i miejsce wstawienia tabeli przestawnej.

W tym nowym arkuszu tworzy się po lewej stronie zalążek tabeli przestawnej, póki co, jeszcze pusty. Gdy klikniesz w jego wnętrze, to po prawej stronie znajduje się panel sterujący tabelą przestawną. Czytaj w kolejnych sekcjach jak używać stworzonej już tabeli przestawnej.

Rysunek 10. „Zalążek” tabeli po lewej i panel po prawej stronie.

Jak działa tabela przestawna w Excelu?

W panelu mamy u góry listę wszystkich pól tabeli przestawnej, które należy wstawić w odpowiednie obszary tabeli przestawnej, znajdujące się w dolnej części tego panelu.

Rysunek 11. Pola i obszary w panelu tabeli przestawnej.

Ale czym są te pola? Otóż każde z pol to jest jedna z kolumn Twojej źródłowej tabeli. Każde pole nazywa się tak, jak nagłówek jednej z kolumn. Czyli przesuwając gdzieś w tabeli przestawnej pole, tak naprawdę działasz na całej kolumnie tabeli źródłowej. Jest to bardzo ważne w zrozumieniu sposobu działania tabel przestawnych. Jeśli działamy zawsze całą kolumną, to w jednej kolumnie powinien być jeden typ informacji (np. tylko stanowiska pracowników, albo tylko wynagrodzenia pracowników, itd). Innymi słowy, każda kolumna tabeli źródłowej opisuje jakąś jedna cechę, np gdy masz tabele z pracownikami, to każdy wiersz to jest osobny, jeden pracownik, a w jednej kolumnie są same nazwiska pracowników, druga kolumna to ich PESELe, trzecia kolumna to płeć, czwarta kolumna to data urodzenia, itd.

Rysunek 12. Przykładowa tabela źródłowa.

Można powiedzieć, że jest to taka klasyczna forma tabeli. Dlaczego tak się rozpisuję na ten temat? Bo często pracujemy tez na innych tabelach, gdzie informacje są bardziej wymieszane w ramach kolumn lub wierszy, np. gdy w jednej kolumnie występują nazwy działów firmy, a pod nimi są nazwiska pracowników tych działów – wówczas, w jednej kolumnie mamy różne typy informacji i taka tabele trudno jest w sensowny sposób analizować w tabeli przestawnej (choc teoretycznie można analizować każda tabelę). W tym miejscu, więc chcę sprostować pewną kwestię – tabelę przestawna można skutecznie zastosować do bardzo wielu różnych tabel, ale nie do wszystkich.

Rysunek 13. W pierwszej kolumnie są zarówno nazwy działów, jak i nazwiska ich pracowników.

No dobra, to jak przestawiać te pola do obszarów tabeli przestawnej? O tym czytaj w kolejnych sekcjach tego artykułu.

Jak używać tabeli przestawnej?

Już wiesz jak wstawić tabelę przestawna, a teraz dowiesz się jak odpowiednio uzupełnić ją danymi. Pola możesz przesuwać do czterech różnych obszarów.

„Wiersze” tabeli przestawnej

To, co wstawisz w tym obszarze, pojawi się w tabeli przestawnej w kolejnych wierszach. Są to wartości, które Excel znalazł w kolumnie Twojej tabeli źródłowej. Zwróć uwagę na to, że w kolejnych wierszach nie powtarza się żadna wartość. Dzieje się tak nawet, gdy w tabeli źródłowej te wartości się powtarzały.

Rysunek 14. Pole wstawione do Wierszy. Żadna wartość się nie powtarza.

Dzieje się tak dlatego, że tabela przestawna często służy do podsumowania podobnych danych, jeśli więc jakaś wartość powtarza się np. 50 razy, to w tabeli przestawnej pojawi się tylko 1 raz i będzie można podsumować 50 wartości. Przykładowo, jest 50 pracowników pewnego działu w firmie i zastanawiasz się ile w sumie pieniędzy jest wydawanych na ich wynagrodzenia – taką sumę zobaczysz w tabeli przestawnej.

„Kolumny” w tabeli przestawnej

Jeśli wstawisz cos do obszaru kolumn, to zobaczysz listę wartości w kolejnych kolumnach tabeli przestawnej. Działa to analogicznie do obszaru wierszy, czyli kolejne wartości to będą dane z tabeli źródłowej.

Rysunek 15. Pole wstawione do Kolumn w tabeli przestawnej.

Mając pola wstawione zarówno do wierszy, jak i do kolumn, możesz analizować dane pod kątem tych dwóch różnych parametrów/cech.

Rysunek 16. Pola zarówno w Wierszach jak i Kolumnach tabeli przestawnej w Excelu.

Obszar „Wartości” w tabelach przestawnych Excela

Obszar „Wartości” jest odpowiedzialny za wyświetlanie liczb w tabeli przestawnej. Z tego powodu, pola, które wstawiamy do Wartości, to zazwyczaj pola z liczbami (wynagrodzenia pracowników, wartość sprzedaży, koszty, liczba klientów i inne dane liczbowe, które przechowujemy w plikach Excela). Zdarzają się jednak sytuacje, gdy wstawia się tam pola, które wcale nie zawierają liczb, ale jest za wcześnie, żeby wgłębiać się w ten temat.

Wstawione do Wartości liczby są w wybrany przez Ciebie sposób podsumowywane (najczęściej jest to zwykła suma). W przykładzie z rysunku 16 widać wynagrodzenia, które są sumowane dla poszczególnych stanowisk i działów w firmie.

„Filtr” w tabeli przestawnej

Jeśli chcesz w swojej tabeli zobaczyć mniej danych, to możesz użyć obszaru „Filtru”. Pola, które tam wstawisz, pojawią się nad tabelą przestawną wraz z listą rozwijaną, z której możesz wybrać wartości, po których chcesz przefiltrować. Jeśli wybierzesz, jak w przykładzie poniżej, dział Administracja, to w tabeli przestawnej zobaczysz wynagrodzenia pracowników tylko z działu Administracji.

Rysunek 17. Obszar filtru.

Prawidłowe źródło w Excelu dla tabeli przestawnej

Należy pamiętać, że aby tabela przestawna była dla nas przydatna, to powinna być zbudowana na odpowiedniej tabeli źródłowej. Poniżej wymieniamy kilka najważniejszych cech „dobrej” tabeli źródłowej:

• Klasyczna forma – każdy wiersz to jakiś element (dokument, pracownik, klient), a każda kolumna, to informacje na temat tego elementu (data wystawienia, wynagrodzenie, nazwa klienta).
• Powtarzające się elementy w tabeli – aby korzystanie z tabeli przestawnej przyniosło realne korzyści, to przynajmniej w niektórych kolumnach powinny być powtarzające się elementy – według tych elementów można dale w tabeli przestawnej grupować.
• Kolumny muszą mieć nagłówki – każda kolumna tabeli źródłowej musi mieć nagłówek, czyli komórkę z dowolną zawartością (nie pustą). W przeciwnym razie nie będzie możliwe wstawienie tabeli przestawnej.
• Pojedyncze nagłówki w kolumnach – tabela przestawna akceptuje tylko pojedyncze nagłówki kolumn. Jeśli w tabeli źródłowej jest więcej niż jeden wiersz z nagłówkami, to do tabeli przestawnej wskazać można i tak tylko jeden – ten znajdujący się najniżej (tuż nad danymi) – niech w nim zawierają się najważniejsze dla Ciebie nagłówki, które to mają pojawić się w tabeli przestawnej.
• Nie może być pustych kolumn – puste kolumny nie mają nagłówków – z powodu braku nagłówków tabela przestawna nie zostanie stworzona.
• Unikać pustych wierszy – dlaczego unikać pustych wierszy w tabeli źródłowej? Jeśli nie zaznaczasz całej tabeli źródłowej przed stworzeniem tabeli przestawnej, ale zaznaczasz pojedynczą komórkę (w oczekiwaniu, że Excel użyje i tak całej tabeli), to pusty wiersz spowoduje, że tylko część tabeli zostanie użyta w tabeli przestawnej Nawet jeśli wprost wskażesz całą tabelę źródłową (w tym puste wiersze) przy tworzeniu tabeli przestawnej, to w przestawnej będą elementy o nazwie „puste”, które „zaśmiecają” tabelę przestawną.
• Jeden typ danych w kolumnie – tabela przestawna niektóre operacje wykonuje tylko na określonym type danych, np. tylko na datach, albo tylko na liczbach. Jeśli spróbujesz zgrupować daty, a „wkradnie” tam się chociaż jedna komórka z tekstem, to grupowanie dat nie będzie możliwe.
• Prawidłowy typ danych w kolumnie – czasami dane wyglądają na prawidłowe, ale wcale takie nie są – przykłady: kolumna zawiera liczby, ale są one traktowane jak tekst; kolumna zawiera daty, ale są one traktowane jak tekst. Dzieje się tak zazwyczaj wtedy, gdy dane generowane są z jakiegoś systemu, a system ten, sformatuje je jako tekst (dzieje się tak dosyć często) i tak zostaną przeniesiona do arkusza Excela. Problem jest taki, że tabela nie będzie w stanie zsumować „tekstowych liczb”, tylko jako wynik pokaże „0”, nawet jeśli dla Ciebie te liczby wyglądają zupełnie ok.