Potęga tabel przestawnych w Excelu – jak szybko analizować dane jak ekspert?
Masz dość przekopywania się przez setki wierszy danych? Tabele przestawne to narzędzie, które w kilka kliknięć pozwala zamienić surowe dane w czytelne raporty, wykresy i zestawienia. Niezależnie od tego, czy analizujesz sprzedaż, budżet czy wyniki ankiet – z tabelami przestawnymi zrobisz to szybciej, prościej i skuteczniej. Przekonaj się, jak dużo możesz zyskać, korzystając z tej funkcji Excela!
W dalszej części poradnika:
Jak zrobić tabelę przestawną w Excelu krok po kroku?
Tabela przestawna to narzędzie w Excelu, które umożliwia szybkie podsumowanie, analizę i porządkowanie dużych zbiorów danych. Pozwala w łatwy sposób grupować informacje, liczyć sumy, średnie, tworzyć zestawienia i raporty – wszystko bez potrzeby pisania skomplikowanych formuł.
Dla przykładu poniżej znajduje się tabela z danymi o sprzedaży produktów. Naszym zadaniem jest wykonanie zestawienia w którym pokażemy łączną wartość sprzedaży dla każdej kategorii. Zapewne wiesz jak to rozwiązać wystarczy, że zastosujemy funkcję SUMA.JEŻELI(). Oczywiście w zależności od tego co będziemy chcieli obliczyć i stopnia skomplikowania kryteriów liczenia możemy stosować takie funkcje jak SUMA.WARUNKÓW(), LICZ.JEŻELI() itp. Ale możemy też zastosować tabele przestawne i nie martwić się o to jaką funkcję zastosować.
Aby utworzyć tabelę przestawną:
- Zaznaczamy dowolną komórkę w zakresie danych
- Wybieramy polecenie Wstawianie > Tabela przestawna
- W oknie, które się pojawiło musimy potwierdzić zakres danych a następnie wybrać gdzie ma pojawić się tabela przestawna Nowy arkusz lub Istniejący arkusz (wtedy wybieramy komórkę w której ma pojawić się tabela przestawna. Ja wybiorę aby tabela przestawna pojawiła się w nowym arkuszu i zatwierdzam mój wybór przyciskiem OK.
Na ekranie pojawi nam się nowy arkusz z pustą tabelą przestawna a po prawej stronie zobaczymy nowe okno Pola tabeli przestawnej. Te dwa elementy są ze sobą powiązane – tabelę przestawną budujemy za pomocą przeciągnięcia odpowiednich Pól (kolumn danych) do jednego z 4 obszarów:
- Filtry
- Kolumny
- Wiersze
- Wartości
Wracając do naszego przykładu aby przedstawić sumę wartości sprzedaży dla każdej kategorii wystarczy że:
- Przeniesiemy Pole (Kategoria) do obszaru Wiersze
- Przeniesiemy Pole (Sprzedaż) do obszaru Wartości
W ten sposób Tabela przestawna automatycznie podsumowała nam wartości sprzedaży dla produktów
Zdobądź jedną z najważniejszych umiejętności w pracy biurowej – naucz się Excela od zera
Kurs Excel Podstawowy – Zbuduj pewność siebie w pracy z Excelem od pierwszej lekcji. Nauczysz się tworzyć formuły, elegancko formatować arkusze, analizować dane i ogarnąć codzienne zadania szybciej i sprawniej. Wszystko pokazane prosto na przykładach i bez lania wody.
Obszar filtrowania tabeli przestawnej
Podsumowanie wyników sprzedaży możemy filtrować na podstawie innych pól dla przykładu aby sprawdzić wynik sprzedaży dla regionu północnego wystarczy, że:
- przeciągniemy pole Region do obszaru Filtry.
- a następnie wybierzemy z listy rozwijalnej pola filtrowania nad tabelą przestawną interesujący nas region lub grupę regionów.
Jak utworzyć tabelę przestawną z kilku Pól danych
Zamiast filtrowania możemy dodać drugi poziom kryteriów obliczeń tabeli przestawnej. Każdy obszar danych może zawierać więcej niż jedno pole. Teraz utworzymy drugi poziom sumowania i oprócz wartości dla Kategorii chcemy zobaczyć wyniki dla każdego regionu. W tym celu należy przenieść pole Region do obszaru Wiersze (pod polem Kategorii) dzięki temu otrzymamy dodatkowe sumowanie dla wszystkich kryteriów z kolumn Kategorie i Region jak poniżej.
Tabela przestawna krzyżowa dla lepszej analizy danych
Gdy chcemy przedstawić podsumowanie sprzedaży zarówno dla Kategorii i Regionu warto wykonać krzyżową tabelę przestawną. Wystarczy, że przeciągniemy jedno z pól np. pole Kategoria do obszaru Kolumny a drugie pole tj. Kategorię pozostawimy w obszarze Wierszy. Dzięki temu uzyskamy efekt tabeli krzyżowej, pozwala ona na lepszą analizę porównawczą. W celu lepszej czytelności wyników w przykładzie dodałem formatowanie warunkowe za pomocą pasków danych.
Sumowanie kilku kolumn w tabeli przestawnej
W tabeli przestawnej możemy prowadzić obliczenia dla kilku kolumn. Najpierw usuniemy pole Region z obszaru Wiersze a pole Kategoria przeniesiemy do obszaru Wiersze i będziemy chcieli obliczyć ilość i sumę sprzedaży produktów. W tym celu przenosimy do obszaru Wartości pole Ilość. Teraz widzimy, że w tabeli przestawnej mamy przedstawioną ilość i sumę sprzedaży w podziale na kategorie.
Zmiana rodzaju obliczeń. Jak obliczyć średnią w tabeli przestawnej.
Tabela przestawna standardowo liczy nam dla pola Wartość:
- sumę dla pól (kolumn) zawierających liczbę
- ilość jeśli pole (kolumna) jest tekstem.
Ale możemy zmienić domyślny sposób liczenia. Dla przykładu będę chciał przedstawić w tabeli przestawnej zamiast sumy wartości średnią wartość.
W tym celu klikam prawym przyciskiem myszy (ppm) na pole Suma Sprzedaży i wybieram Ustawienia pola wartości
Pojawi mi się okno z którego mogę wybrać sposób obliczeń dla pola wartości tutaj wybieram Średnia.
Po zmianie metody obliczeń tabela przestawna wygląda następująco:
Daty w tabeli przestawnej w Excelu
Daty są specjalnie traktowane przez tabelę przestawną. Daty możemy przedstawić jako:
- dni
- miesiące
- kwartały
- lata
Przejdźmy do przykładu w którym będziemy chcieli zobaczyć jak prezentowała się średnia sprzedaż w każdym miesiącu.
W polu Wartości pozostawiam Średnią Sprzedaż a do obszaru Wiersze przenoszę Pole Data Sprzedaży. Zauważymy że pojawią nam się dodatkowe pola Dni (Data Sprzedaży) oraz Miesiące (Data Sprzedaży). Są to pola utworzone na podstawie tabeli Data Sprzedaży i od teraz możemy używać ich niezależnie od innych pól daty.
A w samej tabeli przestawnej zobaczysz, że uzyskaliśmy średnią sprzedaż w podziale na miesiące.
Zauważ, że obok nazwy miesiąca znajduje się mały plusik którym możemy zwiększać lub zmniejszać szczegółowość wyników.
Excel na wyższym poziomie. Pracuj szybciej i mądrzej
Kurs Excel Średniozaawansowany – poznaj funkcje, narzędzia i techniki, które pozwolą Ci analizować dane szybciej i pracować w Excelu dużo sprawniej. Naucz się automatyzować zadania i wykorzystywać Excela na wyższym poziomie.
Grupowanie w tabeli przestawnej na podstawie dat
W poprzednim dziale udało nam się utworzyć tabelę przestawną pokazującą średnią wartość sprzedaży dla miesięcy. Teraz chciałbym zmienić szczegółowość i pokazać wyniki dla kwartałów. W tym celu musimy zmienić grupowanie Pola Data Sprzedaży.
1. Klikamy prawym przyciskiem myszy na datę
2. Wybieramy Grupuj…
3. W nowym oknie na liście wyboru zaznaczamy Kwartały oraz usuwamy zaznaczone automatycznie Dni i Miesiące. Zatwierdzam przyciskiem OK.
Po wykonaniu powyższych czynności zniknęły nam pola Dni (Data Sprzedaży) i Miesiące (Data Sprzedaży) za to pojawiło się nowe pole Kwartały (Data Sprzedaży).
A sama tabela przestawna zmieniła swój wygląd i otrzymaliśmy wynik dla Kwartału1.
Aktualizowanie nowych danych w tabeli przestawnej
W poprzednim przykładzie utworzyliśmy zestawienie pokazujące średnią sprzedaż dla kwartału1 z biegiem czasu do naszego zakresu danych dochodzą nowe pozycje, które będziemy chcieli podsumować w tabeli przestawnej, do zakresu danych dopisałem dwie pozycje, które pojawiły się w drugim kwartale roku.
Po dopisaniu nowych pozycji tabela przestawna nie zmieniła swojego wyglądu, dzieje się tak ponieważ zakres danych tabeli przestawnej nie obejmuje nowo wprowadzonych danych a tabela nie została odświeżona.
Każdorazowo po wprowadzeniu nowych danych lub modyfikacji danych tabelę przestawną należy odświeżyć za pomocą skrótu klawiaturowego Alt + F5 lub klikając prawym przyciskiem myszy na tabelę przestawną.
Jeśli nowo wprowadzone dane znajdują się w tabeli Excelowej to dane w tabeli przestawnej zostaną zaktualizowane.
A co w sytuacji gdy dane nie znajdują się w tabeli Excelowej?
W naszym przypadku nie utworzyliśmy takiej tabeli Excelowej a tabela przestawna bazuje na zakresie danych. W takiej sytuacji musimy ręcznie zaktualizować zakres danych:
- Zaznaczamy dowolną komórkę w tabeli przestawnej
- Wybieramy polecenie Zmień źródło danych znajdujące się na karcie Analiza tabeli przestawnej
- Zmieniamy zakres danych tak aby obejmował nowe wiersze
Po zatwierdzeniu zakresu tabela przestawna zostanie zaktualizowana o nowe pozycje danych
Jak przygotować dane do tabeli przestawnej?
W tym artykule bazujemy na danych, które są przygotowane prawidłowo pod możliwość jej analizy w tabeli przestawnej. Nie zawsze (a przeważnie najczęściej) sprawa jest tak prosta jak w podanym powyżej przykładzie. Częstym problemem już na samym początku stosowania tabel przestawnych jest to, że dane nie mają formy tabelarycznej. Poniżej przedstawiam zasady przygotowania danych do tabeli przestawnej:
- Nazwanie nagłówków kolumn – nazwij nagłówki krótko i zwięźle. Nazwy kolumn będą reprezentowała dane zawarte w każdej z kolumn tabeli.
- Nagłówek musi znajdować się w jednym najwyższym wierszu – tabela przestawna nagłówki w drugim wierszu odczyta już jako dane co może zakłócić obliczenia.
- Jeden typ danych w każdej kolumnie – każda kolumna powinna zawierać tylko jeden ty danych określający parametr np. kolor, wielkość, lokalizację lub wartość np. suma sprzedaży czy ilość.
- Określ format danych w kolumnach – zachowaj jeden format danych w kolumnie np. liczba, tekst, data.
- Brak scalonych komórek w tabeli – usuń scalenie danych jeśli gdziekolwiek występują w zakresie danych.
- Usuń puste wiersze – często w zakresach danych stosuje się puste wiersze do oddzielenia danych, usuń je z zakresu danych.
- Dane przechowuj w tabelach Excela – po utworzeniu tabeli danych, będziesz mógł dodawać nowe dane, które automatycznie objęte zostaną zakresem tabeli przestawnej.
Przydatnym narzędziem Excela, które pozwala zorganizować duże i trudne zbiory danych do formy tabelarycznej jest edytor Power Query.
Tabele przestawne dla kilku źródeł danych (Power Pivot)
Dotychczas omawialiśmy sytuacje tworzenia tabeli przestawnej na podstawie jednego źródła danych. A co jeśli mamy kilka źródeł danych. Oprócz aktualnej tabeli z pozycjami sprzedażowymi, mamy jeszcze specyfikację produktów jak poniżej.
Na podstawie tych dwóch tabel chcemy obliczyć wartość sprzedaży dla każdej marki produktów.
W tym przypadku musimy zastosować dodatek do Excela, Power Pivot. Tutaj zobacz jak włączyć Power Pivot w Excelu.
1. W pierwszym kroku należy dodać dwie tabele do modelu danych. Zaznacz dowolną komórkę zakresów danych, następnie przejdź do kart Power Pivot i wybierz polecenie Dodaj do modelu danych. Jeśli dane nie znajdują się w tabelach Excelowych, zostaniesz poproszony o ich utworzenie. Nazwij te tabele Produkty i Sprzedaż.
2. Następnie wybierz polecenie Zarządzaj na karcie Power Pivot.
3. W edytorze Power Pivot przejdź do polecenia Widok Diagramu na karcie Narzędzia główne i wykonaj relację pomiędzy tabelami na podstawie kolumny Relacje tworzysz poprzez przeciągnięcie nazwy kolumny z jednej tabeli do drugiej Produkt – Produkt.
4. Teraz wróć do Excela i utwórz nową tabelę przestawną z modelu danych. Wskaż lokalizację nowej tabeli przestawnej.
5. Twoim oczom powinna pokazać się pusta tabela przestawna a w oknie pól tabeli przestawnej zobaczysz dwie nazwy tabel, których wewnętrzne nagłówki możesz rozwijać. Wystarczy, że przeciągniesz pole Marka do obszaru wierszy a Sprzedaż do obszaru wartości i zadanie jest gotowe 😊
To jest prosty przykład zastosowania modelu danych. Więcej o analizie danych na podstawie modeli danych możesz dowiedzieć się, zapisując się na kurs Power Pivot i DAX.
Kurs Excel Power Pivot i DAX nauczy Cię jak wykorzystać Power Pivot do budowy zaawansowanych modeli danych, które działają szybciej, są bardziej elastyczne i dają znacznie więcej możliwości niż tradycyjne formuły Excela.
Przykładowe dane do tabeli przestawnej Excel - do pobrania
Inne poradniki
Przydatne Triki w Excelu, Które Usprawnią Twoją Pracę!
Najlepsze triki w Excelu. Odkryj 5 niezwykłych trików w programie Excel, które mogą znacząco usprawnić Twoją pracę. Dowiedz się, jak wykorzystać niestandardowe skróty klawiaturowe, głęboko ukryć arkusze czy szybko znajdować różnice w danych. Poznaj także sposób na zbiorcze wklejanie danych oraz tworzenie dwóch nagłówków w jednej komórce. Zwiększ swoją efektywność w Excelu już dziś!
Power Query dynamiczne źródło danych – zmiana źródła z listy
Automatyczna zmiana pliku z którego pobieramy dane do Excela.
Jak zrobić listę rozwijalną w Excelu? Praktyczny przewodnik krok po kroku
Listy rozwijalne w Excelu zabezpieczają przed błędami przy wpisywaniu danych w komórkach oraz zaoszczędzają czas potrzebny na wprowadzenie danych. W tym nagraniu przedstawię, jak wykonać listę rozwijalną w Excelu a do tego dodam kilka przydatnych sztuczek związanych z listami rozwijalnymi.
Pobieranie Danych Ze Strony Internetowej Do Excela – Power Query i Api
Zobacz, jak szybko pobierać dane ze stron www do Excela – od prostego kopiowania, przez Power Query, aż po wykorzystanie API.
Excel Automatyczne Numerowanie, Numeracja Porządkowa
Automatyczne numerowanie w Excelu jest jedną z podstawowych funkcji podczas pracy z arkuszem. Polega na automatycznym przypisywaniu numeracji wierszom lub kolumnom, bez konieczności wykonywania tego ręcznie, co jest szczególnie przydatne podczas sporządzania wszelkiego rodzaju list i zestawień.
W tym poradniku przedstawię najlepsze metody wykonania autonumeracji, podam wady i zalety każdej z metod. Całość podzielimy na autonumerację zakresów i autonumerację tabel.
Excel Przeliczenie Walut. Jak Przeliczyć Walutę po Kursie NBP w Excelu
Jak przeliczyć wartości wg. kursów NBP z różnych dni dla wielu walut znajdujących się w jednej kolumnie np. dolar, euro, funt na złotówki. Pokażę wam również jak pobrać kurs waluty z dnia poprzedzającego tzw. D-1, który jest potrzebny podczas obliczenia podatku od zysków kapitałowych z akcji zagranicznych
Opanuj Excela szybciej niż myślisz
Kurs Excel Podstawowy – naucz się najważniejszych funkcji, formuł i narzędzi Excela na praktycznych przykładach.
Zacznij pracować szybciej, unikaj błędów i swobodnie korzystaj z Excela w codziennej pracy.