Aby ste mohli vykonávať presné výpočty v programe Excel, je dôležité porozumieť tomu, ako fungujú rôzne typy odkazov na bunky.
Referencie A1 vs. R1C1
Pracovné listy programu Excel obsahujú veľa buniek a (predvolene) každá bunka je identifikovaná svojou bunkou stĺpcové písmeno nasleduje číslo jeho riadku. Toto je známe ako odkazovanie v štýle A1. Príklady: A1, B4, C6
Referenčný štýl A1
Voliteľne môžete prepnúť na Referenčný režim R1C1 odkazovať na riadok bunky & číslo stĺpca. Namiesto odkazu na bunku A1 by ste odkazovali na R1C1 (riadok 1, stĺpec 1). Bunka C4 by bola označená ako R4C3.
Referenčný štýl R1C1
Odkazovanie v štýle R1C1 je v programe Excel veľmi neobvyklé. Pokiaľ nemáte dobrý dôvod, mali by ste sa pravdepodobne držať predvoleného referenčného režimu v štýle A1. Ak však používate VBA, pravdepodobne sa s týmto referenčným štýlom stretnete.
Prepnite na referenčný štýl R1C1
Ak chcete zmeniť referenčný štýl, prejdite na Súbor> Voľba> Vzorec. Začiarknite políčko vedľa Referenčný štýl R1C1.
Pomenované rozsahy
Jednou z najmenej využívaných funkcií programu Excel je funkcia Pomenované rozsahy. Namiesto odkazovania na bunku (alebo skupinu buniek) podľa umiestnenia bunky (napr. B3 alebo R3C2) môžete tento rozsah pomenovať a vo vzorcoch jednoducho odkazovať na názov rozsahu.
Ak chcete pomenovať rozsah:
- Vyberte bunku alebo bunky, ktoré chcete pomenovať
- Kliknite do poľa Názov poľa
- Zadajte požadované meno
- Stlačte Enter
Teraz môžete odkazovať na bunku A1 zadaním = range_name1. Je to veľmi užitočné pri práci s veľkými zošitmi s viacerými pracovnými listami.
Rozsah buniek
Pri použití vstavaných funkcií programu Excel bude možno potrebné odkazovať na rozsah buniek. Bunkové rozsahy vyzerajú takto „A1: B3“. Tento odkaz sa týka všetkých buniek medzi A1 a B3: buniek A1, A2, A3, B1, B2, B3.
Ak chcete pri zadávaní vzorca vybrať rozsah buniek:
- Zadajte rozsah (počiatočný a koncový rozsah oddeľte bodkočiarkou)
- Kliknite myšou na odkaz na prvú bunku, podržte tlačidlo myši a presuňte požadovaný rozsah.
- Podržte kláves Shift a pomocou klávesov so šípkami vyberte svoj rozsah
Absolútne (mrazené) a relatívne odkazy
Pri zadávaní odkazov na bunky vo vzorcoch môžete použiť relatívne alebo absolútne (zmrazené) odkazy. Keď skopírujete vzorec do novej bunky, relatívne odkazy sa budú proporcionálne pohybovať. Absolútne referencie zostanú nezmenené. Pozrime sa na niekoľko príkladov:
Relatívna referencia
Relatívna referencia v programe Excel vyzerá takto
= A1
Keď skopírujete a prilepíte vzorec s relatívnymi odkazmi, relatívne odkazy sa budú pohybovať proporcionálne. Pokiaľ neurčíte inak, odkazy na bunky budú v predvolenom nastavení relatívne (nezmrazené).
Príklad: Ak skopírujete „= A1“ do jedného riadka, odkaz sa zmení na „= A2“.
Absolútne (mrazené) bunkové referencie
Ak nechcete, aby sa vaše odkazy na bunky pri kopírovaní vzorca presúvali, môžete „zmraziť“ referencie na bunky pridaním znakov dolára ($ s) pred referenciu, ktorú chcete zmraziť. Keď teraz skopírujete a prilepíte vzorec, odkaz na bunku zostane nezmenený. Môžete sa rozhodnúť zmraziť odkaz na riadok, odkaz na stĺpec alebo oboje.
A1: Nič nie je zmrazené
$ A1: Stĺpec je zmrazený, ale riadok nie je zmrazený
A $ 1: Riadok je zmrazený, ale stĺpec nie je zmrazený
$ A $ 1: Riadok aj stĺpec sú zmrazené
Skratka pre absolútnu referenciu
Ručné pridávanie znakov dolára ($ s) do vzorcov nie je príliš praktické. Pri vytváraní vzorca namiesto toho pomocou klávesu F4 prepínajte medzi absolútnymi/relatívnymi odkazmi na bunky.
Príklad absolútnej referenčnej bunky
Kedy by ste vlastne potrebovali zmraziť odkaz na bunku? Bežným príkladom je prípad, keď máte vstupné bunky, na ktoré sa často odkazuje. V nižšie uvedenom príklade chceme vypočítať daň z obratu pre každé množstvo položiek ponuky. Daň z obratu je vo všetkých položkách konštantná, preto budeme bunku dane z obratu uvádzať opakovane.
Ak chcete zistiť celkovú daň z obratu, zadajte vzorec „= (B3*C3)*$ C $ 1“ do stĺpca D a skopírujte vzorec nadol.
Zmiešaná referencia
Možno ste už počuli o odkazoch na zmiešané bunky. Zmiešaný odkaz je, keď je zamknutý odkaz na riadok alebo stĺpec (ale nie na oba).
Zmiešaná referencia
Nezabudnite, že pomocou klávesu „F4“ môžete cyklicky prechádzať svojimi relatívnymi, absolútnymi odkazmi na bunky.
Referencie na bunky - vkladanie a odstraňovanie riadkov/stĺpcov
Možno vás zaujíma, čo sa stane s vašimi odkazmi na bunky pri vkladaní alebo odstraňovaní riadkov/stĺpcov?
Odkaz na bunku sa automaticky aktualizuje tak, aby odkazoval na pôvodnú bunku. Je to tak bez ohľadu na to, či je odkaz na bunku zmrazený.
3D referencie
Niekedy môže byť potrebné pracovať s niekoľkými pracovnými listami s identickými vzormi údajov. Excel vám umožňuje odkazovať na viac hárkov naraz bez toho, aby ste museli ručne zadávať každý pracovný hárok. Na rozsah hárkov môžete odkazovať podobne ako na rozsah buniek. Príklad „List1: List5! A1“ by odkazoval na bunky A1 na všetkých listoch od Listu1 do Listu5.
Pozrime sa na príklad:
Chcete sčítať celkový počet predaných jednotiek pre každý produkt vo všetkých obchodoch. Každý obchod má svoj vlastný pracovný list a všetky pracovné listy majú identický formát. Môžete vytvoriť vzorec podobný tomuto:
Nie je to príliš ťažké iba so štyrmi pracovnými listami, ale čo keby ste mali 40 pracovných listov? Naozaj chcete manuálne pridať odkaz na každú bunku?
Namiesto toho môžete použiť 3D referenciu na ľahké odkazovanie na viac hárkov naraz (podobne ako môžete odkazovať na rozsah buniek).
Buď opatrný! Poradie vašich pracovných listov je dôležité. Ak presuniete ďalší list medzi uvedené listy (StoreA a StoreD), tento list bude zahrnutý. Naopak, ak hárok premiestnite mimo rozsah hárkov (pred StoreA alebo po StoreD), nebude už zahrnutý.
Referencia na kruhové bunky
Odkaz na kruhovú bunku je, keď sa bunka odvoláva na seba. Ak sa napríklad výsledok bunky B1 použije ako vstup pre bunku B1, vytvorí sa kruhová referencia. Bunka nemusí odkazovať na seba. Môžu existovať medzikroky.
Príklad:
V tomto prípade je vzorec pre bunku B2 „A2+A3+B2“. Pretože ste v bunke B2, nemôžete v rovnici použiť B2. Spustí sa kruhová referencia a hodnota v bunke „B2“ sa automaticky nastaví na „0“.
Kruhové odkazy sú zvyčajne výsledkom chyby používateľa, ale za určitých okolností budete chcieť kruhový odkaz použiť. Primárnym príkladom použitia kruhovej referencie je iteračný výpočet hodnôt. Ak to chcete urobiť, musíte ísť na Súbor> Možnosti> Vzorce a Povolený iteračný výpočet:
Externé referencie
Pri výpočte údajov môže byť niekedy potrebné odkázať na údaje mimo zošita. Toto sa nazýva externá referencia (odkaz).
Ak chcete pri vytváraní vzorca vybrať externú referenciu, prejdite na externý zošit a vyberte svoju referenciu ako obvykle.
Na prechod do druhého zošita môžete použiť skratku CTRL + TAB alebo prejsť na Zobraziť> Prepnúť Windows.
Akonáhle vyberiete bunku, uvidíte, že externá referencia vyzerá takto:
Všimnite si, že názov zošita je uzavretý v zátvorkách [].
Po zatvorení odkazovaného zošita sa v referencii zobrazí umiestnenie súboru:
Keď znova otvoríte zošit obsahujúci externý odkaz, zobrazí sa výzva na povolenie automatickej aktualizácie odkazov. Ak tak urobíte, Excel otvorí referenčnú hodnotu s aktuálnou hodnotou zošita. Aj keď je zatvorené! Buď opatrný! Toto môžete alebo nemusíte chcieť.
Pomenované rozsahy a externé referencie
Čo sa stane s vašou referenciou na externú bunku, keď sú riadky / stĺpce pridané alebo odstránené z referenčného zošita? Ak sú oba zošity otvorené, odkazy na bunky sa automaticky aktualizujú. Ak však oba zošity nie sú otvorené, odkazy na bunky sa neaktualizujú a už nebudú platné. Pri prepojení na externé zošity je to obrovská starosť. Spôsobuje to mnoho chýb.
Ak odkazujete na externé zošity, odkaz na bunku by ste mali pomenovať pomenovaným rozsahom (ďalšie informácie nájdete v predchádzajúcej časti). Teraz bude váš vzorec odkazovať na pomenovaný rozsah bez ohľadu na to, aké zmeny nastanú v externom zošite.
