Excel 2016 – Lekcia 15: Relatívne a absolútne odkazy na bunky

• Excel 2016 (12. časť): Formátovanie strán a tlač tabuliek
• Excel 2016 (13. časť): Úvod do vzorcov
• Excel 2016 (14. časť): Vytvárajte zložité vzorce

V Exceli existujú dva typy odkazov na bunky: relatívne a absolútne . Relatívne a absolútne odkazy sa správajú odlišne pri odlišnom kopírovaní a vypĺňaní buniek. Relatívne odkazy sa zmenia, keď sa vzorec skopíruje do inej bunky. Absolútne referencie však zostávajú nezmenené bez ohľadu na to, kde sú skopírované.

Pripojte sa k Docs.NeoTechSystems v tomto článku s pokynmi na relatívne a absolútne odkazy na bunky v Exceli 2016 !

Relatívne a absolútne odkazy na bunky v Exceli

• Relatívne referencie
  • Vytvorte a skopírujte vzorec, ktorý používa relatívne odkazy
• Absolútne referencie
  • Vytvorte a skopírujte vzorec podľa absolútneho odkazu
• Použite odkazy na bunky s viacerými pracovnými hárkami
  • Referenčné bunky na pracovných hárkoch

Pozrite si video nižšie a dozviete sa viac o odkazoch na bunky:

Relatívne referencie

V predvolenom nastavení sú všetky odkazy na bunky relatívne. Pri kopírovaní vo viacerých bunkách sa menia na základe relatívnej polohy riadkov a stĺpcov. Ak napríklad skopírujete vzorec =A1+B1 z riadku 1 do riadka 2, vzorec sa zmení na =A2+B2 . Relatívne odkazy sú vhodné najmä vtedy, keď potrebujete zopakovať rovnaký výpočet vo viacerých riadkoch alebo stĺpcoch.

Vytvorte a skopírujte vzorec, ktorý používa relatívne odkazy

V nižšie uvedenom príklade chceme vytvoriť vzorec, ktorý vynásobí cenu každej položky množstvom. Namiesto vytvárania nového vzorca pre každú položku môžeme vytvoriť jeden vzorec v bunke D2 a potom ho skopírovať do ostatných riadkov. Na presný výpočet celkovej sumy pre každú položku použijeme relatívne referencie.

1. Vyberte bunku obsahujúcu vzorec. V príklade vyberieme bunku D4 .

2. Zadajte vzorec na výpočet požadovanej hodnoty. V príklade napíšeme =B4*C4 .

3. Stlačte Enter na klávesnici. Vzorec sa vypočíta a výsledok sa zobrazí v bunke.

4. Nájdite rukoväť výplne v pravom dolnom rohu požadovanej bunky. V príklade nájdeme rukoväť výplne pre bunku D4.

5. Kliknite a potiahnite rukoväť výplne do buniek, ktoré chcete vyplniť. V príklade vyberieme bunky D5:D13 .

6. Uvoľnite myš. Vzorec sa skopíruje do vybratých buniek s relatívnymi odkazmi, pričom v každej bunke sa zobrazia výsledky.

Môžete dvakrát kliknúť na vyplnené bunky a skontrolovať ich presný vzorec. Relatívne odkazy pre každú bunku by sa mali líšiť v závislosti od riadku.

Absolútne referencie

Niekedy možno nebudete chcieť zmeniť odkaz na bunku pri kopírovaní do iných buniek. Na rozdiel od relatívnych referencií sa absolútne referencie pri kopírovaní alebo vypĺňaní nemenia. Môžete použiť absolútny odkaz na udržanie riadkov a/alebo stĺpcov za sebou.

Absolútny odkaz je špecifikovaný vo vzorci pridaním znaku dolára ($) . Môže predchádzať odkaz na stĺpec, odkaz na riadok alebo oboje.

Pri vytváraní vzorcov, ktoré obsahujú absolútne odkazy, zvyčajne použijete formát $A$2 . Ďalšie dva formáty sa používajú menej často.

Pri písaní vzorca môžete stlačením klávesu F4 na klávesnici prepínať medzi relatívnymi odkazmi na bunky a absolútnymi odkazmi na bunky, ako je znázornené na videu nižšie. Toto je jednoduchý spôsob, ako rýchlo vložiť absolútnu referenciu.

Vytvorte a skopírujte vzorec podľa absolútneho odkazu

V nižšie uvedenom príklade použijeme bunku E2 (ktorá má sadzbu dane 7,5 %) na výpočet dane z obratu pre každú položku v stĺpci D. Aby sa zabezpečilo, že odkaz na sadzbu dane zostane konštantný – aj keď sa vzorec skopíruje a vyplnené do iných buniek – z bunky $E$2 musíme urobiť absolútnu referenciu.

1. Vyberte bunku obsahujúcu vzorec. V príklade vyberieme bunku D4 .

2. Zadajte vzorec na výpočet požadovanej hodnoty. V príklade zadáme =(B4*C4)*$E$2 , čím sa $E$2 stane absolútnou referenciou.

3. Stlačte Enter na klávesnici. Vzorec sa vypočíta a výsledok sa zobrazí v bunke.

4. Nájdite rukoväť výplne v pravom dolnom rohu požadovanej bunky. V príklade nájdeme rukoväť výplne pre bunku D4.

5. Kliknite a potiahnite rukoväť výplne do buniek, ktoré chcete vyplniť (v príklade bunky D5:D13).

6. Uvoľnite myš. Vzorec sa skopíruje do vybratých buniek s absolútnym odkazom a hodnoty sa vypočítajú v každej bunke.

Môžete dvakrát kliknúť na vyplnené bunky a skontrolovať ich presný vzorec. Absolútny odkaz v každej bunke by mal byť rovnaký, zatiaľ čo ostatné odkazy sú relatívne k riadku bunky.

Nezabudnite uviesť znak dolára ($) vždy, keď robíte absolútne odkazy vo viacerých bunkách. Znaky dolára boli v nižšie uvedenom príklade vynechané. To spôsobí, že ho Excel interpretuje ako relatívny odkaz a pri kopírovaní do iných buniek vytvára nesprávne výsledky.

Použite odkazy na bunky s viacerými pracovnými hárkami

Excel vám umožňuje odkazovať na ľubovoľnú bunku v hárku, čo môže byť užitočné najmä vtedy, ak chcete odkazovať na konkrétnu hodnotu z iného hárka. Ak to chcete urobiť, jednoducho začnite odkaz na bunku názvom pracovného hárka, za ktorým nasleduje výkričník (!) . Napríklad, ak chcete odkazovať na bunku A1 na Háre1 , jej odkaz na bunku bude Hárok1!A1 .

Všimnite si, že ak názov pracovného hárka obsahuje medzery , budete musieť okolo názvu vložiť zátvorky (' ') . Ak chcete napríklad odkazovať na bunku A1 v pracovnom hárku s názvom Rozpočet na júl , odkaz na bunku bude 'Rozpočet na júl'!A1 .

Referenčné bunky na pracovných hárkoch

V nižšie uvedenom príklade sa budeme odvolávať na bunku s vypočítanou hodnotou medzi dvoma tabuľkami. To nám umožní použiť presnú hodnotu na dvoch rôznych tabuľkách bez prepisovania vzorcov alebo duplikovania údajov.

1. Nájdite bunku, na ktorú chcete odkazovať, a poznačte si jej pracovný hárok. V našom príklade chceme odkazovať na bunku E14 na pracovnom hárku Objednávky menu .

2. Prejdite na požadovanú tabuľku. V našom príklade si vyberieme tabuľku Faktúra za stravovanie .

3. Nájdite a vyberte bunku, v ktorej sa má hodnota zobraziť. V príklade vyberieme bunku C4 .

4. Napíšte znamienko rovnosti (=) , názov tabuľky, za ktorým nasleduje výkričník (!) a adresu bunky. V príklade napíšeme ='MenuOrder'!E14 .

5. Stlačte Enter na klávesnici. Zobrazí sa hodnota odkazovanej bunky. Ak sa teraz zmení hodnota bunky E14 na pracovnom hárku Objednávka jedálneho lístka, automaticky sa aktualizuje aj na hárku Faktúra za stravovanie.

Ak hárok neskôr premenujete, odkaz na bunku sa automaticky aktualizuje, aby odrážal nový názov hárka.

Ak zadáte nesprávny názov tabuľky, #REF! sa zobrazí v poli. V príklade sme si pomýlili názov pracovného listu. Ak chcete upraviť, ignorovať alebo preskúmať chybu, kliknite na tlačidlo Chyba vedľa bunky a vyberte možnosť z ponuky.

Pozrite si ďalšie články:

• Excel 2016 (časť 3): Ako vytvoriť nové a otvoriť existujúce tabuľky
• Excel 2016 (časť 4): Ako ukladať a zdieľať tabuľky
• Excel 2016 (5. časť): Základy buniek a rozsahov

Zabávať sa!