Tabela przestawna w Excelu: od pozycji sprzedaży do raportu

Tabela przestawna pozwala odpowiedzieć na pytanie o dane bez pisania osobnej formuły dla każdej grupy. Z listy sprzedaży mogę dostać przychód według regionu, miesiąca albo produktu. Przeciągam pola między obszarami i oglądam tę samą listę z kilku stron.

Najwięcej problemów pojawia się jednak przed pierwszym przeciągnięciem pola. Trzeba wiedzieć, co oznacza jeden wiersz, czy przychód jest liczbą i czy źródło obejmuje wszystkie rekordy. Pokażę cały proces na czterech syntetycznych pozycjach sprzedaży. Każdą sumę da się sprawdzić na kartce.

Co robi tabela przestawna

Tabela przestawna grupuje dane i oblicza podsumowania. Wybierasz pola określające grupy oraz pole z wartościami, które chcesz sumować, liczyć lub uśredniać. Nie zmienia to samych rekordów źródłowych. Zmieniasz sposób ich przedstawienia w raporcie.

Dokumentacja Microsoft pokazuje tworzenie tabel z zakresu lub tabeli Excela. Podstawowy mechanizm działa w wersjach desktopowych i w Excelu dla sieci Web, choć nazwy poleceń oraz dostępne możliwości różnią się między platformami. Tutaj opisuję zwykłą tabelę opartą na jednym źródle, bez modelu danych i zewnętrznych połączeń.

Ja zaczynam od jednego zdania: „Chcę zobaczyć przychód według regionu i miesiąca”. Dzięki temu wiem, które pola będą potrzebne. Gdy zaczynam od przypadkowego klikania, łatwo zbudować efektowną tabelkę, której znaczenie trudno później wyjaśnić.

Cztery pozycje sprzedaży jako źródło

Wpisz poniższe dane do A1:F5. Wiersz pierwszy zawiera nagłówki. Przychód i liczba sztuk to liczby, data ma być rzeczywistą datą Excela. Wszystkie dane są syntetyczne.

Dla czytelności pokazuję sześć kolumn w dwóch częściach. Pierwszą wpisz do A1:C5, drugą do D1:F5, zachowując tę samą kolejność czterech rekordów. W arkuszu powstanie jedna tabela A1:F5.

Nagłówek A1 nazwij „Zamówienie”, B1 „Data”, C1 „Region”, D1 „Produkt”, E1 „Sztuki”, a F1 „Przychód”.

Jeden wiersz oznacza pozycję zamówienia. Zamówienie Z-3 ma dwie pozycje. Ta informacja będzie ważna, kiedy zaczniemy liczyć rekordy i mówić o średniej. W źródle są cztery pozycje, ale tylko trzy różne identyfikatory zamówień.

Łączny przychód to 100 + 200 + 50 + 150, czyli 500. Liczba sztuk wynosi 2 + 4 + 1 + 3, czyli 10. Zapisuję te wartości przed budową raportu, żeby mieć prosty punkt odniesienia do późniejszych wyników.

Przygotowanie danych przed wstawieniem

Potrzebujesz jednej linii nagłówków, jednej kolumny na każde pole i spójnych typów danych. Nie wstawiaj scalonych nagłówków ani ręcznych sum między rekordami. Wiersz „Razem” w źródle może wejść do obliczeń jako kolejna sprzedaż i podwoić wynik.

Puste wiersze wewnątrz listy utrudniają rozpoznanie zakresu. Pusty region daje natomiast osobną grupę brakujących wartości. Nie usuwam takich rekordów w ciemno. Najpierw sprawdzam, czy brak oznacza nieprzypisany region, błąd eksportu czy rzeczywiście nieznaną lokalizację.

Wybieram komórkę źródła i tworzę tabelę Excela poleceniem Wstawianie > Tabela, zaznaczając obecność nagłówków. Mogę nadać jej nazwę „Sprzedaz”. Tabela ułatwia rozszerzanie źródła o kolejne wiersze, ale raport przestawny nadal trzeba odświeżać. Przekształcenie zakresu w tabelę nie naprawia też tekstów udających liczby.

Wstawienie pierwszej tabeli przestawnej

Kliknij komórkę w przygotowanym źródle, a następnie Wstawianie > Tabela przestawna. Sprawdź wskazaną tabelę lub zakres. Na początek wybierz nowy arkusz jako miejsce raportu. Dzięki temu nie przykryjesz danych i zostawisz przestrzeń na rozbudowę podsumowania.

Po zatwierdzeniu pojawi się obszar raportu i lista pól. Jeżeli panel pól jest ukryty, kliknij wewnątrz tabeli i włącz listę pól z narzędzi tabeli przestawnej. Szczegóły interfejsu zależą od wersji Excela, ale potrzebujesz tych samych czterech obszarów.

Przenieś Region do Wierszy, a Przychód do Wartości. Oczekiwany wynik to 300 dla Północy, 200 dla Południa i suma końcowa 500. Północ obejmuje trzy pozycje: 100, 50 oraz 150. Południe ma jedną pozycję z przychodem 200.

Jeżeli widzisz liczbę 3 oraz 1, zamiast 300 i 200, sprawdź sposób podsumowania pola. Excel prawdopodobnie liczy niepuste wartości, a nie dodaje przychody. Tę sytuację wyjaśniam niżej, bo zmiana nagłówka kolumny nie naprawi błędnie rozpoznanego typu danych.

Wiersze, kolumny, wartości i filtry

Wiersze określają grupy wypisane pionowo. Kolumny tworzą drugi wymiar porównania. Wartości zawierają obliczenia, takie jak suma przychodu. Filtry ograniczają dane uwzględniane w raporcie bez pokazywania danego pola jako osobnego wymiaru tabeli.

Zostaw Region w Wierszach, a Produkt przenieś do Kolumn. Przychód nadal ma być sumowany w Wartościach. Dla Północy dostaniesz 150 za notesy i 150 za teczki. Dla Południa przychód z notesów wyniesie 200. Nie ma tam pozycji z teczką.

Suma kolumny Notes to 350, Teczka 150, a suma całkowita nadal 500. Ta sama lista rekordów została pogrupowana inaczej. Pusta komórka na przecięciu Południa i Teczki oznacza brak takich pozycji w naszym źródle. Nie dowodzi, że region nigdy nie sprzedaje teczek.

Pole Zamówienie możesz na razie zostawić poza raportem. Nie trzeba używać każdej kolumny źródła. Wybór pól powinien wynikać z pytania. Dodawanie kolejnych wymiarów bez potrzeby zwykle utrudnia przeczytanie odpowiedzi.

Suma, licznik i średnia odpowiadają na różne pytania

W ustawieniach pola wartości wybierasz sposób podsumowania. Dla przychodu zwykle zaczynam od sumy. Licznik niepustych wartości opisuje liczbę pozycji zawierających dane, a średnia dzieli sumę przez liczbę wartości liczbowych uwzględnianych w obliczeniu.

W naszym przykładzie średni przychód na pozycję wynosi 500 / 4, czyli 125. Dla Północy to 300 / 3, czyli 100, a dla Południa 200 / 1, czyli 200. Średnia z dwóch średnich regionów wyniosłaby 150 i nie odtwarzałaby średniej wszystkich czterech pozycji. Regiony mają różne liczby rekordów.

Nie nazywam 125 „średnią wartością zamówienia”. Z-3 występuje dwa razy. Aby obliczyć średnią na zamówienie, trzeba najpierw uwzględnić trzy różne zamówienia, a nie cztery pozycje. Zwykły licznik identyfikatora policzy wystąpienia. Nie zamienia się automatycznie w liczbę unikatowych identyfikatorów.

Microsoft opisuje dostępne funkcje podsumowujące. Licznik unikatowy wymaga tabeli korzystającej z modelu danych. Nie włączam go tutaj, ponieważ ten przykład dotyczy zwykłego źródła. Najważniejsze, żeby nazwa wskaźnika odpowiadała temu, co faktycznie policzyłeś.

Dlaczego Excel pokazuje licznik zamiast sumy

Jeśli pole z kwotami zawiera teksty, Excel może domyślnie ustawić licznik. Kwota wyglądająca jak 100 może być tekstem z eksportu, zawierać nieprawidłowy separator albo dodatkowe znaki. Zmiana formatu wyświetlania komórki na liczbowy nie gwarantuje konwersji jej zawartości.

Najpierw poprawiam źródło. Sprawdzam kilka wartości, sposób importu i zgodność separatorów. Dopiero potem odświeżam tabelę i ustawiam właściwe podsumowanie. Wymuszenie sumy bez naprawienia tekstów może sprawić, że część przychodów zostanie pominięta i otrzymasz zaniżony wynik.

Nie dopisuję do liczby jednostki jako tekstu w tej samej komórce. Przychód zostaje liczbą, a sposób prezentacji ustawiam przez format pola wartości. Dzięki temu pole pozostaje użyteczne do dodawania i porównywania. Jednostkę opisuję w nazwie albo formatowaniu raportu.

Miesiące i lata w analizie czasu

Przenieś Produkt poza Kolumny, a w jego miejsce dodaj Datę. Daty można grupować według miesięcy i lat. W zależności od ustawień Excel może utworzyć takie grupy automatycznie albo pozwolić wybrać je poleceniem Grupuj. Instrukcja Microsoft pokazuje tę operację.

Dla naszych danych styczeń daje 300, a luty 200. W układzie region i miesiąc Północ ma 100 w styczniu i 200 w lutym, a Południe 200 w styczniu. W każdym z tych widoków suma pozostaje 500, jeżeli nie zmienisz filtrów.

Gdy źródło obejmuje kilka lat, dodaj również rok. Samo grupowanie po miesiącu może połączyć styczeń z różnych lat. Taki wynik bywa przydatny do badania sezonowości, ale odpowiada na inne pytanie niż miesięczny przychód w konkretnym roku.

Przy problemach z grupowaniem wracam do źródła i sprawdzam tekstowe daty oraz braki. Zmiana wyglądu komórki na datę nie tworzy prawidłowej daty z dowolnego tekstu. Najpierw potrzebujesz spójnego pola, dopiero potem wygodnej hierarchii w raporcie.

Udziały procentowe i znaczenie mianownika

Możesz przeciągnąć Przychód do Wartości drugi raz. Pierwsze pole zostaw jako sumę, a dla drugiego wybierz Pokaż wartości jako > % sumy końcowej. W widoku samych regionów Północ ma 300 / 500, czyli 60%, a Południe 40%.

Przychód 300 i udział 60% mówią o tej samej grupie, ale w inny sposób. Opisz obie kolumny, żeby odbiorca nie musiał zgadywać, do czego odnosi się procent. W bardziej rozbudowanej tabeli procent sumy wiersza i procent sumy kolumny mogą dawać zupełnie inne odpowiedzi.

Po ograniczeniu raportu filtrem sprawdź mianownik ponownie. Udział może dotyczyć wybranego fragmentu danych, a nie całej bazy. Jeżeli filtrujesz wyłącznie Północ, raport nie przedstawia już porównania dwóch regionów. Przy każdym procencie chcę umieć wskazać licznik, mianownik i aktywny zakres danych.

Odświeżenie i dopisywanie rekordów

Dopisz do źródła nową pozycję w marcu z przychodem 100. Jeżeli źródłem jest tabela Excela i nowy wiersz należy do niej, po odświeżeniu tabela przestawna powinna uwzględnić nowy rekord. Suma wzrośnie z 500 do 600.

Przy zwykłym zakresie A1:F5 dopisanie wiersza 6 poza tym zakresem nie rozszerza automatycznie definicji źródła. Trzeba sprawdzić i ewentualnie zmienić źródło, a potem odświeżyć raport. Samo naciskanie „Odśwież” nie pomoże, jeśli nowy rekord w ogóle nie należy do wskazanych danych.

Warto sprawdzić zarówno początek, jak i koniec źródła. Raport może pomijać końcowe rekordy albo obejmować starą sumę kontrolną. Szczegóły odświeżania tabel przestawnych zależą od wersji i połączenia. Dla tego ćwiczenia wystarczy ręczne odświeżenie i porównanie z oczekiwanym wynikiem.

Filtry, fragmentatory i wykres

Filtr Produkt ogranicza raport do wybranego produktu. Dla notesów suma wyniesie 350, a dla teczek 150. Fragmentator może pokazać wybór w formie przycisków. Jego zadaniem jest ułatwienie interakcji, ale nadal trzeba czytelnie pokazywać, które wartości są wybrane.

Wykres przestawny pomaga zobaczyć porównanie regionów lub miesięcy. Na początek wybrałbym proste kolumny. Przy zmianie filtrów wykres dotyczy aktualnego widoku tabeli, dlatego tytuł powinien opisywać miarę i okres. Ładny wykres z niewidocznym filtrem potrafi wprowadzić odbiorcę w błąd.

Nie dokładam wszystkich produktów, miesięcy i regionów do jednego wykresu tylko dlatego, że jest to możliwe. Najpierw wybieram pytanie, a potem układ. Jeśli potrzebujesz pojedynczej sumy do istniejącego raportu, czasem wystarczy SUMA.WARUNKÓW zamiast kolejnej tabeli przestawnej.

Sumy końcowe czytaj zgodnie z obliczeniem

Suma końcowa dla średniej jest obliczana z rekordów źródłowych. Nie interpretuj jej jako dodawania średnich regionów widocznych wyżej. W naszym przykładzie regiony mają średnie 100 i 200, a średnia wszystkich pozycji wynosi 125. Taki układ jest prawidłowy i wynika z różnej liczby obserwacji.

Podobnej uwagi wymagają sumy częściowe przy kilku polach w Wierszach. Podsumowanie regionu zawiera już jego produkty. Gdy skopiujesz wartości produktów i dodasz jeszcze sumę regionu, policzysz te same rekordy drugi raz. Zanim przeniesiesz liczby do innego obliczenia, przeczytaj hierarchię grupowania.

W prezentacji zachowuję definicje pól i kontekst filtrów obok liczb. Odbiorca skopiowanej tabeli nie ma dostępu do panelu, który w Excelu wyjaśniał zakres raportu. Krótka informacja o okresie, jednostce i wybranych kategoriach pomaga poprawnie zrozumieć taki eksport.

Nowsze wersje Excela mogą obsługiwać automatyczne odświeżanie danych lokalnych. To ustawienie, które trzeba znać, a nie założenie dotyczące każdego pliku. Przy cudzym skoroszycie sprawdzam, w jaki sposób i kiedy zaktualizowano wyświetlane podsumowanie.

Co sprawdzić, gdy suma się nie zgadza

Jeżeli oczekujesz 500, a widzisz inną kwotę, wróć do najprostszego widoku: samo pole Przychód w Wartościach, bez grupowania. Sprawdź aktywne filtry, definicję źródła i rodzaj obliczenia. Ta kolejność ogranicza liczbę rzeczy, które zmieniasz jednocześnie. Gdy poprawisz kilka ustawień naraz, trudniej ustalić przyczynę problemu.

Wynik 350 może w naszym przykładzie oznaczać wybrany filtr Notes. Wynik 300 może odpowiadać samej Północy albo samemu styczniowi. Liczba 4 może być licznikiem pozycji. Każdy z tych wyników da się uzyskać poprawną operacją na tych samych danych. O tym, czy pasuje do raportu, decyduje jego definicja.

Jeżeli suma pozostała 500 po dopisaniu nowej pozycji 100, sprawdź zarówno przynależność wiersza do tabeli źródłowej, jak i odświeżenie. To dwa oddzielne warunki. Nowy rekord może być w źródle, ale jeszcze poza bieżącym stanem raportu. Może też w ogóle znajdować się poza zakresem, do którego raport się odwołuje.

Przy danych z systemu porównuję zakres raportu z definicją eksportu. Czy uwzględniono ten sam okres, statusy dokumentów i regiony? Excel nie wie, że plik zawiera jedynie zatwierdzone zamówienia albo pomija ostatni dzień miesiąca. Tabela przestawna opisze rekordy, które dostała, bez oceny kompletności procesu źródłowego.

Dobrą praktyką jest zachowanie osobnej sumy kontrolnej z eksportu oraz krótkiej definicji, które wiersze powinny wejść do zestawienia. Porównanie ma sens, gdy obie strony liczą tę samą rzecz. Różnica między przychodem netto i brutto albo między datą zamówienia i datą realizacji może wyjaśniać rozbieżność, której nie naprawi żadna opcja formatowania tabeli.

Ćwiczenie i kontrola przed przekazaniem

Zbuduj trzy widoki: przychód według regionu, przychód według produktu i przychód według miesiąca. Oczekiwane podsumowania to 300 i 200 dla regionów, 350 i 150 dla produktów oraz 300 i 200 dla stycznia i lutego. Za każdym razem suma czterech pozycji ma wynosić 500.

Potem zmień sumę na średnią i opisz, co oznacza wynik 125. Jeśli podpiszesz go jako średnią na pozycję, definicja będzie zgodna z danymi. Wróć też do kolumny Zamówienie i wyjaśnij, dlaczego cztery wystąpienia nie oznaczają czterech różnych zamówień.

Przed oddaniem raportu sprawdzam zakres źródła, rodzaj obliczenia, aktywne filtry i ostatnie odświeżenie. Dodaję opis okresu oraz jednostki. Gdy te rzeczy są jasne, tabela przestawna staje się użytecznym sposobem oglądania danych, który inna osoba potrafi zrozumieć i samodzielnie odtworzyć.