Odkazy v Excelu – absolutní, relativní a smíšené. Chyby při práci s relativními odkazy v Excelu

Pro usnadnění výpočtů pomocí vzorců v automatickém režimu se používají odkazy na buňky. V závislosti na typu psaní jsou rozděleny do tří hlavních typů:

1. Relativní odkazy. Používá se pro jednoduché výpočty. Kopírování vzorce znamená změnu souřadnic.
2. Absolutní odkazy. Pokud potřebujete vytvářet složitější výpočty, je tato možnost vhodná. Pro upevnění použijte symbol „$“. Příklad: $A$1.
3. smíšené odkazy. Tento typ adresování se používá ve výpočtech, kdy je potřeba samostatně fixovat sloupec nebo řádek. Příklad: $A1 nebo A$1.

Pokud je potřeba zkopírovat data zadaného vzorce, použijí se odkazy s absolutní a smíšenou adresou. Článek na příkladech odhalí, jak se provádí výpočty pomocí různých typů odkazů.

Relativní odkaz na buňku v Excelu

Jedná se o sadu znaků, které definují umístění buňky. Odkazy v programu se automaticky zapisují s relativním adresováním. Například: A1, A2, B1, B2. Přesunutím na jiný řádek nebo sloupec se změní znaky ve vzorci. Například výchozí pozice A1. Horizontálním posunem se změní písmeno na B1, C1, D1 atd. Stejně tak dochází ke změnám při pohybu po svislé čáře, pouze se v tomto případě změní číslo – A2, A3, A4 atd. Pokud je potřeba duplikovat výpočet stejného typu v sousední buňce, výpočet se provede pomocí relativní reference. Chcete-li tuto funkci použít, postupujte podle několika kroků:

1. Jakmile jsou data zapsána do buňky, přesuňte kurzor a klikněte myší. Zvýraznění zeleným obdélníkem indikuje aktivaci buňky a připravenost k další práci.
2. Stisknutím kombinace kláves Ctrl + C zkopírujeme obsah do schránky.
3. Aktivujeme buňku, do které chcete přenést data nebo dříve napsaný vzorec.
4. Stisknutím kombinace Ctrl + V přeneseme data uložená v systémové schránce.

Odborná rada! Chcete-li provést stejný typ výpočtů v tabulce, použijte life hack. Vyberte buňku obsahující dříve zadaný vzorec. Umístěním kurzoru na malý čtvereček, který se objeví v pravém dolním rohu, a podržením levého tlačítka myši přetáhněte na spodní řádek nebo krajní sloupec, v závislosti na provedené akci. Uvolněním tlačítka myši se výpočet provede automaticky. Tento nástroj se nazývá značka automatického vyplnění.

Příklad relativního odkazu

Aby to bylo jasnější, zvažte příklad výpočtu pomocí vzorce s relativním odkazem. Předpokládejme, že majitel sportovního obchodu po roce práce potřebuje spočítat zisk z prodeje.

Pořadí akcí:

1. Příklad ukazuje, že sloupce B a C byly použity k vyplnění množství prodaného zboží a jeho ceny. Chcete-li tedy napsat vzorec a získat odpověď, vyberte sloupec D. Vzorec vypadá takto: = B2 *C

Dávej pozor! Pro usnadnění procesu psaní vzorce použijte malý trik. Vložte znak „=“, klikněte na množství prodaného zboží, nastavte znak „*“ a klikněte na cenu produktu. Vzorec za rovnítkem se zapíše automaticky.

2. Pro konečnou odpověď stiskněte „Enter“. Dále je třeba vypočítat celkovou výši zisku získaného z jiných typů produktů. No, pokud počet řádků není velký, pak lze všechny manipulace provádět ručně. Pro vyplnění velkého počtu řádků současně v Excelu existuje jedna užitečná funkce, která umožňuje přenést vzorec do jiných buněk.
3. Přesuňte kurzor nad pravý dolní roh obdélníku se vzorcem nebo hotovým výsledkem. Vzhled černého kříže slouží jako signál, že kurzor lze táhnout dolů. Provádí se tak automatický výpočet zisku získaného pro každý produkt zvlášť.
4. Uvolněním stisknutého tlačítka myši získáme správné výsledky ve všech řádcích.

Kliknutím na buňku D3 můžete vidět, že souřadnice buňky byly automaticky změněny, a nyní to vypadá takto: =B3 *C3. Z toho vyplývá, že vazby byly relativní.

Možné chyby při práci s relativními odkazy

Tato funkce Excelu nepochybně značně zjednodušuje výpočty, ale v některých případech mohou nastat potíže. Podívejme se na jednoduchý příklad výpočtu koeficientu zisku každé položky zboží:

1. Vytvořte tabulku a vyplňte: A – název produktu; B – prodané množství; C – náklady; D je přijatá částka. Předpokládejme, že v sortimentu je pouze 11 položek. S přihlédnutím k popisu sloupců je tedy vyplněno 12 řádků a celková výše zisku je D
2. Klikněte na buňku E2 a zadejte =D2 /D13.
3. Po stisknutí tlačítka „Enter“ se objeví koeficient relativního podílu tržeb první položky.
4. Natáhněte sloupek dolů a počkejte na výsledek. Systém však zobrazí chybu „#DIV/0!“

Důvodem chyby je použití relativní reference pro výpočty. V důsledku kopírování vzorce se souřadnice změní. To znamená, že pro E3 bude vzorec vypadat takto =D3 /D13. Protože buňka D13 není vyplněna a má teoreticky nulovou hodnotu, program vypíše chybu s informací, že dělení nulou není možné.

Důležité! Pro opravu chyby je nutné napsat vzorec tak, aby souřadnice D13 byly pevné. Relativní adresování žádnou takovou funkcionalitu nemá. K tomu existuje jiný typ odkazů – absolutní. 

Jak vytvoříte absolutní odkaz v Excelu

Díky použití symbolu $ bylo možné opravit souřadnice buňky. Jak to funguje, budeme dále zvažovat. Protože program ve výchozím nastavení používá relativní adresování, aby bylo absolutní, budete muset provést řadu akcí. Pojďme analyzovat řešení chyby „Jak zjistit koeficient z prodeje několika položek zboží“ a provést výpočet pomocí absolutního adresování:

1. Klikněte na E2 a zadejte souřadnice odkazu =D2 /D13. Vzhledem k tomu, že odkaz je relativní, musí být pro opravu dat nastaven symbol.
2. Oprava souřadnic buňky D Chcete-li provést tuto akci, umístěte před písmeno označující sloupec a číslo řádku znak „$“.

Odborná rada! Pro usnadnění zadávání stačí aktivovat buňku pro úpravu vzorce a několikrát stisknout klávesu F4. Dokud nedosáhnete uspokojivých hodnot. Správný vzorec je následující: =D2/$D$13.

3. Stiskněte tlačítko „Enter“. V důsledku provedených akcí by se měl dostavit správný výsledek.
4. Přetažením značky na spodní řádek zkopírujte vzorec.

Vzhledem k použití absolutního adresování ve výpočtech budou konečné výsledky ve zbývajících řádcích správné.

Jak vložit smíšený odkaz do Excelu

Pro výpočty vzorce se používají nejen relativní a absolutní reference, ale také smíšené. Jejich charakteristickým rysem je, že fixují jednu ze souřadnic.

• Chcete-li například změnit polohu řádku, musíte před označení písmene napsat znak $.
• Naopak, pokud je znak dolaru napsán za písmenným označením, pak indikátory v řádku zůstanou nezměněny.

Z toho vyplývá, že pro vyřešení předchozího problému se stanovením koeficientu tržeb zboží pomocí smíšeného adresování je nutné opravit číslo řádku. To znamená, že znak $ je umístěn za písmenem sloupce, protože jeho souřadnice se nemění ani v relativním odkazu. Vezměme si příklad:

1. Pro přesné výpočty zadejte =D1/$D$3 a stiskněte „Enter“. Program dává přesnou odpověď.
2. Chcete-li vzorec přesunout do následujících buněk ve sloupci a získat přesné výsledky, přetáhněte úchyt do spodní buňky.
3. Výsledkem je, že program poskytne správné výpočty.

Pozor! Pokud nastavíte znak $ před písmeno, Excel zobrazí chybu „#DIV/0!“, což znamená, že tuto operaci nelze provést.

"SuperAbsolute" adresování

Na závěr se podívejme na další příklad absolutní vazby – „SuperAbsolute“ adresování. Jaké jsou jeho vlastnosti a rozdíly. Vezměte přibližné číslo 30 a zadejte ho do buňky B2. Právě toto číslo bude hlavní, je nutné s ním provést řadu akcí, například ho zvýšit na moc.

1. Pro správné provedení všech akcí zadejte do sloupce C následující vzorec: =$B$2^$D2. Do sloupce D zadáme hodnotu stupňů.

2. Po stisknutí tlačítka „Enter“ a aktivaci vzorce natáhneme značku dolů po sloupci.
3. Dostáváme správné výsledky.

Sečteno a podtrženo, všechny provedené akce se vztahují k jedné pevné buňce B2, tedy:

• Kopírování vzorce z buňky C3 do buňky E3, F3 nebo H3 nezmění výsledek. Zůstane beze změny – 900.
• Pokud potřebujete vložit nový sloupec, změní se souřadnice buňky se vzorcem, ale výsledek také zůstane nezměněn.

Toto je zvláštnost odkazu „SuperAbsolute“: pokud potřebujete přesunout, výsledek se nezmění. Existují však situace, kdy jsou data vložena ze zdrojů třetích stran. Sloupce se tedy posunou do strany a data se nastaví starým způsobem ve sloupci B2. Co se stane v tomto případě? Při smíchání se vzorec mění podle provedené akce, to znamená, že již nebude ukazovat na B2, ale na C2. Ale protože vložení bylo provedeno v B2, konečný výsledek bude špatný.

Odkaz! Abyste mohli vkládat makra ze zdrojů třetích stran, musíte povolit nastavení pro vývojáře (ve výchozím nastavení jsou zakázána). Chcete-li to provést, přejděte na Možnosti, otevřete nastavení pásu karet a zaškrtněte políčko v pravém sloupci naproti „Vývojář“. Poté se otevře přístup k mnoha funkcím, které byly dosud očím běžného uživatele skryté.

Zde se nabízí otázka: je možné upravit vzorec z buňky C2 tak, aby se původní číslo shromáždilo z buňky B, a to i přes vložení nových datových sloupců? Chcete-li zajistit, aby změny v tabulce neovlivnily určení součtu při instalaci dat ze zdrojů třetích stran, musíte provést následující kroky:

1. Místo souřadnic buňky B2 zadejte následující indikátory: =NEPŘÍMÉ(“B2”). V důsledku toho bude po přesunutí formulační kompozice vypadat takto: =NEPŘÍMÉ(“B2”)^$E2.
2. Díky této funkci odkaz vždy ukazuje na čtverec se souřadnicemi B2, bez ohledu na to, zda jsou v tabulce přidány nebo odstraněny sloupce.

Je třeba si uvědomit, že buňka, která neobsahuje žádná data, má vždy hodnotu „0“.

Proč investovat do čističky vzduchu?

Díky použití tří druhů popsaných odkazů se objevuje spousta příležitostí, které usnadňují práci s výpočty v Excelu. Než tedy začnete pracovat se vzorci, přečtěte si nejprve odkazy a pravidla pro jejich instalaci.