Nejprve se vrátíme úplně na začátek a připomeneme si různé typy odkazů, které v Excelu můžeme vytvořit. Ve druhé části se zaměříme na funkce pracující s odkazy v Excelu – ODKAZ, NEPŘÍMÝ.ODKAZ a HYPERTEXTOVÝ.ODKAZ.
Odkazy v Excelu jsou mechanismem, který umožňuje odkazovat na buňky, rozsahy buněk, nebo dokonce na jiné listy či soubory Excelu. Tyto odkazy jsou užitečné při vytváření vzorců, funkcí a při propojování dat v rámci různých částí sešitu i mezi různými sešity.
Odkaz VŽDY začíná znaménkem ROVNÁ SE. Dále se liší podle typu, pravidla jsou ale pořád stejná:
- Rovná se
- Název sešitu v hranatých závorkách
- Název listu končící vykřičníkem
- Odkaz na buňku (kombinace písmena = sloupec a čísla = řádek)
JAKÉ ODKAZY MŮŽEME V EXCELU VYTVOŘIT?
1. Odkaz v Excelu na buňku:
Pro odkazování na konkrétní buňku klikneme myší do příslušné buňky. Díky tomu vytvoříme odkaz na buňku ve formátu (pokud je buňka na aktuálním listu):
=A1
2. Odkaz v Excelu na více buněk:
Při odkazování na rozsah buněk vytvoří Excel odkaz v tomto formátu (odkazujeme se do oblasti buněk A1 až B10, kdy A1 je levá horní buňky a B10 je pravá dolní buňka):
=A1:B10
3. Odkazy v Excelu na jiné listy:
List1 je název listu, který vidíme dole na „oušku“ listu, za ním následuje vždy VYKŘIČNÍK a adresa buňky (například A1). Ve druhém příkladu vidíme odkaz na rozsah buněk na jiném listu:
=List1!A1
=List1!A1:B10
Tento typ odkazu používáme často ve vzorcích a funkcích. Například: =SUMA(List1!A1:A10), kde se sčítají hodnoty v rozsahu A1:A10 na listu List1.
4. Odkazy v Excelu na jiné sešity:
Můžeme vytvářet i odkazy na buňky v jiných sešitech. V tomto případě bude před názvem listu ještě název sešitu v hranatých závorkách.
=[NázevSešitu]List1!A1
PROČ POUŽÍVAT ODKAZY V EXCELU?
- Výhodou používání odkazů v Excelu je propojení buněk a tedy jejich automatická aktualizace. Pokud se hodnoty v odkazovaných buňkách změní, vzorce automaticky aktualizují své výsledky bez nutnosti manuálních změn na několika místech sešitu.
- Propojování dat – odkazy mohou propojovat data mezi různými listy nebo sešity, což umožňuje efektivnější správu informací a propojování dat.
- Poznámka: Odkazy v žádném případě nevypisujeme na klávesnici, ale označujeme je myší nebo klávesovými zkratkami!
FUNKCE ODKAZ
Pomocí funkce ODKAZ můžeme získat adresu buňky v listu na základě zadaných čísel řádku a sloupce. Funkce ODKAZ(2,3) třeba vrátí adresu $C$2 = 2. řádek a 3. sloupec. Můžete taky použít funkce ŘÁDEK a SLOUPEC pro poskytnutí číselných argumentů řádku a sloupce pro funkci ODKAZ.
Argumenty funkce jsou:
ODKAZ(řádek, sloupec, [typ], [a1], [text])
- Řádek – číslo řádku použité v odkazu na buňku.
- Sloupec – číslo sloupce použité v odkazu na buňku.
- Typ – nepovinný argument, typ odkazu, který bude vrácen (absolutní, relativní, smíšený – vysvětlíme si v dalším článku).
- A1 – nepovinný argument -> můžeme přeskočit nebo dávám 1, protože pracuji s „normálními“ odkazy v Excelu
- Text – pokud chceme zadat název listu
FUNKCE HYPERTEXTOVÝ.ODKAZ
Funkce HYPERTEXTOVÝ.ODKAZ umožňuje přejít na jiné místo v sešitu, ale i mimo něj. Když klikneme na buňku obsahující funkci HYPERTEXTOVÝ.ODKAZ, Excel přejde na uvedené místo nebo otevře zadaný dokument.
Syntaxe funkce HYPERTEXTOVÝ.ODKAZ má následující argumenty:
HYPERTEXTOVÝ.ODKAZ(umístění;[název])
- Umístění odkazu – konkrétní buňka, cesta a název souboru k dokumentu, který chceme otevřít nebo kam chceme přejít.
- Název – nepovinný argument, text, který se zobrazí v buňce z které se budeme odkazovat. Jestliže argument Název vynecháte, zobrazí se v buňce jako text argument Umístění. Text nezapomene zadávat v uvozovkách!
Příklad externího i interního odkazu najdete níže. První odkaz bude mít v buňce napsaný text Excel a po kliknutí mě zavede na webové stránky EXCELHROU.CZ. Druhý odkaz bude mít v buňce napsáno PŘEJÍT SEM a po klinutí mě zavede na list1 do buňky B2:
=HYPERTEXTOVÝ.ODKAZ(„http://excelhrou.cz“;“Excel“)
=HYPERTEXTOVÝ.ODKAZ(„#List1!B2″;“Přejít sem“)
POZNÁMKA 1:
Pokud se odkazujeme v rámci daného sešitu, vkládáme před název listu znak # (Ctrl + levý Alt + X).
POZNÁMKA 2:
Hypertextové odkaz lze vkládat i z Karty Vložit nebo přes pravé tlačítko myši!
FUNKCE NEPŘÍMÝ.ODKAZ
Tato funkce není tolik známá, ale za to je podle mě ze všech zmíněných nejužitečnější a můžete si díky ní ušetřit spoustu práce. Funkce totiž vrací obsah buňky. Funkci NEPŘÍMÝ.ODKAZ použijme, pokud chceme změnit odkaz na buňku v rámci vzorce/funkce a nechceme měnit vzorec.
NEPŘÍMÝ.ODKAZ(odkaz, [a1])
- Odkaz – odkaz na buňku. Pokud odkazujeme na jiný sešit (externí odkaz), musí být druhý sešit otevřený.
- A1 – nepovinný argument, určuje typ odkazu.
KOMBINACE FUNKCE ODKAZ A NEPŘÍMÝ.ODKAZ
To nejlepší na závěr! Úplně skvělá je kombinace funkce NEPŘÍMÝ.ODKAZ a ODKAZ. Funkci ODKAZ lze totiž krásně využít pro naplnění argumentů funkce NEPŘÍMÝ.ODKAZ, kde zadáme adresu řádku a sloupce přes ODKAZ a přes NEPŘÍMÝ.ODKAZ si vrátíme obsah buňky.
Složitější možností by bylo slučování přes funkci CONCAT, nebo spojování řetězce přes &, ale i tuto možnost vám ve videonávodu ukážu.
Pro vrácení čísla sloupce nebo řádku lze použít také funkce ŘÁDEK a SLOUPEC – opět ukážu ve videonávodu.
Videonávod s 2 praktickými příklady "Jak na odkazy v Excelu" sledujte níže:
PŘÍKLAD 1:
Máme list PŘEHLED, na který chceme sbírat data ze všech ostatních listů, které mají naprosto stejnou strukturu, jen se liší obsahem buněk.
PŘÍKLAD 2:
Máme stejná data na listech (například měsíční reporty) a na listu přehled se chceme odkazovat vždy na aktuální měsíc, který změníme v buňce C1. Tato buňka bude napojená na vzorce a funkce a nebude tedy nutné je každý měsíc na celém listu měnit.
Dobrý den,
Mám dotaz ve věci odkazu v nějaké buňce excelu – např. „=E53“, (přičemž v buňce E53 se zobrazuje na základě zadané funkce „=EOMONTH(DATUM($D$52;$D$51;1);0)“ datum 31.05.2024,, tzn. že chci do nějaké buňky zobrazit datum z buňky E53.
Vše se zdá být v pořádku, ale v nějaké buňce, kam chci aby se zobrazoval datum z buňky E53 se zobrazuje místo data odkaz „=E53“. Prosím o radu, co s tím.
Děkuji
S pozdravem
Martin Bína
Dobrý den,
děkuji za dotaz. Zkoušela jsem prakticky váš příklad a i při použití „normálního odkazu =E53 nebo kombinace funkcí NEPŘÍMÝ.ODKAZ a ODKAZ podle článku se mi zobrazuje datum korektně. Napadá mě, že problémem může být formátování cílové buňky, pokud ji máte třeba jako text -> zkuste ji přeformátovat nebo na kartě DOMŮ přes Vymazat odstranit formáty a potom znovu „odentrovat“. Jestli problém stále přetrvává, můžete mi poslat sešit na email excelhrou@email.cz a zkusím na to podívat přímo u vás.
Děkuji a hezký den,
Lucie
Dobrý den,
v Excelu mám pod sebou cca 1200 odkazů, kde jeden odkaz je vždy v samostatné buňce.
Po vložení do excelu jsou ale tyto hypertextové odkazy pouze černé, musím u každého odkazu kliknout do buňky, pak ji opustit aby byl tento odkaz modrý a po najetí na tento odkaz myší a kliknutí se mi již otevře správný soubor.
Lze ale tyto odkazy do excelu vložit tak, aby rovnou po vložení do excelu byl odkaz jako hypertext a nemusel jsem do buňky klikat a opouštět ji?
Používám Excel 2013.
Děkuji moc za radu/pomoc.
Petr Kocourek
Dobrý den,
děkuji za dotaz. Pokud to správně chápu, mohla by Vám pomoct právě funkce =HYPERTEXTOVÝ.ODKAZ. Udělala bych to přes pomocný sloupec. Stačí zadat jako první argument odkaz na buňku s „černým“ odkazem a odkazy by se takto měly hromadně převést na funkční modré.
Dejte vědět, jestli pomohlo :).
Hezký den,
Lucie
Dobrý den,
v excelu mám použit odkaz na hodnotu z jiného listu, např. =’list2′!J8.
Pokud uložím, tento odkaz se změní na hodnotu, tzn. při příštím otevření sešitu již v buňce pouze tato hodnota.
Prosím informaci / radu, čím způsobeno a jak změnit tak, aby zůstal odkaz.
Předem děkuji.
J. Kaštan
Dobrý den,
pokud to dobře chápu, chcete v buňce vidět odkaz místo hodnoty. Potom to můžete zkusit obejít třeba tím, že před odkaz dáte apostrof a díky tomu s ním bude Excel pracovat jako s textem a ne s odkazem.
Například: ‚=list2′!J8
Pomohlo? 🙂
Děkuji za dotaz a hezký den,
Lucie
Dobrý den,
Děje se mi to samé jako panu Kaštanovi. U jednoho souboru mám na jednom listu v buňce odkaz na jinou buňku v jiném listu, třeba na listu1 mám v buňce A1 napsaný odkaz: =List2!A1. Vše funguje a v buňce A1 na listu1 je vidět hodnota z buňky A1 na listu2. Jakmile soubor uložím, odkaz v buňce A1 na listu1 se změní na hodnotu, zkrátka odkaz z buňky zmizí.
Dobrý den,
tak to jsem nejprve špatně pochopila – děkuji za upřesnění :). Jako první zkuste jít do SOUBOR – Možnosti – Vzorce a zde zkontrolovat, že máte nastaveno AUTOMATICKÉ přepočítávání (stejné nastavení je i na kartě VZORCE). Jako další bych zkusila použít funkce NEPŘÍMÝ.ODKAZ a ODKAZ vysvětlené v tomto článku.
Dejte vědět, jestli pomohlo. Hezký den,
Lucie