fbpx

Prosty budżet domowy: podsumowanie wydatków w tabeli przestawnej

20.01.2015 | Budżet domowy, ECP2, Tabele przestawne

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

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"

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ę i lista pól

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:

Układ klasyczny tabeli przestawnej - nazwy miejsc w tabeli

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 układ klasyczny tabeli przestawnej

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

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

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 prezentująca wydatki na poszczególne kategorie po miesiącach

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 prezentującej wydatki na poszczególne kategorie po miesiącach

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 danych

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 tabeli przestawnej na inną tabelę przestawną

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

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

Jeżeli chcesz lepiej poznać Excela na poziomie średniozaawansowanym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 2! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

9 komentarzy

  1. Świetna seria! Mam już wszystko do prowadzenia budżetu domowego (dotychczasowe gotowe programy zawsze zawodziły i traciłam dane). Tak mi się podobało, że zabieram się za pozostałe wpisy.
    Dziękuję za tak cenną wiedzę 🙂

    Reply
    • Joanna,

      cieszę się, że Ci się podoba i PRZYDA seria wpisów o budżecie domowym 🙂 Wkrótce kolejny wpis o budżetowaniu jako takim!

      Pozdrawiam
      Malina

      Reply
  2. Bardzo pomocny poradnik! Co prawda miałam w pewnym momencie trochę kłopotów z formułą wyszukaj.pionowo, ale wystarczyło wpisać dane do komórki w kolumnie „nazwa” i już wszystko było cacy 😀
    Od dawna chciałam zrobić taki budżet w excelu, ale jakoś nie mogłam się za to zabrać, no i nie podobała mi się wizja ręcznego uzupełniania wszystkich komórek (taaak, nie miałam pojęcia, jak wyczyniać takie cuda, jakie tu opisałaś).
    Dziękuję za ten cykl <3

    Reply
    • Saneko,

      bardzo się cieszę, że mój pomysł na budżet Ci się podoba, a co najważniejsze – przyda Ci się!

      Pozdrawiam
      Malina

      Reply
  3. Robię skoroszyt do prowadzenia budżetu domowego i zawiesiłem się na pewnej operacji (na pewno banalnej).

    -Mam dwa arkusze: „główny” do wpisywania danych (wydatków, przychodów itp) oraz „techniczny” który na razie mieści listy.
    -Rzecz rozchodzi się o rozwijalne listy kategorii, chcę osiągnąć następujący efekt:

    A. Z listy rozwijalnej „1” wybiera się główną kategorię wydatku/przychodu która znajduje się w tabeli „2” (mam to zrobione).
    B. W polu „3” chciałbym mieć listę rozwijalną – z możliwością wyboru podgrupy z tabel „4”.

    I w kwestii punktu B potrzebuję pomocy bo nie wiem jak to ugryźć. Chciałbym żeby np po wyborze kategorii „jedzenie”, w polu „3” na liście rozwijalnej pokazywały mi się tylko pozycje z kolumny pod daną kategorią („4”).
    Czyli wybór „jedzenie” powinien odblokowywać na liście opcje takie jak „nabiał, mięso, gruz, warzywa, owoce”.

    Dołączam link z grafikami poglądowymi – o co mi dokładnie chodzi:
    http://zapodaj.net/a08cd3863217c.png.html

    Z góry dziękuję za pomoc,

    Reply
    • Cześć Waldkorg.

      Banalne to to nie jest, ale do zrobienia. Napiszę o tym post – już od jakiegoś czasu chodziło mi to po głowie, więc wywołałeś wilka z lasu 🙂

      Pozdrawiam
      Malina

      Reply
    • A odpowiedź znajdziesz tutaj: Zależna lista rozwijana w komórce.

      Pozdrawiam
      Malina

      Reply
  4. Super poradnik. Dzięki za wskazówki…

    Reply
    • Bardzo się cieszę 🙂

      Reply

Submit a Comment

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *