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

Ebben a leckében megvitatjuk a sejtekre vonatkozó hivatkozásokat, a képlet másolását vagy áthelyezését, valamint a cellák formázását. Először is, tisztázzuk, mit értünk a sejtek referenciáival, amelyek a képletek és funkciók hatalmának és sokoldalúságának nagy részét támasztják alá. Egy konkrét megértés arról, hogy a cella referenciák hogyan teszik lehetővé a legtöbbet az Excel-táblázatokból!

SCHOOL NAVIGÁCIÓ

1. Miért van szüksége képletekre és funkciókra?
2. Formula meghatározása és létrehozása
3. Relatív és abszolút cella hivatkozás és formázás
4. Hasznos funkciók, amelyeket ismerni kell
5. Keresések, diagramok, statisztikák és Pivot táblák

Jegyzet: Feltételezzük, hogy már tudod, hogy egy cellát a táblázatban lévő négyzetek közül az egyik, oszlopokba és sorokba rendezve, amelyeket a vízszintesen és függőlegesen futó betűk és számok hivatkoznak..

Mi a cella hivatkozás?

A „cella-referencia” azt a cellát jelenti, amelyre egy másik sejt utal. Például, ha az A1 cellában van = A2. Ezután A1 jelentése A2.

Tekintsük át, mit mondtunk a 2. leckében a sorokról és oszlopokról, hogy tovább vizsgálhassuk a cella hivatkozásokat.

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

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

A cellahivatkozásokkal kapcsolatos tanulási célokhoz, sorokba, oszlopokba írjuk őket, ez nem érvényes a táblázatban szereplő jelölés, és egyszerűen azt jelenti, hogy a dolgok egyértelműbbé válnak.

A cella hivatkozások típusai

A cella hivatkozásoknak három típusa van.

Abszolút - Ez azt jelenti, hogy a cella hivatkozás ugyanaz marad, ha a cellát más cellába másolja vagy áthelyezi. Ez a sor és az oszlop rögzítésével történik, így a másolás vagy áthelyezés során nem változik.

Relatív - A relatív hivatkozás azt jelenti, hogy a cella címe megváltozik, amikor azt másolja vagy áthelyezi; vagyis a sejt-referencia a helyéhez viszonyítva van.

Vegyes - Ez azt jelenti, hogy kiválaszthatja a sor vagy az oszlop rögzítését, amikor a cellát másolja vagy áthelyezi, így az egyik megváltozik, a másik pedig nem. Például rögzítheti a sor referenciát, majd mozgathat egy cellát két sorban, és négy oszlopon, és a sor hivatkozása ugyanaz marad. Ezt tovább fogjuk magyarázni.

Relatív hivatkozások

Nézzük a korábbi példát - feltételezzük, hogy az A1 cellában van egy képletünk, amely egyszerűen azt mondja = A2. Ez azt jelenti, hogy az A1-es kimenet az A1 cellában történik, amit az A2 cellába bevittek. Az A2 cellában az „A2” betűt írta, így az Excel az „A2” értéket mutatja az A1 cellában.

Tegyük fel, hogy több adatra van szükségünk a táblázatban. A fenti oszlopokat és a sorokat balra kell hozzáadnunk, így a cellát lefelé és jobbra kell mozgatnunk, hogy helyet csináljunk.

Ahogy a cellát jobbra mozgatja, az oszlop száma növekszik. Amikor lefelé mozgatja, a sorszám nő. Az a cella, amelyre utal, a cella referenciája is változik. Ezt az alábbi ábra szemlélteti:

Folytatva a példánkkal, és az alábbi grafikát nézve, ha az A1-es cellában lévő tartalmat jobbra másolja, a négyet pedig a C5 cellába mozgatta..

A két cellát jobbra és négyre másoltuk. Ez azt jelenti, hogy megváltoztattuk azt a cellát, amelyre kettő van, és négy. A1 = A2 most C5 = C6. Az A2-re való hivatkozás helyett most a C5 sejt a C6 cellára utal.

A megjelenített érték 0, mivel a C6 cella üres. A C6 cellában "I am C6" -t írunk, és most a C5 a "I C6" -ot jeleníti meg.

Példa: Szövegformula

Próbáljunk meg egy másik példát. Emlékezz a 2. leckéről, ahol teljes nevet kellett osztanunk az első és az utolsó névre? Mi történik, ha ezt a képletet másoljuk?

Í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, különben frissíti a hivatkozást.

A cellák tetején található cellák tartalmát szerkesztheti a „fx” szóköz melletti mezőbe. Ez a mező hosszabb, mint egy cellák széles, így könnyebb szerkeszteni.

Most már:

Semmi bonyolult, éppen most írtunk egy új képletet a C3 cellába. Most másolja a C3-at C2 és C4 cellákba. Vegye figyelembe az alábbi eredményeket:

Most Alexander Hamilton és Thomas Jefferson első neve van.

A kurzorral jelölje ki a C2, C3 és C4 cellákat. Irányítsa a kurzort a B2 cellára, és illessze be a tartalmat. Nézd meg, mi történt - hiba van: „#REF.” Miért van ez?

Amikor a cellákat a C oszlopból a B oszlopba másoltuk, a referencia egy oszlopot balra frissítette = jobbra (A2, LEN (A2) - FIND (“,”, A2) - 1).

Minden A2-re való hivatkozás az A bal oldali oszlopra változott, de az A. oszlop bal oldalán nincs oszlop. Tehát a számítógép nem tudja, mit jelent.

Az új B2 képlet például a = JOBB (#REF!, LEN (#REF!) - FIND („,”, # REF!) - 1) és az eredmény #REF:

Egy képlet másolása a cellák tartományába

A cellák másolása nagyon hasznos, mert egy képlet írható, és nagy területre másolhatja, és a hivatkozás frissül. Ezzel elkerülhető, hogy minden cellát szerkesszünk, hogy biztosítsuk a megfelelő helyre való utalást.

A „tartomány” alatt több cellát értünk. Például (C1: C10) a C1 cellától a C10 celláig terjedő összes sejtet jelent. Tehát ez egy oszloposzlop. Egy másik példa (A1: AZ1) az A oszloptól az AZ oszlopig.

Ha egy tartomány öt oszlopot és tíz sort tartalmaz, akkor a tartományt a bal felső és alsó jobb oldali, például A1: E10 írásával jelzi. Ez egy négyzetmező, amely a sorokat és oszlopokat keresztezi, és nem csak egy oszlop vagy egy sor egy részét.

Itt van egy példa, amely bemutatja, hogyan lehet egy cellát több helyre másolni. Tegyük fel, hogy a tervezett kiadásainkat egy hónapban szeretnénk megjeleníteni egy táblázatban, hogy költségvetést lehessen készíteni. Ilyen módon készítünk egy táblázatot:

Most másolja a képletet a C3 cellába (= B3 + C2) az oszlop többi részébe, hogy futási egyenleget adjon a költségvetésünknek. Az Excel másolja a cella hivatkozását. Az eredmény alább látható:

Mint látható, minden egyes új cella frissül relatív az új helyre, így a C4 cellája frissíti a képletet = B4 + C3:

Cell C5 frissítések = B5 + C4, és így tovább:

Abszolút hivatkozások

Abszolút referencia nem változik, ha egy cellát mozgat vagy másol. A $ jelet használjuk abszolút hivatkozásra - hogy emlékezzünk erre, gondoljunk egy dollárjelre, mint egy horgonyra.

Adja meg például a képletet = $ A $ 1 bármely cellában. Az oszlop előtti $ az eszköz nem változtatja meg az oszlopot, az 1. sor előtti $ pedig nem változtatja meg az oszlopot, amikor a cellát más cellába másolja vagy áthelyezi.

Amint az alábbi példában látható, a B1 cellában relatív referencia = A1.Ha a B1-et a négy alatta lévő cellára másoljuk, a relatív referencia = A1 a bal oldali cellára változik, így B2 lesz A2, B3 válnak A3-ra, stb. Ezeknek a celláknak nyilvánvalóan nincs értéke, így a kimenet nulla.

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

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

Most már megváltoztatta a költségvetést, és további 2 000 dollárt mentett meg egy kölcsönös alap megvásárlásához. Tegyük fel, hogy egy fix kamatozású alap, és ugyanazt a kamatot fizeti. Adja meg az új fiókot és az egyenleget a táblázatba, majd másolja a képletet = B4 * B1 a C4 cellából a C5 cellába.

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

Az új befektetési alap évente 0 dollárt keres, ami nem lehet helyes, mivel a kamatláb egyértelműen 5 százalék.

Az Excel kiemeli azokat a cellákat, amelyekre a képlet hivatkozásokat tartalmaz. Láthatjuk, hogy a kamatlábra (B1) való hivatkozás az üres B2 cellába kerül. A B1 abszolútra való utalást úgy kellett volna tennünk, hogy $ B $ 1-t írunk a dollárjel segítségével, hogy rögzítsük a sor- és oszlop-hivatkozást.

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

Ezután másolja ezt a képletet C4-ről C5-re. A táblázat a következőképpen néz ki:

Mivel a képletet egy cellát másoltuk le, azaz egy sorral növeltük a sort, az új képlet = B5 * $ B $ 1. A kölcsönös alapok kamatlába helyesen kerül kiszámításra, mivel a kamatlábat a B1-es cellához rögzítik.

Ez jó példa arra, hogy egy „nevet” használhatunk egy cellára való hivatkozásra. A név abszolút hivatkozás. Ha például a "kamatláb" nevet a B1 cellára szeretné hozzárendelni, kattintson a jobb gombbal a cellára, majd válassza ki a "name name" (név megadása) lehetőséget.

A nevek egy cellára vagy tartományra vonatkozhatnak, és egy képletben használhatunk nevet, például = interest_rate * 8 ugyanaz, mint az írás = $ B $ 1 * 8.

Vegyes hivatkozások

Vegyes hivatkozások, amikor bármelyik a sor vagy az oszlop rögzítve van.

Tegyük fel például, hogy a gazdálkodó költségvetést készít. Ön is rendelkezik egy takarmánytárolóval, és eladja a vetőmagokat. Kukoricát, szójababot és lucernát fogsz ültetni. Az alábbi táblázat a hektáronkénti költségeket mutatja. Az „egy hektáronkénti költség” = „ár per font” * „font a magok egy hektáronként” - ez az, amiért fizetnéd meg magának egy hektárnyi növényt.

Adja meg a hektáronkénti költséget mint = $ B2 * C2 a D2 cellában. Azt mondja, hogy szeretné rögzíteni az ár egy font oszlopra. Ezután másolja ezt a képletet ugyanazon oszlop többi sorába:

Most azt szeretné tudni, hogy mennyire értékeli a magvak leltárát. Szüksége van egy fontra vonatkozó árra és a készletben lévő fontok számára, hogy megismerjük a készlet értékét.

Két oszlopot adunk hozzá: a „vetőmag-készlet a készletben”, majd a „leltár értéke”. Most másolja át a D2-F4-es cellát, és vegye figyelembe, hogy az eredeti képlet első részében ($ B2) a sor hivatkozása sorra frissül 4, de az oszlop rögzítve marad, mert a $ "B" -hez rögzíti.

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

Körkörös hivatkozások

Körkörös hivatkozás, amikor egy képlet önmagára utal.

Például a c3 = c3 + 1 nem írható. Ezt a számítást „iterációnak” nevezik, ami azt jelenti, hogy megismétli magát. Az Excel nem támogatja az iterációt, mert mindent csak egyszer számol.

Ha ezt úgy próbálja meg, hogy beírja a SUM (B1: B5) elemet a B5 cellába:

Egy figyelmeztető képernyő jelenik meg:

Az Excel csak azt jelzi, hogy a képernyő alján körkörös hivatkozás van, így előfordulhat, hogy nem veszi észre. Ha körkörös hivatkozása van, és bezár egy táblázatkezelőt, és újra megnyitja, az Excel egy előugró ablakban jelzi, hogy körkörös hivatkozás van.

Ha van egy körkörös hivatkozás, minden alkalommal, amikor megnyitja a táblázatot, az Excel ezt a felugró ablakot fogja megmondani, hogy körkörös hivatkozása van.

Hivatkozások más munkalapokra

A „munkafüzet” a „munkalapok” gyűjteménye. Egyszerűen fogalmazva, ez azt jelenti, hogy ugyanabban az Excel-fájlban (munkafüzet) több táblázatot (munkalapokat) is kaphat. Amint az alábbi példában látható, példaként szolgáló munkafüzetünkben sok munkalap található (piros).

A munkalapok alapértelmezés szerint Sheet1, Sheet2 és így tovább. Az Excel képernyő alján található „+” gombra kattintva létrehozhat egy újat.

A munkalap neve tetszőleges hasznos lehet, mint a „kölcsön” vagy a „költségvetés”, az egér jobb gombjával kattintson az Excel program képernyőjének alján látható munkalap fülre, az átnevezés kiválasztására, és egy új név beírására.

Vagy egyszerűen kattintson duplán a lapra, és nevezze át.

A munkalap hivatkozásának szintaxisa = munkalap! Cella. Ezt a hivatkozást akkor használhatja, ha ugyanazt az értéket két munkalapban használják, amelyek példái lehetnek:

• Mai dátum
• Valuta átváltási árfolyam dollárról euróra
• Bármi, ami a munkafüzet összes munkalapjára vonatkozik

Az alábbiakban egy példát mutatunk be a munkalapot „kölcsön”, a B1-es cellára hivatkozva.

Ha megnézzük a „kölcsön” munkalapot, akkor a hitelösszegre utalunk:

Következik…

Reméljük, hogy most már szilárdan megérti a sejtek hivatkozásait, beleértve a relatív, abszolút és vegyes. Biztosan sok van.

Ez a mai lecke, a 4. leckében megvitatunk néhány hasznos funkciót, amit érdemes tudni a napi Excel használat során.