Odkazy v Exceli – absolútne, relatívne a zmiešané. Chyby pri práci s relatívnymi odkazmi v Exceli

Na uľahčenie výpočtov pomocou vzorcov v automatickom režime sa používajú odkazy na bunky. V závislosti od typu písania sú rozdelené do troch hlavných typov:

1. Relatívne odkazy. Používa sa na jednoduché výpočty. Kopírovanie vzorca znamená zmenu súradníc.
2. Absolútne odkazy. Ak potrebujete vytvoriť zložitejšie výpočty, táto možnosť je vhodná. Na upevnenie použite symbol „$“. Príklad: $A$1.
3. zmiešané odkazy. Tento typ adresovania sa používa pri výpočtoch, keď je potrebné samostatne fixovať stĺpec alebo riadok. Príklad: $A1 alebo A$1.

Ak je potrebné skopírovať údaje zadaného vzorca, použijú sa odkazy s absolútnym a zmiešaným adresovaním. Článok s príkladmi odhalí, ako sa robia výpočty pomocou rôznych typov odkazov.

Relatívny odkaz na bunku v Exceli

Ide o množinu znakov, ktoré definujú umiestnenie bunky. Odkazy v programe sú automaticky zapísané s relatívnou adresou. Napríklad: A1, A2, B1, B2. Presun na iný riadok alebo stĺpec zmení znaky vo vzorci. Napríklad počiatočná pozícia A1. Horizontálnym pohybom sa písmeno mení na B1, C1, D1 atď. Rovnako tak dochádza k zmenám pri pohybe po zvislej čiare, len v tomto prípade sa mení číslo – A2, A3, A4 atď. Ak je potrebné duplikovať výpočet rovnakého typu v susednej bunke, výpočet sa vykoná pomocou relatívneho odkazu. Ak chcete použiť túto funkciu, postupujte podľa niekoľkých krokov:

1. Hneď po zadaní údajov do bunky presuňte kurzor a kliknite myšou. Zvýraznenie zeleným obdĺžnikom indikuje aktiváciu bunky a pripravenosť na ďalšiu prácu.
2. Stlačením kombinácie kláves Ctrl + C skopírujeme obsah do schránky.
3. Aktivujeme bunku, do ktorej chcete preniesť údaje alebo predtým napísaný vzorec.
4. Stlačením kombinácie Ctrl + V prenesieme dáta uložené v systémovej schránke.

Odborná rada! Ak chcete vykonať rovnaký typ výpočtov v tabuľke, použite life hack. Vyberte bunku obsahujúcu predtým zadaný vzorec. Umiestnite kurzor myši na malý štvorec, ktorý sa zobrazí v pravom dolnom rohu, a podržte ľavé tlačidlo myši a potiahnite na spodný riadok alebo krajný stĺpec v závislosti od vykonanej akcie. Uvoľnením tlačidla myši sa výpočet vykoná automaticky. Tento nástroj sa nazýva značka automatického vypĺňania.

Príklad relatívneho odkazu

Aby to bolo jasnejšie, zvážte príklad výpočtu pomocou vzorca s relatívnym odkazom. Predpokladajme, že majiteľ športového obchodu po roku práce potrebuje vypočítať zisk z predaja.

Poradie akcií:

1. Príklad ukazuje, že stĺpce B a C slúžili na vyplnenie množstva predaného tovaru a jeho ceny. Ak chcete napísať vzorec a získať odpoveď, vyberte stĺpec D. Vzorec vyzerá takto: = B2 *C

Venujte pozornosť! Na uľahčenie procesu písania vzorca použite malý trik. Vložte znak „=“, kliknite na množstvo predaného tovaru, nastavte znak „*“ a kliknite na cenu produktu. Vzorec za znakom rovnosti sa zapíše automaticky.

2. Pre konečnú odpoveď stlačte „Enter“. Ďalej musíte vypočítať celkovú výšku zisku získaného z iných typov produktov. No, ak počet riadkov nie je veľký, potom je možné všetky manipulácie vykonať ručne. Na vyplnenie veľkého počtu riadkov súčasne v Exceli existuje jedna užitočná funkcia, ktorá umožňuje preniesť vzorec do iných buniek.
3. Presuňte kurzor nad pravý dolný roh obdĺžnika so vzorcom alebo hotovým výsledkom. Vzhľad čierneho kríža slúži ako signál, že kurzor možno potiahnuť nadol. Takto sa vykoná automatický výpočet zisku získaného pre každý produkt zvlášť.
4. Uvoľnením stlačeného tlačidla myši dostaneme správne výsledky vo všetkých riadkoch.

Kliknutím na bunku D3 môžete vidieť, že súradnice bunky sa automaticky zmenili a teraz to vyzerá takto: =B3 *C3. Z toho vyplýva, že prepojenia boli relatívne.

Možné chyby pri práci s relatívnymi odkazmi

Táto funkcia Excelu nepochybne značne zjednodušuje výpočty, no v niektorých prípadoch môžu vzniknúť ťažkosti. Zoberme si jednoduchý príklad výpočtu koeficientu zisku každej položky tovaru:

1. Vytvorte tabuľku a vyplňte: A – názov produktu; B – predané množstvo; C – náklady; D je prijatá suma. Predpokladajme, že v sortimente je iba 11 položiek. Preto, berúc do úvahy popis stĺpcov, je vyplnených 12 riadkov a celková suma zisku je D
2. Kliknite na bunku E2 a zadajte =D2 /D13.
3. Po stlačení tlačidla „Enter“ sa zobrazí koeficient relatívneho podielu predaja prvej položky.
4. Natiahnite stĺpik nadol a počkajte na výsledok. Systém však zobrazí chybu „#DIV/0!“

Dôvodom chyby je použitie relatívnej referencie na výpočty. V dôsledku kopírovania vzorca sa súradnice menia. To znamená, že pre E3 bude vzorec vyzerať takto =D3 /D13. Pretože bunka D13 nie je vyplnená a teoreticky má nulovú hodnotu, program vypíše chybu s informáciou, že delenie nulou nie je možné.

Dôležité! Na opravu chyby je potrebné napísať vzorec tak, aby súradnice D13 boli pevné. Relatívne adresovanie takúto funkciu nemá. Na tento účel existuje ďalší typ odkazov - absolútny. 

Ako vytvoríte absolútny odkaz v Exceli

Vďaka použitiu symbolu $ bolo možné opraviť súradnice bunky. Ako to funguje, zvážime ďalej. Keďže program v predvolenom nastavení používa relatívne adresovanie, aby bolo absolútne, budete musieť vykonať niekoľko akcií. Poďme analyzovať riešenie chyby „Ako nájsť koeficient z predaja niekoľkých položiek tovaru“, pričom vykonáme výpočet pomocou absolútneho adresovania:

1. Kliknite na E2 a zadajte súradnice odkazu =D2 /D13. Keďže odkaz je relatívny, na opravu údajov musí byť nastavený symbol.
2. Opravte súradnice bunky D Ak chcete vykonať túto akciu, umiestnite znak „$“ pred písmeno označujúce stĺpec a číslo riadku.

Odborná rada! Na uľahčenie zadávania stačí aktivovať bunku na úpravu vzorca a niekoľkokrát stlačiť kláves F4. Kým nedosiahnete uspokojivé hodnoty. Správny vzorec je nasledujúci: =D2/$D$13.

3. Stlačte tlačidlo „Enter“. V dôsledku vykonaných akcií by sa mal objaviť správny výsledok.
4. Potiahnutím značky na spodný riadok skopírujte vzorec.

Vďaka použitiu absolútneho adresovania vo výpočtoch budú konečné výsledky v zostávajúcich riadkoch správne.

Ako vložiť zmiešaný odkaz do Excelu

Na výpočty vzorca sa používajú nielen relatívne a absolútne odkazy, ale aj zmiešané. Ich charakteristickým znakom je, že fixujú jednu zo súradníc.

• Ak chcete napríklad zmeniť polohu riadku, musíte pred označenie písmena napísať znak $.
• Naopak, ak je znak dolára napísaný za písmenom, ukazovatele v riadku zostanú nezmenené.

Z toho vyplýva, že pre vyriešenie predchádzajúceho problému s určením koeficientu tržieb tovaru pomocou zmiešaného adresovania je potrebné opraviť číslo riadku. To znamená, že znak $ je umiestnený za písmenom stĺpca, pretože jeho súradnice sa nemenia ani v relatívnom odkaze. Vezmime si príklad:

1. Pre presné výpočty zadajte =D1/$D$3 a stlačte „Enter“. Program dáva presnú odpoveď.
2. Ak chcete vzorec presunúť do nasledujúcich buniek v stĺpci a získať presné výsledky, potiahnite rukoväť do spodnej bunky.
3. V dôsledku toho program poskytne správne výpočty.

Pozor! Ak nastavíte znak $ pred písmeno, Excel zobrazí chybu „#DIV/0!“, čo znamená, že túto operáciu nemožno vykonať.

„SuperAbsolútne“ oslovovanie

Na záver sa pozrime na ďalší príklad absolútneho odkazu – „SuperAbsolute“ adresovanie. Aké sú jeho vlastnosti a rozdiely. Vezmite približné číslo 30 a zadajte ho do bunky B2. Toto číslo bude hlavné, je potrebné s ním vykonať sériu akcií, napríklad ho zvýšiť na silu.

1. Pre správne vykonanie všetkých akcií zadajte do stĺpca C nasledujúci vzorec: =$B$2^$D2. Do stĺpca D zadáme hodnotu stupňov.

2. Po stlačení tlačidla „Enter“ a aktivácii vzorca natiahneme značku nadol v stĺpci.
3. Získame správne výsledky.

Pointa je, že všetky vykonané akcie sa týkali jednej pevnej bunky B2, teda:

• Kopírovanie vzorca z bunky C3 do bunky E3, F3 alebo H3 nezmení výsledok. Zostane nezmenená – 900.
• Ak potrebujete vložiť nový stĺpec, súradnice bunky so vzorcom sa zmenia, ale výsledok zostane tiež nezmenený.

Toto je zvláštnosť odkazu „SuperAbsolute“: ak potrebujete presunúť, výsledok sa nezmení. Existujú však situácie, keď sú údaje vložené zo zdrojov tretích strán. Stĺpce sa teda posunú nabok a údaje sa nastavia starým spôsobom v stĺpci B2. Čo sa stane v tomto prípade? Pri zmiešaní sa vzorec mení podľa vykonanej akcie, to znamená, že už nebude ukazovať na B2, ale na C2. Ale keďže vloženie bolo vykonané v B2, konečný výsledok bude nesprávny.

Referencia! Aby ste mohli vkladať makrá zo zdrojov tretích strán, musíte povoliť nastavenia vývojára (v predvolenom nastavení sú zakázané). Ak to chcete urobiť, prejdite na Možnosti, otvorte nastavenia pásu a začiarknite políčko v pravom stĺpci oproti „Vývojár“. Potom sa otvorí prístup k mnohým funkciám, ktoré boli doteraz pred očami bežného používateľa skryté.

To vyvoláva otázku: je možné upraviť vzorec z bunky C2 tak, aby sa pôvodné číslo zhromaždilo z bunky B, napriek vloženiu nových stĺpcov údajov? Aby ste zabezpečili, že zmeny v tabuľke neovplyvnia určenie súčtu pri inštalácii údajov zo zdrojov tretích strán, musíte vykonať nasledujúce kroky:

1. Namiesto súradníc bunky B2 zadajte nasledujúce indikátory: =PRIAME(“B2”). Výsledkom je, že po premiestnení bude formulačná kompozícia vyzerať takto: =PRIAME(“B2”)^$E2.
2. Vďaka tejto funkcii odkaz vždy ukazuje na štvorec so súradnicami B2, bez ohľadu na to, či sú v tabuľke pridané alebo odstránené stĺpce.

Je potrebné si uvedomiť, že bunka, ktorá neobsahuje žiadne údaje, vždy zobrazuje hodnotu „0“.

záver

Vďaka použitiu troch druhov opísaných odkazov sa objavuje veľa príležitostí, ktoré uľahčujú prácu s výpočtami v programe Excel. Preto predtým, ako začnete pracovať so vzorcami, najprv si prečítajte odkazy a pravidlá ich inštalácie.