Ahhoz, hogy pontos számításokat végezhessen az Excelben, elengedhetetlen megérteni, hogyan működnek a különböző típusú cellahivatkozások.
A1 vs. R1C1 Hivatkozások
Az Excel munkalapok sok cellát tartalmaznak, és (alapértelmezés szerint) minden cellát azonosítanak oszloplevél majd a sorszáma. Ezt A1-stílusú hivatkozásnak nevezik. Példák: A1, B4, C6
A1 referencia stílus
Opcionálisan átválthat R1C1 referencia mód cella sorára hivatkozni & oszlop száma. Ahelyett, hogy az A1 cellára hivatkozna, az R1C1 -re hivatkozik (1. sor, 1. oszlop). A C4 cellát R4C3 -nak nevezzük.
R1C1 referencia stílus
Az R1C1 stílusú hivatkozás rendkívül ritka az Excelben. Ha nincs jó oka, akkor valószínűleg ragaszkodnia kell az alapértelmezett A1-stílusú referenciamódhoz. Ha azonban VBA -t használ, akkor valószínűleg találkozik ezzel a hivatkozási stílussal.
Váltás az R1C1 referencia stílusra
A hivatkozási stílus váltásához lépjen a Fájl> Opció> Képlet. Jelölje be a melletti négyzetet R1C1 referencia stílus.
Elnevezett tartományok
Az Excel egyik legkevésbé kihasznált szolgáltatása a Named Ranges szolgáltatás. Ahelyett, hogy egy cellára (vagy cellacsoportra) cellahelye (például B3 vagy R3C2) alapján hivatkozna, elnevezheti ezt a tartományt, és egyszerűen hivatkozhat a tartomány nevére a képletekben.
Tartomány megnevezése:
- Válassza ki azt a cellát vagy cellákat, amelyeket el szeretne nevezni
- Kattintson a Tartománynév mezőbe
- Írja be a kívánt nevet
- Nyomja meg az Enter billentyűt
Most hivatkozhat az A1 cellára, ha beírja a = range_name1 parancsot. Ez nagyon hasznos, ha nagy munkafüzetekkel dolgozik, több munkalappal.
Sejtek tartománya
Az Excel beépített funkcióinak használatakor előfordulhat, hogy hivatkoznia kell a cellatartományokra. A cellatartományok így jelennek meg: „A1: B3”. Ez a hivatkozás az A1 és B3 közötti összes cellára vonatkozik: az A1, A2, A3, B1, B2, B3 cellákra.
Cellatartomány kiválasztása képlet megadásakor:
- Írja be a tartományt (a kezdő és a végtartományt pontosvesszővel válassza el)
- Az egérrel kattintson az első cellahivatkozásra, tartsa lenyomva az egérgombot, és húzza a kívánt tartományt.
- Tartsa lenyomva a Shift billentyűt, és a nyílbillentyűkkel navigáljon a tartomány kiválasztásához
Abszolút (fagyasztott) és relatív hivatkozások
Amikor cellahivatkozásokat ír be a képletekbe, használhat relatív vagy abszolút (fagyasztott) hivatkozásokat. A relatív hivatkozások arányosan mozognak, amikor a képletet új cellába másolja. Az abszolút hivatkozások változatlanok maradnak. Nézzünk néhány példát:
Relatív referencia
Az Excel relatív hivatkozása így néz ki
= A1
Amikor relatív hivatkozásokat tartalmazó képletet másol és illeszt be, a relatív hivatkozások arányosan mozognak. Hacsak másképp nem adja meg, a cellahivatkozások alapértelmezésben relatívak (nem fagyasztottak).
Példa: Ha az „= A1” -et egy sorral lefelé másolja, a hivatkozás „= A2” -re változik.
Abszolút (fagyasztott) cellareferenciák
Ha nem szeretné, hogy a cellahivatkozások elmozduljanak egy képlet másolásakor, akkor „lefagyaszthatja” a cellahivatkozásokat, ha dollárjeleket ($ s) ad hozzá a befagyasztani kívánt hivatkozás elé. Most, amikor másolja és beilleszti a képletet, a cellahivatkozás változatlan marad. Választhat, hogy lefagyasztja -e a sorhivatkozást, az oszlophivatkozást vagy mindkettőt.
A1: Semmi sem fagyott le
$ A1: Az oszlop lefagyott, de a sor nincs lefagyva
A $ 1: A sor lefagyott, de az oszlop nincs rögzítve
$ A $ 1: Mind a sor, mind az oszlop befagyott
Abszolút referencia parancsikon
A dollárjelek ($ s) kézi hozzáadása a képletekhez nem túl praktikus. Ehelyett a képlet létrehozásakor az F4 billentyűvel válthat az abszolút/relatív cellahivatkozások között.
Példa abszolút cellareferenciára
Valójában mikor kell befagyasztani egy cellahivatkozást? Az egyik gyakori példa az, amikor olyan bemeneti cellákkal rendelkezik, amelyekre gyakran hivatkoznak. Az alábbi példában a forgalmi adót szeretnénk kiszámítani minden menüpont mennyiségére. A forgalmi adó minden tételnél állandó, ezért többször hivatkozunk a forgalmi adó cellára.
A teljes forgalmi adó megtalálásához írja be a „= (B3*C3)*$ C $ 1” képletet a D oszlopba, és másolja le a képletet lefelé.
Vegyes referencia
Lehet, hogy hallott a vegyes cella referenciákról. Vegyes hivatkozás, ha a sor- vagy oszlophivatkozás zárolva van (de nem mindkettő).
Vegyes referencia
Ne feledje, hogy az „F4” billentyű használatával böngészhet a relatív, abszolút cellahivatkozások között.
Cellahivatkozások - Sorok/oszlopok beszúrása és törlése
Lehet, hogy kíváncsi, mi történik a cellahivatkozásokkal, amikor sorokat/oszlopokat szúr be vagy töröl?
A cellahivatkozás automatikusan frissül, hogy az eredeti cellára hivatkozzon. Ez a helyzet függetlenül attól, hogy a cellahivatkozás be van -e fagyasztva.
3D referenciák
Időnként előfordulhat, hogy több, azonos adatmintával rendelkező munkalappal kell dolgoznia. Az Excel lehetővé teszi, hogy egyszerre több lapra hivatkozzon anélkül, hogy manuálisan kellene beírnia az egyes munkalapokat. Hivatkozhat egy laptartományra, mint egy cellatartományra. Példa „1. lap: 5. lap! A1” hivatkozna az A1 cellákra az összes lapon az 1. laptól az 5. lapon keresztül.
Nézzünk egy példát:
Össze szeretné adni az összes termék összes áruházában értékesített egységeit. Minden üzlet saját munkalappal rendelkezik, és minden munkalap azonos formátumú. Ehhez hasonló képletet hozhat létre:
Ez nem túl nehéz, csak négy feladatlap, de mi lenne, ha 40 munkalapja lenne? Valóban manuálisan szeretné hozzáadni az egyes cellahivatkozásokat?
Ehelyett 3D -s hivatkozást használhat egyszerre több lap hivatkozására (hasonlóan a cellatartomány hivatkozásához).
Légy óvatos! A munkalapok sorrendje számít. Ha másik lapot helyez át a hivatkozott lapok (StoreA és StoreD) között, akkor ez a lap is szerepel. Ezzel szemben, ha egy lapot a lapok tartományán kívülre helyez (a StoreA előtt vagy a StoreD után), akkor az már nem lesz benne.
Körkörös sejtreferencia
Körkörös cellahivatkozás az, amikor egy cella visszautal önmagára. Például, ha a B1 cella eredményét használják bemenetként a B1 cellához, akkor körkörös hivatkozás jön létre. A cellának nem kell közvetlenül önmagára hivatkoznia. Létezhetnek köztes lépések.
Példa:
Ebben az esetben a B2 cella képlete „A2+A3+B2”. Mivel a B2 cellában tartózkodik, nem használhatja a B2 -t az egyenletben. Ez körkörös hivatkozást vált ki, és a „B2” cella értéke automatikusan „0” lesz.
A körkörös hivatkozások általában felhasználói hibákból származnak, de bizonyos körülmények között érdemes körkörös hivatkozást használni. A körkörös hivatkozás használatának elsődleges példája az értékek iteratív kiszámítása. Ehhez el kell mennie a címre Fájl> Beállítások> Képletek és Iteratív számítás engedélyezve:
Külső hivatkozások
Az adatok kiszámításakor előfordulhat, hogy hivatkoznia kell a munkafüzeten kívüli adatokra. Ezt külső hivatkozásnak (linknek) nevezik.
Egy képlet létrehozása közben külső hivatkozás kiválasztásához navigáljon a külső munkafüzethez, és válassza ki a hivatkozást a szokásos módon.
A másik munkafüzethez való navigáláshoz használja a CTRL + TAB billentyűkombinációt, vagy lépjen a Nézet> Windows váltása.
Miután kiválasztotta a cellát, látni fogja, hogy egy külső hivatkozás így néz ki:
Vegye figyelembe, hogy a munkafüzet neve zárójelben van [].
Miután bezárta a hivatkozott munkafüzetet, a hivatkozás megmutatja a fájl helyét:
Amikor újra megnyitja a külső hivatkozást tartalmazó munkafüzetet, a rendszer felszólítja a hivatkozások automatikus frissítésének engedélyezésére. Ha így tesz, akkor az Excel megnyitja a referenciaértéket a munkafüzet aktuális értékével. Még ha zárva is van! Légy óvatos! Lehet, hogy ezt akarja, vagy nem.
Elnevezett tartományok és külső hivatkozások
Mi történik a külső cellahivatkozással, amikor sorokat vagy oszlopokat ad hozzá vagy töröl a referencia munkafüzetből? Ha mindkét munkafüzet nyitva van, a cellahivatkozások automatikusan frissülnek. Ha azonban mindkét munkafüzet nincs megnyitva, akkor a cellahivatkozások nem frissülnek, és már nem lesznek érvényesek. Ez óriási aggodalomra ad okot, amikor külső munkafüzetekhez kapcsolódik. Sok hibát okoz ez.
Ha külső munkafüzetekre mutat hivatkozást, akkor a cellahivatkozást elnevezett tartománygal kell megnevezni (további információkért lásd az előző részt). Most a képlet a megnevezett tartományra fog utalni, függetlenül attól, hogy milyen változások történnek a külső munkafüzetben.
