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
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.
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.
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
do osób, które chcą nauczyć się podstaw Excela za darmo w mniej niż godzinę.
Darmowy Kurs Excel dla początkujących