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 min­den 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ál­toz­ta­tó 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 cel­lá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ép­le­té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ép­le­tet: 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 Pet­ra: 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 el­he­lyez­ke­dé­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 fel­töl­ten­dő 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ű­i­pa­ri 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! Mun­ka­tár­sak 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á­so­lat­ba í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 lej­jebb 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 cel­lá­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. Mi­vel 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ép­le­tet 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 moz­dul­jon 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ö­ve­tő­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 azo­no­sí­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 au­to­ma­ti­ku­san 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 egy­ség­ár­ral. É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] Kat­tint­sunk 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 vissza­kat­tin­ta­nunk 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 azo­no­sí­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 meg­nyom­juk 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 cel­la-azo­no­sí­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­ír­ja 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 ma­rad­jon! Tehát, vagy a szerkesztőléc OK vezérlőjével (pipa) vagy a Ctrl+Enter billentyű-paranccsal.
  ismertető letöltése pdf-ben   munkafüzet letöltése
margitfalvi.arpad@gmail.com