3. Adresování buněk

Už víme, že adresa buňky je dána označením řádku a sloupce, tedy např. adresa buňky ve sloupci B a řádku 4 je B4. Tento způsob odkazu na buňku však není jediný. Odkazy mohou být:

Relativní adresy

Při kopírování vzorců jste si možná všimli tohoto:
Kopírujeme vzorec =B1-A1 z buňky C1 do buněk C2:C5. Když se podíváme na obsah buňky C2, bude tam vzorec =B2-A2. Podobně v buňce C3 je =B3-A3 atd.

Adresa napsaná tak, jak ji známe doposud, se při kopírování vzorce mění a to způsobem, který odpovídá přesunu vzorce. O kolik sloupců či řádků se liší pozice zkopírovaného vzorce od pozice původního vzorce, o tolik sloupců či řádků se liší adresy obsažené v zkopírovaném vzorci od adres v původním vzorci. Adresy skládající se pouze ze jména řádku a sloupce (A1, B4, D7 ap.) nazýváme relativní.

Absolutní adresy

Je zřejmé, že změna adresy při kopírování vzorce nemusí být vždy žádoucí. Jestliže chceme, aby byla adresa neměnná při kopírování, napíšeme před jméno sloupce i řádku znak $ ($A$1, $B$4, $D$7 ap.). Takto zapsané adresy nazýváme absolutní.

Smíšené adresy

Je také možné napsat $ pouze před řádkem nebo pouze před sloupcem. V tom případě část adresy s $ je absolutní a část adresy bez $ je relativní. Takovéto adresy nazýváme  smíšené.

Poznámka:
Pro snadné vložení znaku $ na české klávesnici můžeme použít klávesu F4 Tato klávesa funguje jako přepínač mezi relativní, absolutní a smíšenými adresami buňky. Jestliže jsme vložili relativní adresu, prvním stiskem se změní na absolutní, dalším na smíšenou.

Odkazy na oblast buněk

V mnohých případech je zapotřebí odkázat se na celý úsek buněk. Odkaz na úsek buněk sestává z adres dvou protilehlých rohů (zpravidla levého horního a pravého dolního) mezi něž napíšeme dvojtečku. Např.:

úsek B2:C4

Důležitým jednotlivým buňkám i úsekům tabulky můžeme přiřadit název. Máme-li buňku či úsek označené, volíme z hlavní nabídky Vložit — Název — Definovat... a v zobrazeném dialogovém okně napíšeme název buňky či úseku. Na pojmenované úseky se můžeme místo adresami odvolávat jejich názvem.

Externí odkazy

Protože buňky mají stejné adresy na všech listech v každém sešitě, je třeba se při práci s více listy nebo sešity odkazovat na buňky nejen jejich absolutní či relativní adresou, ale i jménem listu, případně sešitu (souboru).

Mezi název listu a adresu buňky či úseku píšeme " !". Např. na buňku B5 listu List2 se odkážeme List2!B5. Je-li list přejmenován a má v názvu mezeru, musíme dát jméno listu do apostrofů, např. 'Přehled A'!B5. Odkaz na buňku B5 na listech List1 až List3 napíšeme takto: List1:List3!B5.

Pokud se odvoláváme na údaje z jiného sešitu (tj. souboru), musí být v adrese buňky obsažen i název souboru. Odkaz na úsek A2:B12 na listě Výdaje v souboru TAB1 tedy vypadá takto: ='[tab1.xls]Výdaje!$A$2:$B$12'. V zadání jména souboru může být obsažena i cesta k tomuto souboru: = 'a:\prehledy\[tab1.xls]Výdaje'!$A$2:$B$12.

Celá tato kapitola je současně cvičením na zopakování znalostí a dovedností z předchozích kapitol. Naším cílem je vytvoření následující tabulky:

Obrázek 3.2.1: Příklad tabulky se vzorci a funkcemi

Začněte tím, že napíšete do tabulky seznam osob s jejich platy a příplatky. Chcete-li vložit 10 jmen a příjmení tak, jak je tomu na obrázku, rychlý postup je například tento: Kliknětě na buňku A4 a označte oblast buněk A4 až D13. Nyní můžete psát jméno, příjmení, plat, příplatek, jméno, příjmení, plat, příplatek atd., každý zápis do buňky ukončete klávesou Tab. Máte-li celý seznam napsaný, označte sloupce A a B a zadejte příkaz Formát — Sloupec — Přizpůsobit. Šířka sloupců A a B se přizpůsobí délce jmen a příjmení, která jste do nich vložili.

Vyplňte názvy sloupečků v tabulce. Klikněte na buňku C2 a napište Plat. Stisknětě klávesu ® a napište Osobní příplatek. Stejným způsobem postupujte dále, až nadepíšete záhlaví všech sloupečků. Označte právě vyplněné buňky C2 až H2. Zadejte příkaz Formát — Buňky... a zobrazte si kartu Zarovnání. Zvolte vodorovné i svislé zarovnání Na střed a zaškrtněte políčko Zalomit text. Ukončete příkaz stiskem tlačítka . Pokud je text v buňce H2 je zalomen do tří řádků, což nevypadá pěkně, uchopte myší hranici mezi sloupci H a I a rozšířte sloupec H tak, aby v buňce H2 byly dva řádky.

Označte myší buňku E3 a napište 8%, stiknětě klávesu ® , napište 4,5%. Stiknětě dvakrát klávesu ® , napište 2200 a ukončete vkládání tlačítkem . Buňka H3 je aktuální. Stiskněte tlačítko na panelu nástrojů. Text v buňce se zarovná na střed. Označte buňky E3 a F3, zadejte příkaz Formát — Buňky... a zvolte kartu Číslo. Vyberte druh procenta a nastavte počet desetinných míst na 1. Dále označte kartu Zarovnání a na ní zvolte vodorovné zarovnání Na střed. Tlačítkem ukončete příkaz.

Doplňte nadpis tabulky. Text nadpisu vložte do buňky A1. Zápis ukončete stiskem tlačítka , aby buňka zůstala aktuální. Pomocí panelu nástrojů zvětšíte velikost písma v buňce. Pak označte buňky A1 až H1 a na kartě Zarovnání příkazu Formát — Buňky... zvolte vodorovné zarovnání Na střed výběru a svislé zarovníní Na střed. Je vhodné ještě zvětšit výšku prvního řádku.

Sloupec E obsahuje sociální pojistné, jehož velikost v procentech je v buňce E3. Do buňky E4 je tedy třeba vložit vzorec, který vypočte výraz (C4+D4)*E3. V buňce E5 musí být (C5+D5)*E3, v buňce E6 (C6+D6)*E3 atd. Je tedy vidět, že vzorec do buňky 4 musíme zapsat tak, aby se adresa E3 při jeho kopírování do dalších buněk sloupce E neměnila. Zapíšeme ji tedy jako adresu absolutní, tj. $E$3. Došli jsme tedy ke vzorci (C4+D4)*$E$3. Vypočtené pojistné se však zaokrouhluje, a to vždy nahoru na celé koruny. Funkce pro zaokrouhlování nahoru se jmenuje ROUNDUP a má dva parametry. Prvním parametrem je číslo, které chceme zaokrouhlit, druhý parametr určuje řádovou přesnost zaokrouhlení. Je-li druhým parametrem 0, zaokrouhlí se na celá čísla, je-li druhým parametrem např. 2, zaokrouhlí se na setiny a je-li druhý parametr např. -3, zaokroulí se na celé tisíce. V našem případě bude prvním parametrem funkce ROUNDUP výraz (C4+D4)*$E$3, druhým parametrem 0.

Označte buňku E4 a umístěte kurzor do editačního řádku. Psaní vzorců v editačním řádku je totiž přehlednější než přímo v buňce. Napište vzorec: =ROUNDUP((C4+D4)* $E$3;0). Hledání znaku $ se vyhnete, jestliže místo $E$3 napíšete E3 a pak bezprostředně stisknete klávesu F4. Vkládání ukončete tlačítkem . V buňce se objeví číslo – výsledná hodnota vzorce. Pokud tomu tak není, zkontrolujte si zápis vzorce.

V buňce F4 bude vzorec velmi podobný vzorci v buňce E4. Je třeba sečíst obsah buněk C4 a D4, vynásobit hodnotu v F3 a zaokrouhlit na celá čísla. Do buňky F4 tedy patří vzorec =ROUNDUP((C4+D4)*$F$3;0). Abyste si ušetřili psaní celého vzorce, zkopírujte obsah buňky E4 do buňky F4. Buňka E4 by měla být stále aktuální, takže stačí stisknout klávesu Ctrl, přesunout kurzor myši na okraj označené buňky, stisknout levé tlačítko myši a posunout o jednu buňku vpravo. Uvolněte nejdříve tlačítko myši, pak teprve klávesu Ctrl. V editačním řádku vidíte obsah aktuální buňky F4: =ROUNDUP((D4+E4)*$E$3;0). Umístěte kurzor do editačního řádku a opravte adresu E4 ve vzorci na C4 a adresu $E$3 na $F$3 . Ukončete stiskem tlačítka .

Poznámka:
Kdybychom mysleli více dopředu a vzorec v buňce E4 zapsali se smíšenými adresami ve tvaru =ROUNDUP(($D4+$E4)* E$3;0), nemuseli bychom po jeho zkopírování do buňky F4 dělat žádnou opravu.

Ve sloupci G je základ daně, což je součet platu a osobního příplatku snížený o sociální a zdravotní pojistné. Výsledná hodnota se opět zaokrouhluje směrem nahoru, a to na celé stovky. Do buňky G4. tedy zapište vzorec =ROUNDUP(C4+D4-E4-F4;-2). V sloupci H je základ daně snížený o 2200, což je obsah buňky H3. Do buňky H4 tedy vložte jednoduchý vzorec bez funkce: =G4-$H$3.

Označte oblast buněk E4 až H4. Přesuňte kurzor na tažný úchyt v pravém dolním rohu označené oblasti (změní se na tenké +), stisknětě levé tlačítko myši a tahem dolů kopírujte obsah označené části až po třináctý řádek.

Podíváte-li se na cílovou tabulku, vidíte, že zbývá vložit do buňky A15 text průměrný plat:, do buňky A17 text největší příplatek:, do C15 vzorec =PRŮMĚR(C4:C13) a do buňky D17 vzorec =MAX(D4:D13).

Posledním úkolem je pro vás orámování buněk tabulky podle vzoru.