V aplikaci Microsoft Excel je zcela běžné pracovat s odkazy na buňky, které se nacházejí v jiných listech nebo dokonce v odlišných excelových souborech. I když se to na první pohled může zdát komplikované a nejasné, s trochou praxe zjistíte, že to není vůbec složité.
V tomto textu si ukážeme, jak vytvářet odkazy na buňky v rámci jednoho souboru, ale i jak odkazovat na data z jiného souboru. Zaměříme se také na to, jak lze odkazovat na rozsah buněk ve vzorcích, jak si práci usnadnit pomocí definovaných názvů a jak využít funkci SVYHLEDAT pro dynamické odkazy.
Odkazování na buňky v jiném listu stejného souboru Excel
Základní zápis odkazu na buňku se skládá z písmene sloupce a čísla řádku. Například odkaz na buňku B3 ukazuje na buňku, která se nachází na průsečíku sloupce B a řádku 3.
Při odkazování na buňky v jiných listech se před tímto označením buňky uvede název druhého listu. Například, níže vidíte odkaz na buňku B3 v listu nazvaném „leden“.
=leden!B3
Vykřičník (!) slouží jako oddělovač mezi názvem listu a adresou buňky.
Pokud má název listu mezery, je potřeba ho v odkazu uzavřít do jednoduchých uvozovek.
='Lednové prodeje'!B3
Tyto odkazy je možné psát přímo do buňky, avšak mnohem pohodlnější a spolehlivější je nechat Excel, aby odkaz vytvořil za vás.
Do buňky napište rovnítko (=), klikněte na kartu s listem, na který chcete odkazovat, a poté klikněte na cílovou buňku.
Excel automaticky vloží odkaz do řádku vzorců.
Pro dokončení vzorce stiskněte klávesu Enter.
Odkazování na buňky v jiném souboru Excel
Stejným postupem lze odkazovat i na buňky v jiném sešitu. Před zahájením psaní vzorce se ujistěte, že je druhý soubor Excel otevřený.
Vložte znak rovná se (=), přepněte se do druhého souboru a klikněte na buňku, na kterou chcete odkazovat. Pro potvrzení stiskněte Enter.
Kompletní křížový odkaz obsahuje název druhého sešitu v hranatých závorkách, za kterým následuje název listu a buňky.
=[Chicago.xlsx]leden!B3
Pokud název souboru nebo listu obsahuje mezery, je nutné celý odkaz na soubor (včetně hranatých závorek) uzavřít do jednoduchých uvozovek.
='[New York.xlsx]leden'!B3
V tomto příkladu si můžete všimnout znaků dolaru ($) v adrese buňky. Jedná se o absolutní odkaz (více informací o absolutních odkazech).
Při odkazování na buňky a rozsahy v různých souborech Excel jsou odkazy standardně absolutní. V případě potřeby je lze změnit na relativní.
Pokud se na vzorec podíváte ve chvíli, kdy je odkazovaný sešit zavřený, bude obsahovat celou cestu k danému souboru.
I když je vytváření odkazů mezi sešity jednoduché, může být náchylnější k problémům. Uživatelé, kteří vytvářejí nebo přejmenovávají složky a přesouvají soubory, mohou tyto odkazy poškodit a způsobit chyby. Spolehlivější je, pokud je to možné, uchovávat data v jednom sešitu.
Křížové odkazy na rozsah buněk ve funkci
Odkazování na jednu buňku je užitečné, nicméně možná budete chtít použít funkci (např. SUM), která odkazuje na oblast buněk z jiného listu nebo sešitu.
Spusťte funkci standardním způsobem a poté klikněte na list a oblast buněk – stejně jako v předchozích příkladech.
V níže uvedeném příkladu funkce SUM sčítá hodnoty z rozsahu B2:B6 v listu s názvem Prodej.
=SUM(Prodej!B2:B6)
Použití definovaných názvů pro jednoduché křížové odkazy
V Excelu můžete buňce nebo rozsahu buněk přiřadit název. Takový název je mnohem srozumitelnější než adresa buňky nebo rozsahu. Pokud v tabulce používáte velké množství odkazů, pojmenování vám usnadní orientaci.
Navíc, tento název je jedinečný v rámci celého souboru Excel.
Můžeme například buňku pojmenovat „CelkemChicago“ a křížový odkaz by pak vypadal takto:
=CelkemChicago
Je to mnohem přehlednější varianta oproti standardnímu odkazu, jako například:
=Prodej!B2
Vytvoření definovaného názvu je jednoduché. Vyberte buňku nebo rozsah buněk, které chcete pojmenovat.
Klikněte do pole Název v levém horním rohu, napište název a stiskněte Enter.
Při vytváření definovaných názvů nelze používat mezery. Proto byla v tomto příkladu slova v názvu spojena a oddělena velkým písmenem. Můžete také použít pomlčku (-) nebo podtržítko (_).
Excel má také Správce názvů, který usnadňuje sledování těchto názvů. Klikněte na Vzorce > Správce názvů. Zobrazí se okno, kde najdete seznam definovaných názvů v sešitu, jejich umístění a aktuální hodnoty.
Pomocí tlačítek v horní části můžete tyto definované názvy upravovat nebo odstraňovat.
Formátování dat jako tabulky
Při práci s rozsáhlými datovými sadami může použití funkce Formátovat jako tabulku zjednodušit způsob, jakým na data odkazujete.
Vezměme si následující jednoduchou tabulku.
Tuto tabulku lze formátovat jako tabulku.
Klikněte do libovolné buňky v seznamu, přejděte na kartu „Domů“, klikněte na tlačítko „Formátovat jako tabulku“ a vyberte si styl.
Ověřte, že je rozsah buněk správný a že tabulka má záhlaví.
Na kartě „Návrh“ můžete tabulce přiřadit název.
Pokud bychom potřebovali sečíst prodeje v Chicagu, mohli bychom na tabulku odkazovat jejím názvem (z libovolného listu), následovaným hranatými závorkami ([) pro zobrazení seznamu sloupců tabulky.
Vyberte sloupec dvojitým kliknutím v seznamu a vložte uzavírací hranatou závorku. Výsledný vzorec by vypadal asi takto:
=SUMA(Prodeje[Chicago])
Jak je vidět, tabulky usnadňují odkazování na data pro agregační funkce, jako SUMA a PRŮMĚR, než standardní odkazy na listy.
Tato tabulka je pro demonstraci malá. Čím větší je tabulka a čím více listů máte v sešitu, tím více výhod uvidíte.
Použití funkce SVYHLEDAT pro dynamické odkazy
Všechny dosavadní odkazy byly pevně svázané s konkrétní buňkou nebo rozsahem buněk. To je sice praktické, ale často nemusí stačit.
Co se však stane, když se buňka, na kterou odkazujete, po vložení nových řádků změní? Na to se podíváme v následujícím textu.