Pracovný list v programe Excel sa skladá z buniek. Na tieto bunky je možné odkazovať zadaním hodnoty riadka a hodnoty stĺpca.
A1 by napríklad odkazovalo na prvý riadok (špecifikovaný ako 1) a prvý stĺpec (uvedený ako A). Podobne by B3 bol tretí riadok a druhý stĺpec.
Sila Excelu spočíva v tom, že pri vytváraní vzorcov môžete tieto odkazy na bunky použiť v iných bunkách.
Teraz existujú tri druhy odkazov na bunky, ktoré môžete použiť v programe Excel:
- Relatívne referencie na bunky
- Absolútne bunkové referencie
- Referencie zo zmiešaných buniek
Pochopenie týchto rôznych typov odkazov na bunky vám pomôže pracovať so vzorcami a ušetrí čas (najmä pri kopírovaní vzorcov).
Čo sú relatívne odkazy na bunky v programe Excel?
Na jednoduchom príklade vysvetlím koncept relatívnych odkazov na bunky v programe Excel.
Predpokladajme, že mám súbor údajov uvedený nižšie:
Na výpočet súčtu pre každú položku musíme vynásobiť cenu každej položky množstvom tejto položky.
Pre prvú položku bude vzorec v bunke D2 B2* C2 (ako je uvedené nižšie):
Teraz namiesto zadávania vzorca pre všetky bunky jednu po druhej môžete jednoducho skopírovať bunku D2 a vložiť ju do všetkých ostatných buniek (D3: D8). Keď to urobíte, všimnete si, že odkaz na bunku sa automaticky upraví tak, aby odkazoval na príslušný riadok. Napríklad vzorec v bunke D3 sa zmení na B3*C3 a vzorec v D4 sa zmení na B4*C4.
Tieto odkazy na bunky, ktoré sa pri kopírovaní bunky samy upravia, sa nazývajú relatívne odkazy na bunky v programe Excel.
Kedy použiť relatívne odkazy na bunky v programe Excel?
Relatívne odkazy na bunky sú užitočné, keď musíte vytvoriť vzorec pre rozsah buniek a vzorec musí odkazovať na relatívnu referenciu na bunku.
V takýchto prípadoch môžete vytvoriť vzorec pre jednu bunku a skopírovať ju do všetkých buniek.
Čo sú absolútne odkazy na bunky v programe Excel?
Na rozdiel od relatívnych odkazov na bunky sa absolútne odkazy na bunky nezmenia, keď skopírujete vzorec do iných buniek.
Predpokladajme napríklad, že máte súbor údajov, ako je uvedené nižšie, kde musíte vypočítať províziu z celkového predaja každej položky.
Provízia je 20% a je uvedená v bunke G1.
Ak chcete získať províziu za každý predaj položky, použite nasledujúci vzorec v bunke E2 a skopírujte ju pre všetky bunky:
= D2*$ G $ 1
Všimnite si toho, že v referencii bunky, ktorá má províziu, sú dva znaky dolára ($) - $G$2.
Čo robí znak dolára ($)?
Symbol dolára, ktorý je pridaný pred číslo riadka a stĺpca, ho robí absolútnym (t. J. Zabráni zmene čísla riadka a stĺpca pri kopírovaní do iných buniek).
Napríklad vo vyššie uvedenom prípade, keď skopírujem vzorec z bunky E2 do E3, zmení sa z = D2*$ G $ 1 na = D3*$ G $ 1.
Všimnite si toho, že aj keď sa D2 zmení na D3, $ G $ 1 sa nezmení.
Keďže sme do poľa G1 pridali symbol dolára pred „G“ a „1“, pri kopírovaní sa odkaz na bunku nezmení.
Preto je odkaz na bunku absolútny.
Kedy používať absolútne odkazy na bunky v programe Excel?
Absolútne odkazy na bunky sú užitočné, ak nechcete, aby sa odkaz na bunku menil pri kopírovaní vzorcov. To môže nastať vtedy, ak máte pevnú hodnotu, ktorú musíte použiť vo vzorci (napríklad sadzba dane, sadzba provízie, počet mesiacov atď.)
Aj keď môžete túto hodnotu napevno kódovať (tj. Použiť 20% namiesto 2 G $ $), mať ju v bunke a potom použiť odkaz na bunku vám umožní zmeniť ju v budúcnosti.
Ak sa napríklad zmení vaša štruktúra provízií a vy teraz platíte 25% namiesto 20%, môžete jednoducho zmeniť hodnotu v bunke G2 a všetky vzorce sa automaticky aktualizujú.
Čo sú to zmiešané bunkové referencie v programe Excel?
Referencie na zmiešané bunky sú o niečo zložitejšie ako absolútne a relatívne odkazy na bunky.
Môžu existovať dva typy odkazov na zmiešané bunky:
- Riadok je uzamknutý, zatiaľ čo stĺpec sa pri kopírovaní vzorca zmení.
- Stĺpec je uzamknutý, zatiaľ čo sa riadok pri kopírovaní vzorca zmení.
Pozrime sa, ako to funguje na príklade.
Nasleduje súbor údajov, v ktorom musíte vypočítať tri úrovne provízií na základe percentuálnej hodnoty v bunke E2, F2 a G2.
Teraz môžete použiť silu zmiešanej referencie na výpočet všetkých týchto provízií iba pomocou jedného vzorca.
Do bunky E4 zadajte nasledujúci vzorec a skopírujte ho pre všetky bunky.
= $ B4*$ C4*E $ 2
Vyššie uvedený vzorec používa oba druhy odkazov na zmiešané bunky (ten, kde je riadok uzamknutý a druhý, kde je uzamknutý stĺpec).
Analyzujme každú referenciu na bunku a pochopíme, ako funguje:
- $ B4 (a $ C4) - V tomto odkaze je znak dolára tesne pred stĺpcovým zápisom, ale nie pred číslom riadku. To znamená, že keď skopírujete vzorec do buniek napravo, odkaz zostane rovnaký, ako je stĺpec pevný. Ak napríklad skopírujete vzorec z E4 do F4, tento odkaz sa nezmení. Keď ho však skopírujete, číslo riadku sa zmení, pretože nie je uzamknuté.
- 2 doláre - V tomto odkaze je znak dolára tesne pred číslom riadka a stĺpcový zápis nemá znak dolára. To znamená, že keď skopírujete vzorec do buniek, odkaz sa nezmení, pretože číslo riadka je uzamknuté. Ak však skopírujete vzorec vpravo, abeceda stĺpcov sa zmení, pretože nie je uzamknutá.
Ako zmeniť referenciu z relatívnej na absolútnu (alebo zmiešanú)?
Ak chcete zmeniť referenciu z relatívnej na absolútnu, musíte pred znak stĺpca a číslo riadka pridať znak dolára.
Napríklad A1 je relatívna referencia na bunku a stane sa absolútnou, keď ju získate $ A $ 1.
Ak máte len pár odkazov na zmenu, môže byť pre vás ľahké zmeniť tieto odkazy ručne. Môžete teda prejsť na panel vzorcov a vzorec upraviť (alebo vyberte bunku, stlačte kláves F2 a potom ho zmeňte).
Najrýchlejší spôsob, ako to dosiahnuť, je klávesová skratka F4.
Keď vyberiete odkaz na bunku (na paneli vzorcov alebo v bunke v režime úprav) a stlačíte kláves F4, odkaz sa zmení.
Predpokladajme, že máte v bunke odkaz = A1.
Tu sa stane, keď vyberiete referenciu a stlačíte kláves F4.
- Raz stlačte kláves F4: Referencia bunky sa zmení z A1 na $ A $ 1 (stane sa „absolútna“ z „relatívnej“).
- Dvakrát stlačte kláves F4: Referencia bunky sa zmení z A1 na A $ 1 (zmení sa na zmiešanú referenciu, kde je riadok uzamknutý).
- Trikrát stlačte kláves F4: Odkaz na bunku sa zmení z A1 na $ A1 (zmení sa na zmiešaný odkaz, kde je stĺpec uzamknutý).
- Stlačte štyrikrát kláves F4: Odkaz na bunku sa opäť zmení na A1.
