A költségvetési táblázatok kiváló eszközök a pénzügyek kezelésére, de a bennük található adatok és számítások gyorsan túlterheltté tehetik őket. Szerencsére a Google Sheets számos olyan képletet kínál, amelyek egyszerűsíthetik a költségvetési folyamatot. Ezek a képletek segítenek a kiadások nyomon követésében, a bevételek kezelésében és a pénzügyi céljaid elérésében. Ha szeretnéd pénzügyeidet átláthatóbbá és könnyebbé tenni, ezek a funkciók elengedhetetlenek a sikerhez. Megmutatjuk, hogyan használhatók az alábbi fejlett Google Sheets képletek a költségvetésed kezelésére, selejtezésére és elemzésére.
Tartalomjegyzék
1 AVERAGEIF
Az AVERAGEIF függvény segítségével kiszámíthatod egy cellatartományban lévő számok átlagát, amelyek megfelelnek bizonyos kritériumoknak. A függvény szintaxisa a következő:
=AVERAGEIF(range, criterion, [average_range])
Ahol a range az elérni kívánt cellatartomány, a criterion a teljesítendő feltétel, és a [average_range] a számolandó cellatartomány.
AVERAGEIF Példa Számítás
Tegyük fel, hogy létrehoztál egy költségvetési táblázatot, amely nyomon követi a különböző kiadásokat és az azokhoz kapcsolódó dátumokat, ahogy az alábbi példában is látható.
Azt a kérdést szeretnéd megválaszolni, hogy mennyit költesz átlagosan élelmiszerre, ahol az „Élelmiszer” szerepel az A oszlopban, az összeg pedig a B oszlopban, használd a következő képletet:
=AVERAGEIF(A:A, "Élelmiszer", B:B)
A mintaadatok alapján az élelmiszerre költött átlagos összeg 150 dollár.
2 SUMIF
A SUMIF lehetővé teszi, hogy összesd azokat az értékeket egy tartományban, amelyek megfelelnek bizonyos kritériumoknak. A SUMIF függvény szintaxisa a következő:
=SUMIF(range, criterion, [sum_range])
Ahol a range az értékeket értékelő cellatartomány, a criterion a teljesítendő feltétel, és a [sum_range] az összeszendő cellatartomány.
SUMIF Példa Számítás
Ha szeretnéd összegezni a boltban történő vásárlásaidat, használd a következő képletet:
=SUMIF(A:A, "Élelmiszer", B:B)
Ebben az esetben, a mintaadatok alapján az élelmiszerre költött összeg 450 dollár.
3 COUNTIF
A COUNTIF függvény segítségével megszámolhatod, hogy egy tartományban hány cella felel meg a meghatározott kritériumoknak. Ez megkönnyíti bizonyos kiadások gyakoriságának nyomon követését.
A COUNTIF függvény szintaxisa a következő:
=COUNTIF(range, criterion)
Ahol a range a megszálandó cellatartomány, a criterion azt az állapotot jelöli, ami a teljesítendő feltétel.
COUNTIF Példa Számítás
Ha meg szeretnéd számolni, hogy hányszor vásároltál élelmiszert, használd a következő képletet:
=COUNTIF(A:A, "Élelmiszer")
A mintaadatok alapján a képlet 3-at ad vissza, ami azt jelenti, hogy háromszor vásároltam élelmiszert. Ha több kritériumot szeretnél megadni, használhatod a COUNTIFS függvényt.
4 IFS
Az IFS egy fejlett függvény, amely lehetővé teszi több feltétel tesztelését. Hasznos költségek kategorizálásához különböző kritériumok alapján.
Az IFS függvény szintaxisa a következő:
=IFS(condition1, value_if_true1, [condition2, value_if_true2], ...)
Az condition1 az első feltétel, amit értékelni szeretnél, és a value_if_true1 az eredmény, ha ez a feltétel igaz. További feltételeket és a hozzájuk tartozó eredményeket adhatsz meg.
IFS Példa Számítás
Ha szeretnéd kategorizálni a kiadásaidat az összeg alapján, használd a következő képletet:
=IFS(B250, "Alacsony", B2100, "Közepes", B2>=100, "Magas")
Ez a képlet minden kiadást Alacsony, Közepes vagy Magas kategóriába sorol a megfelelő összeg alapján. Például, ha 150 dollárt költesz élelmiszerre, az Magasnak számít, ahogy az az alábbi képernyőképen is látható.
5 TEXT
A TEXT függvény formázza a számokat szövegként, ami hasznos a számok könnyen olvasható megjelenítéséhez (például pénznem vagy százalékok megjelenítése).
A TEXT függvény szintaxisa a következő:
=TEXT(value, format_text)
Ahol a value az a szám, amit formázni szeretnél, és a format_text a kívánt formátum (például pénznem vagy százalék).
TEXT Példa Számítás
Ha meg szeretnéd jeleníteni egy számot pénznemként, használd a következő képletet:
=TEXT(B2, "$#,##0.00")
Mivel a B2 cella értéke 150, a TEXT képlet azt $150.00-ként jeleníti meg, hogy világossá tegye, az érték pénzügyi értéket képvisel.
6 INDIRECT
Az INDIRECT függvény lehetővé teszi, hogy dinamikusan hivatkozz cellákra a szöveges string cella referencia átkonvertálásával. Ez lehetővé teszi, hogy frissítsd a képleteidet változó bemenetek alapján, például különböző lapok vagy tartományok használatával a táblázatodban.
Az INDIRECT függvény szintaxisa a következő:
=INDIRECT(ref_text, [a1])
Ahol a ref_text egy szövegként megadott referencia, és az [a1] egy opcionális argumentum, amely meghatározza, hogy a referencia A1 stílusú (IGAZ), vagy R1C1 stílusú (HAMIS) legyen-e. Alapértelmezetten az [a1] A1 stílusra van állítva (IGAZ).
INDIRECT Példa Számítás
Tegyük fel, hogy van egy cellára hivatkozásod szöveg formátumban (például “B2”), és szeretnéd ezt egy tényleges cella referencia átkonvertálni. Ezt a következő képlettel teheted meg:
=INDIRECT("B2")
Ez a képlet visszaadja a B2 cella értékét, ami 100 dollár.
Tegyük fel, hogy több lapot is tartalmaz a költségvetési munkafüzeted, amelyek mindegyike egy külön hónapot képvisel (például “Január”, “Február”, “Március”). Például az alább látható képernyőfelvétel a havi kiadások mintaadatát mutatja be Március hónapra:
Az összeg a B7 cellában található, és 1,775 dolgot jelent.
Tegyük fel, hogy szeretnél létrehozni egy összesítő lapot, amely az A oszlopban mutatja a hónapokat, és dinamikusan beolvassa a költségeket bármely adott hónapból, és ezeket a B oszlopba írja. Az alábbiakban egy példa arra, hogy hogyan nézne ki a lap.
Most feltételezzük, hogy szeretnéd dinamikusan beemelni a B7 cellából származó teljes kiadást a Március lapról és megjeleníteni az összesítő lapodon. Íme, hogyan használhatod az INDIRECT függvényt ehhez:
=INDIRECT("'" & A4 & "'!B7")
Ebben a példában az A4 hivatkozik arra a cellára az összesítő lapodon, amely tartalmazza a lap nevét, ahonnan a saját adataidat be szeretnéd vonni (például “Március”), és a képlet dinamikusan hivatkozik a B7 cellára a Március lapon, amely a hónap teljes költségeit tartalmazza. Az & jel a szövegstringek összefűzésére használt operátor.
Ebben az esetben egyesíti az egyes idézőjelet (‘) a nőtt nevekkel, az A4 által megadott lap nevével, és az ‘!B7’ számmal, ahol az indulásjel (!) különválasztja a lapennevet a cella hivatkozástól.
A képernyőfelvétel mutatja, hogy a Március havi kiadások összesen 1,775 dollár lett az INDIRECT képlet használatával. Ez megegyezik a Március lap havi kiadásaival. Érdekes módon, ha az A4 értékét “Február”-ra változtatod, az automatikusan frissíti a hivatkozást a Február lapra.
7 FILTER
A FILTER függvény lehetővé teszi, hogy kiszűrd az adatok egy tartományát meghatározott körülmények alapján – megkönnyítve számodra bizonyos kiadások vagy kategóriák kiemelését.
A FILTER függvény szintaxisa a következő:
=FILTER(range, condition1, [condition2], ...)
Ahol a range a kiszűrni kívánt adatok, és a condition1 és condition2 azok a feltételek, amelyeknek az adatoknak meg kell felelniük.
FILTER Példa Számítás
Íme, hogyan használható a FILTER függvény, hogy kiszűrjük az élelmiszerkiadásokat a mintadatok alapján:
=FILTER(A:B, A:A="Élelmiszer")
Írd be a képletet egy új oszlopba. Az én példámban E2 cellába írtam be az E oszlopban. A képlet visszaadja azokat a sorokat, ahol az A oszlopban „Élelmiszer” szerepel, lehetővé téve, hogy a élelmiszerkiadásokra összpontosíts munka nélkül.
8 XLOOKUP
Az XLOOKUP egy sokoldalú függvény, amely keres egy meghatározott tartományban egy egyezést és visszaadja a megfelelő értéket. Tökéletes a kiadások vagy kategóriák keresésére.
Az XLOOKUP függvény szintaxisa a következő:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Ahol a lookup_value a keresett érték, a lookup_array a keresési tartomány, a return_array pedig az a tartomány, ahonnan az eredményt vissza akarod adni. Az opcionális paraméterek segítenek meghatározni, mi történjen, ha az egyezés nem található, illetve hogyan kell kezelni a keresési kritériumokat.
XLOOKUP Példa Számítás
Tegyük fel, hogy van egy költségvetési táblázat, amely nyomon követi a különböző kiadásokat kategóriák szerint az A oszlopban, a költött pénzösszeget a B oszlopban, és az étkezések neveit a C oszlopban. Most szeretnéd meghatározni, mennyit költöttél egy specifikus élelmiszer termékre, például zöldségekre.
Íme az XLOOKUP függvény, amely segít neked ezzel:
=XLOOKUP("Zöldségek", C:C, B:B)
Ebben a példában a képlet 150 dollárt adott vissza, ez az összeg a Zöldségekre költött pénzösszeg volt.
Most, hogy bemutattuk a fent említett képleteket, láthatod, hogy ezek a funkciók jelentősen átalakíthatják a költségvetési folyamatodat, és megkönnyíthetik a pénzügyeid hatékony kezelését. Próbáld ki őket még ma, és fedezd fel, hogyan könnyíthetik meg a költségvetési feladatokat!