W dotychczasowych artykułach o budżecie domowym zbieraliśmy dane. Pokazywałam, jak tworzyć tabelę do gromadzenia danych, jak stworzyć słowniki i listy, jak wstawić do komórek listy wyboru. Wszystko po to, aby łatwiej i szybciej wpisywać dane o przychodach i wydatkach.

Dziś pokażę, w jaki sposób skorzystać ze stworzonych narzędzi. Wreszcie zobaczycie wynik dotychczasowej pracy! Stworzymy bowiem tabelę przestawną będącą raportem podsumowującym nasze przychody i wydatki. Oprzemy ją oczywiście na naszej tabeli danych (przypominam, że nazywa się ona tb_DANE). Tak wygląda przykładowa tabela, którą stworzymy:

Przykładowa tabela: przychody i wydatki po miesiącach

Do dzieła!

Tworzenie i układ tabeli przestawnej

Przejdźmy zatem do arkusza DANE, gdzie znajduje się tabela tb_DANE. Aby stworzyć tabelę przestawną z danych zawartych w tb_DANE, należy zaznaczyć dowolną jej komórkę (np. B3). Następnie z menu Wstawianie/ Tabele należy wybrać opcję Tabela przestawna. Pojawi się następujące okno:

Okno Tworzenie tabeli przestawnej

Ponieważ wszystkie domyślnie zaznaczone w nim opcje są dla nas ok, to klikamy OK.

To, co się pojawi (na rysunku poniżej po lewej stronie), jest dopiero panelem tworzenia tabeli przestawnej. Stanie się nią wtedy, gdy określimy, jakie dane mają się w niej znajdować.

Miejsce na tabelę (po lewej stronie) i lista pól (po prawej)

Trochę to nieporadnie wygląda, bo miała być tabela, a tabeli nie ma. Spokojnie, zaraz ją zrobimy. Tworzenie tabeli przestawnej jest w istocie bardzo proste. Widzicie 4 białe prostokąty (okienka) na dole listy pól (Filtr raportu, Etykiety kolumn, Etykiety wierszy i Wartości)? Każde z nich odpowiada pewnemu miejscu w docelowej tabeli. Będzie to bardziej zrozumiałe, gdy ustawimy klasyczny układ tabeli przestawnej, czyli to:

Klasyczny układ tabeli przestawnej i nazwy miejsc w tabeli

Zauważcie, że prostokąt z napisem Upuść pola kolumn tutaj jest odpowiednikiem Etykiety kolumn na liście pól tabeli. Analogicznie prostokąt Upuść pola wierszy tutaj to odpowiednik Etykiet wierszy. To samo dotyczy pola wartości i filtru raportu (inaczej zwane: pole stron).

Aby ustawić klasyczny układ tabeli przestawnej, należy ustawić opcję o tej właśnie nazwie: układ klasyczny tabeli przestawnej (umożliwia przeciąganie pól w siatce). Tak sobie myślę, że napiszę artykuł o higienie pracy z tabelami przestawnymi, w którym opiszę właśnie ten trik i kilka innych. 😉

Opcja włączająca klasyczny układ tabeli przestawnej

Podam przykład docelowej tabeli. Chcemy analizować nasze przychody i wydatki z 2013 r. po miesiącach. Nasza tabela może wyglądać tak (oczywiście wybrałam 2013 z filtru dla pola Rok):

Przykładowa tabela: przychody i wydatki po miesiącach

W prostokącie wierszy mamy więc miesiąc (Mc), na prostokącie kolumn – In/Out, w prostokącie wartości – oczywiście PLN, a w prostokącie strony/ filtru raportu – Rok. Powstało proste podsumowanie, a jak wiele informacji wnosi.

Aby stworzyć taką tabelę, wystarczy więc przeciągnąć odpowiedni nagłówek (z listy pól) do odpowiedniego prostokąta w tabeli (lub na liście pól tabeli). Moja lista pól do tej tabeli wygląda tak:

Uzupełniona lista pól do prezentowanej tabeli

Żeby otrzymać taki układ jak na obrazkach, należy lewym przyciskiem myszy przeciągnąć wybrane pole (nagłówek) do schematu tabeli w okienku Lista pól tabeli przestawnej. Przy zaznaczonym układzie klasycznym (czyli tym, który przed chwilą ustawiliśmy) można przeciągać te pola (nagłówki) bezpośrednio na tabelę. Jeśli jednak pierwszy raz tworzysz tabelę przestawną, zdecydowanie polecam korzystanie ze schematu na liście pól. Łatwiej to ogarnąć.

Oczywiście układ tabeli możesz dowolnie zmieniać. Może interesować Cię np. podział wydatków na kategorie po miesiącach. Tabela będzie wtedy wyglądała tak jak na obrazku poniżej (posortowana po wydatkach malejąco, czyli od największego do najmniejszego):

Tabela przedstawiająca wydatki na poszczególne kategorie po miesiącach

Aby wyświetlić powyższe wartości w tabeli, należy uzupełnić Listę pól w następujący sposób:

Schemat do tabeli przedstawiającej wydatki na poszczególne kategorie po miesiącach

Kilka tabel opartych na tym samym źródle danych

Obie zaproponowane przeze mnie tabele przedstawiają ciekawe informacje o naszych przychodach i wydatkach, dlatego polecam je zachować. Do tego trzeba mieć jednak dwie tabele oparte jednym źródle. W takiej sytuacji warto pamiętać, że tabelę przestawną od początku tworzymy tylko raz. Potem możemy ją kopiować (ją lub cały arkusz, w którym się znajduje) i tę skopiowaną tabelę modyfikować. W naszym przypadku, jeśli chcemy mieć obie tabele w jednym arkuszu, należy skopiować (Ctrl + C) pierwszą tabelę, czyli zakres A1:C18, i wkleić np. do komórki F1. Tak to wygląda:

Dwie tabele oparte na jednym źródle

Jedyne, o czym należy pamiętać przy umieszczaniu kilku tabel przestawnych w tym samym arkuszu, to to, że lubią one zmieniać swoje rozmiary (np. gdy dojdą nowe dane lub zmienimy układ tabel). Należy więc umieścić je w takiej odległości od siebie, aby nie miały szans na siebie najść. Gdyby jednak doszło co do czego, Excel wyświetli następujący komunikat:

Komunikat o nachodzeniu na siebie tabel przestawnych

W ogóle gdy po odświeżeniu tabela przestawna ma na cokolwiek najechać, Excel nas o tym poinformuje. Nawet jeśli jest to tylko sformatowana komórka, niezawierająca żadnej wartości.

Jeśli jednak nie zamierzamy zmieniać układu pierwszej tabeli (a w naszym budżecie domowym nie zamierzamy), wybrane przeze mnie miejsce jest w porządku. Drugą tabelę oczywiście zmodyfikuj tak, jak opisałam wcześniej (kategorie wydatków po miesiącach).

Odświeżanie danych

Pora na odświeżanie danych. Mija kolejny miesiąc i chcemy zobaczyć, jakie pozycje doszły. Po uzupełnieniu arkusza DANE wystarczy zaznaczyć dowolną komórkę w wybranej tabeli przestawnej (np. B7) i wybrać opcję Odśwież wszystko w menu Dane/ Połączenia (ikonka na rysunku poniżej) lub Narzędzia tabel przestawnych/ Opcje/ Dane/ Odśwież/ Odśwież wszystko. Możemy też skorzystać ze skrótu klawiszowego Ctrl + Alt + F5.

Opcja Odśwież wszystko

Taaak. Zobacz, jak szybko przeanalizowaliśmy dużą ilość danych. Dodatkowo jeszcze mamy możliwość łatwego dokładania analiz. I to w bardzo prosty sposób beż użycia skomplikowanych formuł. Wszystko dzięki tabelom przestawnym. Prawda, że są cudowne? Jestem ciekawa, czy pokochasz to narzędzie tak samo jak ja :). Napisz o tym w komentarzu. Jestem ciekawa Twojej opinii.

Redakcja językowa – Aleksandra Wasiak