Hogyan lehet kategorizálva figyelni egy kiadás-bevétel nyilvántartásban, hogy az egyes terméktípusokra mennyit költünk, vagy adott bevételtípusból pontosan mennyi jött össze? Meg lehet ezt vajon oldani kezdő szinten, vagy bonyolult funkciókat kell hozzá ismerni? Erre kaphatsz választ ebben a bejegyzésben.
Két hete a Dunakeszi Diófa Nagycsaládosok Egyesületében tartottam egy utolsó, konzultációs alkalmat azoknak, akik az Excel tanfolyamra jártak. Átnéztük, befejeztük a tanfolyam tananyagát adó kiadás-bevétel nyilvántartásunkat. Ezután az egyik résztvevőben felmerült egy kérdés, amiről úgy gondoltam, másokat is érdekelhet.
Mégpedig az, hogy rendben van, hogy írogatjuk a kiadásokat és bevételeket, és így összesítve látjuk, hogy mennyit költünk egy adott hónapban, illetve átlagosan egy nap, de hogy fogjuk látni, hogy mik azok a dolgok, amire esetleg lehetne kevesebbet is költeni?
Most egy olyan megoldást mutatok, amit egy kezdő táblázatkezelő is el tud készíteni.
A nyilvántartás előkészítése
A dolog úgy fog működni, hogy a kiadásainkat és bevételeinket kategóriákba soroljuk, és mindegyik kategóriának adunk egy számot. Ezt a nyilvántartásunk mellé le is írjuk egy kis táblázatba, hogy az új tételek beírásánál lehessen puskázni.
A nyilvántartásunkban egy új oszlopot szentelünk arra, hogy minden tételhez a kategória kódszámát tároljuk.
Ha a táblázatunk így elő van készítve, jöhet a függvény.
A SZUMHA függvény
A megoldáshoz a SZUMHA függvényt fogjuk használni. Ez a függvény ugyanazt a műveletet végzi, mint a sima SZUM függvény (összeadás), de egy feltétellel megadhatjuk neki, hogy a megadott tartományból csak a feltételnek megfelelő számokat adja össze.
Nekünk éppen erre van szükségünk. A kis segédtáblázatunkban a kódok mellé ezzel a függvénnyel gyönyörűen ki fogjuk tudni számolni, hogy az aktuális sorban levő kódhoz tartozó kategóriában mennyit költöttünk, vagy mennyi bevételünk volt. Egyszerűen annyi a dolgunk, hogy feltételként megadjuk a kódszámot.
A függvénynek három paramétere van, ebből kettőt kell megadni kötelezően , a harmadik opcionális.
A tartomány az a tömb, ahonnan ki kell majd válogatni a vizsgálandó értéket.
A kritérium lesz az a feltétel, ami alapján majd ki kell válogatni az összeadandó értékeket.
Az összeg tartomány az a tömb, ahol a tulajdonképpeni összeadandó értékek vannak. Ezt csak akkor kell megadni, ha nem egyezik meg a vizsgált tartománnyal.
De nézzük akkor konkrétan, hogy itt hogy is fogjuk használni.
A megoldás
A kategóriákat tartalmazó segédtáblázathoz adjunk még két oszlopot, az egyikbe a kiadásokat, a másikba a bevételeket fogjuk kiszámolni.
Tehát a pontos megoldásunk a következő lesz, miután előhívtuk a függvényvarázslóból a SZUMHA függvényt:
A tartományhoz a nyilvántartásunknak azt az oszlopát jelöljük ki, ahova a kategóriákat írtuk. Fontos, hogy az F4 billentyűvel ezt a tartományt rögzíteni kell, hiszen a többi sorra át szeretnénk majd másolni a képletet.
A kritériumban elég megadni egy számot, ez pedig az aktuális sorban levő kategória száma lehet, így majd lefele másolva a képletet mindig az aktuális sor kategóriájában tartozó kiadásokat fogja összeadni az Excel.
A mi esetünkben szükség van az Összegtartomány paramétert is megadni, hiszen nem a kategóriák sorszámát szeretnénk összeadni, hanem a kívánt kategória sorában szereplő Kiadást. Így itt meg kell adnunk azt az oszlopot, amiben a kiadások összegét tartjuk nyilván, hogy majd ténylegesen azt adja össze a program. Természetesen a későbbi képletmásolás miatt ezt a tartományt is érdemes lerögzíteni, hogy mindig ugyanezeket az összegeket vizsgáljuk.
Amikor mindent beállítottunk, a Kész gomb megnyomásával meg is nézhetjük az első eredményt. Ha rendben van, akkor lehet lefelé másolni az összes kategóriára.
A bevételhez ugyanezek a paraméterek szükségesek, az egyetlen különbség az, hogy a ténylegesen összeadandó tartomány itt majd a Bevételek összegét tartalmazó oszlop lesz, ugyanúgy rögzítve, mint a kiadásoknál.
A végeredmény valahogy így néz ki, természetesen az eredmények mások lehetnek, a nyilvántartás összegeitől függően.
****************************************************************
Szeretnél ilyen kiadás-bevétel nyilvántartást készíteni magadnak, de túl bonyolultnak tűnik? Még nem igazán barátkoztál meg az Excellel? Jó hírem van!
Hamarosan indul az online Excel tanfolyam kezdőknek, ahol éppen ezt a nyilvántartást készítjük el, közben pedig szépen megtanulhatod a táblázatkezelés alapjait. Ha szeretnél értesülni az indulásról, és további részleteket megtudni a tanfolyamról, iratkozz fel a Maja Sulija hírlevelére, hogy ne maradj le semmiről!
****************************************************************
Szép napot!
Nincsenek megjegyzések:
Megjegyzés küldése
Kérlek, oszd meg velem a bejegyzéssel kapcsolatos gondolataidat!