Tworzenie tabel przestawnych (PivotTables)

Opis

Tabela podsumowań (Pivot Table) to potężne narzędzie do obliczania, podsumowywania i analizy danych, które pozwala zobaczyć porównania, wzorce i trendy w danych.

Wyobraź sobie, że musisz sporządzić krótki raport analityczny do podjęcia decyzji na podstawie dużej tabeli, która zawiera wiele różnych informacji. Załóżmy, że dysponujesz danymi wyświetlającymi informacje o portfelu kredytowym Banku, gdzie zapisane są szczegółowe informacje o każdym kredytobiorcy.

Przykład problemu

Załóżmy, że z bazy danych do Excel ładowana jest poniższa tabela w celu analizy portfela kredytowego:

W nim każda linia zawiera pełne informacje na temat jednej umowy kredytowej:

• imię i nazwisko pożyczkobiorcy;
• Numer umowy;
• data udzielenia i data spłaty pożyczki;
• rodzaj pożyczki;
• Kwota kredytu

Oczywiście, jeśli menedżerowie sprzedaży znają swój biznes i ciężko pracują, codziennie do tej tabeli będzie dodawanych kilkadziesiąt linii, a pod koniec na przykład roku lub co najmniej kwartału rozmiar stołu stanie się potworny.

Jednak jeszcze większy horror spowoduje konieczność tworzenia raportów w oparciu o te dane. Ile pożyczek zostało udzielonych każdego miesiąca? Jaka jest wielkość portfela kredytowego pod względem ilości i kwoty akcji kredytowej? Dziesięć największych pożyczkobiorców? itp.

Odpowiedzi na wszystkie pytania można uzyskać łatwiej niż myślisz. Około trzech minut. Z jednym z najbardziej niesamowitych narzędzi Microsoft Excel — Tabele podsumowujące .

Chodźmy...

Przygotowywanie danych

Przed utworzeniem tabel przestawnych rozważ następujące wskazówki dotyczące przygotowania danych:

• aby uzyskać najlepsze wyniki, używaj czystych danych tabelarycznych;
• umieszczaj dane w kolumnach, a nie w wierszach;
• upewnij się, że wszystkie kolumny mają nagłówki z jednym wierszem unikalnych, niepustych podpisów dla każdej kolumny. Unikaj zduplikowanych wierszy nagłówka lub scalonych komórek;
• sformatuj dane jako „inteligentny stół Excel” (kliknij w dowolnym miejscu swoje dane, a następnie wybierz "Wypełnij tabelę" ( Insert - Table ) w menu wstążki (ribbon);
• jeśli masz złożone lub zagnieżdżone dane (takie jak tabela przestawna), użyj Power Query aby je przekonwertować (na przykład możesz użyć makra do anuluj podsumowanie danych ), tak aby były zorganizowane w kolumny z jednym wierszem nagłówka.

Tworzenie tabeli przestawnej

Wybierz całą naszą tabelę danych i kliknij menu Wstaw - tabela podsumowująca ( Insert - PivotTable ).

Pojawi się okno dialogowe, w którym możemy wybrać miejsce umieszczenia naszej tabeli przestawnej oraz dostosować zakres danych.

Domyślnie Excel umieszcza tabelę przestawną w nowym arkuszu (New Worksheet) , ale w razie potrzeby możemy ręcznie wybrać bieżący lub dowolny inny arkusz książki (Existing Worksheet) . Lepiej wybrać do tego nowy arkusz – wtedy nie ma ryzyka, że tabela zbiorcza „pokryje się” z oryginalną listą i otrzymamy masę linków cyklicznych. naciśnij przycisk OK i przechodzimy do najciekawszej części – etapu konstruowania naszego raportu.

Praca z układem

Co zobaczysz po naciśnięciu przycisku OK zwany układem (layout) Tabela podsumowań:

Praca z nim jest łatwa - należy przeciągnąć myszką nazwy kolumn (pól) z okna Lista pól tabeli podsumowującej (PivotTable Field List) w regionie:

• wiersze (Row Labels);
• kolumny (Column Labels);
• filtry (Report Filter);
• dane układu (Values).

Jedyne zastrzeżenie to zrobić to dokładniej, nie przegap! Chodźmy...

W trakcie przeciągania skonsolidowana tabela zacznie zmieniać swój wygląd na Twoich oczach, wyświetlając potrzebne dane. Po przekroczeniu wszystkich czterech potrzebnych nam pól z listy powinniśmy otrzymać prawie gotowy raport. Pozostaje tylko dobrze go sformatować.

Formatowanie tabeli przestawnej

W menu wybierz format tabeli podsumowującej PivotTables Tools - Design :

Ale otrzymujemy następujący wygląd:

To nie jest takie skomplikowane, prawda?

Niedogodności

Jedyną wadą tabel zbiorczych jest brak automatycznej aktualizacji (przeliczenia) w przypadku zmiany danych na pierwotnym zestawieniu. Aby wykonać takie obliczenie należy kliknąć prawym przyciskiem myszy tabelę podsumowującą i wybrać polecenie z menu kontekstowego Aktualizacja (Refresh) .

Artykuły na ten temat:

• Elementy nieobrotowe (Unpivot)
• PowerPivot - aplikacja rozszerzająca możliwości tabel przestawnych