Funkce OPAKOVAT/REPT

Příklad

Mám tabulku s tržbami za jednotlivé dny.
Potřebuji graficky lépe zdůraznit, ve kterých dnech bylo dosaženo kterých tržeb - tím, že ve vedlejším sloupečku zobrazím odpovídající počet čárek nebo jiných symbolů. Asi takto:

Návod

Vyřeším s pomocí funkce OPAKOVAT (anglicky REPT). Do buňky C2 ji napíši takto:
=OPAKOVAT("i";B2/1000)
"i" proto, že právě tento symbol chci opakovat
"B2" proto, že v této buňce je uveden počet požadovaného opakování
"/1000" proto, že nechci mít řady příliš dlouhé - proto si je zkrátím tisíckrát.
Pokud bych chtěl funkci zapisovat prostřednictvím dialogového formuláře, vyplním jej takto:



Pak už jen roztáhnu na další řádky.

Pramen:
http://www.youtube.com/watch?v=BRcTGXF311w

Propojení dvou listů

Příklad

Potřebuji na jednom listu počítat s hodnotami jiného listu.
Např. na listu Sheet2 mám v buňce A1 hodnotu 2, v buňce A2 hodnotu 3, na listu Sheet1 je chci sečíst a dostat číslo 5 v buňce A1.

Návod

Začnu na listu Sheet1 v buňce A1.
Napíšu "=".
Kliknu na ouško listu "Sheet2".

V druhém listu kliknu na buňku A1, ve které je jedna ze sčítaných hodnot.
Všimněte si, že:

• Do řádku vzorců nahoře se vpisuje zápis včetně adresy listu
• Jsou označené oba listy najednou

Nyní stiskněte "+" a klikněte na druhou buňku. Tím je zápis vzorečku hotov.
Stiskněte Enter.
Tím se vrátíte na první stránku a jste hotovi.

Propojení dvou listů

Příklad

Potřebuji na jednom listu počítat s hodnotami jiného listu.
Např. na listu Sheet2 mám v buňce A1 hodnotu 2, v buňce A2 hodnotu 3, na listu Sheet1 je chci sečíst a dostat číslo 5 v buňce A1.

Návod

Začnu na listu Sheet1 v buňce A1.
Napíšu "=".
Kliknu na ouško listu "Sheet2".


















V druhém listu kliknu na buňku A1, ve které je jedna ze sčítaných hodnot.

Všimněte si, že:

• Do řádku vzorců nahoře se vpisuje zápis včetně adresy listu
• Jsou označené oba listy najednou

Nyní stiskněte "+" a klikněte na druhou buňku. Tím je zápis vzorečku hotov.
Stiskněte Enter.
Tím se vrátíte na první stránku a jste hotovi.

Návod na použití absolutních odkazů

Příklad

Potřebuji v této tabulce vyplnit sloupeček s daní.

Tedy u lízátek vynásobit 20 Kč krát 20%, u oplatky 30 Kč krát 20%, u bonbonů 15 Kč krát 20%.

Návod

Lízátka zvládnu snadno. Do buňky C2 napíši vzorec =B2*C7, v buňce se zobrazí správně spočítaná hodnota 4 Kč.

Pak se ale pokusím roztáhnout vzorec i na oplatku a bonbony, a nastane problém - do těchto dvou řádků se zobrazila chyba nebo žádná hodnota.
Proč se to tak stalo?
Protože excel při roztažení vzorečku =B2*C7 připočte v každém dalším řádku ke každému číslu jedničku. Takže oplatky mají vzoreček =B3*C8, bonbony =B4*C9.
Sazba daně je ale pořád v buňce C7, takže vzoreček pro výpočet daně si "sahá" na špatné, prázdné, buňky.
Jak to zařídit, abych se problému vyhnul?
Vrátím se k situaci, kdy mám v první buňce správný výpočet s tímto vzorečkem:
=B2*C7
Teď potřebuji excelu říct něco jako:
"Tady je jeden správný vzoreček, který za chvilku roztáhnu na další řádky. Potřebuji ale, aby se při roztažení měnilo pouze číslo řádku s cenou bez daně, a neměnilo se číslo řádku se sazbou daně."
Řeknu mu to tak, že před části vzorečku, které se nemajjí měnit, zapíšu znak "$". Ten v tomto kontextu neoznačuje peníze, ale právě jen pevnou, absolutní část odkazu.
Náš vzoreček tedy bude vypadat takto:
=B2*$C$7
Symboly mohu buď vypsat, nebo si kliknu ve vzorečku na příslušnou část vzorce, takto...

...a stisknu klávesu F4. Symboly se mi doplní samy. Pak zmáčknu Enter a tím ukončím zápis do buňky.
Pak už jen roztáhnu a tabulka je správně vyplněná.

Samozřejmě, že takto malou tabulku je možné správně "zavzorečkovat" i tím, že napíši každý řádek zvlášť. Na větších tabulkách je však použití absolutních odkazů nezbytné.

SVYHLEDAT / VLOOKUP - funkce pro propojování více tabulek

Příklad

Potřebuji z jedné tabulky doplňovat hodnoty do druhé. Nebo, jinými slovy - potřebuji propojit dvě tabulky.

V tomto případě ve vzorové tabulce potřebuji doplnit do sloupečku "Cena za kus" ceny z malé tabulky vpravo.
Např. u čokolády chci doplnit do buňky C5 cenu 12 Kč, která odpovídá čokoládě, u oplatky 8 Kč atd. Mohl bych to udělat ručně, ale u velkých tabulek by to bylo nepřiměřeně zdlouhavé.

Návod

Pomůže mi funkce, která se jmenuje SVYHLEDAT (anglicky VLOOKUP).
Do buňky C5 vpíši:

=SVYHLEDAT(B5;E4:F9;2;0)

• "=" začíná každou funkci
• "SVYHLEDAT" je název funkce
• "B5" proto, že v této buňce je slovo "čokoláda" - slovo, podle kterého má Excel v pravé tabulce najít příslušnou cenu.
• "E4:F9" proto, že v této oblasti je umístěna tabulka, ze které se vybírá.
• "2" proto, že z malé tabulky, ze které se vybírá, se má doplnit hodnota, která je ve druhém sloupečku, což je sloupeček "Cena/ks".
• Poslední parametr je obvykle nula. Jednička se zadává v případech podobných tomuto.

Pokud chci funkci zadat přes formulář (Vložit - Funkce...), vypadá zápis takto:

Než funkci, zadanou do buňky E5, roztáhnu na další řádky, musím doplnit pevné (absolutní) odkazy a funkce pak vypadá takto:

=SVYHLEDAT(B5;$E$4:$F$9;2;0)

Zafixoval jsem druhý parametr - oblast tabulky, ze které se vybírá. Dělám to proto, že tato tabulka zůstane úplně stejná pro všechny řádky a při roztahování se nemá měnit.

Výsledek je takovýto:

Poznámky:

• Kdyby tabulka, ze které se vybírá, byla "obrácená" (stejně jako tabulka vpravo dole u výsledné tabulky), použil bych místo funkce SVYHLEDAT funkci VVYHLEDAT a ve třetím parametru by bylo číslo řádku místo čísla sloupce (v tomto případě je to tak jako tak dvojka), jinak se obě funkce používají obdobně.
• Jak si pamatovat názvy funkcí? "S" na začátku názvu funkce SVYHLEDAT je odvozené od slova "Svisle", "V" na začátku názvu funkce VVYHLEDAT je odvozeno od slova "Vodorovně".
• V anglické verzi se funkce jmenují VLOOKUP a HLOOKUP (vertical / horizontal).
• Uvedené funkce nahrazují "joinovací" dotaz přes dvě tabulky, se kterým se setkáváme v databázích, ale v excelu se jinak než popsanými funkcemi uskutečnit téměř nedá.
• Běžně nejde dohledávat hodnoty ze dvou tabulek (v mém případě nemohu mít dva ceníky a přiřazovat z obou současně). Dá se to obejít např. použitím funkce IFERROR (CHYBHODN), která vypadá takto: =CHYBHODN(SVYHLEDAT(B5;$E$4:$F$9;2;0);SVYHLEDAT(B5;odkaznadruhoutabulku;2;0))
  Dá se to přeložit jako "vyhledej hodnotu v první tabulce, a když tam není, vyhledej v druhé tabulce", takže logicky pokud je hodnota v obou, má přednost první tabulka.
• Každá tabulka může být v jiném listu - pak stačí standardně upravit odkaz.
• Pokud by tabulka, odkud čerpám (v našem případě ceník) měla obrácené sloupce, pak by SVYHLEDAT nefungovala. Ta funguje jen když je nejprve sloupec s "propojovacími" hodnotami (Název zboží) a až dál, kdekoliv vpravo od něj, sloupec, odkud se dosazuje (Cena / ks). V takovém případě bych musel buď sloupce prohodit (a třeba jeden z nich skrýt - abych nenarušil vzhled tabulky), nebo nahradit funkci SVYHLEDAT fintou s kombinací funkcí INDEX a POZVYHLEDAT(MATCH). Ta je na pořadí sloupců nezávislá.

Chcete-li si stáhnout tabulku, uvedenou v tomto článku, klikněte zde.
Procvičení tématu:

• Procvičit práci s SVYHLEDAT / VLOOKUP

SVYHLEDAT / VLOOKUP - funkce pro propojování více tabulek

Příklad

Potřebuji z jedné tabulky doplňovat hodnoty do druhé. Nebo, jinými slovy - potřebuji propojit dvě tabulky.

V tomto případě ve vzorové tabulce potřebuji doplnit do sloupečku "Cena za kus" ceny z malé tabulky vpravo.
Např. u čokolády chci doplnit do buňky C5 cenu 12 Kč, která odpovídá čokoládě, u oplatky 8 Kč atd. Mohl bych to udělat ručně, ale u velkých tabulek by to bylo nepřiměřeně zdlouhavé.
Upozorňuji na nově vypsaný listopadový kurs Excelu. Školí se středně pokročilá látka, výuka probíhá v malých skupinkách. 
Více informací zde.

Návod

Pomůže mi funkce, která se jmenuje SVYHLEDAT (anglicky VLOOKUP).
Do buňky C5 vpíši:

=SVYHLEDAT(B5;E4:F9;2;0)

• "=" začíná každou funkci
• "SVYHLEDAT" je název funkce
• "B5" proto, že v této buňce je slovo "čokoláda" - slovo, podle kterého má Excel v pravé tabulce najít příslušnou cenu.
• "E4:F9" proto, že v této oblasti je umístěna tabulka, ze které se vybírá.
• "2" proto, že z malé tabulky, ze které se vybírá, se má doplnit hodnota, která je ve druhém sloupečku, což je sloupeček "Cena/ks".
• Poslední parametr je obvykle nula. Jednička se zadává v případech podobných tomuto.

Pokud chci funkci zadat přes formulář (Vložit - Funkce...), vypadá zápis takto:

Než funkci, zadanou do buňky E5, roztáhnu na další řádky, musím doplnit pevné (absolutní) odkazy a funkce pak vypadá takto:

=SVYHLEDAT(B5;$E$4:$F$9;2)

Zafixoval jsem druhý parametr - oblast tabulky, ze které se vybírá. Dělám to proto, že tato tabulka zůstane úplně stejná pro všechny řádky a při roztahování se nemá měnit.

Výsledek je takovýto:

Poznámky:

• Kdyby tabulka, ze které se vybírá, byla "obrácená" (stejně jako tabulka vpravo dole u výsledné tabulky), použil bych místo funkce SVYHLEDAT funkci VVYHLEDAT a ve třetím parametru by bylo číslo řádku místo čísla sloupce (v tomto případě je to tak jako tak dvojka), jinak se obě funkce používají obdobně.
• Jak si pamatovat názvy funkcí? "S" na začátku názvu funkce SVYHLEDAT je odvozené od slova "Svisle", "V" na začátku názvu funkce VVYHLEDAT je odvozeno od slova "Vodorovně".
• V anglické verzi se funkce jmenují VLOOKUP a HLOOKUP (vertical / horizontal).
• Uvedené funkce nahrazují "joinovací" dotaz přes dvě tabulky, se kterým se setkáváme v databázích, ale v excelu se jinak než popsanými funkcemi uskutečnit téměř nedá.
• Běžně nejde dohledávat hodnoty ze dvou tabulek (v mém případě nemohu mít dva ceníky a přiřazovat z obou současně). Dá se to obejít např. použitím funkce IFERROR (CHYBHODN), která vypadá takto: =CHYBHODN(SVYHLEDAT(B5;$E$4:$F$9;2;0);SVYHLEDAT(B5;odkaznadruhoutabulku;2;0))
  Dá se to přeložit jako "vyhledej hodnotu v první tabulce, a když tam není, vyhledej v druhé tabulce", takže logicky pokud je hodnota v obou, má přednost první tabulka.
• Každá tabulka může být v jiném listu - pak stačí standardně upravit odkaz.

Chcete-li si stáhnout tabulku, uvedenou v tomto článku, klikněte zde.
Procvičení tématu:

• Procvičit práci s SVYHLEDAT / VLOOKUP

Přesun sloupečků

Příklad

Mám takovouto tabulku:


Potřebuji "Prohodit" sloupečky - tak, aby nebyly v pořadí Jméno - Příjmení - Telefonní linka, ale Příjmení - Jméno - Telefonní linka.

Návod

Výsledku bych mohl dosáhnout běžným kopírováním a přesouváním. Jde to ale i jednoduššeji.
Označíme přesouvaný sloupeček, v našem případě "Jméno".



Najedeme myší na okraj označeného sloupečku, jak je to zobrazené na obrázku.



Ukazatel myši se promění na černý křížek.
Nyní můžeme myší sloupeček "chytit" a tažením přesouvat doprava nebo doleva. Došlo by však k přepsání dat, která jsou na místě, kam sloupeček umístíme.
Proto při přesunu držíme klávesu Shift. Tím zařídíme, že přesouvaný sloupeček se "vrazí" mezi dva jiné sloupečky. Kam přesně? To nám napoví svislá čára.
Výsledek je tento:



Obdobně jako se sloupečky se pracuje s řádky.

Zamknutí souboru

Příklad

Potřebuji zamknout soubor tak, aby ho nikdo nemohl otevřít, nebo aby ho bylo možné otevřít pouze pro čtení bez možnosti úprav.

Návod

Klikněte na Save as / Uložit jako


Klikněte na Tools / Nástroje


Vyberte General options / Obecné možnosti.

Zadejte hesla pro otevření, pro úpravu nebo obě. Následně je zadejte ještě jednou pro kontrolu.

Uložte soubor.

Zobrazení dnešního data

Příklad

Potřebuji mít v buňce vypsaný dnešní datum, který se bude aktualizovat podle toho, kolikátého právě je.

Návod

Vyřeším s pomocí funkce DNES v české verzi
=dnes()
resp. TODAY v anglické verzi
=today()
Závorky za názvem funkce jsou opravdu prázdné.
Pokud bych chtěl zobrazit například zítřejší datum, zapíši funkci takto:
=dnes()+1
Upozornění - pokud chci vložit datum napevno - tak, aby se neaktualizoval - použiji příslušnou klávesovou zkratku.