fbpx

Prosty budżet domowy – formuły ułatwiające wpisywanie danych

21.10.2014 | Budżet domowy, ECP2

Do wpisania przychodów i wydatków (częściej wydatków…) biorę się przynajmniej raz w tygodniu. Staram się robić to na bieżąco, ale wiadomo jak jest. 😉 Zawsze kilka paragonów mam do wpisania. Gdy mam zaległości, to potrafi się tego zebrać sporo – oj potrafi! Aby więc jak najlepiej przyspieszyć to jakże pasjonujące zajęcie (mam nadzieję, że ironia dała się słyszeć;)), do tabeli danych wpisałam formuły, które uzupełniają za mnie większość pól w tabeli. Wystarczy, że wpiszę datę, cenę, sposób płatności i nazwę wydatku, a reszta wypełni się sama. Ta reszta to pola In/Out, Kategoria, Rodzaj, Osoba, Rok, Mc i Rok-mc. Dużo. 🙂

W tym artykule pokażę ci te formuły. Jednocześnie będzie to ostatni artykuł dotyczący wpisywania przychodów/ wydatków. W kolejnym będziemy już działać, wykorzystując wpisane dane.

Aby przychód/ wydatek mógł zostać rozpoznany, trzeba go najpierw umieścić w słowniku Sl_Nazwy w arkuszu Słowniki. Poniższy obrazek prezentuje przykładową listę przychodów/ wydatków w tym słowniku.

MalinowyExcel-prosty-budżet-domowy-formuły1

Przykładowa lista przychodów i wydatków w słowniku

Zauważmy, że do każdej nazwy (Kolumna A – Nazwa) przypisane są dane w kolumnach In/out, Kategoria, Rodzaj, Osoba. Po wpisaniu nazwy przychodu/ wydatku do tabeli danych, na podstawie słownika Excel uzupełni kolejne pola w wierszu.Cudowne, czyż nie? Oprócz tego, po wpisaniu daty uzupełnią się pola Rok, Rc i Rok-mc. Te ostatnie przydadzą się do późniejszych analiz.

Formuły uzupełniające In/Out, Kategoria, Rodzaj, Osoba

Wszystkie te formuły wyglądają praktycznie tak samo: korzystają z funkcji WYSZUKAJ.PIONOWO. Różnią się jedynie numerem kolumny, którą zwraca ta funkcja. Rozłóżmy więc funkcję WYSZUKAJ.PIONOWO na czynniki pierwsze:)

Zauważ, że za każdym razem szukamy jednej i tej samej wartości: nazwy przychodu/ wydatku. Zawsze szukamy jej w tabeli Sl_Nazwy i chcemy otrzymać wartości przyporządkowane dokładnie do szukanej wartości. Są to trzy z czterech argumentów WYSZUKAJ.PIONOWO:

WYSZUKAJ.PIONOWO(czego_szukasz; gdzie_szukasz; nr_kolumny_do_wyświetlenia; sposób_szukania)

To moje nazwy argumentów tej funkcji – oryginalne niekoniecznie są… hmmm… jakby to określić… intuicyjne 😉

Trzecim agrumentem (nr_kolumny_do_wyświetlenia) będą liczby oznaczające poszczególne kolumny w przeszukiwanej tabeli Sl_Nazwy. Czyli:

  • In/Out: 2,
  • Kategoria: 3,
  • Rodzaj: 4,
  • Osoba: 5.

Zanim przejdę do opisywania formuł, przypomnę szybko wygląd tabeli (zmieniłam tylko kolejność kolumn: Sposób płatności przeniosłam za PLN – tak będzie nam wygodniej wpisywać):

MalinowyExcel-prosty-budżet-domowy-formuły2

Aby zatem w kolumnie In/out wyświetlała się słownikowa pozycja, wpisz w komórce E2 taką formułę:

=WYSZUKAJ.PIONOWO([@Nazwa];Sl_Nazwy;2;0)

Aby uzyskać ten sam efekt w kolumnie Kategoria, wpisz w komórce F2:

=WYSZUKAJ.PIONOWO([@Nazwa];Sl_Nazwy;3;0)

Rodzaj – w G2:

=WYSZUKAJ.PIONOWO([@Nazwa];Sl_Nazwy;4;0)

Osoba – w H2:

=WYSZUKAJ.PIONOWO([@Nazwa];Sl_Nazwy;5;0)

Zauważ, że funkcja jest prawie taka sama dla każdej kolumny. Różni się tylko trzecim argumentem, czyli numerem kolumny, z której wybierana jest wartość będąca wynikiem funkcji (zwracana jest wartość).

Wpisanie do tabeli formuł mających odwołania do konkretnych kolumn tabeli ([@Nazwa]) jeszcze bardziej ułatwi nam uzupełnianie danych. Formuły te będą się bowiem automatycznie kopiowały do nowych wierszy. Genialne!

Formuły związane z datą

Teraz jeszcze wpiszemy formuły dat, czyli uzupełnimy kolumny Rok, Mc i Rok-mc.

Formuła dla roku w komórce J2 to:

=ROK([@Data])

Formuła miesiąca w komórce K2:

=MIESIĄC([@Data])

Formuła łącząca rok i miesiąc w komórce L2:

=[@Rok]&"-"&TEKST([@Mc];"00")

I tyle 🙂 Teraz za każdym razem, gdy wpiszesz przychód/ wydatek, który widnieje w arkuszu Słowniki, reszta danych uzupełni się sama. A jeśli nazwy przychodu/ wydatku nie będzie w słowniku, formuły wyszukiwania zwrócą błąd i trzeba będzie wypełnić pola ręcznie. Sugeruję zostawić formuły w takiej postaci, aby ten błąd zwracały – będzie on wtedy widoczny, co zachęci, aby go zlikwidować, czyli poprawnie uzupełnić dane. 🙂

A tak wygląda tabela z wpisanymi formułami: jeden wiersz pusty, a drugi – uzupełniony danymi.

MalinowyExcel-prosty-budżet-domowy-formuły3

Tak jak napisałam na początku artykułu, to już ostatni wpis związany z tabelą danych. W kolejnym będę już omawiała ciekawsze kwestie 🙂

 

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

18 komentarzy

  1. hmmm, nie wiem w czym rzecz, ale u mnie żadna z tych funkcji nie działa… nie wiem, co mogę robić źle, bo póki co krok po kroku robię to co Ty:-(

    Reply
    • Hmmm, to dziwne. A możesz wysłać mi plik? Zobaczę co i jak 🙂

      Reply
  2. co znaczy małpa w funkcjach?

    Reply
    • Małpa w formułach oznacza „ten wiersz”. Czyli formuła wpisana w konkretnym wierszu tabeli odwołuje się do tego samego wiersza, w którym jest. O rany, chyba trochę skomplikowałam 🙂 Wprowadzono to w Excelu 2010. W 2007 był taki zapis: [#Ten wiersz].

      Doris, może to rozwiązuje Twój problem? Z jakiej wersji Excela korzystasz? Może zamiast wpisywania takich formuł, jakie podałam, wskaż konkretne zakresy? Napisz, czy to coś pomogło.

      Pozdrawiam
      Malina

      Reply
  3. mam po prostu komunikat, ze jest za duzo argumentów funkcji

    Reply
    • Doris,

      zobacz moją odpowiedź poniżej – mam nadzieję, że to pomoże.

      Pozdrawiam
      Malina

      Reply
  4. u mnie też nie działa polecenie „wyszukaj pionowo”
    z tego co widzę to chodzi o argument [@nazwa] – co tu konkretnie wpisać jeśli np. tworze formułę w kolumnie Kategoria.
    Wpisywałam już tak jak podałaś dokładnie [@Nazwa] i poza tym jeszcze [#Nazwa], [#Kategoria], [Kategoria], Nazwa i wiele innych kombinacji i nic.
    Mam office’a 2007.
    poprzednie rzeczy robilam wg twoich wskazowek i działają bez zarzutu.
    Jestem początkująca jeśli chodzi o excell.
    prosze o odpowiedź.

    Reply
  5. ok już wiem 🙂 działa
    biorę się za kolejny etap szkolenia
    pozdrawiam

    Reply
    • Super, cieszę się 🙂

      Reply
  6. Dla czego zamiast używać excela nie użyć programu komputerowego, dedykowanego do prowadzenia budżetu?
    Na rynku jest ich wiele, ja polecam opiumsoft.pl. Dobry bo POLSKI i DARMOWY.

    Reply
    • Irek,

      jak najbardziej. Czemu Excel? Jestem jego fanką i wiem, że wiele osób również 🙂

      Pozdrawiam,
      Malina

      Reply
  7. hej, a będzie ciąg dalszy?

    Reply
    • Tak, następny (jestem w trakcie pisania) będzie o tworzeniu tabeli przestawnej podsumowującej wydatki 🙂 Już wkrótce!

      Pozdrawiam,
      Malina

      Reply
  8. Mam problem wlaśnie z tymi formulami. Cały czas wyskakuje mi ze wystapil blad i jeszcze cos tam a potem przekierowuje mnie do pomocy. Mam office 2007. Jak to rozwiazaliscie?

    Reply
    • Witaj Ania,

      może spróbuj wpisywać adresy komórek zamiast nazw pól tabeli. Np.: zamiast =WYSZUKAJ.PIONOWO([@Nazwa];Sl_Nazwy;2;0) wpisz tak: =WYSZUKAJ.PIONOWO(B2;Sl_Nazwy;2;0).

      Daj znać, czy teraz działa.

      Pozdrawiam
      Malina

      Reply
  9. Witaj Malino!
    Mam problem z wprowadzeniem formuły do komórki którą wcześniej zdefiniowała jako komórkę w której rozwija się listę. Np. kiedy próbuję wprowadzić formułę w E2 (kolumna In/Out) wyskakuje mi komunikat, że wprowadzona wartość jest nieprawidłowa ponieważ użytkownik ograniczył wartości, które mogą być w niej wprowadzane. Co mogę z tym zrobić?
    Z góry dziękuję za pomoc!

    Reply
    • Hmmm… wygląda na to, że wprowadzasz inna wartość do komórki, niż jest na liście (stąd pojawia się komunikat). Sprawdź, czy na liście istnieje dokładnie taka wartość, jaką wprowadzasz do komórki 🙂 Może masz jakąś literówkę?

      Pozdrawiam
      Malina

      Reply

Leave a Reply to Doris Anuluj pisanie odpowiedzi

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

Pin It on Pinterest