Hivatkozások az Excelben – abszolút, relatív és vegyes. Hibák a relatív hivatkozásokkal végzett munka során az Excelben

Az automatikus módban a képletekkel végzett számítások megkönnyítése érdekében a cellákra való hivatkozásokat használjuk. Az írás típusától függően három fő típusra oszthatók:

1. Relatív linkek. Egyszerű számításokhoz használják. A képlet másolása a koordináták megváltoztatásával jár.
2. Abszolút linkek. Ha összetettebb számításokat kell készítenie, ez a lehetőség megfelelő. A rögzítéshez használja a „$” szimbólumot. Példa: $A$1.
3. vegyes linkek. Ezt a fajta címzést akkor alkalmazzák a számításoknál, amikor egy oszlopot vagy sort külön kell rögzíteni. Példa: $A1 vagy A$1.

Ha a beírt képlet adatait másolni kell, abszolút és vegyes címzésű hivatkozásokat használunk. A cikk példákkal feltárja, hogyan történik a számítások különféle típusú hivatkozások használatával.

Relatív cellahivatkozás az Excelben

Ez egy cella helyét meghatározó karakterkészlet. A programban lévő hivatkozások automatikusan relatív címzéssel íródnak. Például: A1, A2, B1, B2. Ha másik sorba vagy oszlopba lép, megváltozik a képlet karakterei. Például az A1 kiindulási helyzet. A vízszintes mozgás megváltoztatja a betűt B1-re, C1-re, D1-re stb. Ugyanígy a függőleges vonal mentén történő mozgáskor is változások következnek be, csak ebben az esetben a szám változik - A2, A3, A4 stb. Ha szükséges a sokszorosítás azonos típusú számítás egy szomszédos cellában, a számítás egy relatív hivatkozás segítségével történik. A funkció használatához kövesse néhány lépést:

1. Amint az adat bekerült a cellába, mozgassa a kurzort, és kattintson az egérrel. A zöld téglalappal történő kiemelés a cella aktiválását és a további munkára való készséget jelzi.
2. A Ctrl + C billentyűkombináció megnyomásával a tartalmat a vágólapra másoljuk.
3. Aktiváljuk azt a cellát, amelybe adatokat vagy egy korábban írt képletet szeretne átvinni.
4. A Ctrl + V kombináció megnyomásával átvisszük a rendszer vágólapjára mentett adatokat.

Szakértői tanács! A táblázatban szereplő azonos típusú számítások elvégzéséhez használjon life hacket. Jelölje ki a korábban beírt képletet tartalmazó cellát. Vigye a kurzort egy kis négyzet fölé, amely a jobb alsó sarokban jelenik meg, és tartsa lenyomva a bal egérgombot, és húzza az alsó sorba vagy a szélső oszlopba, a végrehajtott művelettől függően. Az egérgomb felengedésével a számítás automatikusan megtörténik. Ezt az eszközt automatikus kitöltési markernek nevezik.

Példa relatív linkre

A világosabbá tétel érdekében vegyen egy példát egy relatív hivatkozást tartalmazó képlet felhasználásával végzett számításra. Tegyük fel, hogy egy sportüzlet tulajdonosának egy év munka után ki kell számítania az értékesítésből származó nyereséget.

A műveletek sorrendje:

1. A példa azt mutatja, hogy a B és C oszlopokba az eladott áruk mennyiségét és annak árát kellett kitölteni. Ennek megfelelően a képlet megírásához és a válasz megszerzéséhez válassza ki a D oszlopot. A képlet így néz ki: = B2*C

Figyelem! A képletírás folyamatának megkönnyítése érdekében használjon egy kis trükköt. Tegye fel a „=” jelet, kattintson az eladott áruk mennyiségére, állítsa be a „*” jelet és kattintson a termék árára. Az egyenlőségjel utáni képlet automatikusan felírásra kerül.

2. A végső válaszhoz nyomja meg az „Enter” gombot. Ezután ki kell számítania a más típusú termékekből származó nyereség teljes összegét. Nos, ha a sorok száma nem nagy, akkor minden manipuláció kézzel is elvégezhető. Nagyszámú sor egyidejű kitöltéséhez az Excelben van egy hasznos funkció, amely lehetővé teszi a képlet más cellákba való átvitelét.
3. Vigye a kurzort a képletet vagy a kész eredményt tartalmazó téglalap jobb alsó sarka fölé. A fekete kereszt megjelenése jelzi, hogy a kurzort le lehet húzni. Így az egyes termékek után kapott nyereség automatikus kiszámítása külön-külön történik.
4. A lenyomott egérgomb felengedésével minden sorban a megfelelő eredményt kapjuk.

A D3 cellára kattintva láthatja, hogy a cella koordinátái automatikusan megváltoztak, és most így néznek ki: =B3*C3. Ebből következik, hogy a linkek relatívak voltak.

Lehetséges hibák a relatív hivatkozásokkal végzett munka során

Kétségtelen, hogy ez az Excel funkció nagyban leegyszerűsíti a számításokat, de bizonyos esetekben nehézségek adódhatnak. Tekintsünk egy egyszerű példát az egyes árucikkekre vonatkozó haszonegyüttható kiszámítására:

1. Hozzon létre egy táblázatot, és töltse ki: A – terméknév; B – eladott mennyiség; C – költség; D a kapott összeg. Tegyük fel, hogy csak 11 termék van a választékban. Ezért az oszlopok leírását figyelembe véve 12 sor kerül kitöltésre, és a nyereség teljes összege D
2. Kattintson az E2 cellára, és írja be =D2 /D13.
3. Az „Enter” gomb megnyomása után megjelenik az első cikk eladási relatív részesedésének együtthatója.
4. Nyújtsa le az oszlopot, és várja meg az eredményt. A rendszer azonban a „#DIV/0!” hibát adja ki.

A hiba oka a relatív hivatkozás használata a számításokhoz. A képlet másolása következtében a koordináták megváltoznak. Vagyis az E3 esetében a képlet így fog kinézni =D3 /D13. Mivel a D13 cella nincs kitöltve, és elméletileg nulla értéke van, a program hibát ad azzal az információval, hogy a nullával való osztás lehetetlen.

Fontos! A hiba kijavításához a képletet úgy kell felírni, hogy a D13 koordináták rögzítve legyenek. A relatív címzésnek nincs ilyen funkciója. Ehhez létezik egy másik típusú hivatkozás – abszolút. 

Hogyan készítsünk abszolút hivatkozást az Excelben?

A $ szimbólum használatának köszönhetően lehetővé vált a cellakoordináták rögzítése. Hogy ez hogyan működik, azt tovább vizsgáljuk. Mivel a program alapértelmezés szerint relatív címzést használ, ennek megfelelően, hogy abszolút legyen, számos műveletet kell végrehajtania. Elemezzük a „Hogyan találjuk meg az együtthatót több árucikk eladásából” hiba megoldását, abszolút címzéssel végezve a számítást:

1. Kattintson az E2-re, és adja meg a hivatkozás koordinátáit =D2 /D13. Mivel a hivatkozás relatív, az adatok rögzítéséhez szimbólumot kell beállítani.
2. A D cella koordinátáinak rögzítése A művelet végrehajtásához tegyen egy „$” jelet az oszlopot jelző betű és a sorszám elé.

Szakértői tanács! A bevitel megkönnyítése érdekében elegendő aktiválni a képlet szerkesztéséhez szükséges cellát, és többször megnyomni az F4 billentyűt. Amíg meg nem kapja a kielégítő értékeket. A helyes képlet a következő: =D2/$D$13.

3. Nyomja meg az „Enter” gombot. Az elvégzett műveletek eredményeként a megfelelő eredménynek kell megjelennie.
4. A képlet másolásához húzza a jelölőt az alsó sorba.

A számításoknál az abszolút címzés alkalmazása miatt a fennmaradó sorokban a végeredmény helyes lesz.

Vegyes hivatkozás elhelyezése Excelben

A képletszámításokhoz nem csak relatív és abszolút hivatkozásokat használnak, hanem vegyeseket is. Különlegességük, hogy rögzítik az egyik koordinátát.

• Például egy sor pozíciójának megváltoztatásához a $ jelet kell írni a betűjelölés elé.
• Ellenkezőleg, ha a dollárjelet a betűjelölés után írják, akkor a sorban lévő mutatók változatlanok maradnak.

Ebből az következik, hogy az árueladási együttható vegyes címzéssel történő meghatározásával kapcsolatos korábbi probléma megoldásához szükséges a sorszám rögzítése. Azaz a $ jel az oszlopbetű után kerül, mert a koordinátái még relatív hivatkozásban sem változnak. Vegyünk egy példát:

1. A pontos számításokhoz írja be =D1/$D$3 és nyomja meg az „Enter” gombot. A program pontos választ ad.
2. Ha a képletet az oszlop következő celláiba szeretné mozgatni, és pontos eredményeket szeretne elérni, húzza a fogantyút az alsó cellába.
3. Ennek eredményeként a program megadja a helyes számításokat.

Figyelem! Ha beállítja a $ jelet a betű elé, akkor az Excel „#DIV/0!” hibát ad, ami azt jelenti, hogy ez a művelet nem hajtható végre.

„SzuperAbszolút” címzés

A végén nézzünk egy másik példát egy abszolút linkre – a „SuperAbsolute” címzésre. Mik a tulajdonságai és a különbségei. Vegye ki a hozzávetőleges 30-as számot, és írja be a B2 cellába. Ez a szám lesz a fő, műveletek sorozatát kell végrehajtani vele, például hatalomra emelni.

1. Az összes művelet helyes végrehajtásához írja be a következő képletet a C oszlopba: =$B$2^$D2. A D oszlopba írjuk be a fokok értékét.

2. Az „Enter” gomb megnyomása és a képlet aktiválása után a jelölőt lefelé nyújtjuk az oszlopon.
3. Megfelelő eredményeket kapunk.

A lényeg az, hogy az összes végrehajtott művelet egyetlen rögzített B2 cellára vonatkozott, ezért:

• A képlet C3 cellából az E3, F3 vagy H3 cellába másolása nem változtatja meg az eredményt. Változatlan marad – 900.
• Ha új oszlopot kell beszúrni, akkor a képletet tartalmazó cella koordinátái megváltoznak, de az eredmény is változatlan marad.

Ez a „SuperAbsolute” hivatkozás sajátossága: ha mozgatni kell, az eredmény nem változik. Vannak azonban olyan helyzetek, amikor az adatok harmadik féltől származó forrásokból kerülnek beillesztésre. Így az oszlopok oldalra tolódnak el, és az adatok a régi módon kerülnek be a B2 oszlopba. Mi történik ebben az esetben? Keveréskor a képlet az elvégzett műveletnek megfelelően változik, vagyis már nem B2-re, hanem C2-re fog mutatni. De mivel a beillesztés B2-ben történt, a végeredmény rossz lesz.

Referencia! Ahhoz, hogy harmadik féltől származó makrókat tudjon beilleszteni, engedélyeznie kell a fejlesztői beállításokat (alapértelmezés szerint le vannak tiltva). Ehhez lépjen a Beállítások elemre, nyissa meg a szalagbeállításokat, és jelölje be a jobb oldali oszlopban található négyzetet a „Fejlesztő” mellett. Ezt követően megnyílik a hozzáférés számos olyan funkcióhoz, amelyek korábban rejtve voltak az átlagos felhasználó szeme elől.

Ez felveti a kérdést: lehetséges-e úgy módosítani a képletet a C2 cellából, hogy az új adatoszlopok beszúrása ellenére az eredeti számot a B cellából gyűjtsük össze? Annak érdekében, hogy a táblázat változásai ne befolyásolják a végösszeg meghatározását harmadik féltől származó adatok telepítésekor, a következő lépéseket kell végrehajtania:

1. A B2 cella koordinátái helyett írja be a következő mutatókat: =KÖZVETETT(“B2”). Ennek eredményeként a készítmény áthelyezése után a következőképpen fog kinézni: =KÖZVETETT(“B2”)^$E2.
2. Ennek a funkciónak köszönhetően a hivatkozás mindig a B2 koordinátájú négyzetre mutat, függetlenül attól, hogy a táblázatban oszlopok kerülnek hozzáadásra vagy eltávolításra.

Meg kell érteni, hogy egy adatot nem tartalmazó cella mindig „0” értéket mutat.

Következtetés

A leírt hivatkozások három változatának használatának köszönhetően sok olyan lehetőség jelenik meg, amelyek megkönnyítik az Excelben végzett számításokat. Ezért, mielőtt elkezdené a képletekkel való munkát, először olvassa el a hivatkozásokat és a telepítésük szabályait.