A hyperlink egyszerű, gyakran használt eszköz a munkafüzetben való navigáció megkönnyítésére: könnyű vele egyik munkalapról a másikra ugrani. Gondoltatok rá, hogy ha sok munkalapunk van, milyen egyszerű lenne kiválasztani egy legördülő listából, hogy hová akarunk ugrani? Nem kell nagy helyet elfoglaló link-listát fenntartani minden munkalapon: csak kiválasztod és rákattintasz – ahogy az ábrán látod: a munkalap nevek olasz városok, ezek jelennek meg a legördülő listában:
Igen, ezt meg lehet csinálni – mutatom, hogyan!
Alapvetés
Először nézzük, hogyan működik a hyperlink beszúrás! A Hyperlink beszúrása menüpontban nem csak munkalap cellákat, hanem elnevezett tartományokat is ki lehet választani – így ezekhez egyszerűen kapcsolható hyperlink, ami rögtön a tartományhoz ugrik. Azonban ha jobban megfigyelitek, nem jelennek itt meg azok az elnevezések, amelyek mögött formula áll – hiába ad vissza tartományt a formula. Csak a “statikus”, közvetlen cella referenciát tartalmazó neveket lehet itt a hyperlinkhez adni.
Kérdés tehát: Hogyan tudunk hyperlinket kapcsolni olyan elnevezéshez, ami formulát tartalmaz?
Kicsit be kell ehhez csapni az Excelt: az elnevezést azután változtatod meg, hogy hyperlinkként használtad! Tehát:
- először csinálj egy elnevezést, ami tartományra hivatkozik
- tedd bele a hyperlinkbe
- majd változtasd meg az elnevezést: írd be a formulát, amit használni szeretnél.
Ez lesz a megoldásunk alapja. Nézzük lépésről-lépésre, hogyan lehet felépíteni a legördülő listával kombinált hyperlinket!
1. Munkalap nevek listája
Szükséged lesz egy elnevezett tartományra, ami a legördülő listában használni kívánt munkalap neveket tartalmazza. Írd be egy munkalapra a neveket (ezek az példa szerint az olasz városnevek) és nevezd a tartományt Sheets_List -nek.
2. Érvényesítési lista
Fontos, hogy minden munkalapon ugyanabba a cellába kerüljön majd az érvényesítés! Az egyik munkalapon, ahol használni akarod a trükköt, készíts érvényesítést (Data validation) legördülő listával, a Sheets_List nevet használva.
Ez eddig ugye semmi extra: ki lehet választani egy munkalap nevet a listából, és nem történik semmi. Nézzük tovább!
3. Elnevezés készítése a hyperlinkhez
Csinálj egy elnevezést My_Sheet_Choice névvel, ami az aktuális aktív cellára hivatkozik – ezt fogjuk majd megváltoztatni.
4. Hyperlink felrakása a cellára
Szúrd be a hyperlinket a cellára, ahova a legördülő listát tetted! A hyperlink a My_Sheet_Choice névre mutasson – ahogy az alábbi ábrán látod.
5. A trükk: hivatkozás átírása
Változtasd meg a hivatkozást a My_Sheet_Choice elnevezésben! Olyan formulát fogunk berakni a név referenciájába, ami az adott legördülő listában kiválasztott munkalap celláját adja vissza – így fog a hyperlink a megfelelő munkalapra ugrani.
=INDIRECT( ADDRESS( 1,2,,,INDIRECT( ADDRESS(1,2) ) ) )
=INDIREKT( CÍM( 1;2;;;INDIREKT( CÍM(1;2) ) ) )
Figyelem! a fenti formula a példában bemutatott esethez tartozik, amikor a B1 cellában van a legördülő lista. Ha te máshova tetted, mindkét CÍM formulában kell átírnod a sor és oszlop számot: első paraméter a sor, második az oszlop száma: 1;2 -> B1.
6. Hyperlink másolása
Minden munkalapra, ahol használni szeretnéd a legördülő listás hyperlinket, másold át a cellát, ami a trükkös linket tartalmazza. Figyelj, hogy minden munkalapon ugyanoda kerüljön a linkes cella!
Hogyan működik?
A beágyazott INDIREKT( CÍM(1;2) ) formula kiolvassa a munkalap nevet, amit a B1 cellába írtál. (B1-nek a CÍM formulában az 1;2 paraméter felel meg.) Ez a munkalap név lesz a külső CÍM formula 5. paramétere. Az első két paraméterrel együtt, ami itt is az 1;2 (sor;oszlop) hivatkozás, a formula cella referenciát ad vissza. Összerakja a cellát és a munkalap nevet a megfelelő (szöveges) formátumba. Például: ‘New York’!$B$1 -> aposztrófok közé kerül a munkalap név, és felkiáltójel jön a cellahivatkozás elé. Ebből a szöveges referenciából csinál az INDIREKT valódi cella hivatkozást, amit aztán az elnevezett tartományon keresztül megkap a hyperlink, így ez a dinamikus hivatkozás fogja a hyperlinket a megfelelő munkalapra vezetni.
Innen letölthetitek a példa fájlunkat, amiben fel van építve egy működő modell.
Az elnevezett tartományok alapjairól itt olvashatsz.
Ez az írás egy korábbi angol cikkünk alapján készült.
7 comments
Skip to comment form
Nagyon köszi a cikket!
Nagyon tetszik az ötlet.
Azon gondolkoztam, hogyan lehetne kicsit talán rugalmasabbá tenni, és a HIPERHIVATKOZÁS függvény segítségével talán könnyebben lehet dinamikus linkeket generálni.
Az alap felállás ugyanaz, csináltam egy listát a munkalap nevekkel, amiből készítettem egy legördülő menüt. A példámban az “A2” cellába van a sheet kiválasztó gomb.
A “B2” cellába kézzel meg lehet adni, melyik cellára ugorjon a hiperlink függvény.
maga a képlet így néz ki, amit én a “D2”-be tettem, de bárhol lehet :
=HIPERHIVATKOZÁS(“#”&A2&”!”&B2;”>>>”)
A # azt jelzi, hogy a munkafüzeten belül mozogjon, a A2&”!”&B2 paraméter összefűzi a link további paramétereit, és a második argumentum a “rövid név” azaz friendly name, pedig “>>>” értéket kapott, ez fog megjelenni a cellában.
Ezzel a módszerrel el lehet kerülni az INDIREKT volitális függvények használatát
Author
Köszönöm a kommentet!
Nagyon jó megoldás a tied is, köszi, hogy megosztottad.
A volatilis INDIREKTtel kapcsolatban: az említett esetben a függvény csak elnevezésben van használva, nem a munkalapon. A volatilitás csak a munkalapon értelmezhető, mert az elnevezés akkor “kalkulálódik”, amikor hivatkozunk rá.
Kedves András!
Bár több, mint egy évvel ezelőtti a cikk, de én csak most kerültem ilyen probléma elé.
A megoldás kitűnő, és nagy mennyiségű adatnál gyakorlatilag csak ez a jó, hiszen ott minden hivatkozott fájl esetében bele kellene körmölni legalább a fájl nevét az adott cellához.
Egy csomó munkától mentettél meg!
Az én esetemben egy olyan adatbázist kellett létrehoznom, ahol több ezer fénykép (jpg) fájlt kell csatolnom az adott nevekhez. A jpg-ket egy mappába tettem az Excellel. A fájlok nevét a tárgy – melytől a kép készült – elhelyezkedése (pozíciója) alapján átneveztem, (számoztam), és ezt a nevet az Excelben generáltam össze. Így a link teljesen automatikusan keletkezik, sőt, amennyiben változik a pozíció és át kell nevezni a jpg-t a link követi a fájlt. Ha az egészet másik mappába helyezem a =CELLA(“filenév”) -ből generált mappa követi a jpg elhelyezkedését is.
Úgyhogy még egyszer köszi a dinamikus ötletet.
Gábor
Kedves Kris!
Ez a legördül menüből link gyártás nagyon parádés. Eltettem magamnak a későbbiekre. 🙂
Viszont van egy problémám, ami ide tartozik, ide tartozhat, már ha legördülő listához kapcsolódik.
Adott egy lista, a lista az A1:A10 tartományban található, aminek a neve – egyszerűség kedvéért – LISTA.
Van egy másik táblázat (ADATBEMENET, másik munkalap B1:B20 tartománya), ahol egymás alatt több cellába is a LISTA elemeiből kell kiválasztani adatot. Odáig eljutottam, hogy a cellákat az érvényesítés pontban található opciók segítségével megadtam a LISTA halmazt, mint egyedül érvényes bemenetet. Még az üresek átugrását is bepipáltam. A csavar a következő: LISTA elemeit egyszer és csak egyszer választhatom ki. Még ezt is sikerült megoldanom a LISTA elemei táblázatban ellenőrzőm, hogy adott elem szerepel-e már a “bemenet” táblázatban ( LISTA első cellájának tartalma: =HA(DARABTELI(ADATBEMENET;”lista elem1″)>=1;””;”lista elem1″) ). Sajnos hiába választom ki a LISTA egyik elemét, a következő cella legördülő listájában kihagyni, még kihagyja, de a listában üres hely marad:
Értsd: B3 cellában már kiválasztottam a “lista elem3″ tételt, de a B6 cellában nem azt találom, hogy kihagyás mentesen jönnek a lista elemek, annak ellenére, hogy be van pipálva az üresek átugorja opció(!), hanem a következőt:
lista elem1
lista elem2
lista elem4
…
Kérdésem, hogy rosszul értelmezem az üres fogalmát, vagyis =”” nem egyenlő az üres, így azt nem is hagyja ki a listából?
Illetve, hogy van-e olyan megoldás, amivel ezt a dolgot meg tudom csinálni, vagyis a már kiválasztott elem egy másik cellánál a kiválasztáskor meg se jelenjen és a helyén se legyen üres sor?
Remélem sikerül leírnom a érthetően a problémát.
Segítséged előre is köszönöm.
Üdvözlettel:
Bottlik Gábor
Author
Szia!
Picit hosszú lenne elmagyarázni – valóban az üres az nem mindig úgy üres, ahogy gondoljuk.
Itt találsz egy fájlt, egy lehetséges megoldással. Remélem, erre gondoltál.
Üdv:
Kris
Kedves Krisz!
Ez az!
Nagyon köszönöm a megoldást.
Üdvözlettel:
Bottlik Gábor
Author
Nagyon szívesen, örülök, hogy hasznos!