A cellákra és tartományokra való hivatkozás lehetővé teszi a táblázatkezelő eszközök működését. És az Excel a legjobb és legerősebb.
Ebben az oktatóanyagban lefedem mindazt, amit tudnod kell arról, hogyan kell hivatkozni a cellákra és tartományokra az Excelben. Az ugyanazon a lapon található alapvető hivatkozásokon kívül az oktatóanyag nagy része kb hogyan hivatkozhat egy másik lapra vagy munkafüzetre az Excelben.
Bár nem sok különbség van a működésében, ha ugyanabban a fájlban egy másik lapra hivatkozik, vagy egy teljesen különálló Excel -fájlra hivatkozik, a hivatkozás formátuma kissé megváltozik.
Ezenkívül néhány fontos dolgot szem előtt kell tartania, amikor egy másik lapra vagy más külső fájlra hivatkozik.
De aggódj… semmi túl őrült!
Mire befejezi ezt az oktatóanyagot, tudni fogja, hogy mit kell tudni a cellák és tartományok hivatkozásáról az Excelben (ugyanabban a munkafüzetben vagy egy másik munkafüzetben).
Kezdjük el!
Cella hivatkozása ugyanazon a lapon
Ez a hivatkozás legalapvetőbb szintje, amikor ugyanazon a lapon lévő cellára hivatkozik.
Például, ha a B1 cellában vagyok, és az A1 cellára akarok hivatkozni, a formátum a következő lenne:
= A1
Amikor ezt megteszi, a hivatkozást használó cella értéke megegyezik az A1 cella értékével. És ha bármilyen változtatást hajt végre az A1 cellában, akkor azok megjelennek abban a cellában, ahol ezt a hivatkozást használta.
Cella hivatkozása a másik lapon
Ha hivatkoznia kell egy másik lapra ugyanabban a munkafüzetben, akkor az alábbi formátumot kell használnia:
Sheet_name! Cell_address
Először is, a munkalap neve után felkiáltójel következik, amelyet a cellahivatkozás követ.
Tehát ha hivatkoznia kell az A1 -es cellára az 1. lapon, akkor a következő hivatkozást kell használnia:
= 1. lap! A1
Ha pedig egy másik lap cellatartományára szeretne hivatkozni, akkor a következő formátumot kell használnia:
Első_cella: Utolsó_cella
Tehát, ha hivatkozni szeretne az A1: C10 tartományra ugyanazon munkafüzet másik lapján, akkor az alábbi hivatkozást kell használnia:
= 1. lap! A1: C10
Ne feledje, hogy csak a hivatkozást mutattam meg a cellára vagy a tartományra. A valóságban ezeket képletekben használná. De a fent említett hivatkozások formátuma ugyanaz marad
Sok esetben a hivatkozott munkalapon több szó szerepel a névben. Például lehet Projektadatok vagy Értékesítési adatok.
Ha szóközök vagy nem betűrendes karakterek vannak (például @,!, #, -stb.), Akkor a nevet az idézőjelek között kell használni.
Például, ha az Értékesítési adatok nevű lapon az A1 cellát kívánja használni, akkor az alábbi hivatkozást használja:
= 'Értékesítési adatok'! A1
Abban az esetben, ha a lap neve Sales-Data, akkor a lap A1-es cellájára való hivatkozáshoz az alábbi hivatkozást kell használnia:
= 'Értékesítési adatok'! A1
Ha ugyanazon munkafüzet egyik lapjára hivatkozik, majd később megváltoztatja a munkalap nevét, nem kell aggódnia a hivatkozás tönkremenetelétől. Az Excel automatikusan frissíti ezeket a hivatkozásokat.
Bár nagyszerű tudni ezeknek a hivatkozásoknak a formátumát, a gyakorlatban nem olyan jó ötlet ezeket manuálisan beírni minden alkalommal. Időigényes és nagyon hibás lenne.
Hadd mutassak meg egy jobb módszert a cellahivatkozások létrehozására az Excelben.
Hivatkozás automatikus létrehozása ugyanazon munkafüzet másik lapjára
Sokkal jobb módja annak, hogy cellahivatkozásokat hozzon létre egy másik lapra, ha egyszerűen ráirányítja az Excel -t arra a cellára/tartományra, amelyre a hivatkozást létre szeretné hozni, és hagyja, hogy az Excel maga hozza létre.
Ez biztosítja, hogy nem kell attól tartania, hogy a felkiáltójel vagy az idézetek hiányoznak, vagy bármilyen más formátumbeli probléma merül fel. Az Excel automatikusan létrehozza az Ön számára a megfelelő referenciát.
Az alábbiakban bemutatjuk, hogyan hozhat létre automatikusan hivatkozást egy másik lapra:
- Válassza ki azt a cellát az aktuális munkafüzetben, ahol a hivatkozásra van szüksége
- Addig írja be a képletet, amíg szüksége lesz a referenciára (vagy egyenlőségjelre, ha csak a hivatkozást szeretné)
- Válassza ki azt a lapot, amelyre hivatkoznia kell
- Válassza ki azt a cellát/tartományt, amelyre hivatkozni szeretne
- Nyomja meg az Enter billentyűt a képlet eredményének megtekintéséhez (vagy folytassa a képlet kidolgozását)
A fenti lépések automatikusan hivatkozást hoznak létre egy másik lapon lévő cellára/tartományra. Ezeket a hivatkozásokat a képlet sávban is láthatja. Ha elkészült, egyszerűen nyomja meg az Enter billentyűt, és megadja az eredményt.
Például, ha bizonyos adatok vannak az A1: A10 cellában az Értékesítési adatok nevű lapon, és szeretné megkapni ezen értékek összegét az aktuális lapon, akkor a következő lépések lesznek:
- Írja be a következő képletet az aktuális lapon (ahol szüksége van az eredményre): = Összeg (
- Válassza ki az „Értékesítési adatok” lapot.
- Válassza ki a hozzáadni kívánt tartományt (A1: A10). Amint ezt megteszi, az Excel automatikusan létrehoz egy hivatkozást erre a tartományra (ezt a képlet sávban láthatja)
- Nyomja meg az Enter billentyűt.
Hosszú képlet létrehozásakor előfordulhat, hogy hivatkoznia kell egy cellára vagy egy tartományra egy másik lapon, majd vissza kell térnie az eredeti lapra, és hivatkoznia kell valamelyik cellára/tartományra.
Amikor ezt megteszi, észre fogja venni, hogy az Excel automatikusan beszúr egy lap hivatkozást arra a lapra, ahol a képlet található. Bár ez rendben van és nem árt, nincs rá szükség. Ilyen esetben dönthet úgy, hogy megtartja a hivatkozást, vagy manuálisan eltávolítja.
Egy másik dolog, amit tudnia kell, amikor hivatkozásokat hoz létre a lap, majd a cella/tartomány kiválasztásával, hogy az Excel mindig létrehoz egy relatív referencia (azaz n0 $ jelű hivatkozások). Ez azt jelenti, hogy ha másolom és beillesztem a képletet (az egyik hivatkozással egy másik lapra) egy másik cellába, akkor automatikusan módosítja a hivatkozást.
Íme egy példa, amely megmagyarázza a relatív hivatkozásokat.
Tegyük fel, hogy a következő képletet használom az aktuális lap A1 cellájában (lásd a SalesData munkalap A1 celláját)
= Értékesítési adatok! A1
Ha most bemásolom ezt a képletet és beillesztem az A2 cellába, a képlet a következőre változik:
= Értékesítési adatok! A1
Ez azért történik, mert a képlet relatív, és amikor másolom és beillesztem, a hivatkozások automatikusan módosulnak.
Ha azt szeretném, hogy ez a hivatkozás mindig a SalesData lap A1 cellájára utaljon, az alábbi képletet kell használnom:
= Értékesítési adatok! $ A $ 1
A sor és az oszlop előtti dollárjel zárolja ezeket a hivatkozásokat, hogy ne változzanak.
Itt található egy részletes oktatóanyag, ahol többet megtudhat az abszolút, vegyes és relatív referenciákról.
Most, hogy kitértünk arra, hogyan kell hivatkozni egy másik lapra ugyanabban a munkafüzetben, nézzük meg, hogyan hivatkozhatunk egy másik munkafüzetre.
Hogyan lehet hivatkozni egy másik munkafüzetre az Excelben
Ha egy cellára vagy tartományra hivatkozik egy másik Excel -munkafüzetben, akkor a hivatkozás formátuma attól függ, hogy a munkafüzet nyitva vagy zárva van -e.
És természetesen a munkafüzet és a munkalapok neve is szerepet játszik a formátum meghatározásában (attól függően, hogy a névben szóközök vagy nem betűrendes karakterek vannak-e vagy sem).
Lássuk tehát egy másik munkafüzetre mutató külső hivatkozások különböző formátumait különböző forgatókönyvekben.
Külső hivatkozás nyílt munkafüzetre
Amikor külső nyitott munkafüzetre hivatkozik, meg kell adnia a munkafüzet nevét, a munkalap nevét és a cella/tartomány címét.
Az alábbiakban bemutatjuk a formátumot, amelyet külső nyitott munkafüzetre hivatkozva kell használni
= '[FileName] SheetName! CellAddress
Tegyük fel, hogy van egy „ExampleFile.xlsx” munkafüzete, és hivatkozni szeretne a munkafüzet 1. lapjának A1 cellájára.
Az alábbiakban erre hivatkozunk:
= [PéldaFájl.xlsx] Értékesítési adatok! A1
Abban az esetben, ha szóközök vannak a külső munkafüzet nevében vagy a munkalap nevében (vagy mindkettőben), akkor hozzá kell adnia a fájl nevét (szögletes zárójelben) és a lap nevét egyetlen idézőjelbe.
Az alábbiakban bemutatjuk azokat a példákat, ahol a neveket egyetlen idézőjelben kell megadni:
= '[Példa fájl.xlsx] Értékesítési adatok'! A1 = '[PéldaFájl.xlsx] Értékesítési adatok'! A1 = '[Példa fájl.xlsx] Értékesítési adatok'! A1
Hivatkozás létrehozása egy másik munkafüzethez (automatikusan)
Ismétlem, bár jó tudni a formátumot, a legjobb, ha nem kézzel írja be.
Ehelyett csak mutassa az Excel -t a megfelelő irányba, és létrehozza ezeket a hivatkozásokat az Ön számára. Ez sokkal gyorsabb, kevesebb hibalehetőséggel.
Például, ha van néhány adat az A1: A10 cellában egy „Példa fájl” nevű munkafüzetben az „Értékesítési adatok” nevű lapon, és szeretné megkapni ezen értékek összegét az aktuális lapon, akkor a következő lépések lesznek :
- Írja be a következő képletet az aktuális lapon (ahol szüksége van az eredményre): = Összeg (
- Lépjen a „Példa fájl” munkafüzetbe
- Válassza ki az „Értékesítési adatok” lapot.
- Válassza ki a hozzáadni kívánt tartományt (A1: A10). Amint ezt megteszi, az Excel automatikusan létrehoz egy hivatkozást erre a tartományra (ezt a képlet sávban láthatja)
- Nyomja meg az Enter billentyűt.
Ez azonnal létrehozza a képletet a megfelelő hivatkozásokkal.
Egy dolog, amit észrevehet egy külső munkafüzetre való hivatkozás létrehozásakor, hogy mindig abszolút hivatkozásokat hoz létre. Ez azt jelenti, hogy a sor- és oszlopszám előtt $ jel van. Ez azt jelenti, hogy ha másolja és beilleszti ezt a képletet más cellákba, az abszolút hivatkozás miatt továbbra is ugyanarra a tartományra hivatkozik.
Ha ezt szeretné megváltoztatni, akkor manuálisan kell megváltoztatnia a hivatkozásokat.
Külső hivatkozás egy zárt munkafüzetre
Ha egy külső munkafüzet nyitva van, és erre a munkafüzetre hivatkozik, akkor csak meg kell adnia a fájl nevét, a munkalap nevét és a cella/tartomány címét.
De amikor ez le van zárva, az Excelnek fogalma sincs, hogy hol keresi a hivatkozott cellákat/tartományokat.
Ezért, amikor zárt munkafüzetre hivatkozást hoz létre, meg kell adnia a fájl elérési útját is.
Az alábbiakban egy hivatkozás található, amely a Példa fájl munkafüzet Sheet1 munkalapjának A1 cellájára utal. Mivel ez a fájl nincs megnyitva, a fájl mentési helyére is utal.
= 'C: \ Users \ sumit \ Desktop \ [Példa fájl.xlsx] Lap1'! $ A $ 1
A fenti hivatkozás a következő részeket tartalmazza:
- Fájl elérési útja - az a hely a rendszeren vagy a hálózaton, ahol a külső fájl található
- Fájlnév - a külső munkafüzet neve. Ez magában foglalja a fájlkiterjesztést is.
- Lap neve - a lap neve, amelyben a cellákra/tartományokra hivatkozik
- Cella/tartomány címe - a pontos cella-/tartománycím, amelyre hivatkozik
Amikor külső hivatkozást hoz létre egy nyitott munkafüzetre, majd bezárja a munkafüzetet, észre fogja venni, hogy a hivatkozás automatikusan megváltozik. A külső munkafüzet bezárása után az Excel automatikusan beilleszt egy hivatkozást a fájl elérési útjába is.
A fájlhely megváltoztatásának hatása a referenciákra
Amikor hivatkozást hoz létre egy cellára/tartományra egy külső Excel fájlban, majd bezárja, a hivatkozás most a fájl elérési útját is használja.
De ha megváltoztatja a fájl helyét, semmi sem fog változni a munkafüzetben (amelyben létrehozta a hivatkozást). De mivel megváltoztatta a helyszínt, a link most megszakadt.
Tehát ha bezárja és megnyitja ezt a munkafüzetet, akkor azt fogja mondani, hogy a hivatkozás megszakadt, és vagy frissítenie kell a hivatkozást, vagy teljesen meg kell szakítania. Az alábbi képernyőn megjelenik egy prompt:
Amikor rákattint a Frissítés gombra, megjelenik egy másik prompt, ahol kiválaszthatja a hivatkozások szerkesztésének lehetőségeit (amely az alábbi párbeszédpanelt mutatja)
Ha ezeket a fájlokat összekapcsolva kell tartani, az értékek frissítése gombra kattintva megadhatja a fájl új helyét. Az Excel megnyit egy párbeszédpanelt, ahol megadhatja az új fájl helyét, ha ott navigál és kiválasztja azt.
Hivatkozás meghatározott névre (ugyanazon vagy külső munkafüzetben)
Ha cellákra és tartományokra kell hivatkoznia, akkor jobb módszer a tartományok meghatározott neveinek létrehozása.
Ez hasznos, mivel megkönnyíti, hogy ezekre a tartományokra névvel hivatkozzon a hosszú és bonyolult referenciacím helyett.
Például egyszerűbb a = SalesData használata a = [Példa fájl.xlsx] 1. lapon! $ A $ 1: $ A $ 10
Ha pedig ezt a definíciót több képletben használta, és meg kell változtatnia a hivatkozást, akkor csak egyszer kell elvégeznie.
Az alábbi lépésekkel hozhat létre elnevezett tartományt egy cellatartományhoz:
- Jelölje ki az összes cellát, amelyet fel szeretne venni a megnevezett tartományba
- Kattintson a Képletek fülre
- Kattintson a Név meghatározása lehetőségre (ez a Meghatározott nevek csoportban van)
- Az Új név párbeszédpanelen adjon nevet ennek a tartománynak (ebben a példában a SalesData nevet használom). Ne feledje, hogy a névben nem lehet szóköz
- Tartsa a hatókört munkafüzetként (kivéve, ha komoly oka van rá, hogy lapszintűvé tegye)
- Győződjön meg arról, hogy a tartományra vonatkozó hivatkozások helyesek.
- Kattintson az OK gombra.
Most létrehozta a megnevezett tartományt, és használhatja a cellacímekkel rendelkező cellahivatkozások helyett.
Például, ha meg akarom kapni a SalesData tartomány összes cellájának összegét, akkor használja az alábbi képletet:
= SUM (Értékesítési adatok)
És mi van, ha ezt a megnevezett tartományt szeretné használni más munkalapokkal vagy akár más munkafüzetekkel?
Tudsz!
Csak ugyanazt a formátumot kell követnie, amelyet a fenti részben tárgyaltunk.
Nem kell visszatérni a cikk elejére. Hadd mondjak itt minden példát, hogy megértse.
Munkafüzet vs munkalap szint nevezett tartományok: Tartomány nevű munkafüzetszint létrehozásakor csak egy tartomány lehet ilyen nevű. Tehát ha ugyanazon munkafüzet külső munkafüzeteiből vagy munkalapjairól hivatkozik rá, akkor nem kell megadnia a munkalap nevét (mivel az a teljes munkafüzetben használható). Ha a tartomány nevű munkalap szintet használja, akkor csak a munkalapon tudja használni, és miközben hivatkozik rá, meg kell adnia a munkalap nevét is.Hivatkozás a megadott névre ugyanazon a munkalapon vagy munkafüzetben
Ha létrehozta a meghatározott nevet a munkafüzet szintjéhez, akkor azt bárhol használhatja a munkafüzetben, csak a megadott név használatával.
Például, ha meg akarom szerezni az általunk létrehozott elnevezett tartomány összes cellájának összegét (SaledData), akkor az alábbi képletet használhatom:
= SUM (SaledData)
Ha létrehozott egy tartomány nevű munkalapszintet, akkor csak akkor használhatja ezt a képletet, ha a megnevezett tartomány ugyanazon a lapon jön létre, ahol a képletet használja.
Ha egy másik lapon szeretné használni (mondjuk a 2. lapon), akkor a következő képletet kell használnia:
= SUM (1. lap! $ A $ 1: $ A $ 10)
És ha a lap nevében szóközök vagy alfanumerikus karakterek vannak, akkor a lap nevét egyetlen idézőjelbe kell tenni.
= SUM ("1. lap"! $ A $ 1: $ A $ 10)
Hivatkozás a megadott névre egy másik munkafüzetben (nyitott vagy zárt)
Ha hivatkozott tartományra szeretne hivatkozni egy másik munkafüzetben, meg kell adnia a munkafüzet nevét, majd a tartomány nevét.
Például, ha Excel -munkafüzete van ExampleFile.xlsx névvel, és elnevezett tartománya SalesData névvel rendelkezik, akkor az alábbi képlet segítségével megkaphatja ennek a tartománynak az összegét egy másik munkafüzetből:
= SUM (ExampleFile.xlsx! SalesData)
Abban az esetben, ha szóközök vannak a fájlnévben, akkor ezeket egy idézőjelben kell használni.
= SUM ('Példa fájl.xlsx'! Értékesítési adatok)
Abban az esetben, ha a lapszintű elnevezett tartományokat megadta, akkor meg kell adnia a munkafüzet nevét, valamint a munkalapot, amikor külső munkafüzetből hivatkozik rá.
Az alábbiakban egy példa a lapszintű tartománynév hivatkozására:
= SUM ('[Példa Fájl.xlsx] Lap1'! Értékesítési adatok)
Amint azt fentebb is említettem, mindig a legjobb, ha munkafüzet -szintű tartományokat hoz létre, hacsak nincs komoly oka az első szintű munkalap létrehozására.
Ha egy elnevezett tartományra hivatkozik egy zárt munkafüzetben, akkor meg kell adnia a fájl elérési útját is. Alább egy példa erre:
= SUM ('C: \ Users \ sumit \ Desktop \ Example File.xlsx'! SalesData)
Amikor hivatkozást hoz létre egy megnevezett tartományra egy nyitott munkafüzetben, majd bezárja a munkafüzetet, az Excel automatikusan megváltoztatja a hivatkozást, és hozzáadja a fájl elérési útját.
Hogyan lehet hivatkozást létrehozni egy elnevezett tartományra?
Ha sok megnevezett tartományt hoz létre és dolgozik vele, nem lehet megjegyezni mindegyik nevét.
Az Excel segít abban, hogy megmutatja a létrehozott összes elnevezett tartomány listáját, és lehetővé teszi, hogy ezeket egyetlen kattintással illessze be a képletekbe.
Tegyük fel, hogy létrehozott egy elnevezett tartományt SalesData, amelyet egy képletben szeretne használni, hogy összesítse a megnevezett tartomány összes értékét.
Ehhez tegye a következő lépéseket:
- Válassza ki azt a cellát, amelybe be szeretné írni a képletet.
- Adja meg a képletet addig a pontig, ahová be kell illesztenie a megnevezett tartományt
- Nyomja meg az F3 billentyűt a billentyűzeten. Ezzel megnyílik a Név beillesztése párbeszédpanel az összes létrehozott név listájával
- Kattintson duplán a beilleszteni kívánt névre.
A fenti lépések beillesztenék a nevet a képletbe, és folytathatja a képlet kidolgozását.
Megjegyzés: Ez a technika csak az azonos munkafüzetben található névtartományokra vonatkozik. Ha a neveket egy külső munkafüzetbe szeretné beszerezni, akkor oda kell mennie, ellenőriznie kell a nevet a Beillesztés név párbeszédpanelen, majd vissza kell térnie, és fel kell használnia a képletben (a megfelelő formátumban a fájlnév használatával).Ez minden, amit tudnia kell arról, hogyan hivatkozhat más lapokra vagy munkafüzetekre, és hogyan hozhat létre külső hivatkozást az Excelben.
Remélem, hasznosnak találta ezt az oktatóanyagot.
