Excel képletek írása – Relatív és abszolút hivatkozások

Az Excel képletek segítségével le tudod egyszerűsíteni a számításokat. Ugyanis nem kell beírnod a számokat újra, hanem hivatkozni tudsz arra a cellára, amelyben az adott szám szerepel.

Nézd meg a videót, vagy olvass tovább, hogy hogyan tudsz egyszerűen Excel képleteket írni.

Ha szeretnél élesben gyakorolni, akkor a példát a Kezdő Példatárban vagy a VIP Klubban találod. Vagy ebben a másik videóban megmutatom, hogyan tudod gyorsan berögzíteni az adatokat Magad is az Excelbe.

Excel képletek egyszerűen

Az táblázatomban most szerepelnek a termékek, az hogy pontosan hány darabot adtunk el belőle és milyen áron. Ha szeretnéd kiszámolni a bevételt, akkor egész egyszerűen csak össze kell szorozni a darabszámot és a nettó árat.

Ahhoz, hogy az Excel tudja, hogy te most egy számítást fogsz elvégezni, egyenlőség jellel kell kezdened. (Ez a Shift 7-es billentyű.)

Hivatkozáshoz egyszerűen kattints az egérrel arra a cellára, amivel számolni szeretnél az Excel képletben.

Utána az egyik lehetőséged, hogy fogod az egeret, és rákattintasz az adott számot tartalmazó cellára, tehát darabszám szorozva (beírod szorzásjelet), majd rákattintasz a nettó árat tartalmazó cellára. Miután beírtad a képletet, az Enterrel tudod rögzíteni.

Nézzük, hogy lehet picit gyorsabban csinálni – egér nélkül

Hogyha szeretnéd felülírni a cellának a tartalmát, akkor nem kell kitörölni, hiszen a következő beírás automatikusan felülírja. Mondtam, hogy egyenlőség jellel kell kezdeni a képletet, ezt le lehet cserélni a + jelre. Ezt egy picit könnyebb megnyomni, ha van numerikus billantyűzeted, és az Excel ugyanúgy felismeri és kicseréli, kiegészíti az egyenlőség jellel.

Ahelyett, hogy az egérrel jelölnéd ki a B2-es cellát, ezt be is gépelheted. Ilyenkor nyugodtan használj kis betüket: b2*c2, és a végén Enter.

Vagy még gyorsabb, ha nem begépeled ezeket a cella azonosítókat, hanem ugyanúgy, ahogy a táblázaton belül is tudsz a nyilakkal mozogni, ugyanúgy a képleten belül is használhatod a nyilakat.

Excel képletek létrehozása, kijelölés nyilakkal

Nézzük a harmadik megoldást: + jellel kezdem a képletet és egész egyszerűen ellépdelek a balra nyíllal a B2-es celláig.

Ugyanúgy, mint az egérnél, itt is zizeg a cella körül a keret, vagy legalábbis szaggatott vonal, ami azt jelzi, hogy még tudom mozgatni a nyíllal. Akkor válik fixé, amikor beírom a műveleti jelet, jelen esetben a szorzás jelet.

Az elején elő szokott az fordulni, hogy ilyenkor az emberek a kék cellához viszonyítják a lépést, és jobbra indulnak el. Tulajdonképpen az Excelben mindig a képlet helyéhez képest mondjuk meg, hogy merre van az adott cella.

Azért is hívják ezt a hivatkozást relatív hivatkozásnak, mert azt jegyzi meg az Excel, hogy a képlet helyéhez képest melyik irányba, hány cellányira van az a cella, amit bevonunk a képletbe.

Ilyen esetben semmi gond, ha jobbra indultál el, nyomd meg a balra nyilat egészen addig, amíg el nem érsz a nettó árhoz. A végén lehet Entert nyomni, ugyanúgy kijön a számítás, ugyanúgy bekerült a B2*C2 a cellába.

Excel képletek másolása és ellenőrzése

Excel képletek másolásához húzd az egeret a képlet jobb alsó sarka fölé (megjelenik egy + jel). Ezen kattints duplán.

Hogyan kerül át a többi cellába is a képlet? Érdemes visszajönni az eredeti képletre, tehát a B2-ben van a kurzor.

A jobb alsó sarokban van egy bütyök vagy pontocska, ami fölé ha odahúzod az egeret, akkor megjelenik egy + jel. Ezen a + jelen kell duplán kattintani, akkor szépen az Excel automatikusan feltölti a képletet az összes sorhoz, tehát minden egyes sorra kiszámolja az értéket.

Ctrl lefelé nyíllal elugrok az utolsó cellára és az F2 funkciógombbal megnézem, hogy milyen képlet szerepel ebben az utolsó cellában.

Említettem, hogy az Excel alapértelmezetten relatív hivatkozásokat használ. Ez azt jelenti, hogy az első képletnél azt mondtam, hogy az előtte lévő cellát szorozza meg az előtte kettővel lévő cellával, és ezt megismétli szépen itt a hetedik sorban is.

Excel képletek ellenőrzése: Az F2 funkciógomb megmutatja, hogy milyen képlet került az utolsó sorba.

Amit még tudsz egyszerűsíteni

Visszalépek (Ctrl Z). Figyelhetsz arra, hogy amikor beírod a képletet, tehát balra 2 szorozva balra egyet, akkor a végén nem Entert nyomsz, hanem Ctrl Entert. Ilyenkor ugyanis bennmarad az adott cellában a kijelölés és rögtön tudod a duplakattal másolni az értéket.

Ha nem szeretsz másolni, akkor erősen javaslom neked, hogy haszáld a Tábálázat formátumot, amit a Kezdőlapon találsz Formázás táblázatként ikonnal, vagy pedig a gyorsbillentyűje magyarban a Ctrl R, angolban a Ctrl T. Enterrel máris bekapcsoltam a formátumot.

Erről van egy külön videóm is, mindenképpen javaslom, hogy nézd meg, hogy milyen előnyei vannak a táblázat formátumnak.

Amit én nagyon szeretek, hogy ha képletet írok be (Darab szorozva Nettó árral), akkor automatikusan kitölti az összes sorra, nem kell külön másolgatni:

Nézzük tovább a számítást. Szeretném kiszámolni az adott nettó összegre eső ÁFA tartalmat – és az ÁFA kulcs külön cellában szerepel, 27%.

Megtehetem azt, hogy ezt az eredeti összeget beszorzom 0,27-tel. Tehát beírom magát a számot, ilyenkor ki lehet hagyni a 0-át ugyanúgy, mint a számlógépeken is megteheted: +D2*,27

Máris kiírja az Excel az értéket, duplakattal pedig le tudom másolni. De mi van akkor, ha nem állandó a 27%? Ha inkább beírnám egy cellába, ahol könnyen megváltoztathatom.

Abszolút hivatkozás használata

Tehát biztosra szeretnék menni, mert mondjuk több országban különböző áfakulcsok vannak, vagy Magyarországon is időnként változhatnak az áfakulcsok, és ezért inkább kiírom egy külön cellába a százalékot.

Hogyan tudom ezt a számot bevonni a képletbe?

Ctrl Shift lefele nyíllal kijelölöm, majd kitörlöm a cellákat, és akkor nézzük a képletet: + jellel vagy = jellel kezdünk, D2 (nyíllal odalépsz), szorozva és ugyanígy a nyilakkal eljössz a 27%-ig.

Viszont itt a J4 cella már nem relatív hivatkozás, mint a D4 (mindig balról az elsőt szeretném venni). Itt nem mindig jobbról az ötödik cella meg lefelé a második cellát szeretném, hanem mindig fixen a J4-et szeretném ebbe a képletbe.

Ilyenkor rögzíteni kell a J4-et, hogy maradjon abban a cellában. Ezt hívjuk abszolút hivatkozásnak és dollárjellel jelöljük az Excelben.

Tehát a J4-es hivatkozásnál a J betű elé és a 4-es szám elé is dollárjelet kell tenni. Ezt a legegyszerűbben az F4 funkciógombbal teheted meg.

Ha többször megnyomod az F4 gombot, akkor láthatod, ahogy pörgeti, variálja a dollárjeleket. Összesen 4 féle képpen lehet kitenni a dollárjeleket. Innen meg tudod jegyezni, hogy ez az F4 funkcóbillentyű.

A végén Ctrl Entert nyomok, hogy maradjon a cellában, így a duplakattal egyből le is másolhatom a többi értékre. Az F2 funkciógombbal pedig ellenőrzöm magam az utolsó helyen, és nagyon szépen látszik, hogy valóban az előtte lévő nettó számot szorozza be fixen a J4-es cellában található áfakulccsal.

Nézzük meg ugyanezt a Táblázat formátumban is: Az Összeg (Bevétel)-t szorzom, ugyanúgy ellépdelek a 27%-ig, ugyanúgy F4-gyel berögzítem és az Enterrel szépen végigmásolja az összes sorba és ugyanazok az adatok jöttek ki.

Hogyan számolhatjuk ki a teljes Bruttó bevételt?

Nagyon egyszerűen, csak add össze a nettó összeget és az ÁFA-t és meglesz a bruttó érték: =D2+E2

Ilyenkor, amikor nem használod a Táblázat formátumot, hanem lemásolod az értékeket, akkor nyugodtam használd az egeret, a duplakattot, ez egy olyan trükk, ami tényleg nagyon praktikus, úgyhogy legálisan használhatod az egeret.

Természetesen ezt is, aki már rutinosabb, ki tudja váltani nyilakkal, mozgásokkal, de teljesen elfogadott és praktikus a duplaklikk használata is. Én is szoktam használni.

Ugyanezt a Táblázat formátumban: = Nettó összeg + ÁFA tartalom, itt viszont egyből lemásolja az értékeket.

Számold át dollárba is a forint bevételt

Ez egy viszonlag régi feladat, még a régi árfolyamokkal, de mindjárt meglátjuk ennek az előnyét, hogy miért jó az, ha külön cellába rögzíted az áfakulcsot vagy az árfolyamot.

Egy dollár 202 forint, tehát ha forintról szeretnék dollárba átszámolni, akkor az egy osztás lesz. Tehát a Bruttó összeget (ami az F2 cellába van), el kell osztani az árfolyamot tartamazó cellával (ami a J5 cella).

Itt is figyelni kell arra, hogy, mindig amikor behivatkozol egy cellát, pörgesd végig magadban, hogy az egy relatív hivatkozás-e tehát a képlettel együtt mozdul-e lefelé, vagy egy abszolút hivatkozás, ami mindig abban az egy cellában kell hogy maradjon.

Itt rögtön, ránézésre látom, hogy a 202 Ft egy önálló, egyelülálló cella (J5), amit minden képletben szeretnék használni. Tehát még mielőtt megnyomnám az Entert, előtte az F4-gyel kiteszem a dollárjeleket: =F2/$J$5

Utána lehet az Entert vagy Ctrl Entert nyomni és másolni a további cellákba a hivatkozást.

Mindig javaslom, hogy ellenőrízd is le magadat egy másik cellában, lehetőség szerint a legtávolabbiban nyomj egy F2-t. Akkor szépen megjelennek azok a kék-piros keretek, amivel tudod ellenőrzi magadat.

Tökéletesek az Excel képletek, úgyhogy mehetünk tovább.

Írd át a dollár árfolyamot

Mi történik, ha változik az árfolyam? Mondjuk írd át 295-re!

Ez a szuper az abszolút hivatkozásban, hogy csak egy cellát kell átírmon és rögtön automatikusan újra számolódtak az Excel képletek.

Végezetül nézzük meg a Táblázat formátumban is: a bruttó érték osztva a dollár árfolyammal.

A perjel után a jobbra nyíllal elmegyek a J oszlopig, majd a Ctrl felfelé nyilat használom, hogy elérjek a J5 celláig, és gyorsan megnyomom az F4 funkciógombot, hogy rögzítsem. Már kész is vagyok. (Enter / Ctrl Enter)

Eredmény formázása dollárként

Még formázzuk meg a dollár összeget úgy, hogy lászódjon is rajta, hogy az dollár, tehát ne a forint, hanem a dollár legyen mögé kiírva.

Ehhez jelöld ki a számokat (Ctrl Shift lefelé nyil), és felül a menün a Számformátumnál válaszd a dollár formázást.

Formázás pénznemként (dollár, euro)

Ha nem tetszik nektek, hogy ide előre teszi a dollárjelet akkor miután megformáztad, érdemes megnyitni a Ctrl 1-gyel a cellaformátum panelt, mert ilyenkor már a dollároknál nyílik ki az ablak. Ha van olyan formátum, amit jobban szeretsz, ki lehet választani egy másikat is.

Fejlődj az Excelben

Ha Excellel dolgozol, akkor megéri fejlesztened magad és sokat gyakorolnod. Szívesen segítünk a tanulásban: az egyéni Excel oktatást javasoljuk, mert akkor személyreszabott tanácsokat és törődést kapsz.

Kövess a közösségi médiában:
ExcelTitok Facebook
Instagram
Pinterest
YouTube
Twitter

Excel Titok VIp Klub

Gyorsabb, eredményesebb munkavégzés.

Napi szinten használod az Excelt, és szeretnél a problémákon gyorsan átlendülni? Legyél profibb a munkádban és érd el a célod!

Excel Titok olvasás- oktatás, könyv

Könyv áráért Excel könyvtár

Jelenleg a klubban rengeteg azonnal használható gyakorlatias tipp van. Nem kell órákig keresned a megoldást Itt megtalálod, ami kell a munkádhoz.

Excel Titok fejlődés- oktatás, könyv

Folyamatosan fejlődsz míg eléred a célod!

Előléptetés, nagyobb megbecsülés vagy több szabadidő? Ezt fogod elérni, mert ha elakadsz a Klubban kérdezhetsz és tanulhatsz.

Excel Titok közösség - oktatás, könyv

Szakértőktől kérdezhetsz akár minden nap

Szeretnél egy támogató közösség tagja lenni, akik ugyanúgy lelkesednek az Excelért, mint Te?

Hasznos volt? Oszd meg az ismerőseiddel is!

Megosztás itt: facebook
Facebook
Megosztás itt: linkedin
LinkedIn
Megosztás itt: pinterest
Pinterest
Megosztás itt: email
Email
Megosztás itt: tumblr
Tumblr
Megosztás itt: skype
Skype
Megosztás itt: whatsapp
WhatsApp

Mi a véleményed? Van kérdésed?​

Írj üzenetet!

Az email címet nem tesszük közzé. A kötelező mezőket * karakterrel jelöltük

Excel Titok VIp Klub

Gyorsabb, eredményesebb munkavégzés. Magasabb fizetés és több szabadidő!

Napi szinten használod az Excelt, és szeretnél a problémákon gyorsan átlendülni? Legyél profibb a munkádban és érd el a célod!

Excel Titok fejlődés- oktatás, könyv

Folyamatosan fejlődsz míg eléred a célod!

Excel Titok olvasás- oktatás, könyv

Könyv áráért Excel könyvtár

Excel Titok közösség - oktatás, könyv

Szakértőktől kérdezhetsz akár minden nap

Rólunk mondtátok:
Tetszettek a feladatok, mert életszerűek.

Nagyon jól éreztem magam nálad az óráknál. Tetszettek a feladatok, mert életszerűek és abszolút nem voltak unalmasak.

Érthetően és türelmesen magyaráztál, amit még egyszer nagyon köszönök. Te tényleg megszerettetted velem az Excelt! :)

István

Oszd meg az ismerőseiddel is!

Megosztás itt: facebook
Facebook
Megosztás itt: linkedin
LinkedIn
Megosztás itt: skype
Skype
Megosztás itt: whatsapp
WhatsApp
Megosztás itt: email
Email

Legutóbbi bejegyzések

Kövess minket Facebookon!

Ezt olvastad már?

3 módszer a számjegyek csoportosításához

Az ezres csoportosítás használata azért fontos, mert jellemzően nagy számokat tárolunk az Excel fájlokban, és mind munka közben, mind egy prezentáció során fontos, hogy könnyen …

Tovább olvasom →

Alap szintű Excel feladat megoldása lépésről-lépésre

Ez a bejegyzés a tanfolyamunkon használt 01_formázás feladatsor 6. munkalapján található Excel feladat megoldását írja le lépésről-lépésre. Az egyéni Excel tanfolyamokon rengeteg hasonló feladatot oldunk …

Tovább olvasom →

Szerkeszthető naptársablonok 2020-hoz

A naptár készítés decemberben mindig népszerű téma, sokan szeretnek egyedi naptárt ajándékozni Karácsonyra is. Természetesen a nyomdák is kínálnak sablonokat, ahová csak feltöltöd a fotókat, …

Tovább olvasom →