V aplikaci Microsoft Excel je běžným úkolem odkazovat na buňky v jiných listech nebo dokonce v různých souborech aplikace Excel. Zpočátku se to může zdát trochu skličující a matoucí, ale jakmile pochopíte, jak to funguje, není to tak těžké.
V tomto článku se podíváme na to, jak odkazovat na jiný list ve stejném souboru aplikace Excel a jak odkazovat na jiný soubor aplikace Excel. Probereme také věci, jako je odkazování na rozsah buněk ve funkci, jak věci zjednodušit pomocí definovaných názvů a jak používat SVYHLEDAT pro dynamické odkazy.
Table of Contents
Jak odkazovat na jiný list ve stejném souboru aplikace Excel
Základní odkaz na buňku je zapsán jako písmeno sloupce následované číslem řádku.
Takže odkaz na buňku B3 odkazuje na buňku na průsečíku sloupce B a řádku 3.
Při odkazování na buňky na jiných listech je před tímto odkazem na buňku uveden název druhého listu. Níže je například odkaz na buňku B3 na listu s názvem „leden“.
=January!B3
Vykřičník (!) odděluje název listu od adresy buňky.
Pokud název listu obsahuje mezery, musíte název v odkazu uzavřít do jednoduchých uvozovek.
='January Sales'!B3
Chcete-li vytvořit tyto odkazy, můžete je zadat přímo do buňky. Je však jednodušší a spolehlivější nechat Excel napsat referenci za vás.
Do buňky zadejte rovnítko (=), klikněte na kartu List a poté klikněte na buňku, na kterou chcete vytvořit křížový odkaz.
Jak to uděláte, Excel za vás zapíše odkaz do řádku vzorců.
Stiskněte Enter pro dokončení vzorce.
Jak odkazovat na jiný soubor aplikace Excel
Stejnou metodou můžete odkazovat na buňky jiného sešitu. Než začnete psát vzorec, ujistěte se, že máte otevřený druhý soubor aplikace Excel.
Zadejte znaménko rovná se (=), přepněte na jiný soubor a klepněte na buňku v souboru, na který chcete odkazovat. Až budete hotovi, stiskněte Enter.
Dokončený křížový odkaz obsahuje název druhého sešitu uzavřený v hranatých závorkách, za nímž následuje název listu a číslo buňky.
=[Chicago.xlsx]January!B3
Pokud název souboru nebo listu obsahuje mezery, budete muset odkaz na soubor (včetně hranatých závorek) uzavřít do jednoduchých uvozovek.
='[New York.xlsx]January'!B3
V tomto příkladu můžete mezi adresou buňky vidět znaky dolaru ($). Toto je absolutní odkaz na buňku (Další informace o absolutních odkazech na buňku).
Při odkazování na buňky a rozsahy v různých souborech aplikace Excel jsou odkazy ve výchozím nastavení absolutní. V případě potřeby to můžete změnit na relativní referenci.
Pokud se podíváte na vzorec, když je odkazovaný sešit zavřený, bude obsahovat celou cestu k tomuto souboru.
Ačkoli je vytváření odkazů na jiné sešity jednoduché, jsou 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 narušit a způsobit chyby.
Uchovávání dat v jednom sešitu, pokud je to možné, je spolehlivější.
Jak křížově odkazovat na rozsah buněk ve funkci
Odkazování na jednu buňku je dostatečně užitečné. Možná však budete chtít napsat funkci (například SUM), která odkazuje na oblast buněk v jiném listu nebo sešitu.
Spusťte funkci jako obvykle a poté klikněte na list a oblast buněk – stejně jako v předchozích příkladech.
V následujícím příkladu funkce SUM sčítá hodnoty z rozsahu B2:B6 na listu s názvem Prodej.
=SUM(Sales!B2:B6)
Jak používat definované názvy pro jednoduché křížové odkazy
V Excelu můžete buňce nebo oblasti buněk přiřadit název. To je smysluplnější než adresa buňky nebo rozsahu, když se na ně podíváte zpět. Pokud v tabulce používáte hodně odkazů, pojmenování těchto odkazů může mnohem snáze vidět, co jste udělali.
Ještě lepší je, že tento název je jedinečný pro všechny listy v tomto souboru aplikace Excel.
Například bychom mohli pojmenovat buňku „ChicagoTotal“ a pak by křížový odkaz zněl:
=ChicagoTotal
Toto je smysluplnější alternativa ke standardnímu odkazu, jako je tento:
=Sales!B2
Je snadné vytvořit definovaný název. Začněte výběrem buňky nebo oblasti buněk, které chcete pojmenovat.
Klikněte do pole Name v levém horním rohu, zadejte jméno, které chcete přiřadit, a stiskněte Enter.
Při vytváření definovaných jmen nelze používat mezery. Proto byla v tomto příkladu slova v názvu spojena a oddělena velkým písmenem. Slova můžete také oddělit znaky, jako je pomlčka (-) nebo podtržítko (_).
Excel má také Správce názvů, který usnadňuje sledování těchto názvů v budoucnu. Klikněte na Vzorce > Správce názvů. V okně Správce názvů můžete vidět seznam všech definovaných názvů v sešitu, kde se nacházejí a jaké hodnoty aktuálně ukládají.
Poté můžete pomocí tlačítek v horní části upravit a odstranit tyto definované názvy.
Jak formátovat data jako tabulku
Při práci s rozsáhlým seznamem souvisejících dat může použití funkce Formát jako tabulku aplikace Excel zjednodušit způsob odkazování na data v ní.
Vezměte si následující jednoduchou tabulku.
Toto může být formátováno jako tabulka.
Klikněte na buňku v seznamu, přepněte na kartu „Domů“, klikněte na tlačítko „Formátovat jako tabulku“ a poté vyberte styl.
Potvrďte, že rozsah buněk je správný a že vaše tabulka má záhlaví.
Na kartě „Design“ pak můžete svému stolu přiřadit smysluplný název.
Pokud bychom pak potřebovali sečíst tržby Chicaga, mohli bychom se na tabulku odkázat jejím názvem (z libovolného listu), za kterým následuje hranatá závorka ([) 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])
Můžete vidět, jak tabulky mohou usnadnit odkazování na data pro agregační funkce, jako je SUM a AVERAGE, než standardní odkazy na listy.
Tento stůl je pro účely demonstrace malý. Čím větší je tabulka a čím více listů v sešitu máte, tím více výhod uvidíte.
Jak používat funkci VLOOKUP pro dynamické reference
Odkazy použité v dosavadních příkladech byly všechny fixovány na konkrétní buňku nebo rozsah buněk. To je skvělé a často to pro vaše potřeby stačí.
Co když se však buňka, na kterou odkazujete, může po vložení nových řádků nebo některých změnit