Az első részben elkészítettem a Számlák és Árfolyam munkalapokat, most – ígéretemhez hűen – folytatom a forint alapú tranzakciók illetve a Kategóriák munkalapokkal.
Az első rész itt olvasható.
Kategóriák munkalap
Ezen a munkalapon fogom tárolni a különféle pénzmozgáshoz tartozó kategóriákat, hasonló logikával, kóddal ellátva azokat mint amit már a Számláknál láthattunk. A kategóriákat hierarchikus módon építettem fel, tehát van egy kategória típus, egy főkategória ill. egy alkategória. Összegezve tehát, egy sor ezen a munkalapon tartalmaz:
- kód: rövid pár betűs, a tranzakcióknál fogom használni, bármi lehet, lényeg, hogy egyedi legyen
- típus: milyen irányú mozgást fog az adott kategória képviselni, nálam 3 lesz:
- Bevétel: pozitív pénzmozgás
- Kiadás: negatív pénzmozgás
- Átvezetés: amikor számláról – számlára mozog a pénz (2 db tranzakciót fog majd a valóságban eredményezni)
- főkategória: a hierarchia teteje, például:
Bevásárlás - alkategória: a fő kategória alatti megnevezés, például a
BevásárlásalattKávé és tea - megjelenítés: A típus, fő- ill. alkategória stílusosan formázott, megjelenítésre használt érték
Az átvezetéseknél annyi csalást elkövettem, hogy a főkategóriánál mindenképpen a forrás számlát, alkategóriánál pedig a cél számlát fogom beírni. Példa:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Kód | Típus | Főkategória | Alkategória | Megjelenítés |
| 2 | BF | Bevétel | Fizetés | Bevétel – Fizetés – | |
| 3 | KBZÖ | Kiadás | Bevásárlás | Zöldség és gyümölcs | Kiadás – Bevásárlás – Zöldség és gyümölcs |
| 4 | KBKÁ | Kiadás | Bevásárlás | Kávé és tea | Kiadás – Bevásárlás – Kávé és tea |
| 4 | ÁTFBK | Átvezetés | Tamás – Folyószámla | Barbi – Készpénz | Átvezetés – Tamás – Folyószámla > Barbi – Készpénz |
A logika ugyanaz, mint a Számláknál az előző bejegyzésben. A kódot a típus, fő- és alkategória kezdőbetűi alapján generáltam. Persze volt átfedés néhány kategória között, ott kézzel módosítottam. Itt is az egyetlen elvárás, hogy minden kód egyedi legyen.
A végleges táblázatból egy részlet így néz ki:
Az oszlopok nagyon hasonlóak képlet szempontjából a Számláknál olvasottakhoz:
- A oszlop:
=UPPER(CONCATENATE(LEFT($B2;1);LEFT($C2;1);LEFT($D2;1))) - E oszlop:
=CONCATENATE($B2;" - ";$C2;" - ";$D2)
Most már előállt minden, hogy létrehozzuk a forint alapú tranzakciók munkalapot.
Tranzakciók (HUF) munkalap
Elérkeztünk az Alfához és Omegához. Az agyhoz. Minden tudás bölcsőjéhez. Gyakorlatilag az összes munkalapunk ebből fog táplálkozni és mi is itt fogjuk időnk túlnyomó részét tölteni. A nem forint alapú tranzakciós munkalapok is az itt kialakított logikát fogják követni. Egy tranzakció az alábbi főbb ismérvekkel rendelkezik:
- mikor történt
- melyik számláról vagy számlára mozgott a pénz
- ki volt a tranzakciós partner (kinek fizettünk vagy kitől kaptunk pénzt)
- megnevezés
- milyen kategóriába tartozik
- mi volt a pontos összeg
A példa kedvéért: ma (mikor) elmentem a szemközti SPAR-ba (partner) pékárunak (kategória) egy kiló kenyeret (megjegyzés) vásárolni 759 forintért (pontos összeg), amit a folyószámlámhoz (számla) tartozó kártyával fizettem ki.
A fentiekből tehát az alábbi modell alakult ki:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Dátum | Számla kód | Számla megnevezés | Partner | Megnevezés | Kategória kód | Kategória megnevezés | Összeg |
| 2 | 2022. 10. 06. | TF | Tamás – Folyószámla | Munkahely neve | Fizetés 2022-10 | BF | Bevétel – Fizetés – | 123 456 HUF |
| 3 | 2022. 10. 06. | BF | Barbi – Folyószámla | Munkahely neve | Fizetés 2022-10 | BF | Bevétel – Fizetés – | 654 321 HUF |
| 4 | 2022. 10. 06. | BF | Barbi – Folyószámla | ALDI | Csoki | KBÉD | Kiadás – Bevásárlás – Édesség és nasi | 1 377 HUF |
| 5 | 2022. 10. 06. | BF | Barbi – Folyószámla | ALDI | Basmati rizs 2x5kg | KBTA | Kiadás – Bevásárlás – Tartós élelmiszer | 8 398 HUF |
Az egyes oszlopok igazából önmagukért beszélnek, de azért dióhéjban leírom:
- a számla kódnál azt a kódot kell használni, amely kóddal az adott számla a Számlák munkalapon megjelöltem
- A számla megnevezés automatikusan generált mező a számla kód alapján (a pivot táblához fog kelleni)
- Partner szabad szöveges mező, a Numbers úgy is fel fogja ajánlani, ha már egyszer használtunk egy értéket az oszlopban új sor rögzítésekor
- a megnevezés is szabad szöveges, én arra használom, hogy leírjam mit vettem adott kategórián belül
- a kategória kód is ismerős kell, hogy legyen, a Kategóriák munkalapról kell a szükségeset használni
- a kategória megnevezés – a számla megnevezéshez hasonlóan a kódból generálja le a megnevezést
- az összeg pedig hogy kiadás volt-e (negatív előjellel), vagy bevétel (pozitív előjellel)
A képletek szintjén pedig:
- C oszlop:
=INDIRECT(CONCATENATE("Számlák::Table 1::";ADDRESS(MATCH($B2;Számlák::Table 1::A;0);4;1;TRUE;));TRUE) - G oszlop:
=INDIRECT(CONCATENATE("Kategóriák::Table 1::";ADDRESS(MATCH($F2;Kategóriák::Table 1::$A;0);5;1;TRUE;));TRUE)
Nem fogok függvény magyarázatba kezdeni, arra ott van a gyári dokumentáció. Működésüket tekintve a lényeg ,hogy a jelenlegi munkalapon a kódot megkeresem a releváns munkalap táblázatában, majd a találati pozíciót átalakítom úgy, hogy pontos referencia címzés legyen és megjelenítem azt. Így a számla ill. kategória kódok alapján egész csicsás lesz automatikusan a végeredmény. Az összeg oszlopra még feltételes formázást is raktam, hogy vizuálisan is elkülönítsem a kiadásokat a bevételektől.
Oké, a tranzakciók előálltak, most térjünk vissza a Számlák munkalaphoz, ahol most már meg tudjuk mondani, hogy számolja össze a tranzakciók alapján az egyes számlákhoz tartozó egyenleget.
Számlák munkalap
Ezen a munkalapon a korábban már ismertetett E oszlopban fogunk változtatást eszközölni. Egy jól irányzott =SUMIF() és máris helyben vagyunk. A működési logika az, hogy az adott nem deviza típusú számlánál, ha a tranzakciós munkalapon egyezik az adott soron a számla kódja, akkor megcsináltatjuk a matekot. Elég egyszerű a képlet:
- E oszlop:
=SUMIF(B; "=BF"; 'Tranzakciók (HUF)'::Table 1::H)
Az "=BF"-t kell a megfelelő számlakódhoz igazítani. Végeredmény:
Ha megcsináltad a matekot akkor respect! 🙂
A következő részben megcsinálom a két nem forint alapú tranzakciós munkalapot, a pivot táblát, valamint ejtek pár szót a napi használatról is.
Köszi, ha elolvastad.