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.
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.
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, az Euró árfolyama.
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!
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.
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!
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.
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
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!
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.
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ásolat celláján!
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.
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é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
