roli při identifikaci a popisu těchto čísel. Samotné buňky v Excelu mohou
obsahovat buď text (který nemá numerické vyjádření), nebo číselnou hodnotu.
Čísla mohou být jednoho ze čtyř rozdílných typů:
- konstanta neměnná hodnota, například číslo vyjadřující převodový poměr mezi
minutami a sekundami
- proměnná číslo (například cena), které se může kdykoliv měnit a většinou
ovlivňuje další výpočty
- data hodnoty, které odrážejí stav ze skutečného života, jako například
přehled prodeje výrobků
- výsledky většinou vzorce (jež mohou obsahovat různé funkce), např. celkový
zisk, vzniklý z prodaných kusů (data) a jejich ceny (proměnná)
Obecně řečeno není možno kombinovat v jedné buňce textové a číselné hodnoty.
Pokud jde o Excel, můžete do buňky sice zadat hodnotu "včetně DPH 22 %", ale
uvedená čísla nemají žádný význam, neboť celkově buňka neobsahuje číselnou
hodnotu. Technicky vzato by se hodnota DPH (22 % nebo 0,22) mohla změnit, a
měla by tedy být považována za proměnnou.
Nicméně je možné jednoduše kombinovat v jedné buňce text a čísla. Tato velmi
užitečná schopnost je jeden z prvků, kterému se v článku budeme věnovat,
protože úzce souvisí s problematikou proměnných.
1. Konstanty a proměnné
Kromě čísel používaných k vyjádření specifických výpočtů (procentuální
vyjádření nebo dělení vzorcem) by se do vzorců měly vkládat pouze konstanty.
Jsou to čísla, která nikdy nemění svou hodnotu, a mohou se proto využívat v
řadě výpočtů.
První z ukázkových souborů Rychlost.xls (na našem CD) využívá konstanty při
převodu hodnot pro výpočet dráhy ze zadané rychlosti a času. Dráha (buňka C24)
je vyjádřena takto:
dráha = rychlost (v metrech za sekundu) * čas (v sekundách) = C19 * C20
Ještě předtím je potřeba rychlost v kilometrech za hodinu (C12) převést na
metry za sekundu (C19) tímto výpočtem:
rychlost (m/s) = rychlost (km/h) / 3,6 = C12 / 3,6
Konstanta 1/3,6 vyjadřuje poměr mezi metry za sekundu a kilometry za hodinu, a
její hodnota se nemění.
Podobně se musí čas v hodinách (C13), minutách (C14) a sekundách (C15)
přepočítat na sekundy vzorcem:
čas (s) = hodiny * 3600 + minuty * 60 + sekundy = C13 * 3600 + C14 * 60 + C15
Hodnoty 3600 a 60, vyjadřující počet sekund v hodině a minutě, jsou opět
konstantní.
Snadno se vám může stát, že do vzorce vložíte hodnotu, o které se mylně
domníváte, že je konstantní. Nemusí být snadné si takové chyby všimnout,
protože tabulka může dávat správné výsledky, byť jen pro speciální případy, pro
které je vzorec správný. Omyl se projeví až tehdy, kdy je potřeba z nějakého
důvodu změnit hodnotu nesprávně považovanou za konstantu.
Této chyby se často dopouštějí začínající uživatelé, a pokud se vzorce následně
kopírují, může to vést k významným chybám. Jakmile pak dojde k situaci, kdy je
potřeba změnit hodnotu nesprávně považovanou za konstantní, je mnohdy snadnější
začít tvořit vzorce znovu od začátku, než se pokoušet špatný výpočet opravit.
2. Adresace buněk
Když do tabulky zadáváte proměnné, vložte potřebnou hodnotu do příslušné buňky.
Na proměnnou se pak odkazujte pomocí absolutní adresy buňky.
Na proměnnou v buňce B4 byste se měli vždy, když ji používáte ve vzorci,
odkazovat zapsáním $B$4. Symbol $ pak totiž zamezí změně sloupce (B) nebo řádku
(4) v adrese buňky bez ohledu na to, jestli budete následně vzorec kopírovat
horizontálně (do jiného sloupce) nebo vertikálně (do jiného řádku).
Alternativní metodou zadávání adres buněk do vzorce upravovaného v řádku vzorců
je jednoduché kliknutí na danou buňku. Její adresa pak bude automaticky vložena
na aktuální místo ve vzorci.
Zadávání adres buněk kliknutím je (oproti jejímu psaní z klávesnice), nejen
rychlejší, ale také snižuje riziko překlepu v zápisu vzorce. Po výběru buňky
kliknutím se do vzorce zapíše základní adresa (např. B5) a opakovaným stiskem
funkční klávesy "F4" je možno měnit typ odkazu (B5, $B$5, B$5, $B5). Jednotlivé
varianty adresy mají tento význam:
B5 znamená, že se adresa při kopírování vzorce relativně změní, a to jak
vzhledem ke sloupci, tak k řádku
$B$5 určuje, že se adresa ani při kopírování nezmění
B$5 znamená, že se adresa bude měnit relativně vzhledem ke sloupci (například
při kopírování do strany), ale řádek zůstane vždy zachován (i při kopírování
výš nebo níž)
$B5 naopak znamená, že se v adrese bude při kopírování vzorce relativně měnit
pouze řádek a sloupec zůstane nezměněn
3. Číselníky a posuvníky
Nejčastější metodou změny obsahu proměnné je jeho jednoduché přepsání novou
hodnotou. Ve většině případů je to praktický a logický přístup, ale v některých
situacích je vhodnější použít číselník nebo posuvník. Typickým příkladem pro
využití těchto prvků je proměnná, která má určený rozsah možných hodnot.
Abyste mohli s číselníky a posuvníky pracovat, musíte mít zobrazený excelovský
panel nástrojů Formuláře. Klikněte na tlačítko s ikonou požadovaného prvku a
zobrazeným křížkem vyznačte v tabulce místo pro jeho vložení. Když při kreslení
obdélníku, představujícího budoucí umístění číselníku nebo posuvníku, podržíte
stisknutou klávesu ALT, bude prvek zarovnán s mřížkou tabulky.
Pro nastavení číselníku nebo posuvníku na něm klikněte pravým tlačítkem myši a
zvolte příkaz Formát ovládacího prvku. Na obrázku vidíte dialogové okno s
nastavením vlastností číselníku pro zadání počtu minut. Protože další číselník
umožňuje zadání hodin, jsou minuty omezeny na hodnoty od 0 do 59. Pro nastavení
sekund je k dispozici jak číselník, tak posuvník.
Obecně lze říci, že číselníky jsou užitečnější v situacích, kdy je zadávaná
hodnota z menšího rozsahu. Naopak posuvníky se uplatní u proměnných s velkým
rozsahem.
4. Pojmenování buněk
Přestože na používání běžné metody odkazování na buňky jejich adresami (A1, B5
apod.) není nic špatného, možná dáte přednost účelnějším (a snadněji
rozpoznatelným) jménům jako Cena nebo Zisk. Excel nabízí dva postupy pro
pojmenování buněk, při obou musí být kurzor umístěn na buňce, jejíž jméno
budete definovat:
- přímé zapsání Jméno, které chcete buňce přidělit, jednoduše vepište do "Pole
názvů", umístěného úplně vlevo vedle řádku vzorců. Ve druhém ukázkovém souboru
Analýza.xls (na našem CD) je například buňka C3 označena názvem Cena. Název
buňky nesmí obsahovat mezeru, proto se pro vizuální oddělení dvou slov v názvu
používá znak "_" (viz např. buňku C13 pojmenovanou Fixní_náklady).
- příkaz z menu Po zvolení příkazu Definovat z nabídky Vložit/Název můžete
zadat jméno buňky. Pokud je v některé ze sousedních buněk nalezen vhodný text,
bude nabídnut jako nové jméno buňky, které stačí pouze potvrdit. Kdyby
například byl ve vedlejší buňce text "Celkový roční příjem", nabídnul by Excel
název "Celkový_roční_příjem".
Přestože doplnění textových názvů buněk může ze začátku představovat o něco víc
práce, uživatelé, kterým se tento postup osvědčí, ušetří následně čas při
tvorbě vzorců.
Příklady použití názvů buněk při vytváření vzorců obsahuje i ukázkový soubor
Analýza.xls, jehož cílem je určit, kolik kusů výrobků je potřeba prodat, aby
bylo dosaženo zisku při stanovené ceně, fixních a variabilních nákladech.
V buňce E4 (první buňka ve sloupci prodaných kusů) je tento vzorec:
=CELÁ.ČÁST(Fixní_náklady/(Cena-Variabilní_náklady)-3)
Funkce CELÁ.ČÁST() zajišťuje, že se bude počítat pouze s celými kusy,
(Cena-Variabilní_náklady) představuje zisk z jednoho prodaného kusu a
Fixní_náklady/(Cena-Variabilní_náklady) určuje minimální počet prodaných kusů,
který zajistí zisk. Odečtení trojky zaručí, že vložený graf bude vždy začínat
na rozumné pozici.
Pro přehledné zobrazení pozic pojmenovaných buněk je vhodné přejít na buňku pod
hlavními výpočty sešitu a z nabídky Vložit/Název vybrat příkaz Vložit a
následně tlačítko Vložit seznam. Vytvoří se přehledný výpis pojmenovaných buněk
a jejich standardních adres.
Aby se počet prodaných kusů v tabulce zvyšoval, obsahuje buňka E5 vzoreček
=1+E4, který se po zkopírování směrem dolů změní v dalších buňkách postupně na
=1+E5, =1+E6 atd.
5. Oblasti buněk
Seznam názvů buněk v souboru Analýza.xls ukazuje, že oblast (skupina
sousedících buněk) E4:E13 byla pojmenována Prodáno_kusů. To umožňuje jednoduše
vytvořit vzorce pro výpočet zisku (nebo ztráty) v buňkách F4:F13 takto:
=Prodáno_kusů*(Cena-Variabilní_náklady)-Fixní_náklady
V tomto případě je použití jmen buněk podstatně jednodušší, než vytvoření
ekvivalentních vzorců se standardními adresami. To by vyžadovalo zvýšenou
pozornost zejména s ohledem na použití správných typů absolutních odkazů.
Vzorec v buňce F4 by musel vypadat takto:
=$E4*($C$3-$C$8)-$C$13
Takto vytvořený výpočet by se teprve musel zkopírovat do dalších buněk.
6. Vyhledávací funkce
Užitečnou skupinu schopností Excelu představují vyhledávací funkce, které
umožňují najít specifickou hodnotu v tabulce a vrátit jako výsledek
odpovídající hodnotu z jiného sloupce (resp. řádku).
Pomocí funkce tohoto typu je možno v ukázkovém souboru zjistit, kolik prodaných
kusů je mezní hodnota mezi ziskem a ztrátou. Stačí prohledat oblast F4:F13,
najít hodnotu, která se nejvíce blíží nule (v našem případě to bude přesně
nula), a vrátit odpovídající číslo ze sloupce prodaných kusů (v buňce E7
hodnota 156).
Nejznámější funkcí pro tuto situaci je SVYHLEDAT() (vyhledávání ve svislém
směru). Bohužel je tato funkce schopna vracet pouze hodnoty ze sloupců vpravo
od vyhledávací oblasti a v našem příkladu jsou počty prodaných kusů vlevo od
sloupce se ziskem, který je potřeba prohledat. Bez většího zásahu do designu
sešitu tedy není možno tuto funkci využít.
Existuje však méně známá, avšak obecnější funkce VYHLEDAT(), kterou lze
přizpůsobit k vyhledávání jak vlevo, tak vpravo.
Zápis funkce i s příslušnými argumenty vypadá takto:
VYHLEDAT(co;hledat;výsledek)
- co představuje hodnotu, která se má vyhledávat
- hledat je oblast, v níž se má hodnota vyhledat
- výsledek je odpovídající oblast hodnot, ze které se má vrátit požadovaný
výsledek
V pomocných výpočtech v souboru Analýza.xls je funkce VYHLEDAT() využita k
vyjádření nejmenšího počtu prodaných kusů, který zajistí zisk. Ve vzorci (buňka
D29) jsou využity pojmenované buňky (resp. oblasti) Zisk a Prodáno_kusů:
=VYHLEDAT(0;Zisk;Prodáno_kusů)+1
Pokud by Excel nenašel přesně požadovanou hodnotu, použil by nejbližší
zápornou. Proto je k nalezenému výsledku přičtena jednička, která zaručí
korektnost celé kalkulace.
7. Využití proměnných při analýze
Velice dobrým důvodem pro používání proměnných jako nezávislých hodnot je
skutečnost, že zároveň se změnou proměnné se ihned změní všechny výpočty, které
s ní souvisí. Toho se dá využít při jednoduché analýze typu jestliže se hodnota
proměnné změní na ##, jak to ovlivní výsledek? V ukázkovém souboru Analýza.xls
se nabízí jednoduchý příklad. Jestliže se cena zvýší na hodnotu 15, kolik
výrobků se musí prodat k dosažení alespoň malého zisku? Stačí do buňky C3 zadat
číslo 15 a zjistíte, že adekvátní počet prodaných kusů je 79.
8. Optimalizace proměnných pomocí hledání řešení
Opačný postup analýzy může být vyjádřen takto jaká hodnota proměnné povede k
výsledku ##? Samozřejmě by bylo možné měnit hodnotu příslušné proměnné tak
dlouho, než by bylo dosaženo požadovaného výsledku, avšak byl by to proces
velice pomalý a neefektivní. Excel nabízí pro tuto situaci jednoduché a rychlé
řešení v podobě příkazu Hledání řešení z nabídky Nástroje. Když budeme chtít
zjistit, jaká cena (buňka C3) povede k výslednému počtu 100 kusů (v buňce D29),
dojdeme k výsledku 14,56.
9. Vzorce kombinující text a číselné hodnoty
V úvodu jsme se zmínili, že jedna buňka sešitu může obsahovat buď text, nebo
numerickou hodnotu, nikoliv však obojí. Existuje však metoda, jak toto omezení
(alespoň zdánlivě) obejít. Spočívá v tom, že se číselné hodnoty převedou na
textové funkcí HODNOTA.NA.TEXT(), a pak se textovým operátorem spojí do jedné
buňky jak běžný text (tvořený znaky), tak čísla (převedená na text).
V souboru Analýza.xls je již počet prodaných kusů potřebných k dosažení
minimálního zisku vypočítán v buňce D29. Abyste spojili tuto hodnotu s
odpovídajícím textem je potřeba:
1. Převést numerickou hodnotu v buňce D29 na textovou funkcí:
=HODNOTA.NA.TEXT(D29;0)
Druhý argument 0 (nula) zajišťuje, aby se výsledek nezobrazoval s žádnými
desetinnými místy.
2. Spojit text "Počet prodaných kusů potřebný k dosažení zisku: " s převedenou
číselnou hodnotou pomocí operátoru &, který je vhodnější než znaménko +
používané v číselných výpočtech. Celý vzorec vypadá takto:
="Počet prodaných kusů potřebný k dosažení zisku: "&HODNOTA.NA.TEXT(D29;0)
Jako výsledek se v buňce zobrazí text:
Počet prodaných kusů potřebný k dosažení zisku: 157
Použití výpočtů s texty umožňuje uživateli částečně porušit základní pravidlo o
kombinování textu a čísel v jedné buňce. V tomto případě je však vše v pořádku,
neboť při změně vypočteného počtu kusů (číselná hodnota v buňce D29) se změní
také text, který jej obsahuje (buňka D33).
10. Textové vzorce jako součásti grafů
Excel umožňuje použít výsledek textového výpočtu (např. "Počet prodaných kusů
potřebný k dosažení zisku: 157") jako obsah textového pole tvořícího popisek
grafu. Potřebujete k tomu vložit textové pole z panelu nástrojů Kreslení, a
potom místo zapisování textu přejděte do řádku vzorců, zadejte znak = (rovná
se) a klikněte na buňku obsahující potřebný text (D33).
Přizpůsobení panelů nástrojů Excelu
Excel nabízí mnoho panelů nástrojů, které mají standardní vzhled a obsah, ale
mohou být upraveny, aby lépe vyhovovaly individuálním potřebám uživatele.
Abyste si mohli doplnit na panely nástrojů další tlačítka, klikněte na příkaz
Vlastní v nabídce Nástroje. Přejděte na záložku Příkazy a vyberte si vlevo
některou z kategorií příkazů. V pravé části se zobrazí seznam dostupných
tlačítek, z nichž můžete kterékoliv jednoduchým přetažením umístit na zvolený
panel nástrojů.
Můžete například tlačítka pro zvětšení a zmenšení písma (z kategorie Formát)
přenést do panelu nástrojů Formát mezi ikony pro podržení písma a zarovnání
vlevo. Tlačítka pro změnu velikosti písma jsou poměrně užitečná, neboť umožňují
dynamicky měnit velikost textu a čísel.
Kterékoliv tlačítko odstraníte z panelu nástrojů tak, že podržíte stisknutou
klávesu Alt a tlačítko přetáhnete mimo panel.
Ověřování hodnot
Příkaz Ověření z nabídky Data dovoluje autorům sešitů, které jsou určeny pro
používání jinými uživateli, omezit hodnoty zadávané do jednotlivých buněk. Na
obrázku vidíte příklad použití tohoto příkazu k zajištění toho, aby do buňky A1
mohlo být vloženo pouze číslo mezi nulou a stem. Jestliže se do buňky mají
zadávat procenta, zajistí se tak, že nebude možno vložit nesmyslnou hodnotu.
Kromě kontroly nad hodnotou, vkládanou do buňky, umožňují další volby zobrazit
zprávu ve chvíli, kdy je buňka aktivní (např. k popisu hodnot, které mají být
zadány), nebo chybové hlášení při zadání nepřístupných hodnot. Volba Zpráva při
zadávání byla použita v obou ukázkových souborech k objasnění funkce buněk,
přístupných po aktivaci tlačítka Zobraz... z listu Popis.
PŘEDPLATNÉ
ČLÁNKY DO MAILU