Az Excel munkalapja cellákból áll. Ezekre a cellákra hivatkozhat a sor és az oszlop értékének megadásával.
Például az A1 az első sorra (1 -ként megadott) és az első oszlopra (A -ként) utal. Hasonlóképpen a B3 lenne a harmadik sor és a második oszlop.
Az Excel ereje abban rejlik, hogy ezeket a cellahivatkozásokat más cellákban is használhatja képletek létrehozásakor.
Most háromféle cellahivatkozás használható az Excelben:
- Relatív cellareferenciák
- Abszolút cellareferenciák
- Vegyes cella referenciák
Ezeknek a különböző típusú cellahivatkozásoknak a megértése segít a képletekkel való munkában, és időt takaríthat meg (különösen képletek másolása-beillesztése esetén).
Mik a relatív cellahivatkozások az Excelben?
Hadd vegyek egy egyszerű példát, hogy megmagyarázzam a relatív cellahivatkozások fogalmát az Excelben.
Tegyük fel, hogy van egy alábbi adatkészletem:
Az egyes tételek végösszegének kiszámításához meg kell szorozni az egyes tételek árát az adott tétel mennyiségével.
Az első elemnél a D2 cella képlete B2* C2 (az alábbiak szerint):
Most ahelyett, hogy egyenként beírná az összes cella képletét, egyszerűen másolja a D2 cellát, és illessze be az összes többi cellába (D3: D8). Ha ezt megteszi, észre fogja venni, hogy a cellahivatkozás automatikusan igazodik a megfelelő sorra. Például a D3 cella képletéből B3*C3, a D4 képletből pedig B4*C4 lesz.
Ezeket a cellahivatkozásokat hívják elő, amelyek a cella másolásakor beállítják magukat relatív cellahivatkozások az Excelben.
Mikor kell használni a relatív cellahivatkozásokat az Excelben?
A relatív cellahivatkozások akkor hasznosak, ha képletet kell létrehozni egy cellatartományhoz, és a képletnek relatív cellahivatkozásra kell hivatkoznia.
Ilyen esetekben létrehozhatja a képletet egy cellához, és másolja-illessze be az összes cellába.
Mik az abszolút cellahivatkozások az Excelben?
A relatív cellahivatkozásokkal ellentétben az abszolút cellahivatkozások nem változnak, amikor a képletet más cellákba másolja.
Tegyük fel például, hogy rendelkezik az alábbi ábrán látható adatkészlettel, ahol ki kell számítania a jutalékot az egyes tételek teljes értékesítéséért.
A jutalék 20%, és a G1 cellában szerepel.
Ahhoz, hogy megkapja a jutalék összegét az egyes termékek eladásakor, használja a következő képletet az E2 cellában, és másolja az összes cellára:
= D2*$ G $ 1
Vegye figyelembe, hogy a jutalékot tartalmazó cellahivatkozásban két dollárjel ($) található - $G$2.
Mit csinál a dollár ($) jel?
A dollár szimbólum, ha a sor és az oszlop száma elé kerül, abszolútvá teszi (azaz leállítja a sorok és oszlopok számának változását más cellákba másoláskor).
Például a fenti esetben, amikor a képletet az E2 cellából az E3 cellába másolom, az = D2*$ G $ 1 értékről = D3*$ G $ 1 értékre változik.
Ne feledje, hogy míg a D2 D3 -ra változik, a $ G $ 1 nem változik.
Mivel egy dollár szimbólumot adtunk a „G” és az „1” elé a G1 -ben, nem hagyja, hogy a cellahivatkozás megváltozzon a másoláskor.
Ezért ez abszolútvá teszi a cellahivatkozást.
Mikor kell használni az abszolút cellahivatkozásokat az Excelben?
Az abszolút cellahivatkozások akkor hasznosak, ha nem szeretné, hogy a cellahivatkozás megváltozzon a képletek másolása során. Ez akkor fordulhat elő, ha van egy rögzített értéke, amelyet a képletben kell használnia (például adókulcs, jutalék, hónapok száma stb.)
Míg ezt az értéket is keményen kódolhatja a képletben (azaz használjon 20% -ot a $ G $ 2 helyett), a cellában való elhelyezés, majd a cellahivatkozás használata lehetővé teszi, hogy módosítsa azt egy későbbi időpontban.
Például, ha a jutalék szerkezete megváltozik, és most 25% -ot fizet ki 20% helyett, egyszerűen módosíthatja a G2 cella értékét, és minden képlet automatikusan frissül.
Mik azok a vegyes cellareferenciák az Excelben?
A vegyes cellahivatkozások kicsit trükkösebbek, mint az abszolút és relatív cellahivatkozások.
Kétféle vegyes cellahivatkozás lehet:
- A sor zárolva van, miközben az oszlop megváltozik a képlet másolásakor.
- Az oszlop zárolva van, miközben a sor megváltozik a képlet másolásakor.
Nézzük meg, hogyan működik egy példa segítségével.
Az alábbiakban egy adatkészlet található, ahol ki kell számítani a jutalék három szintjét az E2, F2 és G2 cella százalékos értéke alapján.
Most már használhatja a vegyes referencia erejét, hogy kiszámítsa ezeket a jutalékokat egyetlen képlet segítségével.
Írja be az alábbi képletet az E4 cellába, és másolja az összes cellára.
= $ B4*$ C4*E $ 2
A fenti képlet mindkét típusú vegyes cellahivatkozást használja (az egyik, ahol a sor zárolva van, és a másik, ahol az oszlop zárolva van).
Elemezzük az egyes cellahivatkozásokat és megértjük, hogyan működik:
- B4 USD (és C4 USD) - Ebben a hivatkozásban a dollárjel közvetlenül az oszlop jelölése előtt van, de nem a sor szám előtt. Ez azt jelenti, hogy amikor a képletet a jobb oldali cellákba másolja, a hivatkozás ugyanaz marad, mint az oszlop rögzítése. Ha például a képletet E4 -ről F4 -re másolja, akkor ez a hivatkozás nem változik. Ha azonban lemásolja, a sor száma megváltozik, mivel nincs lezárva.
- 2 USD - Ebben a hivatkozásban a dollárjel közvetlenül a sorszám előtt van, és az oszlop jelölésén nincs dollárjel. Ez azt jelenti, hogy amikor a képletet lemásolja a cellákba, a hivatkozás nem változik, mivel a sorszám zárolva van. Ha azonban a képletet jobbra másolja, akkor az oszlop ábécéje megváltozik, mivel nincs lezárva.
Hogyan lehet megváltoztatni a referenciát relatívról abszolútra (vagy vegyesre)?
Ahhoz, hogy a hivatkozást relatívról abszolútra módosítsa, hozzá kell adnia a dollárjelet az oszlop jelölése és a sorszám előtt.
Például az A1 relatív cellahivatkozás, és abszolút értékűvé válik, ha $ A $ 1 értékűvé teszi.
Ha csak néhány hivatkozást kell módosítania, könnyen lehet, hogy ezeket manuálisan módosítja. Tehát elmehet a képlet sávba, és szerkesztheti a képletet (vagy válassza ki a cellát, nyomja meg az F2 billentyűt, majd módosítsa).
Ennek gyorsabb módja azonban az F4 billentyűparancs.
Ha kiválaszt egy cellahivatkozást (a képlet sávban vagy a cellában szerkesztési módban), és megnyomja az F4 billentyűt, az megváltoztatja a hivatkozást.
Tegyük fel, hogy a cella = A1 hivatkozással rendelkezik.
Ez történik, ha kiválasztja a hivatkozást, és megnyomja az F4 billentyűt.
- Nyomja meg egyszer az F4 billentyűt: A cellahivatkozás A1 -ről $ A $ 1 -ra változik („abszolút” lesz „relatívról”).
- Nyomja meg kétszer az F4 billentyűt: A cellahivatkozás A1 -ről A $ 1 -ra változik (vegyes hivatkozásra változik, ahol a sor zárolva van).
- Nyomja meg háromszor az F4 billentyűt: A cellahivatkozás A1 -ről $ A1 -re változik (vegyes hivatkozásra változik, ahol az oszlop zárolva van).
- Nyomja meg négyszer az F4 billentyűt: A cellahivatkozás ismét A1 lesz.
