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
LinkedIn

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 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 e-mail-címet nem tesszük közzé.

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:
Rendkívül hatékony és hasznos a Skype-os Excel-tanfolyam.

Én jogász végzettségű vagyok, aki eddig a Microsoft Word rejtelmeit ismertem csak, de amikor néha az Excel-t kellett használnom, mindig másoknál kellett kuncsorognom segítségért.

Most viszont büszkén kijelenthetem, hogy öt Skype-os tanfolyami alkalom után végre én is magabiztosan tudom használni az Excel-ben mindazt, amire csak szükség lehet: konkrétan egy cég több éves költségvetését és üzleti tervét kellett elkészítenem önállóan, és mindent tudtam hozzá (függvények, képletek, cellaformázás, grafikon, stb.).

A Skype-os oktatás rendkívül hatékony volt számomra, mert sehova nem kellett elmennem (ezzel sok időt és pénzt takarítottam meg), hanem esténként otthonról a számítógépem előtt ülve folytak az órák, és a Skype képernyő-megosztási funkciójának köszönhetően a kommunikáció ugyanolyan hatékony volt, mintha jelen lett volna a tanár.

Szóval csak ajánlani tudom mindenkinek!

András

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?

Használd ki a tartalomtól függő Feltételes formázás előnyeit

A Feltételes formázás egy nagyszerű lehetőség az Excelben, hogy a kimagaslóan jó értékeket vagy ‘hibákat’ feltűnően jelöld, hogy azonnal észrevedd őket. Előre beállíthatod a kritériumokat …

Tovább olvasom →

Mátrix az Excelben

Gyakori “hiba”, amit cégeknél látok, hogy a követni, elemezni szánt adatokat “mátrixos” formátumban gyűjtik – nem pedig adatbázisként. Látszólag egyszerűbb, hiszen azonnal átlátható, értelmezhető az …

Tovább olvasom →

9 ütős billentyű, hogy a nagy táblázatokban is gyorsan mozoghass

Azt látom, hogy sokan nem mernek nagy adatbázisokkal dolgozni, mert félnek, hogy nem látják át, nem találják meg benne az adatokat. Inkább több fülön több kisebb táblázatot használnak. Kis gyakorlással könnyen …

Tovább olvasom →