Schopnosť odkazovať na bunky a rozsahy je to, čo robí každý tabuľkový nástroj. A Excel je najlepší a najvýkonnejší.
V tomto návode sa budem zaoberať všetkým, čo potrebujete vedieť o tom, ako odkazovať na bunky a rozsahy v programe Excel. Okrem základného odkazovania na ten istý list by bola aj hlavná časť tohto tutoriálu ako odkazovať na iný list alebo zošit v programe Excel.
Aj keď nie je veľký rozdiel v tom, ako to funguje, keď odkazujete na iný hárok v tom istom súbore alebo odkazujete na úplne samostatný súbor programu Excel, formát tohto odkazu sa trochu zmení.
Pri odkazovaní na iný list alebo iné externé súbory je tiež potrebné mať na pamäti niekoľko dôležitých vecí.
Ale starosti … nič príliš šialené!
Keď skončíte s týmto návodom, budete vedieť všetko o odkazovaní na bunky a rozsahy v programe Excel (byť v rovnakom zošite alebo inom zošite).
Začnime!
Odkazovanie na bunku v rovnakom hárku
Toto je najzákladnejšia úroveň odkazovania, kde odkazujete na bunku na rovnakom hárku.
Ak som napríklad v bunke B1 a chcem odkazovať na bunku A1, formát by bol:
= A1
Keď to urobíte, hodnota v bunke, kde použijete tento odkaz, bude rovnaká ako v bunke A1. A ak vykonáte akékoľvek zmeny v bunke A1, prejavia sa tieto zmeny v bunke, v ktorej ste použili tento odkaz.
Odkazovanie na bunku v inom liste
Ak musíte odkazovať na iný list v tom istom zošite, musíte použiť nasledujúci formát:
Sheet_name! Cell_address
Najprv musíte zadať názov hárka a za ním výkričník, za ktorým bude odkaz na bunku.
Ak teda potrebujete odkázať na bunku A1 v hárku 1, musíte použiť nasledujúci odkaz:
= List1! A1
A ak chcete odkazovať na rozsah buniek v inom hárku, musíte použiť nasledujúci formát:
Sheet_name! First_cell: Last_cell
Ak teda chcete odkazovať na rozsah A1: C10 v inom liste v tom istom zošite, musíte použiť nižšie uvedený odkaz:
= List1! A1: C10
Všimnite si toho, že som vám ukázal iba odkaz na bunku alebo rozsah. V skutočnosti by ste ich používali vo vzorcoch. Formát vyššie uvedených odkazov však zostane rovnaký
V mnohých prípadoch má pracovný list, na ktorý sa odvolávate, v názve viac slov. Napríklad to môže byť Údaje o projekte alebo Údaje o predaji.
V prípade, že máte medzery alebo iné ako abecedné znaky (napríklad @,!, #, -atď.), Musíte meno použiť v jednoduchých úvodzovkách.
Ak napríklad chcete odkázať na bunku A1 v hárku s názvom Údaje o predaji, použijete nasledujúci odkaz:
= 'Údaje o predaji'! A1
A v prípade, že je názov listu predajné údaje, na odkaz na bunku A1 v tomto hárku musíte použiť nižšie uvedený odkaz:
= 'Údaje o predaji'! A1
Keď odkážete na list v tom istom zošite a neskôr zmeníte názov pracovného hárka, nemusíte sa obávať, že sa odkaz rozpadne. Excel tieto odkazy za vás automaticky aktualizuje.
Aj keď je skvelé poznať formát týchto referencií, v praxi nie je dobré písať ich zakaždým ručne. Bolo by to časovo náročné a veľmi náchylné na chyby.
Ukážem vám lepší spôsob vytvárania odkazov na bunky v programe Excel.
Automatické vytváranie odkazov na iný list v tom istom zošite
Oveľa lepší spôsob, ako vytvoriť odkaz na bunku na iný hárok, je jednoducho nasmerovať Excel na bunku/rozsah, na ktorý chcete vytvoriť odkaz, a nechať ho, aby ho Excel sám vytvoril.
To zaistí, že sa nebudete musieť starať o to, že bude chýbať výkričník alebo úvodzovky, alebo že sa vyskytne problém s iným formátom. Excel pre vás automaticky vytvorí správnu referenciu.
Nasledujú kroky na automatické vytvorenie odkazu na iný list:
- Vyberte bunku v aktuálnom zošite, kde potrebujete referenciu
- Zadajte vzorec, kým nebudete potrebovať referenciu (alebo znamienko rovnosti, ak chcete iba referenciu)
- Vyberte hárok, na ktorý sa chcete obrátiť
- Vyberte bunku/rozsah, na ktoré chcete odkazovať
- Stlačením klávesu Enter získate výsledok vzorca (alebo pokračujte v práci na vzorci)
Vyššie uvedené kroky by automaticky vytvorili odkaz na bunku/rozsah v inom hárku. Tieto odkazy budete môcť vidieť aj na paneli vzorcov. Keď skončíte, stačí stlačiť kláves Enter a výsledok sa zobrazí.
Ak máte napríklad nejaké údaje v bunke A1: A10 v hárku s názvom Údaje o predaji a chcete získať súčet týchto hodnôt v aktuálnom hárku, postupujte takto:
- Do aktuálneho hárka (kde potrebujete výsledok) zadajte nasledujúci vzorec: = Suma (
- Vyberte list „Údaje o predaji“.
- Vyberte rozsah, ktorý chcete pridať (A1: A10). Hneď ako to urobíte, Excel automaticky vytvorí odkaz na tento rozsah (vidíte to na paneli vzorcov)
- Stlačte kláves Enter.
Pri vytváraní dlhého vzorca možno budete musieť odkázať na bunku alebo rozsah v inom hárku a potom sa musíte vrátiť k pôvodnému hárku a odkazovať tam na nejakú bunku/rozsah.
Keď to urobíte, všimnete si, že Excel automaticky vloží odkaz na hárok na hárok, kde máte vzorec. Aj keď je to v poriadku a nepoškodzuje to, nie je to potrebné. V takom prípade sa môžete rozhodnúť ponechať referenciu alebo ju manuálne odstrániť.
Ďalšia vec, ktorú musíte vedieť pri vytváraní referencií výberom listu a potom bunky/rozsahu je, že Excel vždy vytvorí súbor relatívna referencia (t.j. referencie so znakom n0 $). To znamená, že ak skopírujem a prilepím vzorec (jeden s odkazom na iný hárok) do inej bunky, odkaz sa automaticky upraví.
Tu je príklad, ktorý vysvetľuje relatívne odkazy.
Predpokladajme, že v bunke A1 v aktuálnom hárku použijem nasledujúci vzorec (na odkaz na bunku A1 v názve listu SalesData)
= Údaje o predaji! A1
Teraz, keď skopírujem tento vzorec a prilepím ho do bunky A2, vzorec sa zmení na:
= Údaje o predaji! A1
Stáva sa to preto, že vzorec je relatívny a keď ho skopírujem a prilepím, odkazy sa automaticky upravia.
V prípade, že chcem, aby sa tento odkaz vždy vzťahoval na bunku A1 v hárku SalesData, budem musieť použiť nasledujúci vzorec:
= Údaje o predaji! $ A $ 1
Znak dolára pred číslom riadka a stĺpca tieto odkazy uzamkne, aby sa nezmenili.
Tu je podrobný návod, kde sa môžete dozvedieť viac o absolútnych, zmiešaných a relatívnych referenciách.
Teraz, keď sme sa zaoberali tým, ako odkazovať na iný hárok v tom istom zošite, pozrime sa, ako sa môžeme odvolávať na iný zošit.
Ako odkazovať na iný zošit v programe Excel
Keď odkazujete na bunku alebo rozsah na iný zošit programu Excel, formát tohto odkazu bude závisieť od toho, či je tento zošit otvorený alebo zatvorený.
A samozrejme, názov pracovného zošita a pracovných listov tiež zohráva úlohu pri určovaní formátu (v závislosti od toho, či máte v názve medzery alebo neabecedné znaky alebo nie).
Pozrime sa teda na rôzne formáty externých odkazov na iný zošit v rôznych scenároch.
Externý odkaz na otvorený zošit
Pokiaľ ide o odkaz na externý otvorený zošit, musíte zadať názov zošita, názov pracovného hárka a adresu bunky/rozsahu.
Nasleduje formát, ktorý musíte použiť pri odkazovaní na externý otvorený zošit
= '[FileName] SheetName! CellAddress
Predpokladajme, že máte zošit „ExampleFile.xlsx“ a chcete odkázať na bunku A1 v hárku 1 tohto zošita.
Nasleduje odkaz na to:
= [ExampleFile.xlsx] Údaje o predaji! A1
V prípade, že v názve externého zošita alebo v názve listu (alebo v oboch) sú medzery, musíte pridať názov súboru (do hranatých zátvoriek) a názov listu do jednoduchých úvodzoviek.
Nasledujú príklady, kde by ste museli mať mená v jednoduchých úvodzovkách:
= '[Príklad súboru.xlsx] Údaje o predaji'! A1 = '[PríkladFile.xlsx] Údaje o predaji'! A1 = '[Príklad súboru.xlsx] Údaje o predaji'! A1
Ako vytvoriť odkaz na iný zošit (automaticky)
Opäť platí, že aj keď je dobré poznať formát, je najlepšie nezadávať ho ručne.
Namiesto toho ukážte Excel správnym smerom a on vám vytvorí tieto referencie. Je to oveľa rýchlejšie s menšou pravdepodobnosťou chýb.
Ak napríklad máte v bunke A1: A10 nejaké údaje v zošite s názvom „Príklad súboru“ v hárku s názvom „Údaje o predaji“ a chcete získať súčet týchto hodnôt v aktuálnom hárku, postupujte nasledovne :
- Do aktuálneho hárka (kde potrebujete výsledok) zadajte nasledujúci vzorec: = Suma (
- Prejdite do zošita „Príklad súboru“
- Vyberte list „Údaje o predaji“.
- Vyberte rozsah, ktorý chcete pridať (A1: A10). Hneď ako to urobíte, Excel automaticky vytvorí odkaz na tento rozsah (vidíte to na paneli vzorcov)
- Stlačte kláves Enter.
To by okamžite vytvorilo vzorec so správnymi odkazmi.
Jedna vec, ktorú by ste si pri vytváraní odkazu na externý zošit všimli, je, že vždy vytvorí absolútne referencie. To znamená, že pred číslami riadkov a stĺpcov je znak $. To znamená, že ak skopírujete a prilepíte tento vzorec do iných buniek, bude kvôli absolútnej referencii odkazovať stále na rovnaký rozsah.
Ak chcete, aby sa to zmenilo, musíte zmeniť referencie ručne.
Externý odkaz na uzavretý zošit
Keď je otvorený externý zošit a vy sa naň odvolávate, stačí zadať názov súboru, názov hárka a adresu bunky/rozsahu.
Keď je to však zatvorené, Excel nemá potuchy, kde hľadať bunky/rozsah, o ktorých ste hovorili.
Preto pri vytváraní odkazu na zatvorený zošit musíte tiež zadať cestu k súboru.
Nasleduje odkaz, ktorý odkazuje na bunku A1 v pracovnom hárku List1 v zošite Príklad súboru. Pretože tento súbor nie je otvorený, odkazuje tiež na umiestnenie, kde je súbor uložený.
= 'C: \ Users \ sumit \ Desktop \ [Príklad File.xlsx] list1'! $ A $ 1
Vyššie uvedený odkaz má nasledujúce časti:
- Cesta k súboru - umiestnenie vo vašom systéme alebo sieti, kde sa nachádza externý súbor
- Názov súboru - názov externého zošita. To by zahŕňalo aj príponu súboru.
- Názov hárka - názov hárka, v ktorom odkazujete na bunky/rozsahy
- Adresa bunky/rozsahu - presná adresa bunky/rozsahu, na ktorú odkazujete
Keď vytvoríte externú referenciu na otvorený zošit a potom zošit zavriete, všimnete si, že sa referencia automaticky zmení. Po zatvorení externého zošita program Excel automaticky vloží aj odkaz na cestu k súboru.
Vplyv zmeny umiestnenia súboru na referencie
Keď vytvoríte odkaz na bunku/rozsah v externom súbore programu Excel a potom ho zatvoríte, odkaz teraz použije aj cestu k súboru.
Ale potom, ak zmeníte umiestnenie súboru, nič sa nezmení vo vašom zošite (v ktorom vytvoríte referenciu). Keďže ste však zmenili miesto, odkaz je teraz nefunkčný.
Ak teda zatvoríte a otvoríte tento zošit, oznámi vám, že odkaz je nefunkčný a musíte ho buď aktualizovať, alebo ho úplne prerušiť. Zobrazí sa výzva, ako je uvedené nižšie:
Keď kliknete na Aktualizovať, zobrazí sa vám ďalšia výzva, v ktorej môžete vybrať možnosti úpravy odkazov (čo vám zobrazí nižšie uvedené dialógové okno)
Ak potrebujete zachovať prepojenie týchto súborov, môžete určiť nové umiestnenie súboru kliknutím na položku Aktualizovať hodnoty. Excel vám otvorí dialógové okno, v ktorom môžete zadať nové umiestnenie súboru tak, že tam prejdete a vyberiete ho.
Odkaz na definovaný názov (v rovnakom alebo externom zošite)
Keď sa musíte odvolávať na bunky a rozsahy, je lepšie vytvoriť definované názvy pre rozsahy.
Je to užitočné, pretože vám uľahčuje odkazovať na tieto rozsahy pomocou názvu namiesto dlhej a komplikovanej referenčnej adresy.
Je napríklad jednoduchšie použiť = SalesData namiesto = [Príklad File.xlsx] List1 ′! $ A $ 1: $ A $ 10
A v prípade, že ste použili tento definovaný názov vo viacerých vzorcoch a potrebujete zmeniť referenciu, stačí to urobiť iba raz.
Tu sú kroky na vytvorenie pomenovaného rozsahu pre rozsah buniek:
- Vyberte všetky bunky, ktoré chcete zahrnúť do pomenovaného rozsahu
- Kliknite na kartu Vzorce
- Kliknite na možnosť Definovať meno (je v skupine Definované mená)
- V dialógovom okne Nový názov pomenujte tento rozsah (v tomto prípade používam názov SalesData). Pamätajte si, že v názve nemôžu byť medzery
- Ponechajte rozsah ako zošit (pokiaľ nemáte vážny dôvod na to, aby bol na úrovni hárka)
- Uistite sa, že odkazy na rozsah sú správne.
- Kliknite na tlačidlo OK.
Teraz bol vytvorený váš pomenovaný rozsah a môžete ho použiť namiesto odkazov na bunky s adresami buniek.
Ak napríklad chcem získať súčet všetkých týchto buniek v rozsahu SalesData, môžete použiť nasledujúci vzorec:
= SUM (údaje o predaji)
A čo keď chcete použiť tento pomenovaný rozsah, sú iné pracovné hárky alebo dokonca iné pracovné zošity?
Môžeš!
Musíte len dodržať rovnaký formát, o ktorom sme diskutovali vo vyššie uvedenej časti.
Nie je potrebné vracať sa na začiatok tohto článku. Dovoľte mi, aby som vám tu uviedol všetky príklady, aby ste získali predstavu.
Zošit vs. Pracovný list Úroveň pomenovaných rozsahov: Keď vytvoríte zošit na úrovni s názvom rozsah, môže existovať iba jeden rozsah s týmto názvom. Ak sa teda na to odvolávate z externých zošitov alebo pracovných hárkov v tom istom zošite, nemusíte zadávať názov pracovného hárka (pretože je k dispozícii na použitie v celom zošite). V prípade, že použijete úroveň pracovného hárka s názvom rozsah, budete ju môcť použiť iba v pracovnom hárku a pri odkazovaní na ňu budete musieť zadať aj názov pracovného hárka.Odkaz na definovaný názov v rovnakom pracovnom hárku alebo zošite
Ak ste vytvorili definovaný názov pre úroveň zošita, môžete ho použiť kdekoľvek v zošite iba pomocou samotného definovaného názvu.
Napríklad, ak chcem získať súčet všetkých buniek v pomenovanom rozsahu, ktorý sme vytvorili (SaledData), môžem použiť nasledujúci vzorec:
= SUM (SaledData)
V prípade, že ste na úrovni pracovného hárka vytvorili pomenovaný rozsah, môžete tento vzorec použiť iba vtedy, ak je pomenovaný rozsah vytvorený v rovnakom hárku, kde používate vzorec.
V prípade, že ho chcete použiť na inom hárku (povedzme Hárok2), musíte použiť nasledujúci vzorec:
= SUM (List1! $ A $ 1: $ A $ 10)
A v prípade, že v názve listu sú medzery alebo alfanumerické znaky, budete musieť názov listu vložiť do jednoduchých úvodzoviek.
= SUM ('List 1'! $ A $ 1: $ A $ 10)
Odkazovanie na definovaný názov v inom zošite (otvorenom alebo zatvorenom)
Ak chcete odkazovať na pomenovaný rozsah v inom zošite, budete musieť zadať názov zošita a potom názov rozsahu.
Ak máte napríklad zošit programu Excel s názvom ExampleFile.xlsx a pomenovaný rozsah s názvom SalesData, potom pomocou nižšie uvedeného vzorca môžete získať súčet tohto rozsahu z iného zošita:
= SUM (ExampleFile.xlsx! SalesData)
V prípade, že v názve súboru sú medzery, musíte ich použiť v jednoduchých úvodzovkách.
= SUM („Príklad súboru.xlsx“! Údaje o predaji)
V prípade, že máte pomenované rozsahy na úrovni hárku, musíte pri odkazovaní naň z externého zošita zadať názov zošita a tiež pracovný hárok.
Nasleduje príklad odkazovania na pomenovaný rozsah na úrovni listu:
= SUM ('[Príklad súboru.xlsx] List1'! Údaje o predaji)
Ako som už spomenul aj vyššie, vždy je najlepšie vytvoriť pomenované rozsahy na úrovni zošita, pokiaľ nemáte vážny dôvod na vytvorenie prvej úrovne pracovného hárka.
V prípade, že odkazujete na pomenovaný rozsah v uzavretom zošite, budete musieť zadať aj cestu k súboru. Nasleduje príklad tohto:
= SUM ('C: \ Users \ sumit \ Desktop \ Example File.xlsx'! SalesData)
Keď vytvoríte odkaz na pomenovaný rozsah v otvorenom zošite a potom zošit zavriete, Excel odkaz automaticky zmení a pridá cestu k súboru.
Ako vytvoriť odkaz na pomenovaný rozsah
Ak vytvárate a pracujete s veľkým počtom pomenovaných rozsahov, nie je možné zapamätať si každé z nich.
Excel vám pomôže tým, že vám ukáže zoznam všetkých pomenovaných rozsahov, ktoré ste vytvorili, a umožní vám ich vložiť do vzorcov jediným kliknutím.
Predpokladajme, že ste vytvorili pomenovaný rozsah SalesData, ktorý chcete použiť vo vzorci na SÚČET všetkých hodnôt v pomenovanom rozsahu.
Tu sú kroky, ako to urobiť:
- Vyberte bunku, do ktorej chcete zadať vzorec.
- Zadajte vzorec do bodu, do ktorého chcete vložiť pomenovaný rozsah
- Na klávesnici stlačte kláves F3. Otvorí sa dialógové okno Vložiť názov so zoznamom všetkých mien, ktoré ste vytvorili
- Dvakrát kliknite na meno, ktoré chcete vložiť.
Vyššie uvedené kroky by vložili názov do vzorca a môžete pokračovať v práci na vzorci.
Poznámka: Táto technika funguje iba pre rozsahy názvov, ktoré sú v rovnakom zošite. Ak chcete získať mená v externom zošite, budete musieť ísť tam, skontrolovať meno pomocou dialógového okna Vložiť meno a potom sa vrátiť a použiť ho vo svojom vzorci (v správnom formáte s použitím názvu súboru).To je všetko, čo potrebujete vedieť o tom, ako odkazovať na iné listy alebo zošity a ako vytvoriť externú referenciu v programe Excel.
Dúfam, že ste našli tento návod užitočný.
