A Microsoft Excelben gyakori feladat, hogy más munkalapokon vagy akár különböző Excel-fájlok celláira hivatkozzunk. Eleinte ez kissé ijesztőnek és zavarónak tűnhet, de ha egyszer megérted, hogyan működik, már nem is olyan nehéz.
Ebben a cikkben megvizsgáljuk, hogyan hivatkozhat egy másik munkalapra ugyanabban az Excel-fájlban, és hogyan hivatkozhat egy másik Excel-fájlra. Kitérünk olyan dolgokra is, mint például, hogyan lehet hivatkozni egy függvényben egy cellatartományra, hogyan lehet egyszerűbbé tenni a dolgokat meghatározott nevekkel, és hogyan kell használni a VLOOKUP-ot dinamikus hivatkozásokhoz.
Tartalomjegyzék
Hogyan hivatkozhat egy másik munkalapra ugyanabban az Excel-fájlban
Az alapvető cellahivatkozás az oszlop betűjeként, majd a sorszámmal van írva.
Tehát a B3 cellahivatkozás a B oszlop és a 3. sor metszéspontjában lévő cellára vonatkozik.
Ha más munkalapon lévő cellákra hivatkozik, ezt a cellahivatkozást megelőzi a másik munkalap neve. Az alábbiakban például egy hivatkozás található a „January” lapnév B3 cellájára.
=January!B3
A felkiáltójel (!) választja el a munkalap nevét a cella címétől.
Ha a lapnév szóközt tartalmaz, akkor a hivatkozást idézőjelbe kell tenni.
='January Sales'!B3
A hivatkozások létrehozásához közvetlenül beírhatja őket a cellába. Könnyebb és megbízhatóbb azonban, ha hagyja, hogy az Excel megírja a referenciát.
Írjon be egy egyenlőségjelet (=) egy cellába, kattintson a Lap fülre, majd kattintson arra a cellára, amelyre kereszthivatkozást szeretne adni.
Miközben ezt teszi, az Excel beírja helyetted a hivatkozást a Képletsorba.
Nyomja meg az Entert a képlet befejezéséhez.
Hogyan lehet hivatkozni egy másik Excel-fájlra
Ugyanezzel a módszerrel hivatkozhat egy másik munkafüzet celláira. Csak győződjön meg arról, hogy a másik Excel-fájl nyitva van, mielőtt elkezdi begépelni a képletet.
Írjon be egy egyenlőségjelet (=), váltson át a másik fájlra, majd kattintson a hivatkozni kívánt fájl cellájára. Nyomja meg az Enter billentyűt, ha végzett.
Az elkészült kereszthivatkozás tartalmazza a másik munkafüzet nevét szögletes zárójelben, majd a lap nevét és a cella számát.
=[Chicago.xlsx]January!B3
Ha a fájl vagy a lap neve szóközöket tartalmaz, akkor a fájl hivatkozását (beleértve a szögletes zárójeleket is) idézőjelbe kell tennie.
='[New York.xlsx]January'!B3
Ebben a példában dollárjeleket ($) láthat a cellacímek között. Ez egy abszolút cellahivatkozás (További információ az abszolút cellahivatkozásokról).
Amikor cellákra és tartományokra hivatkozik különböző Excel-fájlokban, a hivatkozások alapértelmezés szerint abszolútak lesznek. Ezt szükség esetén módosíthatja relatív hivatkozásra.
Ha megnézi a képletet a hivatkozott munkafüzet bezárásakor, az a fájl teljes elérési útját fogja tartalmazni.
Bár a hivatkozások létrehozása más munkafüzetekre egyszerű, ezek sokkal érzékenyebbek a problémákra. A mappákat létrehozó vagy átnevező, valamint fájlokat áthelyező felhasználók megszakíthatják ezeket a hivatkozásokat, és hibákat okozhatnak.
Az adatok egy munkafüzetben való tárolása, ha lehetséges, megbízhatóbb.
Hogyan lehet kereszthivatkozni egy függvényben egy cellatartományra
Elég hasznos egyetlen cellára hivatkozni. De érdemes lehet olyan függvényt (például SUM) írni, amely egy másik munkalapon vagy munkafüzeten egy cellatartományra hivatkozik.
Indítsa el a függvényt a szokásos módon, majd kattintson a lapra és a cellák tartományára – ugyanúgy, ahogy az előző példákban tette.
A következő példában egy SUM függvény összegzi a B2:B6 tartomány értékeit egy Értékesítés nevű munkalapon.
=SUM(Sales!B2:B6)
Meghatározott nevek használata egyszerű kereszthivatkozásokhoz
Az Excelben nevet rendelhet egy cellához vagy cellatartományhoz. Ez többet jelent, mint egy cella vagy tartománycím, ha visszanéz rájuk. Ha sok hivatkozást használ a táblázatban, a hivatkozások elnevezése sokkal könnyebben áttekintheti, hogy mit végzett.
Még jobb, hogy ez a név egyedi az Excel-fájlban található összes munkalap esetében.
Például elnevezhetnénk egy cellát „ChicagoTotal”-nak, majd a kereszthivatkozás a következő lenne:
=ChicagoTotal
Ez egy értelmesebb alternatíva az ehhez hasonló szabványos hivatkozásokhoz:
=Sales!B2
Könnyű létrehozni egy meghatározott nevet. Először válassza ki az elnevezni kívánt cellát vagy cellatartományt.
Kattintson a bal felső sarokban található Név mezőbe, írja be a hozzárendelni kívánt nevet, majd nyomja meg az Enter billentyűt.
Definiált nevek létrehozásakor nem használhat szóközt. Ezért ebben a példában a szavakat a névben egyesítettük, és nagybetűvel választottuk el. A szavakat olyan karakterekkel is elválaszthatja, mint például kötőjel (-) vagy aláhúzás (_).
Az Excel névkezelővel is rendelkezik, amely megkönnyíti ezeknek a neveknek a jövőbeni nyomon követését. Kattintson a Képletek > Névkezelő elemre. A Névkezelő ablakban láthatja a munkafüzetben lévő összes definiált név listáját, hol találhatók, és milyen értékeket tárolnak jelenleg.
Ezután a felül található gombokkal szerkesztheti és törölheti ezeket a meghatározott neveket.
Hogyan formázzuk az adatokat táblázatként
Ha a kapcsolódó adatok kiterjedt listájával dolgozik, az Excel Formátum táblázatként funkciójának használatával leegyszerűsítheti az adatokra való hivatkozás módját.
Vegyük a következő egyszerű táblázatot.
Ezt táblázatként is meg lehet formázni.
Kattintson egy cellára a listában, váltson a „Kezdőlap” fülre, kattintson a „Táblázat formázása” gombra, majd válasszon stílust.
Győződjön meg arról, hogy a cellák tartománya helyes, és a táblázatnak vannak fejlécei.
Ezután a „Tervezés” lapon értelmes nevet rendelhet a táblázathoz.
Ezután, ha összegeznünk kell Chicago eladásait, hivatkozhatunk a táblázatra a nevével (bármely lapról), majd egy szögletes zárójellel ([) to see a list of the table’s columns.
Select the column by double-clicking it in the list and enter a closing square bracket. The resulting formula would look something like this:
=SUM(Sales[Chicago])
Láthatja, hogy a táblázatok hogyan tehetik egyszerűbbé az adatok hivatkozását az olyan összesítő függvényekhez, mint a SUM és AVERAGE, mint a szabványos laphivatkozások.
Ez a táblázat kicsi a szemléltetés céljából. Minél nagyobb a táblázat és minél több lap van egy munkafüzetben, annál több előnyt fog látni.
A VLOOKUP függvény használata dinamikus hivatkozásokhoz
A példákban eddig használt hivatkozások mindegyike egy adott cellához vagy cellatartományhoz volt rögzítve. Ez nagyszerű, és gyakran elegendő az Ön igényeinek.
Azonban mi van akkor, ha a hivatkozott cella módosulhat új sorok beszúrásakor vagy néhány esetben