Tabela przestawna w programie Excel: 5 kroków do łatwego raportu

Spis treści:

• Dlaczego warto korzystać z tabel przestawnych: Korzyści i scenariusze zastosowań
• Studium przypadku: Analiza sprzedaży w salonie samochodowym
• Przewodnik krok po kroku po tworzeniu Tabela przestawna
• Jak utworzyć tabelę przestawną w programie Excel: Przewodnik krok po kroku
• Dostosowywanie tabeli przestawnej: Proces krok po kroku
• Dostosowywanie filtrów tabeli przestawnej
• Zaawansowane obliczenia w tabelach przestawnych
• Aktualizowanie danych w tabeli przestawnej: przewodnik krok po kroku

Dlaczego warto korzystać z tabel przestawnych: Korzyści i scenariusze zastosowań

Tabele przestawne umożliwiają szybkie i wydajne przetwarzanie danych poprzez ich podsumowywanie, grupowanie i wykonywanie dodatkowych obliczeń, co znacznie upraszcza tworzenie raportów.
Elastyczność tabel przestawnych pozwala użytkownikowi dostosować je do swoich potrzeb za pomocą zaledwie kilku kliknięć. Można zmieniać układ wierszy i kolumn, filtrować wyniki i przenosić bloki raportów, aby poprawić wizualizację. To sprawia, że ​​tabele przestawne są niezbędne w analityce biznesowej.

Praktyczny przykład: analiza sprzedaży w salonie samochodowym

Wyobraźmy sobie mały salon samochodowy z trzema kierownikami sprzedaży. W ciągu kwartału informacje o sprzedaży zbierano w standardowej tabeli zawierającej dane dotyczące modelu samochodu, jego cech, ceny, daty sprzedaży i pełnego imienia i nazwiska kierownika.

Pod koniec kwartału należy przeprowadzić analizę, aby określić menedżera, który przyniósł największy zysk. W tym celu należy zgrupować wszystkie samochody sprzedane przez każdego menedżera, obliczyć łączną kwotę sprzedaży i określić końcowy procent sprzedaży za kwartał.

Przewodnik krok po kroku po tworzeniu tabeli przestawnej

Teraz przyjrzyjmy się krok po kroku, jak utworzyć tabelę przestawną do analizy danych sprzedażowych w naszym przykładzie. Pomoże Ci to nie tylko w tej sytuacji, ale także w każdym innym scenariuszu, w którym pracujesz z dużą ilością informacji.

Jak utworzyć tabelę przestawną w programie Excel: Przewodnik krok po kroku

Zanim zaczniesz tworzyć tabelę przestawną, musisz upewnić się, że dane źródłowe spełniają określone wymagania. Zapewni to prawidłowe działanie tabeli przestawnej.

• Każda kolumna musi mieć nagłówek, aby program Excel mógł zidentyfikować dane.
• Każda kolumna musi zawierać tylko jeden typ danych — tekst, liczbę lub datę.
• W tabeli nie może być pustych komórek ani wierszy.

Przejdź do karty „Wstaw” w górnym menu programu Excel i wybierz Opcja „Tabela przestawna”. Ta czynność spowoduje otwarcie okna dialogowego z ustawieniami.

W wyświetlonym oknie należy określić dwa kluczowe parametry:

• zakres tabeli źródłowej, z której zostaną wyodrębnione dane;
• arkusz, na którym zostanie umieszczona nowa tabela przestawna w celu dalszej analizy.

W tym przypadku należy zaznaczyć cały zakres tabeli z danymi sprzedaży, łącznie z nagłówkami. Zaleca się wybranie opcji „Nowy arkusz”, aby umieścić tabelę przestawną. Ułatwi to nawigację między danymi źródłowymi a raportem. Następnie kliknij „OK”.

Excel automatycznie utworzy nowy arkusz dla tabeli przestawnej. Dla ułatwienia obsługi pamiętaj o natychmiastowej zmianie jego nazwy.
Nowy arkusz będzie miał po lewej stronie obszar, w którym będą wyświetlane dane tabeli przestawnej, a po prawej panel „Pola tabeli przestawnej”, w którym wprowadzimy ustawienia. W następnym kroku przyjrzymy się bliżej sposobowi korzystania z tego panelu.

Konfigurowanie tabeli przestawnej: proces krok po kroku

Aby uzyskać atrakcyjny wizualnie raport, należy poprawnie skonfigurować tabelę przestawną. W górnej części panelu ustawień wyświetlana jest lista pól, z których możemy skorzystać. Pola te są tworzone na podstawie nagłówków kolumn tabeli źródłowej, takich jak Marka, Model, Kolor, Rok, Objętość, Cena, Data sprzedaży i Sprzedawca.
Panel ustawień jest podzielony na cztery główne obszary: Wartości, Wiersze, Kolumny i Filtry. Każdy z tych obszarów pełni swoją unikalną funkcję:

• „Wartości” – w tym miejscu wykonywane są obliczenia na podstawie wybranych danych. Excel domyślnie sumuje te dane, ale możesz wybrać inne operacje, takie jak średnia, minimum lub maksimum. Na przykład, jeśli analizujesz sprzedaż, program może zsumować całkowitą cenę sprzedanych samochodów.
• „Wiersze” i „Kolumny” – określają sposób rozmieszczenia pól w tabeli przestawnej. Pola umieszczone w wierszach będą wyświetlane wiersz po wierszu, a pola w kolumnach będą wyświetlane pionowo.
• „Filtry” – umożliwiają filtrowanie danych wyświetlanych w tabeli przestawnej. Po utworzeniu tabeli pasek filtrów będzie dostępny osobno, umożliwiając wybór danych do wyświetlenia i ukrycia. Może to być przydatne na przykład do analizy sprzedaży dla konkretnego menedżera lub w określonym okresie.

Istnieją dwa sposoby dostosowania tabeli przestawnej:

• Pierwsza opcja to po prostu zaznaczenie pola wyboru obok żądanego pola. Excel automatycznie umieści tę wartość w tabeli, ale często prowadzi to do nieefektywnego wyświetlania danych.
• Druga metoda polega na ręcznym przeciąganiu i upuszczaniu pól w żądanych obszarach, co pozwala dostosować tabelę przestawną do własnych potrzeb.

Zalecamy skorzystanie z drugiej metody, ponieważ daje ona większą kontrolę nad sposobem wyświetlania danych. W naszym przypadku chcemy wyświetlić nazwiska kierowników sprzedaży, sprzedane samochody i ich ceny. Inne dane (specyfikacje techniczne, daty sprzedaży) mogą być użyte do filtrowania.
Aby utworzyć tabelę informacyjną, umieścimy nazwiska kierowników w wierszach. Znajdź pole „Sprzedawca” u góry panelu i przeciągnij je do obszaru „Wiersze”.
Po tym po lewej stronie arkusza pojawi się blok z nazwiskami kierowników sprzedaży.

Teraz dodajmy modele samochodów sprzedane przez tych menedżerów. Podobnie przeciągnij pole „Marka, Model” do obszaru „Wiersze”.
Teraz drugi blok został dodany po lewej stronie arkusza, a tabela przestawna automatycznie pogrupowała wszystkie samochody według menedżerów, którzy je sprzedali.

Teraz musimy dodać ceny sprzedanych samochodów i ich ilość, aby uzyskać pełniejszy raport.
Aby tabela przestawna automatycznie sumowała te wartości, przeciągnij pola „Marka, Model” i „Cena” do obszaru „Wartości”.

Teraz tabela przestawna wyraźnie pokazuje, które samochody sprzedał każdy menedżer, ile i za jaką cenę. Dodatkowo, obok nazwisk menedżerów, można zobaczyć łączną liczbę sprzedanych samochodów i całkowitą kwotę sprzedaży za kwartał.
Można również dodawać i usuwać inne pola w wybranych obszarach. Wszystkie zmiany zostaną automatycznie zaktualizowane w tabeli przestawnej. W naszym przykładzie wprowadzone dane są wystarczające, a później przyjrzymy się, jak skonfigurować filtry do dalszej analizy.

Konfigurowanie filtrów tabeli przestawnej

Aby tabela przestawna była bardziej informacyjna i wizualna, należy skonfigurować filtry. Umożliwi to łatwe zarządzanie wyświetlaniem danych i szybki oraz wydajny dostęp do potrzebnych informacji.
W tym przykładzie przeniesiemy wszystkie pola, które nie są częścią podstawowej struktury tabeli przestawnej, do obszaru „Filtry”. Obejmuje to wielkość sprzedaży, datę sprzedaży, rok produkcji i kolor samochodu.

Teraz, aby filtrować dane według roku produkcji, ustawimy filtr tak, aby wyświetlał tylko samochody sprzedane w 2024 roku.
Aby to zrobić, w bloku filtra kliknij strzałkę po prawej stronie pola „Rok produkcji”. Otworzy się menu umożliwiające skonfigurowanie filtrowania.

W otwartym oknie odznacz pole wyboru obok opcji „Zaznacz wszystko” i zaznacz pole wyboru obok opcji „2024”. Następnie zamknij okno.

Tabela przestawna będzie teraz wyświetlać tylko 2024 pojazdy sprzedane przez menedżerów w danym kwartale. Aby zwrócić pełną ilość danych, wystarczy usunąć filtr ustawiony w tym samym bloku.

Filtry można wybierać i usuwać w zależności od potrzeb – znacznie upraszcza to analizę danych i pozwala skupić się na najważniejszych informacjach.

Dodatkowe obliczenia w tabelach przestawnych

Obecnie sprzedaż naszych menedżerów jest wyświetlana w rublach, ale w celu przeprowadzenia bardziej szczegółowej analizy musimy wiedzieć, jaki procent całkowitej sprzedaży stanowi każda transakcja. Jednym ze sposobów jest wykonanie obliczeń ręcznie, ale bardziej efektywnym rozwiązaniem byłoby skorzystanie z wbudowanych funkcji tabel przestawnych.
Aby wykonać niezbędne obliczenia, kliknij prawym przyciskiem myszy dowolną komórkę z ceną. W otwartym menu wybierz opcję „Obliczenia zaawansowane”, a następnie wpisz „% sumy”. Dzięki temu szybko i dokładnie uzyskasz potrzebne dane.

Teraz zamiast kwot wyrażonych w rublach wyświetlamy wartości procentowe. Ten wskaźnik pokazuje, jaki udział w całkowitej sprzedaży salonu w danym kwartale miał każdy sprzedany samochód. Procenty obok nazwisk menedżerów wskazują na ich ogólny wkład w sprzedaż w danym okresie.

Aby ułatwić analizę, możesz zwinąć szczegóły wraz z listami samochodów, klikając znak minus (-) po lewej stronie imienia i nazwiska menedżera. Dzięki temu tabela będzie bardziej zwarta, a kluczowe wskaźniki pokazujące, którzy menedżerowie osiągnęli najlepsze wyniki w kwartale, będą łatwo dostępne.

Aby powrócić do szczegółów samochodu, wystarczy kliknąć znak plusa (+).
Jeśli chcesz przywrócić wartości do ich oryginalnych rubli, kliknij prawym przyciskiem myszy i wybierz opcję „Bez obliczeń” w sekcji „Dodatkowe obliczenia”. Pozwoli Ci to elastycznie zarządzać prezentacją danych w zależności od Twoich potrzeb analitycznych.

Aktualizowanie danych w tabeli przestawnej: przewodnik krok po kroku

Wyobraźmy sobie, że w tabeli źródłowej w ostatnim dniu kwartału pojawiły się dwie nowe sprzedaże. Te zmiany nie zostaną automatycznie uwzględnione w tabeli przestawnej, ponieważ zakres danych źródłowych uległ zmianie.

Aby zaktualizować dane w tabeli przestawnej, należy zmienić parametry źródłowe. Przejdź do arkusza z tabelą przestawną i w sekcji Analiza tabeli przestawnej kliknij przycisk Zmień źródło danych.

Przycisk przeniesie Cię do arkusza tabeli źródłowej. Wybierz tam nowy zakres wraz z nowymi wierszami i kliknij „OK”. Tabela przestawna zostanie automatycznie zaktualizowana, a Ty zobaczysz zmiany.

Po aktualizacji dane w tabeli przestawnej ulegną zmianie: na przykład liczba sprzedaży menedżera Tregubova M. wzrośnie z ośmiu do dziesięciu.

Jeśli musisz zmienić informacje w bieżącym zakresie tabeli źródłowej, tabela przestawna nie zaktualizuje się automatycznie. Będziesz musiał to zrobić ręcznie.
Na przykład zmieńmy ceny dwóch samochodów w tabeli sprzedaży.

Aby upewnić się, że dane w tabeli przestawnej odzwierciedlają te zmiany, wróć do arkusza tabeli przestawnej i w sekcji Analiza tabeli przestawnej kliknij przycisk Odśwież.

Teraz menedżer Sokolov P. zaktualizował dane w kolumnie „Cena, RUB”.
Jak efektywnie korzystać z tabel przestawnych w Arkuszach Google? Przejdź do karty „Wstawianie” i wybierz opcję „Utwórz tabelę przestawną”. Proces jest podobny do opisanego dla programu Excel: zaznacz zakres tabeli i arkusz, na którym chcesz utworzyć tabelę przestawną. Następnie w wybranym arkuszu, w oknie Edytora tabeli przestawnej, określ wszystkie niezbędne ustawienia.