Relativní a absolutní odkazy - styl A1 - R1C1

Úvodem aneb co se dozvíte

V tomto článku se dozvíte jaký je rozdíl mezi stylem odkazu A1 - R1C1 a jak v těchto stylech použít absolutní, relativní, smíšené odkazování. Jak využít pojmenované oblasti, případně stylem naformátovat tabulku jako Tabulku.

• Obecně o odkazech - proč odkazy ...
• Druhy odkazu - A1, R1C1, styl tabulky, definované názvy
  • Jak změnit styl A1 -> R1C1 a opačně R1C1 -> A1
• Absolutní relativní (smíšené) odkazy
  • Pro styl A1
  • Pro styl R1C1
• Praktické příklady pro styl A1
  • absolutní
  • relativní
  • smíšené
• Praktické příklady pro styl odkazů R1C1
  • absolutní
  • relativní
  • smíšené
• Pokročilejší odkazy - další list,
  • jiný sešit - možnosti
  • operatory - oblasti průnik, atd.
  • výpočet v části buňky F9
• Definované názvy - pojmenované oblasti (jak dát buňce jméno ...)
• Styl tabulky - Strukturované odkazy - proč a jak využít
• Možné komplikace
• Odlišnosti Apple a konkurence (OppenOfice, Google tabulky)
• Ke stažení - navíc zdarma

Obecně o odkazech

V Excelu se bez odkazů neobejdete (předpokládám, že Excel používáte pro skutečnou práci). Pokud v Excelu provádíte výpočty (neopisujete otrocky čísla) musíte používat odkazy. Zadáváním do buněk přímo hodnot pro výpočet např. =1+1, je nesmysl. Buňka výpočet provede, ale při požadavku na změnu hodnot (místo 1 budete potřebovat zadat 2) musíte projít jednotlivé buňky a hodnoty ručně opravit ve vzorci (což je zdlouhavé, nepraktické a zbytečné). Navíc na první pohled nevíte z jakých čísel je výpočet proveden.

U mnoha funkcí (např. vyhledávací, logické) si nedovedu představit smysluplný zápis vzorce (funkce) bez odkazů.

Proto správné, rychlé, elegantní je, mít v část buněk pro hodnoty a v dalších buňkách příslušné výpočty. Pokud se změní jedna hodnota, změníte příslušnou buňku a ostatní buňky se přepočítají automaticky sami.

V tomto článku se dozvíte, jaké Excel má možností odkazování (typy odkazů), na praktických příkladech představím jejich použití. Pokud odkazování pochopíte, uznáte, že myšlenka odkazování je geniální a bez odkazu se již neobejdete.

Odkazy

V Excelu se dá odkazovat:

Druhy odkazů

V Excelu existují druhy/typy odkazů:

• A1 - klasickým - tj. že řádky jsou označeny čísly a sloupce písmeny (nejčastější použití) - tato odkazy navíc mohou být:
  • absolutní
  • relativní
  • smíšení
• R1C1 - programátorským - méně používaný, ale pro programátory výhodný (lépe se orientuje v rozsáhlých tabulkách, hlavně pokud programujete ve VBA) i tyto odkazy mohou být:
  • absolutní
  • relativní
  • smíšení

Navíc se v Excelu lze odkazovat pomocí:

• Definované názvy - jde vlastně o absolutní odkaz, kdy daná buňka (oblast buněk) nese své jedinečné jméno
• Formát tabulky - pokud na tabulku aplikujete formát, můžete využít odkazy na tabulku s formátem/stylem (na první pohled vypadá složitě, ale opak je pravdou, styly pomohou zpřehlednit a zjednodušit vaše vzorce). Tj. na tabulku se aplikuje styl (formát).

Poznámka: Musíte si vybrat buď použití stylu (způsobu) adresování A1, nebo R1C1. Použití definovaných názvů a na buňky, je nezávislé na volbě stylu adresování A1/R1C1.

A1 - klasický

Nejčastěji používáno pro klasické výpočty, odkazy jsou na první pohled přehlednější. Při prvním spuštění Microsoft Excelu je tento styl odkazu přednastaven (pokud jej někdo nezmění, např. "zákeřný" kolega).

R1C1 - programátorský

Oblíbený hlavně programátory. Výhoda při programování ve VBA se nemusíte zabývat písmenky (zda jde o sloupec AA nebo AB?), postě přičtete patřičný počet sloupců.

Definovaný název

Více o definovaných názvech v samostatném článku: Definované názvy, aneb jak pojmenovat buňku (oblast) a dále ji využít pro výpočty.

Formát tabulky

Použití odkazování pomocí adres pro tabulku s formátem je popsáno dále. Případně podrobněji v samostatném článku Jak na formát (styl) tabulky - Microsoft Excel

Změnit styl A1 -> R1C1 a zpět R1C1 -> A1

Pro Excel 2010. Na kartě Soubor - Možnosti v zobrazeném dialogovém okně Možnosti aplikace Excel v menu Vzorce zatrhnout Styl odkazů R1C1 a máte hotovo.

Vtípek - Typ: Takto můžete "pozlobit" své kolegy ;-)

Pro Excel 2007 jsem popsal v článku: Označování sloupců čísly - styl odkazů R1C1

Absolutní relativní (smíšené) odkazy

Ukázky jak vypadají jednotlivé odkazy (absolutní, relativní smíšený) v jednotlivých stylech:

• A1
• R1C1

Styl A1

• relativní A1 - při kopírování vzorce se dynamicky mění odkaz. Příklady v další kapitole.
• absolutní $A$1 - při kopírování buňky nedochází je změně odkazu
• smíšený - tento odkaz může být buď:
  • řádkově absolutní A$1
  • sloupcově absolutní $A1

Styl R1C1

• relativní R[-1]C[-1] - pokud ve stejném sloupci řádku bez čísla
• absolutní R9C6 - při kopírování buňky nedochází je změně odkazu
• smíšený - tento odkaz může být buď:
  • řádkově absolutní R14C[-1]
  • sloupcově absolutní R[-1]C5

Praktické příklady - pro styl odkazů A1

Úkol: Vypočtěte cenu v Kč, pokud znáte převodní kurz. Pro dvě tabulky, kdy data jsou:

• Data ve sloupcích
• Data v řádcích

Data ve sloupcích

=C8*$G$6

Postačí jen řádkově absolutní odkaz pro kurz.

=C8*G$6

Data v řádcích

=C8*G$6

Praktický příklad pro smíšené odkazování

Máte zdrojovou tabulku s udanou délkou a šířkou daných plechů. V řádku je výška ve sloupci šířka. Vašim úkolem bude vypočíst plochu jednotlivých plechů.

Ukázka tabulky:

obdélník šířka	obdélník výška
	10	20	30	40	50
10
20
30
40
50

Postup řešení

Takže nejprve si vypočteme plochu. Čísla jsou volena schválně tak jednoduchá ať si můžete zkontrolovat výsledek z hlavy. Plocha plechu je jasná: Výška x šířka, pokud zadáte jen =B9*C8 při rozkopírováni do dalších buněk obdržíte nesprávné údaje.

Pro funkční rozkopírováni vzorce musíte doplnit o smíšené odkazování tj.:

• pro první část vzorce sloupcově, aby se neměnil sloupec, když kopírujeme doprava
• pro druhou část vzorce, aby se neměnil řádek, když kopírujeme dolů

takže správný vzorec vypadá:

Výška x šířka =$B9*C$8

=$B9*C$8

Poznámka: Je pravda, že můžete vzít buňku po buňce a do každé doplnit výška * šířka, ale při tabulce o 1000 položkách na to budete potřebovat týden.

Praktické příklady pro styl odkazů R1C1

Úkol: Vypočtěte cenu v Kč, pokud znáte převodní kurz. Pro dvě tabulky, kdy data jsou:

• Data ve sloupcích
• Data v řádcích

Data ve sloupcích

=RC[-1]*R8C8

Postačí jen řádkově absolutní odkaz pro kurz.

=C8*G$6

Data v řádcích

=R[-1]C*R24C8

Praktický příklad pro smíšené odkazování

Máte zdrojovou tabulku s udanou délkou a šířkou daných plechů. V řádku je výška ve sloupci šířka. Vašim úkolem bude vypočíst plochu jednotlivých plechů.

Ukázka tabulky:

obdélník šířka	obdélník výška
	10	20	30	40	50
10
20
30
40
50

Postup řešení

Správný vzorec vypadá: Výška x šířka

=RC2*R8C

Pokročilejší odkazy

Odkazovat se nemusíte jen na buňku v daném listě, ale také:

• =A1 - odkaz na buňku ve stejném listu
• =List2!A1 - odkaz na buňku v jiném listu
• =[priklad.xls]List1!A1 - sešit musí být otevřen
• ='[jiny nazev s mezerami.xls]List1'!A1 - musí být uvedeno v uvozovkách
• ='C:\Dokumenty_moje\Excel\[priklad.xls]List1'!A1 správně nastaveno v možnostech, aby se provedlo automaticky načtení změněných hodnot
• =Excel.Sheet.12|'D:\smaz\excel.xlsx'!'!List 1!R12C5' - propojení
• =[@[MujSloupec]] - další způsob odkazů, pokud používáte styly tabulek kde MujSloupec je název sloupce. Podrobnější popis je uveden níže.

Poznámka

Pokud máte odkaz do jiného sešitu, musíte mít nastaveno automatické obnovování v možnostech. Jinak při otevření sešitu se nenačtou automaticky data z těchto externích sešitu.

Operátory oblasti

Aneb odkazovací operátory

Operátor	Popis	Význam	Příklad
:	dvojtečka	operátor oblasti Jeden odkaz na všechny buňky mezi dvěma odkazy včetně těchto odkazů	(B2:C5)
;	středník	operátor sjednocení Kombinuje více odkazů do jednoho odkazu (sjednocuje)	(B2:C4;C7)
	mezera	operátor průniku Vytváří jeden odkaz na buňky společné dvěma odkazům (tj. průnik), viz ukázka	(B7:D7 C6:C8)

Podrobnější odkazovací operátory jsem sepsal v článku operátory v kapitole odkazovací operátory.

Počítání

Mějte v buňce:

=(1+2+3)*2

Označte1+2+3 a stiskněte klávesu F9 dojde k výpočtu v buňce bude (i po stisku Enter):

=(6)*2

Vrátit můžete přes ikonu Zpět nebo Ctrl + Z.

Podobně při použití odkazu na buňku B2.

=B2*2

Označte=B2 a stiskněte klávesu F9 přenese se číslo (hodnota) z buňky B2.

Definované názvy (pojmenované oblasti)

Jak tyto odkazy definovat, jak je použít ve funkcích, jejich výhody a nevýhody, jak přiřadit buňce nebo oblasti buněk jméno, atd. Toto téma přesahuje rámec tohoto článku. Pokud vás zajímá sepsal jsem samostatný článek Definované názvy - úvod a pro pokročilejší práci s definovanými názvy je k dispozici článek Definované názvy pro dynamicky měněné oblasti.

Styl tabulky - Strukturované odkazy

Při použití stylu tabulka na tabulce se můžete odkazovat na jednotlivé buňky (oblasti buněk) pomocí "strukturovaných odkazů". Ve funkci místo odkazu na buňku C3 uvidíte @[MujSloupec]. Podrobnější informace o těchto odkazech jsou v přípravě (přeci jen se jedná o velice specifickou záležitost a její použití už není tak časté, ač výhody jsou obrovské).

V každé verzi Excel 2007 nebo Excel 2010 je tento zápis trochu odlišný (snad z důvodu, aby Microsoft zmátl carský režim a uživatele Microsoft Excel ;-)), ale při práci s tabulkymi se použitáí vyplatí, nejen pro větší přehlednost.

Jak na styly Tabulky je popsáno v samostatném článku styly Tabulky v Microsoft Excel.

Možné komplikace

Jaké chyby můžete při zadávání odkazu způsobit. Na co si dávat pozor.

• kopírování tabulek s odkazy - do jiných sešitů
• přesouvání tabulek - ať už celé tabulky či části tabulky na jiné místo v listu, sešitu
• mazaní řádků, sloupců (buněk) - na které vedou odkazy
• špatně definované oblasti - velká zrada (mnohdy se složitě hledá)
• přepsaný vzorec
• ...

Podrobnější popis je v přípravě.

Apple a konkurence

Rozdíly, aneb jaké jsou odlišnosti u Apple a konkurenčních produktů.

Funkce F4	Konkurence
F4
+ T
Shift + F4

Ke stažení

Soubor Absolutní relativní odkazy pro styl A1 R1C1 ke stažení zdarma.

Poznámka jen jako doplnění co již bylo řečeno: Pro funkci příslušného stylu odkazů R1C1 nebo A1, musíte v možnostech zvolit správný (požadovaný) styl.

Závěrem

Takže základy odkazování ve stylu A1 i R1C1 - absolutně, relativně, smíšeně, případně využití pojmenované oblasti, nebo stylu tabulky jako Tabulky máte za sebou. Pro případné doplnění, na co jsem v článku zapomněl, můžete použít komentáře.

Článek byl aktualizován: 19.09.2020 11:07

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.