Schopnost odkazovat na buňky a rozsahy je to, co dělá každý tabulkový nástroj. A Excel je nejlepší a nejvýkonnější.
V tomto kurzu se budu zabývat vším, co potřebujete vědět o tom, jak odkazovat na buňky a rozsahy v aplikaci Excel. Kromě základního odkazování na stejný list bude hlavní část tohoto tutoriálu o jak odkazovat na jiný list nebo sešit v aplikaci Excel.
I když není velký rozdíl v tom, jak to funguje, když odkazujete na jiný list ve stejném souboru nebo odkazujete na zcela samostatný soubor aplikace Excel, formát tohoto odkazu se trochu změní.
Při odkazování na jiný list nebo jiné externí soubory je také třeba mít na paměti některé důležité věci.
Ale starosti … nic moc šíleného!
Až tento tutoriál dokončíte, budete vědět vše o odkazování na buňky a rozsahy v aplikaci Excel (být ve stejném sešitu nebo jiném sešitu).
Začněme!
Odkazování na buňku ve stejném listu
Toto je nejzákladnější úroveň odkazování, kde odkazujete na buňku na stejném listu.
Pokud jsem například v buňce B1 a chci odkazovat na buňku A1, formát by byl:
= A1
Když to uděláte, hodnota v buňce, kde použijete tento odkaz, bude stejná jako v buňce A1. A v případě, že provedete nějaké změny v buňce A1, projeví se tyto změny v buňce, kde jste použili tento odkaz.
Odkazování na buňku v jiném listu
Pokud musíte odkazovat na jiný list ve stejném sešitu, musíte použít níže uvedený formát:
Sheet_name! Cell_address
Nejprve máte název listu následovaný vykřičníkem, za nímž následuje odkaz na buňku.
Pokud tedy potřebujete odkazovat na buňku A1 v listu 1, musíte použít následující odkaz:
= List1! A1
A pokud chcete odkazovat na rozsah buněk v jiném listu, musíte použít následující formát:
Sheet_name! First_cell: Last_cell
Pokud tedy chcete odkazovat na rozsah A1: C10 na jiném listu ve stejném sešitu, musíte použít níže uvedený odkaz:
= List1! A1: C10
Všimněte si, že jsem vám ukázal pouze odkaz na buňku nebo rozsah. Ve skutečnosti byste je používali ve vzorcích. Formát výše uvedených odkazů však zůstane stejný
V mnoha případech by pracovní list, na který odkazujete, měl v názvu více slov. Například může být Data projektu nebo Údaje o prodeji.
V případě, že máte mezery nebo jiné než abecední znaky (například @,!, #, -atd.), Musíte jméno použít v jednoduchých uvozovkách.
Pokud například chcete odkazovat na buňku A1 v listu s názvem Údaje o prodeji, použijete následující odkaz:
= 'Údaje o prodeji'! A1
A v případě, že je název listu Sales-Data, pak pro odkaz na buňku A1 v tomto listu musíte použít níže uvedený odkaz:
= 'Údaje o prodeji'! A1
Když odkazujete na list ve stejném sešitu a později změníte název listu, nemusíte se obávat, že by se odkaz rozpadl. Excel tyto odkazy automaticky aktualizuje za vás.
I když je skvělé znát formát těchto odkazů, v praxi to není tak dobrý nápad pokaždé je ručně zadat. Bylo by to časově náročné a velmi náchylné k chybám.
Ukážu vám lepší způsob vytváření odkazů na buňky v Excelu.
Automatické vytváření odkazů na jiný list ve stejném sešitu
Mnohem lepší způsob, jak vytvořit odkaz na buňku na jiný list, je jednoduše nasměrovat Excel na buňku/rozsah, na který chcete vytvořit odkaz, a nechat jej Excel vytvořit sám.
Tím zajistíte, že se nebudete muset starat o to, že bude chybět vykřičník nebo uvozovky nebo že se objeví jiný problém s formátem. Excel pro vás automaticky vytvoří správnou referenci.
Níže jsou uvedeny kroky k automatickému vytvoření odkazu na jiný list:
- Vyberte buňku v aktuálním sešitu, kde potřebujete odkaz
- Zadejte vzorec, dokud nebudete potřebovat odkaz (nebo rovnítko k podpisu, pokud chcete pouze odkaz)
- Vyberte list, ke kterému se musíte vztahovat
- Vyberte buňku/rozsah, na který chcete odkazovat
- Stisknutím klávesy Enter získáte výsledek vzorce (nebo pokračujte v práci na vzorci)
Výše uvedené kroky by automaticky vytvořily odkaz na buňku/rozsah v jiném listu. Tyto odkazy také uvidíte na řádku vzorců. Jakmile budete hotovi, můžete jednoduše stisknout klávesu Enter a výsledek se zobrazí.
Pokud například máte nějaká data v buňce A1: A10 v listu s názvem Údaje o prodeji a chcete získat součet těchto hodnot v aktuálním listu, budou následovat tyto kroky:
- Do aktuálního listu (kde potřebujete výsledek) zadejte následující vzorec: = Součet (
- Vyberte list „Údaje o prodeji“.
- Vyberte rozsah, který chcete přidat (A1: A10). Jakmile to uděláte, Excel automaticky vytvoří odkaz na tento rozsah (můžete to vidět na řádku vzorců)
- Stiskněte klávesu Enter.
Když vytváříte dlouhý vzorec, možná budete muset odkázat na buňku nebo rozsah v jiném listu a pak se budete muset vrátit k původnímu listu a odkazovat tam na nějakou buňku/rozsah.
Když to uděláte, všimnete si, že Excel automaticky vloží odkaz na list na list, kde máte vzorec. I když je to v pořádku a neškodí, není to nutné. V takovém případě se můžete rozhodnout ponechat odkaz nebo jej odebrat ručně.
Další věc, kterou potřebujete vědět při vytváření odkazů výběrem listu a poté buňky/rozsahu, je to, že Excel vždy vytvoří soubor relativní odkaz (tj. reference se znakem n0 $). To znamená, že když zkopíruji a vložím vzorec (jeden s odkazem na jiný list) do nějaké jiné buňky, automaticky se upraví odkaz.
Zde je příklad, který vysvětluje relativní odkazy.
Předpokládejme, že v buňce A1 v aktuálním listu použiji následující vzorec (pro odkaz na buňku A1 v názvu listu SalesData)
= Údaje o prodeji! A1
Pokud zkopíruji tento vzorec a vložím jej do buňky A2, vzorec se změní na:
= Údaje o prodeji! A1
K tomu dochází, protože vzorec je relativní a když jej zkopíruji a vložím, odkazy se automaticky upraví.
V případě, že chci, aby tento odkaz vždy odkazoval na buňku A1 v listu SalesData, budu muset použít níže uvedený vzorec:
= Údaje o prodeji! $ A $ 1
Znak dolaru před číslem řádku a sloupce tyto odkazy uzamkne, aby se nezměnily.
Zde je podrobný návod, kde se můžete dozvědět více o absolutních, smíšených a relativních referencích.
Nyní, když jsme se zabývali odkazem na jiný list ve stejném sešitu, podívejme se, jak můžeme odkazovat na jiný sešit.
Jak odkazovat na jiný sešit v aplikaci Excel
Když odkazujete na buňku nebo rozsah na jiný sešit aplikace Excel, formát této reference bude záviset na tom, zda je tento sešit otevřený nebo zavřený.
A roli při určování formátu samozřejmě hraje i název sešitu a pracovních listů (podle toho, zda máte v názvu mezerní nebo neabecední znaky nebo ne).
Podívejme se tedy na různé formáty externích odkazů na jiný sešit v různých scénářích.
Externí odkaz na otevřený sešit
Pokud jde o odkaz na externí otevřený sešit, musíte zadat název sešitu, název listu a adresu buňky/rozsahu.
Níže je uveden formát, který musíte použít při odkazování na externí otevřený sešit
= '[Název_souboru] SheetName! CellAddress
Předpokládejme, že máte sešit ‘ExampleFile.xlsx’ a chcete odkázat na buňku A1 v listu 1 tohoto sešitu.
Níže je odkaz na toto:
= [ExampleFile.xlsx] SalesData! A1
V případě, že v názvu externího sešitu nebo názvu listu (nebo obou) jsou mezery, musíte přidat název souboru (do hranatých závorek) a název listu do jednoduchých uvozovek.
Níže jsou uvedeny příklady, kde byste potřebovali mít jména v jednoduchých uvozovkách:
= '[Příklad souboru.xlsx] Údaje o prodeji'! A1 = '[PříkladFile.xlsx] Údaje o prodeji'! A1 = '[Příklad souboru.xlsx] Údaje o prodeji'! A1
Jak vytvořit odkaz na jiný sešit (automaticky)
I když je dobré znát formát, je nejlepší jej nezadávat ručně.
Místo toho namiřte Excel správným směrem a vytvoří vám tyto odkazy. To je mnohem rychlejší s menší pravděpodobností chyb.
Pokud například máte v buňce A1: A10 nějaká data v sešitu s názvem 'Příklad souboru' v listu s názvem 'Údaje o prodeji' a chcete získat součet těchto hodnot v aktuálním listu, budou následovat následující kroky :
- Do aktuálního listu (kde potřebujete výsledek) zadejte následující vzorec: = Součet (
- Přejděte do sešitu „Příklad souboru“
- Vyberte list „Údaje o prodeji“.
- Vyberte rozsah, který chcete přidat (A1: A10). Jakmile to uděláte, Excel automaticky vytvoří odkaz na tento rozsah (můžete to vidět na řádku vzorců)
- Stiskněte klávesu Enter.
To by okamžitě vytvořilo vzorec se správnými odkazy.
Jedna věc, které byste si při vytváření odkazu na externí sešit všimli, je, že vždy vytvoří absolutní odkazy. To znamená, že před čísly řádků a sloupců je znak $. To znamená, že pokud tento vzorec zkopírujete a vložíte do jiných buněk, bude kvůli absolutnímu odkazu odkazovat stále na stejný rozsah.
V případě, že to chcete změnit, musíte změnit odkazy ručně.
Externí odkaz na uzavřený sešit
Když je otevřený externí sešit a odkazujete na tento sešit, stačí zadat název souboru, název listu a adresu buňky/rozsahu.
Ale když je toto zavřené, Excel nemá ponětí, kde hledat buňky/rozsah, na který jste odkazovali.
To je důvod, proč při vytváření odkazu na uzavřený sešit musíte také zadat cestu k souboru.
Níže je odkaz, který odkazuje na buňku A1 v listu List1 v sešitu Příklad souboru. Protože tento soubor není otevřený, odkazuje také na umístění, kde je soubor uložen.
= 'C: \ Users \ sumit \ Desktop \ [Příklad souboru.xlsx] List1'! $ A $ 1
Výše uvedený odkaz má následující části:
- Cesta k souboru - umístění ve vašem systému nebo síti, kde je umístěn externí soubor
- Název souboru - název externího sešitu. To by také zahrnovalo příponu souboru.
- Název listu - název listu, ve kterém odkazujete na buňky/rozsahy
- Adresa buňky/rozsahu - přesná adresa buňky/rozsahu, na kterou odkazujete
Když vytvoříte externí odkaz na otevřený sešit a pak sešit zavřete, všimnete si, že se odkaz automaticky změní. Po zavření externího sešitu aplikace Excel automaticky vloží také odkaz na cestu k souboru.
Dopad změny umístění souboru na reference
Když vytvoříte odkaz na buňku/rozsah v externím souboru aplikace Excel a poté jej zavřete, odkaz nyní použije také cestu k souboru.
Pokud však změníte umístění souboru, ve vašem sešitu (ve kterém vytvoříte odkaz) se nic nezmění. Ale protože jste změnili umístění, odkaz byl nyní přerušen.
Pokud tedy tento sešit zavřete a otevřete, řekne vám, že je odkaz nefunkční a je třeba odkaz buď aktualizovat, nebo ho úplně přerušit. Zobrazí se výzva, jak je uvedeno níže:
Když kliknete na Aktualizovat, zobrazí se vám další výzva, kde můžete zvolit možnosti úpravy odkazů (což vám zobrazí níže uvedené dialogové okno)
Pokud potřebujete zachovat propojení těchto souborů, můžete zadat nové umístění souboru kliknutím na Aktualizovat hodnoty. Excel vám otevře dialogové okno, kde můžete zadat nové umístění souboru tak, že tam přejdete a vyberete ho.
Odkaz na definovaný název (ve stejném nebo externím sešitu)
Když musíte odkazovat na buňky a rozsahy, je lepší vytvořit definovaná jména pro rozsahy.
To je užitečné, protože vám usnadňuje odkazovat na tyto rozsahy pomocí názvu místo dlouhé a komplikované referenční adresy.
Jednodušší je například použít = SalesData místo = [Příklad souboru.xlsx] List1 ′! $ A $ 1: $ A $ 10
A v případě, že jste použili tento definovaný pojmenovaný ve více vzorcích a potřebujete změnit odkaz, stačí to udělat jen jednou.
Zde jsou kroky k vytvoření pojmenovaného rozsahu pro rozsah buněk:
- Vyberte všechny buňky, které chcete zahrnout do pojmenovaného rozsahu
- Klikněte na kartu Vzorce
- Klikněte na možnost Definovat jméno (je ve skupině Definovaná jména)
- V dialogovém okně Nový název pojmenujte tento rozsah (v tomto příkladu používám název SalesData). Pamatujte, že ve jménu nesmí být mezery
- Ponechejte rozsah jako sešit (pokud nemáte vážný důvod, aby byl na úrovni listu)
- Ujistěte se, že odkazy na rozsah jsou správné.
- Klikněte na OK.
Nyní byl vytvořen váš pojmenovaný rozsah a můžete jej použít místo odkazů na buňky s adresami buněk.
Pokud například chci získat součet všech těchto buněk v rozsahu SalesData, můžete použít níže uvedený vzorec:
= SUM (prodejní data)
A co když chcete použít tento pojmenovaný rozsah, jsou jiné pracovní listy nebo dokonce jiné sešity?
Můžeš!
Stačí dodržet stejný formát, o kterém jsme diskutovali ve výše uvedené části.
Není třeba se vracet na začátek tohoto článku. Dovolte mi, abych vám zde uvedl všechny příklady, abyste získali představu.
Sešit Vs Pracovní list Úroveň pojmenovaných rozsahů: Když vytvoříte sešit na úrovni pojmenovaného rozsahu, může existovat pouze jeden rozsah s tímto názvem. Pokud na něj tedy odkazujete z externích sešitů nebo listů ve stejném sešitu, není nutné zadávat název listu (protože je k dispozici pro použití v celém sešitu). V případě, že používáte úroveň listu s názvem rozsah, budete ji moci použít pouze v listu a při odkazování na ni budete muset také zadat název listu.Odkazování na definovaný název ve stejném listu nebo sešitu
Pokud jste vytvořili definovaný název pro úroveň sešitu, můžete jej použít kdekoli v sešitu pouhým použitím definovaného názvu samotného.
Pokud například chci získat součet všech buněk v pojmenovaném rozsahu, který jsme vytvořili (SaledData), mohu použít níže uvedený vzorec:
= SUM (SaledData)
V případě, že jste na úrovni listu vytvořili pojmenovaný rozsah, můžete tento vzorec použít, pouze pokud je pojmenovaný rozsah vytvořen ve stejném listu, kde používáte vzorec.
V případě, že ho chcete použít na jiném listu (řekněme List2), musíte použít následující vzorec:
= SUM (List1! $ A $ 1: $ A $ 10)
A v případě, že jsou v názvu listu mezery nebo alfanumerické znaky, budete muset název listu vložit do jednoduchých uvozovek.
= SUM ('List 1'! $ A $ 1: $ A $ 10)
Odkazování na definovaný název v jiném sešitu (otevřeném nebo zavřeném)
Pokud chcete odkazovat na pojmenovaný rozsah v jiném sešitu, budete muset zadat název sešitu a poté název rozsahu.
Pokud máte například sešit aplikace Excel s názvem ExampleFile.xlsx a pojmenovaný rozsah s názvem SalesData, můžete pomocí vzorce uvedeného níže získat součet tohoto rozsahu z jiného sešitu:
= SUM (ExampleFile.xlsx! SalesData)
V případě, že v názvu souboru jsou mezery, musíte je použít v jednoduchých uvozovkách.
= SUM ('Příklad souboru.xlsx'! SalesData)
Pokud máte pojmenované rozsahy na úrovni listu, musíte při odkazování na něj z externího sešitu zadat název sešitu i list.
Níže je uveden příklad odkazování na pojmenovaný rozsah na úrovni listu:
= SUM ('[Příklad souboru.xlsx] List1'! Data o prodeji)
Jak jsem již také zmínil výše, je vždy nejlepší vytvořit pojmenované rozsahy na úrovni sešitu, pokud nemáte pádný důvod vytvořit úroveň prvního listu.
V případě, že odkazujete na pojmenovaný rozsah v uzavřeném sešitu, budete muset zadat také cestu k souboru. Níže je uveden příklad:
= SUM ('C: \ Users \ sumit \ Desktop \ Example File.xlsx'! SalesData)
Když vytvoříte odkaz na pojmenovaný rozsah v otevřeném sešitu a pak sešit zavřete, Excel odkaz automaticky změní a přidá cestu k souboru.
Jak vytvořit odkaz na pojmenovaný rozsah
Pokud vytváříte a pracujete se spoustou pojmenovaných rozsahů, není možné si pamatovat jméno každého z nich.
Excel vám pomůže tím, že vám ukáže seznam všech pojmenovaných rozsahů, které jste vytvořili, a umožní vám je vložit do vzorců jediným kliknutím.
Předpokládejme, že jste vytvořili pojmenovaný rozsah SalesData, který chcete použít ve vzorci SUMA všech hodnot v pojmenovaném rozsahu.
Zde jsou kroky, jak toho dosáhnout:
- Vyberte buňku, do které chcete vzorec zadat.
- Zadejte vzorec do bodu, kde potřebujete vložit pojmenovaný rozsah
- Stiskněte klávesu F3 na klávesnici. Otevře se dialogové okno Vložit název se seznamem všech jmen, která jste vytvořili
- Poklepejte na jméno, které chcete vložit.
Výše uvedené kroky by vložily název do vzorce a na formuláři můžete pokračovat v práci.
Poznámka: Tato technika funguje pouze pro rozsahy jmen, které jsou ve stejném sešitu. Pokud chcete získat názvy v externím sešitu, budete tam muset jít, zkontrolovat název pomocí dialogového okna Vložit název a poté se vrátit a použít jej ve vzorci (ve správném formátu pomocí názvu souboru).To je vše, co potřebujete vědět o tom, jak odkazovat na jiné listy nebo sešity a jak vytvořit externí odkaz v aplikaci Excel.
Doufám, že jste našli tento návod užitečný.
