Artykuł

freeimages.com freeimages.com
gru 28 2014
0

Budżet domowy w Excelu

Nieubłaganie zbliżamy się do końca roku, a przełom roku zawsze jest dobrym czasem na nowe postanowienia i porządki. Wiele można poprawić w życiu każdego z nas, ale na niektóre rzeczy potrzebne są dodatkowe finanse. Dlatego może warto byłoby zacząć porządki właśnie od tego obszaru? Nie jestem ekspertem finansowym, ale już od jakiegoś czas planuje i księguje swoje wydatki. Co mi to daje? Przede wszystkim większą kontrolę tego co wydaję, a także lepsze możliwości planowania. Uwzględniając dane historyczne łatwiej jest prognozować nadchodzące wydatki. Można również pokusić się o pewną optymalizację obszarów, które w naszym odczuciu pochłaniają zbyt duże koszty.

Moją optymalizację finansową realizuję niejako w dwóch krokach. Po pierwsze - zawsze planuje najważniejsze wydatki - szacunkowe kwoty dla najważniejszych stałych i nieregularnych wydatków (zapisuje tu wszystkie wydatki o których wiem i które przekraczają kwotę 40-50 zł). Dzięki temu jestem w stanie zaplanować koszt wydatków stałych, płynnych, a także wyliczyć środki, które mogę skierować na ewentualnie inwestycje.

Po drugie - księguję większość wydatków w Excelu. Większość, ponieważ czasem odpuszczam sobie zapisywanie małych kwot typu 2-3 zł. Można sobie zadać pytanie, czy nie jest to paranoja? Na pierwszy rzut oka może to tak wyglądać.. ale ta paranoja przynosi realne efekty;-)

Dziś chciałbym się z Wami podzielić moim szablonem Excela, którego używam do spisywania bieżących wydatków.

Zbieranie informacji

Kluczem do sukcesu w tworzeniu Excela zawierającego dane naszych wydatków, jest samo ich zbieranie. W tym celu pomocne będą wszelkiej maści paragony, wyciągi bankowe, a czasem po prostu zwykłe karteczki (więcej na ten temat pisał Michał Szafrański na swoim blogu.

Wydatków nie musimy oczywiście zapisywać na bieżąco. Lepiej jest wyznaczyć sobie krótkie sesje co kilka dni. Z doświadczenia mogę powiedzieć, że naprawdę nie zajmuje to wiele czasu.

Wzorcowy Excel

Jeśli zaczęliście mieć wątpliwości czy ten wpis ma jakąkolwiek styczność z IT, to mam nadzieję, że zaraz je stracicie;-) Zacznijmy od tego, że w dziale download, znajdziecie archiwum zip zawierające dwa pliki *.xlsx. Pierwszy z nich nie zawiera danych i jest gotowy do wypełnienia własnymi wydatkami oraz przychodami. Drugi natomiast zawiera kilkanaście przykładowych wpisów, ukazujących działanie budżetowego Excela w praktyce.

Jeśli nie interesują Cię szczegóły techniczne, to tak naprawdę możesz pominąć kolejne akapity. Możesz ewentualnie zajrzeć do sekcji Dobór kolumn, gdzie szerzej objaśniam przeznaczenie konkretnych kolumn. Nie mniej jednak wydaje mi się, że są one na tyle intuicyjne, że nie powinniście mieć większych problemów z rozszyfrowaniem ich przeznaczenia.

Tablice i listy

Excel dla budżetu domowego, oparty jest o tablice i listy, które dla wygody znajdują się w osobnych arkuszach w tym samym pliku. W ten sposób rozpisane zostały:

  • Kategorie wydatków
  • Typy (wydatki, przychody)
  • Informacje o tym czy wydatek był planowany, czy też nie
  • Osoby

Oczywiście umieszczanie w osobnym arkuszu wartości typu tak/nie jest lekką przesadą, ale z drugiej strony dzięki temu w łatwy sposób możemy łączyć funkcjonalność list z poprawnością danych i pozwalać użytkownikowi na wybór tylko odpowiednich opcji.

Listy tworzymy poprzez zaznaczenie komórek (ew. kolumny) i wpisanie nazwy w miejscu gdzie domyślnie pojawia się informacje o zaznaczonej komórce/obszarze.

Tabele tworzy się równie prosto - wystarczy z zakładki Wstawianie wybrać opcję Tabela, a później już działać według wskazówek wyświetlanych na ekranie.

Tabele są bardzo pożyteczne, ponieważ oprócz standardowych możliwości filtrowania i sortowania, umożliwiają one zaawansowane operacje na danych z wykorzystaniem różnych, magicznych funkcji Excela (PODAJ.POZYCĘ, INDEKS itp.)

Poprawność danych

W głównym arkuszu również zastosowałem tabelę (łatwiej dzięki temu filtrować oraz sortować dane) w której dodatkowo skorzystałem z funkcji Poprawność danych (znajdziecie ją w menu Dane). Dzięki temu mogłem narzucić używanie utworzonych wcześniej list w obrębie wybranych kolumn tabeli. I tak np. dla kolumny Osoba, wykorzystałem listę OsobyLista. Analogiczna sytuacja powtarza się jeszcze w kilku miejscach:

  • Kategoria - lista KategorieLista
  • Typ - lista TypyLista
  • Planowany - lista PlanowanyLista

Funkcje INDEKS i PODAJ.POZYCJĘ

Jak widzieliście wcześniej, na screenie 2 pokazywałem tabelę kategorii. Każda kategoria oprócz nazwy miała również jeden z dwóch typów określonych na liście TypyLista. Ponieważ typ kategorii jest dość istotny, a jednocześnie głupio byłoby go sprawdzać ręcznie na głównej liście, postanowiłem zaprząc do pracy funkcję INDEKS i PODAJ.POZYCJĘ. Udało mi się wypracować następującą formułę:

=INDEKS(KategorieTabela[Typ];PODAJ.POZYCJĘ(E2;KategorieTabela[Nazwa kategorii];0))

Dzięki tej kombinacji, automatycznie po wybraniu kategorii o określonej nazwie, wyciągamy również jej typ, który pojawi się w kolumnie obok;-) Zwróćcie uwagę, że w tym przypadku w formule wykorzystujemy naszą tabelę oraz jej nazwane kolumny.

Dobór kolumn

Kolumny w głównej tabeli są raczej standardowe, choć kilka z nich może wymagać dodatkowych objaśnień:

  • Lp - wiadomo; wykorzystujemy funkcję WIERSZ do automatycznego numerowania wierszy
  • Data
  • Nazwa
  • Osoba - wybieramy osobę której dotyczy określona pozycja. Z założenia mogą to być więc członkowie naszego gospodarstwa domowego. Nie chodzi tu bynajmniej o szpiegowanie kto ile zarabia/wydaje, lecz o fakt zapisu kolejnych pozycji np. Wynagrodzenie osoby Ania. W przypadku gdy pozycja dotyczy kilku osób, kogoś obcego, bądź jest to jakiś rachunek, ustawiam Nie dotyczy
  • Kategoria - kategoria wydatku/przychodu pobrana z tabeli KategorieTabela (omówiłem ją wcześniej)
  • Typ elementu - wydatek/przychód
  • Planowany - tą opcję dodałem stosunkowo niedawno. W łatwy sposób pozwala na wyliczanie sumy planowanych/nieplanowanych wydatków. Na ich podstawie można wyciągnąć wnioski na przyszłość
  • Kwota - kwota wydatku, bądź przychodu. Aby zwiększyć czytelność tej komórki, dodałem formatowanie warunkowe. Kolorem zielonym oznaczone są przychody, natomiast czerwonym wydatki. Formatowanie warunkowe było ustawione dla pojedynczej komórki, ale podobnie jak w innych kolumnach, jest automatycznie rozszerzane na wszystkie nowo dodawane wiersze.
  • Komentarz

Bilans

W prawej części arkusza mamy małą metryczkę, bilans oraz podsumowania poszczególnych kategorii. Generalnie korzystamy tutaj z funkcji tablicowych do których wpleciono pewne warunki. Np. żeby wyświetlić sumę wydatków planowanych, musimy skorzystać z następującej formuły:

=SUMA((Zestawienie[Kwota])*(Zestawienie[Planowany]="Tak")*(Zestawienie[Typ]="Przychody"))

A następnie zatwierdzić ją kombinacją przycisków Ctrl + Shift + Enter. W tym przypadku wyraźnie widać, jak ważne w naszym arkuszu są tabele. Dzięki nim możemy operować na wyrażeniach, które mają czytelniejszą dla człowieka formę. Powyższa konstrukcja jest również idealnym przykładem sumy warunkowej - dodajemy tylko te elementy, które zostały zaplanowane i które są przychodami. Formuła ta wygląda na początku dziwnie, ale gwarantuje Wam że działa jak najbardziej poprawnie i jak tylko się z nią dobrze zapoznacie, to będzie ona często bardziej funkcjonalna niż kombinowanie z funkcjami typu JEŻELI.

Podsumowanie kategorii

Zwieńczeniem całego arkusza, jest sekcja w której znajduje się podsumowanie każdej z kategorii. W tym przypadku nazwy kategorii wyciągane są z tablicy kategorii, a następnie na ich podstawie wyliczamy odpowiednie sumy, bazując na kwotach umieszczonych w tabeli Zestawienie.

Podsumowanie

Najwyższa pora na małe podsumowanie. Excel z którego korzystałem w tym wpisie, został skrojony pod moje osobiste potrzeby i skupia się na kilku ważnych dla mnie aspektach. W łatwy można go jednak zmodyfikować poprzez dodanie/usunięcie kolumn tabeli w zestawieniu, bądź też modyfikację pozycji w istniejących listach.

Można również zostawić jeden arkuszy w wersji bazowej zawierający wszystkie listy oraz tabele oraz przygotować kilka kolejnych, które będą się do niego odwoływać, a same w sobie zawierać będą tylko zestawienie z wybranego miesiąca. Różnych opcji jest sporo - warto pokombinować samemu;-)

Podoba Ci się ten wpis? Powiedz o tym innym!

Send to Kindle

Komentarze

blog comments powered by Disqus