2 Vzorce Příklad 2 Excel 2010
OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová
TÉMA: Vzorce, absolutní a relativní odkazy
Sekretářka společnosti „Naše zahrada“ dostala za úkol provést určité výpočty v sešitě se seznamy zboží.
Pro výpočty využila ve vzorcích relativní a absolutní odkazy na buňky a nadefinovanou konstantu.
Zadání:
Otevřete soubor VzorceOdkazy.xlsx.
1. Na listu Nářadí a náčiní vypočítejte ve sloupci Celkem v $ (buňky F4:F18) celkovou hodnotu
zboží v dolarech (vynásobte sloupce Cena v $ a Sklad).
2. Do sloupce Zaokrouhleno (buňky G4:G18) vložte hodnoty ze sloupce Celkem v $,
zaokrouhlené na desítky, pro zaokrouhlení použijte funkci.
3. Na listu Potřeby vložte do sloupce Cena v Kč (buňky D4:D29) vzorec pro výpočet ceny zboží
v korunách. Kurz dolaru je uložen v buňce I3.
4. Do sloupce Cena v Kč 2 vložte opět vzorec pro výpočet ceny zboží v korunách. Pro výpočet
však použijte již vytvořenou konstantu CenaDolaru.
5. Změňte velikost konstanty CenaDolaru na 19.
6. Sešit uložte a uzavřete.
Řešení
2 Vzorce Příklad 2 Excel 2010
OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová
Řešení:
Karta Soubor/Otevřít (nebo tlačítko Otevřít na panelu nástrojů Rychlý přístup, popř. klávesová
zkratka Ctrl+O). V dialogovém okně Otevřít nalézt soubor dle zadání, tlačítko Otevřít. Pozn. Sešit
lze otevřít také přímo v systému Windows (dvakrát kliknout na ikonu souboru nebo kontextová
nabídka ikony/Otevřít).
1. V praxi se v Excelu nenásobí celé sloupce, ale pouze buňky v jednotlivých řádcích. (Násobení
celých sloupců lze provést s využitím matic, viz další příklady). Nejprve vložit vzorec do první
buňky ve sloupci, poté tento vzorec zkopírovat také do ostatních buněk ve sloupci. Vytvoření
vzorce v buňce F4 - zobrazit list dle zadání, dvakrát kliknout do buňky F4 (nebo označit
buňku, klávesa F2), vložit vzorec: =C4*E4 (vepsat znak =, odkazy na buňky při psaní vzorce lze
vložit také kliknutím myši na buňku), klávesa Enter.
Zkopírovat vytvořený vzorec z buňky F4 do ostatních buněk ve sloupci Celkem v $ (do buněk
F5:F18) – označit buňku F4, zkopírovat do schránky (např. klávesy Ctrl+C), vybrat zbytek
sloupce (oblast F5:F18), vložit vzorec ze schránky (např. klávesy Ctrl+V). Vzorec bude
zkopírován do celého sloupce, Excel ve zkopírovaných vzorcích změní automaticky odkazy na
buňky tak, aby odpovídaly příslušným řádkům, což si lze jednoduše ověřit označením buňky,
vzorec se zobrazí v Řádku vzorců. (Také výsledek v buňkách bude odpovídat příslušným
řádkům.) Tyto automatické změny byly provedeny proto, že ve vzorci jsou použity tzv.
relativní odkazy na buňky (kombinace písmene a číslice – např. C4). Pokud nechceme, aby
byly odkazy na buňky při kopírování vzorce automaticky měněny, musí být použity tzv.
absolutní odkazy, viz bod 3. Pozn. Zkopírovat vzorec lze také vyplněním obsahu buňky do
celého sloupce (chytit myší pravý dolní roh, roztáhnout na konec sloupce).
2. Výpočet vložit do sloupce obdobným způsobem jako v bodě 1. Pro výpočet lze použít funkci
ZAOKROUHLIT – karta Vzorce/skupina Knihovna funkcí/příkaz Mat. a trig.
/Zaokrouhlit/doplnit argumenty funkce: Číslo: odkaz na buňku F4, Číslice: -1 (v nápovědě
k funkci je uvedeno, že v argumentu číslice se uvádí číslo odpovídající počtu desetinných
míst, na která má být údaj zaokrouhlen; pokud má být údaj zaokrouhlen na pozice vlevo od
desetinné čárky, uvádí se číslo se znaménkem záporným). Pozn. Podržíme-li nad názvem
funkce (karta Vzorce/skupina Knihovna funkcí/libovolná skupina/název funkce) chvíli
ukazatel myši, zobrazí se stručný popis funkce. Vloženou funkci zkopírovat do celého sloupce
viz bod 1 (opět jsou využity relativní odkazy).
3. Vzorec do sloupce vložit obdobným způsobem jako v bodě 1. Nejprve vložit vzorec do buňky
D4, poté tento vzorec zkopírovat do celého sloupce. Vytvoření vzorce: do buňky D4 vepsat
vzorec =C4*I$3. Vzorec obsahuje tzv. absolutní odkaz na buňku (kombinace písmene, číslice
a znaku dolaru „$“). Znak dolaru lze do vzorce vepsat pomocí klávesnice (anglická klávesnice,
2 Vzorce Příklad 2 Excel 2010
OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová
klávesa s číslicí 4) nebo pomocí klávesy F4 lze při psaní vzorce přepínat kombinace
absolutního odkazu pro právě vybraný odkaz na buňku. Pozn. Znak dolaru v odkazu na buňku
(před písmenem, číslicí, nebo oběma) znamená, že při kopírování vzorce nebude provedena
automatická změna (řádku, sloupce, nebo obou) v odkazu na buňku. Používá
se např. ve vzorcích, které odkazují na buňku mimo sloupec, a kde tento odkaz nechceme při
kopírování měnit.
4. Vzorec do sloupce vložit obdobným způsobem jako v bodě 1. Nejprve vložit vzorec do buňky
E4, poté tento vzorec zkopírovat do celého sloupce. Vytvoření vzorce: do buňky E4 vepsat
vzorec =C4*CenaDolaru. CenaDolaru je konstanta, vytvořená obdobným způsobem jako
pojmenovaná (definovaná) oblast. Její název lze vložit do vzorce buď prostým vepsáním,
nebo při psaní vzorce na kartě Vzorce/skupina Definované názvy/příkaz Použít
ve vzorci/vybrat název konstanty. Výsledné hodnoty ve sloupci Cena v Kč 2 se budou
poněkud lišit od sloupce Cena v Kč, jelikož je pro konstantu CenaDolaru nadefinován jiný
kurz.
Pozn. Konstanty se ve výpočtech často používají v případech, kdy je stejná hodnota používána
ve více vzorcích v sešitě. Výhodou je mj. to, že nemusíme používat absolutní odkaz při
kopírování vzorce a pokud dojde ke změně hodnoty, nemusíme ji přepisovat ve všech
vzorcích - stačí pouze změnit hodnotu konstanty viz bod 5.
5. Konstantu nalezneme mezi nadefinovanými názvy oblastí – karta Vzorce/skupina Definované
názvy/příkaz Správce názvů/označit položku s konstantou CenaDolaru, ve spodní části
dialogového okna přepsat hodnotu, klávesa Enter, potvrdit uložení změny a zavřít dialogové
okno – tlačítko Zavřít. Ve sloupci Cena v Kč 2 budou automaticky přepočítány hodnoty.
2 Vzorce Příklad 2 Excel 2010
OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová
6. Uložit sešit - karta Soubor/Uložit (nebo tlačítko Uložit na panelu nástrojů Rychlý přístup nebo
klávesy Ctrl+S). Zavřít sešit - karta Soubor/Zavřít (nebo tlačítko Zavřít – x v pravém horním
rohu okna).
Zpět na zadání