az Excel-képlet, hivatkozások

2021-04-02    Excel 2019
fogalmak
Az Excel-képlet egy összetett utasítás: - Ezeken az adatokon ezeket a műveleteket végezd el! Természetesen nem minden adatot írunk be a képletbe, legtöbbször csak azt közöljük, hol találja őket a program a munkafüzetben!
A cellákban ezek szerint nem csak állandók (konstansok) állhatnak, hanem képletek is. De hol vannak itt képletek? Én mindenhol csak állandókat látok! Igen, mert a szokásos beállítások mellett a program a cellában nem a képletet, hanem annak eredményét jeleníti meg. A fókuszban álló cella képletét a szerkesztőlécen vizsgálhatjuk.
az aktív cella képlete a szerkesztőlécen
Tehát, a képletben legtöbbször nem az adat, hanem annak helye szerepel. Igen ám, de akkor mi lesz, ha módosítjuk az adatot?! Akkor a képletet újra fel kell dolgozni! Ezzel nekünk nem kell törődni, a program ezt automatikusan megteszi: a munkafüzetben végrehajtott minden adatbeviteli műveletet (beírás, módosítás, törlés) követően újra feldolgozza a munkafüzet minden (!) képletét.
Miért használom ezt a kifejezést: „feldolgozza”, miért nem azt mondom: újra számolja? Mert az Excel-képlet nem matematikai képlet! Nem csak matematikai, hanem más adatkezelő műveletet is tartalmazhat! Például, a vezeték és keresztnév összefűzését.
az Excel-képlet nem matematikai képlet
Visszatérve a képletek folyamatos frissítésére: ez a szolgáltatása teszi lehetővé, hogy bonyolult számítási rendszerrel képzett adataink, az állandó változások ellenére is folyamatosan aktuálisak maradjanak. De persze csak akkor, ha figyelünk arra, hogy az értékét gyakran változtató képlet-tényező saját cellát kapjon. Például, ha az Euró Forint értékével számolunk egy képletben, akkor létre kell hoznunk egy cellát, amelybe mindig beírjuk az aktuális Euró árfolyamot.
cella-azonosítók a képletben
A gyümölcsös példát mutató ábrán a láda árát tartalmazó cella azonosítója elég furcsán szerepel a képletben. Nem C9, hanem $C$9. Mi ez? Elmagyarázom. Vegyünk egy másik példát!
relatív hivatkozás az Excel-képletben
Van egy nagy táblázatom, kismillió sorral. Bevételekkel és kiadásokkal. Ki kell számolnom mindenki egyenlegét. Beírom az első ember képletét és a szerkesztőlécen rákattintok az OK (pipa) vezérlőre. A program kiszámolta Hegyi Félix egyenlegét. És most másolnom kell a képletet: rámutatok a cella jobb alsó sarkában a kitöltő négyzetre és duplát kattintok.
a relatív hivatkozást tartalmazó képlet másolása
Tökéletes! A program egy pillanat alatt elkészült a több mint ezerhatszáz egyenleggel! Elég sokan túlköltekeztek! Itt van például Szőke Petra: majdnem százezer forinttal költött többet, mint amennyit keresett. De nézzük csak a képletét: =B3-C3. Ez rendben van, mert Szőke Petra bevételét a B3-as, kiadását a C3-as cella tartalmazza. De ez a „B3-C3” nem másolata a „B2-C2”-nek!
De akkor mit másolt a program? Egy konstruált képletet! A művelet előtt az Excel [1] meghatározza a képletben szereplő cellák elhelyezkedését a képletet tartalmazó cellához képest, [2] majd ezekkel a relatív pozíciókkal készít egy új képletet és [3] ez az új képlet kerül a feltöltendő cellákba. A példánknál maradva, a B2-es cella a D2-es cellához képest balra (b.) a második (2.) cella, a C2 pedig balra (b.) az első (1.). Az új képlet tehát „b.2. - b.1.” lesz. Ebből következik, hogy a másolatokban nem az eredeti képlet cella-azonosítói szerepelnek, hanem az ő relatív pozícióikban álló celláké.
Oké! De azt még mindig nem értem, hogy kerül a dollárjel a cella-azonosítóba! Mindjárt arra is fény derül. Számoljuk ki néhány könnyűipari termék ÁFA-ját és bruttó árát!
példa a relatív és az abszolút hivatkozásra
A kabát ÁFA-ja a kabát nettó ára és az ÁFA kulcs szorzata. A szorzás jele az Excel-képletben a csillag-karakter: B3*F6. Ezt a képletet kell a programnak másolni. Tehát meg kell határoznia a képletben szereplő cellák relatív pozícióit! A B2-es celláé „j.1.”, az F5-ösé „j.3.l.3.”, azaz jobbra három lefelé három. A további ÁFA-cellák képlete, ezek szerint, ez lesz: „j.1.* j.3.l.3.” .
Amikor a másolt képlet feldolgozásra kerül, akkor a program kiolvassa a képletben rögzített relatív pozíciók celláinak értékét… Vegyük példának a pulóver ÁFA-ját (C3). A b.1. cellában hatezer-háromszáz áll, a j.3.l.3. meg üres. Akkor az eredmény hatezer-háromszáz és a semmi, azaz a nulla szorzata lesz.
Ez így nem jó! Valahogy a program tudtára kell adni, hogy a másolandó (belső) képlet összeállításánál az F5-ös cellának ne képezze a relatív pozícióját. Az minden másolatban „F5” maradjon. Erre szolgál a dollárjel ($). Tehát javítsuk ki a kabát képletét: =B2*$F$5.
példa a relatív és az abszolút hivatkozásra
Most már csak azt tessék megmagyarázni, hogy miért kell két dollárjel? Egy nem elég? Hát… Nem. Vegyünk egy másik példát! Munkatársak mozgó bérét kell kiszámolni, amely a havi fizetésük bizonyos százaléka. Ezt az értéket havonta határozzák meg
vegyes hivazkozás oszlop rögzítése
Tehát a B oszlopban állnak a bérek, azután a havi százalékok jönnek. A jobb oldali önálló táblázatban a mozgó bér összegét számoltam ki. Ács Gyöngyvér januári mozgóbére volt a kiindulás. Az ő képletét másoltam a kitöltőnégyzettel jobbra majd lefelé.
Amikor a képletbe azt írtuk „$F$5”, akkor azt az utasítást adtuk a programnak: Az F5-ös cella relatív pozícióját ne vizsgáld! Azt minden másolatba írd be! A program, az esetleges továbbmásolások miatt, a másolatokban is szerepelteti a dollárjeleket.
Most azt mondjuk neki: A B3-as cellának csak a függőleges relatív pozícióját határozd meg! Tehát, hányadik sorban áll feljebb vagy lejjebb a képletet tartalmazó cella sorához képest. Ugyanabban? Akkor minden másolatba írd be a B-t és a másolatot tartalmazó sor számát. Így lesz a H3 cella képlete „$B3*D3”. A továbbiak: I3 » $B3*E3, G4 » $B4*C4, H4 » $B4*D4
Már volt a $F$5 és a $B3, akkor már csak a <oszlop betűje>$<sor száma> variáció marad. Nézzük!
vegyes hivazkozás sor rögzítése
A felső táblázat egy munka-csoport havi termelési adatait tartalmazza. Személyre bontva és összesítve. Az alsó táblázatban meg kellett állapítani, hogy a csoport tagjai hány százalékát adták a csoport termelésének.
Szalontai Pál százaléka januári darabszámának és a csoport darabszámának hányadosa. Az osztás jele az Excel-képletben a perjel: B2/B$6. Ezt a képletet másoltam a kitöltőnégyzettel jobbra, majd lefelé. B$6-os cella-azonosító arra utasítja a programot, hogy a B6-os cellának csak a vízszintes relatív pozícióját határozza meg: hányadik oszlopban áll jobbra vagy balra a képletet tartalmazó cellához képest. Mivel ez a szám nulla, ezért minden másolatban az azonosító oszlopa a másolatot tartalmazó oszlop lesz. Az azonosító sor száma minden másolatban a 6-os. Néhány másolat: C9 » C2/C$6, C10 » D2/D$6, B10 » B3/B$6.
Összefoglalva a fejezet meséjét: a képlet másolásakor nem a képletben szereplő cella-azonosítók másolódnak, hanem pozícióik a képletet tartalmazó cellához képest. A dollárjel a pozíció-vizsgálat teljes vagy részleges elhagyására utasítja a programot: a dollárjeles oszlop és sor-azonosítókat az Excelnek be kell írnia a másolatokba.
hivatkozások
A képletben álló cella-azonosítókat az informatikai zsargon hivatkozásnak nevezi. Az A1 típusút, tehát a dollárjel nélküli azonosítót, relatív hivatkozásnak, a $A$1 típusút pedig abszolút hivatkozásnak. A $A1 és a A$1 a vegyes hivatkozás.
hivatkozás-típusok az Excel-képetben
Van még egy nagyon gyakran használt metafora a dollárjeles azonosítóra: a rögzítés. Rögzítsd a cellát, hogy a másolás során ne mozduljon el! Vagy: rögzítsd a hivatkozás oszlopát, hogy a másolatokban csak a sor változzon!
Jótanács egy. Miután befejeztük a képlet szerkesztését, és még nem küldtük el feldolgozásra, mindig tegyük föl magunknak a kérdést (nem muszáj hangosan): másolni fogom a képletet? Ha nem, akkor mehet, ha igen, akkor végig kell gondolni a rögzítéseket.
Jótanács kettő. Ha a jótanács egyet megfogadtuk és a másolandó képletben vegyes hivatkozást is alkalmaztunk, akkor a másolást követően mindenképp ellenőrizzük a rögzítéseket! Kattintsunk duplán valamelyik másolatot celláján!
a vegyes hivatkozás ellenőrzése a másolt képletben
A kiválasztott képlet-másolatban szereplő cellákat a program grafikus képlet-szerkesztő szolgáltatása színes szegéllyel jeleníti meg.
a képlet szerkesztése
Miután áttekintettük a feladatot, jöhet a képlet beírása. A program ebben is segít: a szerkesztés közben kattintással kiválasztott cella azonosítóját beírja a képletbe. Az abszolút és a vegyes hivatkozás kialakítása sem igényel gépelést: az F4-t nyomkodva a dollárjelek automatikusan beíródnak az azonosítóba. De nézzünk mindezt a gyakorlatban! Zöldségesek vagyunk és gyümölcsöt veszünk a nagybani piacon. A gyümölcsöt ládában árulják.
az Excel-képlet szerkesztése
Ismerjük a gyümölcsök egységárát és egy láda gyümölcs árát. Utóbbi tartalmazza a göngyöleg árát is. Számoljuk ki, hány kiló gyümölcs van a ládákban!
Ha a ládás-árból kivonjuk a göngyöleg árát, akkor megkapjuk a ládában lévő gyümölcs árát. Ezt a különbséget kell elosztani az egységárral. És készen is vagyok. Most már csak azt kell végiggondolni, hogy a képlet másolása miatt, melyik cellát kell majd rögzítenünk! Hát, a láda árát.
Vegyük sorra a képlet szerkesztésének lépéseit. Helyezzük a fókuszt a D2-re! Ha nem a cellában akarunk dolgozni, akkor kattintsunk a szerkesztőléc szerkesztő területén.
[1] Az első lépés az egyenlőségjel beírása. [2] Ezt követi a nyitó és záró zárójel begépelése. Törekedjünk arra, hogy a zárójeleket mindig párban írjuk be! Ha ezt megszokjuk, akkor a bonyolult képletekbe se fogunk belezavarodni. [3] Helyezzük a kurzort zárójelek közé. [4] Kattintsunk rá a C2-es cellára. Az azonosítót a program beírja a képletbe. [5] Gépeljük be a mínusz jelet (elválasztó jelet). Nem kellett visszakattintanunk a képletet tartalmazó cellára vagy a szerkesztőlécre! [6] Kattintsunk rá a C9 cellára. A program beírja a képletbe a cella azonosítóját. [7] Nyomjuk le az F4 billentyűt! A program elhelyezi a dollárjeleket az oszlop és a sor azonosítója előtt. Ha még egyszer megnyomjuk az F4-et, akkor csak a sor lesz rögzítve. Ha még egyszer, akkor csak az oszlop. És ha még egyszer, akkor a program eltünteti a cella-azonosítóból a dollárjeleket. [8] Helyezzük át a kurzort a záró zárójel után! [9] Gépeljük be a perjelet! [10] Kattintsunk a B2-re. A program beírja a cella azonosítóját a képletbe.
a képlet-szerkesztés lépései
A képletet a végrehajtást követően másolnunk kell, ezért a szerkesztést úgy zárjuk le, hogy a képletet tartalmazó cella a fókuszban maradjon! Tehát, vagy a szerkesztőléc OK vezérlőjével (pipa) vagy a Ctrl+Enter billentyű-paranccsal.
margitfalvi.arpad@gmail.com