Relatív és abszolút cella hivatkozás és formázás

2024 Szerző: Geoffrey Carr | [email protected]. Utoljára módosítva: 2023-12-17 10:54

Ebben a leckében vitatjuk meg a sejtek referenciáit, a képlet másolását vagy áthelyezését, valamint a sejtek formázását. Kezdjük, tisztázzuk, mit értünk sejtes referenciákon, amelyek a képletek és funkciók sok erejét és sokoldalúságát alátámasztják. Egy konkrét megértés, hogy hogyan működnek a cellák referenciái, lehetővé teszi számodra, hogy a lehető legtöbbet hozza ki az Excel táblázatokból!

ISKOLAI NAVIGÁCIÓ

Miért van szükség a képletekre és funkciókra?
A képlet meghatározása és létrehozása
Relatív és abszolút cella hivatkozás és formázás
Hasznos funkciók Önnek tudnia kell
Keresések, diagramok, statisztikák és pivot táblák

Jegyzet: csak azt feltételezzük, hogy már tudja, hogy egy cella a táblázatok egyike, oszlopokba és sorokba rendezve, amelyeket vízszintesen és függőlegesen futó betűkkel és számokkal jelölnek.

Mi az a Cell Reference?

A "cellahivatkozás" azt a sejtet jelenti, amelyhez egy másik sejt utal. Például, ha az A1 cellában van = A2. Ezután A1 jelentése A2.

Tekintsük át a 2. leckében a sorokról és oszlopokról szóló mondatokat, hogy tovább lehessen tárni a sejtes referenciákat.

A táblázatban lévő cellákat sorokkal és oszlopokkal jelölik. Az oszlopok függőlegesek és betűkkel vannak jelölve. A sorok vízszintesek és számokkal jelöltek.

A táblázat első cellája A1, ami azt jelenti, hogy az A oszlop, az 1. sor, a B3 a második oszlopban, a harmadik sorban található cella és így tovább.

A cellahivatkozásokról szóló tanulási célokra időnként sorként, oszlopként írjuk őket, ez nem érvényes jelölés a táblázatban, és egyszerűen csak a dolgok egyértelműbbé tételére szolgál.

Cella-referenciák típusai

Három típusú sejtes referencia létezik.

Abszolút - Ez azt jelenti, hogy a cella hivatkozása ugyanaz marad, ha másolja vagy áthelyezi a cellát bármely más cellába. Ezt a sor és az oszlop horgonyzásával végezzük el, ezért nem változik a másolás vagy áthelyezés során.

Relatív - Relatív hivatkozás azt jelenti, hogy a cellacím megváltozik a másolás vagy áthelyezés közben; vagyis a cellahivatkozás a helyéhez viszonyítva.

Vegyes - Ez azt jelenti, hogy a cellát átmásolhatja vagy áthelyezheti a sor vagy az oszlop horgonyozására, így az egyik változik, a másik pedig nem. Például horgonyozhatja a sor referenciáját, majd mozgathatja a cellát két sorban és négy oszlopon, és a sor referenciája ugyanaz marad. Ezt az alábbiakban részletezzük.

Relatív hivatkozások

Lássuk a korábbi példát - feltételezzük, hogy az A1 cellában van egy képlet, amely egyszerűen azt mondja, hogy az A2 =. Ez azt jelenti, hogy Excel-kimenetet az A1 cellában, bármit is bejuttatunk az A2 cellába. Az A2 cellában "A2" -t írunk be, így az Excel megjeleníti az "A2" értéket az A1 cellában.

Tegyük fel, hogy több adatra van szükségünk a táblázatban. Fel kell tüntetnünk a fenti oszlopokat és a sorokat a bal oldalon, ezért le kell mozgatnunk a cellát, és jobbra kell helyet tenni.

Ahogy jobbra mozdítja a cellát, az oszlop száma növekszik. Ahogy lefelé mozdul, a sor száma növekszik. A sejt, amelyre rámutat, a sejtek referenciája is változik. Ezt az alábbi ábra szemlélteti:

Folytatva a példánkat, és az alábbi grafikát nézzük, ha az A1 cellatartalmat másoljuk jobbra és négyre, áthelyeztük a C5 cellára.

A cellába két oszlopot másoltunk jobbra és négyre. Ez azt jelenti, hogy megváltoztattuk azt a sejtet, amelyre két egymást és négyet lefelé hivatkozunk. A1 = A2 most C5 = C6. Ahelyett, hogy az A2-re hivatkoznánk, most a C5 sejt a C6 sejtre utal.

A bemutatott érték 0, mert a C6 cellák üresek. A C6 cellában az "Én C6" karaktert írtam, és most a C5 megjeleníti az "Én vagyok C6".

Példa: szöveges képlet

Próbáljunk még egy példát. Ne feledkezzen meg a 2. leckeről, ahol meg kellett osztanunk egy teljes nevet az utónév és a vezetéknév között? Mi történik, ha másoljuk ezt a képletet?

Írja be a képletet = RIGHT (A3, LEN (A3) - FIND (",", A3) - 1) vagy másolja a szöveget a C3 cellába. Ne másolja a tényleges cellát, csak a szöveget, másolja a szöveget, ellenkező esetben frissíti a hivatkozást.

A cella tartalmát egy táblázat tetején szerkesztheti a "fx" mondat melletti négyzetben. Ez a doboz hosszabb, mint egy cellának széles, így könnyebben szerkeszthető.

Most már van:

Semmi bonyolult, most egy új formulát írtunk a C3 cellába. Most másolja a C3-at a C2 és a C4 cellákra. Tartsa be az alábbi eredményeket:

Most Alexander Hamilton és Thomas Jefferson első neve.

Használja a kurzort a C2, C3 és C4 cellák kiemeléséhez. Irányítsa a kurzort a B2 cellába, és illessze be a tartalmat. Nézd meg, mi történt - hibát kapunk: "#REF." Miért van ez?

Amikor a C oszlopból a B oszlopba másoltuk a cellákat, az első oszlopot a balra = RIGHT (A2, LEN (A2) - FIND (",", A2) - 1) frissítettük.

Az A2-re vonatkozó összes hivatkozást az A-tól balra lévő oszlopra változtatta, de az oszlop bal oldalán nincs oszlop. Tehát a számítógép nem tudja, mit jelent.

Például B2-ben az új formula = RIGHT (#REF!, LEN (#REF!) - FIND ("," # REF!) - 1), és az eredmény #REF:

Egy képlet másolása cellákra

A cellák másolása nagyon hasznos, mert egy képletet írhat és átmásolhat egy nagy területre, és a hivatkozás frissül.Ezzel elkerülhető, hogy minden egyes cellát módosítsunk annak érdekében, hogy a helyes helyre mutasson.

A "tartományon" egynél több sejtet értünk. Például (C1: C10) a C1 sejttől a C10 sejtig terjedő összes sejtet jelenti. Tehát ez egy sejtoszlop. Egy másik példa (A1: AZ1) az A oszlop AZ oszlopának felső sora.

Ha egy tartomány öt oszlop és tíz sor között mozog, akkor a felső bal oldali cella és a jobb alsó sarka, például az A1: E10 jelzésével jelezheti a tartományt. Ez egy olyan négyzet, amely kereszt sorokat és oszlopokat tartalmaz, és nem csak egy oszlop vagy egy sor egy része.

Íme egy példa, amely bemutatja, hogyan kell egy cellát több helyre másolni. Tegyük fel, hogy meg szeretnénk mutatni a hónapra vonatkozó tervezett költségeinket egy táblázatban, így költségkeretet tudunk készíteni. Ehhez egy táblázatot készítünk:

Most másolja a képletet a C3 cellában (= B3 + C2) az oszlop többi részéhez, hogy a költségvetésünk egyenleg legyen. Az Excel frissíti a cellahivatkozást, amikor másolja. Az eredmény az alábbi:

Amint láthatja, minden új cella frissül relatív az új helyre, így a C4 a képletét frissíti = B4 + C3:

A C5 cellák frissítése = B5 + C4, és így tovább:

Abszolút referenciák

Az abszolút hivatkozás nem változik, ha cellát mozgat vagy másol. Használjuk a $ jelet abszolút hivatkozáshoz - emlékezzünk erre, gondoljunk egy dollárjelre horgonyként.

Például írja be a képletet = $ A $ 1 minden cellában. Az A oszlop előtt lévő $ azt jelenti, hogy nem módosítja az oszlopot, az 1. sor elején lévő $ nem jelenti az oszlop módosítását, amikor átmásolja vagy áthelyezi a cellát egy másik cellába.

Ahogy az alábbi példában láthatjuk, a B1 cellában van relatív referencia = A1. Amikor B1-et másoljuk az alatta lévő négy cellához, akkor a relatív referencia = A1 a bal oldali cellába változik, így B2 B2, B3 A3, stb. Ezek a sejtek nyilvánvalóan nincsenek beadva, így a kimenet nulla.

Ha azonban $ A1 $ 1-et használunk, például a C1-ben, és az alatta lévő négy cellába másoljuk, a referencia abszolút, így soha nem változik, és a kimenet mindig egyenlő az A1 cellában lévő értékkel.

Tegyük fel, hogy nyomon követi érdeklődését, például az alábbi példában. A képletben C4 = B4 * B1 a "kamatláb" * "egyenleg" = "kamat évente".

Most megváltoztatta a költségkeretét, és további 2 000 dollárt mentett meg egy befektetési alap megvásárlásához. Tegyük fel, hogy ez egy fix kamatozású alap, és ugyanazt a kamatot fizetik. Írja be az új fiókot és az egyenleget a táblázatba, majd másolja a képletet = B4 * B1 a C4 celláról a C5 cellára.

Az új költségvetés így néz ki:

Az új befektetési alap évente 0 dollár kamatot keres, ami nem igaz, hiszen a kamatláb egyértelműen 5 százalék.

Az Excel kiemeli azokat a sejteket, amelyekre a képlet hivatkozik. Láthatja, hogy a kamatlábra (B1) való utalás átkerül az üres B2 cellára. B1 abszolút hivatkozást kellett volna megadnunk a $ B $ 1 dollárral a dollárjel használatával, hogy horgonjuk a sor és az oszlop hivatkozását.

Írja át az első számítást C4-ben, hogy olvassa el = B4 * $ B $ 1 az alábbiak szerint:

Ezután másolja a képletet a C4-től a C5-ig. A táblázat most így néz ki:

Mivel a képletet egy sejtre másoltuk le, azaz növeltük a sorokat egyenként, az új képlet = B5 * $ B $ 1. A kölcsönös alap kamatlábát most megfelelően számítják ki, mert a kamatláb a B1-eshez van kötve.

Ez jó példa arra, hogy mikor használhat egy "nevet", hogy hivatkozzon egy cellára. A név abszolút hivatkozás. Például a "kamatláb" nevének a B1 cellához való hozzárendeléséhez kattintson jobb gombbal a cellára, majd válassza ki a "név megadását".

A nevek egy cellára vagy egy tartományra vonatkozhatnak, és egy képletben szereplő nevet használhatunk, például = interest rate * 8 ugyanaz, mint a writing = $ B $ 1 * 8.

Vegyes referenciák

A vegyes referenciák mikor jelennek meg bármelyik a sor vagy oszlop horgonyzott.

Tegyük fel például, hogy mezőgazdasági termelő vagy a költségvetés. Ön is rendelkezik takarmány-áruházzal és eladja a magokat. Meg fogsz ültetni a kukoricát, a szójababot és a lucerna. Az alábbi táblázat mutatja az egy hektáronként felmerülő költségeket. A "hektáronkénti költség" = "fontonkénti díj" * "hektárnyi hektár / hektár" - ez az, ami meg fog költeni egy hektáron.

Adja meg a hektáronkénti költségeket mint = $ B2 * C2 a D2 cellában. Azt állítja, hogy a fontonkénti oszlop árát horgonyozni akarja. Ezt a képletet ugyanabban az oszlopban lévő többi sorhoz másolja:

Most szeretné tudni a magvak jegyzékének értékét. Szüksége van a fontonkénti árra és a készlet mennyiségének fontosságára a készlet értékének ismeretében.

Két oszlopot adunk hozzá: "font vetőmag a készletben", majd "készlet értéke". Most másolja a D2 cellát az F4-re, és vegye figyelembe, hogy az eredeti képlet (B2. 4, de az oszlop rögzítve marad, mivel a $ horgonyozza a "B."

Ez egy vegyes hivatkozás, mert az oszlop abszolút és a sor relatív.

Körlevelek

A kör alakú hivatkozás, ha a képlet magára utal.

Például nem írhat c3 = c3 + 1. Ezt a számítást "iterációnak" nevezzük, ami azt jelenti, hogy megismétli magát. Az Excel nem támogatja az iterációt, mert mindössze egyszer számolja ki.

Ha ezt próbálja meg a SUM (B1: B5) beírásával a B5 cellában:

Az Excel csak azt mondja meg, hogy a képernyő alján egy kör alakú hivatkozás van, így nem veszi észre. Ha van körkörös referenciája, és bezárja a táblázatot, és újra megnyitja, az Excel egy felbukkanó ablakban elmondja, hogy van körkörös hivatkozása.

Ha van körkörös referenciája, minden alkalommal, amikor megnyitja a táblázatot, az Excel megmutatja, hogy a pop-up ablakban van körkörös hivatkozása.

Referenciák más munkalapokra

A "munkafüzet" a "munkalapok" gyűjteménye. Egyszerűen fogalmazva, ez azt jelenti, hogy többféle táblázatot (munkalapot) tartalmazhat ugyanazon Excel fájlban (munkafüzet). Amint az alábbi példában láthattuk, a példaprogram számos munkalapot tartalmaz (piros színnel).

A munkalapok alapértelmezés szerint Sheet1, Sheet2 és így tovább szerepelnek. Új képet hoz létre az Excel képernyő alján található "+" gombra kattintva.

Megváltoztathatja a munkalap nevét valami hasznosnak, mint a "kölcsön" vagy a "költségkeret", ha az egér jobb gombjával kattint az Excel program képernyőjének alsó részén látható munkalapra, átnevezi az új nevet és beírja az új nevet.

Vagy egyszerűen kattintson duplán a fülre, és nevezze át.

A munkalap referenciájának szintaxisa = munkalap! Cell. Ezt a fajta hivatkozást akkor használhatja, ha ugyanazt az értéket két munkalapon használja, például a következő esetekben: