Mielőtt hozzákezdenénk a számítások gyakorlati megvalósításához, ismerkedjünk meg a hivatkozások közül elsőként a relatív hivatkozással. A munkalap celláinak vagy cellacsoportjainak azonosítására alkalmazzuk őket, melyek a cellák illetve a cellacsoportok koordinátái. Ezeket alkalmazzuk a számítások elvégzéséhez, melyek megmutatják, hogy az adott műveletet mely cellák tartalmával kell elvégezni. A képletekben a konstans számokon kívül más cellák tartalmát is felhasználhatjuk e cellákra való hivatkozással. Előnye, hogy az ismételt begépeléssel járó tévedési lehetőséget kiiktathatjuk, valamint megkönnyíthetjük az esetleges módosításokkal járó újraszámolást.

Cella: egy cellára relatívan úgy tudunk hivatkozni, hogy megadjuk az oszlop- majd a sor azonosítóját, ahol a cella van. Pl. E5. A cella az E oszlop 5. sorában található.

Cellacsoportra, tartományra úgy tudunk hivatkozni, hogy megadjuk a tartomány bal felső sarkában található cella hivatkozását, majd kettőspont következik, végezetül hivatkozunk a tartomány jobb alsó sarkában található cellára. Pl. A2:C4. Tehát ez a hivatkozás vonatkozik az A2, A3, A4, B2, B3, B4, C2, C3, C4-es cellákra.

2. ábra
Cellatartomány hivatkozása

Nyissunk egy új munkafüzetet! Jelöljük ki a fenti képen látható tartományt!

 

Nem összefüggő részekre való hivatkozás: ezt akkor használjuk, ha a cellák, cellatartományok nem összefüggőek. Ilyenkor egymás után felsoroljuk a cellákat, cellatartományokat úgy, hogy pontosvesszőt teszünk közéjük. Pl. A1; A3:A6; C2:D6

3. ábra
Nem egybefüggő cellatartományok hivatkozása

Nyissunk egy új munkafüzetet! Jelöljük ki a fenti képen látható tartományt!

 

Hivatkozás egy másik munkalap celláira: begépeljük a munkalap nevét, majd a felkiáltójelet, végezetül a cellára vagy cellákra való hivatkozást. Pl. Munka2!A2:C3.

A képletekben lévő cellákra való hivatkozást nemcsak billentyűzetről történő begépeléssel, hanem egér segítségével is megadhatjuk. A képlet bevitele közben, ha egy cellahivatkozáshoz értünk, kattintsunk a megfelelő cellára! A cellára való kattintás után a képletben megjelenik a cella koordinátája. A koordináta megjelenése után folytathatjuk képletünk begépelését. Az Excel a könnyebb áttekinthetőség érdekében a képletben szereplő cellahivatkozásokat külön színnel jelöli meg, és az érintett cellákat e színekkel ki is emeli a munkalapon.

A Munka1-es munkalapra vigyük be a mintán látható adatokat,
majd számoljuk ki a téglalap kerületét!

4. ábra
Téglalap kerülete

Ha nem a megfelelő cellára kattintottunk, akkor egyszerűen kattintsunk a kívánt cellára! A képletben lévő rossz cellahivatkozás felülíródik az új hivatkozásra.

Gyakran előfordul, hogy egy táblázat soraiban vagy oszlopaiban ugyanazt a számítást kell elvégeznünk. Ebben az esetben egyszerűbb, ha a képletek begépelése helyett azokat másolással sokszorosítjuk.

Az alábbi példa segítségével ismerkedjünk meg az abszolút és vegyes hivatko-zásokkal. Egy fémmegmunkáló kft-ben különböző méretű vaslemezeket kell legyártani. A vaslemezek területét úgy kell meghatározni, hogy a későbbi megmunkálások miatt minden egyes vaslemez területére 2% ráhagyást kell alkalmazni.

A táblázat vízszintes oszlopaiban a vaslemez oldalainak a szélessége található, a függőleges oszlopaiban pedig a magasságok adatai. Az A2-es cellában a ráhagyás szerepel, százalék formátumban.

A Munka2-es munkalapon készítsük el a minta alapján a táblázatot!
A formázásokat is végezzük el

5. ábra
Minta táblázat

Először is határozzuk meg a képletet a 10 cm magas és 10 cm széles vaslemezekre: =10*10+10*10*2%

A számok helyére helyettesítsük be az adatokat tartalmazó cellák hivatkozását:
=B2*A3+B2*A3*A2

Használjuk a matematikai egyszerűsítési lehetőségeket. Emeljük ki a B2*A3 tagot:
=B2*A3*(1+A2)

Vigyük be a képletet a megfelelő, vagyis a B3-as cellába!

Végezetül másoljuk a képletet vízszintesen, majd függőlegesen (a sorrend mindegy) a kitöltőkocka húzásával, ahogy azt a sorozatoknál tanultuk!

6. ábra
Képlet másolása vízszintes és függőleges irányba

Miután elvégezzük a másolásokat, és megnézzük a kapott eredményeket, azt vesszük észre, hogy valami nincs rendben, óriási nagyságú eredményeket kaptunk.

Nézzük meg, hogy vízszintes másolás esetén milyen képletek szerepelnek a cellákban! Pl. milyen képlet van a C3-as cellában?

=C2*B3*(1+B2)

Jó ez a képlet? Nem.

Milyen képletnek kellene a C3-as cellában szerepelnie?

=C2*A3*(1+A2)

 

Hasonlítsuk össze a B3-as és a C3-as cellák képletét!

B3-as cella képlete: =B2*A3*(1+A2)

C3-as cella képlete: =C2*B3*(1+B2)

A másolás után hogyan módosult a B3-as cella képlete?

Ha jobban megnézzük, azt vesszük észre, hogy a képletben szereplő összes cella oszlophivatkozása eggyel megnőtt. Ha a D3-as cellában lévő képlettel hasonlítanánk össze, akkor ahhoz képest kettővel növekedett meg az oszlopazonosító.

Tehát azt a következtetést vonhatjuk le, hogy vízszintes másolás esetén a cellahivatkozások oszlopazonosítója növekszik.

Miután rájöttünk a másolás következményére, újból nézzük meg, hogy milyen képlet szerepel a C3-as cellában, és milyen képletnek kellene ott lennie!

C3-as cella képlete: =C2*B3*(1+B2)

A jó képlet: =C2*A3*(1+A2)

A B3 helyett A3-nak, és a B2 helyett A2-nek kellene ott szerepelnie.

Ez így is van a kiindulási képletnél, amely a C3-ban van: =B2*A3*(1+A2).

Vagyis azt kell elérnünk, hogy ezeknél a cellahivatkozásoknál az oszlopazonosítók ne növekedjenek meg!

Ezt a $ jel segítségével tudjuk megoldani.

Az oszlopazonosító karakter elé tett $ jel hatására másolás után az oszlopazonosító karakterszáma nem fog megnövekedni.

Vonjuk vissza az utolsó két műveletet!

Módosítsuk a kiinduló képletünket (B3-as cella)!

A módosított képlet: =B2*$A3*(1+$A2)

Hiszen A3-ból nem lehet B3, és A2-ből pedig B2.

Ezzel még nem végeztünk, hiszen még meg kell néznünk a függőleges másolás hatását is. A vizsgálódást végezzük el úgy, ahogy azt a vízszintes másolásnál végeztük!

Nézzük meg, hogy a B4-es cellában milyen képlet van!

=B3*A4*(1+A3)

Ez a képlet sem jó. Milyen képletnek kellene ott lennie?

=B2*A4*(1+A2)

Akkor itt is hasonlítsuk össze a B3-as és a B4-es cellák képletét!

B3-as cella képlete: =B2*A3*(1+A2)

B4-es cella képlete: =B3*A4*(1+A3)

Itt a másolás után hogyan módosult a B3-as cella képlete?

Függőleges másolás esetén azt vesszük észre, hogy a képletben szereplő összes cellasor hivatkozása eggyel megnőtt.

Tehát azt a következtetést vonhatjuk le, hogy függőleges másolás esetén a cellahivatkozások sorazonosítója növekszik.

Nézzük meg újból, hogy milyen képlet szerepel a B4-es cellában,
és milyen képletnek kellene ott lennie!

B4-es cella képlete: =B3*A4*(1+A3)

A jó képlet: =B2*A4*(1+A2)

A B3 helyett B2-nek, az A3 helyett A2-nek kellene szerepelnie.

Ez így is van a kiindulási képletnél, amely a C3-ban van: =B2*A3*(1+A2).

Vagyis itt pedig azt kell elérnünk, hogy ezeknél a cellahivatkozásoknál a sorazonosítók ne növekedjenek meg.

Természetesen itt is a $ jelet alkalmazzuk!

A sorazonosító karakter elé tett $ jel hatására a másolás után a sorazonosító karakterszáma nem fog megnövekedni.

Módosítsuk kiinduló képletünket (B3-as cella)!

A módosított képlet: =B$2*A3*(1+A$2)

Hiszen B2-ből nem lehet B3, és A2-ből pedig A3.

Végezetül fésüljük össze a vízszintes és a függőleges másolásnál $ jelekre módosított képleteket:

Vízszintesnél: =B2*$A3*(1+$A2)

Függőlegesnél: =B$2*A3*(1+A$2)

 

A C3-as cellában lévő kiinduló képletet módosítsuk az alábbi összefésült képletre:

=B$2*$A3*(1+$A$2)
Végezzük el újból a vízszintes és a függőleges másolásokat!

Az így kapott táblázat:

7. ábra
Cellaazonosítók rögzítése

Így már tökéletes eredményeket kaptunk.

Mi a különbség az abszolút és a vegyes hivatkozások között?

Relatív hivatkozás: a cellának sem a sor-, sem az oszlopazonosítója nincs rögzítve. Pl. A1

Abszolút hivatkozás: a cella sor- és oszlop azonosítója is rögzített. Pl. az előbbi feladat alapján: $A$2.

Vegyes hivatkozás: az abszolút és a relatív hivatkozás keveréke. Vagyis a cella sor- vagy oszlopazonosítója van rögzítve. Az előbbi példa alapján: B$2, $A3.

A cellahivatkozás miért változik meg a másolás hatására?

Mert az Excel a relatív hivatkozást nem a cella címével tárolja, hanem azt „jegyzi meg", hogy a hivatkozott cella hány sor és hány oszlop távolságra van attól a cellától, amelyikbe a hivatkozást írtuk.

Egy cellahivatkozás rögzítéséhez használhatjuk az F4-es funkcióbillentyűt. Kattintsunk a képletben a kívánt cellára, majd nyomjuk meg az F4 billentyűt! Először a sor- és az oszlopazonosító elé is tesz $ jelet. Következő megnyomásnál, a sor-, újbóli alkalmazásánál csak az oszlopazonosító kerül rögzítésre. Negyedik lenyomásánál pedig visszajutottunk az eredeti állapothoz, a relatív hivatkozáshoz.