Frissítve: 2022 augusztus 26.
Miről szól ez a cikk?
A mindennapok során számos képlet ismeretére van szükségünk.
Az egyik legtöbb problémát a nettó, ÁFA és bruttó számításokhoz kapcsolódó képletek ismeretének hiánya okozza, például a kedvezmények és árcsökkentések mértékének kiszámolása, ellenőrzése terén. A kereskedelemben sajnos napi jelenség a kedvezmények mértékével, az eredeti árral és a kedvezményes árral való félreérthető kommunikáció, így pénztárcánkba vágó érdekünk, hogy tisztában legyünk ezen számításokkal.
Az Excel kitűnően használható a nettó, ÁFA és bruttó számításokhoz, így a bevezető indokai miatt is előszeretettel kérdeznek rá a képletek ismeretére a vizsgákon. Érdemes tehát megtanulnunk, hogyan számítható ki az egyik érték a másik kettő ismeretében, milyen képleteket használhatunk a számításokhoz!
Álljunk meg egy szóra!
Az ÁFA az Általános Forgalmi Adó kifejezés rövidítése, egy olyan adónemet takar, amely a fogyasztást adóztatja. Az árut és/vagy szolgáltatást nyújtó gazdálkodó szervezetek termékeik és/vagy szolgáltatásaik nettó árának ÁFÁ-val növelt értékét, vagyis a bruttó árat kell felszámolniuk a vevőnek.
Mielőtt ismertetnénk a képleteket, emlékezzünk arra, hogy az Excelben a százalékos értékeket kétféle módon is rögzíthetjük egy cellában, vagy gépelhetjük be állandó értékként (konstansként) egy képletbe.
-
Az egyik megoldás, amikor csupán magát az értéket adjuk meg, mindenféle utalás nélkül annak százalékos voltára, például a 27% helyett mindössze a 27 számot adjuk meg. Ha így teszünk, a képletek készítésekor figyelnünk kell arra, hogy a megfelelő módon osszunk vagy szorozzunk százzal.
-
A másik esetben élünk az Excel automatikus formázásával, amely lehetővé teszi, hogy 27% formában gépelhessünk be százalékos értéket egy cellába vagy egy képletbe (a % jelet célszerű közvetlenül a szám után gépelni). Az előbbi esetben a cellában és a Szerkesztőlécen egyaránt 27% fog megjelenni, de a számításokban ennek század részével, 0,27-vel fog számolni a program.
Figyeljünk tehát a képletek készítésekor, hogy melyik formátumban találhatók meg az ÁFA értékek a cellákban, illetve tudatosan alkalmazzuk a megfelelő formátumot!
Álljunk meg egy szóra!
A cikk írásakor a nullától eltérő ÁFA kulcsok a következők: 5%, 18%, 27%.
1. A Bruttó kiszámítása a Nettó és az ÁFA ismeretében
A leggyakoribb feladat, amikor a Nettó érték és az ÁFA ismeretében kell kiszámolnunk a Bruttó értéket. A kiskereskedelemben ugyan kötelező a Bruttó értéket feltüntetni az árcédulákon, de számos esetben előfordul, hogy a Nettóval és az ÁFÁ-val kell számolnunk. Egyéb számításoknál is hasznos az alábbi képlet, ugyanis előfordulhat, hogy egy korábbi értéket kell megnövelnünk megadott százalékkal.
Ha az ÁFÁ-t számként adtuk meg, akkor a képlet a következő:
Bruttó=Nettó*(1+ÁFA/100)
Látható tehát, hogy az ÁFA értékét el kell osztanunk százzal, hozzá kell adnunk 1-et, majd meg kell szoroznunk a Nettó értékével.
Nézzünk erre egy olyan példát, amikor az értékeket állandóként gépeljük be a cellába, mintha csak számológépnek használnánk az Excelt! Gépeljük be az alábbi képletet egy cellába, majd üssük le az Enter billentyűt!
=10000*(1+27/100)
Az eredmény 12700 lesz, vagyis 10000-nek a 27%-al megnövelt értékére voltunk kíváncsiak. Másképp fogalmazva, ha egy termék vagy szolgáltatás ára nettó 10000 forint, akkor annak bruttó ára a 27% ÁFÁ-val növelt értéke lesz, ami 12700 Ft.
Ha azonban százalékban adjuk meg az ÁFA értékét, akkor a képletünk a következőképpen módosul:
Bruttó=Nettó*(1+ÁFA%)
a példánk pedig a következő lesz:
=10000*(1+27%)
vagy, ha az összeadást elvégezzük a zárójelek között, akkor a képlet formája:
=10000*1,27
hiszen 1+0,27, egyenlő 1,27-el. Az eredménynek szintén 12700-nek kell lennie.
2. Az ÁFA kiszámítása a Nettó és a Bruttó ismeretében
A következő feladat az ÁFA értékének kiszámítása a Nettó és a Bruttó ismeretében. Talán akkor a leghasznosabb az alábbi képlet ismerete, ha vásárláskor ellenőrizni akarjuk egy kedvezmény mértékét – az eredeti árból le kell vonnunk annak megadott százalékát, hogy megkapjuk a kedvezményes árat. Ugyanakkor ez a képlet nem csak pénzügyi, hanem más számításoknál is gyakran előfordul, amikor arra vagyunk kíváncsiak, hogy egy korábbi és egy újabb érték között mekkora volt a növekmény, hány százalékkal nőtt vagy csökkent az új érték a korábbihoz képest.
Ha az ÁFÁ-t számként akarjuk megkapni, akkor a képlet a következő:
ÁFA=(Bruttó/Nettó-1)*100
vagyis a Bruttót elosztjuk a Nettó értékkel, majd kivonunk belőle egyet, és az eredményt megszorozzuk százzal.
Használjuk fel az előző példa értékeit: kíváncsiak vagyunk, mennyi az ÁFA, ha a Bruttó 12700, a Nettó pedig 10000. Gépeljük be egy cellába az alábbi képletet, majd üssük le az Enter billentyűt:
=(12700/10000-1)*100
az eredmény 27 lesz.
Ha az ÁFÁ-t törtszámként, százalékban akarjuk megkapni, akkor a képlet a következő:
ÁFA=Bruttó/Nettó-1
Próbáljuk ki a képletet az alábbi példa segítségével:
=12700/10000-1
az eredmény 0,27 lesz, majd a cella Számformátumát módosítsuk Százalékra (Alt+Ő, Á) vagy használjuk a Ctrl+Shift+5 billentyűparancsot!
Tipp: Néhány Számformátumot a következő billentyűkombinációkkal tudunk gyorsan és kényelmesen alkalmazni: Ctrl+Shift+1 – formázás Szám Számformátumra két tizedesjeggyel és ezres tagolással, Ctrl+Shift+2 – formázás Egyéni Számformátumra a cellában található egynél kisebb tizedes törtet tartalmazó érték óra:perc formátumra alakításával, Ctrl+Shift+3 – formázás Egyéni Számformátumra a cellában található érték nn.hhh.éé formátumra alakításával, Ctrl+Shift+4 – formázás Pénznem Számformátumra két tizedesjeggyel és ezres tagolással (negatív érték esetén piros betűszínnel), Ctrl+Shift+5 – formázás Százalék Számformátumra tizedesjegyek nélkül egészre kerekítéssel.
3. A Nettó kiszámítása az ÁFA és a Bruttó ismeretében
Végül nézzük az utolsó esetet, amikor a Nettót kell kiszámolnunk az ÁFA és a Bruttó ismeretében. Erre a számításra lesz szükségünk, ha egy vásárlást követően kíváncsiak vagyunk a Nettó értékre.
Ha az ÁFA számként áll rendelkezésünkre, akkor a képlet a következő:
Nettó=Bruttó/(1+ÁFA/100)
vagyis a Bruttót elosztjuk az egy és az ÁFA százzal osztott értékének összegével. Próbáljuk ki az alábbi képletet:
=12700/(1+27/100)
vagy, ha az osztást és az összeadást elvégezzük a zárójelek között, akkor a képlet formája:
=12700/1,27
Ha azonban százalékban adjuk meg az ÁFA értékét, akkor a képletünk a következőképpen módosul:
Nettó=Bruttó/(1+ÁFA%)
a példa pedig így fog kinézni:
=12700/(1+27%)
vagyis
=12700/1,27
Ezzel készen is vagyunk, megismertük mindhárom módszert.
4. Hivatkozás az ÁFA értékére
A fenti számításokat tartalmazó összetett táblázatokban nem szoktunk külön oszlopot vagy sort szánni az ÁFA értékeknek, mivel azok többnyire azonosak. Ezzel elkerülhetjük az ismétlődő adatok tárolásából fakadó hibákat az esetleges javításoknál, átvezetéseknél. Tehát, akár egy, akár több ÁFA értékkel kell számolnunk, inkább rögzítsük azokat egy-egy cellában a táblázat egy más részén, a feladattól függően számként vagy százalékos formában.
Ha az így rögzített ÁFA értékek celláira oszlopok és/vagy sorok képleteiben kell hivatkoznunk, akkor használjunk abszolút hivatkozást. Az alábbi ábrán bemutatunk egy lehetséges megoldást (a munkafüzet letölthető innen).
Az ÁFA értékét a C1 cellában, a táblázat felett helyeztük el, így az egyes termékek Bruttó árát a C4:C12 tartományban úgy számoljuk ki, hogy az ÁFA% értékére a $C$1 abszolút címmel hivatkozunk. Mivel ezzel az elrendezéssel nem kellett készítenünk egy ÁFA oszlopot csupa 27% értékkel, táblázatunk kisebb, áttekinthetőbb lett, valamint az ÁFA értékének esetleges változását is mindössze egy cellában kell átvezetnünk.
5. Használjuk a LET függvényt!
Egy összetett táblázatban sokat számít, ha a képletek olvashatók. Ezt sokan úgy oldják meg, hogy azokhoz a cellákhoz, tartományokhoz, amelyekre számos és/vagy összetett képletben hivatkoznak, tartománynevet rendelnek – vagyis elnevezik azokat, így a képletekben már a tartománynévvel helyettesíthetik a hivatkozásokat.
Ez azonban kissé körülményes megoldás: éppen ezért vezette be a Microsoft a LET függvényt, amellyel elhagyhatjuk a tartományneveket, helyette a LET függvényben megadott beszédes nevű változónevekkel hivatkozhatunk cellákra és/vagy tartományokra.
Álljunk meg egy szóra!
A cikk utolsó frissítésekor a LET függvényt csak a Microsoft 365 és a Microsoft Office 2021 Excel alkalmazásai ismerik.
Nézzük csak az alábbi képletet, amellyel a fenti munkafüzet C4 cellájának képletét cseréltük le a LET függvényre, kiszámolva a bruttót a nettó és az ÁFA ismeretében:
=LET(NETTÓ;B4;ÁFA;$C$1;NETTÓ*(1+ÁFA))
Az első változó neve NETTÓ, a hozzá tartozó érték a B4 – ez a LET függvény első két paramétere. Az ÁFA nevű változó a C1 cellára hivatkozik, ez az újabb két paramétere a LET függvénynek. Mivel többre nincs szükség, jöhet az utolsó paraméter, amely maga a képlet a változónevekkel. Olvashatóbb, ugye? Lendüljünk bele, nézzük a többi képletet!
A nettó (B4) és az ÁFA (C1) ismeretében, ha az ÁFA számként lett megadva, a képlet a következő formát ölti:
=LET(NETTÓ;B4;ÁFA;$C$1;NETTÓ*(1+ÁFA/100))
Ha a C1-ben keressük az ÁFA értékét, a nettó (B4) és a bruttó (C4) ismeretében:
=LET(NETTÓ;B4;BRUTTÓ;C4;(BRUTTÓ/NETTÓ-1)*100)
vagy
=LET(NETTÓ;B4;BRUTTÓ;C4;BRUTTÓ/NETTÓ-1)
majd Ctrl+Shift+5 a Százalék Számformátumra alakításhoz. Végül készítsük el azt a két képletet, amelyekkel a nettót (B4) keressük az ÁFA (C1) és a bruttó (C4) ismeretében:
=LET(BRUTTÓ;C4;ÁFA;$C$1;BRUTTÓ/(1+ÁFA))
Ha az ÁFA értéke nem Százalék Számformátumú, akkor:
=LET(BRUTTÓ;C4;ÁFA;$C$1;BRUTTÓ/(1+ÁFA/100))
Tipp: Egy ilyen változónév-érték párosból legalább egyet, legfeljebb 126 darabot adhatunk meg. A változónevek kis- és nagybetűsek is lehetnek és egy szóból kell állniuk: Szűrt_tartomány, KERÜLET, Létszám stb. Az érték helyén állhat konstans érték, cella- vagy tartományhivatkozás, és összetett képlet is.
6. Gyakorlópélda
A hivatkozott munkafüzetben találunk még három munkalapot (Bruttó, ÁFA és Nettó néven), amelyeken gyakorolni tudjuk a fenti képleteket. A Bruttó munkalapon a Bruttó ár oszlop hiányzik, tehát a Nettó ár és az ÁFA alapján kell kiszámolnunk a hiányzó értékeket. Az ÁFA munkalapon valamelyik terméksor felhasználásával számoljuk ki az ÁFA értékét. Vigyázzunk, ahogy a B1 cella tartalma is utal rá, a C1 cella százalék formátumkóddal rendelkezik! Végül a Nettó munkalapon a táblázat Nettó ár oszlopának értékeit kell kiszámolnunk.
7. Összefoglalás
Foglaljuk össze a Nettó, ÁFA és Bruttó értékeket kiszámító képleteket:
Nettó és ÁFA ismert: Bruttó=Nettó(1+ÁFA/100)
Nettó és ÁFA% ismert: Bruttó=Nettó(1+ÁFA%)
Nettó és Bruttó ismert: ÁFA=(Bruttó/Nettó-1)*100
Nettó és Bruttó ismert: ÁFA%=Bruttó/Nettó-1, majd Ctrl+Shift+5
ÁFA és Bruttó ismert: Nettó=Bruttó/(1+ÁFA/100)
ÁFA% és Bruttó ismert: Nettó=Bruttó/(1+ÁFA%)
Táblázatokban az ÁFA értékét többnyire egy cellában rögzítjük, amire abszolút címmel hivatkozunk a képletekben.
VÉGE.
Infopanel
Készült: 2020 február 1.
Operációs rendszer: Windows 10 (21H2, 2111b19044)
Irodai programcsomag: Microsoft 365 v2207b15427.20210, Aktuális csatorna
Szint: kezdő, ECDL modul/tanmenet: Táblázatkezelés/S6/4.1
Kategória: Táblázatkezelés → Képletek és függvények → Aritmetikai képletek
![Napi billentyűparancs: az aktuális dátum és idő beillesztése egy Word dokumentumba [#129]](https://www.fiatluxefel.info/wp-content/uploads/2020/10/BejegyzesFaceTwitter_Napibillentyuparancs_1-300x157.png)
